hua_python操作excel

最新推荐文章于 2022-11-21 18:58:02 发布

上海_测试渣渣

最新推荐文章于 2022-11-21 18:58:02 发布

阅读量142

点赞数

分类专栏： python

本文链接：https://blog.csdn.net/weixin_45041997/article/details/108051484

版权

python 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

import pandas as pd
import xlsxwriter
import os
import io
import urllib.request
class option_excel:
    def __init__(self):
        self.colum_name = None
        self.colum_num =  None

    def read_xlsx(self,path,header=0):
        df = pd.read_excel(path,header=header)#读取xlsx中第一个sheet
        datas = df.values  #list形式，读取表格所有数据
        self.colum_name = df.columns.values
        self.colum_num = df.shape[1]
        self.row_num = df.shape[0]
        return datas

    def data_to_xlsx(self,path,newpath):
            read = self.read_xlsx(path)
            print(read)
            print(self.colum_name)
            print(self.colum_num)
            workbook = xlsxwriter.Workbook(newpath)  # 新建excel表
            worksheet = workbook.add_worksheet('sheet1')  # 新建sheet（sheet的名称为"sheet1"）
            # 加宽第一列使文本更清晰。
            worksheet.set_column(0, 10, 20)  # 设置区域列宽度
            for i in range(1,self.row_num+1):
                worksheet.set_row(i, 100)
            header_style = workbook.add_format({
                'bold': False,  # 字体加粗
                'border': 0,  # 单元格边框宽度
                'align': 'center',  # 水平对齐方式
                'valign': 'vcenter',  # 垂直对齐方式
                'fg_color': '#FFFF00',  # 单元格背景颜色
                'text_wrap': False,  # 是否自动换行
            })
            row_style = workbook.add_format({
                'align': 'left',  # 水平对齐方式
                'valign': 'vcenter',  # 垂直对齐方式
                'text_wrap': True,  # 是否自动换行
            })
            worksheet.write_row('A1', self.colum_name,header_style)
             # 将数据插入到表格中
            row = 1
            url = 'https://timgsa.baidu.com/timg?image&quality=80&size=b9999_10000&sec=1597642766628&di=91396d8b709a7455f025faf1b5c8d2fd&imgtype=0&src=http%3A%2F%2Fa.hiphotos.baidu.com%2Fbaike%2Fpic%2Fitem%2Ff703738da977391243bca26cfe198618367ae221.jpg'
            image_data = io.BytesIO(urllib.request.urlopen(url).read())
            for i in read:
                d = [str(j).replace('nan','null') for j in i ]
                worksheet.write_row(row, 0,d,row_style)
                worksheet.insert_image(row,self.colum_num,url, {'x_offset':0,'y_offset':0,'x_scale':0.4,'y_scale':0.4,'url':url,'tip':None,'image_data':image_data,'positioning': None})
                row+=1
            workbook.close()
            print(row)
def read_file():
    data = []
    for root, dirs, files in os.walk(os.getcwd()):
        for file in files:
            if file.endswith('.xlsx'):
                data.append((file,root))
    return data

if __name__ == '__main__':
    file_path = read_file()
    data = option_excel()
    for name,root in file_path:
        path = os.path.join(root,name)
        newpath = os.path.join(root,f'处理后_{name}')
        data.data_to_xlsx(path,newpath)