Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#
Article

Generating Excel (XML Spreadsheet) in C#

Rate me:
Please Sign up or sign in to vote.
3.54/5 (34 votes)
9 Oct 20052 min read 358K   8.6K   99   41
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

XML
<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.

C#
/// <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:

C#
//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:

XML
<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


Written By
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

 
QuestionHow to change the format to ".xlsx" Pin
Thanveer Ahamed2-Sep-16 21:40
Thanveer Ahamed2-Sep-16 21:40 
QuestionI am not able to open the file in Mac machine. it comes as blank. Pin
hishhash4-Nov-12 19:58
hishhash4-Nov-12 19:58 
GeneralMy vote of 1 Pin
lomo745-Sep-12 5:23
lomo745-Sep-12 5:23 
GeneralWhy don't you write directly to file?! Pin
ibogi15-Feb-11 22:46
ibogi15-Feb-11 22:46 
Why don't you write directly to file?!
GeneralMy vote of 4 Pin
mazmoiz25-Sep-10 5:49
mazmoiz25-Sep-10 5:49 
GeneralMy vote of 1 Pin
bouleanu13-Jul-10 23:37
bouleanu13-Jul-10 23:37 
GeneralMy vote of 1 Pin
taptaptap19-Aug-09 11:14
taptaptap19-Aug-09 11:14 
GeneralRe: My vote of 1 Pin
brip6-Sep-09 20:16
brip6-Sep-09 20:16 
GeneralOpening in Excel without saving Pin
shyamal17077525-Mar-09 11:32
shyamal17077525-Mar-09 11:32 
Questiongenerating excel compatible with excel 2000 and excel 2003 Pin
anithasrinath23-Dec-08 20:11
anithasrinath23-Dec-08 20:11 
AnswerRe: generating excel compatible with excel 2000 and excel 2003 [modified] Pin
FilipKrnjic8-Jul-09 1:34
FilipKrnjic8-Jul-09 1:34 
GeneralExcelxmlspreadsheet Pin
Member 323038827-Aug-08 1:57
Member 323038827-Aug-08 1:57 
GeneralRe: Excelxmlspreadsheet Pin
CikaPero28-Mar-10 22:41
CikaPero28-Mar-10 22:41 
Generalcreating and using dropdownlist in excel in asp.net [modified] Pin
s.mehrait10-Oct-07 7:58
s.mehrait10-Oct-07 7:58 
GeneralRe: creating and using dropdownlist in excel in asp.net Pin
dekajp11-Oct-07 17:29
dekajp11-Oct-07 17:29 
GeneralStrange caracter Pin
Salam Y. ELIAS7-Mar-06 1:44
professionalSalam Y. ELIAS7-Mar-06 1:44 
GeneralRe: Strange caracter Pin
Salam Y. ELIAS7-Mar-06 1:46
professionalSalam Y. ELIAS7-Mar-06 1:46 
GeneralRe: Strange caracter Pin
dekajp7-Mar-06 15:48
dekajp7-Mar-06 15:48 
GeneralRe: Strange caracter Pin
Salam Y. ELIAS7-Mar-06 17:09
professionalSalam Y. ELIAS7-Mar-06 17:09 
Generaloffice 2000 Pin
carlito punk10-Jan-06 8:37
carlito punk10-Jan-06 8:37 
GeneralRe: office 2000 Pin
dekajp12-Jan-06 19:40
dekajp12-Jan-06 19:40 
GeneralRe: office 2000 Pin
carlito punk13-Jan-06 4:12
carlito punk13-Jan-06 4:12 
GeneralRe: office 2000 Pin
lizrd82513-Jun-07 5:27
lizrd82513-Jun-07 5:27 
GeneralRe: office 2000 Pin
lizrd82513-Jun-07 5:44
lizrd82513-Jun-07 5:44 
Questionwhy not XSLT? Pin
5-Oct-05 20:26
suss5-Oct-05 20:26 

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.