maven-poi
<!--excel-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.15</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.2</version>
</dependency>
springmvc 文件上传限制
<!--配置文件解析器,id名不可改,必须用这个-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<property name="maxUploadSize" value="10485760"></property>
</bean>
上传excel 并解析excel
<meta http-equiv="Content-Type" content="multipart/form-data; charset=utf-8" />
<form action="fileUpload" method="post" enctype="multipart/form-data">
<input type="file" name="fileUpload"> <%--此处的id值,需要与对应的处理 MultipartFile 实例参数名一致--%>
<input type="submit" value="导入文件">
</form>
//excel文件上传
@RequestMapping(value = "/fileUpload")
public String fileUpload(HttpServletRequest request,MultipartFile fileUpload) throws IOException {
XSSFWorkbook workbook=null;
File file = new File("xx");
FileInputStream fileInputStream = null;
byte[] bytes= fileUpload.getBytes();
OutputStream output = new FileOutputStream(file);
BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
bufferedOutput.write(bytes);
fileInputStream = new FileInputStream(file);
file.deleteOnExit(); //删除文件
fileUpload=null;
BufferedInputStream bis = new BufferedInputStream(fileInputStream);
//创建工作簿
workbook= new XSSFWorkbook(bis);//注意07的版本使用这个
//按索引读取sheet表
XSSFSheet sheet = workbook.getSheetAt(0);
//取得所有的行数
int rowNum = sheet.getLastRowNum();
for (int i = 1; i <= rowNum; i++) {
//读取1行
XSSFRow row = sheet.getRow(i);
XSSFCell cell=null;
//读取每行的列数
//short cellNum = row.getLastCellNum();
Employee employee = new Employee();
cell= row.getCell(1);
employee.setEname(getCellValue( cell));
Department department = new Department();
cell= row.getCell(2);
department.setDid(Integer.parseInt(getCellValue(cell)));
cell= row.getCell(3);
department.setDname(getCellValue(cell));
cell= row.getCell(4);
department.setDaddress(getCellValue(cell));
employee.setDno(department);
iEmployeeService.insertEmployee(employee);
}
return "index";
}
//excel 单元格格式
private String getCellValue(XSSFCell cell) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC: // 数字
// 如果为时间格式的内容
if (DateUtil.isCellDateFormatted(cell)) {
//注:format格式 yyyy-MM-dd
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
return sdf.format(cell.getDateCellValue());
} else {
//有小数保留两位,位数不够自动忽略
return new DecimalFormat("#.##").format(cell.getNumericCellValue());
}
case Cell.CELL_TYPE_STRING: // 字符串
return cell.getStringCellValue();
case Cell.CELL_TYPE_BOOLEAN: // Boolean
return cell.getBooleanCellValue() + "";
case Cell.CELL_TYPE_FORMULA: // 公式
return cell.getCellFormula() + "";
case Cell.CELL_TYPE_BLANK: // 空值
return "";
case Cell.CELL_TYPE_ERROR: // 故障
return "非法字符";
default:
return "未知类型";
}
}
构建excel 并下载
//导出文件 导出的文件不能直接导入,需要另存为xlsx
@RequestMapping("/fileDownload")
public void fileDownload(HttpServletResponse response) throws IOException {
//创建工作簿,SXSSFWorkbook支持大数据量的导出
SXSSFWorkbook workbook = new SXSSFWorkbook();
//创建sheet表
Sheet sheet = workbook.createSheet("康舍司测试表");
List<Employee> employees = iEmployeeService.selectEmployee(0, 0);
//创建标题
Row rowTitle = sheet.createRow(0);
Cell cellTitle=null;
cellTitle = rowTitle.createCell(0);
cellTitle.setCellValue("eid");
cellTitle = rowTitle.createCell(1);
cellTitle.setCellValue("ename");
cellTitle = rowTitle.createCell(2);
cellTitle.setCellValue("did");
cellTitle = rowTitle.createCell(3);
cellTitle.setCellValue("dname");
cellTitle = rowTitle.createCell(4);
cellTitle.setCellValue("daddress");
for (int i = 0; i < employees.size(); i++) {
//创建行
Row row = sheet.createRow(i+1);
Cell cell =null;//创建单元格
cell = row.createCell(0);
cell.setCellValue(employees.get(i).getEid());
cell = row.createCell(1);
cell.setCellValue(employees.get(i).getEname());
cell = row.createCell(2);
cell.setCellValue(employees.get(i).getDno().getDid());
cell = row.createCell(3);
cell.setCellValue(employees.get(i).getDno().getDname());
cell = row.createCell(4);
cell.setCellValue(employees.get(i).getDno().getDaddress());
}
ServletOutputStream outputStream = response.getOutputStream();
response.reset(); //清空输出六
response.setCharacterEncoding("UTF-8");
response.setContentType("application/force-download");
//response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition","attachment;filename=fileOut.xlsx");
response.setHeader("Pargam","no-cache");
response.setHeader("Cache-Control","no-cache");
workbook.write(outputStream);
workbook.close();
}
问题:通过SXSSFWorkbook构建的excel (xlsx) 表格,导出后,该文件导入无法解析
原因:导出的excel文件头中有问题
解决方法:将导出文件,另存为xlsx,就可以,重新上传
导出自动设置列宽