Python操作Excel插入删除行的方法

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 listResult

def 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

3. 使用xlwings

进行一些列尝试和折腾之后,笔者放弃了使用openpyxl操作Excel插入和删除行了,到网上寻觅,发现了xlwings这个轮子,说明里写有api能够调用VBA的函数,这就很炫酷了,然后翻了翻文档,决定使用这个轮子操作,现贴出来笔者写的几段代码作为使用方法示范。

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储存的是原表格中,需要删除的行号,在删除过程中由于总行数也在跟着减少,所以需要把绝对行号转成相对行号进行标记删除,这个转换就是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函数是向上插入空行,并且xlwings这个轮子只能在windows和macos的系统下使用,暂时不支持Linux。不过xlwings运行速度要远超过openpyxl,而且还能直接调用VBA的函数,对于WPS和Excel都能兼容,综合来看,还是选择xlwings比较好一些。

以上这篇Python操作Excel插入删除行的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们。

时间: 2018-12-09

Python输出PowerPoint(ppt)文件中全部文字信息的方法

本文实例讲述了Python输出PowerPoint(ppt)文件中全部文字信息的方法.分享给大家供大家参考.具体分析如下: 下面的代码依赖于windows com,所以必须在机器上安装PowerPoint才能用,可以将PPT文件中的所有纯文本信息进行输出到指定的文件,非常实用. import win32com from win32com.client import Dispatch, constants ppt = win32com.client.Dispatch('PowerPoint.App

解决python xlrd无法读取excel文件的问题

读取文件时报错: xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found '<?xml' 该xls文件在打开时确实会警告该文件与扩展名格式不一致.用文本编辑器打开该xls文件查看,发现确实不是xls文件,而是xml文件被保存为了xls文件. 解决办法:将文件后缀名改为.xml,作为xml文件读入. 改名代码: import os os.rename(u"D:\PycharmP

python使用folium库绘制地图点击框

python使用folium 库生成地图网页的具体代码,供大家参考,具体内容如下 folium 官网 import folium import pandas as pd def mark_map(data): """ 带有标注的地图 :param data: :return: """ # 地图制作 myMap = folium.Map(location=[20, 0], tiles="Mapbox Bright", zoom_

浅谈python 读excel数值为浮点型的问题

如下所示: #读入no data = xlrd.open_workbook("no.xlsx") #打开excel table = data.sheet_by_name("Sheet1") #读sheet nrows = table.nrows cols = table.ncols nos = [] for i in range(1,nrows): #指定从1开始,到最后一列,跳过表头 for j in range(cols): ctype = table.cell

Python使用folium excel绘制point

使用folium excel 绘制point 制作内容 根据气象台资料获得的点进行绘制 对一个特殊的点做特别的标注 数据来源 #!/usr/bin/env python # -*- coding: utf-8 -*- # @File : map03.py # @Author: huifer # @Date : 2018/6/28 import pandas as pd import math import folium def degree_conversion_decimal(x): "&qu

使用python批量读取word文档并整理关键信息到excel表格的实例

目标 最近实验室里成立了一个计算机兴趣小组 倡议大家多把自己解决问题的经验记录并分享 就像在CSDN写博客一样 虽然刚刚起步 但考虑到后面此类经验记录的资料会越来越多 所以一开始就要做好模板设计(如下所示) 方便后面建立电子数据库 从而使得其他人可以迅速地搜索到相关记录 据说"人生苦短,我用python" 所以决定用python从docx文档中提取文件头的信息 然后把信息更新到一个xls电子表格中,像下面这样(直接po结果好了) 而且点击文件路径可以直接打开对应的文件(含超链接) 代码

Python从Excel中读取日期一列的方法

如下所示: import xlrd import datetime file=u"伏特加.xls"#注意读中文文件名稍微处理一下 data=xlrd.open_workbook(file) table = data.sheet_by_index(0)#按照索引读Excel文件 colContent=table.col_values(1)#读某一列,日期在第二列 nrows=table.nrows #行数 print nrows ncols = table.ncols#列数 print

python得到一个excel的全部sheet标签值方法

这里需要用到python处理excel很经典的库openpyxl,安装也特别简单.window直接pip install就好了 代码在这里~ wb = openpyxl.load_workbook(filepath) sheetnames = wb.sheetnames 以上这篇python得到一个excel的全部sheet标签值方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持我们.

Python对excel文档的操作方法详解

本文实例讲述了Python对excel文档的操作方法.分享给大家供大家参考,具体如下: pip安装python库:(linux命令行输入不要在idle输入) pip install xlrd pip install xlwd 1.获取excel中的数据 利用xlrd模块读取并简单操作excel文档: 1.打开excel文档 import xlrd workbook = xlrd.open_workbook("test_case.xlsx") 2.获取所有的sheet表 sheet_na

python获得linux下所有挂载点(mount points)的方法

本文实例讲述了python获得linux下所有挂载点(mount points)的方法.分享给大家供大家参考.具体实现方法如下: # execute the external "mount" command # and parse the output. import commands mount = commands.getoutput('mount -v') lines = mount.split('\n') points = map(lambda line: line.split

python获取Linux下文件版本信息、公司名和产品名的方法

本文实例讲述了python获取Linux下文件版本信息.公司名和产品名的方法,分享给大家供大家参考.具体如下: 区别于前文所述.本例是在linux下得到文件版本信息,主要是通过pefile模块解析文件 中的字符串得到的.代码如下: def _get_company_and_product(self, file_path): """ Read all properties of the given file return them as a dictionary. @retur

python在windows和linux下获得本机本地ip地址方法小结

本文实例总结了python在windows和linux下获得本机本地ip地址方法.分享给大家供大家参考.具体分析如下: python的socket包含了丰富的函数和方法可以获得本机的ip地址信息,socket对象的gethostbyname方法可以根据主机名获得本机ip地址,socket对象的gethostbyname_ex方法可以获得本机所有ip地址列表 第一种方法:通过socket.gethostbyname方法获得 import socket localIP = socket.gethos

Python实现Linux下守护进程的编写方法

本文实例讲述了Python实现Linux下守护进程的编写方法,分享给大家供大家参考,相信对于大家的Python程序设计会起到一定的帮助作用.具体方法如下: 1. 调用fork()以便父进程可以退出,这样就将控制权归还给运行你程序的命令行或shell程序.需要这一步以便保证新进程不是一个进程组头领进程(process group leader).下一步,'setsid()',会因为你是进程组头领进程而失败.进程调用fork函数时,操作系统会新建一个子进程,它本质上与父进程完全相同.子进程从父进程继

python实现linux下使用xcopy的方法

本文实例讲述了python实现linux下使用xcopy的方法.分享给大家供大家参考.具体如下: 这个python函数模仿windows下的xcopy命令编写,可以用在linux下 #!/usr/bin/python # -*- coding: UTF-8 -*- """ xcopy for Linux... Use: ______________________________________________________________________________

Linux下修改Oracle监听地址的方法

lisenter.ora 目录在 /opt/oracle/11g/network/admin LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.111.123)(PORT = 1521)) ) ) ADR_BASE_LISTENER = /opt/oracle tnsnames.o

