Click here to Skip to main content
Click here to Skip to main content

Generating Excel (XML Spreadsheet) in C#

, 9 Oct 2005
Rate this:
Please Sign up or sign in to vote.
Create an Excel XML spreadsheet using simple string manipulations.

Recently, I was working on a web based report in ASP.NET. The report had to be generated in MS Excel format and the data was too much. To give you an idea, I had to generate more than 300 worksheets and the file size was more than 5 MB. I tried the Office PIAs but I was not happy with the Excel object. One day while I was playing with Excel trying to save data, I came across an option "XML Spreadsheet(*.xml)" in Office and after some careful observation of the XML code generated by Excel, I decided to generate the XML string using C# and save the content as a .xls file.

The structure of a XML spreadsheet

<Workbook>
    <Styles>
    </Styles>
    <Worksheet ss:Name="Sheet1">
        <Table>
            <Row>
                <Cell>Data</Cell>
                <Cell>Data</Cell>
            </Row>
        </Table> 
        <WorksheetOptions> </WorksheetOptions>
    </Worksheet> 
    <Worksheet ss:Name="Sheet2">
        <Table>
            <Row>
                <Cell>Data</Cell>
                <Cell>Data</Cell>
            </Row>
        </Table> 
    </Worksheet> 
    ......
    ...... 
    ...... 
    ...... 
<Workbook>

Using the code

The Excel header function returns the header of the file.

/// <summary>
/// Creates Excel Header 
/// </summary>
/// <returns>Excel Header Strings</returns>
private string ExcelHeader()

