【Python】xlrd、xlwt及xlutils 模块 _ 操作Execl的读写改删

操作Execl

  • xlrd：读excel

    • 引用模块(安装xlrd )  
      # pip install xlrd==1.2.0
import xlrd
    •  打开Execl文件 (r 原字符串输出，不做转义)  
      file_path_name = os.getcwd() + r"\xxx.xlsx"
      
workBook = xlrd.open_workbook(file_path_name)
    •  获取Execl数据_sheet 页 
      # 获取所有sheet的名字(list类型)
allSheetNames = self.wb_r.sheet_names();

#按索引号获取sheet内容; sheet索引从0开始
sheet = self.wb_r.sheet_by_index(0); 

# 按sheet名字获取sheet内容
sheet = workBook.sheet_by_name('Sheet1');

      # 获取所有sheet页内容
      sheet = workBook.sheets()
      
      # sheet 页名字
sheet.name
    •   获取sheet页 行数和列数 
      # 总行数
rowNums = sheet1.nrows
      
      # 总列数
colNums = sheet1.ncols 
    •  获取行或列的整组值 
      # 获取第x行内容
 rows = sheet1.row_values(1);  
 print("获取第1行内容: " ,rows);

 # 获取第y列内容
 cols = sheet1.col_values(1);  
 print("获取第1列内容: " ,cols)
    • 获取单元格内容 
      # 三种格式
sheet1.cell(0, 0).value    

sheet1.cell_value(1,1)

sheet1.row(1)[2].value

# 获取单元格内容的数据类型
sheet1.cell(1, 0).ctype

        TIPS：python读取excel中单元格的内容返回的有5种类型　　 [ 0 empty , 1 string , 2 number ,  3 date , 4 boolean , 5 error ]

    •  按行获取sheet页内容 
      　　 pt = xlrd.open_workbook(r"py_test.xlsx") # 读取工作表
    pt_s = pt.sheet_by_name("py_test") # 按sheet读取内容
    pt_s_row = pt_s.nrows # sheet页中数据总行数
    pt_s_col = pt_s.ncols # sheet页中数据总列数

    # 按行读取xlsx内容
    pt_s_row_con_list = []
    pt_s_row_con_value_list = []
    for i in range(pt_s_row):
        pt_s_row_con = pt_s.row(i)
        pt_s_row_con_value = pt_s.row_values(i)
        pt_s_row_con_list.append(pt_s_row_con)
        pt_s_row_con_value_list.append(pt_s_row_con_value)
    print("按行读取内容(包含单元格类型))：", pt_s_row_con_list)
    print("按行读取内容（多行）：", pt_s_row_con_value_list)
    print("按行读取内容（单行）：", pt_s_row_con_value_list[0])
    for i in range(pt_s_row):
        print(f"循环读取{i+1}行内容：", pt_s_row_con_value_list[i])

       运行结果

      按行读取内容(包含单元格类型))： [[text:'测试', text:'python', text:'读取表格'], [number:1.0, number:2.0, number:3.0], [text:'abc', text:'cba', text:'ooo']]
按行读取内容（多行）： [['测试', 'python', '读取表格'], [1.0, 2.0, 3.0], ['abc', 'cba', 'ooo']]
按行读取内容（单行）： ['测试', 'python', '读取表格']
循环读取1行内容： ['测试', 'python', '读取表格']
循环读取2行内容： [1.0, 2.0, 3.0]
循环读取3行内容： ['abc', 'cba', 'ooo']
    •  按列获取sheet也内容 
      # 按列读取xlsx内容
    pt_s_col_con_list = []
    pt_s_col_con_value_list = []
    for i in range(pt_s_col):
        pt_s_col_con = pt_s.col(i)
        pt_s_col_con_value = pt_s.col_values(i)
        pt_s_col_con_list.append(pt_s_col_con)
        pt_s_col_con_value_list.append(pt_s_col_con_value)
    print("按列读取内容(包含单元格类型)：", pt_s_col_con_list)
    print("按列读取内容（多列）：", pt_s_col_con_value_list)
    print("按列读取内容（单列）：", pt_s_col_con_value_list[0])
    for i in range(pt_s_col):
        print(f"循环读取{i+1}列内容：", pt_s_col_con_value_list[0])

       运行结果

      按列读取内容(包含单元格类型)： [[text:'测试', number:1.0, text:'abc'], [text:'python', number:2.0, text:'cba'], [text:'读取表格', number:3.0, text:'ooo']]