Linux下对各种压缩文件的处理方法

Linux下最常用的打包程序就是tar了,使用tar程序打出来的包我们常称为tar包,tar包文件的命令通常都是以.tar结尾的.生成tar包后,就可以用其它的程序来进行压缩了,所以首先就来讲讲tar命令的基本用法: tar命令的选项有很多(用man tar可以查看到),但常用的就那么几个选项,下面 来举例说明一下: # tar -cf all.tar *.jpg 这条命令是将所有.jpg的文件打成一个名为all.tar的包.-c是表示产生新的包,-f指定包的文件名. # tar -rf all

Linux下安装Tomcat7.0.82的简单方法

1.apache-tomcat-liunx-7.0.82下载地址: http://download.csdn.net/download/yichen01010/10019116 2.下载后解压即可 cd /root/Downloads cp apache-tomcat-7.0.82.tar.gz /usr/app cd /usr/app tar -zvxf apache-tomcat-7.0.82.tar.gz 总结 以上所述是小编给大家介绍的Linux下安装Tomcat7.0.82的简单方法,

Linux下实现定时器Timer的几种方法总结

定时器Timer应用场景非常广泛,在Linux下,有以下几种方法: 1,使用sleep()和usleep() 其中sleep精度是1秒,usleep精度是1微妙,具体代码就不写了.使用这种方法缺点比较明显,在Linux系统中,sleep类函数不能保证精度,尤其在系统负载比较大时,sleep一般都会有超时现象. 2,使用信号量SIGALRM + alarm() 这种方式的精度能达到1秒,其中利用了*nix系统的信号量机制,首先注册信号量SIGALRM处理函数,调用alarm(),设置定时长度,代码

linux下查看系统进程占用的句柄数方法

---查看系统默认的最大文件句柄数,系统默认是1024 # ulimit -n 1024 ----查看当前进程打开了多少句柄数 # lsof -n|awk '{print $2}'|sort|uniq -c|sort -nr|more 131 24204 57 24244 57 24231 ........ 其中第一列是打开的句柄数,第二列是进程ID. 可以根据ID号来查看进程名. # ps aef|grep 24204 nginx 24204 24162 99 16:15 ? 00:24:25