I have build an ASP.net webpage with VB.net backend and SQL Server.
I would like a user to be able to select an Excel document from there computer and the page to copy the contents of particular cells in each sheet within the Excel Document to my SQL database.
I have seen some examples but i cant seem to get any working using my exising database connection within web.config.
Does anybody know of any sample code that fits my scenario, it needs to be VB and as my Excel doc is a complicated beast it needs to transfer particular cells into my SQL database table.
I have build an ASP.net webpage with VB.net backend and SQL Server.
I would like a user to be able to select an Excel document from there computer and the page to copy the contents of particular cells in each sheet within the Excel Document to my SQL database.
I have seen some examples but i cant seem to get any working using my exising database connection within web.config.
Does anybody know of any sample code that fits my scenario, it needs to be VB and as my Excel doc is a complicated beast it needs to transfer particular cells into my SQL database table.
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'"
Dim connExcel As New OleDbConnection(strExcelConn)
Dim cmdExcel As New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As Excel.DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
connExcel.Close()
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'"
Dim connExcel As New OleDbConnection(strExcelConn)
Dim cmdExcel As New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As Excel.DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
connExcel.Close()
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'"
Dim connExcel As New OleDbConnection(strExcelConn)
Dim cmdExcel As New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As Excel.DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
connExcel.Close()
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL
Did you check the Button2_Click event handler in that MS article. Thats where you retrieve the data.
FYI, Mudassarkhan's methods will work if you have data in table format and you want to do a sequential read, like in an SQL Server Table. If you want to pick and choose specific cells all over the excel sheet (which I think you wanted by reading your post),
you have to use automation. If you decide you need automation, you need to have excel installed on the server.
I had to change MessageBox.Show to msgbox but then when clicking Button2 i got "Cannot find the Excel workbook. Try clicking Button1 to create an Excel workbook with data before running Button2. Missing Workbook?"
Appoligies for the multiple posts above but my company insist on IE6 and its sometimes goes wappy on this site.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'"
Dim connExcel As New OleDbConnection(strExcelConn)
Dim cmdExcel As New OleDbCommand()
cmdExcel.Connection = connExcel
connExcel.Open()
Dim dtExcelSchema As Excel.DataTable
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
connExcel.Close()
Alright, remember you coding for a web application and not a windows application. The article has code for a windows application so you have to remove or add some stuff depending on the requirement. For instance, MessageBox is windows application thing which
will not work in a web application. So remove that. Also, Button1_Click event handler creates an Excel workbook which is used in Button2_Click. Heres something I got online in VB for opening an existing workbook.
Dim excelApp As ApplicationClass = New ApplicationClass
Dim excelBook As Workbook
excelBook = excelApp.Workbooks.Open("C:\SampleExcel.xls", 0, False, 5, _
System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _
True, False, System.Reflection.Missing.Value, False)
Dim excelSheets As Sheets = excelBook.Sheets
Dim wSheet As Worksheet = excelSheets(1)
Dim cell1 As Range = wSheet.Range("B4:FZ4", Type.Missing)
Dim cell2 As Range = wSheet.Range("A4:A5", Type.Missing)
cell2.Value2 = "SampleText"
excelBook.Save()
excelApp.Quit()
stephen.adsh...
Member
20 Points
76 Posts
Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 01:38 PM|LINK
I have build an ASP.net webpage with VB.net backend and SQL Server.
I would like a user to be able to select an Excel document from there computer and the page to copy the contents of particular cells in each sheet within the Excel Document to my SQL database.
I have seen some examples but i cant seem to get any working using my exising database connection within web.config.
Does anybody know of any sample code that fits my scenario, it needs to be VB and as my Excel doc is a complicated beast it needs to transfer particular cells into my SQL database table.
Many Thanks in advance.
bullpit
All-Star
21838 Points
4822 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 02:12 PM|LINK
Here are the steps you need to take:
1. Let user select file from their machine by using FileUpload control.
2. Upload this file to a folder on your web server.
3. Since you want to specifically read some particular cells, you need to use Excel automation to read the excel sheet cell by cell.
Here's an article that explains it: http://support.microsoft.com/kb/302094
Here's another article: http://www.codeproject.com/KB/office/fasterexcelaccesstoc.aspx
Max
Let Me Google That For You!
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 02:20 PM|LINK
refer my article here
http://www.aspsnippets.com/post/2009/02/04/Read-Excel-using-ADONet.aspx
you can convert it to VB.net
http://www.developerfusion.com/tools/convert/csharp-to-vb/
Contact me
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:19 PM|LINK
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'" Dim connExcel As New OleDbConnection(strExcelConn) Dim cmdExcel As New OleDbCommand() cmdExcel.Connection = connExcel connExcel.Open() Dim dtExcelSchema As Excel.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) connExcel.Close()But
istephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:19 PM|LINK
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'" Dim connExcel As New OleDbConnection(strExcelConn) Dim cmdExcel As New OleDbCommand() cmdExcel.Connection = connExcel connExcel.Open() Dim dtExcelSchema As Excel.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) connExcel.Close()But
i am gettingstephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:19 PM|LINK
Bullpit, your articles showed me how to create a new spreadsheet and show the data but i have a spreadsheet already i just want to import the data in specific cells to my SQL db.
Mudassarkhan, i feel i am getting close with yours i have the following so far:
Dim strExcelConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblStatus.Text & ";Extended Properties='Excel 8.0;HDR=Yes'" Dim connExcel As New OleDbConnection(strExcelConn) Dim cmdExcel As New OleDbCommand() cmdExcel.Connection = connExcel connExcel.Open() Dim dtExcelSchema As Excel.DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) connExcel.Close()But i am getting the error connExcel.Open()
bullpit
All-Star
21838 Points
4822 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:25 PM|LINK
Did you check the Button2_Click event handler in that MS article. Thats where you retrieve the data.
FYI, Mudassarkhan's methods will work if you have data in table format and you want to do a sequential read, like in an SQL Server Table. If you want to pick and choose specific cells all over the excel sheet (which I think you wanted by reading your post), you have to use automation. If you decide you need automation, you need to have excel installed on the server.
By the way, what error are you getting?
Max
Let Me Google That For You!
stephen.adsh...
Member
20 Points
76 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:35 PM|LINK
I had to change MessageBox.Show to msgbox but then when clicking Button2 i got "Cannot find the Excel workbook. Try clicking Button1 to create an Excel workbook with data before running Button2. Missing Workbook?"
Appoligies for the multiple posts above but my company insist on IE6 and its sometimes goes wappy on this site.
mudassarkhan
All-Star
78956 Points
13402 Posts
MVP
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:42 PM|LINK
what error?? and where's ur query
Contact me
bullpit
All-Star
21838 Points
4822 Posts
Re: Import Excel Cell contents to SQL Server using SQLDATASOURCE
Feb 26, 2009 03:49 PM|LINK
Alright, remember you coding for a web application and not a windows application. The article has code for a windows application so you have to remove or add some stuff depending on the requirement. For instance, MessageBox is windows application thing which will not work in a web application. So remove that. Also, Button1_Click event handler creates an Excel workbook which is used in Button2_Click. Heres something I got online in VB for opening an existing workbook.
Dim excelApp As ApplicationClass = New ApplicationClass Dim excelBook As Workbook excelBook = excelApp.Workbooks.Open("C:\SampleExcel.xls", 0, False, 5, _ System.Reflection.Missing.Value, System.Reflection.Missing.Value, _ False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, _ True, False, System.Reflection.Missing.Value, False) Dim excelSheets As Sheets = excelBook.Sheets Dim wSheet As Worksheet = excelSheets(1) Dim cell1 As Range = wSheet.Range("B4:FZ4", Type.Missing) Dim cell2 As Range = wSheet.Range("A4:A5", Type.Missing) cell2.Value2 = "SampleText" excelBook.Save() excelApp.Quit()Max
Let Me Google That For You!