Click here to Skip to main content
Click here to Skip to main content
Go to top

Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type

, 24 Feb 2012
Rate this:
Please Sign up or sign in to vote.
This might not be a ground breaking discovery but I was thinking I might be able to share this piece of code to someone who might need it.

This might not be a ground breaking discovery but I was thinking I might be able to share this piece of code to someone who might need it. As usual I did 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:

using Microsoft.Office.Interop.Excel;

You need Office Interop as a reference then lets do the coding

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 accodring 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 is 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 is will be as simple like this.

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 you have to name your DataSet and that will be the name of the worksheet in Excel.

License

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

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
QuestionVB conversion PinmemberMember 942560312-Sep-12 10:07 
AnswerRe: VB conversion PinmemberMember 942560317-Sep-12 5:00 
GeneralRe: VB conversion PinmemberPriyanka Jain3-May-13 23:24 

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 | Mobile
Web01 | 2.8.140921.1 | Last Updated 24 Feb 2012
Article Copyright 2012 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid