Click here to Skip to main content
11,929,675 members (55,187 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


1 download
87 bookmarked

Output DataSet to Excel

, 21 Jan 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
Allows for outputting multiple tables, and marking text columns appropriately. Uses Excel's XML format.


I did this when I wanted to do a quick export of an entire DataSet (multiple tables) to Excel. I didn't add any additional customization to the fields, but I did want to make sure that dates, boolean, numbers, and text were all formatted correctly.

This code does that.

At some point, I'd like to make a GridView type component that would allow me to detail more about each item. For example, my latest project required me to make a column formatted with a given barcode font ("Free 3 of 9") that required that I put an * before and after the item number. The solution below doesn't make this easy to do, though... So yeah, not perfect. If anyone else has done something like this, let me know Smile | :)

For importing Excel to XML, see this post.

NOTE: This method does NOT require Excel to be installed on the Server.


I prefer to see each table in the DataSet to be named.

ds.Tables[0].TableName = "Colors";
ds.Tables[1].TableName = "Shapes";

I changed it to allow you to pass in a List<Table> in case you don't put them in a DataSet. No big deal either way.

Why did I use an XmlTextWriter when I seem to be only using the WriteRaw? I wanted to be able to have it fix any special characters with the "x.WriteString(row[i].ToString());". Note, this still may have problems with certain characters, since I haven't tested it much.

Using the Code

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Xml;

