pyxcelframe 1.6

Useful tool for more specialized interactions between the Pandas DataFrames and the Excel worksheets

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 ex - DataFrame 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

5. insert_columns(worksheet, dataframe, columns_dict, row_range, num_str_cols, headers)

• From our package pyxcelframe import function insert_columns;
• Insert ex - DataFrame into our sheet according to the cols_dict - Dict which contains the ex DataFrame's column names as the keys and the worksheet Excel Worksheet's column names as the values:

NOTE: Only those columns that are included as the cols_dict keys will be inserted into the worksheet from the ex DataFrame; Also, all the other parameters are similar to the parameters of the insert_frame function, so we will only be giving the required arguments for this example.

from pyxcelframe import insert_columns


# Column "Num" of the `ex` DataFrame will be
# inserted to the "I" column of the `worksheet`
# "Descr" to "J"
# "Squared" to "L"
cols_dict = {
    "Num": "I",
    "Descr": "J",
    "Squared": "L"
}

insert_columns(worksheet=worksheet,
               dataframe=df,
               columns_dict=cols_dict)

• Finally, let's save our changes to a new Excel file:

workbook.save('output.xlsx')

For the really detailed description of the parameters, please see __doc__ attribute of the above functions.

Full Code

import pandas as pd
from openpyxl import load_workbook
from pyxcelframe import copy_cell_style, \
                        insert_frame, \
                        insert_columns, \
                        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']

# Column "Num" of the `ex` DataFrame will be
# inserted to the "I" column of the `worksheet`
# "Descr" to "J"
# "Squared" to "L"
cols_dict = {
    "Num": "I",
    "Descr": "J",
    "Squared": "L"
}


# 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)

## 3 - Insertion according to the `cols_dict` dictionary
insert_columns(worksheet=worksheet,
               dataframe=df,
               columns_dict=cols_dict)


workbook.save('output.xlsx')