Java读取Excel文件，导出Excel文件

朱雀 2024-03-23 21:32 114阅读 0赞

**目录**

1、使用Apache POI框架读取Excel文件

2、使用EasyExcel框架读取Excel文件

3、java导出Excel数据

--------------------

##### 1、使用Apache POI框架读取Excel文件 #####

Java读取Excel文件，并过滤空行，且读取多个工作表

package com.boredou.boredou.test;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    
    public class ReadExcel {
        public static void main(String[] args) {
            try {
                // 创建输入流
                String filePath = "C:\\Users\\Administrator\\Desktop\\1.xlsx";
                int suffix = filePath.lastIndexOf(".");
                //获取文件后缀名
                String substring = filePath.substring(suffix + 1);
                if (!"xlsx".equals(substring) && !"xls".equals(substring)) {
                    System.out.printf("不是excel文件");
                }
                //xlsx
                FileInputStream fis = new FileInputStream(filePath);
    
                // 创建工作簿对象
                Workbook workbook = null;
                if ("xlsx".equals(substring) ) {//xlsx
                    workbook = WorkbookFactory.create(fis);
                }else {//xls
                    workbook = new XSSFWorkbook(fis);
                }
    
                // 获取第一个工作表
                Sheet sheet = workbook.getSheetAt(0);
    
                // 获取行数和列数
                int rowCount = sheet.getLastRowNum() + 1;
                int columnCount = sheet.getRow(0).getLastCellNum();
    
                // 循环遍历每一行
                for (int i = 0; i < rowCount; i++) {
                    Row row = sheet.getRow(i);
    
                    // 判断是否为空行
                    boolean isRowEmpty = true;
                    for (int j = 0; j < columnCount; j++) {
                        //在调用row.getCell(j)方法之前，可以先检查当前行是否为空或是否为null。
                        if (row != null && row.getRowNum() >= 0) {
                            Cell cell = row.getCell(j);
                            if (cell != null && cell.getCellType() != CellType.BLANK) {
                                isRowEmpty = false;
                                break;
                            }
                        }
                    }
                    if (isRowEmpty) {
                        continue;
                    }
    
                    // 遍历每一列
                    for (int j = 0; j < columnCount; j++) {
                        Cell cell = row.getCell(j);
    
                        // 获取单元格的值
                        String cellValue = "";
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            cellValue = cell.getStringCellValue();
                        }
                        switch (j) {
                            case 0:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 1:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 2:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 3:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                        }
                    }
                    System.out.println();
                }
    
    
                // 获取第二个工作表
                Sheet sheet1 = workbook.getSheetAt(1);
    
    			// 获取行数和列数
                int rowCount1 = sheet1.getLastRowNum() + 1;
                int columnCount1 = sheet1.getRow(0).getLastCellNum();
    			// 循环遍历每一行
                for (int i = 0; i < rowCount1; i++) {
                    Row row = sheet1.getRow(i);
    
                    // 判断是否为空行
                    boolean isRowEmpty = true;
                    for (int j = 0; j < columnCount1; j++) {
                        //在调用row.getCell(j)方法之前，可以先检查当前行是否为空或是否为null。
                        if (row != null && row.getRowNum() >= 0) {
                            Cell cell = row.getCell(j);
                            if (cell != null && cell.getCellType() != CellType.BLANK) {
                                isRowEmpty = false;
                                break;
                            }
                        }
                    }
                    if (isRowEmpty) {
                        continue;
                    }
    
                    // 遍历每一列
                    for (int j = 0; j < columnCount1; j++) {
                        Cell cell = row.getCell(j);
    
                        // 获取单元格的值
                        String cellValue = "";
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            cellValue = cell.getStringCellValue();
                        }
                        switch (j) {
                            case 0:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 1:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 2:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                            case 3:
                                if (StringUtils.isNotBlank(cellValue)) {
                                    System.out.print(cellValue + "\t");
                                }
                                break;
                        }
                    }
                    System.out.println();
                }
                // 关闭工作簿和输入流
                workbook.close();
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

所需依赖

<dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>4.1.2</version>
                </dependency>
    
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                    <version>4.1.2</version>
                </dependency>

结果

![ee2d85d120a24643a2f783cabc46a408.png][]

![736a4d5c44b94c518fbd9bca2fd3db0d.png][]

![5d907665cd544d2cbb59d647e24e88a4.png][]

##### 2、使用EasyExcel框架读取Excel文件 #####

public class Demo2 {
        public static void main(String[] args) {
            String fileName = "C:\\Users\\Administrator\\Desktop\\1.xlsx"; // Excel 文件的路径
    
            // 定义数据存储容器
            List<Map<Integer, String>> dataList = new ArrayList<>();
    
            // 构造监听器对象
            AnalysisEventListener<Map<Integer, String>> listener = new AnalysisEventListener<Map<Integer, String>>() {
                @Override
                public void invoke(Map<Integer, String> rowData, AnalysisContext context) {
                    dataList.add(rowData); // 将每行数据添加到容器中
                }
    
                @Override
                public void extra(CellExtra extra, AnalysisContext context) {
                    // 处理额外的单元格信息，比如合并单元格、批注等
                    // ...
                }
    
                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    // 数据解析完成后的操作，可以在这里进行业务逻辑处理
                    // ...
                }
            };
    
            // 读取 Excel 文件
            EasyExcel.read(fileName).registerReadListener(listener).sheet(0).doRead();
    
            // 遍历数据
            for (Map<Integer, String> rowData : dataList) {
                // 处理每行数据
                for (Map.Entry<Integer, String> entry : rowData.entrySet()) {
                    //Integer columnIndex = entry.getKey(); // 列索引
                    String value = entry.getValue();// 单元格数据
    
                    // 处理每个单元格的值
                    System.out.print(value + "\t");
                }
                System.out.println(); // 换行
            }
        }
    }

