excel.py

A toolkit for working with xlsx files.

It demonstrates a bit of fundamental learning about the way openpyxl works.

NB: Row 1 is the header, and column 1 is A. This means that the first element of the matrix, (0, 0), is coordinate ‘A1’. labtools-1.6.7 dealt with a somewhat different behavior that was exhibited by openpyxl 1.8. The openpyxl 2 rev is more consistent. labtools 1.6.8 is somewhat changed to account for that.

Here’s openpyxl locally: $TEAM_ROOT/3rdparty/$OS/anaconda/lib/python2.7/site-packages/openpyxl/cell.py

excel.add_cell(ws, cell, force=False)

Behave like worksheet internal _get_cell() but add a newly created cell.

excel.build_column(ws, newvalues, columnid=None)

Put newvalues into column columnid of worksheet ws.

If newvalues is a dict, treat keys as row indices, if a list, lay them out in order.

If columnid is None, add the column at the right.

excel.build_row(ws, newvalues, rowid=None, fromcol=None, tocol=None)

Put newvalues into row rowname of worksheet ws.

Use column headers if newvalues is a dict, use array offsets if a list.

If rowid is None, add the row at the bottom.

fromcol and tocol apply to list inputs only

excel.cell_name(c)

This is just for documenting how to do it.

excel.column_cells(ws, name=None, columnletter=None, fromrow=1, torow=None)

Return a list of the values in the named column

excel.column_headers(ws)

return the top row of cells

excel.column_index(ws, name)

Return the index of a column name in worksheet ws

excel.column_names(ws)

Return a list of the column names in worksheet ws

excel.column_values(ws, name=None, columnletter=None, fromrow=1, torow=None)

Return a list of the values of the named column in worksheet ws

excel.delete_rows(ws, fromrow, torow=None)

Remove a row or set of rows altogether.

excel.excelfile_rows(xlsfile, sheetname=None)

Read an excel file and build something that looks like the input stream of a pipe-style file.

excel.hide_column(ws, column, mode='hide')

Make a column invisible. Width 0 as well, I think.

excel.hide_row(ws, row, mode='hide')

Make a row invisible. Height 0 as well, I think.

excel.last_cell_coordinate(ws)

Return the coordinate of the last column in worksheet ws

excel.last_column(ws)

Return the coordinate of the last column in worksheet ws

excel.last_row(ws)

Return the id of the last row in worksheet ws

excel.range_overlap(range1=None, range2=None)

Test whether two ranges overlap

excel.row_cells(ws, row, fromcol=1, tocol=None)

Return a list of the values in the row

excel.row_values(ws, row, fromcol=1, tocol=None)

Return a list of the values in the row

excel.set_column_widths(ws, widths, fromcol=1)

Set the column widths of a list of columns

excel.sheet_names(wb)

Return a list of the worksheet names in workbook wb