如何使用Openpyxl获取当前行索引 [英] how to get the current row index with Openpyxl
问题描述
我编写了一个Python脚本,用于从.json文件中提取一些字符串值,将它们存储在字典中,然后使用Openpyxl将其填充到.xlsx文件中,这是我第一次使用:
I wrote a Python script to extract some string values from a .json file, store them in some dictionnary and fill them in an .xlsx file using Openpyxl, which I use for the 1st time:
简而言之,它看起来像这样:
in short, it looks like that :
WORKBOOK = Workbook()
WORKSHEET = WORKBOOK.active
. . .
. . .
for PERSON in TEAM_LIST:
for ITEM in ITEMS[PERSON]:
if PERSON in REGULAR_LIST:
PERSON_ITEMS_ROW = (PERSON,ITEM[0],ITEM[1],ITEM[2],ITEM[3],ITEM[4)]
SHEET.append(PERSON_ITEMS_ROW) # Fill each row with some PERSON ITEMS
else:
PERSON_ITEMS_ROW = (PERSON,ITEM[0],ITEM[1],ITEM[2],ITEM[5],ITEM[6])
SHEET.append(PERSON_ITEMS_ROW) # Fill each row with other PERSON ITEMS
此代码运行良好(尽管我不是100%确信它是正确的)
This code works well (although I am not 100% sure it is correct)
我想更改上面其他"部分中选择的行的背景色和前景色,但我设法找到一种解决方法;
I would like to change backgroud and foreground color of rows selected in the "else" part above, and I don't manage to find a way to do it ;
我知道如何将特定的颜色和字体应用于特定的行:我对用作标题行的第一行执行此操作,但是我不知道如何获取当前行的索引,因此我可以将特定的颜色和字体应用于其他"部分的每一行上的字体
I know how to apply specific color and font to a specific row : I do it for the 1st row used as the header row, but I don't know how to get the current row index so I could apply specific color and font on each row of "else" section
欢迎提出任何想法
谢谢
推荐答案
您正在寻找ws._current_row
.
注意:ws._current_row
仅在插入新单元格后才有效.
You are looking for ws._current_row
.
Note: ws._current_row
are only valid after inserting new cells.
您可以执行以下操作:
...
SHEET.append(PERSON_ITEMS_ROW)
# For all cells in ws._current_row
for row_cells in ws.iter_rows(min_row=ws._current_row, max_row=ws._current_row):
for cell in row_cells:
cell.font = Font(color=colors.GREEN, italic=True)
# Only for cell in column A == 1
ws.cell(row=ws._current_row, column=1).font = Font(color=colors.RED)
如果您不想使用不受支持的ws._current_row
.
If you don't want to use unsupported ws._current_row
.
# Assume you start in row==2
for ws_current_row, PERSON in enumerate(TEAM_LIST, 2):
#...
#SHEET.append(PERSON_ITEMS_ROW)
# For all cells in ws_current_row
for row_cells in ws.iter_rows(min_row=ws_current_row, max_row=ws_current_row):
for cell in row_cells:
cell.font = Font(color=colors.GREEN, italic=True)
# Only for cell in column A == 1
ws.cell(row=ws_current_row, column=1).font = Font(color=colors.RED)
OOP解决方案或openpyxl
可以实现它.
例如:
OOP solution, or openpyxl
could implemented it.
For instance:
from openpyxl.workbook.workbook import Workbook as _Workbook
from openpyxl.worksheet.worksheet import Worksheet as _Worksheet
class Worksheet(_Worksheet):
# Overload openpyxl.Worksheet.append
def append(self, iterable):
super().append(iterable)
return self._current_row, \
self._cells_by_col(min_col=1, min_row=self._current_row,
max_col=self.max_column, max_row=self._current_row)
class Workbook(_Workbook):
# Workaround, as openpyxl is not using self.create_sheet(...) in __init__
def __init__(self, write_only=True):
super().__init__(write_only)
self.__write_only = False
self.create_sheet()
# Not working for self.read_only and self.write_only :
# Overload openpyxl.Workbook.create_sheet
def create_sheet(self, title=None, index=None):
new_ws = Worksheet(parent=self, title=title)
self._add_sheet(sheet=new_ws, index=index)
return new_ws
for PERSON in TEAM_LIST:
# ...
ws_current_row, iter_col = SHEET.append(PERSON_ITEMS_ROW)
# Iterate all cells from generator iter_col
for cell in [col_cells[0] for col_cells in iter_col]:
cell.font = Font(color=colors.RED, italic=True)
# Only for cell in column A == 1
ws.cell(row=ws_current_row, column=1).font = Font(color=colors.BLUE)
使用Python:3.4.2测试-openpyxl:2.4.1-LibreOffice:4.3.3.2
OpenPyXL文档
Tested with Python:3.4.2 - openpyxl:2.4.1 - LibreOffice: 4.3.3.2
OpenPyXL Documentation
这篇关于如何使用Openpyxl获取当前行索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!