Click here to Skip to main content
Licence 
First Posted 26 Sep 2005
Views 208,397
Bookmarked 90 times

Generating Excel (XML Spreadsheet) in C#

By | 9 Oct 2005 | Article
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

About the Author

dekajp



United States United States

Member

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.

Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board. (secure sign-in)
 
Search this forum  
 FAQ
    Noise  Layout  Per page   
  Refresh
GeneralWhy don't you write directly to file?! Pinmemberibogi22:46 15 Feb '11  
GeneralMy vote of 4 Pinmembermazmoiz5:49 25 Sep '10  
GeneralMy vote of 1 Pinmemberbouleanu23:37 13 Jul '10  
GeneralMy vote of 1 Pinmembertaptaptap11:14 19 Aug '09  
GeneralRe: My vote of 1 Pinmemberbrip20:16 6 Sep '09  
GeneralOpening in Excel without saving Pinmembershyamal17077511:32 25 Mar '09  
Questiongenerating excel compatible with excel 2000 and excel 2003 Pinmemberanithasrinath20:11 23 Dec '08  
AnswerRe: generating excel compatible with excel 2000 and excel 2003 [modified] PinmemberFilipKrnjic1:34 8 Jul '09  
GeneralExcelxmlspreadsheet PinmemberMember 32303881:57 27 Aug '08  
GeneralRe: Excelxmlspreadsheet PinmemberCikaPero22:41 28 Mar '10  
Generalcreating and using dropdownlist in excel in asp.net [modified] Pinmembers.mehrait7:58 10 Oct '07  
GeneralRe: creating and using dropdownlist in excel in asp.net Pinmemberdekajp17:29 11 Oct '07  
GeneralStrange caracter PinmemberSalam Y. ELIAS1:44 7 Mar '06  
GeneralRe: Strange caracter PinmemberSalam Y. ELIAS1:46 7 Mar '06  
GeneralRe: Strange caracter Pinmemberdekajp15:48 7 Mar '06  
GeneralRe: Strange caracter PinmemberSalam Y. ELIAS17:09 7 Mar '06  
Generaloffice 2000 Pinmembercarlito punk8:37 10 Jan '06  
GeneralRe: office 2000 Pinmemberdekajp19:40 12 Jan '06  
GeneralRe: office 2000 Pinmembercarlito punk4:12 13 Jan '06  
GeneralRe: office 2000 Pinmemberlizrd8255:27 13 Jun '07  
GeneralRe: office 2000 Pinmemberlizrd8255:44 13 Jun '07  
Questionwhy not XSLT? PinmemberCyclopsTheOneEyedGuy20:26 5 Oct '05  
AnswerRe: why not XSLT? Pinsussdekajp23:57 6 Oct '05  
GeneralRe: why not XSLT? PinmemberJahBreeze5:23 6 Jul '07  
Generalstringbuilder Pinmemberantidemon23:43 3 Oct '05  

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.

Permalink | Advertise | Privacy | Mobile
Web04 | 2.5.120517.1 | Last Updated 10 Oct 2005
Article Copyright 2005 by dekajp
Everything else Copyright © CodeProject, 1999-2012
Terms of Use
Layout: fixed | fluid