前言 文章目录 前言JExcelAPIDemo POIHSSFWorkBookXSSFWorkBookDemo SXSSFWorkBookDemo XSSFReaderD
demo代码:https://github.com/RwTo/excel-demo
JAVA解析excel 一般有三种方式
JExcelapi
POI
EasyExcel
官网:https://jexcelapi.sourceforge.net/
- 仅支持 2003 版本的Excel 也就是 后缀名为 xls 的文件
- 采用流式处理模型,逐行读取和写入 ——因此 可以处理大量数据,一般不会出现OOM
<dependency><groupId>net.sourceforge.jexcelapigroupId><artifactId>jxlartifactId><version>2.6.12version>dependency>
读xls 文件
public class ReadExcelDemo { public static void main(String[] args) { try { // 1. 打开 Excel 文件 String filePath = ExcelConstant.EXCEL_PATH_XLS; Workbook workbook = Workbook.getWorkbook(new java.io.File(filePath)); // 2. 获取第一个工作表 Sheet sheet = workbook.getSheet(0); // 3. 遍历每一行,并读取数据 for (int row = 0; row < sheet.getRows(); row++) { for (int col = 0; col < sheet.getColumns(); col++) { Cell cell = sheet.getCell(col, row); String content = cell.getContents(); content += cell.getCellFORMat().getBackgroundColour().getDescription(); System.out.print(content+"\t"); } System.out.println(); } // 4. 关闭工作簿 workbook.close(); } catch (Exception e) { e.printStackTrace(); } }}
写xls 文件
public class WriteExcelDemo { public static void main(String[] args) { try { String filePath = ExcelConstant.EXCEL_PATH_XLS; // 1. 创建工作簿 WritableWorkbook workbook = Workbook.createWorkbook(new File(filePath)); // 2. 创建工作表 WritableSheet sheet = workbook.createSheet("Sheet1", 0); // 3. 定义单元格颜色 WritableCellFormat greenFormat = new WritableCellFormat(); greenFormat.setBackground(jxl.format.Colour.GREEN); WritableCellFormat yellowFormat = new WritableCellFormat(); yellowFormat.setBackground(jxl.format.Colour.YELLOW); // 4. 写入数据 for (int row = 0; row < 4000; row++) { for (int col = 0; col < 50; col++) { if(col%2 == 0){ Label label = new Label(col, row, "Cell " + (row + 1) + "-" + (col + 1),yellowFormat); sheet.addCell(label); }else{ Label label = new Label(col, row, "Cell " + (row + 1) + "-" + (col + 1),greenFormat); sheet.addCell(label); } } } // 5. 保存工作簿 workbook.write(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } }}
官网:https://poi.apache.org/
功能比较丰富,使用较为广泛,问题也比较多(比如OOM)
四种API 操作Excel
官网介绍:https://poi.apache.org/components/spreadsheet/how-to.html
文档对象模型
HSSFWorkBook (功能丰富,但会OOM)
XSSFWorkBook (功能丰富,但易OOM)
事件处理模型(流式处理)
SXSSFWorkBook (只支持xlsx 的写入)
XSSFReader(仅提供模板,需要使用者自己编写)
POM 文件
<dependency><groupId>org.apache.poigroupId><artifactId>poiartifactId><version>4.1.2version>dependency><dependency><groupId>org.apache.poigroupId><artifactId>poi-ooxmlartifactId><version>4.1.2version>dependency>
- 支持 xls 文件的解析和写入
- 基于DOM ,将整个 Excel 文件加载到内存中,构建一个完整的 Excel 文档对象模型树,再进行解析和操作,当文件较大时,可能会出现内存溢出
但因为 xls 文件支持的样式和单元格数量较少,一般不会出现OOM
- 支持 xlsx 文件的解析和写入
- 基于DOM ,将整个 Excel 文件加载到内存中,构建一个完整的 Excel 文档对象模型树,再进行解析和操作,当文件较大时,可能会出现内存溢出
写入excel(xls,xlsx)
public class WriteExcelDemo { public static void main(String[] args) { try { String filePath = ExcelConstant.EXCEL_PATH_XLS; //String filePath = ExcelConstant.EXCEL_PATH_XLSX; //Workbook workbook = new XSSFWorkbook(); //支持xlsx文件的写入 Workbook workbook = new HSSFWorkbook(); //支持xls文件的写入 Sheet sheet = workbook.createSheet("Sheet1"); for (int row = 0; row < 4000; row++) { Row excelRow = sheet.createRow(row); for (int col = 0; col < 50; col++) { Cell cell = excelRow.createCell(col); cell.setCellValue("Cell " + (row + 1) + "-" + (col + 1)); } } FileOutputStream fileOutputStream = new FileOutputStream(filePath); workbook.write(fileOutputStream); fileOutputStream.close(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } }}
读excel(xls,xlsx)
public class ReadExcelDemo { public static void main(String[] args) { try { String filePath = ExcelConstant.EXCEL_PATH_XLSX; //String filePath = ExcelConstant.EXCEL_PATH_XLSX; FileInputStream fileInputStream = new FileInputStream(filePath); //WorkbookFactory会根据文件类型自动选择使用HSSFWorkBook 或 XSSFWorkBook Workbook workbook = WorkbookFactory.create(fileInputStream); Sheet sheet = workbook.getSheetAt(0); for (Row row : sheet) { for (Cell cell : row) { String cellValue = getCellValueAsString(cell); System.out.print(cellValue + "\t"); } System.out.println(); } fileInputStream.close(); workbook.close(); } catch (Exception e) { e.printStackTrace(); } } private static String getCellValueAsString(Cell cell) { if (cell == null) { return ""; } switch (cell.getCellType()) { case STRING: return cell.getStrinGCellValue(); case NUMERIC: return String.valueOf(cell.getNumericCellValue()); case BOOLEAN: return String.valueOf(cell.getBooleanCellValue()); default: return ""; } }}
- 仅支持xlsx 的写入
- 基于流式处理,逐行对excel 处理,用磁盘空间换取内存,可以高效地写入数据到 Excel 文件中,同时减少内存占用。
- SXSSFWorkbook 采用了内存优化的设计,避免了将所有数据加载到内存的需求。它仅将有限数量的行缓冲在内存中,写入到输出流后即丢弃缓冲的数据,从而减少了内存占用。
- SXSSFWorkbook 支持设置在写入数据之前保留在内存中的行数。通过控制分页大小,可以灵活地控制内存使用,特别适合处理超大型的 Excel 文件。
写入xlsx
public class SXSSFWriteExcelDemo { public static void main(String[] args) { try { // Set custom temporary directory String customTempDirPath = "temp"; System.setProperty("java.io.tmpdir", customTempDirPath); String filePath = ExcelConstant.EXCEL_PATH_XLSX_BIG; SXSSFWorkbook workbook = new SXSSFWorkbook(500);//rowAccesswindowsize为内存中缓存的记录数,默认100 Sheet sheet = workbook.createSheet("Sheet1"); for (int row = 0; row < 100000; row++) { Row excelRow = sheet.createRow(row); for (int col = 0; col < 3; col++) { Cell cell = excelRow.createCell(col); cell.setCellValue("Cell " + (row + 1) + "-" + (col + 1)); } } FileOutputStream fileOutputStream = new FileOutputStream(filePath); workbook.write(fileOutputStream); fileOutputStream.close(); //使用 dispose() 方法释放(删除) SXSSFWorkbook 使用的临时资源,特别是在写入大量数据后,这一步骤很重要。 workbook.dispose(); //程序执行结束后,手动删除临时文件目录(看是否需要) deleteTempFiles(new File(customTempDirPath)); } catch (Exception e) { e.printStackTrace(); } } private static void deleteTempFiles(File directory) { if (directory.isDirectory()) { File[] files = directory.listFiles(); if (files != null) { for (File file : files) { deleteTempFiles(file); } } } if (!directory.delete()) { System.err.println("Failed to delete temp file: " + directory.getAbsolutePath()); } else { System.out.println("Deleted temp file: " + directory.getAbsolutePath()); } }}
官网案例:https://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api
- 仅支持 xlsx 的解析
- 基于流式处理,逐行读取单元格,不会将整个excel 存到内存,适合大型excel的处理
读xlsx
public class SAXWriteExcelDemo { public static void main(String[] args) throws Exception { String filePath = ExcelConstant.EXCEL_PATH_XLSX_BIG; InputStream is = new FileInputStream(filePath); OPCPackage opcPackage = OPCPackage.open(is); try { // 读取 Excel 文件 XSSFReader reader = new XSSFReader(opcPackage); // 使用事件处理器处理 Sheet 数据 SAXSheetHandler sheetHandler = new SAXSheetHandler(); XSSFSheetXMLHandler xmlHandler = new XSSFSheetXMLHandler(reader.getStylesTable(), reader.getSharedStringsTable(), sheetHandler, false); XMLReader sheetParser = XMLReaderFactory.createXMLReader(); sheetParser.setContentHandler(xmlHandler); Iterator<InputStream> sheetsData = reader.getSheetsData(); while(sheetsData.hasNext()){ InputStream sheetIs = sheetsData.next(); InputSource sheet = new InputSource(sheetIs); //开始解析sheet页 System.out.println("=====================================开始处理sheet页=============================================="); long start = System.currentTimeMillis(); sheetParser.parse(sheet); System.out.println("=====================================处理sheet结束=============================================="); long end = System.currentTimeMillis(); List<List<SAXCell>> curSheet = sheetHandler.getCurSheet(); curSheet.forEach(System.out::println); System.out.println("处理行数:"+sheetHandler.getRowCount()); System.out.println("处理单元格数:"+sheetHandler.getCellCount()); System.out.println("处理时间(ms):"+(end-start)); sheetHandler.clear(); } } catch (IOException e) { e.printStackTrace(); } catch (OpenXML4JException e) { e.printStackTrace(); } catch (SAXException e) { e.printStackTrace(); }finally { // 关闭输入流和 OPCPackage is.close(); opcPackage.close(); } }}class SAXSheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler { //当前页 private List<List<SAXCell>> curSheet; //当前行 private List<SAXCell> curRow; //总单元格数 private int cellCount; public SAXSheetHandler() { this.curSheet = new LinkedList<>(); } @Override public void startRow(int i) { //开始处理新的一行,初始化当前行 curRow = new LinkedList<>(); } @Override public void endRow(int i) { //一行处理结束,将这一行数据存入sheet curSheet.add(curRow); } @Override public void cell(String cellReference, String formattedValue, XSSFComment xssfComment) { SAXCell cell = new SAXCell(cellReference, formattedValue); curRow.add(cell); cellCount++; } @Override public void headerFooter(String text, boolean isHeader, String tagName) { // 头部和页脚 } @Override public void endSheet() { //sheet处理结束 } public List<List<SAXCell>> getCurSheet() { return curSheet; } public int getCellCount() { return cellCount; } public int getRowCount() { return curSheet.size(); } public void clear(){ curSheet = new LinkedList<>(); cellCount = 0; }}@Dataclass SAXCell { private String cellName; private String value; public SAXCell(String cellName, String value) { this.cellName = cellName; this.value = value; }}
官网:https://easyexcel.opensource.alibaba.com/
- 支持xlsx和xls 文件的解析和写入
- 流式处理,节省内存,可以处理大型excel
- 支持注解,简单易用
POM
<dependency><groupId>com.alibabagroupId><artifactId>easyexcelartifactId><version>2.2.0-beta2version>dependency><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starterartifactId>dependency>
实例
@Datapublic class UserData { private String name; private int age; private String email; public UserData() { } public UserData(String name, int age, String email) { this.name = name; this.age = age; this.email = email; }}
写入excel(xls,xlsx)
public class EasyWriteExcelDemo { public static void main(String[] args) { String filePath = ExcelConstant.EE_EXCEL_PATH_XLSX_BIG; // 创建写入的数据列表 List<UserData> dataList = new ArrayList<>(); dataList.add(new UserData("Alice", 25, "alice@example.com")); dataList.add(new UserData("Bob", 30, "bob@example.com")); dataList.add(new UserData("Charlie", 28, "charlie@example.com")); // 使用 EasyExcel 写入 Excel 文件 EasyExcel.write(filePath, UserData.class).sheet("Sheet1").doWrite(dataList); }}
读取excel(xls,xlsx)
public class EasyReadExcelDemo { public static void main(String[] args) { String filePath = ExcelConstant.EE_EXCEL_PATH_XLSX_BIG; // 使用 EasyExcel 读取 Excel 文件 EasyExcel.read(filePath, UserData.class, new UserDataListener()).sheet().doRead(); } public static class UserDataListener extends AnalysisEventListener<UserData> { private List<UserData> dataList = new ArrayList<>(); @Override public void invoke(UserData data, AnalysisContext context) { dataList.add(data); } @Override public void doAfterAllAnalysed(AnalysisContext context) { // 在这里可以对 dataList 中的数据进行处理,比如保存到数据库或其他操作 for (UserData userData : dataList) { System.out.println(userData.getName() + "\t" + userData.getAge() + "\t" + userData.getEmail()); } } }}
来源地址:https://blog.csdn.net/ren9436/article/details/131856312
--结束END--
本文标题: JAVA解析EXCEL(JExcelAPI,POI,EasyExcel)
本文链接: https://www.lsjlt.com/news/423287.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-04-01
2024-04-03
2024-04-03
2024-01-21
2024-01-21
2024-01-21
2024-01-21
2023-12-23
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0