Python 操作 Excel

以下代码由PHP站长网 52php.cn收集自互联网

现在PHP站长网小编把它分享给大家，仅供参考

import sys
import xlsxwriter
import prep
import os
import leave
import log

reload(sys)
sys.setdefaultencoding("utf-8")


def  fill_excel( worksheet,cell,content):
    worksheet.write(cell,content)


def get_data( catagory='calc_original_sample' ):

    days = prep.get_days(8)
    z = []
    for day in days:
        if catagory == 'calc_original_sample':
            data = prep.calc_original_sample(day)
        else:
            data = prep.cal_der_sample(day)
        z.append( dict(data ))
    return z


def get_comming_data():
    days_dict  =leave.seven_day_comming_calc()
    days_range = days_dict.keys()
    days_range.sort()
    days_range.reverse()
    z = []
    for i in days_range:
        v = days_dict.get(i)
        z.append( v )
    l =[]
    for i in z:
        d = {}
        for j in i:
            k = j.get("src_name")
            v = j.get("num")
            d[k] = v
        l.append(d)
    return l


def generate_excel( excel_sheet,data,begin_row=3):
    z = data
    source_total = set()
    source =  [i.keys() for i in z]

    for i in source:
        if i:
            for j in i:
                source_total.add(j)
    column = 'A'
    row = begin_row+1
    
    source_total = list (source_total)
    source_total.sort()
    source_len = len(source_total)
    for i in source_total:
        fill_excel(excel_sheet,column + str(row),i)
        row +=1
    row = begin_row

    #for i in z:
    #    calc_sum = "=SUM(%s%d:%s%d)"  %(column,row,column,row + source_len)
    #    fill_excel(excel_sheet,column+str(row),calc_sum) 

    #row=begin_row+1
    column = chr(ord(column)+1)
    for i in z:
        calc_sum = "=SUM(%s%d:%s%d)"  %(column,row+1,row + source_len)
        fill_excel(excel_sheet,calc_sum)
        row = begin_row +1
        for j in source_total:
            v = i.setdefault(j,0)
            fill_excel( excel_sheet,v)
            row+=1
        column = chr(ord(column)+1)
    return row


def today_func():

    import time
    gmt = time.gmtime()
    today = map( str,[gmt.tm_year,gmt.tm_mon,gmt.tm_mday])
    today = "_".join( today )
    return today



def generate_file(filename = "test.xlsx"):

    if not filename.endswith(".xlsx"):filename +=".xlsx"
    workbook = xlsxwriter.Workbook( filename )
    worksheet = workbook.add_worksheet()
    worksheet.set_column('A:A',20)
    source_sample="xe5x8ex9fxe5xa7x8bxe6xa0xb7xe6x9cxac"  #in unicode chinese char
    
    # in unicode chinese char
    derivate_sample='xe5xa4x84xe7x90x86xe5x8cx85xe6x8bxacxe8xa1x8dxe7x94x9fxe6xa0xb7xe6x9cxac'

    comming_sample ='xe6x9dxa5xe6xbax90xe6xa0xb7xe6x9cxac'
    worksheet.write(0,1,source_sample)
    
    days = prep.get_days(8)
    #print(days)
    count = 0
    column = 'A'
    for day in days:
        count = count +1
        column = chr(ord(column)+1)
        co = column + ":" + column
        worksheet.set_column(co,15)
        worksheet.write(0,count,str(day))
    excel_sheet = worksheet
    original_sample_count = get_data('calc_original_sample')

    print(original_sample_count)
    last_row = generate_excel ( excel_sheet,original_sample_count,begin_row=3)
    #fill_excel( excel_sheet,'A' + str(last_row),derivate_sample)
    last_row +=1
    fill_excel( excel_sheet,derivate_sample)
    derivate_sample_count = get_data('derivate')
    last_row = generate_excel(excel_sheet,derivate_sample_count,last_row)

    fill_excel( excel_sheet,comming_sample)
    last_row += 1
    incomming_sample_stat = get_comming_data()
    last_row = generate_excel(excel_sheet,incomming_sample_stat,last_row)

    workbook.close()

def gene_file():

    filename = "/dev/shm/" + today_func() + ".xlsx"
    if os.path.exists(filename):
        os.unlink(filename)
    generate_file( filename )
    return filename

if __name__ == "__main__":
    gene_file()

以上内容由PHP站长网【52php.cn】收集整理供大家参考研究

如果以上内容对您有帮助，欢迎收藏、点赞、推荐、分享。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!