Java实现读取Excel文件并逐行写入到新的Excel文件中

在日常开发中，经常需要处理Excel文件，其中一种常见的需求是将原始Excel文件的内容逐行写入到新的Excel文件中或者写入数据中。本篇博客将介绍使用Java编程语言以及Apache POI库来实现这一功能。Apache POI是一个开源的Java库，用于处理Microsoft Office格式的文件，包括Excel。

1. 环境准备 Apache POI库的依赖添加

 <!--使用 Apache POI 库来读取 Excel 文件-->
        <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>

2. 读取Excel文件逐行写入新的Excel文件

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;

/**
 * Java实现读取Excel文件并逐行写入到新的Excel文件中
 */
public class ExcelReaderAndWriter {

    public static void main(String[] args) {
        String inputFilePath = "src/main/resources/data/excel/读取测试.xlsx";
        String outputFilePath = "src/main/resources/data/excel/写入测试.xlsx";

        try (FileInputStream inputFileStream = new FileInputStream(inputFilePath);
             Workbook inputWorkbook = new XSSFWorkbook(inputFileStream);
             Workbook outputWorkbook = new XSSFWorkbook()) {

            Sheet inputSheet = inputWorkbook.getSheetAt(0);
            Sheet outputSheet = outputWorkbook.createSheet("Output Sheet");

            // 设置日期格式
            CellStyle dateCellStyle = outputWorkbook.createCellStyle();
            dateCellStyle.setDataFormat(outputWorkbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));

            // 遍历每一行并写入新的 Excel 文件
            //使用 Iterator<Row> 来遍历输入工作表的每一行，并对每一行进行处理
            Iterator<Row> rowIterator = inputSheet.iterator();
            while (rowIterator.hasNext()) {
                Row inputRow = rowIterator.next();
                // 创建新的一行并获取输入单元格的迭代器
                Row outputRow = outputSheet.createRow(inputRow.getRowNum());

                // 遍历每一列
                Iterator<Cell> cellIterator = inputRow.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell inputCell = cellIterator.next();
                    Cell outputCell = outputRow.createCell(inputCell.getColumnIndex());

                    // 获取单元格的值并写入新的工作表中
                    switch (inputCell.getCellType()) {
                        case STRING:
                            outputCell.setCellValue(inputCell.getStringCellValue());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(inputCell)) {
                                // 设置日期单元格的值和格式
                                outputCell.setCellValue(inputCell.getDateCellValue());
                                outputCell.setCellStyle(dateCellStyle);
                            } else {
                                outputCell.setCellValue(inputCell.getNumericCellValue());
                            }
                            break;
                        case BOOLEAN:
                            outputCell.setCellValue(inputCell.getBooleanCellValue());
                            break;
                        case FORMULA:
                            outputCell.setCellFormula(inputCell.getCellFormula());
                            break;
                        case BLANK:
                            outputCell.setCellValue("");
                            break;
                        default:
                            break;
                    }
                }

                // 输出每一行的数据
                System.out.println("Row " + (inputRow.getRowNum() + 1) + ": " + rowToString(inputRow));
            }

            // 保存新的 Excel 文件
            try (FileOutputStream outputFileStream = new FileOutputStream(outputFilePath)) {
                outputWorkbook.write(outputFileStream);
            }

            System.out.println("Data has been written to the new Excel file successfully.");

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String rowToString(Row row) {
        StringBuilder stringBuilder = new StringBuilder();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            // 遍历每个单元格的数据
            //System.out.println(cell);
            if (stringBuilder.length() > 0) {
                stringBuilder.append("\t");
            }
            stringBuilder.append(cellToString(cell));
        }
        return stringBuilder.toString();
    }

    private static String cellToString(Cell cell) {
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
}

3. 测试结果