io.keikai.importer

Class XlsxImporter

java.lang.Object

io.keikai.range.impl.imexp.AbstractImporter

io.keikai.importer.XlsxImporter

All Implemented Interfaces:

SImporter, Serializable

public class XlsxImporter
extends AbstractImporter
implements Serializable

An excel xlsx importer

Since:

5.0.0

Author:

jumperchen

See Also:

Serialized Form

Field Summary

Fields

Modifier and Type	Field and Description
protected SBook	book target book model
static String	BUILTIN_PRINT_AREA A built-in defined name that specifies the workbook's print area
static String	BUILTIN_PRINT_TITLE A built-in defined name that specifies the row(s) or column(s) to repeat at the top of each printed page.
static int	ERROR_DIV_0 #DIV/0! - Division by zero
static int	ERROR_FORMULA
static int	ERROR_NA #N/A - Argument or function not available
static int	ERROR_NAME #NAME? - Wrong function or range name
static int	ERROR_NULL #NULL! - Intersection of two cell ranges is empty
static int	ERROR_NUM #NUM! - Value range overflow
static int	ERROR_REF #REF! - Illegal or deleted cell reference
static int	ERROR_VALUE #VALUE! - Wrong type of operand
protected Map<io.keikai.importer.PictureJson,Integer>	importedPictureJson SFont object>
protected Map<XlsxExtractor.XlsxXfExtractor,SCellStyle>	importedXfs

Fields inherited from class io.keikai.range.impl.imexp.AbstractImporter

_importCache, BOOK_TYPE_KEY

Constructor Summary

Constructors

Constructor and Description
XlsxImporter()

Method Summary

