/** * Import the model according to reversed dependency order among model objects: book, sheet, * defined name, cells, chart, pictures, validation. */ @Override public SBook imports(InputStream is, String bookName) throws IOException { // clear cache for reuse importedStyle.clear(); importedFont.clear(); workbook = createPoiBook(is); book = SBooks.createBook(bookName); // book.setDefaultCellStyle(importCellStyle(workbook.getCellStyleAt((short) 0), false)); // //ZSS-780 // ZSS-854 importDefaultCellStyles(); importNamedStyles(); // ZSS-1140 importExtraStyles(); setBookType(book); // ZSS-715: Enforce internal Locale.US Locale so formula is in consistent internal format Locale old = Locales.setThreadLocal(Locale.US); SBookSeries bookSeries = book.getBookSeries(); boolean isCacheClean = bookSeries.isAutoFormulaCacheClean(); try { bookSeries.setAutoFormulaCacheClean(false); // disable it to avoid // unnecessary clean up // during importing importExternalBookLinks(); int numberOfSheet = workbook.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { Sheet poiSheet = workbook.getSheetAt(i); importSheet(poiSheet, i); SSheet sheet = book.getSheet(i); importTables(poiSheet, sheet); // ZSS-855, ZSS-1011 } importNamedRange(); for (int i = 0; i < numberOfSheet; i++) { SSheet sheet = book.getSheet(i); Sheet poiSheet = workbook.getSheetAt(i); for (Row poiRow : poiSheet) { importRow(poiRow, sheet); } importColumn(poiSheet, sheet); importMergedRegions(poiSheet, sheet); importDrawings(poiSheet, sheet); importValidation(poiSheet, sheet); importAutoFilter(poiSheet, sheet); importSheetProtection(poiSheet, sheet); // ZSS-576 } } finally { book.getBookSeries().setAutoFormulaCacheClean(isCacheClean); Locales.setThreadLocal(old); } return book; }
// ZSS-854 protected void importDefaultCellStyles() { ((AbstractBookAdv) book).clearDefaultCellStyles(); for (CellStyle poiStyle : workbook.getDefaultCellStyles()) { book.addDefaultCellStyle(importCellStyle(poiStyle, false)); } // in case of XLS files which we have not support defaultCellStyles if (book.getDefaultCellStyles().isEmpty()) { ((AbstractBookAdv) book).initDefaultCellStyles(); } // ZSS-1132: setup default font ((AbstractBookAdv) book).initDefaultFont(); }
protected SFont createZssFont(Font poiFont) { SFont font = book.createFont(true); // font font.setName(poiFont.getFontName()); font.setBoldweight(PoiEnumConversion.toBoldweight(poiFont.getBoldweight())); font.setItalic(poiFont.getItalic()); font.setStrikeout(poiFont.getStrikeout()); font.setUnderline(PoiEnumConversion.toUnderline(poiFont.getUnderline())); font.setHeightPoints(poiFont.getFontHeightInPoints()); font.setTypeOffset(PoiEnumConversion.toTypeOffset(poiFont.getTypeOffset())); font.setColor(book.createColor(BookHelper.getFontHTMLColor(workbook, poiFont))); return font; }
/** * Name should be created after sheets created. A special defined name, _xlnm._FilterDatabase * (xlsx) or _FilterDatabase (xls), stores the selected cells for auto-filter */ protected void importNamedRange() { for (int i = 0; i < workbook.getNumberOfNames(); i++) { Name definedName = workbook.getNameAt(i); if (skipName(definedName)) { continue; } SName namedRange = null; if (definedName.getSheetIndex() == -1) { // workbook scope namedRange = book.createName(definedName.getNameName()); } else { namedRange = book.createName(definedName.getNameName(), definedName.getSheetName()); } namedRange.setRefersToFormula(definedName.getRefersToFormula()); } }
// ZSS-854 protected void importNamedStyles() { ((AbstractBookAdv) book).clearNamedStyles(); for (NamedStyle poiStyle : workbook.getNamedStyles()) { SNamedStyle namedStyle = new NamedStyleImpl( poiStyle.getName(), poiStyle.isCustomBuiltin(), poiStyle.getBuiltinId(), book, poiStyle.getIndex()); book.addNamedCellstyle(namedStyle); } }
protected SCellStyle importCellStyle(CellStyle poiCellStyle, boolean inStyleTable) { SCellStyle cellStyle = null; // short idx = poiCellStyle.getIndex(); // ZSS-685 if ((cellStyle = importedStyle.get(poiCellStyle)) == null) { // ZSS-685 cellStyle = book.createCellStyle(inStyleTable); importedStyle.put(poiCellStyle, cellStyle); // ZSS-685 String dataFormat = poiCellStyle.getRawDataFormatString(); if (dataFormat == null) { // just in case dataFormat = SCellStyle.FORMAT_GENERAL; } if (!poiCellStyle.isBuiltinDataFormat()) { cellStyle.setDirectDataFormat(dataFormat); } else { cellStyle.setDataFormat(dataFormat); } cellStyle.setWrapText(poiCellStyle.getWrapText()); cellStyle.setLocked(poiCellStyle.getLocked()); cellStyle.setAlignment(PoiEnumConversion.toHorizontalAlignment(poiCellStyle.getAlignment())); cellStyle.setVerticalAlignment( PoiEnumConversion.toVerticalAlignment(poiCellStyle.getVerticalAlignment())); cellStyle.setRotation(poiCellStyle.getRotation()); // ZSS-918 cellStyle.setIndention(poiCellStyle.getIndention()); // ZSS-915 Color fgColor = poiCellStyle.getFillForegroundColorColor(); Color bgColor = poiCellStyle.getFillBackgroundColorColor(); // if (fgColor == null && bgColor != null) { //ZSS-797 // fgColor = bgColor; // } // ZSS-857: SOLID pattern: switch fillColor and backColor cellStyle.setFillPattern(PoiEnumConversion.toFillPattern(poiCellStyle.getFillPattern())); SColor fgSColor = book.createColor(BookHelper.colorToForegroundHTML(workbook, fgColor)); SColor bgSColor = book.createColor(BookHelper.colorToBackgroundHTML(workbook, bgColor)); if (cellStyle.getFillPattern() == FillPattern.SOLID) { SColor tmp = fgSColor; fgSColor = bgSColor; bgSColor = tmp; } cellStyle.setFillColor(fgSColor); cellStyle.setBackColor(bgSColor); // ZSS-780 cellStyle.setBorderLeft(PoiEnumConversion.toBorderType(poiCellStyle.getBorderLeft())); cellStyle.setBorderTop(PoiEnumConversion.toBorderType(poiCellStyle.getBorderTop())); cellStyle.setBorderRight(PoiEnumConversion.toBorderType(poiCellStyle.getBorderRight())); cellStyle.setBorderBottom(PoiEnumConversion.toBorderType(poiCellStyle.getBorderBottom())); cellStyle.setBorderLeftColor( book.createColor( BookHelper.colorToBorderHTML(workbook, poiCellStyle.getLeftBorderColorColor()))); cellStyle.setBorderTopColor( book.createColor( BookHelper.colorToBorderHTML(workbook, poiCellStyle.getTopBorderColorColor()))); cellStyle.setBorderRightColor( book.createColor( BookHelper.colorToBorderHTML(workbook, poiCellStyle.getRightBorderColorColor()))); cellStyle.setBorderBottomColor( book.createColor( BookHelper.colorToBorderHTML(workbook, poiCellStyle.getBottomBorderColorColor()))); cellStyle.setHidden(poiCellStyle.getHidden()); // same style always use same font cellStyle.setFont(importFont(poiCellStyle)); } return cellStyle; }
/* * import sheet scope content from POI Sheet. */ protected SSheet importSheet(Sheet poiSheet, int poiSheetIndex) { SSheet sheet = book.createSheet(poiSheet.getSheetName()); sheet.setDefaultRowHeight(UnitUtil.twipToPx(poiSheet.getDefaultRowHeight())); // ZSS-952 importSheetDefaultColumnWidth(poiSheet, sheet); // reference FreezeInfoLoaderImpl.getRowFreeze() sheet.getViewInfo().setNumOfRowFreeze(BookHelper.getRowFreeze(poiSheet)); sheet.getViewInfo().setNumOfColumnFreeze(BookHelper.getColumnFreeze(poiSheet)); sheet .getViewInfo() .setDisplayGridlines( poiSheet .isDisplayGridlines()); // Note isDisplayGridlines() and isPrintGridlines() are // different sheet.getViewInfo().setColumnBreaks(poiSheet.getColumnBreaks()); sheet.getViewInfo().setRowBreaks(poiSheet.getRowBreaks()); SPrintSetup sps = sheet.getPrintSetup(); SHeader header = sheet.getViewInfo().getHeader(); if (header != null) { header.setCenterText(poiSheet.getHeader().getCenter()); header.setLeftText(poiSheet.getHeader().getLeft()); header.setRightText(poiSheet.getHeader().getRight()); sps.setHeader(header); } SFooter footer = sheet.getViewInfo().getFooter(); if (footer != null) { footer.setCenterText(poiSheet.getFooter().getCenter()); footer.setLeftText(poiSheet.getFooter().getLeft()); footer.setRightText(poiSheet.getFooter().getRight()); sps.setFooter(footer); } if (poiSheet.isDiffOddEven()) { Header poiEvenHeader = poiSheet.getEvenHeader(); if (poiEvenHeader != null) { SHeader evenHeader = new HeaderFooterImpl(); evenHeader.setCenterText(poiEvenHeader.getCenter()); evenHeader.setLeftText(poiEvenHeader.getLeft()); evenHeader.setRightText(poiEvenHeader.getRight()); sps.setEvenHeader(evenHeader); } Footer poiEvenFooter = poiSheet.getEvenFooter(); if (poiEvenFooter != null) { SFooter evenFooter = new HeaderFooterImpl(); evenFooter.setCenterText(poiEvenFooter.getCenter()); evenFooter.setLeftText(poiEvenFooter.getLeft()); evenFooter.setRightText(poiEvenFooter.getRight()); sps.setEvenFooter(evenFooter); } } if (poiSheet.isDiffFirst()) { Header poiFirstHeader = poiSheet.getFirstHeader(); if (poiFirstHeader != null) { SHeader firstHeader = new HeaderFooterImpl(); firstHeader.setCenterText(poiFirstHeader.getCenter()); firstHeader.setLeftText(poiFirstHeader.getLeft()); firstHeader.setRightText(poiFirstHeader.getRight()); sps.setFirstHeader(firstHeader); } Footer poiFirstFooter = poiSheet.getFirstFooter(); if (poiFirstFooter != null) { SFooter firstFooter = new HeaderFooterImpl(); firstFooter.setCenterText(poiFirstFooter.getCenter()); firstFooter.setLeftText(poiFirstFooter.getLeft()); firstFooter.setRightText(poiFirstFooter.getRight()); sps.setFirstFooter(firstFooter); } } PrintSetup poips = poiSheet.getPrintSetup(); sps.setBottomMargin(poiSheet.getMargin(Sheet.BottomMargin)); sps.setTopMargin(poiSheet.getMargin(Sheet.TopMargin)); sps.setLeftMargin(poiSheet.getMargin(Sheet.LeftMargin)); sps.setRightMargin(poiSheet.getMargin(Sheet.RightMargin)); sps.setHeaderMargin(poiSheet.getMargin(Sheet.HeaderMargin)); sps.setFooterMargin(poiSheet.getMargin(Sheet.FooterMargin)); sps.setAlignWithMargins(poiSheet.isAlignMargins()); sps.setErrorPrintMode(poips.getErrorsMode()); sps.setFitHeight(poips.getFitHeight()); sps.setFitWidth(poips.getFitWidth()); sps.setHCenter(poiSheet.getHorizontallyCenter()); sps.setLandscape(poips.getLandscape()); sps.setLeftToRight(poips.getLeftToRight()); sps.setPageStart(poips.getUsePage() ? poips.getPageStart() : 0); sps.setPaperSize(PoiEnumConversion.toPaperSize(poips.getPaperSize())); sps.setCommentsMode(poips.getCommentsMode()); sps.setPrintGridlines(poiSheet.isPrintGridlines()); sps.setPrintHeadings(poiSheet.isPrintHeadings()); sps.setScale(poips.getScale()); sps.setScaleWithDoc(poiSheet.isScaleWithDoc()); sps.setDifferentOddEvenPage(poiSheet.isDiffOddEven()); sps.setDifferentFirstPage(poiSheet.isDiffFirst()); sps.setVCenter(poiSheet.getVerticallyCenter()); Workbook poiBook = poiSheet.getWorkbook(); String area = poiBook.getPrintArea(poiSheetIndex); if (area != null) { sps.setPrintArea(area); } CellRangeAddress rowrng = poiSheet.getRepeatingRows(); if (rowrng != null) { sps.setRepeatingRowsTitle(rowrng.getFirstRow(), rowrng.getLastRow()); } CellRangeAddress colrng = poiSheet.getRepeatingColumns(); if (colrng != null) { sps.setRepeatingColumnsTitle(colrng.getFirstColumn(), colrng.getLastColumn()); } sheet.setPassword(poiSheet.getProtect() ? "" : null); // import hashed password directly importPassword(poiSheet, sheet); // ZSS-832 // import sheet visible if (poiBook.isSheetHidden(poiSheetIndex)) { sheet.setSheetVisible(SheetVisible.HIDDEN); } else if (poiBook.isSheetVeryHidden(poiSheetIndex)) { sheet.setSheetVisible(SheetVisible.VERY_HIDDEN); } else { sheet.setSheetVisible(SheetVisible.VISIBLE); } // ZSS-1130 // import conditionalFormatting importConditionalFormatting(sheet, poiSheet); return sheet; }
/** * Gets the book-type information ("xls" or "xlsx"), return null if not found * * @param book * @return */ public static String getBookType(SBook book) { return (String) book.getAttribute(BOOK_TYPE_KEY); }