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;
xlSheets = xlWorkbook.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
xlWorksheet.Name = dataSet.DataSetName;
foreach (DataColumn dataColumn in dataTable.Columns)
{
colIndex++;
xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
}
object[,] objData = new object[rowNo, columnNo];
for (int row = 0; row < rowNo; row++)
{
for (int col = 0; col < columnNo; col++)
{
objData[row, col] = dataTable.Rows[row][col];
}
}
Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
range.Value2 = objData;
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:
break;
}
xlWorksheet.Range[xlApp.Cells[2, colIndex],
xlApp.Cells[rowNo + 1, colIndex]].NumberFormat = format;
}
}
((Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();
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.