These are the steps to create a spreadsheet document using OpenXML SDK

1. Add a reference to Microsoft.Office.DocumentFormat.OpenXml (from .Net tab, provided you have installed SDK)

2. use the namespace

using Microsoft.Office.DocumentFormat.OpenXml.Packaging;

3. Create workbook document and add workbook and worksheet

SpreadsheetDocument spDoc = SpreadsheetDocument.Create(@"c:\testnow.xlsx", SpreadsheetDocumentType.Workbook);
WorkbookPart wbPart = spDoc.AddWorkbookPart();
WorksheetPart wsPart = wbPart.AddNewPart<WorksheetPart>();

4. generate the id for sheet

string relId = wbPart.GetIdOfPart(wsPart);

5. Either create workbook.xml and sheet1.xml file (any file name) or load the existing file. Here, I am loading the existing file

XmlDocument xwb = new XmlDocument();
xwb.Load(@"..\..\workbook.xml");

6. Locate the sheet element and set the r;id attribute to the one we generated in the step 4

XmlNamespaceManager nsManager = new XmlNamespaceManager(xwb.NameTable);
            nsManager.AddNamespace("default", xwb.DocumentElement.NamespaceURI);
            XmlNodeList nodelist = xwb.SelectNodes("//default:sheets/default:sheet", nsManager);

            foreach (XmlNode node in nodelist)
            {
                string sheetName = node.Attributes["name"].Value;
                if (sheetName == "Sheet1")
                {
                    node.Attributes["r:id"].Value = relId;
                }

            }

7. Load the modified xml file into the workbook part
Stream stream = wbPart.GetStream();
xwb.Save(stream);

8. Load sheet1.xml file in to the worksheet part

 XmlDocument xsheet=new XmlDocument();
 xsheet.Load(@"..\..\sheet1.xml");
 Stream stream1 = wsPart.GetStream();
 xsheet.Save(stream1);

6. Close the document handler

 spDoc.Close();

 

This is what I've been searching for, for 2 days. Thank you so much!

Hi Sheela, I have 2 questions I hope you can help with.

 

1. If want to create a completely new .xlsx document with a blank worksheet, how can I do this? How do steps 5-6 change?

2. My end goal is to save my SQL-generated xml data file as a spreadsheet in Excel. When manually opening the xml data file in Excel, I am asked a "How do you want to open this xml" question. What would be the easiest way to do this programatically, (a) Follow your steps to make a blank document, save it, then somehow import the data into the document, and save it? Or (b) Follow your steps up to a point, but then break apart my xml data document and fill the cells myself.

 

 

Thank you very much for your help.

-ak

 

 

Hi,

Very sorry for the late reply

1. Step 6, 7remains same, in the step 8, load the sheet1.xml which is blank

i.e

<?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">

  <sheetData><!--no data-->

  </sheetData>

</worksheet>

 

2. Not very clear about the problen defn. As per my understanding
apply XSLT to the xml file generated from database to make OOXML compliant. The same can be loaded to worksheet part

Sheela

ak47:

My end goal is to save my SQL-generated xml data file as a spreadsheet in Excel. 

Hello AK,

I hope all is fine.

You can easily achieve your end goal to save/export your SQL-generated Excel reports in XLSX, XLS or SpreadsheetML format by using Aspose.Cells for Reporting Services.

You can download the evaluation version and its easy-to-understand documentation will definitely help you to do more than you are expecting, I suppose.

Have a good day.

Usman Sarfraz

Here you are using the file system to load / save your xml files, what if we have the xml in memory, and want to keep the filesystem out of it, how to we control the filename of the part?

Hi,

Either way, you can not control the filename of the part as we do using in System.Io.Packaging API.

The filename of the parts are predefined (ex for first sheet, it takes as sheet1.xml, for workbook, it takes as workbook.xml).

SDK behaviour is same as word 2007 editor.

Sheela

 

None of this works and it does not make sense. What is the point in calling WorkbookPart.AddPart<WorksheetPart>() before loading worksheet.xml.

I believe we can do the same without loading any ,xml
I don't think loading .xml is the best approach.
Can you do a version without loading .xml?

and btw, you load a workbook.xml,
what is inside this workbook.xml?

Dear maxi,

what do yu mean by version, i dont understand.

how you can update the XML without loading it.

the workbook.XML contains the framework for the workbook which is predefined format by OpenXML.

workbook.XML contains the information aboutall the entities this workbook will carry like Definition of all the worksheets and relationship, Definition of all the defined names, formula Calculation mode, protection attributes and many more......

Regards

Vikas

Dear Vikassony,

What I mean is a source code that generate a .xlsx without using any externel .xml source.

I understand workbook.xml have a schema defined. What I want to do is generate a minimal workbook.xml by coding, not by XmlDocument.Load(".xml in your server"), even if that .xml is minimal. and then I will add XmlElement programmatically.

if you load workbook.xml and sheet.xml, the relationship Id is hard coded inside them.

to put my question in another way, I want to generate .xlsx using only coding.

you can see my post http://openxmldeveloper.org/forums/3675/ShowThread.aspx#3675

Dear Maxi,

In software to implement one flow there can be 10 or more logics.

the objective is to add the base part in all the worksheets/workbook/sharedstring/calcchain

so those scema part either you add by hardcoding or by loading an XML its upto you.

for your reference i am giving you a code sample.

in this code i am adding the XML by reading  an Existing XML which you can hard code with a string too and pass to the part.

what i preffered instead of hardcoding a string i have kept the dummy XML files one for each (workbook/worksheet/sharedstring), just read and add that into the part.

the dummy XML contains the only xml which i have commented below.

 

now you can create the XLSX file only for writting few lines of code.

in next few days you can check my blog with workable solution

http://vikas2tech.spaces.live.com

 btw your problem i have tested by below code i works very well.

public void CreateSpreadsheet(string path, string firstSheetName)

{

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

{

//Add the workbook

WorkbookPart workbook = doc.AddWorkbookPart();

 

//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, this.ReadXML(@"Templates\SharedStringTemplate.xml"));

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

this.AddPartXml(workbook, this.WorkbookXml(sheetId, firstSheetName));

/*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>*/

this.AddPartXml(sheet, this.ReadXML(@"Templates\WorkSheetTemplate.xml"));

doc.Close();

}

}

 

protected void AddPartXml(OpenXmlPart part, string xml)

{

using (Stream stream = part.GetStream())

{

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

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

}

}

 

 

Vikas

 

thnx sheela, it helps so much

You could store the xml template in a database.

hth
Mat