I have been trying to upload and read an Excel file (.xls, or .xlsx)
I am upploading sucessfully using this code:
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click Dim filepath As String = "" If FileUpload1.HasFile Then Try Dim filename As String = FileUpload1.PostedFile.FileName Dim extension = (filename.Substring(filename.LastIndexOf("."), (filename.Length() - filename.LastIndexOf(".")))) If extension = ".xlsx" Or extension = ".xls" Then filepath = "\" & Common.toUnix(Now) & "_" & filename FileUpload1.SaveAs(Server.MapPath("~/") & filepath) ' ==== NOW READ THE FILE Else StatusLabel.InnerText = "Only Excel file types are accepted (.xls/.xlsx)<br> File Uploaded had extension: " & extension End If Catch ex As Exception StatusLabel.InnerText = "Upload status: The file could not be uploaded. The following error occured: " + ex.ToString() End Try End If End Sub
It uploads OK, but when trying to read the file I get this error:
System.Data.OleDb.OleDbException (0x80004005): The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.
I am using code to read similar to this:
Therefore the connection is as follows:
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0" & _ ";Data Source=" & ExcelFile & _ ";Extended Properties=Excel 8.0;" Dim conn As OleDbConnection = Nothing Dim dt As System.Data.DataTable = Nothing Dim excelDataSet As New DataSet() Try conn = New OleDbConnection(connString) conn.Open() '<<< ERROR IS RAISED ON THIS LINE dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) If dt Is Nothing Then Return Nothing End If Dim excelSheets(dt.Rows.Count) As String Dim i As Integer = 0 For Each row As DataRow In dt.Rows excelSheets(i) = row("payments").ToString System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1) If i = SheetNumber Then Exit For End If Next ..................
I'm uploading to a shared server so don't have control as to permissions as such, but I do have read/write permissions and uploading Images works OK, but it's reading this file that I can't get to work.
This error occurs with .xls files, when using .xlsx I get this error:
System.Data.OleDb.OleDbException (0x80004005): Cannot update. Database or object is read-only. at System.Data.OleDb.OleDbConnectionInternal
This error occurs on this line:
For Each row As DataRow In dt.Rows
So it appears it is uplpoading and opening the file OK, but not reading the rows....
I am not sure why that's happening either!
Any help would be much appreciated!
ACE is brutal, have troubles with it all the time and it cannot exist on a System in both 32 and 64 bit version at the same time. As a result I just dont use it at all.
To read an Excel XLSX file I use "EPPlus" which has proven to be very easy to deal with and extreamly efficient.
It ONLY works with XLSX (not XLS)
Example... (simple just pulls out first sheet as a datatable)
Public Function XlsxToDataTable(byteStream As IO.MemoryStream) As DataTable Using pac As New OfficeOpenXml.ExcelPackage(byteStream) Dim wb As OfficeOpenXml.ExcelWorkbook = pac.Workbook Dim ws As OfficeOpenXml.ExcelWorksheet = wb.Worksheets(1) '1 based Dim out As New DataTable For iC As Integer = 1 To ws.Dimension.End.Column out.Columns.Add(ws.Cells(1, iC).Value.ToString) Next For iR As Integer = 2 To ws.Dimension.End.Row Dim nr As DataRow = out.NewRow For iC As Integer = 1 To ws.Dimension.End.Column nr(iC - 1) = ws.Cells(iR, iC).Value Next out.Rows.Add(nr) Next Return out End Using End Function
User contributions licensed under CC BY-SA 3.0