About these ads

Export Multiple Datasets to Multiple Excel sheets dynamically formatted according to the records Data Type

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.

About these ads

4 Responses to Export Multiple Datasets to Multiple Excel sheets dynamically formatted according to the records Data Type

  1. Great article, I am looking for some read-made function that do this trick. Thanks.

  2. pranali pm says:

    how to set autofit property to all sheet cell range and how to change order of excel sheet
    by default it is showing last sheet first

  3. Amarjeet says:

    Great !! Comments More Helpful,Thanks

  4. brahmanandam says:

    Server Error in ‘/intra_Latest’ Application.
    Compilation Error
    Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

    Compiler Error Message: CS1546: Property, indexer, or event ‘Range’ is not supported by the language; try directly calling accessor method ‘Microsoft.Office.Interop.Excel._Worksheet.get_Range(object, object)’

    Source Error:

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

    How to solve this problem????

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 197 other followers

%d bloggers like this: