Calc Performance Optimizations for CellStylesInOoxML
If there are a lot of cell styles in MS 2007 .xlsx file, when loading the file in Aoo, it will take about 20 seconds to create the cell styles.
After analyze the sample file, there are lots of duplicated cell style items in the original file,and such file maybe created by script or other tools. So a method must be used to get rid of the duplicated unused cell styles.
In MS 2007 .xlsx file,there are 3 items to record the cell style.
- cellStyleXfs
This element contains the master formatting records (xf's) which define the formatting for all named cell styles in this workbook. Master formatting records reference individual elements of formatting (e.g., number format, font definitions, cell fills, etc) by specifying a zero-based index into those collections. Master formatting records also specify whether to apply or ignore particular aspects of formatting, for example whether to apply a border or not.
- cellXfs
This element contains the master formatting records (xf) which define the formatting applied to cells in this workbook. These records are the starting point for determining the formatting for a cell. Cells in the Sheet Part reference the xf records by zero-based index.
- cellStyles
This element contains the named cell styles, consisting of a sequence of named style records. A named cell style is a collection of direct or themed formatting (e.g., cell border, cell fill, and font type/size/style) grouped together into a single named style, and can be applied to a cell.
Here is the relationship.
Here is the result data by the rational purify.
Calls: 178,325 F+D: 25,128,429
Calls: 4,268 F+D: 24,404,739
1) ScCellRangesBase::setPropertyValues Calls: 45,336 F+D: 13,999,231
2) ScCellRangesBase::setPropertyValue Calls: 36,334 F+D: 8,745,959
3) ScStyleObj::setPropertyValues Calls: 28,282 F+D: 10,547,312
1)oox::xls::OoxSheetDataContext::onCreatContext 1) oox::xls::OoxSheetDataContext::importRow
oox::xls::WorksheetData::writeXfldRowRangeProperties
oox::xls::StylesBuffer::writeCellXfToPropertySet
oox::xls::StylesBuffer::writeFontToProperSet
oox::xls::StylesBuffer::writeBorderToProperSet
oox::xls::Allignment::writeToPropertySet
oox::xls::StylesBuffer::writeFillToPropertySet
4) ScStyleObj::setPropertyValue Calls: 36,334
F+D: 8,745,959
Calls:178,308
F+D: 5,538,047
Calls: 158,771
F+D: 4,810,522
1) ScCellRangesBase::setPropertyValues
2) ScCellRangesBase::setPropertyValue
3) ScStyleObj::setPropertyValues
2)oox::xls::OoxSheetDataContext::onEndElement 1) oox::xls::WorksheetHelper::setCellFormat
oox::xls::WorksheetData::writeXfldRowRangeProperties
oox::xls::StylesBuffer::writeCellXfToPropertySet
oox::xls::StylesBuffer::writeFontToProperSet
oox::xls::StylesBuffer::writeBorderToProperSet
oox::xls::Allignment::writeToPropertySet oox::xls::StylesBuffer::writeFillToPropertySet 4) ScStyleObj::setPropertyValue Calls: 1 F+D: 21,475,924
Calls: 14,234
F+D: 21,419,377
1) ScCellRangesBase::setPropertyValues 2) ScCellRangesBase::setPropertyValue 3) ScStyleObj::setPropertyValues
3)oox::xls::OoxStylesFragement::finalizeImport 1) oox::xls::CellStyle::createStyle
oox::xls::StylesBuffer::writeStylesXfToPropertySet
oox::xls::StylesBuffer::writeFontToProperSet
oox::xls::StylesBuffer::writeBorderToProperSet
oox::xls::Allignment::writeToPropertySet
oox::xls::StylesBuffer::writeFillToPropertySet 4) ScStyleObj::setPropertyValue
The oox::xls::CellStyle::createStyle is called 14,234 times, and the F+D: 21,419,377
In this solution:
1. if there are two same cell styles,named "style1" and "style2", if a cell uses the second one,all the two cell styles will be created.
- the used styles should be imported.
- the unused duplicate styles should be imported only once.
2. if there are two different cell styles, all the styles will be imported to spreadsheet.
3. if there are two same cell styles, but they are used by different cells, all of them will be imported to spreadsheet.
4. if there are two same cell styles, and they are not used by any cell, only import the first one to spreadsheet.
