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 |
0 comments:
Post a Comment