{
    // Excel header
    System.Text.StringBuilder sb = new System.Text.StringBuilder();
    sb.Append("<?xml version=\"1.0\"?>\n");
    sb.Append("<?mso-application progid=\"Excel.Sheet\"?>\n");
    sb.Append(
      "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
    sb.Append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
    sb.Append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
    sb.Append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
    sb.Append("xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n");
    sb.Append(
      "<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
    sb.Append("</DocumentProperties>");
    sb.Append(
      "<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">\n");
    sb.Append("<ProtectStructure>False</ProtectStructure>\n");
    sb.Append("<ProtectWindows>False</ProtectWindows>\n");
    sb.Append("</ExcelWorkbook>\n");
    return sb.ToString(); 
}

The main function to generate Excel worksheets using String Builder:

//First Write the Excel Header
strExcelXml.Append(ExcelHeader());
// Get all the Styles
strExcelXml.Append(ExcelStyles ("styles.config"));

// Create First Worksheet
strExcelXml.Append(WriteFirstWorkSheet());
// Worksheet options Required only one time 
strExcelXml.Append(ExcelWorkSheetOptions()); 

for(int i=1;i<iWorkSheet;i++)
{
    // Create First Worksheet tag
    strExcelXml.Append(
        "<Worksheet ss:Name=\"WorkSheet"+i.ToString()+"\">");
    // Then Table Tag
    strExcelXml.Append("<Table>");
    for(int k=1;k<iRow;k++)
    {
        // Row Tag
        strExcelXml.Append("<tr>");
        for(int j=1;j<iCol;j++)
        {
            // Cell Tags
            strExcelXml.Append("<td>");
            strExcelXml.Append(
              "Sheet"+i.ToString()+"Row"+k.ToString()+"Col"+j.ToString()); 
            strExcelXml.Append("</td>");
        }
        strExcelXml.Append("</tr>");
    }
    strExcelXml.Append("</Table>");
    strExcelXml.Append("</Worksheet>"); 
}
// Close the Workbook tag (in Excel header 
// you can see the Workbook tag)
strExcelXml.Append("</Workbook>\n");

Points of interest

The test application may take more time if there are more than 100 sheets. To improve on this you need to do a lot of string manipulations (concatenation, find and replace). As the final string grows in size, concatenation and find & replace methods start taking time. So you need to be a little careful while dealing with huge reports.

One can generate various styles like bold, italics, hyperlink, column merge etc. in the report. What Excel does is it generates a Style tag at the top of the file and keeps it for future use. A sample of the Style tag is shown below:

<Styles>
    <Style ss:ID="Default" ss:Name="Normal">
        <Alignment ss:Vertical="Bottom"/>
        <Borders/>
        <Font/>
        <Interior/>
        <NumberFormat/>
        <Protection/>
    </Style>
    <Style ss:ID="s27" ss:Name="Hyperlink">
        <Font ss:Color="#0000FF" ss:Underline="Single"/>
    </Style>
    <Style ss:ID="s24">
        <Font x:Family="Swiss" ss:Bold="1"/>
    </Style>
    <Style ss:ID="s25">
        <Font x:Family="Swiss" ss:Italic="1"/>
    </Style>
    <Style ss:ID="s26">
        <Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
    </Style>
</Styles>

Conclusion

Using the above methods, we can generate high end Excel files by simply using string manipulations. This gives you freedom from Office PIA's which are heavy, memory hungry and system dependent.

Version history

  • 10th Oct, 2005: Last modified.
  • Upgraded the string concatenation using the StringBuilder class and there is a huge performance enhancement. Please refer to this article.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

dekajp

United States United States
Hi I am Jyoti Prakash Deka From Assam/India . Basically I am an Electrical Engg. Currently working for Software Consultancy firm in Bangalore/India.
 
I am working in Microsoft Technologies .NET ,Asp.net,MS Sql server,C#.
 
I can be reached at dekajp@yahoo.com.

Comments and Discussions

 
QuestionI am not able to open the file in Mac machine. it comes as blank. Pinmemberhishhash4-Nov-12 19:58 
GeneralMy vote of 1 Pinmemberlomo745-Sep-12 5:23 
GeneralWhy don't you write directly to file?! Pinmemberibogi15-Feb-11 22:46 
GeneralMy vote of 4 Pinmembermazmoiz25-Sep-10 5:49 
GeneralMy vote of 1 Pinmemberbouleanu13-Jul-10 23:37 
GeneralMy vote of 1 Pinmembertaptaptap19-Aug-09 11:14 
GeneralRe: My vote of 1 Pinmemberbrip6-Sep-09 20:16 
GeneralOpening in Excel without saving Pinmembershyamal17077525-Mar-09 11:32 
Questiongenerating excel compatible with excel 2000 and excel 2003 Pinmemberanithasrinath23-Dec-08 20:11 
AnswerRe: generating excel compatible with excel 2000 and excel 2003 [modified] PinmemberFilipKrnjic8-Jul-09 1:34 
GeneralExcelxmlspreadsheet PinmemberMember 323038827-Aug-08 1:57 
GeneralRe: Excelxmlspreadsheet PinmemberCikaPero28-Mar-10 22:41 
Generalcreating and using dropdownlist in excel in asp.net [modified] Pinmembers.mehrait10-Oct-07 7:58 
GeneralRe: creating and using dropdownlist in excel in asp.net Pinmemberdekajp11-Oct-07 17:29 
GeneralStrange caracter PinmemberSalam Y. ELIAS7-Mar-06 1:44 
GeneralRe: Strange caracter PinmemberSalam Y. ELIAS7-Mar-06 1:46 
GeneralRe: Strange caracter Pinmemberdekajp7-Mar-06 15:48 
GeneralRe: Strange caracter PinmemberSalam Y. ELIAS7-Mar-06 17:09 
Generaloffice 2000 Pinmembercarlito punk10-Jan-06 8:37 
GeneralRe: office 2000 Pinmemberdekajp12-Jan-06 19:40 
GeneralRe: office 2000 Pinmembercarlito punk13-Jan-06 4:12 
GeneralRe: office 2000 Pinmemberlizrd82513-Jun-07 5:27 
GeneralRe: office 2000 Pinmemberlizrd82513-Jun-07 5:44 
Questionwhy not XSLT? PinmemberCyclopsTheOneEyedGuy5-Oct-05 20:26 
AnswerRe: why not XSLT? Pinsussdekajp6-Oct-05 23:57 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 10 Oct 2005
Article Copyright 2005 by dekajp
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid