江宁区住房与城乡建设局网站,列举网站建设的SEO策略,wordpress做网址导航,网站评论做外链最近在处理excel#xff0c;数据很多#xff0c;需要将excel拆分成许多小块#xff0c;并保留原来的格式#xff0c;于是写了该算法#xff0c;并能保留原来的样式#xff0c;使用很简单#xff1a;
Sheet splitSheet ExcelUtil.split(sheet, 0, 20, 5, 8);
传入开始…最近在处理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();}
