Click here to Skip to main content
15,868,150 members
Articles / Programming Languages / C#

Export Multiple DataSets to Multiple Excel Sheets Dynamically Formatted According to the Record's Data Type

Rate me:
Please Sign up or sign in to vote.
5.00/5 (4 votes)
24 Feb 2012CPOL1 min read 57.4K   12   9
Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type

This might not be a ground breaking discovery but I was thinking I might be able to share this piece of code with someone who might need it. As usual, I searched online first whether this is available but I haven’t seen something exactly similar yet so it’s a good time to share it. This article is basically how to Export Multiple Datasets to multiple Excel sheets formatted according to data type. Basically, what the code does is what the title implies, it will be a 1 is to 1 relationship between a dataset and worksheet which means if you pass 10 datasets, there will be 3 worksheets and columns will be formatted according to the datatype of the record. This is created as generic as possible so you can just copy and paste the codes.

So this sample will only consist of one method and here it is:

C#
using Microsoft.Office.Interop.Excel;

You need Office Interop as a reference, then let's do the coding:

C#
public void DataSetsToExcel(List<DataSet> dataSets, string fileName)
{
    Microsoft.Office.Interop.Excel.Application xlApp = 
              new Microsoft.Office.Interop.Excel.Application();
    Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
    Sheets xlSheets = null;
    Worksheet xlWorksheet = null;

    foreach (DataSet dataSet in dataSets)
    {
        System.Data.DataTable dataTable = dataSet.Tables[0];
        int rowNo = dataTable.Rows.Count;
        int columnNo = dataTable.Columns.Count;
        int colIndex = 0;

        //Create Excel Sheets
        xlSheets = xlWorkbook.Sheets;
        xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1], 
                       Type.Missing, Type.Missing, Type.Missing);
        xlWorksheet.Name = dataSet.DataSetName;

        //Generate Field Names
        foreach (DataColumn dataColumn in dataTable.Columns)
        {
            colIndex++;
            xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
        }

        object[,] objData = new object[rowNo, columnNo];

        //Convert DataSet to Cell Data
        for (int row = 0; row < rowNo; row++)
        {
            for (int col = 0; col < columnNo; col++)
            {
                objData[row, col] = dataTable.Rows[row][col];
            }
        }

        //Add the Data
        Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
        range.Value2 = objData;

        //Format Data Type of Columns 
        colIndex = 0;
        foreach (DataColumn dataColumn in dataTable.Columns)
        {
            colIndex++;
            string format = "@";
            switch (dataColumn.DataType.Name)
            {
                case "Boolean":
                    break;
                case "Byte":
                    break;
                case "Char":
                    break;
                case "DateTime":
                    format = "dd/mm/yyyy";
                    break;
                case "Decimal":
                    format = "$* #,##0.00;[Red]-$* #,##0.00";
                    break;
                case "Double":
                    break;
                case "Int16":
                    format = "0";
                    break;
                case "Int32":
                    format = "0";
                    break;
                case "Int64":
                    format = "0";
                    break;
                case "SByte":
                    break;
                case "Single":
                    break;
                case "TimeSpan":
                    break;
                case "UInt16":
                    break;
                case "UInt32":
                    break;
                case "UInt64":
                    break;
                default: //String
                    break;
            }
            //Format the Column according to Data Type
            xlWorksheet.Range[xlApp.Cells[2, colIndex], 
                  xlApp.Cells[rowNo + 1, colIndex]].NumberFormat = format;
        }
    }

    //Remove the Default Worksheet
    ((Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();

    //Save
    xlWorkbook.SaveAs(fileName,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        XlSaveAsAccessMode.xlNoChange,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value,
        System.Reflection.Missing.Value);

    xlWorkbook.Close();
    xlApp.Quit();
    GC.Collect();
}

If you notice some datasets are formatted by default, I just added all of the different datatypes of a dataset for you to add your own formatting. To use the code will be as simple like this:

C#
DataSet dataSet1 = new DataSet("My Data Set 1");
dataAdapter1.Fill(dataSet1);

DataSet dataSet2 = new DataSet("My Data Set 2");
dataAdapter1.Fill(dataSet2);

DataSet dataSet3 = new DataSet("My Data Set 3");
dataAdapter1.Fill(dataSet3);

List<DataSet> dataSets = new List<DataSet>();
dataSets.Add(dataSet1);
dataSets.Add(dataSet2);
dataSets.Add(dataSet3);

DataSetsToExcel(dataSets, "{Your File Name}")

Take note that you have to name your DataSet and that will be the name of the worksheet in Excel.

Image 1

License

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


Written By
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/

Comments and Discussions

 
BugNot working after published into IIS Pin
suggala vara prasad3-Jan-16 18:54
suggala vara prasad3-Jan-16 18:54 
QuestionAny Working example Pin
Member 114031842-Feb-15 6:59
Member 114031842-Feb-15 6:59 
QuestionInsufficient memory to continue the execution of the program. Pin
Rajeshkumar32895-May-14 20:10
Rajeshkumar32895-May-14 20:10 
AnswerRe: Insufficient memory to continue the execution of the program. Pin
kathi19825-Apr-17 1:58
kathi19825-Apr-17 1:58 
QuestionVB conversion Pin
Member 942560312-Sep-12 10:07
Member 942560312-Sep-12 10:07 
AnswerRe: VB conversion Pin
Member 942560317-Sep-12 5:00
Member 942560317-Sep-12 5:00 
GeneralRe: VB conversion Pin
Priyanka Jain3-May-13 23:24
Priyanka Jain3-May-13 23:24 
QuestionExport multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type Pin
pixxeldigital22-Jun-12 1:56
pixxeldigital22-Jun-12 1:56 
AnswerRe: Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type Pin
praveen_0718-Jul-12 5:47
praveen_0718-Jul-12 5:47 

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.