Click here to Skip to main content
Click here to Skip to main content

Export DataSet to Multiple Excel Sheets

, 12 Dec 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Exporting multiple tables in a DataSet to multiple sheets in an Excel file

Introduction

I was in need of exporting multiple tables in a DataSet to an Excel file with multiple sheets, and I couldn't find anything that actually works. So, I wrote one to help people who might be in the same situation. The complete code in ExcelHelper.cs is shown below.

This only works for Excel 2003 and later versions. If a table in the dataset has more than 65,000 rows, it will break it into multiple sheets for the table with sheet name (tableNameX). replaceXmlChar() function is added to escape XML reserved characters.

Limitations of the Code

It does not handle data tables with more than 256 columns (Excel 2003 column limit), and when data tables have very large rows count, it might throw OutOfMemory exception.

Using the Code

To export a DataSet, just call the ExcelHelper.ToExcel() function as follows:

 var ds = new DataSet();
            var dt = new DataTable("TableName For Sheet1");
            dt.Columns.Add("col1");
            dt.Columns.Add("col2");
            dt.Rows.Add("Value1", "Value2");

            var dt2 = new DataTable("TableName For Sheet2");
            dt2.Columns.Add("col1");
            dt2.Columns.Add("col2");
            dt2.Rows.Add("Value1", "Value2");
            ds.Tables.Add(dt);
            ds.Tables.Add(dt2);
            ExcelHelper.ToExcel(ds, "test.xls", Page.Response);      

Here is the code that does the exporting:

//ExcelHelper.cs

public class ExcelHelper
{
    //Row limits older Excel version per sheet
        const int rowLimit = 65000;

