python3 使用openpyxl 处理excel表格数据

最新推荐文章于 2024-06-21 10:34:53 发布

置顶书弋江山

最新推荐文章于 2024-06-21 10:34:53 发布

阅读量1k

点赞数

分类专栏： Python 文章标签： python openpyxl excel

本文链接：https://blog.csdn.net/bobbob32/article/details/103057190

版权

Python 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

直接贴代码，注释很清楚

import openpyxl
import time
import os
import threading
class mExcelManager:

    def __init__(self,filerootpath,filename):
        self.allmodels = []
        self.zimu = ["A", "B", "C", "D", "E", "F",
                     "G", "H", "I", "J", "K", "L",
                     "M", "N", "O", "P", "Q", "R",
                     "S", "T", "U", "V", "W", "X",
                     "Y", "Z"]
        self.filerootpath = filerootpath
        self.filename = filename
        self.result_excel_name = ""

    # 读取excel表格
    def read_excel_data(self,defaultmax_column=12):
        wb = openpyxl.load_workbook(filename=self.filename, read_only=True, )
        ws = wb.active
        model = []
        print(ws.max_row)
        for index in range(2, ws.max_row):
            if model.__len__() > 1:
                self.allmodels.append(model)
                model = []
                # print("read datas =>")
            if self.allmodels.__len__() == 200:
                time.sleep(0.1)
                continue
            for rowindex in range(1,defaultmax_column):
                data = ws[""+self.zimu[rowindex] + str(index)].value
                model.append(data)

    #写入excel表格，写入速度比较慢
    def write_to_excel(self,file_path_name):
        wb = openpyxl.load_workbook(filename=file_path_name)
        ws = wb.active
        start_time = time.time()
        count = 0
        while (True):
            if self.allmodels.__len__() > 0:
                model = self.allmodels.pop(0)
                ws.append(model)
                wb.save(file_path_name)
                count = count + 1
                currenttime = time.time()
                useseconds = int(currenttime) - int(start_time)
                print("write datas "+ " time:" + str(useseconds) +" count: " + str(count))
            else:
                time.sleep(0.01)
                continue
    # 创建表格
    def create_excel(self,filename,sheet_name = "",title = ""):
        self.result_excel_name = filename
        wb = openpyxl.Workbook()
        if sheet_name.__len__()>0:
            wb.create_sheet("Name", 0)
        ws = wb.active
        if title.__len__() >0:
            ws.title = "bob"
        wb.save(filename=filename)