使用openpyxl操作现有的Excel表 [英] Manipulate existing excel table using openpyxl

问题描述

我目前正在磨练自己的python/excel技能，并且遇到了openpyxl问题

I'm currently honing my python / excel skills, and have run into an issue with openpyxl

我试图打开一个工作簿，替换现有表中的行，然后再次保存该工作簿.

I am trying to open a workbook, replace rows in an existing table, and save the workbook again.

理想情况下，我还想先删除表中的所有行(尽管保留表结构)

Ideally I'd like to also first be able delete all rows from the table (Though retaining the table structure)

我的初始工作簿包含一个名为"inputData"的工作表.在此，我有一个名为数据"的表，其中A，B，C列和2行数据.

My initial workbook contains a sheet named "inputData". In this I have a table named "Data" columns A, B, C and 2 rows of data.

我还有一个名为"input.csv"的csv文件，其中包含相同的列，但有4行数据.

I also have a csv file named "input.csv" containing The same columns but 4 rows of data.

当我运行代码时，数据被写入工作表中，但是表结构并未扩展为包含两行新数据.

when I run my code, the data is written into the worksheet, but the table structure is not expanded to encompass the two new rows of data.

关于如何使用openpyxl更改命名表结构的数据源的任何想法?

Any ideas of how to change the data source of a named table structure using openpyxl?

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

wb = load_workbook(filename = 'workbook.xlsx')
ws = wb["inputData"]

with open('input.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader):
        if not i == 0:
            for j, cell in enumerate(row): 
                ws.cell(row=i+1, column=j+1).value = cell

wb.save('output.xlsx')

链接到文件

推荐答案

我想出了我问题的答案.

I figured out the answer to my question.

我能够从openpyxl访问表，更改ref(范围)，然后再次将其保存.

I am able to access the table from openpyxl, change the ref (range) and then save it back again.

这使我可以在同一表中输入更多数据，并使我的公式在其他工作表中可以将新数据考虑在内.

This enables me to enter more data into the same table, and have my formulas on my other worksheet take the new data into account.

当我需要在将来不破坏引用的情况下将大量数据推送到现有的excel工作表中时，这将是一个非常有用的功能.

This will be a very helpful feature, when I need to push a lot of data into an existing excel sheet without breaking references in the future.

import csv
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table, TableStyleInfo
tableName = 'Data'

style = TableStyleInfo(name="TableStyleMedium9", showFirstColumn=False,
                       showLastColumn=False, showRowStripes=True, showColumnStripes=False)

def colnum_string(n):
    string = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        string = chr(65 + remainder) + string
    return string

wb = load_workbook(filename = 'workbook.xlsx')
ws = wb["inputData"]

with open('input.csv', newline='', encoding='utf-8-sig') as f:
    reader = csv.reader(f, delimiter=';')
    for i, row in enumerate(reader):
        for j, cell in enumerate(row): 
            if not i == 0:
                ws.cell(row=i+1, column=j+1).value = float(cell)
            else:
                ws.cell(row=i+1, column=j+1).value = cell

            maxRef = [i,j]

for i, table in enumerate(ws._tables):
    if table.name == tableName:
        tableRef = i

resTable = Table(displayName="Data", ref="A1:{}{}".format(colnum_string(maxRef[0]), maxRef[1]))
resTable.tableStyleInfo = style

ws._tables[tableRef] = resTable

wb.save('output.xlsx')

这篇关于使用openpyxl操作现有的Excel表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！