1. 前言

由于近期有任務需要，要寫一個能夠處理Excel的腳本，實現的功能是，在A表格上其中一列，對字符串進行分組和排序，然后根據排序好的A表格以固定格式自動填寫到B表格上。

開始寫腳本之前查了很多資料，最開始采用了openpyxl這個模塊，用起來很順手，使用這個對A表格其中一列進行了重新填寫，但是后來發現，需要用到刪除和插入空白行的操作，使用openpyxl比較困難，這個模塊僅支持在表格的最后一行繼續添加新行，不支持在中間插入和刪除行。

在查找的過程中發現，網上流傳了一些使用openpyxl進行插入刪除行的操作，現整理一下。

2. 使用openpyxl

一種思路是將sheet數據轉換成list，然后在list進行操作，這種方法可行，但是實際測試之后發現運行起來速度太慢了，數據1000多條，時間就已經等不起了。

# Creat insert row function group----------------------------------------------def blankRowInsert(sheet, row_num, add_num):  myList = Sheet2List(sheet)  insertLine(myList, row_num, add_num, sheet.max_column)  List2Sheet(sheet,myList)def Sheet2List(sheet):  # 把一個表格中的數據全部導出到一個列表  listResult = []  for i in range(1,sheet.max_row + 1):    lineData = []    for j in range(1,sheet.max_column +1):      cell = sheet.cell(row = i, column = j)      lineData.append(cell.value)    listResult.append(lineData)  return listResultdef insertLine(aList, row_num , add_num, maxColumn):  # 對列表進行添加操作操作  for _ in range(1,add_num + 1):    # ['']*N是創建一個個數為N的空格列表，插入列表aList    aList.insert(row_num, [''] * maxColumn)def List2Sheet(sheet,list):  # 把數據寫回sheet  for i in range(1, len(list) + 1):    for j in range(1, len(list[0]) + 1):      cell = sheet.cell(row=i, column=j)      cell.value = list[i-1][j-1]# End of insert row function group---------------------------------------------

另外一種思路是直接自己給openpyxl這個輪子補胎，添加一個新的方法，筆者沒有試驗，下面的代碼是StackOverflow相關問題上面貼的，如果各位有興趣可以自己嘗試。

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]+)(?P<row>/$?/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 > 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 > 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    )# Use way:# Worksheet.insert_rows = insert_rows