“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}")
Thank you for reading the article about Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type on the blog NEW TECH If you want to disseminate this article on please list the link as the source, and if this article was helpful please bookmark this page in your web browser by pressing Ctrl + D on your keyboard keys.