blocks|key|1909662|text|不幸的是，没有更好的方法来读取文件，并使用像xlwt这样的库来写出一个新的excel文件(在顶部插入新行)。Excel的工作方式与数据库不同，您可以对其进行读取和追加操作。不幸的是，您只需读取信息并在内存中进行操作，然后将其写出本质上是一个新文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1909663|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Unfortunately there isn't really a better way to do in that read in the file, and use a library like xlwt to write out a new excel file (with your new row inserted at the top). Excel doesn't work like a database that you can read and and append to. You unfortunately just have to read in the information and manipulate in memory and write out to what is essentially a new file.

blocks|key|1696443|text|用我现在用来达到预期结果的代码来回答这个问题。请注意，我是在位置1处手动插入行，但这应该足够容易，以便根据特定需要进行调整。您还可以轻松地调整它以插入多行，并简单地填充从相关位置开始的其余数据。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1696444|此外，请注意，由于下游依赖关系，我们将手动指定“Sheet1”中的数据，并将数据复制到在工作簿开头插入的新工作表中，同时将原始工作表重命名为“Sheet1.5”。|1696445|编辑:我还添加了(稍后)对format_code的更改，以修复此处的默认复制操作删除所有formatting：new_cell.style.number_format.format_code+=+'mm/dd/yyyy'的问题。我找不到任何文档表明这是可设置的，这更多的是一种反复试验的情况！|offset|length|style|CODE|1696446|最后，别忘了这个例子比原来的例子节省了时间。您可以在适用的情况下更改保存路径以避免这种情况。|1696447|++++import+openpyxl

++++wb+=+openpyxl.load_workbook(file)
++++old_sheet+=+wb.get_sheet_by_name('Sheet1')
++++old_sheet.title+=+'Sheet1.5'
++++max_row+=+old_sheet.get_highest_row()
++++max_col+=+old_sheet.get_highest_column()
++++wb.create_sheet(0,+'Sheet1')

++++new_sheet+=+wb.get_sheet_by_name('Sheet1')

++++#+Do+the+header.
++++for+col_num+in+range(0,+max_col):
++++++++new_sheet.cell(row=0,+column=col_num).value+=+old_sheet.cell(row=0,+column=col_num).value

++++#+The+row+to+be+inserted.+We're+manually+populating+each+cell.
++++new_sheet.cell(row=1,+column=0).value+=+'DUMMY'
++++new_sheet.cell(row=1,+column=1).value+=+'DUMMY'

++++#+Now+do+the+rest+of+it.+Note+the+row+offset.
++++for+row_num+in+range(1,+max_row):
++++++++for+col_num+in+range+(0,+max_col):
++++++++++++new_sheet.cell(row+=+(row_num+%2B+1),+column+=+col_num).value+=+old_sheet.cell(row+=+row_num,+column+=+col_num).value

++++wb.save(file)|code-block|syntax|javascript|1696448|entityMap^0|0|0|1J|1J|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@$F|V|G|W|H|I]]|9|@]|A|$]]|$1|J|3|K|5|6|7|X|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|Y|8|@]|9|@]|A|$O|P]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Answering this with the code that I'm now using to achieve the desired result. Note that I am manually inserting the row at position 1, but that should be easy enough to adjust for specific needs. You could also easily tweak this to insert more than one row, and simply populate the rest of the data starting at the relevant position.

Also, note that due to downstream dependencies, we are manually specifying data from 'Sheet1', and the data is getting copied to a new sheet which is inserted at the beginning of the workbook, whilst renaming the original worksheet to 'Sheet1.5'.

EDIT: I've also added (later on) a change to the format_code to fix issues where the default copy operation here removes all formatting: <code>new_cell.style.number_format.format_code = 'mm/dd/yyyy'</code>. I couldn't find any documentation that this was settable, it was more of a case of trial and error!

Lastly, don't forget this example is saving over the original. You can change the save path where applicable to avoid this.

