Usage Tracking Spreadsheet

Loading the data from the Web server

Pre-constants user code loads the basic data from the Web server. Doing this here, before the constants are applied, allows a user to manually override the values by typing in adjusted values.

1. In the pre-constants user code, load up the usage data from a Web server. Use basic HTTP authentication to confirm with the server that we have access rights:

   from System.IO import StreamReader
   from System.Net import NetworkCredential, WebRequest
   
   uri = 'http://www.resolversystems.com' +
         '/documentation/examples/usages/usages.csv'
   request = WebRequest.Create(uri)
   request.Credentials = NetworkCredential("demouser", "demopassword")
   
   response = request.GetResponse()
   try:
       reader = StreamReader(response.GetResponseStream())
       usageData = reader.ReadToEnd()
   finally:
       response.Close()
   

2. Split the usage data into separate lines. Split each non-blank line into tab-separated values and put these into the "Raw data" sheet, converting them to numbers if possible:

   for rowIndex, line in enumerate(usageData.split("\n")):
       if not line.strip():
           continue
       for colIndex, value in enumerate(line.split("\t")):
           try:
               value = float(value)
           except:
               pass
           workbook["Raw data"][colIndex + 1, rowIndex + 1] = value
   

Adding calculations

The next interesting bit is where we add calculations. We do this in the pre-formulae user code so as to take account of any values that were manually adjusted.

• Work through all of the non-header rows in the raw data, and fill in a new "Existing user runs" column with the difference between the total number of runs and the runs by new users.

for row in workbook["Raw data"].Rows:
    if row is workbook["Raw data"].HeaderRow:
        continue
    row["Existing user runs"] = row["All runs"] - row["New user runs"]

Note the use of headers to make the formula easier to understand.

Drawing the graphs

The final bit of code, in the post-formulae user code section, draws the graph.

1. Load the ZedGraph assembly:

   import clr
   clr.AddReference("ZedGraph")
   

2. Import various elements for later use:

   from System import Array, Math, String
   from System.Drawing import Bitmap, Graphics, RectangleF
   from ZedGraph import AxisType, BarType, GraphPane, PointPairList, SymbolType
   

3. Create a GraphPane, which will do the graphing, with an appropriate size, title, and X and Y axis labels:

   chart = GraphPane(
       RectangleF(0, 0, 640, 480),
       "Resolver One Daily Usage",
       "Date",
       "Uses" )
   

4. Generate a list of dates for the X axis, and two lists of values to make up the two halves of each bar:

   dates = []
   existingRunsList = PointPairList()
   newRunsList = PointPairList()
   for row in workbook["Raw data"].Rows:
       if row is workbook["Raw data"].HeaderRow:
           continue
       dates.append(row["Date"])
       existingRunsList.Add(row.Index, row["Existing user runs"])
       newRunsList.Add(row.Index, row["New user runs"])
   

5. Pass the values, along with a couple of other configuration parameters, to the GraphPane.

   chart.AddBar("Existing runs", existingRunsList, Color.Blue)
   chart.AddBar("New runs", newRunsList, Color.Red)
   chart.XAxis.Type = AxisType.Text
   chart.XAxis.Scale.TextLabels = Array[String](dates)
   chart.BarSettings.Type = BarType.Stack
   g = Graphics.FromImage(Bitmap(1, 1))
   chart.AxisChange(g)
   

6. Get the image of the graph from the GraphPane, and create an image worksheet to display it.

And that's it! In a Python environment, tasks like this are straightforward, which is why we made Resolver One a Python environment...