| Easily Export DataTable or DataSet to Excel |
|
If you are programming in .NET, chances are you will come across a time where you need to export the DataTable to Excel. Unforntuate there is no easy way to do this, and the best way to do this is to use Interop. First you need to add a Microsoft Excell 11.0 Object Library Reference to your project.
After you have the reference you can writing data to the Excel file. private void ExportToExcel() { FolderBrowserDialog fbdExport = new FolderBrowserDialog();
//First get the path to where user want to save the file. if (fbdExport.ShowDialog() == DialogResult.OK) { string selectPath = String.Empty; selectPath = fbdExport.SelectedPath;
Database db = DatabaseFactory.CreateDatabase(); DataSet dsExport = new DataSet(); DbCommand dbCommand = null;
dbCommand = db.GetStoredProcCommand("dbo.prDataQueryProcedure"); dbCommand.CommandTimeout = 600; db.AddInParameter(dbCommand, "@searchString", DbType.String, "Find Name"); db.AddInParameter(dbCommand, "@searchEntityID", DbType.Int32, 1); db.AddInParameter(dbCommand, "@searchLevel", DbType.Int32, 1); db.AddInParameter(dbCommand, "@rootEntityID", DbType.Int32, 121);
dsExport = db.ExecuteDataSet(dbCommand);
//Export to Excel file
//Create an array of tables to store multiple selection object misValue = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(misValue); Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(1); Microsoft.Office.Interop.Excel.Worksheet worksheet2 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets.get_Item(2);
//Print title worksheet1.Cells[1, 1] = "Entity Id"; worksheet1.Cells[1, 2] = "Functional Location Label"; worksheet1.Cells[1, 3] = "Functional location description"; worksheet1.Cells[1, 4] = "Superior Functional Location"; worksheet1.Cells[1, 5] = "Sort Field";
worksheet2.Cells[1, 1] = "Entity Id"; worksheet2.Cells[1, 2] = "Functional Location Label"; worksheet2.Cells[1, 3] = "Functional location description"; worksheet2.Cells[1, 4] = "Superior Functional Location"; worksheet2.Cells[1, 5] = "Sort Field";
//Fill in the data
int count1 = 0; int count2 = 0;
for (int i = 0; i < tbExport.Rows.Count; i++) { //Use Navi sheet DataRow row = tbExport.Rows[i];
if (Int32.Parse(row["EntityTypeID"].ToString()) == (int)EntityType.NAVI) { worksheet1.Cells[count1 + 2, 1] = row["EntityID"].ToString(); worksheet1.Cells[count1 + 2, 2] = row["EntityNumber"].ToString(); worksheet1.Cells[count1 + 2, 3] = row["EntityName"].ToString(); worksheet1.Cells[count1 + 2, 4] = row["ParentEntityNumber"].ToString(); worksheet1.Cells[count1 + 2, 5] = row["TagNumber"].ToString(); count1++; } else //Use Prod sheet { worksheet2.Cells[count2 + 2, 1] = row["EntityID"].ToString(); worksheet2.Cells[count2 + 2, 2] = row["EntityNumber"].ToString(); worksheet2.Cells[count2 + 2, 3] = row["EntityName"].ToString(); worksheet2.Cells[count2 + 2, 4] = row["ParentEntityNumber"].ToString(); worksheet2.Cells[count2 + 2, 5] = row["TagNumber"].ToString(); count2++; } }
string fileName = selectPath + String.Format("\\filename-{0}.xls", DateTime.Now.ToString("ddMMyyyy-hhmm"));
workbook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); workbook.Close(true, misValue, misValue); excel.Quit();
releaseObject(worksheet1); releaseObject(worksheet2); releaseObject(workbook); releaseObject(excel);
} }
//Clear resources private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Exception Occured while releasing object " + ex.ToString()); } finally { GC.Collect(); } }
|