<pre><code> import openpyxl

 wb = openpyxl.load_workbook(file)
 old_sheet = wb.get_sheet_by_name('Sheet1')
 old_sheet.title = 'Sheet1.5'
 max_row = old_sheet.get_highest_row()
 max_col = old_sheet.get_highest_column()
 wb.create_sheet(0, 'Sheet1')

 new_sheet = wb.get_sheet_by_name('Sheet1')

 # Do the header.
 for col_num in range(0, max_col):
 new_sheet.cell(row=0, column=col_num).value = old_sheet.cell(row=0, column=col_num).value

 # The row to be inserted. We're manually populating each cell.
 new_sheet.cell(row=1, column=0).value = 'DUMMY'
 new_sheet.cell(row=1, column=1).value = 'DUMMY'

 # Now do the rest of it. Note the row offset.
 for row_num in range(1, max_row):
 for col_num in range (0, max_col):
 new_sheet.cell(row = (row_num + 1), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

 wb.save(file)
</code></pre>

blocks|key|1910041|text|根据此处的反馈，==已更新为全功能版本:+groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw.==|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1910042|正如其他人所指出的，openpyxl不提供此功能，但我已经按如下方式扩展了Worksheet类以实现插入行。希望这篇文章能对其他人有用。|offset|length|style|CODE|1910043|def+insert_rows(self,+row_idx,+cnt,+above=False,+copy_style=True,+fill_formulae=True):
++++"""Inserts+new+(empty)+rows+into+worksheet+at+specified+row+index.

++++:param+row_idx:+Row+index+specifying+where+to+insert+new+rows.
++++:param+cnt:+Number+of+rows+to+insert.
++++:param+above:+Set+True+to+insert+rows+above+specified+row+index.
++++:param+copy_style:+Set+True+if+new+rows+should+copy+style+of+immediately+above+row.
++++:param+fill_formulae:+Set+True+if+new+rows+should+take+on+formula+from+immediately+above+row,+filled+with+references+new+to+rows.

++++Usage:

++++*+insert_rows(2,+10,+above=True,+copy_style=False)

++++"""
++++CELL_RE++=+re.compile("(?P<col>\$?[A-Z]%2B)(?P<row>\$?\d%2B)")

++++row_idx+=+row_idx+-+1+if+above+else+row_idx

++++def+replace(m):
++++++++row+=+m.group('row')
++++++++prefix+=+"$"+if+row.find("$")+!=+-1+else+""
++++++++row+=+int(row.replace("$",""))
++++++++row+%2B=+cnt+if+row+>+row_idx+else+0
++++++++return+m.group('col')+%2B+prefix+%2B+str(row)

++++#+First,+we+shift+all+cells+down+cnt+rows...
++++old_cells+=+set()
++++old_fas+++=+set()
++++new_cells+=+dict()
++++new_fas+++=+dict()
++++for+c+in+self._cells.values():

++++++++old_coor+=+c.coordinate

++++++++#+Shift+all+references+to+anything+below+row_idx
++++++++if+c.data_type+==+Cell.TYPE_FORMULA:
++++++++++++c.value+=+CELL_RE.sub(
++++++++++++++++replace,
++++++++++++++++c.value
++++++++++++)
++++++++++++#+Here,+we+need+to+properly+update+the+formula+references+to+reflect+new+row+indices
++++++++++++if+old_coor+in+self.formula_attributes+and+'ref'+in+self.formula_attributes[old_coor]:
++++++++++++++++self.formula_attributes[old_coor]['ref']+=+CELL_RE.sub(
++++++++++++++++++++replace,
++++++++++++++++++++self.formula_attributes[old_coor]['ref']
++++++++++++++++)

++++++++#+Do+the+magic+to+set+up+our+actual+shift++++
++++++++if+c.row+>+row_idx:
++++++++++++old_coor+=+c.coordinate
++++++++++++old_cells.add((c.row,c.col_idx))
++++++++++++c.row+%2B=+cnt
++++++++++++new_cells[(c.row,c.col_idx)]+=+c
++++++++++++if+old_coor+in+self.formula_attributes:
++++++++++++++++old_fas.add(old_coor)
++++++++++++++++fa+=+self.formula_attributes[old_coor].copy()
++++++++++++++++new_fas[c.coordinate]+=+fa

++++for+coor+in+old_cells:
++++++++del+self._cells[coor]
++++self._cells.update(new_cells)

++++for+fa+in+old_fas:
++++++++del+self.formula_attributes[fa]
++++self.formula_attributes.update(new_fas)

++++#+Next,+we+need+to+shift+all+the+Row+Dimensions+below+our+new+rows+down+by+cnt...
++++for+row+in+range(len(self.row_dimensions)-1%2Bcnt,row_idx%2Bcnt,-1):
++++++++new_rd+=+copy.copy(self.row_dimensions[row-cnt])
++++++++new_rd.index+=+row
++++++++self.row_dimensions[row]+=+new_rd
++++++++del+self.row_dimensions[row-cnt]

++++#+Now,+create+our+new+rows,+with+all+the+pretty+cells
++++row_idx+%2B=+1
++++for+row+in+range(row_idx,row_idx%2Bcnt):
++++++++#+Create+a+Row+Dimension+for+our+new+row
++++++++new_rd+=+copy.copy(self.row_dimensions[row-1])
++++++++new_rd.index+=+row
++++++++self.row_dimensions[row]+=+new_rd
++++++++for+col+in+range(1,self.max_column):
++++++++++++col+=+get_column_letter(col)
++++++++++++cell+=+self.cell('%25s%25d'%25(col,row))
++++++++++++cell.value+=+None
++++++++++++source+=+self.cell('%25s%25d'%25(col,row-1))
++++++++++++if+copy_style:
++++++++++++++++cell.number_format+=+source.number_format
++++++++++++++++cell.font++++++=+source.font.copy()
++++++++++++++++cell.alignment+=+source.alignment.copy()
++++++++++++++++cell.border++++=+source.border.copy()
++++++++++++++++cell.fill++++++=+source.fill.copy()
++++++++++++if+fill_formulae+and+source.data_type+==+Cell.TYPE_FORMULA:
++++++++++++++++s_coor+=+source.coordinate
++++++++++++++++if+s_coor+in+self.formula_attributes+and+'ref'+not+in+self.formula_attributes[s_coor]:
++++++++++++++++++++fa+=+self.formula_attributes[s_coor].copy()
++++++++++++++++++++self.formula_attributes[cell.coordinate]+=+fa
++++++++++++++++#+print("Copying+formula+from+cell+%25s%25d+to+%25s%25d"%25(col,row-1,col,row))
++++++++++++++++cell.value+=+re.sub(
++++++++++++++++++++"(\$?[A-Z]{1,3}\$?)%25d"%25(row+-+1),
++++++++++++++++++++lambda+m:+m.group(1)+%2B+str(row),
++++++++++++++++++++source.value
++++++++++++++++)+++
++++++++++++++++cell.data_type+=+Cell.TYPE_FORMULA

++++#+Check+for+Merged+Cell+Ranges+that+need+to+be+expanded+to+contain+new+cells
++++for+cr_idx,+cr+in+enumerate(self.merged_cell_ranges):
++++++++self.merged_cell_ranges[cr_idx]+=+CELL_RE.sub(
++++++++++++replace,
++++++++++++cr
++++++++)

Worksheet.insert_rows+=+insert_rows|code-block|syntax|javascript|1910044|entityMap^0|0|A|8|11|9|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]|$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

