Python操作Excel插入刪除行

2024-06-15 02:33| 來源: 網(wǎng)絡(luò)整理| 查看: 265

Python作Excel插入刪除行 1. 前言2. 使用openpyxl3. 使用xlwings 3.1. 刪除行: range.api.EntireRow.Delete()3.2. 插入行: sheet.api.Rows(row_number).Insert() 1. 前言

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

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

在查找的過程中發(fā)現(xiàn)，網(wǎng)上流傳了一些使用openpyxl進(jìn)行插入刪除行的作，現(xiàn)整理一下。

2. 使用openpyxl

一種思路是將sheet數(shù)據(jù)轉(zhuǎn)換成list，然后在list進(jìn)行作，這種方法可行，但是實際測試之后發(fā)現(xiàn)運行起來速度太慢了，數(shù)據(jù)1000多條，時間就已經(jīng)等不起了。

# 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): # 把一個表格中的數(shù)據(jù)全部導(dǎo)出到一個列表 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 listResult def insertLine(aList, row_num , add_num, maxColumn): # 對列表進(jìn)行添加作作 for _ in range(1,add_num + 1): # ['']*N是創(chuàng)建一個個數(shù)為N的空格列表，插入列表aList aList.insert(row_num, [''] * maxColumn) def List2Sheet(sheet,list): # 把數(shù)據(jù)寫回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相關(guān)問題上面貼的，如果各位有興趣可以自己嘗試。

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\$?[A-Z]+)(?P\$?\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 3. 使用xlwings

進(jìn)行一些列嘗試和折騰之后，筆者放棄了使用openpyxl作Excel插入和刪除行了，到網(wǎng)上尋覓，發(fā)現(xiàn)了xlwings這個輪子，說明里寫有api能夠調(diào)用VBA的函數(shù)，這就很炫酷了，然后翻了翻文檔，決定使用這個輪子作，現(xiàn)貼出來筆者寫的幾段代碼作為使用方法示范。

3.1. 刪除行: range.api.EntireRow.Delete() # Delete origin row temp_del = 0 if len(delete_list) > 0: for delete_row in delete_list: # Report schedule print("Have alerady done: " + \ str((temp_del*100)//delete_num) + "%") # Delete one row wb_sheet.range('A'+str(delete_row-temp_del)).api.EntireRow.Delete() temp_del = temp_del + 1 wb.save()

上面這段代碼使用了一些小技巧，delete_list儲存的是原表格中，需要刪除的行號，在刪除過程中由于總行數(shù)也在跟著減少，所以需要把絕對行號轉(zhuǎn)成相對行號進(jìn)行標(biāo)記刪除，這個轉(zhuǎn)換就是temp_del變量的使用目的。

3.2. 插入行: sheet.api.Rows(row_number).Insert() if key_word == sheet.range('A'+str(i_row+1)).value: # Insert new line sheet.api.Rows(i_row+2).Insert()

需要注意的是，這個VBA函數(shù)是向上插入空行，并且xlwings這個輪子只能在windows和macos的系統(tǒng)下使用，暫時不支持Linux。不過xlwings運行速度要遠(yuǎn)超過openpyxl，而且還能直接調(diào)用VBA的函數(shù)，對于WPS和Excel都能兼容，綜合來看，還是選擇xlwings比較好一些。

【本文地址】

公司簡介

聯(lián)系我們

今日新聞

推薦新聞

專題文章