ssExcelPivot class

Summary of what this class can do:

• Will create a pivot report inside excel or as html for you without needing much effort on your part.  You don't need to know automation nor pivoting to create wonderful reports
• Gives you possible 84 report appearances on pivot report alone.
• Gives you possible 84 report appearances on non-pivot report (looks differ than the pivot report output)
• Allows user interaction or not
• All you need is just a recordsource (cursor or table) with all the fields you need, declare values of some properties of the class (or not) and you are on the go.  No fuzz, no cuzz, straight mouth approach of report generation.

Properties that can be set to give the report its initial (default) look:


This one is a must:

• _recordsource- specifies the record source which maybe a cursor or a table. 

 The rest are optional:

Why Append/Import From an .xlsx fails?

We do know that since Office 2003, VFP pose no problem importing from an .xls or any of its other formats; then came Office 2007 introducing with it an .xlsx format and suddenly VFP can no longer "directly" append nor import from it.  A lot has been baffled by this and maybe have thought 'hey, VFP is really dying!'.    So is it really VFP's incompetence or not?  Let us find out.

One solution I proposed to tackle that problem of importing/appending from an .xlsx file is via automation as I have shown in these blogs:

a. Appending/Importing from Excel 2007's xlsx via automation:
 http://weblogs.foxite.com/sandstorm36/archive/2009/11/20/9387.aspx

b.  Importing from an xlsx file via automation - Part II:
 http://sandstorm36.blogspot.com/2011/11/importing-from-xlsx-file-via-automation.html

The trick I used there is to save the excel file on a lower version format and I advice .xl5 format for that.

However, though I have shown the automation ways on those blogs, we haven't tackled exactly "why" appending/importing from an xlsx format is failing.  And this blog is meant to shed some light onto that.

With Office 2003 and lower versions, when we open an excel file, we are dealing with only a "single" file.  With the advent of Office 2007 however, along comes a new format called xlsx.  Why the extra x at the end?  Because that extra x at the end actually signifies the new format which is 'xml'.

With .xlsx, we are no longer dealing with a single file but "several" xml files.  An .xlsx actually is a compressed folder with several sub-folders and several xml files.  If you want to study it, here is what you should do:

a.  Create a copy of one of your xlsx files. 
b.  Rename the copy to any name you want but rename the extension as well from .xlsx to .zip
c.  Open or Extract
d.  Analyze the contents



Here is how it will look like:

VFP has no fault at all why it can no longer append nor import directly from an .xlsx file.  My automation approach works because what it does is like fusing all the pieces of a broken glass to make it as a whole again, which is what VFP is designed to recognize in the first place. 

So is VFP the one at fault on failure to append/import from an .xlsx file?  Now you know better!


Cheers!

ssPolyClock - a 100% VFP clock widget

Update: February 13, 2012

5 new skins for this class. Skins can be cycled via clicking on the arrows on the lower left of the clock.  Advantages of this class over activex ones are:

• Works properly with Anchor.  Unlike activex where anchoring pose a little problem (if you don't know how to work around it), this one does not pose any problem with Anchor.
• Can be skinned on the ply (6 skins to choose from)
• Can be moved around your form (optional)
• Can hide digital clock portion (optional)

Here are all skins for now:

That is all for now!

 ======

Originally, I gave you a tutorial under weblogs about adding a Graphical Analog Clock ( http://weblogs.foxite.com/sandstorm36/2008/05/27/graphical-analog-clock/ ) in your form.  That one utilizes a ShockWave Flash animation and is done using SwissMax.

The idea of that clock, as I have said there, plus the way to use it in our form comes from my cousin Glen Villar, my protegee and a proud member of Foxite forum.  Since ssClasses are becoming famous and I was not making any more clock widget, he volunteered to include his PolyClock class among my ssClasses objects and requested that I try to make it look better.  Well here it is now, Glen's PolyClock into ssClasses.

It is called ssPolyClock because the objects here are pure shapes with manipulated polypoints with the exception of the background image which is embedded now into the class.  And I salute my cousin Glen for thinking about this, the first ever (as far as I know) graphical analog clock that is 100% VFP in objects and codes.

 

Shown above is another widget called ssCalendar under old _Sandstorm36 class.  Do you like it?

P.S.  It appears that Glen is not the original who thought of something like this, LOL!  Let us give a proper credit to Luis Maria Guayan (co-creator of FoxBarCode) who published an Analog Clock running on Polypoints in 2005 (Wow!):  http://www.portalfox.com/article.php?sid=1887

ssToExcel2

Enhancements (February 16, 2012):

Created two new sections called fonts and colors.  This is where you can control further the fonts (in addition to _ExtraFonts which will be the default font for columns if used) and colors of data cells:

 

Enhancements (February 13, 2012):

Added new properties to make it more flexible.  Here is a complete listing of those plus showing below is how to instruct ssToExcel2 on its click event:

Sample Click Event:

CREATE CURSOR junk (fld1 c(30), fld2 d, fld3 I, fld4 i)
INSERT INTO junk VALUES ('Jun Tangunan',DATE(),40,26)
INSERT INTO junk VALUES ('Sandstorm36',DATE()-5,40,26)
INSERT INTO junk VALUES ('Celyn Tangunan',DATE()+8,27,17)

* Change the field name to proper ones using AS clause.  To create more than one word in excel, use underscore

ssClasses Showroom

Just thought it would be better to show some forms I have with me that were under ssClasses.  I will post here one form at a time, depending on the availability of my time.

Forms will be shown small to fit the blog column.  To see those in real sizes, just click on an image.   If it is not yet on its full size, right-click and choose View then once it is onto that view, you can zoom it in to full size.

Tale of the Ugly Duckling:

Here are some of my forms:


a.  Using ssTitleBar - Theme 3, ssContainers 1 & 2, ssButton4, ssSwitch Theme 5, ssToExcel2, ssGridLock and ssEditBox:

b.  Using ssClose, ssContainer3, ssButton3, ssGridSorter and Ony Too's DatePicker:

c.  Using ssClose ssOptSwitch, ssButton3, ssContainer2, ssGridLock and ssToExcel classes.

Adjusted the above yesterday to include ssToExcel2 and Theme5 of ssOptSwitch.  Here it is again for comparison:

Here is my Easy ID Creator Module (6 possible colors) using ssTitleBar2:

My Request Encoding Module using ssClose, ssButton 3 & 4, ssContainer2, ssGridLock, ssAnchorSizer, ssEditBox, and ssOptSwitch

Purchase Order History

--- Will add other images soon --- 

Note:  You can view these images on their real sizes via clicking on it, then right-click - View Image, then you can zoom in further if you like to reach 100% size.

ssContainer4

Well, though I am already happy with ssContainer 1 to 3, there are times that I still thought of new designs or looks for my app as well as for my subscribers.  Introducing ssContainer4, a container that absolutely have no graphic images.  This is all shapes where Polypoints are manipulated.

This new class comes with 6 colors at the moment.  Here are the parameters:

1. cCaption = is the caption of the title bar of container
2. nColor = is numeric values of 1 to 6 at the moment
3. lEmboss = Logical value of .T. or .F. to give it an Emboss effect
4. l3DEffect = Logical value of .T. or .F. to give it an 3D effect
5. lBackColor = Logical value of .T. or .F. for inside color. .F.(default) gives white inside.

I will show how it looks like one by one:

Check ssClasses complete listings (actually that is not the complete lists because I cannot find time to create a writeup of others included in _sandstorm36 class which is likewise given to subscribers).  Enjoy!