Computer Tricks and Tips for System: Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type

Computer Tricks and Tips for System Computer Tricks, Computer Tricks and Tips for System, computer tips, computer, tips, information, listing, tip, computer tip, about, windows, internet, internet, ticks, explorer, Microsoft --- Computer tips and tricks that help make you more productive and your overall computer experience a lot more enjoyable, Windows 8, Mobile, iPad, Computer Tricks and Tips for System

Manage your social media Best social media tool for image publishing to Facebook and Twitter. Look amazing and delight your followers. Get 40% off when you sign up today. From our sponsors

Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type Sep 26th 2013, 14:35, by Girish Dubey “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}")

You are receiving this email because you subscribed to this feed at blogtrottr.com. If you no longer wish to receive these emails, you can unsubscribe from this feed, or manage all your subscriptions