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

How do I show only unique rows in my datawindow without using SELECT DISTINCT in the SQL?

First sort the data on the column for which you want unique values, then apply the following sort and filter strings to the datawindow.
Sort String: “department_name A”
Filter String: “department_name <> department_name[-1] or GetRow () = 1″

How do I change the color of a column to let the user know it has been modified?

In the Color property of a column, enter the expression IF (column_name <>column_name.Original, RGB(255, 0, 0), RGB(0, 0, 0)).   This will change the color of the column to, in this case, red if the user changes the value and black if the column has not been modified.  The trick here is that column_name <> column_name.Original compares the current column value with the original column value.

How can I remove rows from a DataWindow without deleting or filtering them?

A relatively new and unknown command, RowsDiscard(), will do the trick.  This command removes the rows from the DataWindow so that when you issue an Update() they will be neither updated (if modified) or deleted from the database.

How can I make sure that a particular column is unique for every row?

There are many times you want to make sure that a column’s value is unique. For example, you might have a DataWindow where the user can select an employee ID, however they may only select the employee once. To validate this business rule you could could evaluate each row individually in a FOR/NEXT loop to make sure it’s unique, or you could take the following short-cut.

First sort by the column in question (e.g., employee_id) and create a computed column (e.g., f_unique) with the evaluation employee_id = employee_id[-1]. Code something like the following in the DataWindow pfc_validation event or, if your non-PFC, prior to the Update():

This.SetSort(“employee_id A”)
This.Sort()
IF (This.Find(“f_unique = 1″, 1, This.RowCount()) > 0) THEN
  // error processing
  MessageBox(“Error”, “Some meaningful error message”)
  Return -1
END IF

How can I get two or more columns both be AutoHeight without leaving a ton of space between the two?

1) Make all fields AutoSize Height 2) Make detail band AutoSize Height (right-click, Properties, General Tab (first tab)) 3) Right-click on each column (and their labels!), select Properties. Go to Position tab. In the Up: field of the Slide groupbox, select All Above from the DDLB.

How do I number rows in a DataWindow when I have groups defined?

Suppose you want to display a row number for each row in a DW. Easy enough – just make a computed column with the value GetRow().

Now suppose you have a group in the DW, and want the row number to start over for every group break. To do this, just create a computed column with the value GetRow() – First(GetRow() for Group 1) + 1.

How can I tell in a DataWindow which rows are the first and last ones displayed?

Create a hidden computed column with the following expression:

IF (GetRow() = First(GetRow() FOR Page), 1, 0) // 1 = first row on page

IF (GetRow() <> 1 AND GetRow() = Last(GetRow() FOR Page), 1, 0) // 1 = last row on page

With this knowledge, you can also display the rows currently displayed:
[Footer Band]

‘Rows ‘ + String(First(GetRow() FOR Page)) + ‘ to ‘ + String(Last(GetRow() FOR Page)) + ‘ are displayed’

How can I make an Edit-style column in a DataWindow have word-wrap?

Set AutoHScroll off, AutoVScroll and VScrollBar on, and re-size the control to the desired size.

How can I get the Find() function to to search anything other than the Primary! buffer?

Create a function in your ancestor DataWindow / DataStore UserObject (pfe_dw/pfe_ds or u_dw/u_ds in PFC):

uf_Find(String as_FindExpression, Long al_StartRow, Long al_EndRow, dwBuffer ae_SearchBuffer)

Long ll_row, ll_NumRows, ll_RetValue
DataStore lds_Buffer
String ls_Filter

IF ae_SearchBuffer = Primary! THEN
// Normal search
ll_RetValue = This.Find(as_FindExpression, al_StartRow, al_EndRow)
ELSE
// Create datastore to temporarily hold the data
lds_Buffer = CREATE DataStore
lds_Buffer.DataObject = This.DataObject
IF ae_SearchBuffer = Filter! THEN
ll_NumRows = This.FilteredCount()
ELSE
ll_NumRows = This.DeletedCount()
END IF

// Copy data to temporary datastore
This.RowsCopy(1, ll_NumRows, ae_SearchBuffer, lds_Buffer, 1, Primary!)
// Search for it
ll_RetValue = lds_Buffer.Find(as_FindExpression, al_StartRow, al_EndRow)

DESTROY lds_Buffer
END IF

Return ll_RetValue

