steps to create excel XML spreadsheet using c#

Question

We have got a requirement like exporting data into excel sheet in Xml Format like creating a new XML SpreadSheet I have followed this link for creating excel xml Spreadsheet. In this link he has mentioned sample

< ?xml version="1.0"?>
< ?mso-application progid="Excel.Sheet"?>
<workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<documentproperties xmlns="urn:schemas-microsoft-com:office:office">
<author>Author</author>
<lastauthor>LastAuthor</lastauthor>
<created>11-09-2007</created>
<version>12.00</version>
</documentproperties>
<excelworkbook xmlns="urn:schemas-microsoft-com:office:excel">
<protectstructure>False</protectstructure>
<protectwindows>False</protectwindows>
</excelworkbook>
</workbook>

where I need to define this format in c# project, In the above code i need to get the information about author and last author need to bind from database ....

in that link he hasn't mentioned completely for creating document...

If I want to create a ExcelXml spread sheet what are steps that i need to follow, do i need to create a predefined format that will be stored in project...

we are able to access the open XML sdk, but i dint find any sample solutions for creating xml format inside excel spreadsheet, is it possible to do same thing with open XML SDK, and if it is possible would you pls pointed me in right direction...

would any one has any ideas and any solutions that would be very grateful to me ....

Many Thanks in advance

You could have a template spreadsheet and just copy it, if a template would be ideal of course. — Paul Zahra, Sep 21 '13 at 07:45
Most of the time we just create a base speadsheet and change the it with XSLT. — Kees C. Bakker, Dec 22 '14 at 12:16
I highly recommend using something like ClosedXML. Trying to use the OpenXML API is awful. — aquinas, Sep 03 '15 at 17:34
If want to use OpenXml API, there is a great guide here: http://www.dispatchertimer.com/tutorial/how-to-create-an-excel-file-in-net-using-openxml-part-1-basics/ — Daniel Stackenland, Feb 05 '16 at 13:25
Or are your question really about how to set documentproperties like author, created etc?? — Daniel Stackenland, Feb 05 '16 at 13:34

score 2 · Answer 1 · answered May 07 '16 at 10:55

You can use OpenXml SDK for this task.

Using the OpenXml SDK directly is not easy, for a simple application you are better off using a wrapper.

Take a look at the JumboExcel project (disclosure: I'm the author).

Creating a spreadsheet is as easy as following:

var tempFileName = Path.Combine(Path.GetTempPath(), Guid.NewGuid() + ".xlsx");
using (var file = new FileStream(tempFileName, FileMode.CreateNew))
{
    OpenXmlBuilder.Write(
        file, 
        new[] {
            new Worksheet(
                "Parameters",
                null,
                new Row(new InlineString("Name"), new InlineString("Value")),
                new Row(new InlineString("Height"), new DecimalCell(123m))
            )
        }
    );
}
Process.Start(tempFileName);

Also, you can explore the sources see the sources at Github page and take a look at DemoTests for more examples.

Paul Zahra · Answer 2 · 2013-09-21T09:02:17.970

1

Failing a template try the following taken from here

using System.IO;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static void CreateSpreadsheetWorkbook(string filepath)
    {
        // Create a spreadsheet document by supplying the filepath.
        // By default, AutoSave = true, Editable = true, and Type = xlsx.

        SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

        // Add a WorkbookPart to the document.
        WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
        workbookpart.Workbook = new Workbook();

        // Add a WorksheetPart to the WorkbookPart.
        WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
        worksheetPart.Worksheet = new Worksheet(new SheetData());

        // Add Sheets to the Workbook.
        Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());

        // Append a new worksheet and associate it with the workbook.
        Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" };
        sheets.Append(sheet);

        workbookpart.Workbook.Save();

        // Close the document.
        spreadsheetDocument.Close();
    }

// Called using
CreateSpreadsheetWorkbook("C:\\Test\\Test.xlsx");

EDIT: You could convert the xml to excel using the following code:

Workbook workbook = new Workbook(); 
workbook.LoadFromFile(@"../../Data/test.xml"); 
workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);

If you want to actually create an Office XML doc, I'm unsure as to how to automate that process from an xml file. Have a look at this for some pointers

edited Sep 21 '13 at 09:02

answered Sep 21 '13 at 07:48

Paul Zahra

9,522
8
54
76

thanks for that , one doubt is like where i need to put all these ones is it in c# class file or is it in xml file ... ` ` – Glory Raj Sep 21 '13 at 09:08
for exporting, do i need to create xml format first and then export that into excel sheet .. is that way correct... – Glory Raj Sep 21 '13 at 09:19
I missunderstood that you had an xml document and wanted it put into excel. – Paul Zahra Sep 21 '13 at 09:27
You want to actually write xml into an excel file? into just one cell? One point you say import xml DATA into spreadsheet, and now it seems you want the actual XML in the spreadsheet? Two very different things. – Paul Zahra Sep 21 '13 at 09:32
sorry for confusing i want actual xml in spread sheet .. i have got data coming form database .. like emp name and emp id... how can i do that ... – Glory Raj Sep 21 '13 at 09:42
correction for the above lines `I need to create a xml format first and then it is need to exported to excel as per requirement..` in this case how can i do I am using `asp.net mvc4` with `c#` .... – Glory Raj Sep 21 '13 at 09:49
You could try using the Excel Helper Library http://ehl.codeplex.com/ but I would say that inserting actual XML into an excel spreadsheet is a strange idea, the xml tags would be interpreted by excel and do odd things. – Paul Zahra Sep 21 '13 at 10:10
not inserting actual xml and i need to use xml format for exporting to excel.. like this http://stackoverflow.com/questions/15691910/mvc-4-export-to-excel-failing?rq=1 – Glory Raj Sep 21 '13 at 10:13

steps to create excel XML spreadsheet using c#

2 Answers2