How To generate a .xlsx using Open XML SDK without loading any .xml

Question

 

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"));

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

 

//do the same for worksheet .xml
XmlElement eleSheet = xwb.CreateElement("sheet");
eleSheet.SetAttribute("name", "Sheet1");
eleSheet.SetAttribute("sheetId", "1");
eleSheet.SetAttribute(@"r:id", relId);
eleWorkbook.FirstChild.AppendChild(eleSheet);

 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

Answer 1

 

With the new SDK you dont have to write the XML language on your own.... you still need to know what classes you have to invoke for generating that XML part though.

Use the dool "DocumentReflector" part of the SDK 2.0.. load an xlsx document into it and it will generate lots of code that would generate that document. There you can see that you dont need to write the XML elements on your own... instead you genearte corresponding objects for each element in the order they should be loaded...

 

Regards,

Kalpana

Answer 2

 

Thanks for the news.

I will try 2.0 and see if it helps.

if the only thing needs to be taking care of is the order will be fine by me.

Answer 3

 

Actually you needn't worry about the order too... because for eveyr object class like Sheet or Workbook constructors, all it needs is a list of OpenXmlElements... an array of them.

So all you should know is which set of OpenXmlElements you need to load for this object... for that you should have a fair idea of the ECMA spec of the ML you are going to generate...

 

Regards,

Kalpana

Answer 4

Dear Maxi,

 

I hope i have responded to your query in OpenXML developer forum too.

 

this sample does not need any thing apart from your code and SDK 1.0/2.0.

 

I read your post and realizes that you are worrying about loading XML and writting XML elements/nodes and speacially keeping any file at the server..

 

this sample will help you to generate the XLSX file on the fly as you wanted......

 

 

/// <summary>

        /// This method will help to create the spreadsheet at the server without Office existence

        /// </summary>

        /// <param name="path"></param>

        /// <param name="firstSheetName"></param>

        public void CreateSpreadsheet(string path, string firstSheetName)

        {

 

            using (SpreadsheetDocument doc = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))

            {

                //Add the workbook

                WorkbookPart workbook = doc.AddWorkbookPart();                              

 

                //Create a worksheet

                WorksheetPart sheet = workbook.AddNewPart<WorksheetPart>();

 

                //Get the relationship id so the workbook and worksheet can be related

                string sheetId = workbook.GetIdOfPart(sheet);

 

                //Create the workbook

                string XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><workbook xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships""><sheets><sheet name=""{1}"" sheetId=""1"" r:id=""{0}"" /></sheets></workbook>";

                XML=string.Format(XML, sheetId, firstSheetName);

                this.AddPartXml(workbook, XML);

               

                XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><worksheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" ><sheetData/></worksheet>";

                this.AddPartXml(sheet, XML);

 

                //Create the shared strings part

                SharedStringTablePart stringTable = workbook.AddNewPart<SharedStringTablePart>();

 

                XML = @"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?><sst xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main""></sst>";

                this.AddPartXml(stringTable, XML);

 

                doc.Close();

 

            }

 

        }

 

        /// <summary>

        /// this method will be called from CreateSpreadsheet

        /// </summary>

        /// <param name="part"></param>

        /// <param name="xml"></param>

        protected void AddPartXml(OpenXmlPart part, string xml)

        {

            using (Stream stream = part.GetStream())

            {

                byte[] buffer = (new UTF8Encoding()).GetBytes(xml);

                stream.Write(buffer, 0, buffer.Length);

            }

       }

 

 

Cheers.