How to generate 2 worksheets in excel workbook when we are generating that excel from xml after applying OOXML xsl?

Question

How to generate 2 worksheets in excel workbook when we are generating that excel from xml after applying OOXML xsl?

R, K 0

Hi, I am not able to relate/create 2nd sheet in xsl using ooxml. How to link Sheet defined in workbook variable to the <worksheet.. > element where we actually populate the data? Thanks.

Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-12T03:52:45.7233333+00:00
Hi R, K, It looks like you are using the Open XML SDK library to programmatically generate worksheets. If this is correct, you should direct your question to either:

https://stackoverflow.com/questions/tagged/openxml-sdk

https://github.com/dotnet/Open-XML-SDK/issues

If this is primarily a "how-to" question, use #1 above.

If you believe that the SDK has a bug or is not behaving as it should, use #2 above.

Either way, someone can help you out in those forums.

If I've misunderstood your question, I apologize, and please elaborate or explain further so we can best help.

Best regards,
Tom Jebo
Microsoft Open Specifications Support

R, K 0

Hi Tom,

I think I should have put more information.

Following way I am trying to create an excel report.

I have one xml file. I am applying xsl file, using xsl file it is generating excel file.

In xsl, using old way I could generate 2 sheets in a single excel but using ooxml xsl I could not.

Following is the snippet from the ooxml xsl :-

  <!--workbook definition-->
  <xsl:variable name="workbook" select="'&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;&lt;workbook xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot; xmlns:r=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot; xmlns:mc=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot; xmlns:x15=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&quot; mc:Ignorable=&quot;x15&quot;&gt;&lt;bookViews&gt;&lt;workbookView xWindow=&quot;120&quot; yWindow=&quot;90&quot; windowWidth=&quot;28515&quot; windowHeight=&quot;14370&quot; /&gt;&lt;/bookViews&gt;&lt;sheets&gt;&lt;sheet name=&quot;Sheet1&quot; sheetId=&quot;1&quot; r:id=&quot;rId1&quot;/&gt;&lt;sheet name=&quot;Sheet2&quot; sheetId=&quot;6&quot; r:id=&quot;rId6&quot;/&gt;&lt;/sheets&gt;&lt;/workbook&gt;'">
  </xsl:variable>


In this workbook definition I am asking to create 2 sheets, but it is creating a corrupt excel.


I have made changes for sheet2 by referring to the sheet1 in other relationship variables.

But nowhere in the xsl I can find reference of rId1 ,which is relationship id to the sheet1, related to the <worksheet> tag that identifies to create sheet1. Which is very confusing.


Also, I have referred to Office Open XML - Anatomy of an OOXML SpreadsheetML File this site. 


Thanks.

Tom Jebo 2,336 Microsoft Employee Moderator

Hi R, K,

I converted your answer to a comment assuming that's what you meant. The code you show looks to only generate the workbook.xml part, which does indeed have the elements defining "Sheet1" and "Sheet2" in the sheets list. However, I'm assuming that you wanted to generate the sheet2.xml part as well. Are you doing that? Can you supply the resulting workbook (i.e. final_output.xlsx)? Either you are not generating the sheet2.xml part or you are not adding the relationship in xl/_rels/workbook.xml.rels. There are other possibilities but without seeing the all of the xsl and xml used, I can't say what else.

Here is the formatted xml you are using to create the workbook.xml part (to add clarity for other readers of this thread):

<?xml version="1.0" encoding="UTF-8"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
  xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
  xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
  xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x15">
  <bookViews>
    <workbookView xWindow="120" yWindow="90" windowWidth="28515" windowHeight="14370" />
  </bookViews>
  <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1" />
    <sheet name="Sheet2" sheetId="6" r:id="rId6" />
  </sheets>
</workbook>

And here is an example xl/_rels/workbook.xml.rels part to show both sheet relationships:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
    <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme" Target="theme/theme1.xml"/>
    <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet2.xml"/>
    <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/>
    <Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>