== Updated to a fully functional version, based on feedback here: groups.google.com/forum/#!topic/openpyxl-users/wHGecdQg3Iw. ==

As the others have pointed out, <code>openpyxl</code> does not provide this functionality, but I have extended the <code>Worksheet</code> class as follows to implement inserting rows. Hope this proves useful to others.

<pre><code>def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True):
 """Inserts new (empty) rows into worksheet at specified row index.

 :param row_idx: Row index specifying where to insert new rows.
 :param cnt: Number of rows to insert.
 :param above: Set True to insert rows above specified row index.
 :param copy_style: Set True if new rows should copy style of immediately above row.
 :param fill_formulae: Set True if new rows should take on formula from immediately above row, filled with references new to rows.

 Usage:

 * insert_rows(2, 10, above=True, copy_style=False)

 """
 CELL_RE = re.compile("(?P&lt;col&gt;\$?[A-Z]+)(?P&lt;row&gt;\$?\d+)")

 row_idx = row_idx - 1 if above else row_idx

 def replace(m):
 row = m.group('row')
 prefix = "$" if row.find("$") != -1 else ""
 row = int(row.replace("$",""))
 row += cnt if row &gt; row_idx else 0
 return m.group('col') + prefix + str(row)

 # First, we shift all cells down cnt rows...
 old_cells = set()
 old_fas = set()
 new_cells = dict()
 new_fas = dict()
 for c in self._cells.values():

 old_coor = c.coordinate

 # Shift all references to anything below row_idx
 if c.data_type == Cell.TYPE_FORMULA:
 c.value = CELL_RE.sub(
 replace,
 c.value
 )
 # Here, we need to properly update the formula references to reflect new row indices
 if old_coor in self.formula_attributes and 'ref' in self.formula_attributes[old_coor]:
 self.formula_attributes[old_coor]['ref'] = CELL_RE.sub(
 replace,
 self.formula_attributes[old_coor]['ref']
 )

 # Do the magic to set up our actual shift 
 if c.row &gt; row_idx:
 old_coor = c.coordinate
 old_cells.add((c.row,c.col_idx))
 c.row += cnt
 new_cells[(c.row,c.col_idx)] = c
 if old_coor in self.formula_attributes:
 old_fas.add(old_coor)
 fa = self.formula_attributes[old_coor].copy()
 new_fas[c.coordinate] = fa

 for coor in old_cells:
 del self._cells[coor]
 self._cells.update(new_cells)

 for fa in old_fas:
 del self.formula_attributes[fa]
 self.formula_attributes.update(new_fas)

 # Next, we need to shift all the Row Dimensions below our new rows down by cnt...
 for row in range(len(self.row_dimensions)-1+cnt,row_idx+cnt,-1):
 new_rd = copy.copy(self.row_dimensions[row-cnt])
 new_rd.index = row
 self.row_dimensions[row] = new_rd
 del self.row_dimensions[row-cnt]

 # Now, create our new rows, with all the pretty cells
 row_idx += 1
 for row in range(row_idx,row_idx+cnt):
 # Create a Row Dimension for our new row
 new_rd = copy.copy(self.row_dimensions[row-1])
 new_rd.index = row
 self.row_dimensions[row] = new_rd
 for col in range(1,self.max_column):
 col = get_column_letter(col)
 cell = self.cell('%s%d'%(col,row))
 cell.value = None
 source = self.cell('%s%d'%(col,row-1))
 if copy_style:
 cell.number_format = source.number_format
 cell.font = source.font.copy()
 cell.alignment = source.alignment.copy()
 cell.border = source.border.copy()
 cell.fill = source.fill.copy()
 if fill_formulae and source.data_type == Cell.TYPE_FORMULA:
 s_coor = source.coordinate
 if s_coor in self.formula_attributes and 'ref' not in self.formula_attributes[s_coor]:
 fa = self.formula_attributes[s_coor].copy()
 self.formula_attributes[cell.coordinate] = fa
 # print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
 cell.value = re.sub(
 "(\$?[A-Z]{1,3}\$?)%d"%(row - 1),
 lambda m: m.group(1) + str(row),
 source.value
 ) 
 cell.data_type = Cell.TYPE_FORMULA

 # Check for Merged Cell Ranges that need to be expanded to contain new cells
 for cr_idx, cr in enumerate(self.merged_cell_ranges):
 self.merged_cell_ranges[cr_idx] = CELL_RE.sub(
 replace,
 cr
 )

