Click here to Skip to main content
14,332,489 members

Generating Excel (XML Spreadsheet) in C#

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

Image 1

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

 
AnswerRe: why not XSLT? Pin
dekajp6-Oct-05 23:57
memberdekajp6-Oct-05 23:57 
GeneralRe: why not XSLT? Pin
JahBreeze6-Jul-07 5:23
memberJahBreeze6-Jul-07 5:23 
AnswerRe: why not XSLT? Pin
dekajp119-Dec-14 0:00
memberdekajp119-Dec-14 0:00 
Generalstringbuilder Pin
antidemon3-Oct-05 23:43
memberantidemon3-Oct-05 23:43 
GeneralRe: stringbuilder Pin
dekajp4-Oct-05 0:39
memberdekajp4-Oct-05 0:39 
Generalmake it more flexible Pin
Huisheng Chen27-Sep-05 4:09
memberHuisheng Chen27-Sep-05 4:09 
GeneralRe: make it more flexible Pin
dekajp27-Sep-05 18:35
memberdekajp27-Sep-05 18:35 
Generalother way (XLS) Pin
Jan Gex27-Sep-05 1:37
memberJan Gex27-Sep-05 1:37 
GeneralRe: other way (XLS) Pin
dekajp27-Sep-05 1:59
memberdekajp27-Sep-05 1:59 
GeneralEasier Pin
FZelle26-Sep-05 21:45
memberFZelle26-Sep-05 21:45 
GeneralRe: Easier Pin
dekajp27-Sep-05 1:52
memberdekajp27-Sep-05 1:52 
GeneralRe: Easier Pin
Huisheng Chen27-Sep-05 3:59
memberHuisheng Chen27-Sep-05 3:59 
GeneralNo Source, No Explanations Pin
delyk20-Sep-06 11:06
memberdelyk20-Sep-06 11:06 
GeneralRe: No Source, No Explanations Pin
dekajp120-Sep-06 15:30
memberdekajp120-Sep-06 15:30 
GeneralStringBuilder memory problem Pin
aawwaaaawwaa26-Sep-05 20:15
memberaawwaaaawwaa26-Sep-05 20:15 
GeneralRe: StringBuilder memory problem Pin
dekajp27-Sep-05 1:47
memberdekajp27-Sep-05 1:47 

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

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

Article
Posted 26 Sep 2005

Stats

332.2K views
8.3K downloads
98 bookmarked