Click here to Skip to main content
13,195,821 members (53,990 online)
Click here to Skip to main content
Add your own
alternative version


93 bookmarked
Posted 7 Dec 2008

Export DataSet to Multiple Excel Sheets

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


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.Rows.Add("Value1", "Value2");

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

Here is the code that does the exporting:


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
            sb.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n
		xmlns:x=\"urn:schemas- microsoft-com:office:excel\"\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>
                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>
                    //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)
                                sheetCount = (i / rowLimit);
                            sw.Write("<Worksheet ss:Name=\"" +
				replaceXmlChar(dt.TableName) +
                                     (((i / rowLimit) == 0) ? "" :
				Convert.ToString(i / rowLimit)) + "\"><Table>");
                            //write column name row
                            foreach (DataColumn dc in dt.Columns)
                                        "<Cell ss:StyleID=\"BoldColumn\">

				<Data ss:Type=\"String\">{0}</Data></Cell>",
                        foreach (DataColumn dc in dt.Columns)
                                    "<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">


            return sw.ToString();
        public static string GetExcelXml(DataTable dtInput, string filename)
            var excelTemplate = getWorkbookTemplate();
            var ds = new DataSet();
            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.AppendHeader("Content-Type", "application/");
		("Content-disposition", "attachment; filename=" + filename);

        public static void ToExcel
		(DataTable dtInput, string filename, HttpResponse response)
            var ds = new DataSet();
            ToExcel(ds, filename, response);


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


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


About the Author

Software Developer Oliver Wyman Groups
United States United States
Education: Masters in Applied mathematics

MCP in
MCP in SQL Server 2008 Implementation.

Working Experience In .Net since 2005

You may also be interested in...


Comments and Discussions

