Any easy way to update sharedStrings.xml?

Oct 21, 2011 at 3:53 PM

After a large dataload, the sheet.xml can become quite large with redundant data.  Is there an easy way to force the updating of the sharedstrings.xml file in the package to reduce the file size?

Coordinator
Oct 28, 2011 at 2:20 PM

Hmm, the sharedstrings.xml should not contain any duplicates, so im not sure what you mean. Only string data is stored in the sharedstring so this does not apply to numbers.

Oct 28, 2011 at 3:34 PM

I really should have written that better, I apologize.

When I do a copy/paste of data into excel, the resultant sheet1.xml looks like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
	<dimension ref="A1"/>
	<sheetViews>
		<sheetView tabSelected="1" workbookViewId="0"/>
	</sheetViews>
	<sheetFormatPr defaultRowHeight="15"/>
	<sheetData>
		<row r="1" spans="1:1">
			<c r="A1" t="s">
				<v>0</v>
			</c>
		</row>
	</sheetData>
	<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>


vs. loading from datatable

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
	<dimension ref="A1" />
	<sheetViews>
		<sheetView tabSelected="1" workbookViewId="0" />
	</sheetViews>
	<sheetFormatPr defaultRowHeight="15" />
	<sheetData>
		<row r="1" >
			<c r="A1" s="0" t="s">
				<v>0</v>
			</c>
		</row>
	</sheetData>
	<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3" />
</worksheet>

So loading 50K rows with 10 columns can cause the sheet1.xml to have a 80% increase in size when imported vs. copy/pasted.

I think its the addition of the s="0" to every single cell.


Coordinator
Nov 3, 2011 at 3:41 PM

s="0" is the style ID.

I guess it could be omitted if the StyleID is 0. I'll have a look at it to the next version. This "size increase" is only a problem if you havn't applied any style to your data.

Jan

Nov 3, 2011 at 4:00 PM

Absolutely.  Thanks for looking into it.