按列读取内容（多列）： [['测试', 1.0, 'abc'], ['python', 2.0, 'cba'], ['读取表格', 3.0, 'ooo']]
按列读取内容（单列）： ['测试', 1.0, 'abc']
循环读取1列内容： ['测试', 1.0, 'abc']
循环读取2列内容： ['测试', 1.0, 'abc']
循环读取3列内容： ['测试', 1.0, 'abc']
    •  交叉获取单元格内容 
      # 交叉单元格读取内容(包含单元格类型)
    pt_s_cell_list = []
    for i in range(pt_s_row):
        for j in range(pt_s_col):
            pt_s_ce = pt_s.cell(i, j)
            pt_s_cell_list.append(pt_s_ce)
    print(pt_s_ce)
    print(pt_s_cell_list)


    # 交叉单元格读取内容value
    pt_s_cell_list = []
    for i in range(pt_s_row):
        pt_s_ce_list = []
        for j in range(pt_s_col):
            pt_s_ce = pt_s.cell_value(i, j)
            pt_s_ce_list.append(pt_s_ce)
        pt_s_cell_list.append(pt_s_ce_list)
    print(pt_s_ce)
    print(pt_s_ce_list)
    print(pt_s_cell_list)

       运行结果

      text:'ooo'
[text:'测试', text:'python', text:'读取表格', number:1.0, number:2.0, number:3.0, text:'abc', text:'cba', text:'ooo']
ooo
['abc', 'cba', 'ooo']
[['测试', 'python', '读取表格'], [1.0, 2.0, 3.0], ['abc', 'cba', 'ooo']]
  • xlwt　：新建excel
    • 指定单元格插入数据 
      def xlwt_save()
   # 新建工作簿
    wb = xlwt.Workbook('utf-8')
    # 创建工作表
    ws = wb.add_sheet("xlwt_xlsx")
    # 指定单元格写入值
    ws.write(0,0,"张三是个志愿者")
    # 保存到新建工作簿
    wb.save（"save_xlwt.xlsx"）

       　 

    •  插入多行数据 
      def write_xlsx():
    # 新建工作簿
    wb = xlwt.Workbook('utf-8')
    # 创建工作表
    ws = wb.add_sheet("xlwt_xlsx")
    # 向指定单元格写入内容
    table_row = ["姓名", "性别", "学历", "工作状态", "备注"]
    table_row_con = [["张三", "男", "大专", "在职", "工龄：5年"], ["里屋", "女", "本科", "在职", "工龄：0.5年"], ["戈恩", "男", "未知", "待业", "外国人"]]
    # 插入表头
    for i in range(len(table_row)):
        add_table = ws.write(0, i, table_row[i])
    # 插入内容
    # 行
    for i in range(len(table_row_con)):
        # 列
        for j in range(len(table_row_con[i])):
            # 写入内容
            add_con = ws.write(i+1, j, table_row_con[i][j])
    # 保存工作簿到上一级xlsx文件夹下
    wb.save("../xlsx/save_py.xlsx")

       

    •  将数据保存到多个sheet页中 
      def write_xlsx():
    # 新建工作簿
    wb = xlwt.Workbook('utf-8')
    # 创建工作表
    ws = wb.add_sheet("xlwt_xlsx")
    ws_bak = wb.add_sheet("xlwt_xlsx_bak")
    # 向指定单元格写入内容
    table_row = ["姓名", "性别", "学历", "工作状态", "备注"]
    table_row_con = [["张三", "男", "大专", "在职", "工龄：5年"], ["里屋", "女", "本科", "在职", "工龄：0.5年"], ["戈恩", "男", "未知", "待业", "外国人"]]
    # 插入表头
    for i in range(len(table_row)):
        add_table = ws.write(0, i, table_row[i])
        add_table_bak = ws_bak.write(0, i, table_row[i])
    # 插入内容
    # 行
    for i in range(len(table_row_con)):
        # 列
        for j in range(len(table_row_con[i])):
            # 写入内容
            add_con = ws.write(i+1, j, table_row_con[i][j])
            add_con_bak = ws_bak.write(i+1, j, table_row_con[i][j])
    # 保存工作簿到上一级xlsx文件夹中
    wb.save("../xlsx/save_py.xlsx")

       xlsx页面展示：                         

    •  单元格添加样式 
      def xlwt_style(name, height, bold=False, format_str='', color="blue"):
    style = xlwt.XFStyle()  # 初始化样式

    font = xlwt.Font()  # 为样式创建字体
    font.name = name  # 'Times New Roman'
    font.bold = bold
    font.height = height

    borders= xlwt.Borders() # 为样式创建边框 1 单线，5 加粗线，6 双线 10 加粗-·-·线
    borders.left = 6
    borders.right = 6
    borders.top =6
    borders.bottom = 6

    style.font = font
    style.borders = borders
    style.num_format_str = format_str

    return style
      def write_xlsx2():
    # 新建工作簿
    wb = xlwt.Workbook('utf-8')
    # 创建工作表
    ws = wb.add_sheet("xlwt_xlsx")

    # 向指定单元格写入内容
    table_row = ["姓名", "性别", "学历", "工作状态", "备注"]
    table_row_con = [["张三", "男", "大专", "在职", "工龄：5年"], ["里屋", "女", "本科", "在职", "工龄：0.5年"],
                     ["戈恩", "男", "未知", "待业", "外国人"]]
    # 插入表头
    for i in range(len(table_row)):
        ws.col(i).width = 100 * 50 # 设置单元格宽度
        add_table = ws.write(0, i, table_row[i], xlwt_style("宋体", 200, bold=True, format_str=""))
    set_table_st = xlwt.easyxf('pattern: fore_colour pink', 'font: colour red, bold False;')

    pattern = xlwt.Pattern()  # 一个实例化的样式类
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # 固定的样式
    pattern.pattern_fore_colour = xlwt.Style.colour_map["pink"]  # 背景颜色
    set_table_st.pattern = pattern


    # 插入内容
    # 行
    for i in range(len(table_row_con)):
        # 列
        for j in range(len(table_row_con[i])):
            # 写入内容
            add_con = ws.write(i + 1, j, table_row_con[i][j], style=set_table_st)
    # 保存工作簿到上一级xlsx文件夹下
    wb.save("../xlsx/save_py2.xlsx")

       运行结果文件示例：       

       

       

       

  • pandas：写excel

    • 　第一种：pandas 
      # -*- coding:utf-8 -*-
import os
import pandas as pd

class SaveData:

    def save_execl(self,data):
        df = pd.DataFrame(data, columns=['test1', 'test2', 'test3'])
        # 保存到本地excel
        excel_name = "result_%s.xlsx" % str("execl")
        try:
            df.to_excel(u'..\\execl\\' + str(excel_name), index=False)
        except:
            os.makedirs("../execl")  # 创建文件夹
            df.to_excel(u'..\\execl\\' + str(excel_name), index=False)

if __name__ == "__main__":
    # 需要插入的数据
    data = [['阴天','晴天','雨天']]
    SaveData().save_execl(data)

  • xlsxwriter

    • 创建文件及sheet页面 
      # 创建文件
wb = xlsxwriter.Workbook("new_excel.xlsx")
# 创建sheet
 ws = wb.add_worksheet("prcation")
    • 单元格设置
      • 字体颜色：color
      • 字体加粗：bold
      • 字体大小：font_site
      • 日期格式：num_format
      • 超链接：url
      • 下划线设置：underline
      • 单元格颜色：bg_color
      • 边框：border
      • 对齐方式：align
    • # 单元格设置
 f1 = wb.add_format({"color" : "cccccc", "bold": True, "bg_color":"aaaaaa"})
    • 设置行高列宽 
       ws.set_row(0, 80)
 ws.set_column('A:B', 35)
    • 写入数据 
      # 方法一：
 ws.write(1, 0, 'hello world 12123', f1)

