“Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type”
public void DataSetsToExcel(List<DataSet> dataSets, string fileName)
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets xlSheets = null;
Microsoft.Office.Interop.Excel.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 = (Microsoft.Office.Interop.Excel.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
((Microsoft.Office.Interop.Excel.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();
}
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}")