Modifier and Type	Method and Description
protected SFont	createDxfZssFont(XlsxExtractor.XlsxFontExtractor xFont)
protected SBorder	createSBorder(Borders.Border border)
protected XSSFChartX	createXSSFChartX(XSSFDrawing patriarch, org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTGraphicalObjectFrame gfrm, XSSFClientAnchor xanchor)
protected SFont	createZssFont(XlsxExtractor.XlsxFontExtractor xFont)
double	geDefaultColumnWidthPerBaseColWidth(int baseWidth, int charWidth)
protected int	getAnchorHeightInPx(io.keikai.importer.ShapeJson shapeJson, SSheet sheet) DefaultBookWidgetLoader.getXSSFHeightInPx()
protected int	getAnchorWidthInPx(io.keikai.importer.ShapeJson shapeJson, SSheet sheet) Reference DefaultBookWidgetLoader.getXSSFWidthInPx()
static String	getBackgroundHTMLColor(XlsxExtractor.XlsxColorExtractor color, XlsxExtractor.XlsxThemesExtractor themes, XlsxImporter xlsxImporter)
static String	getBorderHTMLColor(XlsxExtractor.XlsxColorExtractor color, XlsxExtractor.XlsxThemesExtractor themes, XlsxImporter xlsxImporter)
static byte	getErrorCode(String errorText)
static String	getErrorText(int errorCode)
static String	getFontHTMLColor(XlsxExtractor.XlsxColorExtractor color, XlsxExtractor.XlsxThemesExtractor themes, XlsxImporter xlsxImporter)
static String	getForegroundHTMLColor(XlsxExtractor.XlsxColorExtractor color, XlsxExtractor.XlsxThemesExtractor themes, XlsxImporter xlsxImporter)
protected short	getPasswordHash(String password)
protected Font	getPoiFontFromRichText(Workbook book, Cell cell, RichTextString rstr, int run)
protected String	getTitleFormula(io.keikai.importer.SeriesJson seriesJson, int seriesIndex) return a formula or generate a default title ("Series[N]")if title doesn't exist.
protected int	getXoffsetInPixel(io.keikai.importer.ShapeJson shapeJson)
protected int	getYoffsetInPixel(io.keikai.importer.ShapeJson shapeJson)
protected void	importAutoFilter(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected void	importAutoFilterColumns(XlsxExtractor.XlsxAutoFilterExtractor xFilter, SAutoFilter zssFilter)
protected void	importAxis(io.keikai.importer.ChartSpaceJson chartSpaceJson, SChart chart)
protected void	importBorders(List<XlsxExtractor.XlsxBorderExtractor> extractors)
protected SCell	importCell(XlsxExtractor.XlsxCellExtractor xCell, int row, SSheet sheet)
protected SCellStyle	importCellStyleXf(XlsxExtractor.XlsxXfExtractor xCellXf, boolean inStyleTable)
protected void	importChart(io.keikai.importer.ChartSpaceJson chartSpaceJson, XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet) Not import X & Y axis title because XSSFCategoryAxis doesn't provide API to get title.
protected SColorFilter	importColorFilter(XlsxExtractor.XlsxColorFilterExtractor colorFilter)
protected void	importColors(XlsxExtractor.XlsxRGBColorsExtractor extractor)
protected void	importColumn(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet) [ISO/IEC 29500-1 1st Edition] 18.3.1.13 col (Column Width & Formatting) By experiments, CT_Col is always created in ascending order by min and the range specified by min & max doesn't overlap each other. For example:
protected void	importComments(XlsxExtractor.XlsxSheetExtractor xSheet)
protected void	importConditionalFormatting(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected SCustomFilters	importCustomFilters(XlsxExtractor.XlsxCustomFiltersExtractor xCustomFilters)
protected void	importDefaultCellStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)
protected void	importDrawings(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected SDynamicFilter	importDynamicFilter(XlsxExtractor.XlsxDynamicFilterExtractor xDynamicFilter)
protected void	importExternalBookLinks(List<XlsxExtractor.XlsxExternalLinkExtractor> externalReferences)
protected SExtraStyle	importExtraStyle(XlsxExtractor.XlsxDxfExtractor xDxf)
protected void	importExtraStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)
protected void	importFills(List<XlsxExtractor.XlsxFillExtractor> extractors)
protected void	importFonts(List<XlsxExtractor.XlsxFontExtractor> extractors)
protected void	importHyperlinks(XlsxExtractor.XlsxSheetExtractor xSheet)
protected void	importMergedRegions(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected void	importNamedRange() Name should be created after sheets created.
protected void	importNamedStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)
protected void	importNumFmts(List<XlsxExtractor.XlsxNumFmtExtractor> extractors)
protected void	importPassword(XlsxExtractor.XlsxSheetProtectionExtractor protectionExtractor, SSheet sheet)
protected void	importPicture(io.keikai.importer.PictureJson pictureJson, XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected void	importPrintArea(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected void	importRichText(XlsxExtractor.XlsxRichTextExtractor xRichText, SRichText richText)
protected SRow	importRow(XlsxExtractor.XlsxRowExtractor xRow, SSheet sheet)
SBook	imports(InputStream inputStream, String bookName) Import book from a input stream
protected void	importSeries(List<? extends io.keikai.importer.CategorySeriesJson> seriesList, SGeneralChartData chartData) reference ChartHelper.prepareCategoryModel() Category normally indicates the values show on X axis.
protected SSheet	importSheet(XlsxExtractor.XlsxSheetExtractor xSheet)
protected void	importSheetProtection(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected void	importSheets(List<XlsxExtractor.XlsxSheetExtractor> sheets)
protected void	importStyleSheet(XlsxExtractor.XlsxStyleSheetExtractor extractor)
protected void	importTableName(STable table)
protected void	importTables(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet)
protected STableStyle	importTableStyle(XlsxExtractor.XlsxTableStyleExtractor tableStyle)
protected STableStyleElem	importTableStyleElem(XlsxExtractor.XlsxTableStyleElementExtractor styleElement)
protected void	importTableStyles(XlsxExtractor.XlsxStyleSheetExtractor extractor)
protected STop10Filter	importTop10Filter(XlsxExtractor.XlsxTop10Extractor xTop10Filter)
protected void	importValidation(XlsxExtractor.XlsxSheetExtractor xSheet, SSheet sheet) Reference BookHelper.validate()
protected void	importVmlDrawing(XlsxExtractor.XlsxSheetExtractor xSheet)
protected void	importXySeries(List<? extends io.keikai.importer.XYSeriesJson> seriesList, SGeneralChartData chartData)
protected void	importXyzSeries(List<? extends io.keikai.importer.XYZSeriesJson> seriesList, SGeneralChartData chartData) reference ChartHelper.prepareXYZModel()
protected boolean	mustCalc(SCell cell)
protected SColorScale	prepareColorScale(XlsxExtractor.XlsxCfRuleExtractor ctRule)
protected ConditionalFormattingImpl	prepareConditionalFormattingImpl(SSheet sheet, XlsxExtractor.XlsxConditionalFormattingExtractor cf)
protected ConditionalFormattingRuleImpl	prepareConditonalFormattingRuleImpl(SConditionalFormatting cfi, XlsxExtractor.XlsxCfRuleExtractor xRule)
protected SDataBar	prepareDataBar(XlsxExtractor.XlsxCfRuleExtractor ctRule)
protected void	prepareFormulas(ConditionalFormattingRuleImpl cfri, XlsxExtractor.XlsxCfRuleExtractor xRule)
protected SIconSet	prepareIconSet(XlsxExtractor.XlsxCfRuleExtractor ctRule)
protected SCFValueObject	prepareValueObject(XlsxExtractor.XlsxCfvoExtractor ctvo)
protected void	setBookType(SBook book)
protected boolean	skipName(XlsxExtractor.XlsxDefinedNameExtractor definedName)
SChart.ChartGrouping	toChartGrouping(String grouping)
static SFont.TypeOffset	toTypeOffset(String typeOffset)
static SFont.Underline	toUnderline(String underline)
protected ViewAnchor	toViewAnchor(io.keikai.importer.ShapeJson shapeJson, SSheet sheet)

Methods inherited from class io.keikai.range.impl.imexp.AbstractImporter

imports, imports, isImportCache, setImportCache

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

importedXfs

protected Map<XlsxExtractor.XlsxXfExtractor,SCellStyle> importedXfs

importedPictureJson

protected Map<io.keikai.importer.PictureJson,Integer> importedPictureJson

SFont object>

book

protected SBook book

target book model

BUILTIN_PRINT_AREA

public static final String BUILTIN_PRINT_AREA

A built-in defined name that specifies the workbook's print area

See Also:

Constant Field Values

BUILTIN_PRINT_TITLE

public static final String BUILTIN_PRINT_TITLE

A built-in defined name that specifies the row(s) or column(s) to repeat at the top of each printed page.

See Also:

Constant Field Values

ERROR_NULL

public static final int ERROR_NULL

#NULL! - Intersection of two cell ranges is empty

See Also:

Constant Field Values

ERROR_DIV_0

public static final int ERROR_DIV_0

#DIV/0! - Division by zero

See Also:

Constant Field Values

ERROR_VALUE

public static final int ERROR_VALUE

#VALUE! - Wrong type of operand

See Also:

Constant Field Values

ERROR_REF

public static final int ERROR_REF

#REF! - Illegal or deleted cell reference

See Also:

Constant Field Values

ERROR_NAME

public static final int ERROR_NAME

#NAME? - Wrong function or range name

See Also:

Constant Field Values

ERROR_NUM

public static final int ERROR_NUM

#NUM! - Value range overflow

See Also:

Constant Field Values

ERROR_NA

public static final int ERROR_NA

#N/A - Argument or function not available

See Also:

Constant Field Values

ERROR_FORMULA

public static final int ERROR_FORMULA

See Also:

Constant Field Values

Constructor Detail

XlsxImporter

public XlsxImporter()

Method Detail

imports

public SBook imports(InputStream inputStream,
                     String bookName)
              throws IOException

Description copied from interface: SImporter

Import book from a input stream

Specified by:

imports in interface SImporter

Parameters:

inputStream - the input stream

bookName - the book name for imported book

Returns:

the book instance

Throws:

IOException

importPrintArea

protected void importPrintArea(XlsxExtractor.XlsxSheetExtractor xSheet,
                               SSheet sheet)

importHyperlinks

protected void importHyperlinks(XlsxExtractor.XlsxSheetExtractor xSheet)

importVmlDrawing

protected void importVmlDrawing(XlsxExtractor.XlsxSheetExtractor xSheet)

importComments

protected void importComments(XlsxExtractor.XlsxSheetExtractor xSheet)

importStyleSheet

protected void importStyleSheet(XlsxExtractor.XlsxStyleSheetExtractor extractor)

importColors

protected void importColors(XlsxExtractor.XlsxRGBColorsExtractor extractor)

importNumFmts

protected void importNumFmts(List<XlsxExtractor.XlsxNumFmtExtractor> extractors)

importFonts

protected void importFonts(List<XlsxExtractor.XlsxFontExtractor> extractors)

importFills

protected void importFills(List<XlsxExtractor.XlsxFillExtractor> extractors)

importBorders

protected void importBorders(List<XlsxExtractor.XlsxBorderExtractor> extractors)

importAutoFilter

protected void importAutoFilter(XlsxExtractor.XlsxSheetExtractor xSheet,
                                SSheet sheet)

importSheetProtection

protected void importSheetProtection(XlsxExtractor.XlsxSheetExtractor xSheet,
                                     SSheet sheet)

importValidation

protected void importValidation(XlsxExtractor.XlsxSheetExtractor xSheet,
                                SSheet sheet)

Reference BookHelper.validate()

importMergedRegions

protected void importMergedRegions(XlsxExtractor.XlsxSheetExtractor xSheet,
                                   SSheet sheet)

importDrawings

protected void importDrawings(XlsxExtractor.XlsxSheetExtractor xSheet,
                              SSheet sheet)

createXSSFChartX

protected XSSFChartX createXSSFChartX(XSSFDrawing patriarch,
                                      org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTGraphicalObjectFrame gfrm,
                                      XSSFClientAnchor xanchor)

importPicture

protected void importPicture(io.keikai.importer.PictureJson pictureJson,
                             XlsxExtractor.XlsxSheetExtractor xSheet,
                             SSheet sheet)

toViewAnchor

protected ViewAnchor toViewAnchor(io.keikai.importer.ShapeJson shapeJson,
                                  SSheet sheet)

getAnchorWidthInPx

protected int getAnchorWidthInPx(io.keikai.importer.ShapeJson shapeJson,
                                 SSheet sheet)

Reference DefaultBookWidgetLoader.getXSSFWidthInPx()

getAnchorHeightInPx

protected int getAnchorHeightInPx(io.keikai.importer.ShapeJson shapeJson,
                                  SSheet sheet)

DefaultBookWidgetLoader.getXSSFHeightInPx()

getXoffsetInPixel

protected int getXoffsetInPixel(io.keikai.importer.ShapeJson shapeJson)

getYoffsetInPixel

protected int getYoffsetInPixel(io.keikai.importer.ShapeJson shapeJson)

importChart

protected void importChart(io.keikai.importer.ChartSpaceJson chartSpaceJson,
                           XlsxExtractor.XlsxSheetExtractor xSheet,
                           SSheet sheet)

Not import X & Y axis title because XSSFCategoryAxis doesn't provide API to get title. Reference ChartHelper.drawXSSFChart()

toChartGrouping

public SChart.ChartGrouping toChartGrouping(String grouping)

importSeries

protected void importSeries(List<? extends io.keikai.importer.CategorySeriesJson> seriesList,
                            SGeneralChartData chartData)

reference ChartHelper.prepareCategoryModel() Category normally indicates the values show on X axis. Tile indicates the name for each series.

Parameters:

seriesList - source chart data

chartData - destination chart data

importXySeries

protected void importXySeries(List<? extends io.keikai.importer.XYSeriesJson> seriesList,
                              SGeneralChartData chartData)

importAxis

protected void importAxis(io.keikai.importer.ChartSpaceJson chartSpaceJson,
                          SChart chart)

importXyzSeries

protected void importXyzSeries(List<? extends io.keikai.importer.XYZSeriesJson> seriesList,
                               SGeneralChartData chartData)

reference ChartHelper.prepareXYZModel()

Parameters:

seriesList - // * @param chart

getTitleFormula

protected String getTitleFormula(io.keikai.importer.SeriesJson seriesJson,
                                 int seriesIndex)

return a formula or generate a default title ("Series[N]")if title doesn't exist. reference ChartHelper.prepareTitle()

Parameters:

seriesJson -

Returns:

importColumn

protected void importColumn(XlsxExtractor.XlsxSheetExtractor xSheet,
                            SSheet sheet)

[ISO/IEC 29500-1 1st Edition] 18.3.1.13 col (Column Width & Formatting) By experiments, CT_Col is always created in ascending order by min and the range specified by min & max doesn't overlap each other.
For example:

geDefaultColumnWidthPerBaseColWidth

public double geDefaultColumnWidthPerBaseColWidth(int baseWidth,
                                                  int charWidth)

importRow

protected SRow importRow(XlsxExtractor.XlsxRowExtractor xRow,
                         SSheet sheet)

importCell

protected SCell importCell(XlsxExtractor.XlsxCellExtractor xCell,
                           int row,
                           SSheet sheet)

getErrorText

public static final String getErrorText(int errorCode)

Returns:

Standard Excel error literal for the specified error code.

Throws:

IllegalArgumentException - if the specified error code is not one of the 7 standard error codes

getErrorCode

public static final byte getErrorCode(String errorText)

mustCalc

protected boolean mustCalc(SCell cell)

importRichText

protected void importRichText(XlsxExtractor.XlsxRichTextExtractor xRichText,
                              SRichText richText)

getPoiFontFromRichText

protected Font getPoiFontFromRichText(Workbook book,
                                      Cell cell,
                                      RichTextString rstr,
                                      int run)

createDxfZssFont

protected SFont createDxfZssFont(XlsxExtractor.XlsxFontExtractor xFont)

createZssFont

protected SFont createZssFont(XlsxExtractor.XlsxFontExtractor xFont)

getBorderHTMLColor

public static String getBorderHTMLColor(XlsxExtractor.XlsxColorExtractor color,
                                        XlsxExtractor.XlsxThemesExtractor themes,
                                        XlsxImporter xlsxImporter)

getFontHTMLColor

public static String getFontHTMLColor(XlsxExtractor.XlsxColorExtractor color,
                                      XlsxExtractor.XlsxThemesExtractor themes,
                                      XlsxImporter xlsxImporter)

getForegroundHTMLColor

public static String getForegroundHTMLColor(XlsxExtractor.XlsxColorExtractor color,
                                            XlsxExtractor.XlsxThemesExtractor themes,
                                            XlsxImporter xlsxImporter)

getBackgroundHTMLColor

public static String getBackgroundHTMLColor(XlsxExtractor.XlsxColorExtractor color,
                                            XlsxExtractor.XlsxThemesExtractor themes,
                                            XlsxImporter xlsxImporter)

toUnderline

public static SFont.Underline toUnderline(String underline)

toTypeOffset

public static SFont.TypeOffset toTypeOffset(String typeOffset)

skipName

protected boolean skipName(XlsxExtractor.XlsxDefinedNameExtractor definedName)

importNamedRange

protected void importNamedRange()

Name should be created after sheets created. A special defined name, _xlnm._FilterDatabase (xlsx) or _FilterDatabase (xls), stores the selected cells for auto-filter

importDefaultCellStyles

protected void importDefaultCellStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)

importCellStyleXf

protected SCellStyle importCellStyleXf(XlsxExtractor.XlsxXfExtractor xCellXf,
                                       boolean inStyleTable)

importNamedStyles

protected void importNamedStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)

importTables

protected void importTables(XlsxExtractor.XlsxSheetExtractor xSheet,
                            SSheet sheet)

importTableName

protected void importTableName(STable table)

importColorFilter

protected SColorFilter importColorFilter(XlsxExtractor.XlsxColorFilterExtractor colorFilter)

importAutoFilterColumns

protected void importAutoFilterColumns(XlsxExtractor.XlsxAutoFilterExtractor xFilter,
                                       SAutoFilter zssFilter)

importCustomFilters

protected SCustomFilters importCustomFilters(XlsxExtractor.XlsxCustomFiltersExtractor xCustomFilters)

importDynamicFilter

protected SDynamicFilter importDynamicFilter(XlsxExtractor.XlsxDynamicFilterExtractor xDynamicFilter)

importTop10Filter

protected STop10Filter importTop10Filter(XlsxExtractor.XlsxTop10Extractor xTop10Filter)

importExtraStyles

protected void importExtraStyles(XlsxExtractor.XlsxStyleSheetExtractor styleSheet)

importExtraStyle

protected SExtraStyle importExtraStyle(XlsxExtractor.XlsxDxfExtractor xDxf)

createSBorder

protected SBorder createSBorder(Borders.Border border)

importTableStyles

protected void importTableStyles(XlsxExtractor.XlsxStyleSheetExtractor extractor)

importTableStyleElem

protected STableStyleElem importTableStyleElem(XlsxExtractor.XlsxTableStyleElementExtractor styleElement)

importTableStyle

protected STableStyle importTableStyle(XlsxExtractor.XlsxTableStyleExtractor tableStyle)

setBookType

protected void setBookType(SBook book)

importExternalBookLinks

protected void importExternalBookLinks(List<XlsxExtractor.XlsxExternalLinkExtractor> externalReferences)

importSheets

protected void importSheets(List<XlsxExtractor.XlsxSheetExtractor> sheets)

importSheet

protected SSheet importSheet(XlsxExtractor.XlsxSheetExtractor xSheet)

importPassword

protected void importPassword(XlsxExtractor.XlsxSheetProtectionExtractor protectionExtractor,
                              SSheet sheet)

getPasswordHash

protected short getPasswordHash(String password)

importConditionalFormatting

protected void importConditionalFormatting(XlsxExtractor.XlsxSheetExtractor xSheet,
                                           SSheet sheet)

prepareConditionalFormattingImpl

protected ConditionalFormattingImpl prepareConditionalFormattingImpl(SSheet sheet,
                                                                     XlsxExtractor.XlsxConditionalFormattingExtractor cf)

prepareConditonalFormattingRuleImpl

protected ConditionalFormattingRuleImpl prepareConditonalFormattingRuleImpl(SConditionalFormatting cfi,
                                                                            XlsxExtractor.XlsxCfRuleExtractor xRule)

prepareColorScale

protected SColorScale prepareColorScale(XlsxExtractor.XlsxCfRuleExtractor ctRule)

prepareIconSet

protected SIconSet prepareIconSet(XlsxExtractor.XlsxCfRuleExtractor ctRule)

prepareFormulas

protected void prepareFormulas(ConditionalFormattingRuleImpl cfri,
                               XlsxExtractor.XlsxCfRuleExtractor xRule)

prepareDataBar

protected SDataBar prepareDataBar(XlsxExtractor.XlsxCfRuleExtractor ctRule)

prepareValueObject

protected SCFValueObject prepareValueObject(XlsxExtractor.XlsxCfvoExtractor ctvo)