如何用poi读取excel模板内容 并且保留样式 之后另存为

Question

如题- -我想读取一个excel文件，然后需要他的全部内容跟样式

Answer 1

可以使用 Apache POI

<dependencies>
    <!-- Apache POI -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>5.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.3</version>
    </dependency>
    <!-- Apache POI for dealing with OOXML -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml-schemas</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!-- Apache POI for dealing with common files -->
    <dependency>
        <groupId>org.apache.xmlbeans</groupId>
        <artifactId>xmlbeans</artifactId>
        <version>5.1.1</version>
    </dependency>
</dependencies>

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;

public class ExcelCopyExample {

    public static void main(String[] args) {
        String sourceFilePath = "path/to/source/excel/file.xlsx"; // 源文件路径
        String destinationFilePath = "path/to/destination/excel/file.xlsx"; // 目标文件路径

        try (FileInputStream fis = new FileInputStream(sourceFilePath);
             Workbook sourceWorkbook = new XSSFWorkbook(fis);
             Workbook destinationWorkbook = new XSSFWorkbook()) {

            // 遍历源工作簿中的每个工作表
            for (int i = 0; i < sourceWorkbook.getNumberOfSheets(); i++) {
                Sheet sourceSheet = sourceWorkbook.getSheetAt(i);
                Sheet destinationSheet = destinationWorkbook.createSheet(sourceSheet.getSheetName());

                // 复制每个工作表中的每一行和单元格
                for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
                    Row sourceRow = sourceSheet.getRow(rowIndex);
                    Row destinationRow = destinationSheet.createRow(rowIndex);

                    if (sourceRow != null) {
                        copyRow(sourceRow, destinationRow, destinationWorkbook);
                    }
                }
            }

            // 将目标工作簿写入文件
            try (FileOutputStream fos = new FileOutputStream(destinationFilePath)) {
                destinationWorkbook.write(fos);
            }

            System.out.println("Excel文件复制成功！");

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

    // 复制行
    private static void copyRow(Row sourceRow, Row destinationRow, Workbook destinationWorkbook) {
        for (int colIndex = 0; colIndex < sourceRow.getLastCellNum(); colIndex++) {
            Cell sourceCell = sourceRow.getCell(colIndex);
            Cell destinationCell = destinationRow.createCell(colIndex);

            if (sourceCell != null) {
                copyCell(sourceCell, destinationCell, destinationWorkbook);
            }
        }
    }

    // 复制单元格
    private static void copyCell(Cell sourceCell, Cell destinationCell, Workbook destinationWorkbook) {
        CellStyle newCellStyle = destinationWorkbook.createCellStyle();
        newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
        destinationCell.setCellStyle(newCellStyle);

        switch (sourceCell.getCellType()) {
            case STRING:
                destinationCell.setCellValue(sourceCell.getStringCellValue());
                break;
            case NUMERIC:
                destinationCell.setCellValue(sourceCell.getNumericCellValue());
                break;
            case BOOLEAN:
                destinationCell.setCellValue(sourceCell.getBooleanCellValue());
                break;
            case FORMULA:
                destinationCell.setCellFormula(sourceCell.getCellFormula());
                break;
            case BLANK:
                destinationCell.setBlank();
                break;
            default:
                break;
        }
    }
}