Worksheet.insert_rows = insert_rows
</code></pre>

blocks|key|1696485|text|我采用了Dallas解决方案，并添加了对合并单元的支持：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1696486|++++def+insert_rows(self,+row_idx,+cnt,+above=False,+copy_style=True,+fill_formulae=True):
++++++++skip_list+=+[]
++++++++try:
++++++++++++idx+=+row_idx+-+1+if+above+else+row_idx
++++++++++++for+(new,+old)+in+zip(range(self.max_row%2Bcnt,idx%2Bcnt,-1),range(self.max_row,idx,-1)):
++++++++++++++++for+c_idx+in+range(1,self.max_column):
++++++++++++++++++col+=+self.cell(row=1,+column=c_idx).column+#get_column_letter(c_idx)
++++++++++++++++++print("Copying+%25s%25d+to+%25s%25d."%25(col,old,col,new))
++++++++++++++++++source+=+self["%25s%25d"%25(col,old)]
++++++++++++++++++target+=+self["%25s%25d"%25(col,new)]
++++++++++++++++++if+source.coordinate+in+skip_list:
++++++++++++++++++++++continue

++++++++++++++++++if+source.coordinate+in+self.merged_cells:
++++++++++++++++++++++#+This+is+a+merged+cell
++++++++++++++++++++++for+_range+in+self.merged_cell_ranges:
++++++++++++++++++++++++++merged_cells_list+=+[x+for+x+in+cells_from_range(_range)][0]
++++++++++++++++++++++++++if+source.coordinate+in+merged_cells_list:
++++++++++++++++++++++++++++++skip_list+=+merged_cells_list
++++++++++++++++++++++++++++++self.unmerge_cells(_range)
++++++++++++++++++++++++++++++new_range+=+re.sub(str(old),str(new),_range)
++++++++++++++++++++++++++++++self.merge_cells(new_range)
++++++++++++++++++++++++++++++break

