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

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.

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