QuestionExcellent work! Pin
Member 118914521-Dec-15 6:51
memberMember 118914521-Dec-15 6:51 
QuestionNeed to add images, charts Pin
Arkadeep De3-Nov-15 19:31
professionalArkadeep De3-Nov-15 19:31 
QuestionExport to Excel with Image Pin
King Ravi Kumar30-Sep-15 21:41
memberKing Ravi Kumar30-Sep-15 21:41 
QuestionThanks... :) Pin
aambhi3-Mar-15 4:35
memberaambhi3-Mar-15 4:35 
AnswerDatetime format issue - resolved Pin
Babuji Godem16-Feb-15 0:37
memberBabuji Godem16-Feb-15 0:37 
QuestionProbelm with datetime Pin
Babuji Godem15-Feb-15 22:19
memberBabuji Godem15-Feb-15 22:19 
QuestionSave xls file at directory Pin
Member 841450123-Oct-14 10:56
memberMember 841450123-Oct-14 10:56 
QuestionSave as Xlsx file Pin
vimalrvs16-Sep-14 19:41
membervimalrvs16-Sep-14 19:41 
Questionhow can i replace Table1,Table2(Sheet name) to some other value Pin
Jeno M18-Mar-14 1:09
memberJeno M18-Mar-14 1:09 
QuestionRegarding implementing the sample code Pin
dk deepak16-Oct-13 23:41
memberdk deepak16-Oct-13 23:41 
QuestionIt's great Pin
Ahmed Mostafa1-Oct-13 20:50
memberAhmed Mostafa1-Oct-13 20:50 
QuestionExport DataSet to Multiple Excel Sheets Pin
hossein nasirzade31-May-13 21:03
memberhossein nasirzade31-May-13 21:03 
QuestionThread Exception Pin
Member 995235021-May-13 3:57
memberMember 995235021-May-13 3:57 
Questionjet4.0 Pin
nadersghir28-Jan-13 3:41
membernadersghir28-Jan-13 3:41 
QuestionHow to store the excel file to Network shared Drive instead of opening on client machine Pin
keyur_parekh25-Oct-12 11:37
memberkeyur_parekh25-Oct-12 11:37 
QuestionUnable to export if there are 1 lakh records in the dataset Pin
Abhishek K Gohil11-Oct-12 1:51
memberAbhishek K Gohil11-Oct-12 1:51 
Questionnot in correct format for 2010. Pin
Teenustar10-Oct-12 10:42
memberTeenustar10-Oct-12 10:42 
QuestionRe: not in correct format for 2010. Pin
vimalrvs16-Sep-14 19:38
membervimalrvs16-Sep-14 19:38 
GeneralThanks For Share this artical very helpful Pin
sapnapal8-Aug-12 22:29
membersapnapal8-Aug-12 22:29 
GeneralMy vote of 5 Pin
chus2425-Jun-12 14:43
memberchus2425-Jun-12 14:43 
Questiondata display in gridview depend on user key to export it? Pin
musiw12-Jun-12 23:11
membermusiw12-Jun-12 23:11 
QuestionWorks Great...Thanks Pin
Member 3324275-Jun-12 8:29
memberMember 3324275-Jun-12 8:29 
GeneralNice Pin
sivasankari ts14-May-12 23:28
membersivasankari ts14-May-12 23:28 
GeneralVote of 5! Pin
mk_ln12-Apr-12 11:07
membermk_ln12-Apr-12 11:07 
GeneralMy vote of 5 Pin
manoj kumar choubey4-Apr-12 23:27
membermanoj kumar choubey4-Apr-12 23:27 
GeneralThanks for this helpful code Pin
monika nafdey14-Mar-12 19:19
membermonika nafdey14-Mar-12 19:19 
GeneralMy vote of 5 Pin
ProEnggSoft1-Mar-12 18:30
memberProEnggSoft1-Mar-12 18:30 
Questionerror message when opening text.xls Pin
Member 86927471-Mar-12 16:55
memberMember 86927471-Mar-12 16:55 
AnswerRe: error message when opening text.xls Pin
cadetduke30-Mar-12 9:49
membercadetduke30-Mar-12 9:49 
GeneralRe: error message when opening text.xls Pin
Teenustar10-Oct-12 10:55
memberTeenustar10-Oct-12 10:55 
GeneralMy vote of 5 Pin
ARK Kambhampati27-Feb-12 20:03
memberARK Kambhampati27-Feb-12 20:03 
QuestionAlternate row color Pin
kinjalin29-Nov-11 21:02
memberkinjalin29-Nov-11 21:02 
GeneralMy vote of 5 Pin
Fredde197728-Sep-11 3:50
memberFredde197728-Sep-11 3:50 
Questionneed help with style sheet error [modified] Pin
talmans26-Aug-11 12:47
membertalmans26-Aug-11 12:47 
BugNot woking with Marquee taq? Pin
muthura120-Jun-11 1:39
membermuthura120-Jun-11 1:39 
GeneralMy vote of 5! Pin
Filip D'haene14-May-11 11:11
memberFilip D'haene14-May-11 11:11 
QuestionCell Formatting.. Issues...? Pin
codingrocks13-Mar-11 2:24
membercodingrocks13-Mar-11 2:24 
AnswerRe: Cell Formatting.. Issues...? Pin
Ming_Lu13-Mar-11 14:41
memberMing_Lu13-Mar-11 14:41 
GeneralMy vote of 4 Pin
praveen_adb9-Jan-11 20:26
memberpraveen_adb9-Jan-11 20:26 
GeneralMy vote of 5 Pin
bacoares23-Nov-10 4:48
memberbacoares23-Nov-10 4:48 
GeneralMy vote of 5 Pin
L.H.C1-Oct-10 18:56
memberL.H.C1-Oct-10 18:56 
GeneralEasy alternative Pin
CikaPero13-Apr-10 22:52
memberCikaPero13-Apr-10 22:52 
GeneralAuto Fit Excel Columns Pin
Member 465177014-Jan-10 1:10
memberMember 465177014-Jan-10 1:10 
GeneralSys.WebForms.PageRequestManagerParserErrorException Pin
Steve Crochet28-Dec-09 9:14
memberSteve Crochet28-Dec-09 9:14 
GeneralRe: Sys.WebForms.PageRequestManagerParserErrorException Pin
Ming_Lu2-Jan-10 3:55
memberMing_Lu2-Jan-10 3:55 
GeneralRe: Sys.WebForms.PageRequestManagerParserErrorException Pin
Steve Crochet4-Jan-10 5:44
memberSteve Crochet4-Jan-10 5:44 
QuestionWhat shd i use instead of var?? Pin
Bushra2812-Oct-09 23:12
memberBushra2812-Oct-09 23:12 
GeneralBig File Size Pin
txshxx4-Oct-09 17:12
membertxshxx4-Oct-09 17:12 
QuestionExport to Excel 2003 using ASP [modified] Pin
karansaxena15-Jun-09 10:46
memberkaransaxena15-Jun-09 10:46 
GeneralHandle linefeed in cells Pin
DACS13-Mar-09 13:43
memberDACS13-Mar-09 13:43 

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.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171019.1 | Last Updated 12 Dec 2008
Article Copyright 2008 by Ming_Lu
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid