Hot File

Generating Excel (XML Spreadsheet) in C#

View: 2426    Dowload: 2   Comment: 0   Post by: naruto  
Author: none   Category: MS Office   Fields: Other

9 point/8 review File has been tested

Download   generating-excel-xml.rar (25.63 KB)

You need to Sign In to download the file generating-excel-xml.rar
If you do not have an account then Sign up for free here

Download error   Report copyright violation

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.

Introduction

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>

Generating Excel (XML Spreadsheet) in C#

Generating Excel (XML Spreadsheet) in C# Posted on 12-05-2014  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. 1.125/10 2426

Comment:

To comment you must be logged in members.

Files with category

  • Document Library - Enable New Document, Edit Document Features

    View: 1872    Download: 0   Comment: 0   Author: none  

    Document Library - Enable New Document, Edit Document Features

    Category: MS Office
    Fields: Other

    0/5 review
    In this article we can explore the underlying aspects involved in enabling the New Document feature in a Document Library.

  • Generating Excel (XML Spreadsheet) in C#

    View: 2426    Download: 2   Comment: 0   Author: none  

    Generating Excel (XML Spreadsheet) in C#

    Category: MS Office
    Fields: Other

    0.5625/8 review
    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...

  • Searching - Human Way and Index Way

    View: 1942    Download: 0   Comment: 0   Author: none  

    Searching - Human Way and Index Way

    Category: MS Office
    Fields: Other

    0.093023255813953/86 review
    It was really an art to optimize the data retrieval from database and data retrieval optimization is part of every developer's life. Indexes are one of the best ways of Optimization that SQL Servers provide. Understanding indexes some time may be...

  • Creating Pivot Tables and Charts Using ActiveX

    View: 2507    Download: 0   Comment: 0   Author: none  

    Creating Pivot Tables and Charts Using ActiveX

    Category: MS Office
    Fields: none

    1.125/4 review
    Most of us (developers) use the “Generate Excel” feature in our applications. This allows us to generate an Excel copy on any of our reports. Most of us are also familiar with “Pivot Table” and “Pivot Charts”. This feature from MS Excel allows the...

  • Automating MS Excel Documents

    View: 1847    Download: 0   Comment: 0   Author: none  

    Automating MS Excel Documents

    Category: MS Office
    Fields: Other

    0/3 review
    I have been working on automating MS Office applications since some time. I have seen that it is very easy to find information on automation of MS Word but it is difficult to find information on automation of PowerPoint or Excel. So I decided to...

  • Embedding PowerPoint presentation player into a WPF application

    View: 952    Download: 0   Comment: 0   Author: none  

    Embedding PowerPoint presentation player into a WPF application

    Category: MS Office
    Fields: Other

    0/2 review
    On one occasion I came across an interesting and, I can even say, challenging task of building a customized player of PowerPoint presentations. This task emerged as part of a project which my teammates and I developed at Reliable Systems. Our...

  • Parallel Processing MS Access VBA Application

    View: 921    Download: 0   Comment: 0   Author: none  

    Parallel Processing MS Access VBA Application

    Category: MS Office
    Fields: Other

    0/1 review
    I got a challenging assignment to provide alarm capability on top of an existing application, written in VBA Access. This required a few issues to be dealt with and resolved. I will, in this article, describe the steps I took to provide parallel...

  • Microsoft Office XML formats, defective by design?

    View: 3477    Download: 0   Comment: 0   Author: none  

    Microsoft Office XML formats, defective by design?

    Category: MS Office
    Fields: Other

    0/549 review
    Microsoft is trying to push new file formats that are using Zip and XML. Are those new file formats any good for Office developers? In other words, should anyone feel safe to make direct access to file parts, and start getting free of running...

 
Newsletter Email

File suggestion for you

File top downloads

logo codetitle
Codetitle.com - library source code to share, download the file to the community
Copyright © 2015. All rights reserved. codetitle.com Develope by Vinagon .Ltd