CODEWITHSUNDEEP
pythonexcelopenpyxl
Jakob
Jakob

Reputation: 80

Modify Named Table in Excel File with Python openpyxl

I want to add columns to an existing table in an excel file. Therefore I wan't to use python and the openpyxl library.

Right now I use a class when it is initialising, it is connecting to the file. Afterwards I call the check_for_column function and when the column is not existing it should create it. And in the end of the script I save the file.


import os
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableColumn, range_boundaries
from openpyxl.utils.cell import get_column_letter


class ExcelHandler:
    _wb_name = None
    _table = None
    _wb = None

    def __init__(self):
        self._wb_name = os.getenv('EXCEL_FULLPATH')
        self._wb = load_workbook(filename=self._wb_name, keep_vba=True)
        sheet = self._wb['DataInbox']
        self._table = sheet.tables['WebPageForms']
        return

    def check_for_column(self, column_name):

        if not column_name in self._table.column_names:
            lst_ids = [my_object.id for my_object in self._table.tableColumns]
            new_id = lst_ids[-1]+1

            # change range of table
            min_col, min_row, max_col, max_row = range_boundaries(
                self._table.ref)
            max_col += 1
            mx = get_column_letter(max_col)
            mn = get_column_letter(min_col)
            self._table.ref = '{}{}:{}{}'.format(mn, min_row, mx, max_row)

            # add column to table
            tc = TableColumn(id=new_id, name=column_name)
            self._table.tableColumns.append(tc)

        return

    def save_wb(self):
        self._wb.save(self._wb_name)
        return

The code runs fine as shown. Although when I then try to open the file with excel it gives me an alert saying:

We found a problem with some content in ’file.xlsm’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

This is the repair result of excel when I press yes

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>Repair Result to file.xml</logFileName><summary>Errors were detected in file ’*path*/file.xlsm’</summary><repairedRecords summary="Following is a list of repairs:"><repairedRecord>Repaired Records: Table from /xl/tables/table1.xml part (Table)</repairedRecord></repairedRecords></recoveryLog>

I would highly appreciate If anyone could help me

Upvotes: 1

Views: 980

Answers (1)

Jakob
Jakob

Reputation: 80

Ok, I found the problem why the excel file is corrupt, my bad. when I create the column in the table, I also have to write the name in the respective cell:

    def check_for_column(self, column_name):

            ***

            # write name in cell of new column header
            self._ws.cell(row=min_row, column=max_col).value = column_name

            ***

        return

If I add this to the code, my table is modified correctly

Upvotes: 1

Related Questions