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:

using Microsoft.Office.Interop.Excel;

You need Office Interop as a reference, then let's 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 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:

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.