Excel导入导出
导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
关于类型转换问题
//异常次数
String count = getCellValue(row.getCell(3));
if (!StringUtils.isEmpty(count)) {
Integer countInt = Integer.parseInt(count);
historyExceptionVo.setOccurrenceCount(countInt);
}
//监测时间
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String timeString = getCellValue(row.getCell(4));
if (!StringUtils.isEmpty(timeString)) {
Date date=sdf.parse(timeString);
// 将 Date 转换为 Instant
Instant instant = date.toInstant();
// 将 Instant 转换为 LocalDateTime
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
historyExceptionVo.setTime(localDateTime);
}
ServiceImpl
private final static String XLS = "xls";
private final static String XLSX = "xlsx";
@Override
@SuppressWarnings("resource")
public String TestImport(MultipartFile myFile,HttpServletResponse response) {
//生成错误报告
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet1 = wb.createSheet("Sheet1");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row1 = sheet1.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
HSSFCell cell = row1.createCell(0);
cell.setCellValue("行号");
cell.setCellStyle(style);
cell = row1.createCell(1);
cell.setCellValue("设备编号");
cell.setCellStyle(style);
cell = row1.createCell(2);
cell.setCellValue("设备名称");
cell.setCellStyle(style);
cell = row1.createCell(3);
cell.setCellValue("责任人");
cell.setCellStyle(style);
cell = row1.createCell(4);
cell.setCellValue("错误详情");
cell.setCellStyle(style);
/* */
// 1、用HSSFWorkbook打开或者创建“Excel文件对象”
//
// 2、用HSSFWorkbook对象返回或者创建Sheet对象
//
// 3、用Sheet对象返回行对象，用行对象得到Cell对象
//
// 4、对Cell对象读写。
//获得文件名
Workbook workbook = null;
String fileName = myFile.getOriginalFilename();
if (fileName.endsWith(XLS)) {
//2003
try {
workbook = new HSSFWorkbook(myFile.getInputStream());
} catch (IOException e) {
e.printStackTrace();
}
} else if (fileName.endsWith(XLSX)) {
//2007
try {
workbook = new XSSFWorkbook(myFile.getInputStream());
} catch (IOException e) {
e.printStackTrace();
}
} else {
try {
throw new Exception("文件不是Excel文件");
} catch (Exception e) {
e.printStackTrace();
}
}
Sheet sheet = workbook.getSheet("Sheet1");
int rows = sheet.getLastRowNum();// 指的行数，一共有多少行+
if (rows == 0) {
try {
throw new Exception("请填写数据");
} catch (Exception e) {
e.printStackTrace();
}
}
Integer dataImpor = new Integer(0);
Integer dataErrorImpor = new Integer(0);
for (int i = 1; i <= rows + 1; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
TestEquipment deviceInfo = new TestEquipment();
// **读取cell**
//excelFile = new excel generateErrorReport(list hearderList){}
//var currentData
try {
//currentData == row
//设备编号
String deviceNumber = getCellValue(row.getCell(0));
deviceInfo.setDeviceNumber(deviceNumber);
//设备名称
String name = (getCellValue(row.getCell(1)));
deviceInfo.setDeviceName(name);
//责任人
String responsiblePerson = getCellValue(row.getCell(2));
deviceInfo.setResponsiblePerson(responsiblePerson);
testMApper.insert(deviceInfo);
dataImpor++;
} catch (Exception e) {
e.printStackTrace();
//对Excel错误报告中添加数据
row1 = sheet1.createRow(dataErrorImpor + 1);
TestEquipment stu1 = deviceInfo;
// 第四步，创建单元格，并设置值
Integer rowNumber = i+1;
row1.createCell(0).setCellValue(rowNumber);
row1.createCell(1).setCellValue(stu1.getDeviceNumber());
row1.createCell(2).setCellValue(stu1.getDeviceName());
row1.createCell(3).setCellValue(stu1.getResponsiblePerson());
row1.createCell(4).setCellValue("第"+rowNumber+"行数据异常");
dataErrorImpor++;
// cell = row.createCell(3);
// cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));
//excelFile . add currentData
}
}
}
try {
OutputStream output=response.getOutputStream();
response.reset();
long errorfilename = System.currentTimeMillis();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
String errorfileName = df.format(new Date());// new Date()为获取当前系统时间
response.setHeader("Content-disposition", "attachment; filename="+errorfileName+".xls");
response.setContentType("application/msexcel");
String dataImpor1= dataImpor.toString();
String dataErrorImpor1 = dataErrorImpor.toString();
response.addHeader("success",dataImpor1);
response.addHeader("error",dataErrorImpor1);
wb.write(output);
output.close();
} catch (IOException e) {
e.printStackTrace();
}
return "成功导入了" + dataImpor + "条数据,失败导入了" + dataErrorImpor + "条数据";
}
public String getCellValue(Cell cell) {
String value = "";
if (cell != null) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case NUMERIC: // 数字
value = cell.getNumericCellValue() + "";
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd").format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell.getNumericCellValue());
}
break;
case STRING: // 字符串
value = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
value = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
value = cell.getCellFormula() + "";
break;
case BLANK: // 空值
value = "";
break;
case ERROR: // 故障
value = "非法字符";
break;
default:
value = "未知类型";
break;
}
}
return value.trim();
}
@Override
public void exportExcel(HttpServletResponse response,List<Long> ids) {
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("Sheet1");
// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步，创建单元格，并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell(0);
cell.setCellValue("设备编号");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("设备名称");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("责任人");
cell.setCellStyle(style);
// 第五步，写入实体数据 实际应用中这些数据从数据库得到，
List<TestEquipment> list = new ArrayList<>();
list = equipmentLedgerMapper.getTestInId(ids);
for (int i = 0; i < list.size(); i++){
row = sheet.createRow(i + 1);
TestEquipment stu = list.get(i);
// 第四步，创建单元格，并设置值
row.createCell(0).setCellValue(stu.getDeviceNumber());
row.createCell(1).setCellValue(stu.getDeviceName());
row.createCell(2).setCellValue(stu.getResponsiblePerson());
// cell = row.createCell(3);
// cell.setCellValue(new SimpleDateFormat("yyyy-MM-dd").format(stu.getTime()));
}
//第六步,输出Excel文件
try {
OutputStream output=response.getOutputStream();
response.reset();
long filename = System.currentTimeMillis();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
String fileName = df.format(new Date());// new Date()为获取当前系统时间
response.setHeader("Content-disposition", "attachment; filename="+fileName+".xls");
response.setContentType("application/msexcel");
wb.write(output);
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}
Controller
@ApiOperation("导入")
@PostMapping("/importExcel")
public Result importExcel(@RequestParam("myfile") MultipartFile myFile){
ModelAndView modelAndView = new ModelAndView();
try {
historyExcepTionService.importExcel(myFile);
} catch (Exception e) {
modelAndView.addObject("msg", e.getMessage());
String eString = e.toString();
return Result.fail(eString);
}
modelAndView.addObject("msg", "数据导入成功");
return Result.success();
}
@ApiOperation("导出")
@GetMapping("/exportExcel")
public Result exportExcel(HttpServletResponse response, @RequestParam List<Long> ids) {
try {
historyExcepTionService.exportExcel(response,ids);
log.info("{}",response);
return Result.success();
} catch (Exception e) {
e.printStackTrace();
log.info("{}",e);
return Result.fail(e);
}
}