</Relationships>

Tom

R, K 0 Reputation points

2024-01-15T07:42:35.62+00:00

Hi Tom, Thanks for reply. I am attaching the file with which I am facing the issue. Test.xml I am attaching Test.xml please change extension to .xsl. Now you will see that nowhere it is mentioned to insert all data in sheet1.xml only. Or how can we indicate which data to put in which xml?

Thanks.
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-17T20:49:27.2833333+00:00
Hi R, K,

Thanks for providing the entire .xsl file. I see in the xsl you provided, that you write out the various parts of the package in generate_OOXML and that you generate the content for the two worksheets Sheet1 and Sheet2 in generate_real_content template, for example, the workbook part like this:

<xsl:variable name="generate_workbook" select="crf:writeDocument('xl/workbook.xml',$workbook)" />

However, in generate_OOXML there is no line which will write the worksheet content from generate_real_content to /xl/worksheets/sheet1.xml and /xl/worksheets/sheet2.xml.

I would expect to see two separate lines in generate_OOXML to write out these two parts.

Can you explain how that works or clarify that you are not doing that?

Tom
R, K 0 Reputation points

2024-01-18T04:56:54.2966667+00:00

Hi Tom, Thats what. Even if I am not writing anything to write the content in sheet1.xml. it is always writing data in sheet1.xml. So i wanted to know what indication or trigger is there it is always dumping all the data in sheet1.xml and not even creating sheet2.xml. Thanks.
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-22T20:09:08.99+00:00

Hi R, K,

I'm not sure how you are invoking that XSL but it looks like it will write out the <worksheets> block with the two <worksheet> blocks into the main XML output document as the whole document (i.e. matched as "/"). All the other parts, for example the [Content_Types].xml, xl/workbook.xml, xl/styles.xml, etc... are being written to separate XML documents in the generate_ooxml template which is correct. xl/worksheets/sheet1.xml and xl/worksheets/sheet2.xml also need to be written as separate documents in the paths indicated above. Subsequently all these XML part documents must be zipped together to create the final ISO 29500 Open Packaging Convention format and renamed to have the extension .xlsx.

Note: I don't have your xmlns:crf="http://ExternalFunction.setFile" external functions so I'm not able to write the parts out when I test the XSL. But I can see what it's doing.

Tom
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-24T00:45:58.53+00:00
Hi R, K,

Can you please point me to information about:

xmlns:crf="http://ExternalFunction.setFile"

I'll need this to write the document parts and come up with the final package.

Tom

1 answer

Your answer

Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-12T03:52:45.7233333+00:00

Hi R, K, It looks like you are using the Open XML SDK library to programmatically generate worksheets. If this is correct, you should direct your question to either:

https://stackoverflow.com/questions/tagged/openxml-sdk

https://github.com/dotnet/Open-XML-SDK/issues

If this is primarily a "how-to" question, use #1 above.

If you believe that the SDK has a bug or is not behaving as it should, use #2 above.

Either way, someone can help you out in those forums.

If I've misunderstood your question, I apologize, and please elaborate or explain further so we can best help.

Best regards,
Tom Jebo
Microsoft Open Specifications Support
R, K 0 Reputation points

2024-01-12T12:14:23.2766667+00:00

