The java class used for merging the 2 workbook

mergeCode.java (text/plain), 20.28 KB, created by Lucas on 2018-08-16 10:34:22 UTC

(hide)

Description: The java class used for merging the 2 workbook

Filename:

MIME Type:

Creator: Lucas

Created: 2018-08-16 10:34:22 UTC

Size: 20.28 KB

patch

obsolete

>package com.aqm.utils; > >import java.io.ByteArrayInputStream; >import java.io.ByteArrayOutputStream; >import java.io.File; >import java.io.FileInputStream; >import java.io.FileOutputStream; >import java.io.IOException; >import java.util.ArrayList; >import java.util.HashMap; >import java.util.List; >import java.util.Map; > >import org.apache.commons.io.IOUtils; >import org.apache.poi.ss.usermodel.CreationHelper; >import org.apache.poi.ss.usermodel.DataFormat; >import org.apache.poi.ss.usermodel.Drawing; >import org.apache.poi.ss.usermodel.Footer; >import org.apache.poi.ss.usermodel.Header; >import org.apache.poi.ss.usermodel.Name; >import org.apache.poi.ss.usermodel.PrintSetup; >import org.apache.poi.ss.usermodel.Sheet; >import org.apache.poi.ss.usermodel.Workbook; >import org.apache.poi.ss.util.CellRangeAddress; >import org.apache.poi.ss.util.CellReference; >import org.apache.poi.xssf.usermodel.XSSFCell; >import org.apache.poi.xssf.usermodel.XSSFCellStyle; >import org.apache.poi.xssf.usermodel.XSSFClientAnchor; >import org.apache.poi.xssf.usermodel.XSSFDrawing; >import org.apache.poi.xssf.usermodel.XSSFName; >import org.apache.poi.xssf.usermodel.XSSFPicture; >import org.apache.poi.xssf.usermodel.XSSFPictureData; >import org.apache.poi.xssf.usermodel.XSSFRow; >import org.apache.poi.xssf.usermodel.XSSFShape; >import org.apache.poi.xssf.usermodel.XSSFSheet; >import org.apache.poi.xssf.usermodel.XSSFWorkbook; >import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; >import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTTwoCellAnchor; > > >/** > * This class is to append an excel sheets to another excel, only support XSSF file. > * Please refer to http://www.coderanch.com/t/420958/open-source/Copying-sheet-excel-file-excel > */ >public class ExcelCombine { > > public static void main(String[] args) throws Exception { > > String sheetToMerge = "sheetPath\\sheetName"; > String originWorkbook = "originWorkbookPath\\originWorkbookName"; > String targetWorkbook = "expectedPath\\expectedName"; > combineExcel(new File(targetWorkbook), originWorkbook, sheetToMerge); > > } > > > @SuppressWarnings("unused") > private static class FormulaInfo { > > private String sheetName; > private Integer rowIndex; > private Integer cellIndex; > private String formula; > > private FormulaInfo(String sheetName, Integer rowIndex, Integer cellIndex, String formula) { > this.sheetName = sheetName; > this.rowIndex = rowIndex; > this.cellIndex = cellIndex; > this.formula = formula; > } > > public String getSheetName() {return sheetName;} > public void setSheetName(String sheetName) {this.sheetName = sheetName;} > > public Integer getRowIndex() {return rowIndex;} > public void setRowIndex(Integer rowIndex) {this.rowIndex = rowIndex;} > > public Integer getCellIndex() {return cellIndex;} > public void setCellIndex(Integer cellIndex) {this.cellIndex = cellIndex;} > > public String getFormula() {return formula;} > public void setFormula(String formula) {this.formula = formula;} > } > > static List<FormulaInfo> formulaInfoList = new ArrayList<FormulaInfo>(); > > public static void refreshFormula(XSSFWorkbook workbook) { > try { > for (FormulaInfo formulaInfo : formulaInfoList) { > workbook.getSheet(formulaInfo.getSheetName()).getRow(formulaInfo.getRowIndex()) > .getCell(formulaInfo.getCellIndex()).setCellFormula(formulaInfo.getFormula()); > } > } finally { > formulaInfoList.removeAll(formulaInfoList); > } > } > > public static byte[] combineExcel(byte[] fileBytes, byte[] pendingFileBytes) throws IOException{ > > XSSFWorkbook book = new XSSFWorkbook(new ByteArrayInputStream(fileBytes)); > XSSFWorkbook b = new XSSFWorkbook(new ByteArrayInputStream(pendingFileBytes)); > for (XSSFPictureData p : b.getAllPictures()) { > book.addPicture(p.getData(), p.getPictureType()); > } > for (int i = 0; i < b.getNumberOfSheets(); i++) { > XSSFSheet sheet = book.createSheet(b.getSheetName(i)); > copySheets(book, sheet, b.getSheetAt(i)); > String printArea = b.getPrintArea(i); > if (printArea != null){ > book.setPrintArea(book.getSheetIndex(sheet), printArea.split("!")[1]); > } > System.out.println("===> combine excel print area:" + b.getSheetName(i) + ":" + b.getPrintArea(i)); > } > ByteArrayOutputStream os = new ByteArrayOutputStream(); > book.write(os); > return os.toByteArray(); > } > > protected byte[] toByteArray(Workbook wb) { > ByteArrayOutputStream os = new ByteArrayOutputStream(); > try { > wb.write(os); > return os.toByteArray(); > } catch (IOException e) { > throw new RuntimeException("failed to write excel file."); > }finally { > IOUtils.closeQuietly(os); > } > } > > public static void combineExcel(File dest, String file, String pendingFile) throws Exception { > XSSFWorkbook book = new XSSFWorkbook(new FileInputStream(file)); > XSSFWorkbook b = new XSSFWorkbook(new FileInputStream(pendingFile)); > for (XSSFPictureData p : b.getAllPictures()) { > book.addPicture(p.getData(), p.getPictureType()); > } > for (int i = 0; i < b.getNumberOfSheets(); i++) { > XSSFSheet sheet = book.createSheet(b.getSheetName(i)); > //this is import for print pack > String printArea = b.getPrintArea(i); > if (printArea != null){ > book.setPrintArea(book.getSheetIndex(sheet), printArea.split("!")[1]); > } > copySheets(book, sheet, b.getSheetAt(i)); > } > > FileOutputStream out = new FileOutputStream(dest); > book.write(out); > out.close(); > } > > private static void copySheets(XSSFWorkbook newWorkbook, > XSSFSheet newSheet, XSSFSheet sheet) { > newSheet.enableLocking(); > copySheetSettings(newSheet, sheet); > copyPictures(newSheet, sheet); > copySheets(newWorkbook, newSheet, sheet, true); > } > > private static void copySheets(XSSFWorkbook newWorkbook, > XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle) { > int maxColumnNum = 0; > Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() > : null; > int firstRowNum = sheet.getFirstRowNum(); > int lastRowNum = sheet.getLastRowNum(); > for (int i = firstRowNum; i <= lastRowNum; i++) { > XSSFRow srcRow = sheet.getRow(i); > if (srcRow != null) { > XSSFRow destRow = newSheet.createRow(i); > copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap); > if (srcRow.getLastCellNum() > maxColumnNum) { > maxColumnNum = srcRow.getLastCellNum(); > } > } > } > // set column width > for (int i = 0; i <= maxColumnNum; i++) { > newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); > } > // set merge region > for (int i = 0; i < sheet.getNumMergedRegions(); i++) { > newSheet.addMergedRegion(sheet.getMergedRegion(i)); > } > for(int i=0; i<sheet.getDataValidations().size(); i++){ > newSheet.addValidationData(sheet.getDataValidations().get(i)); > } > > } > > private static void copyRow(XSSFWorkbook newWorkbook, XSSFSheet srcSheet, > XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, > Map<Integer, XSSFCellStyle> styleMap) { > destRow.setHeight(srcRow.getHeight()); > for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { > if (j == -1) { > continue; > } > XSSFCell oldCell = srcRow.getCell(j); > if (oldCell != null) { > XSSFCell newCell = destRow.createCell(j); > copyCell(newWorkbook, oldCell, newCell, styleMap); > } > } > } > > private static void copyCell(XSSFWorkbook newWorkbook, XSSFCell oldCell, > XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) { > if (styleMap != null) { > int stHashCode = oldCell.getCellStyle().hashCode(); > XSSFCellStyle newCellStyle = styleMap.get(stHashCode); > if (newCellStyle == null) { > newCellStyle = newWorkbook.createCellStyle(); > newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); > > newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottomEnum()); > newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeftEnum()); > newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRightEnum()); > newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTopEnum()); > > newCellStyle.setBottomBorderColor(oldCell.getCellStyle().getBottomBorderXSSFColor()); > newCellStyle.setLeftBorderColor(oldCell.getCellStyle().getLeftBorderXSSFColor()); > newCellStyle.setTopBorderColor(oldCell.getCellStyle().getTopBorderXSSFColor()); > newCellStyle.setRightBorderColor(oldCell.getCellStyle().getRightBorderXSSFColor()); > > newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPatternEnum()); > > DataFormat newDataFormat = newCell.getSheet().getWorkbook().createDataFormat(); > short newFormat = newDataFormat.getFormat(oldCell.getCellStyle().getDataFormatString()); > newCellStyle.setDataFormat(newFormat); > newCellStyle.setAlignment(oldCell.getCellStyle().getAlignmentEnum()); > newCellStyle.setVerticalAlignment(oldCell.getCellStyle().getVerticalAlignmentEnum()); > newCellStyle.setHidden(oldCell.getCellStyle().getHidden()); > newCellStyle.setLocked(oldCell.getCellStyle().getLocked()); > newCellStyle.setWrapText(oldCell.getCellStyle().getWrapText()); > newCellStyle.setBorderBottom(oldCell.getCellStyle().getBorderBottomEnum()); > newCellStyle.setBorderLeft(oldCell.getCellStyle().getBorderLeftEnum()); > newCellStyle.setBorderRight(oldCell.getCellStyle().getBorderRightEnum()); > newCellStyle.setBorderTop(oldCell.getCellStyle().getBorderTopEnum()); > newCellStyle.setFillPattern(oldCell.getCellStyle().getFillPatternEnum()); > newCellStyle.setIndention(oldCell.getCellStyle().getIndention()); > newCellStyle.setRotation(oldCell.getCellStyle().getRotation()); > > try { > newCellStyle.setFillForegroundColor(oldCell.getCellStyle().getFillForegroundColorColor()); > newCellStyle.setFillBackgroundColor(oldCell.getCellStyle().getFillBackgroundColorColor()); > } catch (Exception e) { > > } > > styleMap.put(stHashCode, newCellStyle); > } > newCell.setCellStyle(newCellStyle); > } > > newCell.setCellComment(oldCell.getCellComment()); > newCell.setCellType(oldCell.getCellType()); > switch (oldCell.getCellType()) { > case XSSFCell.CELL_TYPE_STRING: > newCell.setCellValue(oldCell.getRichStringCellValue()); > break; > case XSSFCell.CELL_TYPE_NUMERIC: > newCell.setCellValue(oldCell.getNumericCellValue()); > break; > case XSSFCell.CELL_TYPE_BLANK: > newCell.setCellType(XSSFCell.CELL_TYPE_BLANK); > break; > case XSSFCell.CELL_TYPE_BOOLEAN: > newCell.setCellValue(oldCell.getBooleanCellValue()); > break; > case XSSFCell.CELL_TYPE_ERROR: > newCell.setCellErrorValue(oldCell.getErrorCellValue()); > break; > case XSSFCell.CELL_TYPE_FORMULA: > newCell.setCellFormula(oldCell.getCellFormula()); > formulaInfoList.add(new FormulaInfo(oldCell.getSheet().getSheetName(), oldCell.getRowIndex(), oldCell > .getColumnIndex(), oldCell.getCellFormula())); > break; > default: > break; > } > } > > private static void copyPictures(Sheet newSheet, Sheet sheet) { > Drawing drawingOld = sheet.createDrawingPatriarch(); > Drawing drawingNew = newSheet.createDrawingPatriarch(); > CreationHelper helper = newSheet.getWorkbook().getCreationHelper(); > if (drawingNew instanceof XSSFDrawing) { > List<XSSFShape> shapes = ((XSSFDrawing) drawingOld).getShapes(); > for (int i = 0; i < shapes.size(); i++) { > if (shapes.get(i) instanceof XSSFPicture) { > XSSFPicture pic = (XSSFPicture) shapes.get(i); > XSSFPictureData picdata = pic.getPictureData(); > int pictureIndex = newSheet.getWorkbook().addPicture(picdata.getData(), picdata.getPictureType()); > XSSFClientAnchor anchor = null; > CTTwoCellAnchor oldAnchor = ((XSSFDrawing) drawingOld).getCTDrawing().getTwoCellAnchorArray(i); > if (oldAnchor != null) { > anchor = (XSSFClientAnchor) helper.createClientAnchor(); > CTMarker markerFrom = oldAnchor.getFrom(); > CTMarker markerTo = oldAnchor.getTo(); > anchor.setDx1((int) markerFrom.getColOff()); > anchor.setDx2((int) markerTo.getColOff()); > anchor.setDy1((int) markerFrom.getRowOff()); > anchor.setDy2((int) markerTo.getRowOff()); > anchor.setCol1(markerFrom.getCol()); > anchor.setCol2(markerTo.getCol()); > anchor.setRow1(markerFrom.getRow()); > anchor.setRow2(markerTo.getRow()); > } > drawingNew.createPicture(anchor, pictureIndex); > } > } > } > } > > > private static void copySheetSettings(Sheet newSheet, Sheet sheetToCopy) { > > newSheet.setAutobreaks(sheetToCopy.getAutobreaks()); > newSheet.setDefaultColumnWidth(sheetToCopy.getDefaultColumnWidth()); > newSheet.setDefaultRowHeight(sheetToCopy.getDefaultRowHeight()); > newSheet.setDefaultRowHeightInPoints(sheetToCopy.getDefaultRowHeightInPoints()); > newSheet.setDisplayGuts(sheetToCopy.getDisplayGuts()); > newSheet.setFitToPage(sheetToCopy.getFitToPage()); > > newSheet.setForceFormulaRecalculation(sheetToCopy.getForceFormulaRecalculation()); > > PrintSetup sheetToCopyPrintSetup = sheetToCopy.getPrintSetup(); > PrintSetup newSheetPrintSetup = newSheet.getPrintSetup(); > > newSheetPrintSetup.setPaperSize(sheetToCopyPrintSetup.getPaperSize()); > newSheetPrintSetup.setScale(sheetToCopyPrintSetup.getScale()); > newSheetPrintSetup.setPageStart(sheetToCopyPrintSetup.getPageStart()); > newSheetPrintSetup.setFitWidth(sheetToCopyPrintSetup.getFitWidth()); > newSheetPrintSetup.setFitHeight(sheetToCopyPrintSetup.getFitHeight()); > newSheetPrintSetup.setLeftToRight(sheetToCopyPrintSetup.getLeftToRight()); > newSheetPrintSetup.setLandscape(sheetToCopyPrintSetup.getLandscape()); > newSheetPrintSetup.setValidSettings(sheetToCopyPrintSetup.getValidSettings()); > newSheetPrintSetup.setNoColor(sheetToCopyPrintSetup.getNoColor()); > newSheetPrintSetup.setDraft(sheetToCopyPrintSetup.getDraft()); > newSheetPrintSetup.setNotes(sheetToCopyPrintSetup.getNotes()); > newSheetPrintSetup.setNoOrientation(sheetToCopyPrintSetup.getNoOrientation()); > newSheetPrintSetup.setUsePage(sheetToCopyPrintSetup.getUsePage()); > newSheetPrintSetup.setHResolution(sheetToCopyPrintSetup.getHResolution()); > newSheetPrintSetup.setVResolution(sheetToCopyPrintSetup.getVResolution()); > newSheetPrintSetup.setHeaderMargin(sheetToCopyPrintSetup.getHeaderMargin()); > newSheetPrintSetup.setFooterMargin(sheetToCopyPrintSetup.getFooterMargin()); > newSheetPrintSetup.setCopies(sheetToCopyPrintSetup.getCopies()); > > Header sheetToCopyHeader = sheetToCopy.getHeader(); > Header newSheetHeader = newSheet.getHeader(); > newSheetHeader.setCenter(sheetToCopyHeader.getCenter()); > newSheetHeader.setLeft(sheetToCopyHeader.getLeft()); > newSheetHeader.setRight(sheetToCopyHeader.getRight()); > > Footer sheetToCopyFooter = sheetToCopy.getFooter(); > Footer newSheetFooter = newSheet.getFooter(); > newSheetFooter.setCenter(sheetToCopyFooter.getCenter()); > newSheetFooter.setLeft(sheetToCopyFooter.getLeft()); > newSheetFooter.setRight(sheetToCopyFooter.getRight()); > > newSheet.setHorizontallyCenter(sheetToCopy.getHorizontallyCenter()); > newSheet.setMargin(Sheet.LeftMargin, sheetToCopy.getMargin(Sheet.LeftMargin)); > newSheet.setMargin(Sheet.RightMargin, sheetToCopy.getMargin(Sheet.RightMargin)); > newSheet.setMargin(Sheet.TopMargin, sheetToCopy.getMargin(Sheet.TopMargin)); > newSheet.setMargin(Sheet.BottomMargin, sheetToCopy.getMargin(Sheet.BottomMargin)); > > newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines()); > newSheet.setRowSumsBelow(sheetToCopy.getRowSumsBelow()); > newSheet.setRowSumsRight(sheetToCopy.getRowSumsRight()); > newSheet.setVerticallyCenter(sheetToCopy.getVerticallyCenter()); > newSheet.setDisplayFormulas(sheetToCopy.isDisplayFormulas()); > newSheet.setDisplayGridlines(sheetToCopy.isDisplayGridlines()); > newSheet.setDisplayRowColHeadings(sheetToCopy.isDisplayRowColHeadings()); > newSheet.setDisplayZeros(sheetToCopy.isDisplayZeros()); > newSheet.setPrintGridlines(sheetToCopy.isPrintGridlines()); > newSheet.setRightToLeft(sheetToCopy.isRightToLeft()); > newSheet.setZoom(100); > copyPrintTitle(newSheet, sheetToCopy); > } > > private static void copyPrintTitle(Sheet newSheet, Sheet sheetToCopy) { > int nbNames = sheetToCopy.getWorkbook().getNumberOfNames(); > Name name = null; > String formula = null; > > String part1S = null; > String part2S = null; > String formS = null; > String formF = null; > String part1F = null; > String part2F = null; > int rowB = -1; > int rowE = -1; > int colB = -1; > int colE = -1; > > for (int i = 0; i < nbNames; i++) { > name = sheetToCopy.getWorkbook().getNameAt(i); > if (name.getSheetIndex() == sheetToCopy.getWorkbook().getSheetIndex(sheetToCopy)) { > if (name.getNameName().equals("Print_Titles") > || name.getNameName().equals(XSSFName.BUILTIN_PRINT_TITLE)) { > formula = name.getRefersToFormula(); > int indexComma = formula.indexOf(","); > if (indexComma == -1) { > indexComma = formula.indexOf(";"); > } > String firstPart = null; > String secondPart = null; > if (indexComma == -1) { > firstPart = formula; > } else { > firstPart = formula.substring(0, indexComma); > secondPart = formula.substring(indexComma + 1); > } > > formF = firstPart.substring(firstPart.indexOf("!") + 1); > part1F = formF.substring(0, formF.indexOf(":")); > part2F = formF.substring(formF.indexOf(":") + 1); > > if (secondPart != null) { > formS = secondPart.substring(secondPart.indexOf("!") + 1); > part1S = formS.substring(0, formS.indexOf(":")); > part2S = formS.substring(formS.indexOf(":") + 1); > } > > rowB = -1; > rowE = -1; > colB = -1; > colE = -1; > String rowBs, rowEs, colBs, colEs; > if (part1F.lastIndexOf("$") != part1F.indexOf("$")) { > rowBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length()); > rowEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length()); > rowB = Integer.parseInt(rowBs); > rowE = Integer.parseInt(rowEs); > if (secondPart != null) { > colBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length()); > colEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length()); > colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs); > colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs); > } > } else { > colBs = part1F.substring(part1F.lastIndexOf("$") + 1, part1F.length()); > colEs = part2F.substring(part2F.lastIndexOf("$") + 1, part2F.length()); > colB = CellReference.convertColStringToIndex(colBs);// CExportExcelHelperPoi.convertColumnLetterToInt(colBs); > colE = CellReference.convertColStringToIndex(colEs);// CExportExcelHelperPoi.convertColumnLetterToInt(colEs); > > if (secondPart != null) { > rowBs = part1S.substring(part1S.lastIndexOf("$") + 1, part1S.length()); > rowEs = part2S.substring(part2S.lastIndexOf("$") + 1, part2S.length()); > rowB = Integer.parseInt(rowBs); > rowE = Integer.parseInt(rowEs); > } > } > newSheet.setRepeatingRows(new CellRangeAddress(rowB - 1, rowE - 1, colB, colE)); > } > } > } > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]