Read and write data to excel

[jhoanofarch]

I have a pre-formatted excel file which I need to use and populate with data from SQL database. This one should be done in ASP.Net Is there a way I could use an existing excel file and put data on a specific cell on the sheet and insert new record on the same excel file? I've already search microsoft website and i was able to see some sample but not using a pre-formatted excel. Does any one have solution for this one? Thanks!!!

[jhoanofarch]

I tried using this code:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")

but the properties and methods are not displayed. can anyone tell where can i find the list of all methods/properties for this object.

Can i use the code above to open an existing excel file in asp.net? How?

[Mike_R]

Mostly you just need to add a refrerence to the Microsoft Excel Library. Overall, you should give the Automating Office Programs with VB.Net (http://www.xtremevbtalk.com/showthread.php?t=160433) a read... and then come back with any further Q's that you have.

[randy_belcher]

I would just take the sample code from microsoft and test it with the excel template. You can open the template and do a "Save As". This way you never mess up your template. The only question is whether the cells in a newly added row will retain the format of the template. You can test that manually in excel.

[jhoanofarch]

Can you write the code on how to do this? I'm getting a hardtime on the syntax?

Thanks for the reply.

[jhoanofarch]

Thanks for the reply it is very informative.

The samples on the articles create a new instance of excel application but how about if i want to use an existing excel file and I want to update a specific cell and insert new rows. How should I do that? Hope you could share some code for this.

Second, i tried adding reference to miscrosoft excel but i still can't see the Microsoft.office.interop when i do imports

Hope you could help on this one. I need it badly.

Thanks!!!!

[herilane]

Can you explain in more detail what you are trying to achieve? I understand that you want to get data from a SQL database into an existing Excel sheet. Which part are you having trouble with? Getting the data? Opening the Excel file? Putting the data in the right place in the sheet? Something else?
And what do you mean by "insert new record on the same excel file"?

Also please note that we won't write the code for you... we can help you and advise, but not do the work for you. Please post the code you have, and we'll be happy to take a look.

I've merged your two threads about this. Please don't start multiple threads for the same question; it just leads to confusion.

[jhoanofarch]

Hi!
Right now I dont have a code that read existing file (pre-formatted), that is my problem.
If you could just show me how to open an existing excel file and i should be able to put data from SQL to a specific cell.

I got this code from microsoft website:

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

'Add data to cells of the first worksheet in the new workbook.
oSheet = oBook.Worksheets(1)
oSheet.Range("A1").Value = "Last Name"
oSheet.Range("B1").Value = "First Name"
oSheet.Range("A1:B1").Font.Bold = True
oSheet.Range("A2").Value = "Doe"
oSheet.Range("B2").Value = "John"

'Save the Workbook and quit Excel.
oBook.SaveAs(sSampleFolder & "Book1.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()


this code is useful but did not show how i could open an existing excel file.

Maybe you will be asking why i need the pre-formatted excel, the reason behind this is that, this excel file came from a bank company. We are not allowed to modify it because there are macros inside it which they use in their internal processing. My job is just to fill it out with the data from the server.

Hope this one helps you understand my problem. :) ;)
Thanks in advance!!!

[jhoanofarch]

I already found the answer. but when i tried it is not functioning
the code is

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Sheets

oExcel = New Excel.Application
oExcel.Workbooks.Open("D:\test.xls", )

oSheet = oBook.Worksheets(1)
oSheet("A1").Value = "1"
oSheet("B1").Value = "2"
oSheet("A1:B1").Font.Bold = True
oSheet("A2").Value = "3"
oSheet("B2").Value = "4"


the code is on the web form. The error is on the oExcel = New excel.application

the error is access denied. Do you have other way to solve the problem i mentioned on my previous post? thanks!!!

[Mike_R]

Try changing that line to oExcel = CreateObject("Excel.Application") Fingers crossed...

[herilane]

This looks like a security issue, not a coding problem...
Have a look at post #5 here (http://forums.aspfree.com/t14597/s.html), and these 3 MSDN articles:
KB 257757 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757)
KB 288366 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;288366)
KB 288367 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;288367)

[mark007]

I'm quite intrigued as to what you're actually wanting to do.

ASP.NET is a server side technology so would be accesiing Excel on the actual server. I can't really see what you want this for. If you want to access it on the client's machine you would use VBscript or soemthing.....

:)

[jhoanofarch]

It should on the server side. The problem is I dont know how. Could you help me on this one?

I'm new in web programming that why I dont know whats the best approach to do. I thought it would be the same as vb6 and MS Access VBA because i was able to this using vb6 and MS access.

Thanks guys for your replies!!!!

The actual error message is also posted below.



Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Sheets

oExcel = New Excel.Application '''''''' the error is on this line.'''''
oExcel.Workbooks.Open("D:\excelupload.xls", )

oSheet = oBook.Worksheets(1)

oSheet("A1").Value = "test"
oSheet("B1").Value = "only"
oSheet("A1:B1").Font.Bold = True
oSheet("A2").Value = "test 2"
oSheet("B2").Value = "test 3"


The error is:
Server Error in '/test' Application.

Access is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.UnauthorizedAccessException: Access is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

To grant ASP.NET write access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.

Source Error:

Line 42: Dim oSheet As Excel.Sheets
Line 43:
Line 44: oExcel = New Excel.Application
Line 45: oExcel.Workbooks.Open("D:\template.xls", )
Line 46: oBook = oExcel.Worksheets(1)

Source File: C:\Inetpub\wwwroot\test\excelupload.aspx.vb Line: 44

Stack Trace:

[UnauthorizedAccessException: Access is denied.]
test.excelupload.btnCreateEXCEL_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\test\exceluploading.aspx.vb:44
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.R aisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()


Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

[herilane]

Did you check out the pages I linked to?

[jhoanofarch]

Yes. I'm testing it now. I will let you know the outcome. Thanks a lot!!!!

[jhoanofarch]

After reading the articles, I found out, that I should not have done this in asp.net :whoops:

There is an alternative mentioned on the article which is to use a Client-side scripting. But problem is, it's hard to debug application because you won't see where the program stopped executing.

Now, I'm planning to change it to a windows application instead. Right choice or not? :D

[herilane]

It all depends on your needs and your situation... but to me, that sounds like an easier route. :) Then again I know very little about web programming.