Hi Tom, I think I should have put more information. Following way I am trying to create an excel report. I have one xml file. I am applying xsl file, using xsl file it is generating excel file. In xsl, using old way I could generate 2 sheets in a single excel but using ooxml xsl I could not. Following is the snippet from the ooxml xsl :-  <xsl:variable name="workbook" select="'<?xml version="1.0" encoding="UTF-8"?><workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" xmlns:x15="http://schemas.microsoft.com/office/spreadsheetml/2010/11/main" mc:Ignorable="x15"><bookViews><workbookView xWindow="120" yWindow="90" windowWidth="28515" windowHeight="14370" /></bookViews><sheets><sheet name="Sheet1" sheetId="1" r:id="rId1"/><sheet name="Sheet2" sheetId="6" r:id="rId6"/></sheets></workbook>'"> </xsl:variable> In this workbook definition I am asking to create 2 sheets, but it is creating a corrupt excel. I have made changes for sheet2 by referring to the sheet1 in other relationship variables. But nowhere in the xsl I can find reference of rId1 ,which is relationship id to the sheet1, related to the <worksheet> tag that identifies to create sheet1. Which is very confusing. Also, I have referred to Office Open XML - Anatomy of an OOXML SpreadsheetML File this site. Thanks.
R, K 0 Reputation points

2024-01-15T07:42:35.62+00:00

Hi Tom, Thanks for reply. I am attaching the file with which I am facing the issue. Test.xml I am attaching Test.xml please change extension to .xsl. Now you will see that nowhere it is mentioned to insert all data in sheet1.xml only. Or how can we indicate which data to put in which xml?

Thanks.
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-17T20:49:27.2833333+00:00

Hi R, K,

Thanks for providing the entire .xsl file. I see in the xsl you provided, that you write out the various parts of the package in generate_OOXML and that you generate the content for the two worksheets Sheet1 and Sheet2 in generate_real_content template, for example, the workbook part like this:

<xsl:variable name="generate_workbook" select="crf:writeDocument('xl/workbook.xml',$workbook)" />

However, in generate_OOXML there is no line which will write the worksheet content from generate_real_content to /xl/worksheets/sheet1.xml and /xl/worksheets/sheet2.xml.

I would expect to see two separate lines in generate_OOXML to write out these two parts.

Can you explain how that works or clarify that you are not doing that?

Tom
R, K 0 Reputation points

2024-01-18T04:56:54.2966667+00:00

Hi Tom, Thats what. Even if I am not writing anything to write the content in sheet1.xml. it is always writing data in sheet1.xml. So i wanted to know what indication or trigger is there it is always dumping all the data in sheet1.xml and not even creating sheet2.xml. Thanks.
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-22T20:09:08.99+00:00

Hi R, K,

I'm not sure how you are invoking that XSL but it looks like it will write out the <worksheets> block with the two <worksheet> blocks into the main XML output document as the whole document (i.e. matched as "/"). All the other parts, for example the [Content_Types].xml, xl/workbook.xml, xl/styles.xml, etc... are being written to separate XML documents in the generate_ooxml template which is correct. xl/worksheets/sheet1.xml and xl/worksheets/sheet2.xml also need to be written as separate documents in the paths indicated above. Subsequently all these XML part documents must be zipped together to create the final ISO 29500 Open Packaging Convention format and renamed to have the extension .xlsx.

Note: I don't have your xmlns:crf="http://ExternalFunction.setFile" external functions so I'm not able to write the parts out when I test the XSL. But I can see what it's doing.

Tom
Tom Jebo 2,336 Reputation points Microsoft Employee Moderator

2024-01-24T00:45:58.53+00:00

Hi R, K,

Can you please point me to information about:

xmlns:crf="http://ExternalFunction.setFile"

I'll need this to write the document parts and come up with the final package.

Tom

Answer 1

Hi R, K,

In the absence of responses, I'm assuming that you either no longer need assistance or have taken a different approach.

Regardless of the approach you take and with the limited information I have, my only recommendation at this point is that you must separate the worksheet output in your sample xsl so that sheet1 and sheet2 are output separately to xl/worksheets/sheet1.xml and xl/worksheets/sheet2.xml. The current template you have will definitely output both sheets to the same output unless there is post-processing that I'm not seeing.

Good luck with the project!

Best regards,
Tom Jebo
Microsoft Open Specifications Support

Share via

How to generate 2 worksheets in excel workbook when we are generating that excel from xml after applying OOXML xsl?

1 answer

Your answer