from openpyxl import load_workbook  #引入模块

class MyExcel:

    def __init__(self,filepath,sheetname):  # 初始化函数在实例化对象时调用

        self.wb = load_workbook(filepath)

        self.sh = self.wb[sheetname]

    # 读取指定单元格的数据

    def read_data(self,row,column):

        return self.sh.cell(row,column).value

    # 读取所有数据

    def read_alldate(self):

        for index in range(1,self.sh.max_row+1):

            print('行号：',index)

            for i in range(1,self.sh.max_column+1):

                print('列号：',i,'内容：',self.sh.cell(row=index,column=1).value)

    # 写入数据

    def write_data(self,row,column,content):

        self.sh.cell(row,column).value = content

    # 保存数据

    def save_data(self,filepath):

        self.wb.save(filepath)

# excel = MyExcel('testdata.xlsx','Sheet1')

# print(excel.read_data(2,1))

#

# excel.write_data(4,1,'12345678936')

# excel.write_data(4,2,'a1234567')

# excel.save_data('testdata.xlsx')

import unittest

from excel_operate import MyExcel

class TestExcel(unittest.TestCase):

    @classmethod

    def setUpClass(cls):

        cls.excel = MyExcel('testdata.xlsx','Sheet1')

    # 测试用例  读

    def test_read_data(self):

        pass

    # 测试用例  写

    def test_write_data(self):

        # 调用自己的写方法

        self.excel.write_data(5, 1, '')

        self.excel.write_data(5, 2, 'a1234567')

        self.excel.save_data('testdata.xlsx')

import unittest

from HTMLTestRunnerNew import HTMLTestRunner

from test_excel_operate import TestExcel

import time

import os

if __name__ == '__main__':

    s = unittest.TestSuite()

    s.addTests(unittest.TestLoader().loadTestsFromTestCase(TestExcel))

    now = time.strftime('%Y%m%d_%H%M%S')

    filename = open(os.getcwd() + '/ExcelUnittestReport_' + now + '.html','wb')

    runner = HTMLTestRunner(

        stream = filename,

        title = 'Excel单元测试报告',

        description = 'Excel单元测试报告',

        tester = 'lixiaozhe'

                            )

    runner.run(s)