wordpress hit counter
OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Creating Spreadsheet document using OpenXML SDK

  • This post has 50 Replies |
  • 21 Followers
  • 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

  • Here is a HelloWorld example with WordprocessingML and SpreadsheetMl using OpenXML SDK 2:

    using System.Linq;

    using DocumentFormat.OpenXml;

    using DocumentFormat.OpenXml.Packaging;

    using DocumentFormat.OpenXml.Spreadsheet;

    using DocumentFormat.OpenXml.Wordprocessing;

    namespace Word_Excel_Dyn

    {

    class Program

    {

    static void Main(string[] args)

    {

    Program p = new Program();

    p.HelloWorldDocx("Hi_World.docx");

    p.HelloWorldXlsx("Hi_World.xlsx");

    }

    public void HelloWorldDocx(string docName)

    {

    // Create a Wordprocessing document.

    using (WordprocessingDocument package = WordprocessingDocument.Create(docName, WordprocessingDocumentType.Document))

    {

    // Add a new main document part.

    package.AddMainDocumentPart();

    // Create the Document DOM.

    package.MainDocumentPart.Document =

    new Document(

    new Body(

    new Paragraph(

    new DocumentFormat.OpenXml.Wordprocessing.Run(

    new DocumentFormat.OpenXml.Wordprocessing.Text("Hello World!")))));

    // Save changes to the main document part.

    package.MainDocumentPart.Document.Save();

    }

    }

    public void HelloWorldXlsx(string docName)

    {

    // Create a Wordprocessing document.

    using (SpreadsheetDocument package = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook))

    {

    // Add a new workbook part.

    package.AddWorkbookPart();

    package.WorkbookPart.Workbook = new Workbook();

    // Add a new worksheet part.

    package.WorkbookPart.AddNewPart<WorksheetPart>();

    //Create the Spreadsheet DOM.

    package.WorkbookPart.WorksheetParts.First().Worksheet =

    new Worksheet(

    new SheetData(

    new Row(

    new Cell(

    new InlineString(

    new DocumentFormat.OpenXml.Spreadsheet.Text("Hello World!"))) { DataType = CellValues.InlineString })));

     

    // Save changes to the spreadsheet part.

    package.WorkbookPart.WorksheetParts.First().Worksheet.Save();

    // create the worksheet to workbook relation

    package.WorkbookPart.Workbook.AppendChild(new Sheets());

    package.WorkbookPart.Workbook.GetFirstChild<Sheets>().AppendChild(new Sheet()

    {

    Id = package.WorkbookPart.GetIdOfPart(package.WorkbookPart.WorksheetParts.First()),

    SheetId = 1,

    Name = "Hello World!"

    });

    package.WorkbookPart.Workbook.Save();

    }

    }

    }

    }

Page 1 of 4 (51 items) 1234