Tuesday, April 8, 2014

Extending ssToExcel & ssExcelPivot classes

For those who are familiar with ssToExcel, ssToExcel2, and ssExcelPivot classes, then they already know that it can easily export records from a table or cursor into Excel with added formats or create a pivot report by setting just a few properties in no time at all.  For those who are wondering what these classes are, then here are my original posts on this:

ssToExcel2:
http://sandstorm36.blogspot.com.au/2012/02/sstoexcel2.html

ssExcelPivot:
http://sandstorm36.blogspot.com.au/2012/12/ssexcelpivot-class-enhancements.html
http://sandstorm36.blogspot.com.au/2012/02/ssexcelpivot-class.html


Those classes are designed to be used generic as users may export different data to excel, in their own particular field ordering based on their needs.  But sometimes though, the generated output of these two classes may still lack something.  Like for instance, while I gave the ability to show total of a numeric columns like this:

      This._totalfields = 'Regular,OT,Holiday,Total'

Which will create a total on those 3 fields (vertically), I have not added a horizontal summation capability.  Why?  Because a user's exported data may vary based on the record source, the last may not be a column representing the numeric total of the fields on its left.

Is there a way to perform extra automation on the sheets generated by these classes?

Excel, Knowing the last row per sheet

Here is a very simple automation trick on how to find out the last row on an Excel Sheet.  This also shows how to find out the number of sheets, how to transfer between those, plus knowing the last row of each sheet.

Enjoy!


#Define xlLastCell 11

Local lcFile, loExcel As Excel.Application, lnLastRow, lnloop
lcFile = Getfile("xls,xlsx")
If !Empty(m.lcFile)   && check if not cancelled
      loExcel = Createobject("excel.application")
      With loExcel
            .Workbooks.Open(m.lcFile)
            For lnloop = 1 To .sheets.Count
                  .Worksheets(m.lnloop).Select
                  .ActiveCell.SpecialCells(xlLastCell).Select
                  lnLastRow = .ActiveCell.Row
                  Messagebox('Sheet'+Transform(m.lnloop)+' last Row'+;
                                     Transform(m.lnLastRow))
            Next
            * Visually Check
            .Visible = .T.
      Endwith
Else
      Messagebox("Aborted by user!",0+64,"Oppppssss!")
Endif

Saturday, April 5, 2014

Localized Data II - Animated Weather

They say a picture paints a thousand words so I will try to show here my weather forecast section and what the animation effect brings to that.

IMHO, this gives a better feel for the users of my app than the normal weather animations we see on the web or on our mobile gadgets.  Plus I always like to have something unique for my apps.

Now, for you be able to achieve the same effect, what you need to do is download animated images from the web, create a table for it, store it to a memo or blob field, and create another field to trap the weather condition which is unique with the web site you will be getting your forecast from.

Like I said with the first blog about this, what I do then on my end is refresh data every 60 seconds and display result on the section of my form for that.  Then I perform an SQL SELECT to get all animations stored on my table which has the same weather condition on the current hour forecast, and finally picks one randomly to be shown above the forecast.

Here is the section of said weather forecast for you to see.  Although the image of the grid representing the forecast is not clear here because I have to resize it larger to fit with the animated image I have shown here whereas on my app, it is the other way around.  The animated image is stretched to fit with the same size of my actual grid below.

Foolish me!  I like this animated image of raining so I chose it.  So as not to confuse the readers, I've overwritten the Cloudy weather with Heavy Rain but forgot to change the caption of Current Weather.  LOL!


And here is the portion where it randomly picks for an animated image:

* Select Animation
Use weather In 0 Shared
Use weathergrp In 0 Shared
With This.imgweather
      .Picture = ''
      * Get an animation based on Weather
      Select t1.wimage, t2.Descript From weather t1;
            LEFT Outer Join weathergrp t2;
            ON t2.wgpk = t1.wgfk;
            WHERE Alltrim(t2.Descript) == m.lcWeather ;
            Into Cursor JunkWeather NOFILTER
      If _Tally > 0
            Goto (1 + _Tally * Rand( ))
            lcImage = Addbs(Getenv("TEMP"))+'weather.gif'
            Strtofile(JunkWeather.wimage,m.lcImage)
            .Picture = m.lcImage
            .Visible = .T.
      Else
            .Visible = .F.
      Endif
      Use In Select('Weather')
      Use In Select('WeatherGrp')
Endwith
      * End of Select Animation

Its advantage is speed, being all data are localized.  You won't see it lagging as both the weather forecast info and weather animation is updated/changed in a split second .

You can achieve the same on your end, just follow the ideas I shared here and in the first part of localized data blog.  Cheers!


Friday, April 4, 2014

Localized Data

Cousin Glen shared a way to put the weather on our form in his blog as well as in foxite last time and so when I decided that I finally needed one because we are working simultaneously on several road construction projects these days and the weather forecast will be helpful, I did not need to look far away and simply checked what he has shared which is via XMLHttpRequest.

What is XMLHttpRequest?

XMLHttpRequest (XHR) is an API available to web browser scripting languages such as JavaScript. It is used to send HTTP or HTTPS requests to a web server and load the server response data back into the script.  http://en.wikipedia.org/wiki/XMLHttpRequest

Using XMLHttpRequest has some (expected) peculiarities like it appears based on my observation that the previous request is compounding seeing my laptop which is doing the data fetching getting slower over time. Thus, I informed him of such and we decided to do these additional things:

Wednesday, April 2, 2014

Combobox using RowSourceType = 3, How to update content?

Well I use this to answer inside foxite forum earlier but then I just thought maybe some does not know yet how to use a cursor from an SQL SELECT as RowSource of a combobox, or if some do, they are maybe wondering how to update such when there is a change from the source table/cursor, in result keeping them away over its usage on a combobox; so I transferred it over here as part of my simple tutorials.

You see, I prefer to work on cursors on my end as that makes my app works faster, less prone to corruption (possibility of corruption may happen only during transferring of data from cursor to actual table), and easier to maintain.

So I use cursors on grids, listbox, combobox, etc.  However, some may have been wondering that if they use a cursor for a combobox RowSource, how to update said combobox then when there is a change in data from the actual table it is coming from?  Or in other words, how to show the changes back to the combobox when it is already set to run on a cursor that was created earlier than when the change on the actual source table/cursor happened?

Monday, March 31, 2014

Highlighting Pageframe ActivePage

Aesthetics is something that always fall to individual tastes. Some wanted their app to simply follow the OS theme, others want it to look plain, yet others like me wants to make it a bit colorful or somewhat unique thinking it will spice up the app.

However, sometimes we tend to overdo the look.  For example, in the past I tend to create a pageframe with several pages each having its own colors (theme should be set to .F. otherwise the color will be ignored). In the first glance, things look cool to my taste but then in the end either I simply turn the theme back on because I get tired of the multitude of colors or I just retain one color for all pages with theme still set to .F.

Saturday, February 1, 2014

LOCATE vs SEEK, which is faster?

From time to time someone would inquire which is faster?  LOCATE or SEEK? And the answer mostly is it depends, if the records are small, then the speed difference is negligible enough.  True.  But that does not still answer which really is faster.  When we say faster, it has to be at least a nanosecond quicker than the other one.  We are not arguing "how" fast but "which" is faster.



To understand better which is really faster, we have to first understand how the two differs.  How does LOCATE performs searching?  How does SEEK performs searching?  Let us start with SEEK() first.

HOW DOES SEEK() PERFORM SEARCHING?