6

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

Improve this question
6
  • 1
    You could have a template spreadsheet and just copy it, if a template would be ideal of course. Commented Sep 21, 2013 at 7:45
  • Most of the time we just create a base speadsheet and change the it with XSLT. Commented Dec 22, 2014 at 12:16
  • I highly recommend using something like ClosedXML. Trying to use the OpenXML API is awful. Commented Sep 3, 2015 at 17:34
  • @aquinas at the end i used the same API(ClosedXML).... Commented Sep 4, 2015 at 5:50
  • If want to use OpenXml API, there is a great guide here: dispatchertimer.com/tutorial/… Commented Feb 5, 2016 at 13:25

2 Answers 2

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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

Improve this answer

8 Comments

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>
for exporting, do i need to create xml format first and then export that into excel sheet .. is that way correct...
I missunderstood that you had an xml document and wanted it put into excel.
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.
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 ...
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.