更新于:2020-04-25T00:00:00Z

0x00 概述

小l接到任务需要在几万条记录xlsx格式的excel文件中按照规则插入点空白行，手动操作太累了吧，试试python。

0x01 上代码

#coding=utf-8

from openpyxl import Workbook
from openpyxl import load_workbook


wb = load_workbook("ll.xlsx")
wb_new = Workbook()
sheet_new = wb_new.active
sheet_new.title = 'sheet1'
sheet_new['A1'] = 'A9'
sheet_new['B1'] = 'A10'
sheet_new['C1'] = 'A11'
sheet_new['D1'] = 'A12'
sheet_new['E1'] = 'A13'
sheet_new['F1'] = 'A14'
sheet_new['G1'] = 'A15'
sheet_new['H1'] = 'A16'
sheet_new['I1'] = 'A17'
sheet_new['J1'] = 'A18'
sheet_new['K1'] = 'A19'
sheet_new['L1'] = 'A20'
sheet_new['M1'] = 'A21'
sheet_new['N1'] = 'A22'
sheet_new['O1'] = 'A23'
sheet_new['P1'] = 'A24'
my_sheet = wb.active
_first_min_flag = False
_row = 1
for row in my_sheet.rows:
    if str(row[0].value) != '1':
        _first_min_flag = False
        sheet_new['A{}'.format(_row)] = row[0].value
        sheet_new['B{}'.format(_row)] = row[1].value
        sheet_new['C{}'.format(_row)] = row[2].value
        sheet_new['D{}'.format(_row)] = row[3].value
        sheet_new['E{}'.format(_row)] = row[4].value
        sheet_new['F{}'.format(_row)] = row[5].value
        sheet_new['G{}'.format(_row)] = row[6].value
        sheet_new['H{}'.format(_row)] = row[7].value
        sheet_new['I{}'.format(_row)] = row[8].value
        sheet_new['J{}'.format(_row)] = row[9].value
        sheet_new['K{}'.format(_row)] = row[10].value
        sheet_new['L{}'.format(_row)] = row[11].value
        sheet_new['M{}'.format(_row)] = row[12].value
        sheet_new['N{}'.format(_row)] = row[13].value
        sheet_new['O{}'.format(_row)] = row[14].value
        sheet_new['P{}'.format(_row)] = row[15].value
    else:
        if row[10].value == 'min' and _first_min_flag is False:
            _first_min_flag = True
            sheet_new['A{}'.format(_row)] = ''
            sheet_new['B{}'.format(_row)] = ''
            sheet_new['C{}'.format(_row)] = ''
            sheet_new['D{}'.format(_row)] = ''
            sheet_new['E{}'.format(_row)] = ''
            sheet_new['F{}'.format(_row)] = ''
            sheet_new['G{}'.format(_row)] = ''
            sheet_new['H{}'.format(_row)] = ''
            sheet_new['I{}'.format(_row)] = ''
            sheet_new['J{}'.format(_row)] = ''
            sheet_new['K{}'.format(_row)] = ''
            _row += 1
            sheet_new['A{}'.format(_row)] = ''
            sheet_new['B{}'.format(_row)] = ''
            sheet_new['C{}'.format(_row)] = ''
            sheet_new['D{}'.format(_row)] = ''
            sheet_new['E{}'.format(_row)] = ''
            sheet_new['F{}'.format(_row)] = ''
            sheet_new['G{}'.format(_row)] = ''
            sheet_new['H{}'.format(_row)] = ''
            sheet_new['I{}'.format(_row)] = ''
            sheet_new['J{}'.format(_row)] = ''
            sheet_new['K{}'.format(_row)] = ''
            _row += 1
        sheet_new['A{}'.format(_row)] = row[0].value
        sheet_new['B{}'.format(_row)] = row[1].value
        sheet_new['C{}'.format(_row)] = row[2].value
        sheet_new['D{}'.format(_row)] = row[3].value
        sheet_new['E{}'.format(_row)] = row[4].value
        sheet_new['F{}'.format(_row)] = row[5].value
        sheet_new['G{}'.format(_row)] = row[6].value
        sheet_new['H{}'.format(_row)] = row[7].value
        sheet_new['I{}'.format(_row)] = row[8].value
        sheet_new['J{}'.format(_row)] = row[9].value
        sheet_new['K{}'.format(_row)] = row[10].value
        sheet_new['L{}'.format(_row)] = row[11].value
        sheet_new['M{}'.format(_row)] = row[12].value
        sheet_new['N{}'.format(_row)] = row[13].value
        sheet_new['O{}'.format(_row)] = row[14].value
        sheet_new['P{}'.format(_row)] = row[15].value
    _row += 1


wb_new.save('ll_new.xlsx')

0xff 完结撒花

没啥技术含量，最起码我这种笨方法比人工操作excel强点吧，无需激活码。

转载请注明：【Python 使用openpyxl读写Excel的xls或xlsx文件】https://jihuo.ma/python-openpyxl.html