Difference between revisions of "Calc/Performance/CellStylesOfOoxML"

Latest revision as of 03:07, 19 October 2012

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.