Controller层
@ApiOperation("导入")
@PostMapping("/importExcel")
public Result importExcel(@RequestParam("myfile") MultipartFile myFile){
ModelAndView modelAndView = new ModelAndView();
try {
equipmentLedgerService.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 {
//ids批量导出选中的数据
equipmentLedgerService.exportExcel(response,ids);
log.info("{}",response);
return Result.success();
} catch (Exception e) {
e.printStackTrace();
log.info("{}",e);
return Result.fail(e);
}
}
Service层
@Override
@SuppressWarnings("resource")
public Result importExcel(MultipartFile myFile){
// 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();
}
}
for (int i = 1; i <= rows+1; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
// **读取cell**
TestEquipment deviceInfo = new TestEquipment();
//设备编号
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);
equipmentLedgerMapper.insert(deviceInfo);
}
}
return Result.success(rows-1);
}
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();
}
}