++++++++++++++++++if+source.data_type+==+Cell.TYPE_FORMULA:
++++++++++++++++++++target.value+=+re.sub(
++++++++++++++++++++++"(\$?[A-Z]{1,3})%25d"%25(old),
++++++++++++++++++++++lambda+m:+m.group(1)+%2B+str(new),
++++++++++++++++++++++source.value
++++++++++++++++++++)
++++++++++++++++++else:
++++++++++++++++++++target.value+=+source.value
++++++++++++++++++target.number_format+=+source.number_format
++++++++++++++++++target.font+++=+source.font.copy()
++++++++++++++++++target.alignment+=+source.alignment.copy()
++++++++++++++++++target.border+=+source.border.copy()
++++++++++++++++++target.fill+++=+source.fill.copy()
++++++++++++idx+=+idx+%2B+1
++++++++++++for+row+in+range(idx,idx%2Bcnt):
++++++++++++++++for+c_idx+in+range(1,self.max_column):
++++++++++++++++++col+=+self.cell(row=1,+column=c_idx).column+#get_column_letter(c_idx)
++++++++++++++++++#print("Clearing+value+in+cell+%25s%25d"%25(col,row))
++++++++++++++++++cell+=+self["%25s%25d"%25(col,row)]
++++++++++++++++++cell.value+=+None
++++++++++++++++++source+=+self["%25s%25d"%25(col,row-1)]
++++++++++++++++++if+copy_style:
++++++++++++++++++++cell.number_format+=+source.number_format
++++++++++++++++++++cell.font++++++=+source.font.copy()
++++++++++++++++++++cell.alignment+=+source.alignment.copy()
++++++++++++++++++++cell.border++++=+source.border.copy()
++++++++++++++++++++cell.fill++++++=+source.fill.copy()
++++++++++++++++++if+fill_formulae+and+source.data_type+==+Cell.TYPE_FORMULA:
++++++++++++++++++++#print("Copying+formula+from+cell+%25s%25d+to+%25s%25d"%25(col,row-1,col,row))
++++++++++++++++++++cell.value+=+re.sub(
++++++++++++++++++++++"(\$?[A-Z]{1,3})%25d"%25(row+-+1),
++++++++++++++++++++++lambda+m:+m.group(1)+%2B+str(row),
++++++++++++++++++++++source.value
++++++++++++++++++++)|code-block|syntax|javascript|1696487|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I took Dallas solution and added support for merged cells:

