云南省网站开发公司深圳市住房和建设局局长级别

最近在处理excel，数据很多，需要将excel拆分成许多小块，并保留原来的格式，于是写了该算法，并能保留原来的样式，使用很简单：

Sheet splitSheet = ExcelUtil.split(sheet, 0, 20, 5, 8);

传入开始行、结束行、开始列、结束列即可

    public static Sheet split(Sheet sheet, int startRow, int endRow, int startCol, int endCol) {Workbook workbook = new SXSSFWorkbook();Sheet newSheet = workbook.createSheet("Sheet1");for (int i = startRow; i <= endRow; i++) {Row tableDataRow = sheet.getRow(i);Row newRow = newSheet.createRow(i - startRow);if (tableDataRow == null) {continue;}for (int j = startCol; j <= endCol; j++) {Cell cell = tableDataRow.getCell(j);Cell newCell = newRow.createCell(j - startCol);CellStyle cellStyle = workbook.createCellStyle();cellStyle.cloneStyleFrom(cell.getCellStyle());newCell.setCellStyle(cellStyle);newCell.setCellValue(cell.getStringCellValue());}}for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) {int firstRow = mergedRegion.getFirstRow();int lastRow = mergedRegion.getLastRow();int firstColumn = mergedRegion.getFirstColumn();int lastColumn = mergedRegion.getLastColumn();if (firstRow >= startRow && lastRow <= endRow && firstColumn >= startCol && lastColumn <= endCol) {CellRangeAddress cellAddresses = new CellRangeAddress(firstRow - startRow, lastRow - startRow, firstColumn - startCol, lastColumn - startCol);newSheet.addMergedRegion(cellAddresses);}}return newSheet;}

测试代码

    public static void main(String[] args) throws Exception {String path = "xxx.xlsx";String targetPath = "xxx1.xlsx";FileInputStream fis = null;File file = new File(path);try {fis = new FileInputStream(file);Workbook workbook = WorkbookFactory.create(fis);Sheet sheet = workbook.getSheetAt(0);Sheet splitSheet = ExcelUtil.split(sheet, 0, 20, 5, 8);ExcelUtil.saveSheet(targetPath,splitSheet);} catch (FileNotFoundException e) {throw new RuntimeException(e);} catch (IOException e) {throw new RuntimeException(e);}}

保存sheet工具类

    public static void saveSheet(String path, Sheet sheet) throws IOException {File file = new File(path);FileOutputStream fos = new FileOutputStream(file);sheet.getWorkbook().write(fos);fos.close();sheet.getWorkbook().close();}