我编写了一个函数,可以使用openpyxl在电子表格中任意位置插入整个行或整个二维表。
该函数的每一行都有注释,但如果您只想插入一行,请将您的行设置为[row]。例如,如果row = [1,2,3,4,5],则将输入设置为[[1,2,3,4,5]]。如果您希望将此行插入到电子表格的顶行(A1),则Start = [1,1]。
您确实可以像我在底部的示例中看到的那样覆盖文件名。
def InputList(Start, List): #This function is to input an array/list from a input start point; len(Start) must equal 2, where Start = [1,1] is cell 1A. List must be a two dimensional array; if you wish to input a single row then this can be done where len(List) == 1, e.g. List = [[1,2,3,4]]
x = 0 #Sets up a veriable to go through List columns
y = 0 #Sets up a veriable to go through List rows
l = 0 #Sets up a veriable to count addional columns against Start[1] to allow for column reset on each new row
for row in List: #For every row in List
l = 0 #Set additonal columns to zero
for cell in row: #For every cell in row
ws.cell(row=Start[0], column=Start[1]).value = List[y][x] #Set value for current cell
x = x + 1 #Move to next data input (List) column
Start[1] = Start[1] + 1 #Move to next Excel column
l = l + 1 #Count addional row length
y = y + 1 #Move to next Excel row
Start[0] = Start[0] + 1 #Move to next Excel row
x = 0 #Move back to first column of input data (ready for next row)
Start[1] = Start[1] - l #Reset Excel column back to orignal start column, ready to write next row
在第7行的开头插入单个行的示例:
from openpyxl import load_workbook
wb = load_workbook('New3.xlsx')
ws = wb.active
def InputList(Start, List):
x = 0
y = 0
l = 0
for row in List:
l = 0
for cell in row:
ws.cell(row=Start[0], column=Start[1]).value = List[y][x]
x = x + 1
Start[1] = Start[1] + 1
l = l + 1
y = y + 1
Start[0] = Start[0] + 1
x = 0
Start[1] = Start[1] - l
test = [[1,2,3,4]]
InputList([7,1], test)
wb.save('New3.xlsx')