I want to generate .xlsx with my application.

the reason I don't want to do this by loading .xml are :

1. my application will then depend on those .xml, I have to maintain my code and also those .xml

2. if I load from .xml, maybe it is even better to have a empty .xlsx in my server, open it, do some modification to it, write it out. This doesn't make sense to me. and I don't want to have a bunch of .xlsx or .xml in my server.

I believe we are capable of doing this, but just don't have a walk-through here. I have been reading many documents just trying to do this simple things. I can't find many information on this one.

Here is what I have gone so far, hope that someone can complete this puzzle together.

//create a excel 2007 package, .xlsx
SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(@"C:\sample\ooxmlSDKnewSS.xlsx", SpreadsheetDocumentType.Workbook);
//add a workbook to the package
WorkbookPart wbPart = excelDoc.AddWorkbookPart();
//add a worksheet to the workbook
WorksheetPart wsPart = wbPart.AddNewPart<WorksheetPart>();
//generate the id for the worksheet
string relId = wbPart.GetIdOfPart(wsPart);

This should handle the relationship thing of the package with one workbook and one worksheet only.
We now need to create the real .xml document for them.

//create a xml document for workbook.xml
XmlDocument xwb = new XmlDocument
//I believe this is what every .xml must have
xwb.AppendChild(xwb.CreateXmlDeclaration("1.0", "UTF-8", "yes"));
//add the root element,
//I Believe there are better ways to handle namespace
//if you know, please tell me
XmlElement eleWorkbook = xwb.CreateElement("workbook", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
eleWorkbook.SetAttribute(@"xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
xwb.AppendChild(eleWorkbook);
eleWorkbook.AppendChild(xwb.CreateElement("sheets"));

XmlElement eleSheet = xwb.CreateElement("sheet");
eleSheet.SetAttribute("name", "Sheet1");
eleSheet.SetAttribute("sheetId", "1");
eleSheet.SetAttribute(@"r:id", relId);
eleWorkbook.FirstChild.AppendChild(eleSheet);

//write this .xml to workbookpart
Stream wbstream = wbPart.GetStream();
xwb.Save(wbstream);

//do the same for worksheet .xml
 XmlDocument xws = new XmlDocument();
 xws.AppendChild(xws.CreateXmlDeclaration("1.0", "UTF-8", "yes"));
XmlElement eleWorksheet = xws.CreateElement("worksheet", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
eleWorksheet.SetAttribute(@"xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
xws.AppendChild(eleWorksheet);

XmlElement eleSheetData = xws.CreateElement("sheetData");
eleWorksheet.AppendChild(eleSheetData);

Stream wsstream = wsPart.GetStream();
xws.Save(wsstream);

//close it
excelDoc.Close();

This code can generate a package, with files that are needed in place, but cannot be opened by Excel 2007. I believe the problem is caused by namespace or relationship in-consistency. probably relationship. I am still strugling  on how to make my hand-made .xml to be consistent with the automated relationship by the SDK, those Id sort of things.

Please help me out if you have done this before.

Thank you

Here are some known issue about the .xlsx I generated.

1. the content type of default .xml if not correct.

2. there are no app.xml and core.xml.

Hello Maxi,

By looking at the spreadsheet document manually, I found couple of problems.I manually updated them and then document opened fine.

Here are them:

workbook.xml:

This file contains   <sheet name="Sheet1" sheetId="1" id="Rb20d937179c144b1" /> but id attribute doesnt have r name space defined. so it should be

<sheet name="Sheet1" sheetId="1" r:id="Rb20d937179c144b1" />

And workbook.xml contains sheet tag as

<sheets xmlns=""> where we dont need xmlns="" becuase we need a namespace to define sheets element.

sheet.xml

Here the sheetdata element is defined as <sheetData xmlns="" /> and the problem of blank namespace.

[Content_Types].xml

This file is missing a very improtant tag.

  <Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>

Please modify your code for these and then& spreadsheet document will work just fine.

Note : If I understand your requirement correctly, you would like to generate the XML file  on the fly. For this what I suggest, add XML file to your project which contains minimum xml tags. And then you can use LINQ /System.xml to manipulate XML file to insert /update any data you want.

Please note that now Open XML Format SDK 2.0 has been released.

http://www.microsoft.com/downloads/details.aspx?familyid=c6e744e5-36e9-45f5-8d8c-331df206e0d0&displaylang=en&tm

The SDK supports programming in the style of LINQ to XML which makes coding against the XML content much easier than the traditional W3C XML DOM programming model.

Ankush

Hello Ankush,

Thank you for pointing out my problems, I was finding solutions to them.

for the r:id, I noticed that it is missing too, I insert @"r:id" to it but the result come out is still "id".

I try many ways but none of them work. and also the empty namespace, I never put them their, I search on google and people got the same problem too. they said those are generated when you save or serialize the xml object. and have to get rid of them with some extra coding. for the content_type problem. I didn't do any since I think the SDK will take care of that correctly, but it doesn't.

my requirement is just as what you understand, generate it on the fly.I have been getting my hands on SDK 2.0, it is much better but still have some problems with the IEnumeralbe.

The example from their manual : Insert a New Worksheet into a Spreadsheet Document

which won't work.

// Get a unique ID for the new worksheet.
        uint sheetId = 1;
        if (sheets.Elements<Sheet>().Count() > 0)
        {
            sheetId = sheets.Elements<Sheet>().Select(s => s.SheetId.Value).Max() + 1;
        }

the compiler of VS 2008 tells me that it doesn't have count() method.

and then I try to implement the method by using movenext(), but this method is not correct too!

It returns true no matter what...

Thank you for you help, I will keep working on this issue.

Hello Maxi,

I used that sample ("Insert a New Worksheet into a Spreadsheet Document") in the VS 2008 and the error I got is :
-------------
Error 1 'System.Collections.Generic.IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheet>' does not contain a definition for 'Count' and no extension method 'Count' accepting a first argument of type 'System.Collections.Generic.IEnumerable<DocumentFormat.OpenXml.Spreadsheet.Sheet>' could be found (are you missing a using directive or an assembly reference?) C:\Users\abhatia\Desktop\PresSample\PresSample\Form1.cs 391 38 PresSample
----------

Then I realized that all these methods are extension methods and I need to use LINQ namespace, once I did that it worked fine. so try running code after importing LINQ namespace.

================

Also on XML generation and where it inserts space , it all depands how you create your XML file.

Following are 2-3 differnt ways in which you can write any xml (the sample I have taken is to create a workbook.xml).

==========
code sample 1: This code you have used in your application. I have changed this to have the correct namespace
==========

//create a xml document for workbook.xml

XmlDocument xwb = new XmlDocument();

//I believe this is what every .xml must have

xwb.AppendChild(xwb.CreateXmlDeclaration("1.0", "UTF-8", "yes"));

//add the root element,

//I Believe there are better ways to handle namespace

//if you know, please tell me

XmlElement eleWorkbook = xwb.CreateElement("workbook");

eleWorkbook.SetAttribute(@"xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

eleWorkbook.SetAttribute(@"xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

eleWorkbook.AppendChild(xwb.CreateElement("sheets"));

XmlElement eleSheet = xwb.CreateElement("sheet");

eleSheet.SetAttribute("name", "Sheet1");

eleSheet.SetAttribute("sheetId", "1");

eleSheet.SetAttribute(@"id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", "rId1");

//write this .xml to workbookpart

xwb.Save(@"c:\temp\test3.xml");

==========
code sample 2: using XMLWriter
==========

XmlWriterSettings xws = new XmlWriterSettings();

xws.ConformanceLevel = ConformanceLevel.Auto;

XmlWriter xw = XmlWriter.Create(@"c:\temp\test2.xml",xws);

xw.WriteProcessingInstruction("xml", "version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ");

xw.WriteStartElement("workbook", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

xw.WriteStartElement("sheets", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

xw.WriteStartElement("sheet", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

xw.WriteAttributeString("id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", "rId1");

==========
code sample 3 using simple approch
==========

And here is the updated code which works fine

//create a excel 2007 package, .xlsx

SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(@"C:\sample\ooxmlSDKnewSS.xlsx", SpreadsheetDocumentType.Workbook);

//add a workbook to the package

WorkbookPart wbPart = excelDoc.AddWorkbookPart();

//add a worksheet to the workbook

WorksheetPart wsPart = wbPart.AddNewPart<WorksheetPart>();

//generate the id for the worksheet

string relId = wbPart.GetIdOfPart(wsPart);

//This should handle the relationship thing of the package with one workbook and one worksheet only.

//We now need to create the real .xml document for them.

NameTable nt = new NameTable();

XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);

// nsManager.AddNamespace("p", presentationmlNamespace);

XmlDocument xwb = new XmlDocument(nt);

//create a xml document for workbook.xml

//I believe this is what every .xml must have

xwb.AppendChild(xwb.CreateXmlDeclaration("1.0", "UTF-8", "yes"));

//add the root element,

//I Believe there are better ways to handle namespace

//if you know, please tell me

XmlElement eleWorkbook = xwb.CreateElement("workbook");

eleWorkbook.SetAttribute(@"xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

eleWorkbook.SetAttribute(@"xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

eleWorkbook.AppendChild(xwb.CreateElement("sheets"));

XmlElement eleSheet = xwb.CreateElement("sheet");

eleSheet.SetAttribute("name", "Sheet1");

eleSheet.SetAttribute("sheetId", "1");

eleSheet.SetAttribute(@"id", "http://schemas.openxmlformats.org/officeDocument/2006/relationships", relId);

//write this .xml to workbookpart

Stream wbstream = wbPart.GetStream();

//do the same for worksheet .xml

XmlDocument xws = new XmlDocument();

xws.AppendChild(xws.CreateXmlDeclaration("1.0", "UTF-8", "yes"));

XmlElement eleWorksheet = xws.CreateElement("worksheet");

eleWorksheet.SetAttribute(@"xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");

eleWorksheet.SetAttribute(@"xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

XmlElement eleSheetData = xws.CreateElement("sheetData");

Stream wsstream = wsPart.GetStream();

//close it

Ankush

Hi Ankush,

Thank you for your useful reply so much.

I almost know nothing about C# 2.0

I do not know that .count() is extension method.

and also do not know using extension method need to import LINQ namespace.

Thank you again,

Maxi