最近在处理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;
}
测试代码文章来源:https://www.toymoban.com/news/detail-820016.html
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工具类文章来源地址https://www.toymoban.com/news/detail-820016.html
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();
}
到了这里,关于Java Excel分割成许多小文件的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!