Okay here is my running code, I uploaded a .xlsx file,
Sub UploadAndDisplayExcel()
Dim FileName As String = Path.GetFileName(fu_bst.PostedFile.FileName)
Dim Extension As String = Path.GetExtension(fu_bst.PostedFile.FileName)
Dim FilePath As String = Server.MapPath(FolderPath + FileName)
conStr = ConfigurationManager.ConnectionStrings("Excel07ConString").ConnectionString
' "Excel07ConString" - is already configured in my web.config ..
conStr = String.Format(conStr, FilePath, "No")
Dim connExcel As New OleDbConnection(conStr)
Dim cmdExcel As New OleDbCommand()
Dim oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim SheetName As String = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
connExcel.Close()
connExcel.Open()
cmdExcel.CommandText = "SELECT * From [" & SheetName & "]"
oda.SelectCommand = cmdExcel
oda.Fill(dt)
connExcel.Close()
End Sub
Response.Write(ConvertDataTableToHTMLTable(dt)) 'ConvertDataTableToHTMLTable is a function that does convert DataTable to Html Table
and this is the sample excel that i want to upload (it looks like this)
--------------------------------------------------------------
Preview1 | | | Preview2 | | | Preview |
--------------------------------------------------------------
Sample | Data | Data | * | | | |
--------------------------------------------------------------
Data | Data | Data | - | | | |
--------------------------------------------------------------
Data | Data | Data | - | | | |
--------------------------------------------------------------
THe * is a Date ..
The - is a computed value, when it has no computation it display "-"
and Data is character, number and formula
when i use the function .. it uploads and display but .. in column Preview2 all "-" changes into #12/30/1899# i dunno why?
Should I my excel file must have value?? can i upload the excat values the excel has? HOW?