DataBinding to an OWC 11 Spreadsheet Control

[N8dagr8]

First, I'm creating a WinForms application, not a web page.

Second, I have a .Net Dataset with data I want to display to the user.
Originally, we were using the DataGrid control, but our customer is more
familiar with Excel and the DataGrid control in .Net 1.1 is not nearly
tailorable enough for our needs.

Third, we've placed an OWC 11 spreadsheet control on our form. While I've
found several articles describing how to load data into the spreadsheet, I
have not found any solution describing "databinding" to the spreadsheet like
you can do on the datagrid.

Basically, I'd like the Excel web component to be a view into the .Net
Dataset, such that when a user makes a change in Spreadsheet, it is sent back
to the .Net Dataset.

I know you CANNOT use a .Net DataSet as a datasource for the Excel Web
Component. So I was planning to convert the .Net DataSet into an ADO
Recordset and using this as the DataSource for the web component.

Will this meet my needs (i.e., display data to user and update data in
Recordset when user edits data in the Excel control)? If not, does anybody
have any alternative suggestions?

Thanks,
Nate

 

[brettveenstra]

Nate,

Does this fit your bill?

Transform DataSet to Spreadsheet XML
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319180

I'd highly recommend that you create a TEMPLATE to store
header/formatting/etc once you get that setup on the control. Just
copy the XMLData property and save it a separate .XML in your project
and you can reference that as the initial XMLDATA property during
runtime...



This will give you some hints on tying to the OWC events in WinForms if
you are having problems there...
http://support.microsoft.com/kb/823982

 

[N8dagr8]

Brett -

Thanks for your responses. I appreciate the help. The article on converting
dataset to XML data is very useful as is the event article at MS.

Unfortunately, I'm not that worried about gettting data into the Spreadsheet
WebComponent as I am getting it back out into a Dataset. I know the
Spreadsheet control has DataBinding events so I would assume there's some way
to databind the Spreadsheet control to a datasource. I could capture that
the user has made a change to the spreadsheet, but then I'm forced to figure
out what change was made, and how to incorporate that change back into the
dataset.

I was hoping for something similar to the two-way relationship between a
DataGrid and a DataTable. The one-way between a SpreadSheet and dataset is
pretty straightforward...either convert DataSet into XML Spreadsheet or ADO
Record set. How I get the Spreadsheet data back into the DataSet I'm not sure
about.

My current thoughts of how to do this are as follows:
Write an XML Spreadsheet that contains XML describing what the current cell
contents hold (i.e., Name, Address, Zipcode), etc. When a user makes a change
to the spreadsheet, export the spreadsheet as xml. Read in the spreadsheet as
XML into my application, parse the xml, pulling out all the data that is
mapped to fields in my dataset and updating the dataset with the value of
that cell in excel.

Seems like a rather messy workaround.

Any other advice would be greatly appreciated --
Nate

 

[brettveenstra]

Nate,

I'm basically in your same boat... just been plodding through the OWC
documentation and web links when I can find them...

At this very moment, I was researching databinding with the spreadsheet
object because I believe that will give better event handling on the
client side.

I'm also hopeful that it will allow me to push back a lite version of
the XML data during my mass save.

Sorry I don't have anything more useful right now...

-B