[Python知识库]Python Excel批处理

保存下面的代码为excelbatch.py：

from sys import argv
from openpyxl import load_workbook
from openpyxl.utils import column_index_from_string as str_to_int
from math import *

NO_ENOUGH_ARGS_ERROR = 1
MODE_ERROR = 2
NO_SUCH_FILE_ERROR = 3
NO_SUCH_SHEET_ERROR = 4
BATCH_CODE_RUN_ERROR = 5
BAD_CELL_NAME_ERROR = 6

if len(argv) < 6:
    print('Usage: %s [excel-filename] [sheet-name] [mode(range/single)] [batch-code] [*cells(single)]/<[start-cell] [end-cell](range)>' % argv[0])
    exit(NO_ENOUGH_ARGS_ERROR)

fp = argv[1]
sname = argv[2]
mode = argv[3]
if mode not in ['range', 'single']:
    print('Error: mode must be range/single')
    exit(MODE_ERROR)
batchcode = argv[4]
if mode == 'range':
    start, end = argv[5:]

try:
    wb = load_workbook(fp)
except:
    print('Error: No such excel file')
    exit(NO_SUCH_FILE_ERROR)

try:
    sheet = wb.get_sheet_by_name(sname)
except:
    print('Error: No such sheet')
    exit(NO_SUCH_SHEET_ERROR)

if mode == 'range':
    try:
        for row in sheet[start:end]:
            for col in row:
                try:
                    col.value = eval(batchcode.replace('cell', str(col.value)))
                except:
                    print('Error: Batch code run error')
                    exit(BATCH_CODE_RUN_ERROR)
    except:
        print('Error: Bad cell name')
        exit(BAD_CELL_NAME_ERROR)
else:
    try:
        for cell in argv[5:]:
            try:
                sheet[cell].value = eval(batchcode.replace('cell', str(sheet[cell].value)))
            except:
                print('Error: Batch code run error')
                exit(BATCH_CODE_RUN_ERROR)
    except:
        print('Error: Bad cell name')
        exit(BAD_CELL_NAME_ERROR)

wb.save(fp)

按照代码里的提示运行就好啦~