Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type ~ NEW TECH

“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.