<pre><code> def insert_rows(self, row_idx, cnt, above=False, copy_style=True, fill_formulae=True):
 skip_list = []
 try:
 idx = row_idx - 1 if above else row_idx
 for (new, old) in zip(range(self.max_row+cnt,idx+cnt,-1),range(self.max_row,idx,-1)):
 for c_idx in range(1,self.max_column):
 col = self.cell(row=1, column=c_idx).column #get_column_letter(c_idx)
 print("Copying %s%d to %s%d."%(col,old,col,new))
 source = self["%s%d"%(col,old)]
 target = self["%s%d"%(col,new)]
 if source.coordinate in skip_list:
 continue

 if source.coordinate in self.merged_cells:
 # This is a merged cell
 for _range in self.merged_cell_ranges:
 merged_cells_list = [x for x in cells_from_range(_range)][0]
 if source.coordinate in merged_cells_list:
 skip_list = merged_cells_list
 self.unmerge_cells(_range)
 new_range = re.sub(str(old),str(new),_range)
 self.merge_cells(new_range)
 break

 if source.data_type == Cell.TYPE_FORMULA:
 target.value = re.sub(
 "(\$?[A-Z]{1,3})%d"%(old),
 lambda m: m.group(1) + str(new),
 source.value
 )
 else:
 target.value = source.value
 target.number_format = source.number_format
 target.font = source.font.copy()
 target.alignment = source.alignment.copy()
 target.border = source.border.copy()
 target.fill = source.fill.copy()
 idx = idx + 1
 for row in range(idx,idx+cnt):
 for c_idx in range(1,self.max_column):
 col = self.cell(row=1, column=c_idx).column #get_column_letter(c_idx)
 #print("Clearing value in cell %s%d"%(col,row))
 cell = self["%s%d"%(col,row)]
 cell.value = None
 source = self["%s%d"%(col,row-1)]
 if copy_style:
 cell.number_format = source.number_format
 cell.font = source.font.copy()
 cell.alignment = source.alignment.copy()
 cell.border = source.border.copy()
 cell.fill = source.fill.copy()
 if fill_formulae and source.data_type == Cell.TYPE_FORMULA:
 #print("Copying formula from cell %s%d to %s%d"%(col,row-1,col,row))
 cell.value = re.sub(
 "(\$?[A-Z]{1,3})%d"%(row - 1),
 lambda m: m.group(1) + str(row),
 source.value
 )
</code></pre>

blocks|key|1910079|text|编辑Nick的解决方案时，此版本接受开始行、要插入的行数和文件名，并插入必要数量的空行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1910080|#!+python+3

import+openpyxl,+sys

my_start+=+int(sys.argv[1])
my_rows+=+int(sys.argv[2])
str_wb+=+str(sys.argv[3])

wb+=+openpyxl.load_workbook(str_wb)
old_sheet+=+wb.get_sheet_by_name('Sheet')
mcol+=+old_sheet.max_column
mrow+=+old_sheet.max_row
old_sheet.title+=+'Sheet1.5'
wb.create_sheet(index=0,+title='Sheet')

new_sheet+=+wb.get_sheet_by_name('Sheet')

for+row_num+in+range(1,+my_start):
++++for+col_num+in+range(1,+mcol+%2B+1):
++++++++new_sheet.cell(row+=+row_num,+column+=+col_num).value+=+old_sheet.cell(row+=+row_num,+column+=+col_num).value

for+row_num+in+range(my_start+%2B+my_rows,+mrow+%2B+my_rows):
++++for+col_num+in+range(1,+mcol+%2B+1):
++++++++new_sheet.cell(row+=+(row_num+%2B+my_rows),+column+=+col_num).value+=+old_sheet.cell(row+=+row_num,+column+=+col_num).value

