dynamic ranges, repeating drop downs in rows, pivot tables

[agustin.villena]

Hi!

I'm a python and excel developer, and resolver one seems to be the perfect mix between them.

I have some questions:
- In Office 2007 exists the "Table" concept, that defines a resizable area of data with colums headers, named in a fancy way
Is there any concept like that in resolver one?
if not, in early versions of excel, I could define "dynamic ranges", i.e. ranges that autoresize covering only non blank elements
How I could resemble this feature in resolver one?

- I want to implement a CRUD-like page, where one colums must be filled only with values defined in another (dynamic) range. This implies that I want to define a dropdown in every new row of this page.
Is this possible in resolver one?

- is there anyway to emulate "pivot tables" feature in resolver one?
I only want to pivot data from another page in the workbook

Thanks and good job

Agustin

[tartley]

Hello Agustin,

Regarding the 'pivot table' aspect of your question: Resolver does not feature a GUI to help you build pivot tables, however, it does features a function called 'Aggregate' which helps you create them using a little bit of usercode in the spreadsheet.

I've created a quick demo of using Aggregate to produce a result similar to a pivot-table, 'pivot_tables_using_aggregate.rsl', that should be attached to this reply.

The first sheet 'donations', is purely data I typed in.

The second sheet is generated using a few lines of post-formula usercode, which invokes Aggregate. This calculates the count, the total and the average donation, all by country.

I used three built-in functions to aggregate the values in the pivot table (len, SUM and AVERAGE.) If you require a more complex aggregation function, you could write your own, as described here:
http://www.resolversystems.com/document ... #aggregate

I hope that's helpful, let me know if it isn't. Responses to your other questions should come soon.

Update: Attachment removed. See updated version below, posted by 'Jonathan Hartley'.

Thanks for trying out Resolver,

Jonathan

[glenn]

- In Office 2007 exists the "Table" concept, that defines a resizable area of data with colums headers, named in a fancy way
Is there any concept like that in resolver one?
if not, in early versions of excel, I could define "dynamic ranges", i.e. ranges that autoresize covering only non blank elements
How I could resemble this feature in resolver one?

You can dynamically create CellRanges in Resolver based on all sorts of criteria. Here we define a CellRange that covers the populated area of the spreadsheet.

Code: Select all

ws1 = workbook['Sheet1']
fullWorksheet = CellRange(ws1.TopLeft, ws1.BottomRight)


I want to implement a CRUD-like page, where one colums must be filled only with values defined in another (dynamic) range. This implies that I want to define a dropdown in every new row of this page.

Here, Sheet1 and Sheet2 have row 1 set as a HeaderRow. Sheet1!A1 contains the string 'Options' and the options for the dropdowns are below it in column A. Sheet2 has a number of columns. DD is the column where the dropdown will appear. As the user of the spreadsheet enters data into Sheet2, the dropdown for the new row will be created.

The following user code uses the ContentCells property of the Options column to create a dropdown in every populated row of Sheet2 in column DD. The options list picks up every value in the 'Options' column, so it dynamically changes as values are added or changed.

Code: Select all

ws1 = workbook['Sheet1']
ws2 = workbook['Sheet2']

options = [c.Value for c in ws1.Cols['Options'].ContentCells if c.Value is not Empty]
for row in ws2.ContentRows:
    row.Cells['DD'].DropdownItems = options


If this doesn't cover your specific use cases, let us know what you're trying to achieve and we can provide better examples.

[agustin.villena]

Thanks you for your answers. As soon as I have some time to try your solutions, I post some feedback in this forum

Agustin

[michael]

One thing we intended to mention, but forgot, is that if you want to use a dynamic range to access all data in a worksheet then there is a simple way to do it from user code.

Worksheets have two 'iterators', ContentRows and ContentsCols, that allow you to iterate over every row or column in the populated area of the worksheet (the bounds) automatically skipping the header row /col.

Code: Select all

sheet = worbook['Sheet1']
for row in sheet.ContentRows:
    person = row['Name']
    age = row['Age']
    if age > 30:
        row.BackColor = Color.Blue
        row['Favourite Color'] = 'Blue'


If you use the technique we suggested for creating a cellrange that covers the populated area, then you can set Traits directly on the cellrange to format the populated area (achieving some of the visual effect of a table in Excel 2007). For example:

Code: Select all

sheet = workbook['Sheet1']
cellrange = CellRange(sheet.TopLeft, sheet.BottomRight)

cellrange.BackColor = Color.PeachPuff
cellrange.BorderTop = True
cellrange.BorderLeft = True
cellrange.Rows[-1].BorderBottom = True
cellrange.Cols[-1].BorderRight = True


As you enter more data in the worksheet, the formatted cellrange automatically grows to cover it. You can also use the cellrange to extract / set data.

Worksheets have lots of useful properties that let you examine their contents / discover which areas contain useful data. It is relatively easy to use them to create cellranges that change dynamically as the data inside the sheet changes.

I hope this is helpful.

Michael

[jonathan hartley]

I updated the pivot table example to demonstrate behaviour that may be more in line with people's expectations (see attached .rsl document.)

The new version adds a column to the pivot table which lists the names of all people who donated from a particular country:

Code: Select all

donations = workbook['donations']
byCountry = workbook['byCountry']

Aggregate(donations, byCountry, 'Country', [
    ('Count', len),
    ('Total', SUM),
    ('mean', AVERAGE),
    ('names', lambda rows: [row['Person'] for row in rows]),
])

As you can see, I've defined an anonymous function here to define the 'names' column, which aggregates a list of all people donating from a given country.

As shown, this produces a slightly ugly looking output, with names displayed in the spreadsheet as a Python list. (eg. ['Jon', 'Will', 'Alice'])

To improve the appearance of lists like this in cells, you can add a formatter to that column:

Code: Select all

def listFormatter(x, _):
    if isinstance(x, basestring):
        return x
    else:
        return ', '.join(x)

byCountry.Cols['names'].Formatter = listFormatter

The names in the pivot table now appear in a pretty format.

[agustin.villena]

Hi!

I tried the pivot table example and works as expected
The thing that is not working for me is the "dynamic dropdown list" behavior

here is the spreadsheet where I tried to implement this feature. I'm trying to add tracking time to tasks defined on the "Plan" worksheet

Thanks for your answers!

[glenn]

ContentRows only includes rows up to the last non-empty row, so where you iterate over ContentRows there is nothing to iterate over unless the user has entered a value in one of the rows on the Tracking worksheet.

A simple fix is to use the following instead:

Code: Select all

for rowIndex in range(2, tracking.MaxRow + 2):
    tracking.Cells['Task', rowIndex].DropdownItems = tasks


As data is entered in to the tracking worksheet, the row after the last entry will get a dropdown.

[michael]

As Jonathan's example spreadsheet is a good example of using Aggregate, we've put it up on the website.

http://resolversystems.com/exchange/tag ... %20tables/