合并学校荣誉册原始数据

最新推荐文章于 2022-01-10 14:56:44 发布

DAo_1990

最新推荐文章于 2022-01-10 14:56:44 发布

阅读量218

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/DAo_1990/article/details/104803560

版权

python 专栏收录该内容

36 篇文章 3 订阅

订阅专栏

帮老婆合并学校荣誉册原始数据

#coding=utf-8
import xlrd
import xlwt
import os
import collections
from xlutils.copy import copy

def saveCSV(saveFileName,sheetIndex,rowValues,timeFormatCols):
    tem_excel=xlrd.open_workbook('origin_headers.xls',formatting_info=True)

    new_excel=copy(tem_excel)
    new_sheet=new_excel.get_sheet(sheetIndex)

    style=xlwt.XFStyle()
    font=xlwt.Font()
    font.name='宋体'
    font.bold=False
    font.height = 20 * 12  # 字体大小
    style.font=font

    borders=xlwt.Borders()
    borders.top=xlwt.Borders.THIN
    borders.bottom=xlwt.Borders.THIN
    borders.left=xlwt.Borders.THIN
    borders.right=xlwt.Borders.THIN
    style.borders=borders

    alignment=xlwt.Alignment()
    alignment.horz=xlwt.Alignment.HORZ_CENTER
    alignment.vert=xlwt.Alignment.VERT_CENTER
    style.alignment=alignment
    style.alignment.wrap = 1#自动换行

    index = 1
    startRow = 2
    if sheetIndex == 1:
        startRow = 3

    for row in range(startRow,len(rowValues)):
        if isinstance(rowValues[row][1], str):
            if rowValues[row][1].find("注：")>-1 or rowValues[row][1].find("说明：")>-1 or rowValues[row][1].strip(" ")=="":
                continue
        for cel in range(len(rowValues[0])):
            if cel == 0:
                new_sheet.write(row, cel, index,style)
                index += 1
            else:
                try:
                    if cel in timeFormatCols:
                        if isinstance(rowValues[row][cel],float):
                            style.num_format_str = 'YYYY/M/D'
                        # 设置列宽
                        new_sheet.col(cel).width = 256 * 15

                    new_sheet.write(row, cel, rowValues[row][cel],style)
                    style.num_format_str = 'General'
                except Exception as e:
                    print("saveCSV error: {}".format(str(e)))
                    pass

    new_excel.save(saveFileName)




def readExecl(fileName,sheetName,keyIndex):
    file = xlrd.open_workbook(fileName)

    # 输出Excel中表的个数
    print(file.nsheets)
    values = []
    # 读取某张表
    try:
        sheet = file.sheet_by_name(sheetName)
    except Exception as e:
        print("readExecl error: not found ,"+str(e))
        return values
    # 获取表的行数
    nrows = sheet.nrows
    # 获取表的列数
    ncols = sheet.ncols
    print("nrows: %d, ncols: %d ,sheetName:%s" % (nrows, ncols,sheetName))

    # 获取第一行的数据
    values=[]
    startRow =2
    if sheetName=="二、子课题获奖":
        startRow =3
    for i in range(startRow,nrows):
        row_value = sheet.row_values(i)
        print(row_value)
        if row_value[keyIndex]!="":
            values.append(row_value)
    return values

ROOTDIR="new_1_add/全区荣誉录汇总/data"

def main():
    sheetNameDict=  collections.OrderedDict()
    sheetNameDict["一、独立立项课题获奖"]=1
    sheetNameDict["二、子课题获奖"] = 1
    sheetNameDict["三、专著"] = 1
    sheetNameDict["四、教师公开发表的成果 "] = 1
    sheetNameDict["五、学术活动交流成果"] = 3
    sheetNameDict["六、教师论文获奖"]=1
    sheetNameDict["七、案例、课件、教案获奖"] = 1
    sheetNameDict["八、教师竞赛获奖情况"] = 1
    sheetNameDict["九、教师荣誉"] = 1
    sheetNameDict["十、单位集体荣誉"] = 3
    sheetNameDict["十一、学生作品发表情况"] = 1
    sheetNameDict["十二、学生习作获奖情况"] = 1
    sheetNameDict["十三、学生艺术获奖情况"] = 1
    sheetNameDict["十四、学生体育获奖情况"] = 1
    sheetNameDict["十五、其它学科学生竞赛获奖"] = 1
    sheetNameDict["十六、学生获得的荣誉称号"] = 1



    timeFormat=[[6,8],[11,12],[8],[6,7],[8],
                [7],[7],[7],[5],[6],[6],[9],[9],[9],[9],[5]]

    sheetIndex=0
    for sheetName,keyIndex in sheetNameDict.items():
        values=[]
        for file in os.listdir(ROOTDIR):
            file_name="{}/{}".format(ROOTDIR,file)
            print("scan {}".format(file_name))
            values.extend(readExecl(file_name,sheetName,keyIndex))
        timeFormatCols=timeFormat[sheetIndex]
        saveCSV("origin_headers.xls",sheetIndex,values,timeFormatCols)
        sheetIndex+=1


if __name__ == '__main__':
    main()