The Desktop Alpha5 program is a much superior way to deal with data than Excel. That is what originally brought me to the Alpha products many years ago. Somewhere along the line I stopped using the action script genies and became an Alpha5 developer. I believe many of us on this Forum have a similar experience. However, despite Alpha5's advantages I still have co-workers who want the data in spreadsheets. There is no avoiding it I have to generate spreadsheets from some data sets.
Alpha Five can create Excel Spreadsheets in several different ways. The built-in methods allow all fields or selected fields in a table to be exported. When using these methods, I found myself spending a lot of time formatting the resulting Spreadsheet. Setting column widths changing the column names stuff like that.
Stan Mathews has given several samples in the forum on how to create Spreadsheets using OLE, Object Linking and Embedding. It's a method where one windows program can control another. It allows Alpha5 to place field values directly into a Excel Spreadsheet. If you know the required property names you can also set formatting attributes for individual cells all from Alpha5.
Using Stan's examples and the limited Alpha5 documentation on this subject I was able to directly build a formatted Excel Spreadsheet. It worked nicely.
Early on in the process I decided it would make sense to place the heading names and field names into a list rather than use a repetitive series of xBasic statements to enter the values into the spreadsheet. That way I could just process the contents of the list using the numerous functions in xbasic available for processing list. It was also easier to define and edit a list than a series of xBasic statements. I added additional functionality to the interpretation of the list, to the point where the entire Spreadsheet was defined by the contents of two list and a pointer to a table or set is used to populate the list.
When I wanted to create additional spreadsheets it made sense to move the code to build a spreadsheet into a function that you just pass the Spreadsheet definitions and table or set pointer to. This is the function zExcelBld.
The only thing I needed was a way to determine what properties needed to be assigned to get the desired formatting. To do this the script Tool-Excel-Properties was created. This Script presents a mode-less Dialog Box that will display the properties of the selected Cell, In the Selected WorkSheet of the specified Spreadsheet. This is done by opening the spreadsheet using OLE and examining the cell contents using OLE.
My approach is to build the spreadsheet first using one of the built in Alpha5 methods then edit till it looks "right". Then open using the Tool-Excel-Properties and use this script to help define the two list required to build the spreadsheet using zExcelBld.
Many of the displayed properties don't need to be set for every cell, they are actually default values. By selecting a reference Cell, one you have not applied any formatting to, the dialog will only display the properties that differ from the reference cell. Not all properties need to be assigned to every cell. For example if the first cell in the row sets a height the remaining cells do not need to specify the height. Just as if the column widths are specified in the header definition they need not be defined in the field definitions.
Using the arrow keys you can easily adjust the cell you are checking the properties of. The copy button will copy the properties to the clip board after which you can paste them into the list definition.
With Excel 2016 you may need to open Excel before the script attempts to open the selected spread sheet. The selected spreadsheet is not actually opened in Excel till the Lookup Button is pressed or an arrow key is used to adjust the cell we are examining the properties of.
The attachment on this Post contains The script and function already mentioned in addition to;
Unlike the built in Alpha5 methods for building a spreadsheet this function can build spreadsheets from the values in a set that contains one-to-one links. Also single records or composite records can also be spread across multiple lines by using the <NEWLINE> directive.
The zExcelBld function uses a progress bar included that uses a Alpha5 class therefore this code requires at least version 10 of Alpha5 to run. If you remove the references to zProgress this code may run in earlier versions of Alpha5
The one thing I haven't figured out how to do using this method is draw boarders. If you know how please let me know.
If anyone finds this code of use I would very much appreciate hearing from you.
Build_Excel.txt
Alpha Five can create Excel Spreadsheets in several different ways. The built-in methods allow all fields or selected fields in a table to be exported. When using these methods, I found myself spending a lot of time formatting the resulting Spreadsheet. Setting column widths changing the column names stuff like that.
Stan Mathews has given several samples in the forum on how to create Spreadsheets using OLE, Object Linking and Embedding. It's a method where one windows program can control another. It allows Alpha5 to place field values directly into a Excel Spreadsheet. If you know the required property names you can also set formatting attributes for individual cells all from Alpha5.
Using Stan's examples and the limited Alpha5 documentation on this subject I was able to directly build a formatted Excel Spreadsheet. It worked nicely.
Early on in the process I decided it would make sense to place the heading names and field names into a list rather than use a repetitive series of xBasic statements to enter the values into the spreadsheet. That way I could just process the contents of the list using the numerous functions in xbasic available for processing list. It was also easier to define and edit a list than a series of xBasic statements. I added additional functionality to the interpretation of the list, to the point where the entire Spreadsheet was defined by the contents of two list and a pointer to a table or set is used to populate the list.
When I wanted to create additional spreadsheets it made sense to move the code to build a spreadsheet into a function that you just pass the Spreadsheet definitions and table or set pointer to. This is the function zExcelBld.
The only thing I needed was a way to determine what properties needed to be assigned to get the desired formatting. To do this the script Tool-Excel-Properties was created. This Script presents a mode-less Dialog Box that will display the properties of the selected Cell, In the Selected WorkSheet of the specified Spreadsheet. This is done by opening the spreadsheet using OLE and examining the cell contents using OLE.
My approach is to build the spreadsheet first using one of the built in Alpha5 methods then edit till it looks "right". Then open using the Tool-Excel-Properties and use this script to help define the two list required to build the spreadsheet using zExcelBld.
Many of the displayed properties don't need to be set for every cell, they are actually default values. By selecting a reference Cell, one you have not applied any formatting to, the dialog will only display the properties that differ from the reference cell. Not all properties need to be assigned to every cell. For example if the first cell in the row sets a height the remaining cells do not need to specify the height. Just as if the column widths are specified in the header definition they need not be defined in the field definitions.
Using the arrow keys you can easily adjust the cell you are checking the properties of. The copy button will copy the properties to the clip board after which you can paste them into the list definition.
With Excel 2016 you may need to open Excel before the script attempts to open the selected spread sheet. The selected spreadsheet is not actually opened in Excel till the Lookup Button is pressed or an arrow key is used to adjust the cell we are examining the properties of.
The attachment on this Post contains The script and function already mentioned in addition to;
- The function zFilterList, used to support Tool-Excel-Properties.
- The Class zProgress used to by the zExcelBld function. Classes are only available in Alpha5 Ver 10 and up.
- The demo script Demo-ExcelBld. This script will build a formatted spreadsheet from the Customer table in the AlphaSports sample application. Just import these 2 scripts and 2 functions into Alpha Sports to use the demo script.
Unlike the built in Alpha5 methods for building a spreadsheet this function can build spreadsheets from the values in a set that contains one-to-one links. Also single records or composite records can also be spread across multiple lines by using the <NEWLINE> directive.
The zExcelBld function uses a progress bar included that uses a Alpha5 class therefore this code requires at least version 10 of Alpha5 to run. If you remove the references to zProgress this code may run in earlier versions of Alpha5
The one thing I haven't figured out how to do using this method is draw boarders. If you know how please let me know.
If anyone finds this code of use I would very much appreciate hearing from you.
Build_Excel.txt
Comment