Excel XML question re column + row counts 1

[TimeTraveler]

Situation:

Previously, at my day gig we have been passing Excel spreadsheets as single-page workbooks via the browser by altering the headers.

However, we can see that this is going to be limited going forward. We would also like to send a multi-page workbook via email rather than a single-page spreadsheet via browser download. Enter XML.

So, I'm trying to create an XML file in stages: XML header, content, remaining XML code, then email it to the recipient.

Problem: It seems Excel's XML spec wants a column and row count BEFORE it is known. I'm referring to <TABLE ss:ExpandedColumnCount=...

Do I have to specify? Does it matter if I list more columns than I need?

I've checked out other related issues using MS KB and on this forum but didn't notice an answer to my question.

TIA for any help, links or thoughts.

 

[Diancecht]

So you want to send a Excel file by mail and you need to transform it to XML? Why not send the .xls file? What about the Save as ... XML option?

Cheers,
Dian

 

[TimeTraveler]

The Excel (XML file with MS Excel Schema) is created on the fly. The particular tab/worksheet does not exist at the time the XML header (everything up to and including the table tag) is created. The parts are created in order.

Although, now that I write that, I could rewrite the app to just create the XML table first, then forensicly determine the column info afterwards, then assemble the parts and send.

The project did not have time budgeted for any extended re-write of the application, just change the file format from a) HTML with Excel headers to XML with MS Excel shema, and b) send via email vs pass via the browser and download stream. I have part b). I need part a).

The question remains, tho, is that do the column/row attributes in the table tag really matter?

<CFFILE action="write" FILE=" ... " OUTPUT='<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="[URL unfurl="true"]http://www.w3.org/TR/REC-html40">[/URL]

...

<Worksheet ss:Name="download">
		<Table ss:ExpandedColumnCount="#colCount#" ss:ExpandedRowCount="#rowCount#" x:FullColumns="1"
			x:FullRows="1">

 

[tsuji]

ref

http://msdn.microsoft.com/en-us/library/aa140066.aspx

--quote--[tt][ul][li]<ss:Table> Tag[/li]
[ul]
[li]Used by: Both Excel and the Spreadsheet component[/li]
[li]Parent element: ss:Worksheet[/li]
[li]Required elements: (none)[/li]
[li]Optional elements: ss:Column, ss:Row[/li]
[li]Required attributes: (none)[/li]
[li][blue]Optional attributes[/blue]: ssefaultColumnWidth, ssefaultRowHeight, [blue]ss:ExpandedColumnCount, ss:ExpandedRowCount[/blue], ss:LeftCell, ss:StyleID, ss:TopCell, x:FullColumns, x:FullRows[/li]
[/ul][/ul][/tt]
--unquote--

 

[TimeTraveler]

Thanks tsuji!

I had figured out by trial-and-error that I could produce the result less the Column tag. Seeing that the attributes in question are themselves optional for the Table tag is a big help since I want to create a proper file and did not want to omit if not within the spec.


-TT