WNGZWZSC0110iWâ ÕáõùøüöþOOò8ý4#0You must have a sheet open to use this function.üôð?ôþ9ð?þð?>7òcþ9ò&ý #You must select a CELL RANGE/ý,#(The first selection must be a CELL RANGEûð?þþ“ôð?þþ’ôð?þÑ.+ð?ôð?þÐ.+ð?ôôôÔÒQô9òNÔÒ.êò0ÔÒÔÒ.ð?½ÔÒ­¾­ôôþ.@—ôùøJ9òŠý9òM=òø=òÖÔÒR8ò*ð?ôÙÿÔÒô  ­ôô ð?2ôô +ð?ð?+ð?ð?÷ .ô  õÎ  ð?¾ô  ¬H@; ¬€L@=6ò2  5ô Jô ;ò*ð?ôh ¬€F@9ò  ­9ò   5ô 9 ¬G@9ò(<ò  5ô ôÿ J9òbPercent9ò"ý ±-Y@ÿ Ôÿý ±ÿ Ôÿý Ôÿý ÿ*ð?ôIô $þô*ð?ôþýØÿý"format 5ÿJ9ò ý J9ò ý=ò=òäÔÒQ8ò*ð?ôÙÿô ý Ôÿþn@—@@9ò$ÔÒ¹5ô Iþn@—H@9ò!ÔÒ,Y@¹%5ô ÔÒ¹ô ý ÿ Ôÿý"align 5ÿ*ð?ôÿô*ð?ôôþýØÿýýûöýÿÿÿÿ€9ò ýConvert Value to TextýConvert Text to Valueý)OK@Cancel@Ð@ °«@ý ý ýýº!´TextVal.scz:ok_button = TRUE() TextVal.scz:format_string = CTSTRING(3,0) TextVal.scz:use_commas = CTVALUE(4,1) TextVal.scz:use_parens = CTVALUE(4,2) TextVal.scz:prec = CTVALUE(5,0)9ò£ý5GeneralLeftCenterRightp ýe Align Text@þÈ@—÷9ò øý6÷°@9ò øý‚÷À@9ò øýƒøý„ÌýGFixedCurrencyPercent ScientificGeneralp ýeNumber Format@þn@—÷0@9ò øýM÷@@9ò øý‚6÷H@9ò øýƒ÷P@9ò øý„øý…ýý7C@ommas( )'s £@€@ðµ@ ýg Format with@ý9òýzý £@Pðµ@ ýg Precision@ý <ñÿý?€ý=ý >ÿIôýûü TextVal.sczFormatOptsDialogrowcol last_row last_col str_length use_parensformat_string use_commas ok_buttonnew_str orig_str valid_str spot char precperiodrcnewtypeTOTEXTTOVALUEConvrtFormatOptsDialogV{ Convrt() and FormatOptsDialog() This script was developed by Informix to demonstrate how to design a dialog box. This script creates a dialog box with a number wheel, check boxes, and radio buttons. There are only two functions in this file; Convrt and FormatOptsDialog. The function Convrt does the actual text-to-value or value-to-text conversion after the user has selected the formatting options they want from the dialog box created by FormatOptsDialog. Conventions: HyperScript keywords and functions are in all capital letters, user functions are in proper case, and variables are in lower case. Some variables are used as constants (i.e. they are defined and initialized, but are never changed), these always appear in all capital letters. TO USE THIS SCRIPT: To add this function to a menu, add the following to a Startup script: SELECT MENU "~Sheet"; ADD MENUITEM "Text/Value Conversion..." COMMAND "GET SCRIPT ""/usr/lpp/Wingz/Examples/TextVal.scz""; CALL TextVal.scz:Convrt(); REMOVE SCRIPT ""TextVal.scz"";"; You can then access the function just by going to the "Sheet" menu and selecting the command from the menu. NOTE: Before calling this function or choosing it from the menu, you must select a range of cells to convert. LIMITATIONS: These functions will only convert the FIRST selected cell range. } { Variable List } DEFINE row,col { hold the upper left corner of the } { selected area } DEFINE last_row,last_col { hold the lower right corner of the } { selected area } DEFINE str_length { holds the length of the original } { string } DEFINE use_parens { a flag used to indicate whether or } { not the user wants negative numbers } { to be enclosed in parenthesis } DEFINE format_string { holds the format type selected } DEFINE use_commas { a flag used to indicate whether or } { not the user wants numbers to have } { commas } DEFINE ok_button { holds the status of the OK button } DEFINE new_str { the converted string } DEFINE orig_str { the original string } DEFINE valid_str { flag used to indicate if the current } { cell contains a string that is valid } { for conversion } DEFINE spot { a var used in the FOR loop that } { steps through the original string, } { marks the current spot in the string } DEFINE char { holds the current character being } { processed in the original string } DEFINE prec { used to hold the precision the user } { selected from the dialog } DEFINE period { flag to indicate if the string being } { converted contained a period, and if } { it does, this var holds the number of } { places following the period } DEFINE r,c { holds the original row and column } { of the selected area } DEFINE newtype { holds what type of conversion is } { going to be done: } { 0 means Text to Value } { 1 means Value to Text } { Variables used as Constants } DEFINE TOTEXT,TOVALUE { constants used to identify what kind } { of conversion is being done } CALL Convrt(); { Convrt() This function does the actual conversion of the selected area using the options selected in the dialog box displayed by the FormatOptsDialog function. } FUNCTION Convrt() { Check to see if there is a sheet open. } IF (ISERR(NAME())) MESSAGE "You must have a sheet open to use this function." EXIT SCRIPT END IF TOVALUE = 0 { Setup constants that are used to } TOTEXT = 1 { keep track of what type of conversion } { is going on. } { Determine if a range has been } { selected, and if the first range } { selected is a cell range. } { If not print an appropriate message } { and exit the script. } IF ((NRSELECTIONS() = 0) OR (SELECTIONTYPE(1) <> 1)) { Something failed. } { Determine which condition failed. } IF (NRSELECTIONS() = 0) MESSAGE ("You must select a CELL RANGE") ELSE MESSAGE ("The first selection must be a CELL RANGE") END IF EXIT FUNCTION END IF { Save the upper-left and lower-right } { corners of the selected area. } row = ROWOF(SELECTION(1)) col = COLOF(SELECTION(1)) last_row = row + ROWS(SELECTION(1))-1 last_col = col + COLS(SELECTION(1))-1 r = row { Save coordinates of the current } c = col { cursor position. } { Determine which way to convert } newtype = ISNUMBER(INDIRECT(MAKECELL(col,row))) { If the conversion is TOVALUE, then } { set the variable prec = to the } { precision of the string. If there is } { no precision, then set prec = to 0. } { If TOTEXT, set prec = to currently } { defined precision. } IF newtype = 0 IF CONTAINS(INDIRECT(MAKECELL(col,row)),".") prec = LENGTH(MID(INDIRECT(MAKECELL(col,row)), MATCH(INDIRECT(MAKECELL(col,row)),".",1),LENGTH(INDIRECT(MAKECELL(col,row))))) ELSE prec = 0 END IF ELSE prec = BITAND(FORMAT(),15) END IF CALL TextVal.scz:FormatOptsDialog() { Go put up a dialog box and let the } { user select the formatting options. } IF (ok_button = TRUE()) { Unless they selected cancel, } { convert the selected block. } REPAINT OFF { We don't need to watch the conversion.} IF (newtype = TOVALUE) { Go from text to value. } WHILE (col <= last_col) { Process all the marked columns. } WHILE (row <= last_row) { Process all of the rows in the } { the current column before changing } { to the next column. } { If the current cell is NOT a string, } { skip over it and go to the bottom } { of the WHILE loop. } IF NOT ISSTRING(INDIRECT(MAKECELL(col,row))) row = row + 1 CONTINUE WHILE END IF { At this point, you start processing } { the string... } { Get the contents of the current cell } { and then save it's length. } orig_str = INDIRECT(MAKECELL(col,row)) str_length = LENGTH(orig_str) new_str = "" { Initialize the variable that will } { hold the converted string. } period = -1 { Set the period flag to -1, } { -1 will mean NO PERIOD has been seen. } { Once a period IS detected, the var } { will be incremented once for each } { character in the string. This will } { be used to set the precision. } { Convert the string 1 char at a time. } FOR spot = 0 TO str_length-1 char = MID(orig_str,spot,1) { Get the next character. } { Is the character a number? } { 48 is the ASCII value for 0 } { 57 is the ASCII value for 9 } IF ((CODE(char) >= 48) AND (CODE(char) <= 57)) { If it is a number, append it to the } { converted string. } new_str = new_str & char { Set a flag that indicates there is } { a valid string being created. } valid_str = TRUE() IF period >= 0 period = period + 1 END IF { If a negative sign (ASCII 45) is } { encountered, only add it to the } { converted string if no other } { numbers have been added. } ELSEIF CODE(char) = 45 IF LENGTH(new_str) = 0 new_str = new_str & char END IF { If a period (ASCII 46) is } { encountered AND it is the first one } { in the string, add it to the } { converted string. } ELSEIF CODE(char) = 46 IF period < 0 new_str = new_str & char period = 0 END IF END IF { END IF it was a number } END FOR { END FOR processing the entire string } { If there is a new string, format it } IF (valid_str = TRUE()) { If the user selected to display the } { converted values as percents, convert } { them and put them in the current cell.} IF (format_string = "Percent") PUT VALUE(new_str)/100 INTO MAKECELL(col,row) ELSE PUT VALUE(new_str) INTO MAKECELL(col,row) END IF SELECT RANGE MAKECELL(col,row) { Set the precision. } PRECISION PREC END IF { END IF there was a valid string } row = row + 1 { Go to the next row } valid_str = FALSE() { Reset the "valid str to format flag". } END WHILE { END WHILE process all rows in the } { current column } row = r { At this point, you are done convert- } col = col + 1 { ing the current row, so reset the } { row variable to the first row in the } { range and move to the next column. } END WHILE { END WHILE process all columns in the } { marked range. } SELECT RANGE MAKERANGE(c,r,last_col,last_row) RUN "format "&format_string IF (use_commas = TRUE()) use commas END IF IF (use_parens = TRUE()) use parenthesis END IF ELSE { Convert from value to text. } WHILE (col <= last_col) { Process all the marked columns. } WHILE (row <= last_row) { Process all of the rows in the } { the current column before changing } { to the next column. } { If the current cell is NOT a number, } { skip over it and goto the bottom } { of the WHILE loop. } IF NOT ISNUMBER(INDIRECT(MAKECELL(col,row))) row = row + 1 CONTINUE WHILE END IF new_str = "" { Initialize the var that will hold } { the converted string. } SELECT RANGE MAKECELL(col,row) { Determine the current format. } { If it is formatted as currency, put a } { dollar sign on the string. } IF (BITAND(FORMAT(),240) = 32) new_str = "$" & STRING(INDIRECT(MAKECELL(col,row)),prec) { If it formatted as percent, put a } { percent sign on the string and } { multiply the number by 100. } ELSEIF (BITAND(FORMAT(),240) = 48) new_str = STRING(INDIRECT(MAKECELL(col,row))*100,prec) & "%" { Otherwise, just make the string a } { regular number. } ELSE new_str = STRING(INDIRECT(MAKECELL(col,row)),prec) END IF { Put the new string in the current } { cell and format it. } PUT new_str INTO MAKECELL(col,row) RUN "align "&format_string row = row + 1 { Move the current cell to the next row.} END WHILE { END WHILE there are more rows to } { convert } row = r { At this point, you are done convert- } col = col + 1 { ing the current row, so reset the } { row variable to the first row in the } { range and move to the next column. } END WHILE { END WHILE process all columns in } { the marked range. } END IF { END IF which way to convert } SELECT RANGE MAKERANGE(c,r,last_col,last_row) REPAINT ON REPAINT WINDOW END IF { END IF there was anything to convert } { SELECT RANGE MAKERANGE(col,row,last_col,last_row)} END FUNCTION { END FUNCTION Convrt() } { FormatOptsDialog() This function puts a dialog box on the screen that allows the user to select several formatting options that will determine how the converted text or values will appear. } FUNCTION FormatOptsDialog() { Define a dialog box of the specified } { height and width that is centered on } { the screen. } { The (-1,-1) means center this box on } { the screen. The second set of coord- } { inates specify the size of the box. } NEW MODAL DIALOG BOX AT (-1,-1) (4inch,2.5inch) { Add a main title so the user knows } { which way the conversion is going to } { proceed, this way they can CANCEL if } { they thought the conversion was } { supposed to do something else. } IF (newtype = TOTEXT) NAME DIALOG BOX "Convert Value to Text" ELSE NAME DIALOG BOX "Convert Text to Value" END IF { Add the two buttons, the OK and } { CANCEL buttons in the specified } { recatangle, these will be controls } { one and two. } { CONTROLS 1 and 2 } ADD PUSH BUTTON "OK@","Cancel@" AT (0.5inch,2inch) (3.5inch,2.4inch) DIALOG CANCEL PUSH BUTTON { Specify an action for the CANCEL } { button, which is the current control } { because it was the last one added. } SELECT CONTROL 1 { Select the OK button, it was the } { first control added so it has the } { control number of 1. } DIALOG DEFAULT PUSH BUTTON { Make the OK button the default } { control, which means it will have a } { wider border. } { Specify what should happen when the } { OK button is pressed: } { -set the OK flag. } { -save the current values of all of } { the controls in the box. } SCRIPT "TextVal.scz:ok_button = TRUE() "& "TextVal.scz:format_string = CTSTRING(3,0) "& "TextVal.scz:use_commas = CTVALUE(4,1) "& "TextVal.scz:use_parens = CTVALUE(4,2) "& "TextVal.scz:prec = CTVALUE(5,0)" { Depending on what type of conversion } { is being done, put the appropriate } { buttons in the dialog. } { CONTROL 3 } { If you are converting to text, add } IF (newtype = TOTEXT) { buttons for aligning text. } ADD RADIO BUTTON "General", "Left", "Center", "Right" AT (0.1inch,0.1inch) (1.5inch,1.8inch) SHOW CONTROL TITLE "Align Text@" CASE BITAND(FORMAT(),12288) WHEN 0 RADIO BUTTON 1 WHEN 4096 RADIO BUTTON 2 WHEN 8192 RADIO BUTTON 3 OTHERWISE RADIO BUTTON 4 END CASE ELSE { If you are converting to value, add } ADD RADIO BUTTON { buttons for formatting numbers. } "Fixed", "Currency", "Percent", "Scientific", "General" AT (0.1inch,0.1inch) (1.5inch,1.8inch) SHOW CONTROL TITLE "Number Format@" CASE BITAND(FORMAT(),240) WHEN 16 RADIO BUTTON 1 WHEN 32 RADIO BUTTON 2 WHEN 48 RADIO BUTTON 3 WHEN 64 RADIO BUTTON 4 OTHERWISE RADIO BUTTON 5 END CASE END IF OUTLINE 2 BORDER { Add two check boxes that will give } { the user the option of displaying } { commas and parenthesis in the format. } { CONTROL 4 } ADD CHECK BOX "C@ommas", "( )'s" AT (1.7inch,0.35inch) (3.9inch,1inch) SHOW CONTROL Name "Format with@" OUTLINE 2 BORDER { Turn the border on the check box } { labels off. } { If you are converting to text, the } { check boxes about commas and parens } { don't apply, so grey them out. } IF (newtype = TOTEXT) GREY CONTROL END IF { Add a number wheel to let the user } { specify a precision for the format. } { Limit the range of the wheel to valid } { Wingz values, (-15 to 15). } { Set the format of the wheel and the } { initial value. } { CONTROL 5 } ADD NUMBER WHEEL AT (1.7inch,1.3inch) (3.9inch,1.8inch) SHOW CONTROL NAME "Precision@" NUMBER WHEEL RANGE -15 TO 15 NUMBER WHEEL PRECISION 0 NUMBER WHEEL STEP 1 NUMBER WHEEL POSITION prec ok_button = FALSE() USE DIALOG BOX { Display the box and then leave. } END FUNCTION