Reading data from Excel to DataTable
----------------------------------------
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
----------------------------------
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.
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