Import Excel file into OWC11 in .net 2

[jpandviv]

Ok. Here it goes...

Background: I'm trying to allow my user to load a local Excel file
into and OWC11 spreadsheet control and modify it before uploading it
into a database.

What I have done so far: I have been able to place a spreadsheet
control on the aspx form. I also have the code in place that will
allow the user to find an Excel file and load it into an OWC control
(server-side I think).



code-behind in import.aspx.vb:

Protected Sub LoadSheet()

Dim o_Sheet As New Microsoft.Office.Interop.Owc11.Spreadsheet
Dim i_Cols As Integer
Dim i_Rows As Integer
Dim IntICol As Integer
Dim o_EXsheet As Excel.Worksheet
Dim o_EXapp As Excel.Application
Dim o_EXwbk As Excel.Workbook
Dim i As Integer

o_EXapp = New Excel.Application
o_EXwbk =
o_EXapp.Workbooks.Open(Filename:=ImportFileCtrl.PostedFile.FileName)
o_EXsheet = CType(o_EXwbk.Worksheets(1), Excel.Worksheet)

i_Rows = o_EXsheet.UsedRange.Rows.Count
i_Cols = o_EXsheet.UsedRange.Columns.Count

o_Sheet.Rows.Clear()
o_Sheet.Columns.Clear()
o_Sheet.TitleBar.Caption = ImportFileCtrl.FileName

For i = 1 To i_Rows
For IntICol = 1 To i_Cols
o_Sheet.ActiveSheet.Cells(i, IntICol).Value =
Trim(o_EXsheet.Cells(i, IntICol).value & "")
Next
Next

'ImportGrid = o_Sheet <-- commented out because I get an error
here

o_EXwbk.Close("No")
o_EXapp.Quit()


MsgBox("Finished Uploading Roster", vbOKOnly, "NASCC Roster
Upload")

End Sub

object declaration on the form:
<object id="ImportGrid"
classid="CLSID:0002E559-0000-0000-C000-000000000046" style="z-index:
109; left: 18px; width: 621px; position: absolute; top: 146px; height:
192px" name="ImportGrid">
</object>

I think I'm running into a server-side/client-side issue, but I'm not
sure. Basically, I'm looking for a way to bind the data I loaded in
the procedure to the control I placed on the form. Is this possible?
Can someone out there in asp-land please tell me what I'm doing wrong.
Your help is appreciated more than you know.

Regards.

 

[Alvin Bruney [MVP]]

I sense confusion in the force tehehe.

You have an interop spreadsheet (server side) AND and embedded spreadsheet
client-side. I suspect you don't need this many objects. You typically use
either or. The server-side object will not allow you to touch the client.
The client object will not allow you to touch the server.

What I have done so far: I have been able to place a spreadsheet control
on the aspx form.

There really is no technical reason why this should work. If you say it
does, I will take your word for it.

First decide where the data will be uploaded from (client or server) then
use the appropriate object. BUT not both.

 

[jpandviv]

I agree with you completely. I used the fileupload control (.net 2) on
the form which I'm not totally sure I need. I think that uploads the
file to the server which is definitely not what I want to do. I want
the use the client object on my form allowing the user to load and edit
a spreadsheet client-side. Then I want to submit that client-side
control to the server to upload to the database.

Is this possible?

There really is no technical reason why this should work. If you say it
does, I will take your word for it.

I was able to place the OWC11 spreadsheet control on the form manually
using the object tag and the classid. When I'm programming the form, I
can't "see" the control because it's on the client-side which is why I
was using the client-side vb script.

Thanks for getting back to me so quickly.

JP

 

[Alvin Bruney [MVP]]

The spreadsheet will load a file using either a URL or a file path. The file
path points to the client desktop, the URL can point anywhere there is a
server. Once the data is in the control, simply post the data to the server
and use code to form your query. If you don't need the user to see the data,
you are better off using the file upload to move the file to the server and
forming a query from there - you wouldn't even need a spreadsheet control.

 

[jpandviv]

Thanks for getting back to me Alvin. The problem I'm having is as
you've stated. I need to get the data into the control. The user will
need to see and modify the data in the control before uploading it into
the database. The tool I'm using is the .net 2 FileUpload control.
I'm not sure, but I think this control uploads the file directly to the
server in effect skipping the step I need which is loading it locally
so the user can modify the data.

Once the user has modified the data, then I'll fire off some sort of
script to handle that part. I won't even get into that part yet...

Thanks for your patience and help with this issue Alvin.

JP

 

[Alvin Bruney [MVP]]

Ok, but the design leaves a lot to be desired but working with what you have
here goes:
From a high level, you are moving the file from the client to the server
(via .net 2 fileupload) and then back to the client for *review* and then
back to the server for saving. You can see why that is ugly.

Assuming the file is located on the server (after .net2fileuload) in folder
\myfolder\file.txt. You load it into the owc control using sp.HTMLDATA =
http://server/myfolder/file.txt roughly. At this point, the user *reviews*
and makes changes. Provide the user with a save button. On save, you dump
the data into a text box control set to run on the server.
textbox1.text = sp.CSVDATA;

in your page load routine, you examine textbox1 and parse its contents to
build your query. You can see bits of the code in action by search the black
book using amazon's search feature if you care.

 

[jpandviv]

Thank you Alvin for all of your help. I'm new to .NET (no surprise
there, huh?), but old to programming, so the learning curve is rather
steep right now. I searched for your book at B&N, but found I needed
to order it. I will definitely make that purchase.

Regards,

JP

 

[jpandviv]

I just recieved your book and it has some great informaiton in it.
Thanks for doing that for all of us who needed the help. Question for
you though. I have some pretty big spreadsheets (8,000+ rows). Won' t
this cause problems if people are uploading this in a server-side text
box? Should it be broken up before submitting the data?

Thanks,

JP

 

[Alvin Bruney [MVP]]

I have production code that loads 20,000 rows of data x 12 columsn withought
a hitch - if that answers your question. I used a couple old tricks to get
that to load in under 20 seconds but you should know that it is definitely
possible.