        private static string getWorkbookTemplate()
        {
            var sb = new StringBuilder();
            sb.Append("<xml version>\r\n<Workbook
		xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n");
            sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n
		xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\r\n
		xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">\r\n");
            sb.Append(" <Styles>\r\n
		<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n
		<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>");
            sb.Append("\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>\r\n
		<Protection/>\r\n </Style>\r\n
		<Style ss:ID=\"BoldColumn\">\r\n <Font ");
            sb.Append("x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n
		<Style ss:ID=\"s62\">\r\n <NumberFormat");
            sb.Append(" ss:Format=\"@\"/>\r\n </Style>\r\n
		<Style ss:ID=\"Decimal\">\r\n
		<NumberFormat ss:Format=\"0.0000\"/>\r\n </Style>\r\n ");
            sb.Append("<Style ss:ID=\"Integer\">\r\n
		<NumberFormat ss:Format=\"0\"/>\r\n </Style>\r\n
		<Style ss:ID=\"DateLiteral\">\r\n <NumberFormat ");
            sb.Append("ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n
		<Style ss:ID=\"s28\">\r\n");
            sb.Append("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Top\"
		ss:ReadingOrder=\"LeftToRight\" ss:WrapText=\"1\"/>\r\n");
            sb.Append("<Font x:CharSet=\"1\" ss:Size=\"9\"
		ss:Color=\"#808080\" ss:Underline=\"Single\"/>\r\n");
            sb.Append("<Interior ss:Color=\"#FFFFFF\" ss:Pattern=\"Solid\"/>

		</Style>\r\n</Styles>\r\n {0}</Workbook>");
            return sb.ToString();
        }

        private static string replaceXmlChar(string input)
        {
            input = input.Replace("&", "&");
            input = input.Replace("<", "<");
            input = input.Replace(">", ">");
            input = input.Replace("\"", """);
            input = input.Replace("'", "&apos;");
            return input;
        }

        private static string getWorksheets(DataSet source)
        {
            var sw = new StringWriter();
            if (source == null || source.Tables.Count == 0)
            {
                sw.Write("<Worksheet ss:Name=\"Sheet1\"><Table><Row>

		<Cell  ss:StyleID=\"s62\"><Data ss:Type=\"String\"></Data>
		</Cell></Row></Table></Worksheet>");
                return sw.ToString();
            }
            foreach (DataTable dt in source.Tables)
            {
                if (dt.Rows.Count == 0)
                    sw.Write("<Worksheet ss:Name=\"" + replaceXmlChar(dt.TableName) +
			"\"><Table><Row><Cell  ss:StyleID=\"s62\">

			<Data ss:Type=\"String\"></Data></Cell></Row>
			</Table></Worksheet>");
                else
                {
                    //write each row data
                    var sheetCount = 0;
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        if ((i % rowLimit) == 0)
                        {
                            //add close tags for previous sheet of the same data table
                            if ((i / rowLimit) > sheetCount)
                            {
                                sw.Write("</Table></Worksheet>");
                                sheetCount = (i / rowLimit);
                            }
                            sw.Write("<Worksheet ss:Name=\"" +
				replaceXmlChar(dt.TableName) +
                                     (((i / rowLimit) == 0) ? "" :
				Convert.ToString(i / rowLimit)) + "\"><Table>");
                            //write column name row
                            sw.Write("<Row>");
                            foreach (DataColumn dc in dt.Columns)
                                sw.Write(
                                    string.Format(
                                        "<Cell ss:StyleID=\"BoldColumn\">

				<Data ss:Type=\"String\">{0}</Data></Cell>",
                                        replaceXmlChar(dc.ColumnName)));
                            sw.Write("</Row>\r\n");
                        }
                        sw.Write("<Row>\r\n");
                        foreach (DataColumn dc in dt.Columns)
                            sw.Write(
                                string.Format(
                                    "<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">

					{0}</Data></Cell>",
                                    		replaceXmlChar
					(dt.Rows[i][dc.ColumnName].ToString())));
                        sw.Write("</Row>\r\n");
                    }
                    sw.Write("</Table></Worksheet>");
                }
            }

            return sw.ToString();
        }
        public static string GetExcelXml(DataTable dtInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            var worksheets = getWorksheets(ds);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static string GetExcelXml(DataSet dsInput, string filename)
        {
            var excelTemplate = getWorkbookTemplate();
            var worksheets = getWorksheets(dsInput);
            var excelXml = string.Format(excelTemplate, worksheets);
            return excelXml;
        }

        public static void ToExcel
		(DataSet dsInput, string filename, HttpResponse response)
        {
            var excelXml = GetExcelXml(dsInput, filename);
            response.Clear();
            response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            response.AppendHeader
		("Content-disposition", "attachment; filename=" + filename);
            response.Write(excelXml);
            response.Flush();
            response.End();
        }

        public static void ToExcel
		(DataTable dtInput, string filename, HttpResponse response)
        {
            var ds = new DataSet();
            ds.Tables.Add(dtInput.Copy());
            ToExcel(ds, filename, response);
        }
    }

History

  • 7th December, 2008: Initial post
  • 8th December, 2008: Source code and article updated
  • 11th December, 2008: Source code and article updated

License

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

Share

About the Author

Ming_Lu
Software Developer Oliver Wyman Groups
United States United States
Education: Masters in Applied mathematics
 
Certification:
MCP in Asp.net
MCP in SQL Server 2008 Implementation.
 
Working Experience In .Net since 2005

Comments and Discussions

 
QuestionSave xls file at directory PinmemberMember 841450123-Oct-14 11:56 
QuestionSave as Xlsx file Pinmembervimalrvs16-Sep-14 20:41 
Questionhow can i replace Table1,Table2(Sheet name) to some other value PinmemberJeno M18-Mar-14 2:09 
QuestionRegarding implementing the sample code Pinmemberdk deepak17-Oct-13 0:41 
QuestionIt's great PinmemberAhmed Mostafa1-Oct-13 21:50 
QuestionExport DataSet to Multiple Excel Sheets Pinmemberhossein nasirzade31-May-13 22:03 
QuestionThread Exception [modified] PinmemberMember 995235021-May-13 4:57 
Questionjet4.0 Pinmembernadersghir28-Jan-13 4:41 
QuestionHow to store the excel file to Network shared Drive instead of opening on client machine Pinmemberkeyur_parekh25-Oct-12 12:37 
QuestionUnable to export if there are 1 lakh records in the dataset PinmemberAbhishek K Gohil11-Oct-12 2:51 
Questionnot in correct format for 2010. PinmemberTeenustar10-Oct-12 11:42 
QuestionRe: not in correct format for 2010. Pinmembervimalrvs16-Sep-14 20:38 
GeneralThanks For Share this artical very helpful Pinmembersapnapal8-Aug-12 23:29 
GeneralMy vote of 5 Pinmemberchus2425-Jun-12 15:43 
Questiondata display in gridview depend on user key in.how to export it? Pinmembermusiw13-Jun-12 0:11 
QuestionWorks Great...Thanks PinmemberMember 3324275-Jun-12 9:29 
GeneralNice Pinmembersivasankari ts15-May-12 0:28 
GeneralVote of 5! Pinmembermk_ln12-Apr-12 12:07 
GeneralMy vote of 5 Pinmembermanoj kumar choubey5-Apr-12 0:27 
GeneralThanks for this helpful code Pinmembermonika nafdey14-Mar-12 20:19 
GeneralMy vote of 5 PinmemberProEnggSoft1-Mar-12 19:30 
Questionerror message when opening text.xls PinmemberMember 86927471-Mar-12 17:55 
Hello,
 
Very NICE post.
 
Im using your sample code without any change, when I save the file test.xls on my desktop and when I open it, I get an error message saying "The file you are trying to open, 'test.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
 
I then click on Yes, and the file in Excel is working perfectly as it should, 2 worksheets are created, with correct rows and columns as coded in your code.
 
So why do I get this message? Any way to generate an excel file that would avoid getting this error?
 
Thank you,
 
Philippe
AnswerRe: error message when opening text.xls Pinmembercadetduke30-Mar-12 10:49 
GeneralRe: error message when opening text.xls PinmemberTeenustar10-Oct-12 11:55 
GeneralMy vote of 5 PinmemberARK Kambhampati27-Feb-12 21:03 
QuestionAlternate row color Pinmemberkinjalin29-Nov-11 22:02 
GeneralMy vote of 5 PinmemberFredde197728-Sep-11 4:50 
Questionneed help with style sheet error [modified] Pinmembertalmans26-Aug-11 13:47 
BugNot woking with Marquee taq? Pinmembermuthura120-Jun-11 2:39 
GeneralMy vote of 5! PinmemberFilip D'haene14-May-11 12:11 
QuestionCell Formatting.. Issues...? Pinmembercodingrocks13-Mar-11 3:24 
AnswerRe: Cell Formatting.. Issues...? PinmemberMing_Lu13-Mar-11 15:41 
GeneralMy vote of 4 Pinmemberpraveen_adb9-Jan-11 21:26 
GeneralMy vote of 5 Pinmemberbacoares23-Nov-10 5:48 
GeneralMy vote of 5 PinmemberL.H.C1-Oct-10 19:56 
GeneralEasy alternative PinmemberCikaPero13-Apr-10 23:52 
GeneralAuto Fit Excel Columns PinmemberMember 465177014-Jan-10 2:10 
GeneralSys.WebForms.PageRequestManagerParserErrorException PinmemberSteve Crochet28-Dec-09 10:14 
GeneralRe: Sys.WebForms.PageRequestManagerParserErrorException PinmemberMing_Lu2-Jan-10 4:55 
GeneralRe: Sys.WebForms.PageRequestManagerParserErrorException PinmemberSteve Crochet4-Jan-10 6:44 
QuestionWhat shd i use instead of var?? PinmemberBushra2813-Oct-09 0:12 
GeneralBig File Size Pinmembertxshxx4-Oct-09 18:12 
QuestionExport to Excel 2003 using ASP [modified] Pinmemberkaransaxena15-Jun-09 11:46 
GeneralHandle linefeed in cells PinmemberDACS13-Mar-09 14:43 
AnswerRe: Handle linefeed in cells PinmemberMing_Lu3-Apr-09 8:18 
GeneralRe: Handle linefeed in cells PinmemberDACS3-Apr-09 22:35 
QuestionCan I use this class in Windows Application? PinmemberSu Win8-Mar-09 5:17 
AnswerRe: Can I use this class in Windows Application? PinmemberMing_Lu3-Apr-09 8:29 
GeneralRe: Can I use this class in Windows Application? PinmemberSu Win5-Apr-09 18:26 
GeneralRe: Can I use this class in Windows Application? PinmemberBushra2813-Oct-09 0:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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
Web04 | 2.8.141220.1 | Last Updated 12 Dec 2008
Article Copyright 2008 by Ming_Lu
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid