ASP.NET and OWC Spreadsheet

[stockbuildingsupply]

hello everyone,
i am developing a web application that needs to give Excel functionality
within the clients browser. here are the specs:
- user should not be able to save the workbook to their box.
- user should only be able to modify/view a certain range.
- input into above range must be validated.
- when user clicks update the data in the spreadsheet is extracted and
sent to the database.
what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page but
none of its events are exposed. i know nothing about OWC and am eager to
explore their capabilities especially for this project. any input is highly
valued.
thanks,
sbs

 

[Jeffrey Tan[MSFT]]

Hi,

Currently, we are finding someone to help you on this issue, and we will
reply to you ASAP. Thanks for your understanding.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

 

[Alvin Bruney [MVP - ASP.NET]]

While you wait on Tan's assistance you may want to consider a few
possibilities.

- user should not be able to save the workbook to their box.

If you use the OWC, you have a few options. Simply remove the save option
from the toolbar, context menu and short cut key. You can do this at startup
or override the save action using the eventing model.

- user should only be able to modify/view a certain range.

OWC offers range protection thru passwords. You can explicitly set passwords
to protected specific ranges. The black book discusses some caveats of
password protection that you should be aware of if you choose that route.
Finally, for less draconian measures you can simply make the target ranges
readable, writable or both thru manipulation of the range object.

- input into above range must be validated.

Use the eventing model to examine input as data is added to each cell

- when user clicks update the data in the spreadsheet is extracted and
sent to the database.

I've posted code in here a few months ago on how to accomplish this
relatively simple task.

what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then
streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page but
none of its events are exposed. i know nothing about OWC and am eager to
explore their capabilities especially for this project. any input is
highly
valued.

Using the OWC, you gain the advantage of an object oriented platform that is
easy to program against. Also, there are certain disadvantages of using
content type rendering since nothing else but excel can be on that page. The
advantage of the components here is that this restriction is lifted.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

 

[Wei-Dong XU [MSFT]]

Hi,

For your four scenario requirement, I list my replies for you on each
requirement below:
1. - user should not be able to save the workbook to their box.
Spreadsheet can only export the spreadsheet data into Excel or export to
one html page etc. There is one "export to excel" button available at the
Spreadsheet toolbar. Internally Spreadsheet object uses the method Export
to export the data. If we disable the spreadsheet toolbar, the customer
will not get the data from spreadsheet then.

However, one customer can copy your spreadsheet can paste it into Excel.
This will be very easy and we can't stop it. I don't know whether your
scenario accept this. If not, SpreadSheet will not be suitable for you.
* for fully securing your code from saving, I'd suggest you will need to
develop your own Spreadsheet-like activex / winForm control to render the
data at client.

2. - user should only be able to modify/view a certain range.
At server side, you can extract the data and then show them in spreadsheet
for the customer. I hope your data at server is not stored in Excel because
the server-side automation is not supoprted by Microsoft now:
257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/?id=257757
We have to wait for the Whidbey release on the server-side automation.
There will be one ServerClass available to operate the Office file at
server.

For passing the data to the client and then displaying them in spreadsheet,
generally spearking, there are two methods:
A. pass the data to the client and then write one javascript code to
initialize the spreadsheet with these data
B. use one data source client activex control (for example the datasource
control at Office web component) to obtain the data and then show them at
the Spreadsheet.
C. Web Service (recommended)
One MSDN article will give one very good demonstration for you on this:
http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/default.aspx

3. - input into above range must be validated.
You will need to sink the spreadsheet event SheetChange to perform the
validation. Since Spreadsheet is one client side activex control, you will
need to write javascript code to handle this event.

4. - when user clicks update the data in the spreadsheet is extracted and
sent to the database.
Spreadsheet is one client ActiveX control, we can obtain the spreadsheet
data from its XML property. With the help of XMLHTTP, we can send the xml
string into server very easily. This kb article introduces the method on
how to pass the data into server in this way:
How To Submit Form Data by Using XMLHTTP or ServerXMLHTTP Object
http://support.microsoft.com/?id=290591

Please feel free to let me know if you have any question. It is my pleasure
to be of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[stockbuildingsupply]

alvin,
thanks for your reply. what is the black book? i am new here so that is
probably a dumb question. also, why in ASP.NET, cant the OWC control be
directly referenced in my code or any of its events? is it because of some
interop thing? is the only way to use the control in ASP.NET through j/vb
script on the client side? thanks again, dennis

While you wait on Tan's assistance you may want to consider a few
possibilities.

- user should not be able to save the workbook to their box.

If you use the OWC, you have a few options. Simply remove the save option
from the toolbar, context menu and short cut key. You can do this at startup
or override the save action using the eventing model.

- user should only be able to modify/view a certain range.

OWC offers range protection thru passwords. You can explicitly set passwords
to protected specific ranges. The black book discusses some caveats of
password protection that you should be aware of if you choose that route.
Finally, for less draconian measures you can simply make the target ranges
readable, writable or both thru manipulation of the range object.

- input into above range must be validated.

Use the eventing model to examine input as data is added to each cell

- when user clicks update the data in the spreadsheet is extracted and
sent to the database.

I've posted code in here a few months ago on how to accomplish this
relatively simple task.

what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then
streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page but
none of its events are exposed. i know nothing about OWC and am eager to
explore their capabilities especially for this project. any input is
highly
valued.

Using the OWC, you gain the advantage of an object oriented platform that is
easy to program against. Also, there are certain disadvantages of using
content type rendering since nothing else but excel can be on that page. The
advantage of the components here is that this restriction is lifted.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

hello everyone,
i am developing a web application that needs to give Excel functionality
within the clients browser. here are the specs:
- user should not be able to save the workbook to their box.
- user should only be able to modify/view a certain range.
- input into above range must be validated.
- when user clicks update the data in the spreadsheet is extracted and
sent to the database.
what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then
streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page but
none of its events are exposed. i know nothing about OWC and am eager to
explore their capabilities especially for this project. any input is
highly
valued.
thanks,
sbs

 

[stockbuildingsupply]

wei-dong,
thanks for your reply. i will take a look at the links/suggestions you
provided. from your post i gather it is only possible to manipulate the OWC
control through client side scripting. if so, how do you pass server side
data to client side j/vb script code in this context? code examples would be
very helpful. thanks again.

 

[Alvin Bruney [MVP - ASP.NET]]

black book is listed here www.lulu.com/content/82300

All ActiveX objects without exception, run on the client. Your code-behind
files run on the server. The two can't talk to each other because of the
endless ocean between the two. What you must do is force your server side
code to push instructions thru the response stream to the client where the
script can execute. It's not terribly difficult. Here is an example from the
asp.net pro magazine june issue.

if(!page.ispostback)
Response.Write("<OBJECT id='pTable' style='Z-INDEX: 109; LEFT: 8px; WIDTH:
502px; POSITION: absolute; TOP: 8px; HEIGHT: 217px' height='217' width='502'
classid='clsid:0002E55A-0000-0000-C000-000000000046'VIEWASTEXT></OBJECT>");


//send the load instructions

Response.Write(@"<script>document.all.pTable.ConnectionString =
'Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties=\'DSN=sampledb;\''</script>");

Response.Write("<script>document.all.pTable.DataMember =
'SampleDb'</script>");



you should get the general idea


--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

alvin,
thanks for your reply. what is the black book? i am new here so that is
probably a dumb question. also, why in ASP.NET, cant the OWC control be
directly referenced in my code or any of its events? is it because of
some
interop thing? is the only way to use the control in ASP.NET through j/vb
script on the client side? thanks again, dennis

While you wait on Tan's assistance you may want to consider a few
possibilities.

- user should not be able to save the workbook to their box.

If you use the OWC, you have a few options. Simply remove the save option
from the toolbar, context menu and short cut key. You can do this at
startup
or override the save action using the eventing model.

- user should only be able to modify/view a certain range.

OWC offers range protection thru passwords. You can explicitly set
passwords
to protected specific ranges. The black book discusses some caveats of
password protection that you should be aware of if you choose that route.
Finally, for less draconian measures you can simply make the target
ranges
readable, writable or both thru manipulation of the range object.

- input into above range must be validated.

Use the eventing model to examine input as data is added to each cell

- when user clicks update the data in the spreadsheet is extracted and
sent to the database.

I've posted code in here a few months ago on how to accomplish this
relatively simple task.

what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then
streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page
but
none of its events are exposed. i know nothing about OWC and am eager
to
explore their capabilities especially for this project. any input is
highly
valued.

Using the OWC, you gain the advantage of an object oriented platform that
is
easy to program against. Also, there are certain disadvantages of using
content type rendering since nothing else but excel can be on that page.
The
advantage of the components here is that this restriction is lifted.

--
Regards,
Alvin Bruney - ASP.NET MVP

[Shameless Author Plug]
The Microsoft Office Web Components Black Book with .NET
Now available @ www.lulu.com/owc, Amazon.com etc

hello everyone,
i am developing a web application that needs to give Excel
functionality
within the clients browser. here are the specs:
- user should not be able to save the workbook to their box.
- user should only be able to modify/view a certain range.
- input into above range must be validated.
- when user clicks update the data in the spreadsheet is extracted and
sent to the database.
what i have done to achieve this functionality is build the above
requirements via vba into an Excel workbook. the workbook is then
streamed
via the ContentType() and WriteFile() methods of the response object in
ASP.NET. is there an easier way to do this using an OWC Spreadsheet
component. I have tried referencing the component in my asp.net page
but
none of its events are exposed. i know nothing about OWC and am eager
to
explore their capabilities especially for this project. any input is
highly
valued.
thanks,
sbs

 

[Wei-Dong XU [MSFT]]

Hi,

You are very welcome!

"from your post i gather it is only possible to manipulate the OWC control
through client side scripting."
Sorry that I haven't made this very clear for you. We can manipulate OWC
control at server side, however, for providing one interatctive OWC control
for the customer at client side, we will need to use the script code.

"if so, how do you pass server side data to client side j/vb script code in
this context?"
For filling the data to Spreadsheet, the best code sample is the Office XP
Web Component Toolpack which contains a lot of sample code for you:
http://www.microsoft.com/downloads/details.aspx?FamilyId=BEB5D477-2100-4586-
A13C-50E56F101720&displaylang=en

And the MSDN article I suggested in the previous post demonstrate how to
pass the data in xml to Spreadsheet.
http://msdn.microsoft.com/msdnmag/issues/03/10/OLAP/default.aspx

Please feel free to let me know if any question. I am more than happy to be
of any assistance.

Best Regards,
Wei-Dong XU
Microsoft Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.