Writing to an excel spreadsheet via ASP.NET RSS

• • Reply
  

  timothym

  Member

  399 Points

  97 Posts

  Writing to an excel spreadsheet via ASP.NET

  Nov 25, 2003 12:06 PM|LINK

  Hi, below is the code I am using to try and write to the excel spreadsheet. Imports System.Data Imports System.Data.OleDb Public Class Excel_Write_Test Inherits System.Web.UI.Page Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Timmy\\Demographic Application\\CENTRAL.XLS;Extended Properties=Excel 8.0;" Dim objConn As New OleDb.OleDbConnection(strConn) Dim obj As New OleDbCommand("INSERT INTO (General$A86) VALUES ('2000')", objConn) objConn.Close() End Sub End Class //end code I do not know what to use - if the oledbcommand is the correct one. As it is, the cell A86 in the General worksheet is not being written to and I cannot figure out why. Any help would be greatly appreciated! Thanks in advance, Timothy
• • Reply
  

  ASOHN

  Participant

  995 Points

  199 Posts

  Re: Writing to an excel spreadsheet via ASP.NET

  Dec 29, 2003 02:58 AM|LINK

  Here is the connection string you should use:

  "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Timmy\\Demographic Application\\CENTRAL.XLS;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

  When you connect, it treats the excel workbook as an acces database. Build the SQL query just as you would an access database. Treat the worksheets as tables - however, in order to reference the worksheet, you must put a $ before the worksheet name. The db provider translates the first row of each column as the name of the column. Also, be sure to add an sequential ID feild in the worksheet. So, in order to do what you want you would use the following SQL query: (Lets say that the name of column "A" is "Price")

  "INSERT INTO $General (Price) VALUES ('2000') WHERE ID = 86"
  

• • Reply
  

  ASOHN

  Participant

  995 Points

  199 Posts

  Re: Writing to an excel spreadsheet via ASP.NET

  Dec 29, 2003 03:08 AM|LINK

  Just to correct my previous post: the "WHERE" part of that SQL statement isn't supposed to be there. That statement(minus the where) just adds a row to the database(you of course need to specify the other rows as well, you cant specify only one cell to write to) If you want to update a single cell(that exists already in the worksheet) then you would use the following statement:

  "UPDATE $General SET Price='2000' WHERE ID=86"