Excel Operations: Reading Data From Excel To DataTable & Generating Excel from DataTable

Reading data from Excel to DataTable

----------------------------------------

?

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

Public Function ConvertExcelToDT(ByVal sPath As String, ByVal sQuery As String) As DataTable   ConvertExcelToDT = Nothing   Dim ds As New DataSet   Dim StrXLProvider As String = ""   Dim OleAdpt As Data.OleDb.OleDbDataAdapter = Nothing   Dim Connection As OleDb.OleDbConnection   Try       Connection = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sPath + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")   OleAdpt = New Data.OleDb.OleDbDataAdapter(sQuery, Connection)   OleAdpt.TableMappings.Add("Table", "Table")   OleAdpt.Fill(ds)   Connection.Close()   resetcounter:   For Each dr In ds.Tables(0).Rows   If dr(0).ToString() = "" Then   ds.Tables(0).Rows.Remove(dr)   ds.Tables(0).AcceptChanges()   GoTo resetcounter   End If   Next               Catch ex As Exception   ConvertExcelToDT = Nothing   Me.Response.TransRemarks = ex.ToString()   Me.Response.TransStatus = True   Finally   If Connection IsNot Nothing Then   Connection.Close()   Connection.Dispose()   End If   ConvertExcelToDT = ds.Tables(0)   End Try       End Function

Reqs>>1. It should be Excel file

2. AccessDatabaseEngine.exe should be installed to read data from Excel to DataTable other wise you will get The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

3. No need of Excel to be installed in the hosting server

Generating Excel from DataTable

----------------------------------

?

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

Private Sub GenerateExcel(ByVal dtExcel As DataTable)   Dim oAppln As Microsoft.Office.Interop.Excel.Application   Dim oWorkBook As Microsoft.Office.Interop.Excel.Workbook   Dim oWorkSheet As Microsoft.Office.Interop.Excel.Worksheet   Dim ColumnIndex As Int32 = 0   Dim rowIndex As Int32 = 0   Dim fileName As String = "JNTax" & DateTime.Now.ToString().Replace(":", "_").Replace("/", "-") + ".xls"   Dim uploadingFilePath As String = Server.MapPath("AirlineRuleFiles")   Try   oAppln = New Microsoft.Office.Interop.Excel.Application()   oWorkBook = DirectCast(oAppln.Workbooks.Add(True), Microsoft.Office.Interop.Excel.Workbook)   'oWorkSheet = DirectCast(oWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing), Microsoft.Office.Interop.Excel.Worksheet)   'oWorkSheet.Activate()   oWorkSheet = oWorkBook.ActiveSheet()   For Each col As DataColumn In dtExcel.Columns   ColumnIndex += 1   oWorkSheet.Cells(1, ColumnIndex) = col.ColumnName       Next       For Each row As DataRow In dtExcel.Rows   rowIndex += 1   ColumnIndex = 0   For Each col As DataColumn In dtExcel.Columns   ColumnIndex += 1   oWorkSheet.Cells(rowIndex + 1, ColumnIndex) = row(col.ColumnName).ToString()   Next       Next   If System.IO.Directory.Exists(uploadingFilePath) Then   System.IO.Directory.Delete(uploadingFilePath, True)   End If       System.IO.Directory.CreateDirectory(uploadingFilePath)   fileName = uploadingFilePath & fileName   oWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Nothing, Nothing, False, False, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, False, False, Nothing, Nothing, Nothing)   oWorkBook.Close(Nothing, Nothing, Nothing)   oAppln.Quit()       Response.ClearContent()   Response.ContentType = "application/vnd.ms-excel"   Response.WriteFile(fileName)   Response.End()   Catch ex As Exception       End Try   End Sub

Reqs: 1. Excel should be installed if you are generating Excel using Excel Application class. Plus you need to set DCOM settings. I tried this one but faced issues so I won't recommend this method.

There is another way to Generate Excel files without using Microsoft Excel Application class.

?

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 96 97 98 99 100 101 102 103 104 105 106 107

Protected Sub GenerateExcelWithOutUsingExcelInterop(ByVal dtExcel As DataTable, ByVal fileName As String, ByVal sheetName As String)   Dim stream As FileStream = Nothing   Dim writer As ExcelWriter   Dim uploadingFilePath As String = Server.MapPath("AirlineRuleFiles")   Dim iheaderColumnsCount As Int16 = 0   Dim iRow As Int16 = 0   Dim iColumn As Int16 = 0   Try       If System.IO.Directory.Exists(uploadingFilePath) Then   System.IO.Directory.Delete(uploadingFilePath, True)   End If       System.IO.Directory.CreateDirectory(uploadingFilePath)       If Not System.IO.File.Exists(uploadingFilePath & fileName) Then   stream = System.IO.File.Create(uploadingFilePath & fileName)   End If       writer = New ExcelWriter(stream)       writer.BeginWrite()       iRow = 0   iColumn = 0   For Each column As DataColumn In dtExcel.Columns   writer.WriteCell(iRow, iColumn, column.ColumnName)   iColumn += 1   Next       iRow = 1   iColumn = 0   For Each row As DataRow In dtExcel.Rows   iColumn = 0   For Each column As DataColumn In dtExcel.Columns   writer.WriteCell(iRow, iColumn, row(column.ColumnName).ToString())   iColumn += 1   Next   iRow += 1   Next       writer.EndWrite()   stream.Close()       Response.ClearContent()   Response.ContentType = "application/vnd.ms-excel"   Response.AppendHeader("Content-Disposition", "attachment; filename=" & sheetName & ".xls")   Response.WriteFile(uploadingFilePath & fileName)   Response.End()   Catch ex As Exception   Throw ex   End Try       End Sub