API

xl_link

class xl_link.XLDataFrame(data=None, index=None, columns=None, dtype=None, copy=False)

Monkeypatched DataFrame modified by xl_link!

• to_excel modified to return an XLMap.
• XLDataFrame._constructor set to XLDataFrame -> stops reverting to normal DataFrame

Notes

Conversions from this DataFrame to Series or Panels will return regular Panels and Series, which will convert back into regular DataFrame’s upon expanding/ reducing dimensions.

See also

Pandas.DataFrame

to_excel(excel_writer, sheet_name='Sheet1', na_rep='', float_format=None, columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, engine=None, merge_cells=True, encoding=None, inf_rep='inf', verbose=True, **kwargs)

Monkeypatched DataFrame.to_excel by xl_link!

Returns:corresponding to position of frame as it appears in excel (see XLMap for details) Return type:XLMap

See also

Pandas.DataFrame.to_excel()

Note

When providing a path as excel_writer, default engine used is ‘xlsxwriter’, as xlsxwriter workbooks can only be saved once, xl_link suppresses calling excel_writer.save(), as a result, xlmap.writer.save() should be called once no further changes are to be made to the spreadsheet.

class xl_link.XLMap(data_range, index_range, column_range, f, writer=None)

An object that maps a Pandas DataFrame to it’s positions on an excel spreadsheet.

Provides access to basic pandas indexers - __getitem__, loc, iloc, ix, iat and at. These indexers are modified such that they return the cell/ range of the result.

The idea is should make using the data in spreadsheet easy to access, by using Pandas indexing syntax. For example can be used to create charts more easily (see example below).

Notes

Recommended to not be created directly, instead via, XLDataFrame.to_excel.

XLMap can only go ‘one level deep’ in terms of indexing, because each indexer always returns either an XLCell, or an XLRange. The only workaround is to reduce the size of your DataFrame BEFORE you call write_frame. This limitation drastically simplifies the implementation. Examples of what WON’T WORK:

>>> xlmap.loc['Mon':'Tues', :].index
    AttributeError: 'XLRange' object has no attribute 'index'

>>> xlmap.index['Mon':'Tues'] # Doesn't work because index is not a Pandas Index, but an XLRange.
    TypeError: unsupported operand type(s) for -: 'str' and 'int'

Parameters:

• index_range, column_range (data_range,) – that represents the region the DataFrame’s data sits in.
• f (DataFrame) – that has been written to excel.

index

XLRange – range that the index column occupies

columns

XLRange – range that the frame columns occupy

data

XLRange – range that the frame data occupies

writer

Pandas.ExcelWriter – writer used to create spreadsheet

sheet

object – sheet object corresponding to sheet the frame was written to, handy if you want insert a chart into the same sheet

Examples

>>> calories_per_meal = XLDataFrame(columns=("Meal", "Mon", "Tues", "Weds", "Thur"),
                                     data={'Meal': ('Breakfast', 'Lunch', 'Dinner', 'Midnight Snack'),
                                           'Mon': (15, 20, 12, 3),
                                           'Tues': (5, 16, 3, 0),
                                           'Weds': (3, 22, 2, 8),
                                           'Thur': (6, 7, 1, 9)})
>>> calories_per_meal.set_index("Meal", drop=True, inplace=True)

Write to excel

>>> writer = pd.ExcelWriter("Example.xlsx", engine='xlsxwriter')
>>> xlmap = calories_per_meal.to_excel(writer, sheet_name="XLLinked") # returns the XLMap

Create chart with XLLink

>>> workbook = writer.book
>>> xl_linked_sheet = writer.sheets["XLLinked"]
>>> xl_linked_chart = workbook.add_chart({'type': 'column'})
>>> for time in calories_per_meal.index:
>>>     xl_linked_chart.add_series({'name': time,
                                    'categories': proxy.columns.frange,
                                    'values': proxy.loc[time].frange})

at

Proxy for DataFrame.at, see Pandas DataFrame at help for behaviour.

