Best Way to Export Web Data to Excel or Word

[Tim Attaway]

I have a report that is generated on a web page. I need to be able to export
parts of this data to either Word or Excel. The approach that I am currently
taking is to duplicate the portions of the report that I need to export into
HTML format and then send that to the client, directing it Word or Excel as
appropriate (using HttpContext.Current.Response.ContentType =
"application/ms-excel" or "ms-word"). Everything works great except for two
things. First, I'd really like to include a footer in the Word or Excel
version of the report and I haven't been able to figure out how to do that.
Second, Word properly handles the column widths specified in the HTML but
Excel seems to ignore them.

Does anyone have any better way to do this or a way to fix these problems?

Thanks.

 

[Cindy M.]

Hi =?Utf-8?B?VGltIEF0dGF3YXk=?=,

I have a report that is generated on a web page. I need to be able to export
parts of this data to either Word or Excel. The approach that I am currently
taking is to duplicate the portions of the report that I need to export into
HTML format and then send that to the client, directing it Word or Excel as
appropriate (using HttpContext.Current.Response.ContentType =
"application/ms-excel" or "ms-word"). Everything works great except for two
things. First, I'd really like to include a footer in the Word or Excel
version of the report and I haven't been able to figure out how to do that.
Second, Word properly handles the column widths specified in the HTML but
Excel seems to ignore them.

Does anyone have any better way to do this or a way to fix these problems?

The first problem is certainly because basic HTML doesn't have the concept of a
"footer". About all you could do would be to save a Word document with the same
basic structure as a "web page" then look at what that HTML contains. Then build
your HTML using these elements.

Beyond that, if you target Office 2003 you could write these files in the
Word/Excel XML file formats. Or if there's no huge hurry, target the Office 2007
file formats. When Office 2007 is released, there will be file converters that
will enable Office 2000+ apps to open and write to the new XML file formats.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[jini]

I have a report that is generated on a web page. I need to be able to export
parts of this data to either Word or Excel. ...

Does anyone have any better way to do this or a way to fix these problems?

I use iOpus iMacros to export/import data from and to several websites.
Using their Scripting Edition you can extract the web data directly via
VBA to your Excel sheet. This works very well for me. _Much_ better
than the Excel web query feature, especially since the software can
also deal with frames, pop-ups and all kind of other web browser
dialogs. And their support is outstanding. A free 30-day trial version
is available http://www.iopus.com/download/ . It includes a full Excel
VBA example for web scraping and form filling via Excel.

Jim

 

[Tim Attaway]

Cindy, Thanks for the response. I did what you suggested - create a Word
document with a footer and try to save it as a web page. When you do, it
saves the footer information in a separate file which is in a folder distinct
from the mother document. Since I'm trying to send this as a web response, I
couldn't figure out how to send the main document plus the folder
containining the footer information all at once.

Also, you didn't address the other question. Why does Word respect the
column widths that I set and Excel ignore them?

Could you point me to some documentation on the Office 2003 Word/Excel XML
file format? Thanks.

 

[Cindy M.]

Hi =?Utf-8?B?VGltIEF0dGF3YXk=?=,

I did what you suggested - create a Word
document with a footer and try to save it as a web page. When you do, it
saves the footer information in a separate file which is in a folder distinct
from the mother document. Since I'm trying to send this as a web response, I
couldn't figure out how to send the main document plus the folder
containining the footer information all at once.

How about the file type "Single file webpage"?

Of course you wouldn't get it in straight HTML. The webpage "standard"
doesn't have the concept of headers and footers, after all.

Also, you didn't address the other question. Why does Word respect the
column widths that I set and Excel ignore them?

Probably because Word is more "layout-oriented" than Excel. But you can try
asking in an Excel-specific newsgroup about that...

Could you point me to some documentation on the Office 2003 Word/Excel XML
file format?

http://msdn.microsoft.com/office/tool/xml/2003/download/#References

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[alex_f_il]

You can try SW Explorer Automation
(SWEA)http://home.comcast.net/~furmana/SWIEAutomation.htm.

The following example shows how to get a table from the
http://newsite.tab.co.nz/racing/ Web site using SWEA. SWEAProject.htp
was created by SWEA Visual Designer. SWEA designer contains Visual
Table Extractor. The Table Data Extractor extracts tabular data from
the Web pages. If a Web page contains repeating information patterns
than the data can be transformed into ADO.NET DataTable object.

public static void Main() {
SWExplorerAutomation.Client.ExplorerManager explorerManager = new
SWExplorerAutomation.Client.ExplorerManager();
SWExplorerAutomation.Client.Scene scene;
explorerManager.Connect(-1);
explorerManager.LoadProject("SWEAProject.htp");
explorerManager.Navigate("http://newsite.tab.co.nz/racing/");
scene = explorerManager["Scene_0"];
scene.WaitForActive(30000);
System.Data.DataTable dataTable;
dataTable =
((HtmlContent)(scene["HtmlContent_0"])).TableDataExtractor.DataTable;
explorerManager.DisconnectAndClose();
}

The DataTable can be stored into text file (CSV) and loaded by Excel or

Excel application can be called directly from the scraping program.