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