# 方法二：
ws.write('B1', 13)    
ws.write('B2', 8)    # 写入数字
ws.write('B3', '=sum(B1:B2)') # 写入函数
# 写入日期
d = wb.add_format({'num_format': 'yyyy-mm-dd'})　　# 设置单元格格式=日期
ws.write(0, 0, datetime.datetime.strptime('2017-09-13', '%Y-%m-%d'), d)

# 批量往单元格写入数据
 ws.write_column('A1', [1, 2, 3, 4, 5])  # 列写入，从A1开始
 ws.write_row('B2', [6, 7, 8, 9])  # 行写入，从B2开始
    • 关闭execl 
      # 关闭workbook
 wb.close()
    •  
    •  
    •  
    • 　　　　　　 
  • xlutils：修改execl
    • 　修改单元格 
      import xlrd, xlwt
# 单独引用xlutils 下的copy 写法为copy,如果仅引用xlutils 使用copy时为xlutils.copy.copy
from xlutils.copy import copy

def revise_xlsx():
    wb = xlrd.open_workbook("../xlsx/save_py.xlsx")
    cp_wb = copy(wb)
    cp_ws = cp_wb.get_sheet("xlwt_xlsx_bak")
    # copy 直接修改单元格内容
    cp_ws.write(0, 0, "id")
    cp_wb.save("../xlsx/save_py.xlsx")

       运行结果：  0,0 位置值“姓名”修改为"id"

      　 
    •  设置单元格样式 
          def result_style():
        '''
        新表里面写入新的数据的格式
        '''
        #  新建一个样式表
        style = xlwt.XFStyle()

        # 新建一个字体格式对象
        font = xlwt.Font()
        # 设置字体样式
        font.name = '微软雅黑'
        font.bold = True
        font.height = 200
        #  把字体放入样式表
        style.font = font

        # 新建一个边框样式
        borders = xlwt.Borders()
        #  边框都是细线
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        #  把边框给样式表
        style.borders = borders

        #  新建一个对齐
        alingnment = xlwt.Alignment()
        #  垂直和水平对齐
        alingnment.horz = xlwt.Alignment.HORZ_CENTER
        alingnment.vert = xlwt.Alignment.VERT_CENTER
        #  把对齐给样式表
        style.alignment = alingnment

        # 设置背景颜色
        pattern = xlwt.Pattern()
        # 背景颜色
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = 48
        # 把背景颜色给样式表
        style.pattern = pattern

        return style

       使用

      wb = xlrd.open_workbook("result.xlsx")
# 获取原表格数据
old = wb.sheet_by_name("Sheet1")
old_norws = old.nrows
# 复制表格
cp_wb = copy(wb)
cp_ws = cp_wb.get_sheet("Sheet1")
# 写入数据
cp_ws.write(0, 0, "列名", result_style())

       

    • 执行结果        
    • 参考地址： https://blog.csdn.net/weixin_35757704/article/details/116290252 

 

拓展：操作json文件

 

获取.json 数据

# 获取json文件
path_name = "文件路径+文件名"
def get_json(self):
    try:
        with open(file=path_name, mode="r+", encoding="utf-8") as fr:
            json_all = json.loads(fr.read())
            logs.debug(json_all)
        fr.close()
    except Exception as e:
        logs.error("请检查对应的文件夹及文件是否存在,%s" % e)
        return False
    else:
        return json_all

 

        try:
            # 先读取json文件
            with open(file=self.path_name, mode="r+", encoding="utf-8") as fr:
                json_all = json.loads(fr.read())
        except Exception as e:
            logs.error("请检查对应的文件夹及文件是否存在,%s" % e)
            return False
        else:
            self.update_json(json_all)
            try:
                # 更新key-value写入json文件
                with open(file=self.path_name, mode="w+", encoding="utf-8") as fw:
                    json.dump(json_all, fw, ensure_ascii=False, indent=4)
            except Exception as e:
                logs.error("请检查对应的文件夹及文件是否存在,%s" % e)
                return False
            else:
                logs.debug(json_all)
                return json_all

  

json_all["timestamp"] = PublicTools.timestamp()

 

 
posted @ 2021-11-07 19:05  Phoenixy  阅读(476)  评论(0)    收藏  举报