OWC spreadsheet import to DataSet

[dave]

Hi, I am using OWC10 in a ASP.NET Web Application with VB.NET codebehind. I
need to import specific data from a spreadsheet (i.e. "A17") to a DataSet.
I have tried two methods:

1)I saved the spreadsheet as .xls file and tried to open it as an OLE
Connection with the following connection string:

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sheetPath &
";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2;"""

This works with other .xls files, but when I try it on ones generated by
OWC, I get this exception:

"External table is not in the expected format."

I assume this is a problem with the format OWC saves as...how can I get
around this?


2)I saved the spreadsheet as a .xml file and called the DataSet.ReadXml
function, and get this exception:

"A column named 'WorksheetOptions' already belongs to this DataTable: cannot
set a nested table name to the same name."

I assume this happens because the ReadXml does not know what columns to look
for. How can manually set these?


Also, if there is a better way to accomplish my goal than I would love to
hear it. I really appreciate the help.

Thanks,

David

 

[Alvin Bruney [MVP]]

I've had success with converting the contents of the dataset into a comma
delimited string and pushing the entire string to the client in the request
object. You may try this approach to see if it makes sense for you. The
downside is that you lose formatting capabilities since it is no longer XML.
However, you gain performance and reduced bandwidth.

There's code in the black book which you can google through amazon's search
inside if you aren't interested in a purchase. I do believe that i have
posted the code in here long ago as well.

--
Regards,
Alvin Bruney
------------------------------------------------------
Shameless author plug
Excel Services for .NET is coming...
https://www.microsoft.com/MSPress/books/10933.aspx
OWC Black Book www.lulu.com/owc
Professional VSTO 2005 - Wrox/Wiley