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?



Yes, of course that is why I created this post.  As I was busy most of the time and since no one asks before, then I always forgot to show or mention how to extend that.  Now, someone actually asked me how it can be done.

Unexplained Features:

These properties are actually explained in the simplest explanation on those links but since those posts are already long, I just left some details to the imaginations of the users.  But since no one seems to have thought of utilizing some of those properties to their advantages, I will now elaborate some overlooked features of those classes for your benefits on extra automation:

ssToExcel 1 and 2 Properties:

_cFolder = specify target folder destination.  Default is user's local temp folder
_FileName = to specify a filename other than default sys(3)
_lWithOutput = to show screen output of result or not, default is .T.

When you set _lWithOutput to .F., then the class will perform your hardcoded instructions to generate an excel output with formats, headings, etc.; then save it and exit excel outright without showing you the result (Silent).  You will just be given a prompt of Messagebox("Conversion Complete!")

ssExcelPivot Properties counterparts:

._filename = to specify a filename other than default sys(3)
._nooutput = This is the opposite of the above.  This is set to .F. by default. Set it to .T. so now screen output will happen (loExcel.Visible = .F.)


Now that we have successfully exported records with extra formats via ssToExcel classes (first or 2nd version) or has created the pivot report via ssExcelPivot class without showing us the end result on our screen, then we can now move to extra automation.

Performing Extra Automations

The first thing we need is to create a form property that will handle the filename of the output we've generated above on either ssToExcel or ssExcelPivot classes.  The main trick is to know the filename of those excel worksheets so that after those classes have done their work and generated the output, then we can reopen said file and perform some more automation on those.

Let us create as an example a simple Daily Time Record.  The barest commands we can issue to generate one is something like this:

Local ldStart, ldEnd

ldStart = Gomonth(Date(),-1)

ldEnd = Date()

Select EmpCode, Empname, Regular, OT, Holiday, Total ;

      FROM MyTable ;

      WHERE MyDate Between m.ldStart And m.ldEnd ;

      INTO Cursor junkrep NOFILTER

If _Tally > 0

      This._cursorname = 'junkrep'

      This._headings = "DAILY TIME RECORD|For the Period "+;

            DTOC(m.ldStart)+" to "+Dtoc(m.ldEnd))

      This._nopop = .T.

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

      DoDefault()

Else

      Messagebox('Nothing to export yet!',0+64,'Oooopppsss!')

Endif

But the problem with this, while I have a hardcoded instruction for creating totals of the columns via:

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

The total field is not a calculated column.  Its value is fixed based  from the cursor.  However, if the need is say someone wants to encode further later in that excel output the values on OT and Holiday fields, then while the total of those columns will adjust, the Column total won't.

For this reason is why I am showing the way to extend these classes by means of further automation.

For additional automation to work, you need to have something to tie to to the intended output excel file.  It can be a _screen property, a global object, or simply a form property.  Once you know the filename to get back to, then performing additional automation on said file is a piece of cake.

Sample extended now:

Local ldStart, ldEnd

ldStart = Gomonth(Date(),-1)

ldEnd = Date()

Select EmpCode, Empname, Regular, OT, Holiday, Total ;

      FROM MyTable ;

      WHERE MyDate Between m.ldStart And m.ldEnd ;

      INTO Cursor junkrep NOFILTER

If _Tally > 0

      This._cursorname = 'junkrep'

      This._headings = "DAILY TIME RECORD|For the Period "+;

            DTOC(m.ldStart)+" to "+Dtoc(m.ldEnd))

      This._nopop = .T.

      * We have to save this now to a specific filename so we can perform extra automation later

      Local lcExcelSheet

      lcExcelSheet = Sys(3)

      This._filename = m.lcExcelSheet

      Thisform._excelrun = m.lcExcelSheet

      This._lwithoutput = .F.  && Don't show output

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

      DoDefault()

      * Perform additional automation

      Thisform._extraautomation()

Else

      Messagebox('Nothing to export yet!',0+64,'Oooopppsss!')

Endif

In the above, I now track the filename via a form property which I named _ExcelRun which stores the filename value, then a hardcoded instruction to not show the output but simply saves and exits via _lWithOutput = .F. property.  Then below I called another method in form I created and named _ExtraAutomation which has these codes:

#Define xlLastCell 11

Local lcFile, loExcel As Excel.Application, lnLastRow

* Get the file we worked on earlier

lcFile = FORCEEXT(ADDBS(GETENV("TMP"))+This._excelrun,'xls')

loExcel = Createobject("excel.application")

With loExcel

      .Workbooks.Open(m.lcFile)

      .ActiveCell.SpecialCells(xlLastCell).Select

      lnLastRow = .ActiveCell.Row

      .Range("E5").Select

      .Selection.FormulaR1C1 = "=SUM(RC[-7]:RC[-1])"

      .Selection.AutoFill(.Range("E5:E"+Transform(m.lnLastRow)))

      * show now

      .Visible = .T.

      Endwith

And the final result is a report in excel, nicely formatted and with horizontal calculated columns.

That is only a sample, you can do anything further on your end based on your need.  This post is just meant to show you the way how to extend the output of my classes.

I hope I have explained well because sometimes sharing a thought especially when you are rushing to do other things may result to a confusing write-ups.  But hey, I have to do a lot more other works on my end so just post a question below if you need one.

Cheers!

P.S.

If you are looking at the above image and you noticed Issuance button does not look like the button of my ssExcelPivot class, then you are right.  That is ButtonX class of ssUltimate theme 3.  Please read this as well for a somewhat related topic:  http://sandstorm36.blogspot.com.au/2014/04/working-on-invisible-objects.html