I must to import information from Excel Spreadsheets into a .net object to read and store some data. I did the importing of namespace and I know how to create Excel objects. But I don't know how to set the file (loaded through a FileUpload object) to this object. Thanks.
Add a comment
|
2 Answers
One way you could proceed with this is to save the file using the FileUpload.SaveAs() method and reading it with the Excel Interop library (Microsoft.Office.Interop.Excel). Like so:
ApplicationClass app = new ApplicationClass();
// Open Excel File
Workbook = app.Workbooks.Open(path_to_file_uploaded,
0,
true,
5,
"",
"",
true,
XlPlatform.xlWindows,
"\t",
false,
false,
0,
true,
1,
0);
PD: I suppose that you could be looking for other, more direct, way of doing this without having to save to disk. If so let me know to see if I can find a way of doing that.
Comments
Use OleDbConnection for reading file content:
Using cnn As New System.Data.OleDb.OleDbConnection(connectionString)
Using cmd = cnn.CreateCommand
cmd.CommandText = "SELECT * FROM [" & sheetName & "]"
Using dr = cmd.ExecuteReader
While dr.Read
Dim item = New With {.firstName = dr(0), .lastName = dr(1)}
End While
End Using
End Using
End Using
for connection string use:
//Excel 2003:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & uploadedFilePath & ";Extended Properties=""Excel 8.0; HDR=Yes"""
//or
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & uploadedFilePath & ";Extended Properties=""Excel 8.0; HDR=Yes"""
//Excel 2007:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & uploadedFilePath & ";Extended Properties=""Excel 12.0; HDR=Yes"""
//Excel 2010:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & uploadedFilePath & ";Extended Properties=""Excel 14.0; HDR=Yes"""
Remember installing this if using ACE (2007/2010):
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d
2 Comments
Alex
It's a very interesting solution. It deserved a vote up. But the excel sheet that I receive dayly is a litle bit tricky, and the first option was the fittest way for me.
Afshin Gh
@darth: thank you. If you just need to read data, using oledb is better. but if you need to do more with your excel file, you may use other solutions. You may need to install excel for other solutions, but reading data using oledb is kind of a standard approach.