wb.save(str_wb)|code-block|syntax|javascript|1910081|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Edited Nick's solution, this version takes a starting row, the number of rows to insert, and a filename, and inserts the necessary number of blank rows.

<pre><code>#! python 3

import openpyxl, sys

my_start = int(sys.argv[1])
my_rows = int(sys.argv[2])
str_wb = str(sys.argv[3])

wb = openpyxl.load_workbook(str_wb)
old_sheet = wb.get_sheet_by_name('Sheet')
mcol = old_sheet.max_column
mrow = old_sheet.max_row
old_sheet.title = 'Sheet1.5'
wb.create_sheet(index=0, title='Sheet')

new_sheet = wb.get_sheet_by_name('Sheet')

for row_num in range(1, my_start):
 for col_num in range(1, mcol + 1):
 new_sheet.cell(row = row_num, column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

for row_num in range(my_start + my_rows, mrow + my_rows):
 for col_num in range(1, mcol + 1):
 new_sheet.cell(row = (row_num + my_rows), column = col_num).value = old_sheet.cell(row = row_num, column = col_num).value

wb.save(str_wb)
</code></pre>

blocks|key|1910154|text|这对我很有效：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1910155|++++openpyxl.worksheet.worksheet.Worksheet.insert_rows(wbs,idx=row,amount=2)|code-block|syntax|javascript|1910156|在row==idx前插入2行|1910157|请参阅：http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html|offset|length|1910158|entityMap|0|LINK|mutability|MUTABLE|url|http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html^0|0|0|0|4|26|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

This worked for me:

<pre><code> openpyxl.worksheet.worksheet.Worksheet.insert_rows(wbs,idx=row,amount=2)
</code></pre>

Insert 2 rows before row==idx

See: <a href="http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html" rel="nofollow noreferrer">http://openpyxl.readthedocs.io/en/stable/api/openpyxl.worksheet.worksheet.html</a>

blocks|key|1910342|text|从openpyxl1.5开始，你现在可以使用.insert_rows(idx，row_qty)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1910343|from+openpyxl+import+load_workbook
wb+=+load_workbook('excel_template.xlsx')
ws+=+wb.active
ws.insert_rows(14,+10)|code-block|syntax|javascript|1910344|它不会像在Excel中手动拾取idx行的格式那样拾取idx行的格式。之后，您将应用正确的格式，即单元格颜色。|1910345|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

As of openpyxl 1.5 you can now use .insert_rows(idx, row_qty)

<pre><code>from openpyxl import load_workbook
wb = load_workbook('excel_template.xlsx')
ws = wb.active
ws.insert_rows(14, 10)
</code></pre>

It will not pick up the formatting of the idx row as it would if you did this manually in Excel. you will have apply the correct formatting i.e. cell color afterwards.

blocks|key|1910387|text|在Python中使用openpyxl将行插入Excel电子表格|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1910388|下面的代码可以帮助你：|1910389|import+openpyxl

file+=+"xyz.xlsx"
#loading+XL+sheet+bassed+on+file+name+provided+by+user
book+=+openpyxl.load_workbook(file)
#opening+sheet+whose+index+no+is+0
sheet+=+book.worksheets[0]

#insert_rows(idx,+amount=1)+Insert+row+or+rows+before+row==idx,+amount+will+be+no+of+
#rows+you+want+to+add+and+it's+optional
sheet.insert_rows(13)|code-block|syntax|javascript|1910390|对于插入列，amount=1也有类似的函数i.e.insert_cols(idx，openpyxl+)|1910391|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

To insert row into Excel spreadsheet using openpyxl in Python

Below code can help you :-

<pre><code>import openpyxl

file = "xyz.xlsx"
#loading XL sheet bassed on file name provided by user
book = openpyxl.load_workbook(file)
#opening sheet whose index no is 0
sheet = book.worksheets[0]

#insert_rows(idx, amount=1) Insert row or rows before row==idx, amount will be no of 
#rows you want to add and it's optional
sheet.insert_rows(13)
</code></pre>

For inserting column also openpyxl have similar function i.e.insert_cols(idx, amount=1)

blocks|key|1696916|text|我已经写了一个函数，它可以用openpyxl在电子表格中的任意位置插入整行，或者插入整个二维表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1696917|函数的每一行都使用注释进行解释，但如果您只想插入一行，只需使您的行等于row即可。例如，如果行=+1,2,3,4,5，则将输入设置为[1,2,3,4,5]。如果希望将此行插入到电子表格(A1)的顶行，则Start+=+1,1。|1696918|您确实可以覆盖文件名，就像在底部的示例中所看到的那样。|1696919|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+%2B+1+#Move+to+next+data+input+(List)+column
++++++++++++Start[1]+=+Start[1]+%2B+1+#Move+to+next+Excel+column
++++++++++++l+=+l+%2B+1+#Count+addional+row+length
++++++++y+=+y+%2B+1+#Move+to+next+Excel+row
++++++++Start[0]+=+Start[0]+%2B+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|code-block|syntax|javascript|1696920|在第7行开始处插入单行的示例：|1696921|from+openpyxl+import+load_workbook
wb+=+load_workbook('New3.xlsx')
ws+=+wb.active

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+%2B+1+#Move+to+next+data+input+(List)+column
++++++++++++Start[1]+=+Start[1]+%2B+1+#Move+to+next+Excel+column
++++++++++++l+=+l+%2B+1+#Count+addional+row+length
++++++++y+=+y+%2B+1+#Move+to+next+Excel+row
++++++++Start[0]+=+Start[0]+%2B+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

test+=+[[1,2,3,4]]
InputList([7,1],+test)

wb.save('New3.xlsx')|1696922|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|T|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

I've written a function which will both insert an entire row anywhere you want in a spreadsheet, or an entire 2D table, with openpyxl.

Every row of the function is explained with a comment but if you want to just insert a single row, just make your row equal to [row]. i.e. if row = [1,2,3,4,5] then set your input to [[1,2,3,4,5]]. If you want this row to be inserted into the top row of your spreadsheet (A1) then Start = [1,1].

You can indeed overwrite the file name as see you can with my example at the bottom.

<pre><code>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
</code></pre>

Example with single row being inserted at start of row 7:

<pre><code>from openpyxl import load_workbook
wb = load_workbook('New3.xlsx')
ws = wb.active

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

test = [[1,2,3,4]]
InputList([7,1], test)

wb.save('New3.xlsx')
</code></pre>

I'm looking for the best approach for inserting a row into a spreadsheet using openpyxl.

Effectively, I have a spreadsheet (Excel 2007) which has a header row, followed by (at most) a few thousand rows of data. I'm looking to insert the row as the first row of actual data, so after the header. My understanding is that the append function is suitable for adding content to the end of the file.

Reading the documentation for both openpyxl and xlrd (and xlwt), I can't find any clear cut ways of doing this, beyond looping through the content manually and inserting into a new sheet (after inserting the required row). 

Given my so far limited experience with Python, I'm trying to understand if this is indeed the best option to take (the most pythonic!), and if so could someone provide an explicit example. Specifically can I read and write rows with openpyxl or do I have to access cells? Additionally can I (over)write the same file(name)?

Insert row into Excel spreadsheet using openpyxl in Python

数据库

Python

我正在寻找使用openpyxl在电子表格中插入行的最佳方法。实际上，我有一个电子表格(Excel2007)，它有一个标题行，后面跟着(最多)几千行数据。我希望插入行作为实际数据的第一行，所以在标题之后。我的理解是append函数适用于将内容添加到文件的末尾。阅读openpyxl和xlrd (以及xlwt)的文档时，除了...

问在Python中使用openpyxl在Excel电子表格中插入行
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Python中使用openpyxl在Excel电子表格中插入行EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Python中使用openpyxl在Excel电子表格中插入行
EN