PyxcelFrame

Tools for more specialized interactions between the Pandas DataFrames and the Excel worksheets.

Install

pip install pyxcelframe

Usage

Examples

Let’s suppose that we have an Excel file named “numbers.xlsx” with the sheet
named “Dictionary” in which we would like to insert the pandas.DataFrame.

Import pandas and create an example DataFrame (which will be inserted into the Excel worksheet):

import pandas as pd


ex = {
    'Num': [1, 2, 3, 4],
    'AfterFirstBlankCol': 'AfterFirstBlank',
    'Descr': ['One', 'Two', 'Three', 'Four'],
    'AfterSecondBlankCol': 'AfterSecondBlank.',
    'Squared': [1, 4, 9, 16],
    'Binary:': ['1', '10', '11', '100']
}

df = pd.DataFrame(ex)
  • Import openpyxl.load_workbook and open numbers.xlsx – Our Excel workbook;
  • Get – Dictionary our desired sheet:

from openpyxl import load_workbook


workbook = load_workbook('numbers.xlsx')
worksheet = workbook['Dictionary']

Functions

1. column_last_row(worksheet, column_name)
  • If we had to get the last non-empty row in coolumn A of Excel worksheet called Dictionary and if we definitely knew that there would not be more than 10000 row records in that column:

NOTE: By default count_from will be 1048576, because that number is the total amount of the rows in an Excel worksheet.

from pyxcelframe import column_last_row


column_last_row(worksheet=worksheet, column_name=['A'], count_from=10000)
2. copy_cell_style(cell_src, cell_dst)
  • Let’s say, we have a cell in Excel Dictionary worksheet that we would like to copy the style from,
    and it is O3;
  • Let O4 be our destination cell:

NOTE: If we wanted to copy that style to more than one cell, we would simply use the loop
depending on the locations of the destination cells.

from pyxcelframe import copy_cell_style


copy_cell_style(cell_src=worksheet['O3'], cell_dst=worksheet['O4'])
3. sheet_to_sheet(filename_sheetname_src, filename_sheetname_dst, calculated)
  • Let’s say that we have two Excel files, and we need specific sheet from one file
    to be completely copied to another file’s specific sheet;
  • filename_sheetname_src is the parameter for one file -> sheet the data
    to be copied from (tuple([‘FILENAME_SRC’, ‘SHEETNAME_SRC’]));
  • worksheet_dst is the parameter for the destination Worksheet the data
    to be copied to (openpyxl.worksheet.worksheet.Worksheet);
  • Let’s assume that we have file_src.xlsx as src file and for worksheet_src we can
    use its CopyThisSheet sheet.
  • We can use output.xlsx -> CopyToThisSheet sheet as the destination worksheet, for which
    we already declared the Workbook object above.

NOTE: We are assuming that we need all the formulas (where available) from the source sheet, not calculated data, so we set calculated parameter to False.

from pyxcelframe import sheet_to_sheet


worksheet_to = workbook['CopyToThisSheet']

sheet_to_sheet(filename_sheetname_src=('file_src.xlsx', 'CopyThisSheet'),
               worksheet_dst=worksheet_to,
               calculated=False)
4. insert_frame(worksheet, dataframe, col_range, row_range, num_str_cols, skip_cols, headers)
  • From our package pyxcelframe import function insert_frame;
  • Insert exDataFrame into our sheet twice – with and without conditions:

from pyxcelframe import insert_frame


# 1 - Simple insertion
insert_frame(worksheet=worksheet, dataframe=df)

# 2 - Insertion with some conditions
insert_frame(worksheet=worksheet,
             dataframe=df,
             col_range=(3, 0),
             row_range=(6, 8),
             num_str_cols=['I'],
             skip_cols=['D', 'F'],
             headers=True)

In the first insertion, we did not give our function any arguments, which means the DataFrame
ex will be inserted into the Dictionary sheet in the area A1:F4 (without the headers).

However, with the second insertion we define some conditions:

  • col_range=(3, 0) – This means that insertion will be started at the Excel column with the
    index 3 (column C) and will not be stopped until the very end, since we gave 0 as the
    second element of the tuple

  • row_range=(6, 8) – Only in between these rows (in Excel) will the DataFrame data be inserted,
    which means that only the first row (since the headers is set to True) from ex will be inserted into the sheet

  • num_str_cols=['F'] – Another condition here is to not convert Binary column values to int.
    If we count, this column will be inserted in the Excel column F, so we tell the function to leave
    the values in it as string

  • skip_cols=['D', 'F']D and F columns in Excel will be skipped and since our worksheet
    was blank in the beginning, these columns will be blank (that is why I named the columns in the
    DataFrame related names)

  • headers=True – This time, the DataFrame columns will be inserted, too, so the overall
    insertion area would be C6:J8

For really detailed description of the parameters, please see:
  1. insert_frame.__docs__
  2. sheet_to_sheet.__docs__
  3. copy_cell_style.__docs__
  • Finally, let’s save our changes to a new Excel file:
workbook.save('output.xlsx')

Full Code

import pandas as pd
from openpyxl import load_workbook
from pyxcelframe import copy_cell_style, \
                        insert_frame, \
                        sheet_to_sheet, \
                        column_last_row


ex = {
    'Num': [1, 2, 3, 4],
    'AfterFirstBlankCol': 'AfterFirstBlank',
    'Descr': ['One', 'Two', 'Three', 'Four'],
    'AfterSecondBlankCol': 'AfterSecondBlank.',
    'Squared': [1, 4, 9, 16],
    'Binary:': ['1', '10', '11', '100']
}

df = pd.DataFrame(ex)

workbook = load_workbook('numbers.xlsx')
worksheet = workbook['Dictionary']


# Get the last non-empty row of the specific column
column_last_row(worksheet=worksheet, column_name=['A'], count_from=10000)


# Copy the cell style
copy_cell_style(cell_src=worksheet['O3'], cell_dst=worksheet['O4'])


# Copy the entire sheet
worksheet_to = workbook['CopyToThisSheet']

sheet_to_sheet(filename_sheetname_src=('file_src.xlsx', 'CopyThisSheet'),
               worksheet_dst=worksheet_to,
               calculated=False)


# Insert DataFrame into the sheet

## 1 - Simple insertion
insert_frame(worksheet=worksheet, dataframe=df)

## 2 - Insertion with some conditions
insert_frame(worksheet=worksheet,
             dataframe=df,
             col_range=(3, 0),
             row_range=(6, 8),
             num_str_cols=['I'],
             skip_cols=['D', 'F'],
             headers=True)

workbook.save('output.xlsx')

GitHub

View Github