0

I need to add new columns in the table in my excel spreadsheet. And I have a problem with expanding the table.

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
wb = load_workbook("table.xlsx")
ws = wb.worksheets[0]
table = ws.tables['Table1']

table.ref = f'A1:{get_column_letter(ws.max_column) + str(ws.max_row+1)}'  # table expands downwards

f_col = TablePartList()
f_col.append(ws['F'])
ws['F1'] = '2015'
table.tableColumns.append(f_col)  # this decision don't work

if I use

ws.insert_cols(2)

the table will break completely. I will be grateful for any help.

this is the original table in spreadsheet

after using ws.insert_cols(2)

after using table.ref = f'A1:{get_column_letter(ws.max_column) + str(ws.max_row)}'

Nikolaj Kozich
  • 1
  • 1

2 Answers2

0

I faced this issue earlier today and was searching to find a way to refresh / reload the workbook so that the next iteration of my new column addition works, calling the initialization to reload the workbook did the trick.

##################################################################################################
import openpyxl


class CustomOpenpyxl:

    # Initialize the class with filename as only argument
    def __init__(self, _my_file_name):
        assert _my_file_name.split('.')[-1] == 'xlsx', 'Input file is not xlsx'
        self.my_filename = _my_file_name
        self.my_base_wb = openpyxl.load_workbook(self.my_filename, read_only=False)
        # following line will get the names of worksheets in the workbook
        self.ws_names_in_my_base_wb = self.my_base_wb.sheetnames
        # following line will set the last worksheet in the workbook as active
        self.my_base_active_ws = self.my_base_wb.active
        # following line will get the maximum column number in active worksheet
        self.my_base_active_ws_max_col = self.my_base_active_ws.max_column
        # following line will get the column names (as list) in active worksheet
        self.my_base_active_ws_col_titles = [_col_name[0].value for _col_name in self.my_base_active_ws.iter_cols()]

    # Method to get column titles for active worksheet
    # Argument to this method is: - Row number of Header Values
    def get_col_names_active_ws(self, _header_row_num):
        self.my_base_active_ws_col_titles = [_col_name.value for _col_name in
                                             self.my_base_active_ws[_header_row_num]]
        return self.my_base_active_ws_col_titles

    # Method to add a new column with name as the last column of worksheet
    # Arguments to this method are: - New column name and row number of headers
    def add_new_col_at_the_end_to_active_ws(self, _new_col_name, _header_row_num):
        # New column will be added only if, it does not exist already
        if _new_col_name not in self.get_col_names_active_ws(_header_row_num):
            # Note:- Since we are adding the column at the end, we need to auto identify its index
            # We are using the existing max_column property of the worksheet and incrementing it by 1
            _new_col_idx = self.my_base_active_ws_max_col + 1
            # Once the columns is added, we correctly identify the cell where the column name will be written
            self.my_base_active_ws.cell(row=_header_row_num, column=_new_col_idx).value = _new_col_name
            # This method calls save internally, hence its auto save from user perspective
            self.save_wb()

    # Method to save the workbook
    # No arguments to this method
    def save_wb(self):
        return self.my_base_wb.save(self.my_filename)

    # Method to reload the workbook after any value update or column additions
    # No argument to this method
    def reload_wb(self):
        # Call the initialization method with the file name to reload
        # since "openpyxl.load_workbook(self.my_filename, read_only=False)" is part of initialization
        self.__init__(self.my_filename)


_tgt_file_name = '_File_Data.xlsx'
# Instantiate an object
_mismatch_check_obj = CustomOpenpyxl(_tgt_file_name)

print(_mismatch_check_obj.get_col_names_active_ws(1))

# list of columns to be added
_new_columns_to_be_added = ['Mismatch-File-Name', 'Pilot-Indicator', 'DELTA', 'Theeta']

for _ in _new_columns_to_be_added:
    # Add the columns in a for loop
    _mismatch_check_obj.add_new_col_at_the_end_to_active_ws(_, 1)
    # Needed Step: Reload the workbook so that next iteration picks up the latest changes
    _mismatch_check_obj.reload_wb()

print(_mismatch_check_obj.get_col_names_active_ws(1))

#################################################Print - Result########
['My_Column']
['My_Column', 'Mismatch-File-Name', 'Pilot-Indicator', 'DELTA', 'Theeta']
Ashish Samarth
  • 147
  • 4
0

I solved the problem by adding the column then increasing the table size and then saving the hole thing. To do it again i update the worksheet and repeat the procedure. Of cause you can just add multiple colums at one and increase the table size by more then one column.

Here are the three functions i wrote for this. The addShit() function is copied from a link! So you only need an empty xlsx file to try this example. Imports:

import os
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo

def addShit(ws):
    ws.append(["Fruit", "2011", "2012", "2013", "2014","2011", "2012", "2013", "2014"])

    data = [
        ['Apples', 10000, 5000, 8000, 6000],
        ['Pears', 2000, 3000, 4000, 5000],
        ['Bananas', 6000, 6000, 6500, 6000],
        ['Oranges', 500, 300, 200, 700],
        ['Apples', 10000, 5000, 8000, 6000],
        ['Pears', 2000, 3000, 4000, 5000],
        ['Bananas', 6000, 6000, 6500, 6000],
        ['Oranges', 500, 300, 200, 700],
    ]
    for row in data:
        ws.append(row)
    tab = Table(displayName="Table1", ref="A1:E5")
    style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                           showLastColumn=False, showRowStripes=True, showColumnStripes=True)
    tab.tableStyleInfo = style
    ws.add_table(tab)

def update(xlsxPfad, sheetName):
    wb = load_workbook(filename=xlsxPfad)
    ws = wb[sheetName]
    wb.save(xlsxPfad)
    return wb,ws

def replaceTab(ws, ref, displayName):
    #copys autofilter
    autofilter = ws.tables[displayName].autoFilter

    #copy current style
    style = ws.tables[displayName].tableStyleInfo

    #create new enlarged table
    entab = Table(displayName=displayName, ref=ref)
    entab.tableStyleInfo = style

    #change current table to new enlarged table
    ws.tables[displayName] = entab

def addCol(ws, colNr ,headerRow , headerVal):
    ws.insert_cols(colNr)
    ws.cell(row=headerRow, column=colNr).value = headerVal

if __name__ == "__main__":
    UserName = os.getlogin()
    #empty xlsx file
    xlsxPfad = "C:\\Users\\" + str(UserName) + "\\Downloads\\book1.xlsx"
    wb = load_workbook(filename=xlsxPfad)
    ws = wb["Sheet1"]
    
    #Add data to empty xlsx sheet1
    addShit(ws)
    wb.save(xlsxPfad)
    #update workbook and worksheet
    wb, ws = update(xlsxPfad, "Sheet1")

    #add column in column 2
    addCol(ws, 2, 1, "NewCol1")

    # update table size
    replaceTab(ws, "A1:F5", "Table1")
    wb.save(xlsxPfad)

    #update workbook and worksheet
    wb, ws = update(xlsxPfad, "Sheet1")

    # add column in column 2
    addCol(ws, 2, 1, "NewCol2")

    #update table size
    replaceTab(ws, "A1:G5", "Table1")
    wb.save(xlsxPfad)

    os.startfile(xlsxPfad)
jakobis
  • 1
  • 1