reservoircap
asked on
Can I export data from an unsaved excel spreadsheet (using ADO.Net/VB.Net)?
I have an application that uses Excel as a number cruncher. I need for Excel to spit out all its calculated output into a format that I can easily "slap" into a SQL Database. I have been able to do this successfully if I save the Excel file, however the file is so enormous that this is simply not an option due to performance concerns. I also do not want to copy and paste data into a temporary Excel file and read from there.
Is there a way to connect to an already open Excel spreadsheet and read data from it without having to save the file? I've pasted the code that I've been using - this is the code that successfully reads from an already saved Excel file. Thanks EE community!
Is there a way to connect to an already open Excel spreadsheet and read data from it without having to save the file? I've pasted the code that I've been using - this is the code that successfully reads from an already saved Excel file. Thanks EE community!
Dim connXL As New OleDbConnection
Dim sXLConnectionString As String = "Provider=" & g_sXLConnectionString_Provider & ";Data Source=" & sFilePathString & ";Extended Properties=""" & g_sXLConnectionString_ExtendedProperties & ";HDR=" & sHDR & """"
'Create Connection to Excel Workbook
connXL = New OleDbConnection(sXLConnectionString)
'Excel Workbook "database" command
Dim commandXL As New OleDbCommand("Select '" & iRunID & "', '" & iFileID & "', '" & CDate(sMonthDate) & "', * FROM [" & sRange & "]", connXL)
connXL.Open()
Dim drXL As DbDataReader
drXL = commandXL.ExecuteReader
If xlConnAndData.drXL Is Nothing Or Err.Description <> "" Then
g_sErrorMessage = "Error occured in GrabExcelData function while creating the datareader object for Excel. MSError: " & Err.Description
g_bErroredOut = True
Return xlConnAndData
Exit Function
End If
This page explains how to select data out using ADODB on an unsaved open Excel spreadsheet.
http://stackoverflow.com/questions/2031207/is-there-a-way-to-use-ole-db-provider-for-jet-on-an-unsaved-excel-workbook
Lee
http://stackoverflow.com/questions/2031207/is-there-a-way-to-use-ole-db-provider-for-jet-on-an-unsaved-excel-workbook
Lee
By the way, the conclusion in the given link is
"I don't see any way to run a SQL query on unsaved data."
"I don't see any way to run a SQL query on unsaved data."
Hmm, correct. I have done this before, a while ago with Excel 2000 so hopefully you can still do it in Excel 2007, as you need to find the location of the temp file that is created. I thought this page was the one that told me. Seems not. I'll dig around to see if I can find the page.
Lee
Lee
ASKER
To CodeCruiser:
The problem with interop is that whether I suck in the Excel values using a giant array or a List (of arrays of values), at some point I will need to loop through the elements to place into a dataset or datatable (or list items to insert directly into the SQL database) . I'm trying to look for a solution where I would not have to use a loop since this process will need to be repeated each time the application sucks in data.. and in this partiuclar group of workboks, each worksheet is about 1400 rows long and 265 columns wide. It would take awhile for VB.Net to loop through each element or row.
Maybe there's a way for me to directly slap an entire array or listview created using Excel Interop into a SQL database that I'm unaware of?
The problem with interop is that whether I suck in the Excel values using a giant array or a List (of arrays of values), at some point I will need to loop through the elements to place into a dataset or datatable (or list items to insert directly into the SQL database) . I'm trying to look for a solution where I would not have to use a loop since this process will need to be repeated each time the application sucks in data.. and in this partiuclar group of workboks, each worksheet is about 1400 rows long and 265 columns wide. It would take awhile for VB.Net to loop through each element or row.
Maybe there's a way for me to directly slap an entire array or listview created using Excel Interop into a SQL database that I'm unaware of?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.c-sharpcorner.com/UploadFile/mgold/HowtoOepnandReadanExcelSpreadsheetinaListViewin.NET11282005034134AM/HowtoOepnandReadanExcelSpreadsheetinaListViewin.NET.aspx