openpyxl利用python处理excel脚手架

openpyxl利用python处理excel脚手架

千挑万选,代码最香

from openpyxl import Workbook, load_workbook

class ExcelUtil:
    @staticmethod
    def read_xlsx(filename, start, indexs):
        # 从filename文件的第一个sheet的第start行开始读取indexs这些列
        wb = load_workbook(filename)
        sheet_names = wb.sheetnames
        ws = wb[sheet_names[0]]
        number = ws.max_row
        data_list = list()
        for i in range(start, number + 1):
            line = list()
            for j in indexs:
                cel = ws.cell(row=i, column=j).value
                line.append(cel)
            data_list.append(line)
        return data_list

    @staticmethod
    def write_xlsx(filename, start, indexs, data):
        # 创建filename文件,并从start行开始把数据写到indexs列中
        wb = Workbook()
        ws = wb.active
        number = len(data or [])
        num = 0
        for i in range(start, number + 1):
            for j in range(len(indexs)):
                ws.cell(row=i, column=indexs[j]).value = data[num][j]
            num += 1
        wb.save(filename)

    @staticmethod
    def load_xlsx(filename, start=2):
        # 加载整个表单
        wb = load_workbook(filename)
        sheet_names = wb.sheetnames
        ws = wb[sheet_names[0]]
        number = ws.max_row
        data = list()
        titles = [ws.cell(row=1, column=c).value for c in range(1, ws.max_column + 1)]
        none_line =0
        for r in range(start, number + 1):
            line = [ws.cell(row=r, column=c).value for c in range(1, ws.max_column + 1)]
            if any(line):
                # 特殊格式处理
                for i in range(len(line)) :
                    if isinstance(line[i], datetime.datetime):
                        line[i] = str(line[i])
                data.append(line)
            else:
                none_line +=1
                if none_line>10:
                    break
        return titles, data

    @staticmethod
    def get_titles(filename):
        # 获取文件第一行标题
        wb = load_workbook(filename)
        sheet_names = wb.sheetnames
        ws = wb[sheet_names[0]]
        titles = [ws.cell(row=1, column=c).value for c in range(1, ws.max_column + 1)]
        return titles

    @staticmethod
    def fast_write(filename, data, titles = []):
        # 快速写文件,data为二维数组
        wb = Workbook()
        ws = wb.active
        if titles:
            ws.append(titles)
        for line in data:
            ws.append(line)
        wb.save(filename)
更新时间:2017-04-13 13:35:16

本文由 智慧煮粥 创作,如果您觉得本文不错,请随意赞赏
采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名
原文链接:http://zz.zzhub.cn/archives/openpyxlpythonexcel
最后更新:2017-04-13 13:35:16

评论

Your browser is out of date!

Update your browser to view this website correctly. Update my browser now

×