首页 > 其他分享 >使用hutool poi 拷贝sheet到新excel文件中

使用hutool poi 拷贝sheet到新excel文件中

时间:2022-10-10 19:14:39浏览次数:52  
标签:oldCell case sheet int excel hutool oldSheet newCell newSheet

public class CopySheetToFileTest {

    public static void main(String[] args) {
        File file = new File(CopySheetToFileTest.class.getResource("/bx.xlsx").getFile());
        String sheetName = "表4 人身险公司主要财务与风险指标(月度)";
        ExcelReader excelReader = ExcelUtil.getReader(file, sheetName);
        Sheet oldSheet = excelReader.getSheet();
        excelReader.close();

        File outputFile = new File("C:\\Users\\hdwang\\Desktop\\bx_test.xlsx");
        ExcelWriter excelWriter = new ExcelWriter(outputFile, sheetName);
        Sheet newSheet = excelWriter.getSheet();
        copySheet(excelWriter.getWorkbook(), newSheet, oldSheet);
        excelWriter.close();

    }

    /**
     * 复制sheet到另一个excel文件中
     *
     * @param newWorkbook 新文件工作簿
     * @param newSheet    新文件sheet
     * @param oldSheet    老文件sheet
     */
    private static void copySheet(Workbook newWorkbook, Sheet newSheet, Sheet oldSheet) {

        //合并单元格
        int numMergedRegions = oldSheet.getNumMergedRegions();
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress mergedRegion = oldSheet.getMergedRegion(i);
            newSheet.addMergedRegion(mergedRegion);
        }
        //增加列宽
        int physicalNumberOfCells = oldSheet.getRow(0).getPhysicalNumberOfCells();
        for (int i = 0; i < physicalNumberOfCells; i++) {
            newSheet.setColumnWidth(i, 256 * 20);
        }

        //最大获取行数
        int maxRowSize = oldSheet.getPhysicalNumberOfRows();
        for (int i = 0; i < maxRowSize; i++) {
            Row newRow = newSheet.createRow(i);
            Row oldRow = oldSheet.getRow(i);


            //获取当前行,最大列数
            int maxColSize = oldRow.getPhysicalNumberOfCells();
            for (int j = 0; j < maxColSize; j++) {
                Cell newCell = newRow.createCell(j);
                Cell oldCell = oldRow.getCell(j);
                if (oldCell == null) {
                    continue;
                }
                CellType cellType = oldCell.getCellType();
                switch (cellType) {
                    case NUMERIC:
                        newCell.setCellValue(oldCell.getNumericCellValue());
                        break;
                    case STRING:
                        newCell.setCellValue(oldCell.getStringCellValue());
                        break;
                    case BLANK:
                        break;
                    case ERROR:
                        newCell.setCellValue(oldCell.getErrorCellValue());
                        break;
                    case BOOLEAN:
                        newCell.setCellValue(oldCell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        newCell.setCellFormula(oldCell.getCellFormula());
                        break;
                }

                //直接copy原cell的样式,定义在遍历行的地方,定义在外面可能出现样式渲染错误
                CellStyle cellStyle = newWorkbook.createCellStyle();
                cellStyle.cloneStyleFrom(oldCell.getCellStyle());
                newCell.setCellStyle(cellStyle);
            }
        }
    }

}

 

标签:oldCell,case,sheet,int,excel,hutool,oldSheet,newCell,newSheet
From: https://www.cnblogs.com/hdwang/p/16776828.html

相关文章