所需依赖

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.2.6</version>
            </dependency>

示例

![1ca3de0c1f994756b8757468f6d05355.png][]

##### 3、java导出Excel数据 #####

// 创建工作簿和工作表
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("Sheet1");
            //要写入的数据
         	List<Object> list1 = Arrays.asList("Apple", "Banana", "Orange");
         	List<Object> list2 = Arrays.asList("zhangsan", "lisi", "wangwu");
         	List<Object> list3 = Arrays.asList(1, 2, 3);
            List<List<Object>> ListString = Arrays.asList(list1, list2, list3);
    
            // 写入数据
            int rowNum = 0;
            for (List<Object> stringList : ListString) {
                //行数
                Row row = sheet.createRow(rowNum++);
                int colNum = 0;
                for (Object s : stringList) {
                    //列数
                    Cell cell = row.createCell(colNum++);
                    if (s instanceof String) {
                        //字符串类型的
                        cell.setCellValue((String) s);
                    } else if (s instanceof Integer) {
                        //int类型
                        cell.setCellValue((Integer) s);
                    }
                }
            }
    
            // 保存Excel文件
            try {
                //
                FileOutputStream outputStream = new FileOutputStream("D:\\output.xlsx");
                //写入数据
                workbook.write(outputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }finally {
                // 关闭工作簿
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

![2f11db84d19f4afdadeeabd67dcaf2e8.png][]

[ee2d85d120a24643a2f783cabc46a408.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/cf38f18d60e84bdf83c48ebd3d8557a9.png
[736a4d5c44b94c518fbd9bca2fd3db0d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/88ad701259144991b7cb0202c8c9b12f.png
[5d907665cd544d2cbb59d647e24e88a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/ef7df49d5373479cae38284c633a91bf.png
[1ca3de0c1f994756b8757468f6d05355.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b70db644c1f8401081d1bc2672b53b97.png
[2f11db84d19f4afdadeeabd67dcaf2e8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/b3462b50922648939feb4ef4cd5ddd23.png