python 通过win32操作Excel 简单封装

python 通过win32操作Excel 简单封装

# -*- coding: utf-8 -*-
# @Author: 
# @Date :2019/7/3
# @Time :15:01
# @file :Excel.py
# @Software: PyCharm


import win32com.client
from enum import IntEnum



class FileConst(IntEnum):
    XLSX = 51
    XLS = 56
    CSV = 6
    MAXROW = 1048576
    MAXCOLUMN = 16384


class Excel:

    def __init__(self):
        self.__app = win32com.client.Dispatch("Excel.Application")
        self.__app.Visible = False
        self.__app.DisplayAlerts = False

    def openExcelFile(self, filename):
        return WorkBook(self.__app.Workbooks.Open(filename))

    def newExcelFile(self):
        return WorkBook(self.__app.Workbooks.Add())

    def __del__(self):
        self.__app.DisplayAlerts = True
        self.__app.Quit()
        del self.__app


class WorkBook():
    def __init__(self, wb):
        # wb 指向所打开的文件
        self.__wb = wb

    def add_sheet(self, sheet_name=None, before=None):
        if sheet_name:
            if before:
                self.__wb.Worksheets.Add(Before=self.__wb.Sheets(before)).Name = sheet_name
            else:
                self.__wb.Worksheets.Add().Name = sheet_name
        else:
            if before:
                self.__wb.Worksheets.Add(Before=self.__wb.Sheets(before))
            else:
                self.__wb.Worksheets.Add()

    def delete_sheet(self, index):
        self.__wb.Worksheets(index).Delete()

    def activateSheet(self, index):
        return Sheet(self.__wb.Worksheets(index))

    def save(self, filename=None):
        if filename:
            self.__wb.SaveAs(filename)
        else:
            self.__wb.Save()

    def saveAs(self, filename, fileformat=None):
        self.__wb.SaveAs(filename, FileFormat=fileformat)

    @property
    def sheetCount(self):
        return self.__wb.Worksheets.Count

    @property
    def sheetsName(self):
        lst = []
        for i in range(1, self.sheetCount + 1):
            lst.append(self.__wb.Sheets(i).Name)
        return lst

    def close(self):
        self.__wb.Close()
        del self.__wb


class Sheet():

    def __init__(self, sheet):
        self.__sheet = sheet

    def __check_index(self, row=1, column=1):
        if row < 1 or column < 1 or row > FileConst.MAXROW or column > FileConst.MAXCOLUMN:
            raise ValueError("索引出界")

    def setCellValue(self, row, col, value):
        self.__check_index(row, col)
        self.__sheet.Cells(row, col).Value = value

    def getCellValue(self, row, col):
        self.__check_index(row, col)
        return self.__sheet.Cells(row, col).Value

    def getCellObject(self, row, column):
        location = str(self.__sheet.Cells(row, column).Address)
        return Cell(self.__sheet.Range(location))

    def delete_row(self, row):
        self.__check_index(row)
        self.__sheet.Rows(row).Delete()

    def delete_column(self, column):
        self.__check_index(column=column)
        self.__sheet.Columns(column).Delete()

    def insert_row(self, row):
        self.__check_index(row=row)
        self.__sheet.Rows(row).Insert()

    def insert_column(self, col):
    	self.__check_index(column = col)
        self.__sheet.Columns(col).Insert()

    def rename(self, newName):
        self.__sheet.Name = newName

    @property
    def maxRow(self):
        return self.__sheet.UsedRange.Rows.Count + self.__sheet.UsedRange.Cells(1, 1).Row - 1

    @property
    def maxCloumn(self):
        return self.__sheet.UsedRange.Columns.Count + self.__sheet.UsedRange.Cells(1, 1).Column - 1

    # 支持sheet["A1"] 取值
    def __getitem__(self, item):
        return self.__sheet.Range(item)

    # 支持sheet["A1"] = value 这样赋值
    def __setitem__(self, key, value):
        self.__sheet.Range(key).Value = value


class Cell:

    def __init__(self, cell):
        self.__cell = cell

    def setFont(self, value):
        self.__cell.Font.Name = value

    def setFontSize(self, value):
        self.__cell.Font.Size = value

    def setValue(self, value):
        self.__cell.Value = value

    def getValue(self):
        return self.__cell.Value



if __name__ == '__main__':
    s = Excel()
    wb = s.openExcelFile(r"D:\DataFile\PYCHARM\python_pro02\实践\test.xls")
    sht = wb.activateSheet(1)

    print(sht.maxCloumn)
    print(sht.maxRow)

    cell = sht.getCellObject(1, 4)
    cell.setFont("仿宋")
    cell.setFontSize(11)
    print(cell.getValue())
    cell.setValue(45)
    print(cell.getValue())

    wb.save(r"D:\DataFile\PYCHARM\python_pro02\实践\test.xls")

测试结果

11
10
13.0
45.0