Добавляйте строки в таблицу Excel с помощью Python, не забивая формулы
поэтому я пишу сценарий, который собирает данные из листа Excel, немного их обрабатывает, а затем добавляет в другую таблицу в другом файле Excel. целевая таблица содержит несколько столбцов чистых данных и несколько столбцов формул. Я хочу добавить новые строки, сохранив при этом формулы.
проблема в том, что когда я пытаюсь извлечь данные из целевой таблицы, чтобы добавить имеющиеся у меня данные, я просто получаю значения, а не формулы, а когда я .update() таблицу, я просто получаю значения, а формулы исчезают.
мой код выглядит примерно так (простите за испанские имена переменных):
import pandas as pd
import xlwings as xw
planilla = 'planilla.xlsx'
sheet = 'Hoja1'
tabla = "empleados"
with xw.App(visible=True) as xl:
book = xl.books.open(planilla)
rng = book.sheets[sheet].tables[tabla].range
df= rng.expand().options(pd.DataFrame , index = False).value
newrow = {"nam" :"Carlos",
'años' : 23.0,
"trabajo" : "actor",
"sueldo": 100.0
}
df2= pd.DataFrame([newrow],index=["10"])
conc = pd.concat((df,df2))
book.sheets[sheet].tables[tabla].update(conc, index=False)
book.save()
book.close()
давайте предположим, что есть еще 2 столбца, которые имеют что-то вроде "=[@sueldo]*[@años]"или какой-либо другой формулы
Код работает, работает без ошибок, но формулы пропали, их заменяют значения, превосходящие рассчитанные до первого запуска кода. мне нужно, чтобы формулы оставались после добавления строк. возможно ли это?
Ответы 2
Возможно я ошибаюсь, но....
Вероятно, это связано с тем, что мы не можем перенести лист из Excel в Pandas с неповрежденной формулой. (это связано с тем, что формулы в Excel не являются допустимым типом данных в Pandas или SQL и т. д.) мы можем извлекать только данные допустимого типа данных, то есть строки, int, float и т. д.
Я предлагаю вам скопировать лист с формулами на другой лист и обязательно «вставить только значения» и попытаться обновить или добавить новые данные на этот новый лист. Таким образом, вы можете хранить свои формулы на первом листе. Затем вы сможете использовать эти формулы на новом листе с новыми данными, как только снова откроете Excel.
Xlwings хранит формулы в атрибутах ячеек «формула» и «формула2». Атрибут ячейки «значение» всегда содержит только значение ячейки. Поэтому при извлечении данных с помощью
rng.expand().options(pd.DataFrame , index = False).value
любые ячейки с формулой будут оценкой этой формулы, а не самой формулой.
Например. для ячейки A1, содержащей формулу =SUM(B1+C1)wsheet['A1'].formula это формула =SUM(B1+C1)wsheet['A1'].value всегда является значением суммы B1 и C1
В любом случае ваш метод кажется слишком сложным, поскольку вы просто хотите добавить еще одну строку в существующую таблицу Excel.
Нет необходимости копировать данные таблицы, добавлять их, а затем вставлять обратно в Excel с помощью Pandas.
Поскольку вы используете приложение Excel с Xlwings, просто добавьте новую строку в конец существующей таблицы, и Excel автоматически включит эту строку в эту таблицу.
В приведенном ниже примере кода получается диапазон таблицы, например. 'A6:D17'
Исходя из этого определяется столбец и строка для следующей строки таблицы, например. 'А18'
Записывает новые данные на лист по этой координате, поэтому Excel включает строку в существующую таблицу.
Существующие данные в таблице не изменяются, а формулы остаются без изменений.
Пример кода;
Примечание. Для удобства я использовал утилиту Openpyxl для преобразования некоторой информации о координатах. В остальном этот пример не использует модуль Openpyxl.
import xlwings as xw
import openpyxl
planilla = 'planilla.xlsx'
sheet = 'Hoja1'
tabla = "empleados"
with xw.App(visible=True) as xl:
book = xl.books.open(planilla)
wsheet = book.sheets[sheet]
### Get the range of the existing Table named tabla
table_range = wsheet.range(tabla).expand('table').address
### Use top left cell and bottom right cell to get the coord for the next row to be added
table_tlc, table_brc = table_range.replace('$', '').split(':')
tl_col = openpyxl.utils.cell.coordinate_from_string(table_tlc)[0]
tl_row = openpyxl.utils.cell.coordinate_from_string(table_brc)[1]
### Create next row coordinates
next_row = f"{tl_col}{tl_row+1}"
### Add the new data to this row
wsheet.range(next_row).value = [["Carlos", 23.0, "actor", 100.0]]
### Save the workbook
book.save()
book.close()