Get a Jumpstart with our Sample Apps
Results 1 to 5 of 5

Thread: Function to build formatted Excel Spreadsheets

  1. #1
    Member
    Real Name
    Paul Verboom
    Join Date
    Apr 2006
    Location
    Halifax, Nova Scotia, Canada
    Posts
    128

    Default Function to build formatted Excel Spreadsheets

    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;


    • 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

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,698

    Default Re: Function to build formatted Excel Spreadsheets

    This won't be any help in what you are doing but I was never able to get any of the borders commands to translate. I had to resort to writing a macro into Excel with Alpha and then running that. (Thanks, Marcel)

    Code:
    	VBComp.CodeModule.InsertLines(1,"Public Sub LeftEdge()")
    	VBComp.CodeModule.InsertLines(2,"'This code is created with Alpha Five")
    	VBComp.CodeModule.InsertLines(3,"With Selection")
    	VBComp.CodeModule.InsertLines(4,".Borders(7).LineStyle = 1 '\xlContinuous")
    	VBComp.CodeModule.InsertLines(5,".Borders(7).Weight = 2 '\xlmedium")
    	VBComp.CodeModule.InsertLines(6,".Borders(7).ColorIndex = -4105 '\xlAutomatic")
    	VBComp.CodeModule.InsertLines(7,"End With")
    	VBComp.CodeModule.InsertLines(8,"End Sub")
    And later

    Code:
    xlApp.Sheets(1).Range("I1:I3").Select()
    	xlApp.Application.Run("LeftEdge")
    I should mention that before this will work one must make appropriate security settings in Excel to allow macros to run and

    Code:
    	'Create a module in the Excel Project section.
    	dim vbext_ct_StdModule as n
    	vbext_ct_StdModule = 1 'VBEXT constant
    	xlApp.VBE.ActiveVBProject.VBComponents.Add(vbext_ct_StdModule)' Create a module in the Excel project environment.
    	
    	dim VBComp as P
    	'Assumption is made that there is no Module1 in the empty Excel file.
    	'If created the module is always named "Module1"
    	VBComp = xlApp.VBE.ActiveVBProject.VBComponents.Item("Module1")
    Last edited by Stan Mathews; 11-14-2016 at 09:32 AM.
    There can be only one.

  3. #3
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    24,698

    Default Re: Function to build formatted Excel Spreadsheets

    Further investigation has revealed that

    xlApp.Selection.Borders.Linestyle = 1
    xlApp.Selection.Borders.Weight = 2
    xlApp.Selection.Borders.ColorIndex = -4105

    will be honored for the chosen selection where

    xlApp.Selection.Borders(#).Linestyle = 1

    was not for a given #.

    This allows individual cells to have borders drawn and groups of cells to have borders drawn. It does not allow a group of cells to have an outside border drawn with no internal cell borders. Attempts to remove the internal borders after initially drawing group cell borders are not honored.

    After the selection is made one can

    xlApp.Selection.MergeCells = .T.

    and have the border commands apply to the merged block of cells.
    There can be only one.

  4. #4
    Member
    Real Name
    Paul Verboom
    Join Date
    Apr 2006
    Location
    Halifax, Nova Scotia, Canada
    Posts
    128

    Default Re: Function to build formatted Excel Spreadsheets

    Below is the latest version of these scripts. Thanks to Stan's input drawing borders is supported. However, I couldn't get Cells to merge using these tools.

    Build_Excel-02.txt

  5. #5
    Member
    Real Name
    Richard Mount
    Join Date
    Mar 2006
    Posts
    11

    Default Re: Function to build formatted Excel Spreadsheets

    Thanks for your advice.

Similar Threads

  1. Displaying Charts in Excel Via Export To Excel Function
    By aal_fareed in forum Alpha Five Version 11 - Desktop Applications
    Replies: 3
    Last Post: 12-21-2014, 07:38 PM
  2. Report output as formatted excel spreadhseet in v11 Web application
    By lemandras in forum Application Server Version 11 - Web/Browser Applications
    Replies: 0
    Last Post: 07-16-2012, 09:56 AM
  3. Import custom formatted time field from Excel
    By borumrm in forum Alpha Five Version 7
    Replies: 7
    Last Post: 07-07-2006, 02:47 PM
  4. Excel Spreadsheets
    By Cynthia Dobbins in forum Alpha Four Versions 7 and 8
    Replies: 0
    Last Post: 01-20-2005, 05:21 AM
  5. EXCEL SPREADSHEETS
    By rmerson in forum Alpha Four Versions 7 and 8
    Replies: 2
    Last Post: 05-28-2003, 12:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •