PowerBuilder Corner

Dear Friends,

This time I want to share with you this article about dw.net date formatting. I will explain how you can set the date format in dw.net (Web Environment). I have been reading many messages from Sybase’s costumers asking for a work around. I hope this can help you solve the problem.

First of all, we need to create a new Web Project in Vs.net (at the end of this article you’ll find a link to a zip file containing the example project). After that, drag & drop a DW control on the WebForm. Make sure you assign both “libraryList” and “datawindowobject” properties.

Before explaining the work around, I would like to describe how Dw.net does handle the rendered html. Dw.net has a server side engine to handle data rendering, every time a page is requested, this generate the entire html and javascript rendering that is sent back to the client. If you check the source code in the client side, you’ll find a bunch of javascript code along with html. Javascript is used to perform client data validation and manipulation, this means that DataType checking, error messaging and some how data formatting is done by this code. Luckily, Sybase’s DW.net let us manually handle how this javascript code is rendered.

In order to handle the date formatting the way we want, we have to follow these steps:

1. Go to dw controls’ properties, you’ll find at the bottom of the property window a Link “Generate Client Management Javascript”. watch the picture:
   
   
   
   
   
   A pop up window will show, watch the picture:
   
   
   
   
   
   This will save javascript files on the selected path. Open the “dwdatetime.js” file, and find the following text (“Hint: use find or search tools)
   
   //var DW_PARSEDT_monseq = 0;
   
   //var DW_PARSEDT_dayseq = 1;
   
   //var DW_PARSEDT_yearseq = 2;
   
   These constants are used to define how the date will be parsed. So if you need to change the format to ‘dd/mm/yyy’ you should set this constant as follow:
   
   var DW_PARSEDT_monseq = 1; // this is the month position
   
   var DW_PARSEDT_dayseq = 0; // this is the day position
   
   var DW_PARSEDT_yearseq = 2; // this is year position
   
   
   
   
2. Once you have done it, there’s one last thing to do. Notice that this format will be applied to date textbox and dropdown calendar textbox as well.
   
   
   In orde to use the Javascript files, you have generated. You should change some properties in the property window. Watch the picture:
   
   
   
   
   
   These properties are used by the engine to check if it has to generate the javascript files or if it has to use the generated file. Note: You could delete “CommonManagementJavascriptFile”, “NumberManagmentJavascriptFile” and “StringManagementJavascriptFile” values. This means, that engine will generate automatically these files, and it will use only the datetime javascript file. You should note that using javascript files is a better option regarding performance. Because these files can be cached on the client and it will reduce the amount of code send it from the server.
   
   

So that’s it. I hope this help you to solve the problem. My best regards to all of you. Happy Programming !!!.

Here is the link to the project zip file : http://www.geocities.com/progra1lasi/DateMask.zip

If you have questions, you could use the comments option or you could send an email to carlone@galileo.edu.

Regards,

Ing. Carlos A. Lone
Guatemala

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.

Powerbuilder provides a rich set of String Functions, even though, There's no function for a massive replace, for example: I have this string "Hello my name is {field} , my name {field} comes from ... thanks regards from {field}" and you want to replacle the string "{field}" with "Carlos Lone", Fortunately, while i was surfing the net at Sybase code-exchange site, I found this useful function, this enable us to perfom the action described in the previous example.

/*A String Occurrence Search and Replace RoutineThe following code demonstrates a string occurrence search and replaceroutine.This routine works generically for any string. For example,if old_str = "red" and new_str ="green", all occurrences of "red" inside of mystring will be replaced with "green".
Parameters
Name = source Type = String
Name = look_for Type = String
Name = replace_with Type = String
*/

//variables
int start_pos=1,len_look_for

len_look_for = len(look_for)
//find the first occurrence of look_for ...
start_pos = Pos(source,look_for,start_pos)
//only enter the loop if you find whats in look_for
DO WHILE start_pos > 0
    //replace look_for with replace_with ...
    source = Replace(source,start_pos,Len_look_for,replace_with)
    //find the next occurrence of
    look_forstart_pos = Pos(source,look_for,start_pos+Len(replace_with))
LOOP
return source


I hope you can find this helpful.


Regards,
Ing. Carlos A. LoneGuatemala

Hello dear friends,

Sybase is promoting a new comunity of Developers called ISUG (International Sybase User Group) http://www.isug.com, by joining this groud you have the oportunity to access news, technical documents, white papers, etc about sybase product, even more you have the oportunity of comunicate across the sybase's products users network all around the World, In my opinion this is wonderful, because this enable us (developers) to have a new source of support and information.

Have a nice Day, regards !!!

Carlos Lone
Guatemala

Hello dear Friends, My name is Carlos Lone, I'm from the beautiful country of Guatemala, I have started this Blog so we can Share Code Snipets, Bugs, Experiences, Thoughts, Examples, etc. about the PowerFul tool from Sybase, PowerBuilder. So everyone is invited to join this group. I hope that you can find interesting stuffs here ...

Regards


Ing. Carlos A. Lone
Guatemala.