Will return location result rather than underlying data.

Returns:location corresponding to position value within spreadsheet. Return type:XLCell

Example

>>> xlmap.at["Mon", "Lunch"]
    <XLCell: C3>

create_chart(type_='scatter', values=None, categories=None, names=None, subtype=None, title=None, x_axis_name=None, y_axis_name=None)

Create excel chart object based off of data within the Frame.

Parameters:

• type (str) – Type of chart to create.
• values (str or list or tuple) – label or list of labels to corresponding to column to use as values for each series in chart. Default all columns.
• categories (str or list or tuple) – label or list of labels to corresponding to column to use as categories for each series in chart. Default, use index for ‘scatter’ or None for everything else.
• names (str or list or tuple) – str or list of strs to corresponding to names for each series in chart. Default, column names corresponding to values.
• subtype (str) – subtype of type, only available for some chart types e.g. bar, see Excel writing package for details
• title (str) – chart title
• x_axis_name (str) – used as label on x_axis
• y_axis_name (str) – used as label on y_axis

Returns:

Return type:

Chart object corresponding to the engine selected

Notes

values, categories parameters can only correspond to columns.

df

for convenience provides read-only access to the DataFrame originally written to excel.

f

for convenience provides read-only access to the DataFrame originally written to excel.

iat

Proxy for DataFrame.iat, see Pandas DataFrame iat help for behaviour.

Will return location result rather than underlying data.

Returns:location corresponding to position value within spreadsheet. Return type:XLCell

Example

>>> xlmap.iat[3, 2]
    <XLCell C3>

iloc

Proxy for DataFrame.iloc, see Pandas DataFrame iloc help for behaviour.

Will return location result rather than underlying data.

Returns:corresponding to position of DataFrame, Series or Scalar found within spreadsheet. Return type:XLCell or XLRange

Example

>>> xlmap.iloc[3, :]
    <XLRange: A2:D2>

ix

Proxy for DataFrame.ix, see Pandas DataFrame ix help for behaviour. (That said this is deprecated since 0.20!)

Will return location result rather than underlying data.

Returns:corresponding to position of DataFrame, Series or Scalar found within spreadsheet. Return type:XLCell or XLRange

Example

>>> xlmap.ix[3, :]
    <XLRange A2:D2>

loc

Proxy for DataFrame.loc, see Pandas DataFrame loc help for behaviour.

Will return location result rather than underlying data.

Returns:corresponding to position of DataFrame, Series or Scalar found within spreadsheet. Return type:XLCell or XLRange

Example

>>> xlmap.loc['Tues']
    <XLRange: A2:D2>

xl_link.write_frame(f, excel_writer, to_excel_args=None)

Write a Pandas DataFrame to excel by calling to_excel, returning an XLMap, that can be used to determine the position of parts of f, using pandas indexing.

Parameters:

• f (DataFrame) – Frame to write to excel
• excel_writer (str or ExcelWriter) – Path or existing Excel Writer to use to write frame
• to_excel_args (dict) – Additional arguments to pass to DataFrame.to_excel, see docs for DataFrame.to_excel

Returns:

Mapping that corresponds to the position in the spreadsheet that frame was written to.

Return type:

XLMap

xl_link.get_xl_ranges(frame_index, frame_columns, sheet_name='Sheet1', columns=None, header=True, index=True, index_label=None, startrow=0, startcol=0, merge_cells=True)

Deduces location of data_range, index_range and col_range within excel spreadsheet, given the parameters provided.

Does not require an actual DataFrame, which could be useful!

Parameters:

