Python常用操作之excle文件的基本操作（功能后续更新）

绝地灬酷狼 2022-04-17 06:43 150阅读 0赞

废话不多说，直接上代码：

#!/usr/bin/env python3
    # encoding : utf-8
    # @author  : 'Jonny'
    # @location: '北京'
    # @date    : '2018/11/12 17:37'
    # @Email   : 15006562764@163.com
    # @File    : ExcleHelp.py
    # @Software: PyCharm
    # @blog    : https://blog.csdn.net/m0_37338590
    # @describe: 实现对excle文件的读写操作，其他功能后续继续添加
    
    
    import pandas as pd
    import xlwt
    import xlrd
    import os
    from xlutils.copy import copy
    
    class ExcleHelp(object):
        def __init__(self,file_path_name):
            self.file_path_name = file_path_name
            # self.read_cols_name = read_cols_name
            # self.write_cols_name = write_cols_name
            # self.w_e = xlwt.Workbook(encoding="utf8")
            # self.sheet = self.w_e.add_sheet(sheet_name)
    
        def read_excle_pandas(self,read_cols_name):
            '''
            利用pandas库，实现对整个excle文件的某一列的一次性读取
            :param read_cols_name: 需要读取的列号
            :return: 返回该列的数据
            '''
            df = pd.DataFrame(pd.read_excel(self.file_path_name))
            data = [[i[j] for j in range(len(read_cols_name))]  for i in df[read_cols_name].values]
            return data
    
        def write_excle_pandas(self,save_file,write_data,write_cols_name):
            '''
            对文件的某一类数据一次性写入
            :param write_data: 要写入的数据
            :param write_cols_name:要写入列的列号
            :param save_file: 要写入的文件名
            :return: None
            '''
            df_out = pd.DataFrame(write_data,columns =write_cols_name)
            df_out.to_excel(save_file)
    
        def write_excle_xlwt(self,sheet_num,row_num,col_num,write_data):
            '''
            对excle文件的某一单元格的数据进行写入，测试部分可以同时对文件的某一单元格进行读取，
            :param sheet_num: 要写入excle表名
            :param row_num: 写入单元格的行号
            :param col_num: 写入单元格的列号
            :param write_data: 要写入的数据
            :return: None
            '''
            try:
                rb = xlrd.open_workbook(self.file_path_name)
            except Exception as e:
                print("[ExcleHelp-->write_excle_xlwt--->rb]",e)
                return None
            #************************测试（用于测试是否可以同时读写操作）*******************************
            # sheet = rb.sheet_by_name(sheet_num)
            # print(sheet.cell_value(rowx=row_num, colx=0))
            # *********************************************************
            wb = copy(rb)
            sheet = wb.get_sheet(sheet=sheet_num)
            sheet.write(r=row_num,c=col_num,label=write_data)
            os.remove(self.file_path_name)
            wb.save(self.file_path_name)
    
        def read_excle_xlrt(self,sheet_num,row_num,col_num):
            '''
             对excle文件的某一单元格的数据进行读取
            :param sheet_num: 要读取excle表名
            :param row_num: 读取单元格的行号
            :param col_num: 读取单元格的列号
            :return: 读取到的数据
            '''
            try:
                rb = xlrd.open_workbook(self.file_path_name)
            except Exception as e:
                print("[ExcleHelp-->read_excle_xlrt--->rb]",e)
                return None
            sheet = rb.sheet_by_name(sheet_num)
            nrows = sheet.nrows
            ncols = sheet.ncols
            if row_num < nrows+1 and col_num < ncols+1:
                return sheet.cell_value(rowx=row_num,colx=col_num)
            else:
                return None
    
    
    
    
    if __name__ == '__main__':
        print(ExcleHelp('01.xlsx').write_excle_xlwt(sheet_num="haha",row_num=12,col_num=2,write_data="第二次也成功了"))