Excel文件:.xls 和 .xlsx ,① .xls是旧版本,.xlsx是新版本,后者支持存储更多的数据。② 存储同样多的数据,.xlsx格式文件更小。③ .xlsx转为.xls,可能会丢失数据;.xls转为.xlsx,没有问题。
使用Apache POI框架提供的API操作Excel。
POI提供了三种API来操作Excel:HSSF、XSSF、SXSSF。
HSSF对应旧的xls格式,XSSF对应新的xlsx格式,SXSSF是在XSSF的基础上,支持导出大批量的Excel数据。
使用HSSF来操作Excel处理表格数据
sheet是表格,一个Excel工作簿能创建多个表格:(Excel文档=工作簿)
HSSF的类和说明
类 | 描述 |
---|---|
HSSFWorkbook | 工作簿,Excel的文档对象 |
HSSFSheet | 工作表,Excel的表格对象 |
HSSFRow | Excel的行 |
HSSFCell | Excel的单元格 |
HSSFFont | Excel的字体 |
HSSF操作Excel,引入POI依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
Java生成Excel文档
Excel的基本操作
- 创建Excel文件:new HSSFWorkbook()
- 创建sheet表格:workbook.createSheet() → HSSFSheet
- 创建第0行:sheet.createRow(0)
- 创建当前行的第0列的单元格:row.createCell(0)
- 设置单元格的内容:cell.setCellValue(“数据”)
- 导出数据
6.1.导出数据生成Excel文件:6.2.导出数据到浏览器下载:workbook.write(outputStream); outputStream.close(); workbook.close();
public void getExcel(HttpServletResponse response) throws IOException { Workbook workbook = service.getExcel(); String fileName = "test.xls"; //响应类型为application/octet- stream,表示不想直接显示内容 response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE); //以附件的方式下载 response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8")); ServletOutputStream outputStream = response.getOutputStream(); workbook.write(outputStream); outputStream.close(); workbook.close();
Java读取Excel文档
读取Excel文件:new FileInputStream(File)
new HSSFWorkbook(ins)根据索引获取工作表:workbook.getSheetAt(index)
获取最后行的索引,做遍历:sheet.getLastRowNum()
根据索引获取行:<= sheet.getRow(index)
获取当前行的最后单元格的索引,做遍历:< row.getLastCellNum()
根据索引获取单元格:row.getCell(index)
获取单元格的值
方式一:根据单元格值的类型调用不同的值获取方法:switch (cell.getCellType()){ case HSSFCell.CELL_TYPE_NUMERIC -> cell.getNumericCellValue() case HSSFCell.CELL_TYPE_STRING -> cell.getStringCellValue() case HSSFCell.CELL_TYPE_BOOLEAN -> cell.getBooleanCellValue() case HSSFCell.CELL_TYPE_FORMULA -> cell.getCellFormula() case HSSFCell.CELL_TYPE_BLANK -> System.out.println(" "); case HSSFCell.CELL_TYPE_ERROR -> System.out.println("非法字符"); default -> System.out.println("未知类型\t"); }
方式二: cell.toString()
读取手机号使用的是科学计数法1.32…E10,使用DataFormatter格式化:
if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){ //日期格式化 if (HSSFDateUtil.isCellDateFormatted(cell)){ Date date = cell.getDateCellValue(); short format = cell.getCellStyle().getDataFormat(); if (format == 14 || format == 17){ cellValue = new SimpleDateFormat("YYYY-MM-dd").format(date); }else { cellValue = new SimpleDateFormat("YYYY-MM-dd hh:mm:ss").format(date); } }else{ //手机号格式化 DataFormatter dataFormatter = new DataFormatter(); dataFormatter.addFormat("###########", null); String cellValue = dataFormatter.formatCellValue(cell); } }else { String cellValue = cell.toString(); }
关闭输入流:ins.close()
解析Excel出错
OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format.
使用XSSFWorkbook解析Excel文件出错:
The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)
问题:使用XSSFWorkbook读取.xls文件导致,XSSFWorkbook仅用于读取.xlsx文件。XSSFWorkbook类型支持Excel2007之后的文件版本,不支持07以前的版本;HSSFWorkbook类型支持后面的版本,
解决: