Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Function to build formatted Excel Spreadsheets

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    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, 09:32 AM.
    There can be only one.

    Comment


    • #3
      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.

      Comment


      • #4
        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

        Comment


        • #5
          Re: Function to build formatted Excel Spreadsheets

          Thanks for your advice.

          Comment

          Working...
          X