python下openpyxl和xlrd、xlwt、xutils.copy的简单使用（自测）

# -*- coding: utf-8 -*-
"""
Created on Wed Sep 18 14:05:24 2019

@author: Administrator
"""

import os
import traceback
import openpyxl
import xlrd
import xlwt
from xlutils.copy import copy


# 操作.xlsx格式excel
# openpyxl
class OpenpyxlExcel(object):
    def __init__(self, filename):
        root = "d:/"
        self.filename = filename
        self.path = root + self.filename
        self.wb = openpyxl.load_workbook(self.path)
    
    
    def openpyxl_read(self):
        sheet = self.wb.worksheets[0]
        self.lsh_tuple = sheet.values
        yield self.lsh_tuple
    
    
    def openpyxl_write(self, data):
        sheet = self.wb.active
        # row 和 col的索引从1开始
        sheet.active_cell(row=1, column=2, value="流水号")
        sheet.active_cell(row=1, column=3, value="机构全名")
        sheet.active_cell(row=1, column=4, value="机构编码")
        for num in range(len(data)):
            sheet.active_cell(row=num+2, column=2, value=data[11])
            sheet.active_cell(row=num+2, column=3, value=data[1])
            sheet.active_cell(row=num+2, column=4, value=data[1])
        self.wb.save(self.path)
        
# 操作.xls格式excel
# xlrd  xlwt  xlutils.copy
class OprationExcel(object):
    def __init__(self, filename):
        root = "d:/test/"
        self.filename = filename
        self.path = root + self.filename
        if not os.path.exists(root):
            os.mkdir(root)
        if not os.path.exists(self.path):
            f = open(self.path, "w")
            f.close()

    # 读
    def excel_read(self):
        try:
            rt = xlrd.open_workbook(self.path)
            sheet1 = rt.sheet_by_index(0)
            self.lsh_list = sheet1.col_values(0)
            print(self.lsh_list)
        except xlrd.biffh.XLRDError:
            print("文件内容为空")
#            exit()
#        yield self.lsh_list
    
    # 写
    def excel_write(self, data):
        wt = xlwt.Workbook()
        sheet1 = wt.add_sheet("Sheet1")
        
        # row 和 col的索引从0开始
        sheet1.write(0, 1, "流水号")
        sheet1.write(0, 2, "机构全名")
        sheet1.write(0, 3, "机构编码")
        for num in range(len(data)):
            sheet1.write(num+1, 1, data[11])
            sheet1.write(num+1, 2, data[0])
            sheet1.write(num+1, 3, data[1])
        wt.save(self.path)

    # 追加
    def excel_append(self, data):
        # formatting_info=True 保留文件原格式
        wt =  xlrd.open_workbook(self.path, formatting_info=True)
        
        # 复制文件进行操作，直接操作原文件会覆盖
        new_wt = copy(wt)
        new_sheet = new_wt.get_sheet(0)
        new_sheet.write(0, 0, "序号")
        for num in range(len(data)):
            new_sheet.write(num+1, 0, data[num])
        new_wt.save(self.path)


test_list = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12]

if __name__ == "__main__":
    try:
        excel0 = OpenpyxlExcel("test0.xlsx")
        excel0.openpyxl_read()
        excel0.openpyxl_write(test_list)
        
        excel1 = OprationExcel("test.xls")
        excel1.excel_read()
        excel1.excel_write(test_list)
        excel1.excel_append(test_list)
    except:
        traceback.print_exc()