Writing data inthe Dataset into an Excel (with formattings)
The code below help to writing the data from the DataSet into a Excel. You can also do some style formatting in the Excel output. You need two important namespace to implement this concept, ‘Microsoft.Office.Interop.Excel’, ‘Microsoft.VisualBasic’. You should also need to reference these interopable dll into you project.
This code need the MS Excel to be installed in the machine. As we are reference the Excel dll into the project.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | using Microsoft.Office.Interop.Excel; using Microsoft.VisualBasic; private void TOExcel(string strExcelPath, DataSet ds) { //declaring the application Microsoft.Office.Interop.Excel.Application oAppln; //declaring work book Microsoft.Office.Interop.Excel.Workbook oWorkBook; //declaring worksheet Microsoft.Office.Interop.Excel.Worksheet oWorkSheet; //declaring the range Microsoft.Office.Interop.Excel.Range oRange; Microsoft.Office.Interop.Excel.Range oCellRange; Microsoft.Office.Interop.Excel.Range oHeadRange; try { oAppln = new Microsoft.Office.Interop.Excel.Application(); oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true)); oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet; int iRow = 2; int totRow = ds.Tables[0].Rows.Count; int totCol = ds.Tables[0].Columns.Count; //check for data if (totRow > 0) { //headers for (int j = 0; j < totCol; j++) { string colHead = ds.Tables[0].Columns[j].ColumnName; switch (colHead) { case "employee": oWorkSheet.Cells[1, j + 1] = "EMP ID"; break; case "first_name": oWorkSheet.Cells[1, j + 1] = "FIRST NAME"; break; case "last_name": oWorkSheet.Cells[1, j + 1] = "LAST NAME"; break; case "hrms_att_shift": oWorkSheet.Cells[1, j + 1] = "CURRENT SHIFT"; break; } } ///getting the head range and apply header styles oHeadRange = oWorkSheet.get_Range(oWorkSheet.Cells[1, 1], oWorkSheet.Cells[1, totCol]); oHeadRange.Font.Bold = true; oHeadRange.Interior.ColorIndex = 11; oHeadRange.Font.ColorIndex = 2; //inserting datas for (int rowNo = 0; rowNo < totRow; rowNo++) { //in each row for (int colNo = 0; colNo < totCol; colNo++) { // in each column oWorkSheet.Cells[iRow, colNo + 1] = ds.Tables[0].Rows[rowNo][colNo].ToString(); } if ((rowNo & 1) == 1) { oCellRange = oWorkSheet.get_Range(oWorkSheet.Cells[iRow, 1], oWorkSheet.Cells[iRow, totCol]); oCellRange.Interior.ColorIndex = 15; } //moving to next row iRow++; } } //range of the excel sheet oRange = oWorkSheet.get_Range("A1", oWorkSheet.Cells[totRow + 1, totCol]); oRange.EntireColumn.AutoFit(); oRange.Borders.Value = 1; oWorkBook.SaveAs(strExcelPath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, null, null, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, null, null, null, null, null); } catch (Exception theException) { string s = theException.Message; Console.WriteLine("Console: Attachment - " + theException.Message.ToString()); } finally { ///Clearing all loaded memory variables oRange = null; oCellRange = null; oWorkBook = null; oWorkSheet = null; oAppln = null; } } |
Tags: do some style formatting in the Excel output, dot net timer, dot net vb, dotnet net, Microsoft.Office.Interop.Excel, Microsoft.VisualBasic, namespace, need to reference interopable dll to you project, open dot net, source code dot net, vb net code, visual basic dot net code, visual basic net code, write the data from the DataSet into a Excel