Tuesday, August 30, 2005

 

Exporting Datawindows to Excel without loosing Format

Hello friends,

Recently, I have been reading threads about the subject of this post. There are many people who needs to export a DW content to Excel. The easiest way to achive this is by using SaveAS method, but wait up...., someone could say, I have tried this already, but it didn't work when using computed fields or what about crosstabs. Well, don't get scared !!!, there's a way out of this. As many people knows Datawindow is capable to expose its data and format as HTML, so by using this and a little trick you can achieve to export data from dw to excel.

To do this, you should follow these steps:

Very Important:

Before following this instructions, go to DW Designer and make sure the css property is enabled for your DW object. To do this: open DW designer, select the DW object and open the Datawindow Painter, on the properties tab control select "HTML Table" tab, and make sure the CSS property is checked. Without this you will not be able to see font colors or backgroud colors styles.

Windows Enviroment:

1. Let's supose that you have a buton to export data to excel, on the button's clicked event, you should use this:

For powerbuilder
Sintax dw_controlname.saveas ({filename.xls}, HTMLTABLE!,{true or false to show headers)

Example:
dw_1.saveas("excelreport.xls",HTMLTable! ,true)


For .net

VB.net
Sintax dw_controlname.saveas ({filename.xls}, FileSaveAsType.Excel,{true or false to show headers)
Example:
dw_controlname.SaveAs("excelreport.xls", FileSaveAsType.Excel, True)

C#
Sintax dw_controlname.saveas ({filename.xls}, FileSaveAsType.Excel,{true or false to show headers);
Example:
dw_controlname.SaveAs("excelreport.xls", FileSaveAsType.Excel, True);

2. Open the new file, voala!!!!, ther you have it. You should be able to see the data in excel.

3. Caveats, nothing is for free !!!!. There's a small problem with this approach, but is feasible to fix it. We're cheating to excel, because we're not exporting the data as excel, instead, we're saving HTML and CSS content as file with excel extension. Excel is able to read HTML and HTML Tables. The problem comes with CSS, because the DW generates an extra semicolon ";" and because of this, excel is not able to apply the style. but i'll explain how to avoid this problem.


Web Enviroment:

1. In a web enviroment there are some changes you have to do. Most problably you'll want to redirect the user to a new page that shows the data in a excel file. Firs of all you should code this on the button's clicked event:

Vb.net
Response.Redirect ("Newpage.aspx")
C#
Response.Redirect ("Newpage.aspx");

2. follow these code to retrieve the excel file

VB.net

on the Newpage.aspx, go to "DOCUMENT PROPERTIES" and go to "contentType" property and change the value to: application/vnd.ms-excel

on the Newpage.aspx ' s page load event

private sub Page load()

Dim ds As DataStore
Dim SQLCA As New AdoTransaction
SQLCA.Connection = dalFactory.getConexion
SQLCA.BindConnection()
ds.LibraryList = Server.MapPath("../") & "yourlibrary.pbl"
ds.DataWindowObject = "d_yourdatawindowobject"
ds.SetTransaction(SQLCA)

if (ds.retrieve()>0) then
Response.Write ( ds.Describe("DataWindow.Data.HTML") )
end if

End sub


C#

On the Newpage.aspx, go to "DOCUMENT PROPERTIES" and go to "contentType" property and change the value to: application/vnd.ms-excel

On the Newpage.aspx ' s page load event
private void Page load()
{
DataStore ds;
AdoTransaction SQLCA = New AdoTransaction();
SQLCA.Connection = dalFactory.getConexion;
SQLCA.BindConnection();
ds.LibraryList = Server.MapPath("../") + "yourlibrary.pbl";
ds.DataWindowObject = "d_yourdatawindowobject";
ds.SetTransaction(SQLCA);

if (ds.retrieve()>0)
{
Response.Write ( ds.Describe("DataWindow.Data.HTML") );
}

}

With this code, you should be able to redirect the user to a new page and show the content of a dw as a Excel document.

Enhacements:

Below you'll find the code to Fix CSS problem (Mentioned above).

VB.net
change this:
Response.Write ( ds.Describe("DataWindow.Data.HTML") )
to this:
Response.Write ( ds.Describe("DataWindow.Data.HTML") .replace("{;","{"))

C#
change this:
Response.Write ( ds.Describe("DataWindow.Data.HTML") ) ;
to this:
Response.Write ( ds.Describe("DataWindow.Data.HTML") .replace("{;","{"))


Ok folks, I hope you find this useful !!!!!

Regards, and HAPPY CODING !!!!!!!

Ing. Carlos A. Lone
"The guy from Guatemala"

P.D. Excuse me for my bad english.

Comments:
NSU - 4efer, 5210 - rulez

 
Gracias, necesitaba saber esto.
 
This is a "basura code"
 
Hi,
I tried to export cross tab into Excel format. I checked CSS property on HTML tab but still it is not exporting Font & background color and format.

Regards,
Hiren
 
Hey, this worked for me !!! Thanks a lot!
 
i trying to export 0012345, but the 00 missing in excel. what can i do?
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?