Java中使用POI进行excel的合并

Java中使用POI进行excel的合并"/>

Java中使用POI进行excel的合并

public class Utils {private static final int XLS_MAX_ROWS = 65535;private static final int XLSX_MAX_ROWS = 1048575;/*** 合并多个 Excel 文件* * @param mergedFile 合并后的文件* @param files      待合并的文件* @param isXlsx     合并文件类型是否是 xlsx* @throws IOException       合并异常*/public static void mergeExcel(String tempPath,File mergedFile, List<Long> files, boolean isXlsx) throws IOException {if (mergedFile == null || files == null) {return;}try(SXSSFWorkbook mergedWorkbook = new SXSSFWorkbook(100);FileOutputStream out = new FileOutputStream(mergedFile)){SXSSFSheet newSheet;Sheet sheetAt;for (Long file : files) {int start = 0;if (ObjectUtils.isEmpty(file)) {continue;}try (XSSFWorkbook oldWorkbook1 =  new XSSFWorkbook(new FileInputStream(tempPath+File.separator+file+".xlsx"));SXSSFWorkbook oldWorkbook = new SXSSFWorkbook(oldWorkbook1)) {newSheet =  mergedWorkbook.createSheet(oldWorkbook.getSheetName(0));int oldSheetSize = oldWorkbook.getXSSFWorkbook().getNumberOfSheets();
//					sheetAt = oldWorkbook.getSheetAt(0);sheetAt = oldWorkbook.getXSSFWorkbook().getSheetAt(0);mergeSheetAllRegion2(sheetAt,newSheet);Sheet oldSheet;Row oldRow;SXSSFRow newRow;for (int i = 0; i < oldSheetSize; i++) {oldSheet = oldWorkbook.getXSSFWorkbook().getSheetAt(i);int oldRowSize = oldSheet.getLastRowNum();for (int j = 0; j <= oldRowSize; j++) {if (start == (isXlsx ? XLSX_MAX_ROWS : XLS_MAX_ROWS)) {start = newSheet.getLastRowNum();}oldRow = oldSheet.getRow(j);if(ObjectUtils.isEmpty(oldRow)) continue;for (int k = 0; k < oldRow.getLastCellNum(); k++) {newSheet.setColumnWidth(k, oldSheet.getColumnWidth(k));}newRow = newSheet.createRow(start);copyRow(mergedWorkbook,oldWorkbook,oldRow, newRow);start++;}}newSheet.flushRows();} catch (Exception e) {e.printStackTrace();}}mergedWorkbook.write(out);out.flush();} catch (Exception e ){e.printStackTrace();}}private static void copyRow(Workbook workbookNew,SXSSFWorkbook workbook,Row oldRow, Row newRow) {if (ObjectUtils.isEmpty(oldRow)){return;}int rowNum =0;try {rowNum = oldRow.getRowNum();newRow.setZeroHeight(oldRow.getZeroHeight());if (1 == rowNum || 0==rowNum){newRow.setHeight((short)1000);} else {newRow.setHeight(oldRow.getHeight());}} catch (Exception e) {e.printStackTrace();}for (int i = oldRow.getFirstCellNum(); i <= oldRow.getLastCellNum(); i++) {Cell oldCell = oldRow.getCell(i);if(ObjectUtils.isEmpty(oldCell)) continue;SXSSFCell cell = (SXSSFCell)newRow.createCell(i);if (oldCell.getSheet().isColumnHidden(oldCell.getColumnIndex())) {cell.getSheet().setColumnHidden(oldCell.getColumnIndex(),true);}copyCell(workbookNew,workbook, oldCell, cell);}}private static void copyCell(Workbook workbookNew,SXSSFWorkbook workbook,Cell oldCell, Cell newCell) {CellStyle cellStyleAt = workbookNew.createCellStyle();short format = workbookNew.createDataFormat().getFormat("0.00000");CellStyle cellStyle = oldCell.getCellStyle();short dataFormat = cellStyle.getDataFormat();String dataFormatString = cellStyle.getDataFormatString();System.out.println("dataFormatString-----"+dataFormatString);DataFormat dataFormat1 = workbookNew.createDataFormat();System.out.println(dataFormat);// 复制单元格样式
//		cellStyleAt.cloneStyleFrom(oldCell.getCellStyle());Font fontAt = workbook.getXSSFWorkbook().getFontAt(oldCell.getCellStyle().getFontIndexAsInt());Font font = workbookNew.createFont();font.setBold(fontAt.getBold());font.setFontHeightInPoints(fontAt.getFontHeightInPoints());cellStyleAt.setFont(font);cellStyleAt.setAlignment(oldCell.getCellStyle().getAlignment());cellStyleAt.setVerticalAlignment(oldCell.getCellStyle().getVerticalAlignment());if (dataFormatString.startsWith("0")){String substring = dataFormatString;short format1 = dataFormat1.getFormat(substring);System.out.println(substring);cellStyleAt.setDataFormat(format1);}if (oldCell.getCellComment() != null) {newCell.setCellComment(oldCell.getCellComment());}// 不同数据类型处理CellType tmpCellType = oldCell.getCellType();if(tmpCellType != CellType.FORMULA){newCell.setCellType(tmpCellType);}switch (oldCell.getCellTypeEnum()) {case FORMULA:if (oldCell.getCellFormula().startsWith("DATEVALUE")){newCell.setCellFormula("=E2");}else {newCell.setCellFormula(oldCell.getCellFormula());}//				cellStyleAt.setDataFormat(format);break;case NUMERIC:if (DateUtil.isCellDateFormatted(oldCell)) {newCell.setCellValue(cn.hutool.core.date.DateUtil.formatDate(oldCell.getDateCellValue()));newCell.setCellType(CellType.NUMERIC);} else {newCell.setCellValue(oldCell.getNumericCellValue());newCell.setCellType(CellType.NUMERIC);}
//				cellStyleAt.setDataFormat(format);break;case BLANK:newCell.setCellValue(oldCell.getStringCellValue());break;case BOOLEAN:newCell.setCellValue(oldCell.getBooleanCellValue());break;case STRING:String stringCellValue = oldCell.getStringCellValue();if (!ObjectUtils.isEmpty(stringCellValue) && isInteger(stringCellValue)){
//					cellStyleAt.setDataFormat(format);newCell.setCellValue(Float.parseFloat(stringCellValue));newCell.setCellType(CellType.NUMERIC);} else {newCell.setCellValue(stringCellValue);}break;default:break;}// 单元格样式newCell.setCellStyle(cellStyleAt);}public static boolean isInteger(String str) {Pattern pattern = Patternpile("[0-9]*\\.?[0-9]+");return pattern.matcher(str).matches();}public static void mergeSheetAllRegion2(Sheet tmpSheet, Sheet newExcelSheet) {int num = tmpSheet.getNumMergedRegions();CellRangeAddress cellRange = null;for (int i = 0; i < num; i++) {cellRange = tmpSheet.getMergedRegion(i);newExcelSheet.addMergedRegion(cellRange);}}}

多个excel合并为一个excel多sheet