<jupyter_start><jupyter_text>How to work with openpyxlA lot of times we need to use python to either create Excel reports with fancy formating and formulas or update existing reports with new data. Up to now there is no perfect python library that does a good job working with Excel files Introduction on openpyxl Installation- The installation is pretty straight forward: `pip install openpyxl`- For more details, go to this page: https://openpyxl.readthedocs.io/en/stable/index.html Example setup- `sample.xlsx` has two worksheets- `PFC.csv` file is a csv file. Data in this file will be inserted into `sample.xlsx` file Example 1: Insert rows- This example shows how to insert rows into an existing worksheet.- New rows will be inserted under the header row and on top of existing data rows.- The screenshot before inserting new rows![title](img/openpyxl-1.png)- The screenshot after new rows are inserted![title](img/openpyxl-2.png) Example 2: Replace with new data- This example shows how to first clear contents in an worksheet and then push new data to the worksheet- First data in a worksheet will be cleared; Then new data will be pushed to the worksheet, but the formatting will be kept.- The screenshot before replacing worksheet with new data![title](img/openpyxl-3.png)- The screenshot after replacing worksheet with new data![title](img/openpyxl-4.png) Limitations- one big limitation is this package can't read password protected Excel files though it can create password protected excel files<jupyter_code>#load libraries
import pandas as pd
from openpyxl import Workbook, load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
#read sample excel file
wb = load_workbook(filename = 'sample.xlsx')
print(wb.sheetnames)
#read new data file
df_new = pd.read_csv('PFE.csv')
df_new.shape
df_new.head()<jupyter_output><empty_output><jupyter_text>Example 1: Insert rows<jupyter_code>#demostrates how to insert new rows on top (below the header row)
ws = wb['PFE']
ws.insert_rows(idx=2, amount=5)
i = 0
for row in ws['A2:G6']:
    j = 0
    for cell in row:
        cell.value = df_new.iloc[i, j]
        j = j+1

    i = i+1<jupyter_output><empty_output><jupyter_text>Example 2: Replace with new data<jupyter_code>#first clear contents 
#then assign values
ws = wb['PFE2']
print(ws.max_row, ws.max_column)
for row in ws['A2:G' + str(ws.max_row)]:
    for cell in row:
        cell.value = None

print(ws.max_row)

i = 0
for row in ws['A2:G' + str(df_new.shape[0])]:
    j = 0
    for cell in row:
        cell.value = df_new.iloc[i, j]
        j = j+1

    i = i+1

wb.save(filename = 'sample.xlsx')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08521870286576169, 'fraction_numerical': 0.00904977375565611, 'mean_word_length': 4.414285714285715, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '508741', 'license': 'Apache-2.0', 'n_tokens_mistral': 859, 'n_tokens_neox': 821, 'n_words': 335, 'path': 'openpyxl.ipynb', 'repo_name': 'xxxxyyyy80008/python_notes', 'size': '2652'}