public void Convert(DataSet ds, string fileName) {
    Convert(ds.Tables, fileName);
public void Convert(IEnumerable tables, string fileName) {
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/";
             "attachment; filename=" + fileName + ".xls");

    using (XmlTextWriter x = new XmlTextWriter(Response.OutputStream, Encoding.UTF8)) {
        int sheetNumber = 0;
        x.WriteRaw("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>");
        x.WriteRaw("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
        x.WriteRaw("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
        x.WriteRaw("<Styles><Style ss:ID='sText'>" + 
                   "<NumberFormat ss:Format='@'/></Style>");
        x.WriteRaw("<Style ss:ID='sDate'><NumberFormat" + 
                   " ss:Format='[$-409]m/d/yy\\ h:mm\\ AM/PM;@'/>");
        foreach (DataTable dt in tables) {
            string sheetName = !string.IsNullOrEmpty(dt.TableName) ? 
                   dt.TableName : "Sheet" + sheetNumber.ToString();
            x.WriteRaw("<Worksheet ss:Name='" + sheetName + "'>");
            string[] columnTypes = new string[dt.Columns.Count];

            for (int i = 0; i < dt.Columns.Count; i++) {
                string colType = dt.Columns[i].DataType.ToString().ToLower();

                if (colType.Contains("datetime")) {
                    columnTypes[i] = "DateTime";
                    x.WriteRaw("<Column ss:StyleID='sDate'/>");

                } else if (colType.Contains("string")) {
                    columnTypes[i] = "String";
                    x.WriteRaw("<Column ss:StyleID='sText'/>");

                } else {
                    x.WriteRaw("<Column />");

                    if (colType.Contains("boolean")) {
                        columnTypes[i] = "Boolean";
                    } else {
                        //default is some kind of number.
                        columnTypes[i] = "Number";

            //column headers
            foreach (DataColumn col in dt.Columns) {
                x.WriteRaw("<Cell ss:StyleID='sText'><Data ss:Type='String'>");
            bool missedNullColumn = false;
            foreach (DataRow row in dt.Rows) {
                for (int i = 0; i < dt.Columns.Count; i++) {
                    if (!row.IsNull(i)) {
                        if (missedNullColumn) {
                            int displayIndex = i + 1;
                            x.WriteRaw("<Cell ss:Index='" + displayIndex.ToString() + 
                                       "'><Data ss:Type='" + 
                                       columnTypes[i] + "'>");
                            missedNullColumn = false;
                        } else {
                            x.WriteRaw("<Cell><Data ss:Type='" + 
                                       columnTypes[i] + "'>");

                        switch (columnTypes[i]) {
                            case "DateTime":
                            case "Boolean":
                                x.WriteRaw(((bool)row[i]) ? "1" : "0");
                            case "String":

                    } else {
                        missedNullColumn = true;


  • Jan 7, 2009: Added link about importing (see Introduction).
  • Jan 8, 2009: Updated link about importing (see Introduction).
  • Jan 21, 2009: Added note about Excel not being required on the server


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Software Developer
United States United States
likes boardgames, computer games, and enjoys his .net programming job.

You may also be interested in...

Comments and Discussions

QuestionHow to using this Pin
rifkiharahap5-Sep-13 0:44
memberrifkiharahap5-Sep-13 0:44 
AnswerRe: How to using this Pin
ColinBashBash5-Sep-13 6:04
memberColinBashBash5-Sep-13 6:04 
Generalfloat / double - use dot instead of comma (for German installations) [modified] Pin
Sina Falahati21-Aug-10 10:51
memberSina Falahati21-Aug-10 10:51 
Double / float is exported incorrectly I noticed on my C# installation, which is in German:

Be careful: 1,123 is the same as 1123 for Excel.
1.123 is interpreted correctly as a number smaller than 2.

It seems that not in all languages the interpretation is the same for ToString();

therefore use:

where it applies.

Explaining InvariantCulture[^]

Thanks for the article. You have written a great piece of code Thumbs Up | :thumbsup:

modified on Saturday, August 21, 2010 4:00 PM

GeneralStyles applied to cells Pin
Budsy27-Feb-09 13:58
memberBudsy27-Feb-09 13:58 
GeneralRe: Styles applied to cells Pin
ColinBashBash2-Mar-09 4:24
memberColinBashBash2-Mar-09 4:24 
QuestionDoes this code works when there is no excel in server ? Pin
srinivasanvk20-Jan-09 2:30
membersrinivasanvk20-Jan-09 2:30 
AnswerRe: Does this code works when there is no excel in server ? Pin
ColinBashBash20-Jan-09 4:23
memberColinBashBash20-Jan-09 4:23 
AnswerRe: Does this code works when there is no excel in server ? Pin
CikaPero4-Sep-11 22:17
memberCikaPero4-Sep-11 22:17 
Questionis it possible to pass a macro - 'format as table' Pin
Ric2913-Jan-09 13:09
memberRic2913-Jan-09 13:09 
AnswerRe: is it possible to pass a macro - 'format as table' Pin
ColinBashBash14-Jan-09 5:46
memberColinBashBash14-Jan-09 5:46 
GeneralGetting Error Message Pin
ndennisv13-Jan-09 4:01
memberndennisv13-Jan-09 4:01 
GeneralRe: Getting Error Message Pin
ColinBashBash13-Jan-09 5:15
memberColinBashBash13-Jan-09 5:15 
RantXmlTextWriter Pin
jonnyroyster13-Jan-09 1:03
memberjonnyroyster13-Jan-09 1:03 
GeneralGood work Pin
Dr.Luiji13-Jan-09 0:16
memberDr.Luiji13-Jan-09 0:16 
GeneralYou can also use Transform function of XslCompliedTransform class in .Net Framework to complete this work Pin
songjietoo6-Jan-09 20:57
membersongjietoo6-Jan-09 20:57 
GeneralRe: You can also use Transform function of XslCompliedTransform class in .Net Framework to complete this work Pin
ColinBashBash7-Jan-09 4:19
memberColinBashBash7-Jan-09 4:19 
GeneralRe: You can also use Transform function of XslCompliedTransform class in .Net Framework to complete this work Pin
Cstruter20-Oct-10 20:55
memberCstruter20-Oct-10 20:55 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.151126.1 | Last Updated 21 Jan 2009
Article Copyright 2009 by ColinBashBash
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid