Business Logic:
For this example worksheet we used the System.DateTime object to create all of our data. In real life you would use a custom .NET business object or an IronPython type, but the DateTime provides us with an easy to understand yet complex object that we can use to analyze a range of Dates. This object is going to be stored directly in the Resolver One spreadsheet, so we want to have full control over the formatting using the __str__() method. Since the DateTime object is a value type, we cannot inherit it, so instead we must create the following wrapper class in our PreConstantsCode:
class CustomDate(object):
"""Wrapper class for the System.DateTime object so that we can control the formatting and treat it as a simple Date instead of DateTime."""
def __init__(self, year, month, day):
"New CustomDate using year, month, and day)"
self.dt = DateTime(year,month,day) #self.dt will store the original DateTime object
def __str__(self):
"""Returns the DateTime using the ShortDateString. This is usually m/d/yyyy or d/m/yyyy depending on your location."""
return self.dt.ToShortDateString()
def AddDays(self, intDays):
"""Returns a new CustomDate whose value is this CustomDate + intDays"""
newDate = CustomDate(2000,12,1)
newDate.dt = self.dt.AddDays(intDays) #replace dt with self.dt+1Day
return newDate
def WeekOfYear(self, aDate=None):
"Return the Week of the Year(ie: week 2 of 52)"
if aDate == None: #Use this self.dt
return CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(self.dt, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
else:
return CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(aDate, CalendarWeekRule.FirstDay, DayOfWeek.Sunday);
def WeekOfMonth(self):
"Return the Week of the Month (ie: 1st week of the month)"
return self.WeekOfYear()-self.WeekOfYear(aDate=DateTime(self.dt.Year,self.dt.Month,1)) + 1
As you can see our object is mostly just a wrapper that stores an instance of the DateTime type. It also has a __str__() method that returns the ToShortDateString() for the DateTime that is stored and a few methods to help with determining what week of the month or week of the year this date is a part of. Most business object would be much more complex, but we get the benefit of using all the build in logic from the System.DateTime object to get access to the Day, Year, Month, and WeekDay values. See the Column Level Formulae table from the Basic Help section for more details.
Date Selector:
For our Date selector we use a button that will display a CalendarForm as defined in the rslCalendarForm.py file. The form includes a Windows.Forms.Calendar control which includes all of the validation and display logic needed to input a valid Date. To display the form we use the following code on the Resolver One button’s click event.
def btnStartDate_Click():
"Displays a DateTime picker to chose the start date."
dtStart = None
if type(StartDate.Value) == CustomDate:
dtStart = StartDate.Value.dt
frmCalendar = CalendarForm("Select Start Date", dtStart)
if CalendarForm.LastDateSelected <> None:
StartDate.Formula = "=CustomDate(%d,%d,%d)" % (CalendarForm.LastDateSelected.Year,CalendarForm.LastDateSelected.Month,CalendarForm.LastDateSelected.Day) #Set StartDate cell formula
LoadWorksheet(CustomDate(CalendarForm.LastDateSelected.Year,CalendarForm.LastDateSelected.Month,CalendarForm.LastDateSelected.Day), EndDate.Value) #formula does'nt get applied until the next recalculation, so send in a copy of the right data
The LoadWorksheet function simply determines how many days are in the date range and then creates the correct number of rows and loads the DateID column with the correct values. The worksheet Column Level Formula will then do the rest of the work for creating the data for each row.
Excel COM Automation:
Once the Worksheet data has been loaded, pressing the Pivot button will pack up the data into an array and then send it to the ExcelManager defined in ExcelManager.py to create a new ExcelWorksheet. Special care must be taken to make sure that the data type for each value is compatible with Excel, which only supports basic Numerical, Text and Date formats. Below is the method defined in the PreConstantsCode.py file used to Pack the data into an Array:
def PackCells(aCellRange):
"Packes the data from a worksheet or cellrange into rows in an array."
lstPacked = []
for aRow in aCellRange.Rows:
lstRow = []
lstPacked.append(lstRow)
for aCell in aRow.Cells:
if type(aCell.Value) in (int, long, float, complex, Decimal,DateTime): #Keep integer types the same. Also DateTime is a special case (See ExcelManager.SetCell)
lstRow.append(aCell.Value)
elif type(aCell.Value) == CustomDate: #Convert our CustomDate back to a DateTime
lstRow.append(aCell.Value.dt)
elif aCell.Value is Empty: #Convert ResolverOne Empty type to None
lstRow.append(None)
else: #Cast non-integer variables to string
lstRow.append(str(aCell.Value))
return lstPacked
Once we have the worksheet data packed into an array, we now can use the ExcelManager object to create a new instance of Excel and setup the Pivot Chart, Pivot Table and Autofilter worksheets. Here is the Pivot button click handler code:
def btnPivot_Click():
"Pivot button click method"
print "Start generating Pivot table."
global PackedData #Create as global variable so that you can debug easier using the Console
PackedData = PackCells(PivotRange)
ExcelManager.OpenExcel()
ExcelManager.NewBlankWorkbook("Autofilter")
ExcelManager.LoadSheetData(PackedData)
ExcelManager.CreatePivotTable("Pivot Table")
ExcelManager.CreatePivotChart("Pivot Chart")
ExcelManager.HideToolbars() #Disable all the toolbars except the Pivottable, Standard toolbar and FormulaBar
ExcelManager.ShowWindow()
print "Finished generating Pivot table."
ExcelManager will handle all of the COM Automation for creating the new workbook, copying the data into the worksheet, adding the Autofilter, adding the PivotTable and PivotChart, and then displaying the Workbook. For more details please see the ExcelManager.py file.
HTML based Help file:
This workbook also uses an HTML based help system called rslTutorial. The help system consists of a Windows.Form that includes a tab strip and a WebBrowser control, as well as a rudimentary system for creating custom actions for individual webpages. The goal was to be able to create a fully interactive tutorial system such that when users pressed a “Show Me” button it would perform a script of operations on the Resolver One workbook, but after testing it was found that there are a few difficulties in being able to update a workbook from a separate thread. In most cases you do not need a full tutorial anyway, so the system still works well for embedding HTML based help documentation directly into the workbook. The HTML files can be served locally or live from the web. Here is the Help button click handler code used to display this help guide:
myHelp = '' #Define help system as global variable so it can be debuged from the console
def btnHelp_Click():
"Method called when Help button is clicked. Used to create an HTML based help system."
global myHelp
print "btnHelp_Click start at "+ DateTime.Now.ToString()
#Setup Help system
from rslTutorial import *
myHelp = rslTutorial()
myHelp.CreateTab("Basic Help", dirname(__file__)+"\\Help\\Basic.htm") #Add starting tabs, load files from local \help\ folder
myHelp.CreateTab("Data Analysis", dirname(__file__)+"\\Help\\DataAnalysis.htm")
myHelp.CreateTab("Code Overview", dirname(__file__)+"\\Help\\Code.htm")
myHelp.CreateTab("About", "http://www.theg2.net/rslpivot/About.htm") #load directly from web
myHelp.Start()
print "btnHelp_Click end at "+ DateTime.Now.ToString()