• frame_index (Pandas Index or Array-like) – to determine location of index within spreadsheet.
• frame_columns (Pandas Index or Array-like) – used to determine location of column within spreadsheet.
• excel_writer (string or ExcelWriter) –
• sheet_name (str) – default ‘Sheet1’, Name of sheet which will contain DataFrame
• columns (sequence) – optional, Columns to write
• header (bool or list of strings,) – default True Write out the column names. If a list of strings is given it is assumed to be aliases for the column names
• index (bool) – default True. Write row names (index)
• index_label (str or sequence) – default None. Column label for index column(s) if desired. If None is given, and header and index are True, then the index names are used. A sequence should be given if the DataFrame uses MultiIndex.
• startrow (int) – upper left cell row to dump data frame
• startcol (int) – upper left cell column to dump data frame
• merge_cells (bool) – default True. Write MultiIndex and Hierarchical Rows as merged cells.

Returns:

• data_range, index_range, col_range (XLRange) – Each range represents where the data, index and columns can be found on the spreadsheet
• empty_f (DatFrame) – an empty DataFrame with matching Indices.

xl_types.py

class xl_link.xl_types.XLRange(start, stop)

Represents a range in an Excel spreadsheet

Parameters:

• start (XLCell) – corresponding to start cell
• stop (XLCell) – corresponding to stop cell

start

XLCell – corresponding to start cell

stop

XLCell – corresponding to stop cell

sheet

str – name of sheet range exists in

Examples

Using __init__

>>> start = XLCell.from_cell('A1')
>>> stop = XLCell.from_cell('B7')
>>> XLRange(start, stop)
    <XLRange: A1:B7>

Notes

Upon initialisation, if start and stop are not from the same sheet, an assertion error is raised.

start and stop are attributes that can be accessed and modified directly.

All methods are designed to return new objects, preserving the state of self.

See also

XLRange.from_range, XLRange.from_frange

frange

Gets the Excel range this object represents, for use in excel formulas

