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

Pastille

You could have a template spreadsheet and just copy it, if a template would be ideal of course. – Wadley

Most of the time we just create a base speadsheet and change the it with XSLT. – Fifth

I highly recommend using something like ClosedXML. Trying to use the OpenXML API is awful. – Cacology

@Cacology at the end i used the same API(ClosedXML).... – Pastille

If want to use OpenXml API, there is a great guide here: dispatchertimer.com/tutorial/… – Unrefined

Or are your question really about how to set documentproperties like author, created etc?? – Unrefined

Answer 1

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.

Ist

Answer 2

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

Wadley

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 ... <?xml version="1.0"?> <ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> <ss:Worksheet ss: Name="Sheet1"> <ss:Table> </ss:Table> </ss:Worksheet> </ss:Workbook> – Pastille

for exporting, do i need to create xml format first and then export that into excel sheet .. is that way correct... – Pastille

I missunderstood that you had an xml document and wanted it put into excel. – Wadley

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. – Wadley

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 ... – Pastille

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# .... – Pastille

You could try using the Excel Helper Library 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. – Wadley

not inserting actual xml and i need to use xml format for exporting to excel.. like this #15692410 – Pastille