process xlsx with template

main.py

from openpyxl import Workbook, load_workbook
from openpyxl.styles.borders import Border, Side
import os
import re


def getData(filePath):
    wb = load_workbook(filename=filePath)
    sheet = wb.worksheets[0]
    row_count = sheet.max_row
    column_count = sheet.max_column
    data = []
    for row in range(7, row_count + 1):
        data.append([sheet.cell(row=row, column=1).value, sheet.cell(row=row, column=2).value, sheet.cell(
            row=row, column=3).value, sheet.cell(row=row, column=9).value])
    return data


def writeData(data, courseName, outputFilePath):
    wb = load_workbook(filename='template/模板.xlsx')
    sheet = wb.worksheets[0]
    firstColumnData = data[:30]
    secondColumnData = data[30:]
    sheet.cell(row=2, column=1).value = '考试科目：{courseName}'.format(
        courseName=courseName)
    sheet.cell(
        row=2, column=4).value = '考试时间：2019年1月7日' if '思想政治' in courseName else '考试时间：2019年1月8日'
    if(len(data) > 60):
        print("data is too large")
    for index, item in enumerate(firstColumnData):
        sheet.cell(row=5 + index, column=1).value = item[0]
        sheet.cell(row=5 + index, column=2).value = item[1]
        sheet.cell(row=5 + index, column=3).value = item[2]
        sheet.cell(row=5 + index, column=4).value = item[3]
    for index, item in enumerate(secondColumnData):
        sheet.cell(row=5 + index, column=5).value = item[0]
        sheet.cell(row=5 + index, column=6).value = item[1]
        sheet.cell(row=5 + index, column=7).value = item[2]
        sheet.cell(row=5 + index, column=8).value = item[3]

    wb.save(outputFilePath)


def getCourseName(fileName):
    match = re.match(r'.*课程：(.*?)(\(.*)', fileName)
    if(match):
        return match.group(1)
    print('course name for ${fileName} not found, return empty'.format(
        courseName=courseName))
    return ''


def adjustColumnAuto(worksheet, col):
    max_length = 0
    column = col[5].column
    for cell in col:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2) * 1.2
    worksheet.column_dimensions[column].width = adjusted_width


def adjustColumnForce(worksheet, col, adjusted_width):
    column = col[5].column
    worksheet.column_dimensions[column].width = adjusted_width


thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))


def process(filePath, outputFilePath):
    wb = load_workbook(filename=filePath)
    worksheet = wb.worksheets[0]
    # adjustColumnAuto(worksheet, worksheet['B'])
    adjustColumnForce(worksheet, worksheet['B'], 10)
    adjustColumnForce(worksheet, worksheet['C'], 8)
    adjustColumnForce(worksheet, worksheet['F'], 10)
    # worksheet.delete_cols(12)
    # set border
    row_count = worksheet.max_row
    column_count = worksheet.max_column
    data = []
    for row in range(6, row_count + 1):
        for column in range(1, column_count + 1):
            worksheet.cell(row=row, column=column).border = thin_border

    wb.save(outputFilePath)


if __name__ == "__main__":
    inputPath = 'data'
    outputPath = 'output'
    for file in os.listdir(inputPath):
        filePath = os.path.join(inputPath, file)
        outputFilePath = os.path.join(outputPath, file)
        print('process file {filePath}, output the result to {outputFilePath}'.format(
            filePath=filePath, outputFilePath=outputFilePath))
        # data = getData(filePath)
        # writeData(data, getCourseName(file), outputFilePath)
        try:
            process(filePath, outputFilePath)
        except:
            print("------------------")
            print("process {filePath} failed".format(filePath=filePath))
            print("------------------")