Returns:representing this range in excel notation for use in excel formulas (e.g. “’{sheet}’!{start}:{stop}” Return type:str

Examples

>>> start = XLCell.from_cell('A1')
>>> stop = XLCell.from_cell('B7')
>>> range = XLRange(start, stop)
>>> range.frange
    "'Sheet1'!A1:B7"

classmethod from_frange(frange)

Alternative constructor

Parameters:frange (str) – str in excel formula notation (e.g. “‘Sheet1’!A1:B20”), representing excel range. Returns:initialised XLRange Return type:XLRange

classmethod from_range(range, sheet='Sheet1')

Alternative constructor

Parameters:

• range (str) – str in excel notation (e.g. ‘A1:B20’), representing excel range.
• sheet (str) – sheet range belongs to, default=’Sheet1’

Returns:

initialised XLRange

Return type:

XLRange

iterrows()

Iterate over each row of self, yields each row as XLRange

Yields:XLRange – corresponding to current row

range

Gets the Excel range this object represents

Returns:representing this range in excel notation Return type:str

Examples

>>> start = XLCell.from_cell('A1')
>>> stop = XLCell.from_cell('B7')
>>> range = XLRange(start, stop)
>>> range.range
    'A1:B7'

rowcol_rowcol

Coordinates of start and stop

Returns:

• start.row, stop.col (tuple) – start position in rows and column num (start.row, start.col)
• stop.row, stop.col (tuple) – stop position in rows and column num (stop.row, stop.col)

shape

returns: representing shape of XLRange in form (height, width) :rtype: tuple

trans(row, col)

Short for XLRange.translate

See also

XLRange.translate()

translate(row, col)

Translates whole range by row, col.

Parameters:

• row (int) – corresponding to movement in row direction (+ve down the spreadsheet)
• col (int) – corresponding to movement in col direction (+ve right across the spreadsheet)

Returns:

new with translation applied

Return type:

XLRange

Notes

This moves the whole range, and cannot be used to change the shape of self.

class xl_link.xl_types.XLCell(row, col, sheet='Sheet1')

Represents the location of a cell in an Excel spreadsheet

Parameters:

• row (int) – corresponding to cell row number (0 indexed)
• col (int) – corresponding to cell col number (0 indexed)
• sheet (str) – corresponding to the sheet the cell is in, default=’Sheet1’

row

int – corresponding to cell row number (0 indexed)

col

int – corresponding to cell col number (0 indexed)

sheet

str – corresponding to the sheet the cell is in, default=’Sheet1’

Examples

>>> XLCell(0, 5, 'Accounts')
    <XLCell: F1>

>>> XLCell.from_cell('F1', 'Accounts')
    <XLCell: F1>

>>> XLCell.from_fcell("'Accounts'!F1")
    <XLCell: F1>

Notes

sheet can be set to None if desired, keep in mind it defaults to ‘Sheet1’, mostly for convenience.

row, col and sheet are attributes that can be accessed and set directly.

All methods are designed to return new objects, preserving the state of self.

See also

XLCell.from_cell, XLCell.from_fcell

cell

Gets the cell location str.

Returns:cell location in excel notation Return type:str

Examples

>>> cell = XLCell(0, 5, 'Accounts')
>>> cell.cell
    'F1'

copy()

Returns:copy of self Return type:XLCell

fcell

Gets the cell location for use in excel formulas.

Returns:cell location in excel notation, in ‘{sheet}’!{cell} form Return type:str

Examples

>>> cell = XLCell(0, 5, 'Accounts')
>>> cell.fcell
    "'Accounts'!F1"

classmethod from_cell(cell, sheet='Sheet1')

Alternative constructor

Parameters:

• cell (str) – cell in excel notation (e.g. ‘A1’), representing cell location.
• sheet (sheet) – sheet cell belongs to, default=’Sheet1’

Returns:

Initialised XLCell

Return type:

XLCell

classmethod from_fcell(fcell)

Alternative constructor

Parameters:fcell (str) – cell in excel formula notation (e.g. “‘Sheet1’!A1”), representing cell location. Returns:Initialised XLCell Return type:XLCell

range_between(other)

Get XLRange between this cell and other.

Parameters:other (XLCell) – other XLCell that makes up stop of excel range Returns:range between this cell and other Return type:XLRange

Example

>>> start = XLCell.from_cell('A1')
>>> stop = XLCell.from_cell('B7')
>>> start.range_between(stop)
    <XLRange: A1:B7>

rowcol

Coordinates of cell

Returns:row, col – (row, col) corresponding to location of cell Return type:tuple(int, int)

trans(row, col)

Short for XLCell.translate

See also

XLCell.translate()

translate(row, col)

Returns new XLCell with translation applied

Parameters:

• row (int) – corresponding to movement in row direction (+ve down the spreadsheet)
• col (int) – corresponding to movement in col direction (+ve right across the spreadsheet)

Returns:

new cell with translation applied

Return type:

XLCell

chart_wrapper.py

xl_link.chart_wrapper.create_chart(workbook, engine, type_, values, categories, names, subtype=None, title=None, x_axis_name=None, y_axis_name=None)

Create a chart object corresponding to given engine, within workbook.

Parameters:

• workbook (object) – to insert chart into, either XlsxWriter.Workbooks or openpyxl Workbooks.
• engine (str) – representing engine to use, either XlsxWriter.Workbooks or openpyxl Workbooks.
• type (str) – representing chart type, see engine docs for options e.g. ‘line’.
• values (XLRange or sequence of XLRanges) – use as values for each series/ data set (Excel equivalent to y data).
• categories (XLRange or sequence of XLRanges) – use as categories for each series/ data set (Excel equivalent to x data).
• names (str/ sequence of str) – use as name for each series/ data set (Excel uses this to label each dataset).
• subtype (str) – representing chart subtype, see engine docs for details.

Returns:

chart – populated chart object corresponding to engine’s chart type.

Return type:

object

class xl_link.chart_wrapper.AbstractChartWrapper(book, type_, subtype=None, engine=None)

Wraps around excel writer module, for use by create_chart.

add_series(name, values, categories=None)

Abstract: should add a series to chart.

Parameters:

• name (str) – representing the name of the series
• values (XLRange) – represending values of series
• categories (XLRange) – representing values of categories