Rotating text and columns to any angle in a DataWindow

Have you ever wanted display some static text or a column up and down instead of left to right in a DataWindow? The Font.Escapement attribute will let you do this. As with most attributes, you can either set this dynamically using Modify() and dot object notation, or you can set it in the DataWindow using the Expressions tab of an object.

Note that you must multiple the degree by 10 – so if you want to rotate 90 degrees, you would use 900.

Displaying the current printer in a DataWindow

I was suprised by the simplicity when I came across this gem in the newsgroups. To display the printer that the user will be printing to, just add this script:

st_currentprinter.Text = dw_1.Describe(“Datawindow.Printer”)

or

st_currentprinter.Text = dw_1.Object.DataWindow.Printer

Printing a DataWindow to a file

You can easily print a DataWindow to a file using the current printer driver using the following code:

dw_x.Object.DataWindow.Print.Filename = “somefile.prn”
dw_x.Print()

Using the DataWindow.DocumentName attribute for printing

The DocumentName attribute is helpful in two ways. If your users print a banner page on a network printer with every printout, the DocumentName will usually print on the banner. Also the DocumentName is used on the standard “printing document” window that displays when you issue a dw_1.Print(). To set the attribute, right-click on a blank area of a DataWindow in the DW Painter and select Properties. Go to the Print Specifications and you will see the Document Name attribute at the top.

This can get tedious if you want to retrofit your already written DataWindows. If all your DataWindows have a standard text or computed field name for the title (e.g., st_title), try adding the following code to your DW or window ancestor object:

// Make sure st_title exists on the DW
IF (dw_1.Describe(“st_title.Band”) <> “!”) THEN
dw_1.Object.DataWindow.Print.DocumentName = dw_1.Object.st_title.Text
END IF

Get data from Excel via the clipboard

OLEObject  excel

Integer    li_RetValue, li_rtn
Boolean    lb_sheet_rtn
Long       ll_cnt

excel = create OLEObject

li_rtn = excel.ConnectToNewObject("excel.application")
IF li_rtn <> 0 THEN
    MessageBox('Excel erro','can not run Excel Program')
    DESTROY excel
    RETURN 0
END IF

excel.WorkBooks.Open( "c:\mysheet.xls" )
excel.Application.Visible = false
excel.windowstate = 2 // 1 : Normal, 2 : Minimize, 3 : Maximize

lb_sheet_rtn = excel.worksheets(1).Activate
excel.Worksheets(1).Range("A1:E5000").Copy  //  copy to clipboard
ll_cnt = dw_1.importclipboard()
IF ll_cnt <<= 1 THEN
    Messagebox("Inf", "Could not find .")
END IF

excel.Worksheets(1).Range("A10000:A10000").Copy  //reset clipboard
excel.Application.Quit
excel.DisConnectObject()
DESTROY excel

Highlight a Text in the Datawindow

To highlight / focus on a column in the datawindow.

  dw_pb.setcolumn( "Column1")

*Column1 is the name of the column in the datawindow

Passing Parameters in Powerbuilder

There are 2 ways of passing parameters from one window to another window
First:

//1st window                   >>>> 2nd Window 
message.of_setstringparm       >>>> message.of_getstringparm
message.of_setdoubleparm       >>>> message.of_getdoubleparm
message.of_setpowerobjectparm  >>>> message.of_getpowerobjectparm

Second:

//1st window                         >>>> 2nd Window
closewithreturn(window, 'string')    >>>> message.stringparm
closewithreturn(window, powerobject) >>>> message.powerobjectparm

Windows API – Get Windows User

Use windows api to get the username.
Declare in the Global External Functions

Function boolean GetUserNameA( ref string userID, ref ulong len ) library "ADVAPI32.DLL" ALIAS FOR "GetUserNameA;ansi"
Function boolean GetUserName(Ref String lpBuffer, Ref Ulong nSize) Library "ADVAPI32.DLL" ALIAS FOR "GetUserNameW"

*ALIAS FOR "GetUserNameA;ansi" - NEED TO CALL ALIAS IF NOT the string return contains garbage.

To use this function in powerscript

 string  login_name
 string  ls_temp
 ulong   lul_value
 boolean lb_rc

 lul_value = 255
 ls_temp = Space( 255 )
 lb_rc = GetUserNameA( ls_temp, lul_value )
 login_name = Trim( ls_temp )