Generating Excel (XML Spreadsheet) in C#

dekajp

3.54/5 (34 votes)

Sep 27, 2005

2 min read

361269

8631

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

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