Python 操作Excel-封装类

简介： 包含Python对Excel的常用操作，如Excel的行数列数信息读取、按行按列读取Excel数据、按行按列写入Excel数据、删除指定Sheet等操作，具体内容参考下述代码。

引用库： xlrd、openpyxl；

注： 本类适用于xlsx格式的文件，xlrd版本为1.2.0；

#!/usr/bin/python
# -*- coding: UTF-8 -*-

"""
@name：handle_excel.py
@desc: excel处理类，用于读取和写入excel, 需要为xlsx格式
@author: zxy
@date: 2023/08/02
@version: 1.0
usage:
	from handle_excel import HandleExcel
	
	excel_handle = HandleExcel("test.xlsx") 					#初始化对象
	data = {'0':'[123,456]','1':'[456,789]'}
	excel_handle.write_excel_by_row('test', data)               #向指定sheet的写入行数据
	excel_handle.write_excel_by_col('test', data)               #向指定sheet的写入列数据
	excel_handle.delete_sheet('test')                           #删除指定sheet页
	
	row_num ,col_num = excel_handle.get_row_col_num('test')     #获取指定sheet的行列信息
	row_data = excel_handle.read_excel_by_row('test',[0,1,2])   #获取指定sheet的前三列数据
	row_data = excel_handle.read_excel_by_row('test',[])        #获取指定sheet的全部列数据
	col_data = excel_handle.read_excel_by_col('test',[0,1,2])   #获取指定sheet的前三行数据
	col_data = excel_handle.read_excel_by_col('test',[])        #获取指定sheet的全部行数据
"""

import os
import xlrd
import openpyxl


