Save To Excel using HTMLTable!

When saving datawindow to Excel using the Excel! datawindow will just save all the columns to the file and the format you wanted (Headers, Groups..etc ).

You can use HTMLTable! to make it look like excel file.

string        ls_filename, ls_path, ls_describe
long ll_retval

ll_retval = GetFileSaveName ( "Select File", ls_path, ls_filename, "Excel", "Excel (*.xls),*.xls")

if ll_retval <> 1 then return

if UPPER(right(trim(ls_filename),3)) = 'XLS' then
  //Use HTMLTable! instead of Excel but rename the file with an xls extension 
  dw_1.of_saveas("Filename.xls",ls_path, HTMLTable!,TRUE)
end if

Alternating Detail Row Color in Datwindow.

this.Modify("datawindow.detail.color='255~t if(mod(getrow(),2)=0,RGB(198,226,255),RGB(255,255,255))'")

Reading/Writing To Text Files

integer li_FileNum, li_FileNum_Write
string ls_Emp_Input, ls_output
long ll_FLength
long ll_count

ll_count = 1
ll_FLength = FileLength64("C:\countries.txt")
li_FileNum = FileOpen("C:\countries.txt",  LineMode!)
li_FileNum_Write = FileOpen("C:\country2.TXT", LineMode!, Write!, LockWrite!, Append!)

IF ll_FLength < 32767 THEN
  do  while FileRead(li_FileNum, ls_Emp_Input) > 0
    ls_output = 'dw_1.Setvalue("country", ' + string(ll_count) +  ', "' + trim(ls_emp_input) +'")'
    FileWrite(li_FileNum_Write, ls_output)
    ll_count = ll_count +1
  loop

END IF

Get the Display Value of a DDDW Column

Solution #1 (Recommended):

---

Use the Describe() Evaluate function. In the following example the ddlb or dddw column is called state_code.

string rownumber,displayvalue
rownumber = string(dw_1.getrow())
displayvalue = dw_1.describe("Evaluate( 'lookupdisplay(state_code) ', "+rownumber+" )")

This solution does not require the definition of an additional computed column on the datawindow. Note: This solution will not work in the itemchanged event of the main datawindow. It must be done in an event that occurs After the itemchanged event has completed by creating a custom user event ON THE WINDOW mapped to pbm_customxx called getdisplayvalue and then windowname.postevent (getdisplayvalue) from the itemchanged event of the datawindow.
If you have several dddw columns or other columns, you may want to use a choose case statement to have an action associated with each column name.

CHOOSE CASE dw_1.getcolumnname()
CASE "state_code"
  displayvalue = dw_1.describe("Evaluate( 'lookupdisplay(state_code) ', +rownumber+" )")
CASE "city_code"
  displayvalue = dw_1.describe("Evaluate( 'lookupdisplay(city_code) ', "+rownumber+" )")
CASE ELSE
 
END CHOOSE

Solution #2:

---

1. Go into the Datawindow painter and add a computed column.
2. The expression should be: Lookupdisplay(dept_id) where dept_id is the name of your dddw column.
3. Name the computed column ‘display’.
4. Place the computed column anywhere since we will make it invisible with Modify().
5. Go into the window and add a user event called ue_lookup. In the script for this event code:

// displayvalue will contain the display value the user has selected from ddlb or dddw column
string displayvalue
displayvalue = dw_1.getitemstring(dw_1.getrow(),"display")

In the Itemchanged event for the main DataWindow (dw_1) code:

// check to see if the ddlb or dddw column is the correct one they are changing.
// use the column number (#) of the ddlb or dddw column.
if getcolumn() = 3 then
  parent.event post ue_lookup()
end if

In the open event for the window add:

dw_1.modify("display.visible=0').
(This sets the computed columns visible attribute to "invisible".)

Can I use True/False instead of 1/0 when setting DataWindow attributes via Modify()?

PowerSoft says that, where appropriate, you can set boolean-type attributes using either True/False, 1/0, or ‘Y’/'N’.  For example, the following are all equivalent:

dw_1.Object.column_name.Visible = 0

dw_1.Object.column_name.Visible = False

dw_1.Object.column_name.Visible = ‘N’

Behind the scenes, PowerBuilder stores all attributes as string, regardless of whether is a boolean, long, or string.  To prove this to yourself, export a DataWindow and edit it – you’ll see that even the column colors, which are represented by a number, are enclosed in quotes.

Deleting rows quickly

Often you’ll have several rows to delete, and the most common way is to delete them in a loop like:

FOR ll_RowOn = 1 TO dw_1.RowCount()

 dw_1.DeleteRow(ll_RowOn)

NEXT

A quicker way (both typing and speed) is to just move the rows from the Primary! buffer to the Delete! buffer.  For example, to delete all the rows in the Primary! buffer:

dw_1.RowsMove(dw_1, 1, dw_1.RowCount, Primary!, dw_1, 1, Delete!)

Don’t forget that filtered rows are in a different buffer.

Using DataStores instead of hidden DataWindows

Pre-5.0 developers had to use hidden DataWindows to get and process data behind the scenes.  Version 5.0 offered the DataStore, which has alleviated the need for hidden DataWindows.  Here are several reasons to use DataStores instead of hidden DataWindows:

1) Hidden DataWindows, although invisible, still have a visual component that takes up memory.
2) DataStores can be destroyed after they have served their purpose, releasing the memory they use.
3) DataStores are the “bridge” to 3-tier architecture, so you’ll make your life easier when you move your application from traditional Client/Server to 3-tier or Web based.
4) Since DataStores are usually defined as instance variables, you can make them private, unlike window controls.
Do not forget to Destroy them before closing the window / user object they are used in, or the you’ll get memory leaks.  Here’s the code need to use a DataStore:

[in instance variables]
DataStore ids_Source

[in Open / Constructor event]

ids_Source = Create DataStore // Create an instance of the datastore
ids_Source.DataObject = “d_DataWindow_Name”
ids_Source.SetTransObject(SQLCA)

[in Close / Destructor event]
IF (IsValid(ids_Source)) Destroy ids_Source // Release the memory