class HandleExcel:
    def __init__(self, excel_name):
        self.__excel_name = excel_name
        
    def __get_sheet(self, sheet_name):
        """
        根据sheet名称或编号获取sheet对象, 查询时自动调用
        :param sheet_name: sheet名称或编号
        :return: 查询正常时返回 sheet对象;
                 查询异常时返回 None
        """        
        try:
            book = xlrd.open_workbook(self.__excel_name)
            if isinstance(sheet_name, int):
                sheet = book.sheet_by_index(sheet_name)
            else:
                sheet = book.sheet_by_name(sheet_name)
        	return sheet
        except:
            return None
            
	def __create_excel(self):
        """创建excel，写数据时自动调用"""
		workbook = openpyxl.Workbook()
        workbook.save(self.__excel_name)
        workbook.close()
        
    def delete_sheet(self, sheet_name):
        """
        删除指定名称的sheet
        :param sheet_name: 删除sheet_name名称或者编号的sheet
        :return: 删除正常时返回 0;
                 删除异常时返回 -1
        """
        try:
            workbook = openpyxl.load_workbook(self.__excel_name)
			workbook.remove(workbook[sheet_name])
            workbook.save(self.__excel_name)
            workbook.close()
            return 0
        except:
            return -1

    def get_row_col_num(self, sheet_name):
        """
        获取excel行数、列数信息
        :param sheet_name: 查询sheet的名称或者编号, 如：'test'或者 0
        :return: 查询正常时返回 (sheet.nrows, sheet.ncols), excel指定sheet的行数、列数信息元组;
                 查询异常时返回 -1
        """
        try:
            sheet = self.__get_sheet(sheet_name) 
            return sheet.nrows, sheet.ncols
        except:
            return -1, -1
            
    def read_by_col(self, sheet_name, col_num_list):
        """
        按列读取excel
		:param sheet_name: sheet名称或编号
		:param col_num_list: 读取指定列编号的列表，如:[0, 1, 2, 3], 当col_num_list为[]时则读取全部列数据
        :return: 查询正常时返回 读取内容的字典格式，列编号为键-int类型，数据为值，如{0:'a', 1:'b'}
                 查询异常时返回 {}
        """
        col_values = {}
        try:
            sheet = self.__get_sheet(sheet_name)
            if not col_num_list:
                col_num_list = range(sheet.ncols)
                
            for tmp_col_num in col_num_list:
                tmp_col_values = sheet.col_values(int(tmp_col_num))
                col_values.update({tmp_col_num: tmp_col_values})
            return col_values
        except:
            return {}
            
    def read_by_row(self, sheet_name, row_num_list):
 		"""
        按行读取excel
        :param sheet_name: sheet名称或编号
        :param row_num_list: 读取行标号的列表，如:[0, 1 , 2, 3], 当col_num_list为[]时则读取全部列数据
        :return: 查询正常时返回 读取内容的字典格式，行编号为键-int类型，数据为值，如{0:'a', 1:'b'}
                 查询异常时返回 {}
        """
        row_values = {}
        try:
            sheet = self.__get_sheet(sheet_name)
            if not row_num_list:
                row_num_list = range(sheet.nrows)

            for tmp_col_num in row_num_list:
                tmp_row_values = sheet.row_values(int(tmp_col_num))
                row_values.update({tmp_col_num: tmp_row_values})
            return row_values
        except:
            return {}
            
    def write_by_row(self, sheet_name, row_values_dict):
        """
        写入excel
        :param sheet_name: sheet名称-字符串类型
        :param row_values_dict: 需要写入的字典，键为行号-int类型，值为每一行的字符串列表,如{0:[0,1,2,3], 1:[2,3,4,5,6]}
        :return: 写入正常时返回 0
                 写入异常时返回 -1
        """
        try:
            if not os.path.exists(self.__excel_name):
                self.__create_excel()
            workbook = openpyxl.load_workbook(self.__excel_name)
            
            if not self.__get_sheet(sheet_name):
                workbook.create_sheet(title=sheet_name)
                
            work_sheet = workbook[sheet_name]
            for row_key in row_values_dict.keys():
                for col_key in range(0, len(row_values_dict[row_key])):
					work_sheet.cell(row_key+1, col_key+1).value = row_values_dict[row_key][col_key]
			workbook.save(self.__excel_name)
			workbook.close()
			return 0
        except:
            return -1

    def write_by_col(self, sheet_name, col_values_dict):
        """
        写入excel
        :param sheet_name: sheet名称-字符串类型
        :param col_values_dict: 需要写入的字典，键为行号-int类型，值为每一列的字符串列表,如{0:[0,1,2,3], 1:[2,3,4,5,6]}
        :return: 写入正常时返回 0
                 写入异常时返回 -1
        """
        try:
            if not os.path.exists(self.__excel_name):
                self.__create_excel()
            workbook = openpyxl.load_workbook(self.__excel_name)

            if not self.__get_sheet(sheet_name):
                workbook.create_sheet(title=sheet_name)
                
			work_sheet = workbook[sheet_name]
            for col_key in col_values_dict.keys():
				for row_key in range(0, len(col_values_dict[col_key])):
					work_sheet.cell(row_key+1, col_key+1).value = col_values_dict[col_key][row_key]
			workbook.save(self.__excel_name)
			workbook.close()
			return 0
        except:
            return -1

def main():
    excel_handle = HandleExcel('test.xlsx')
    excel_data = {0: ['111', '222', '333'], 1: [123, 456, 789]}
    excel_handle.write_by_col('sheet1', excel_data)
    excel_handle.write_by_row('sheet2', excel_data)
    excel_handle.delete_sheet('Sheet')
    row_num, col_num = excel_handle.get_row_col_num('sheet1')
    print(row_num, col_num)
    col_data_all = excel_handle.read_by_col('sheet1', [])
    col_data_select = excel_handle.read_by_col('sheet1', [0])
    row_data_all = excel_handle.read_by_row('sheet1', [])
    row_data_select = excel_handle.read_by_row('sheet1', [0, 1])
    print(col_data_all)
    print(col_data_select)
    print(row_data_all)
    print(row_data_select)


if __name__ == '__main__':
    main()