python操作excel

1. 引用模块：import  xlrd

2.打开excel  

workbook = xlrd.open_workbook('../dataconfig/case1.xls') # 相对路径

workbook = xlrd.open_workbook('D:/python/test/cythia_test/dataconfig/case1.xls') #绝对路径表示法

3.获取excel的sheet信息

获取sheet的名称：sheetname = workbook.sheet_names()[0]

获取sheet的页面数：page = len(workbook.sheet_names())

4.获取sheet内容

根据sheet索引获取sheet内容 ：sheet = workbook.sheet_by_index(0)

根据sheet名称获取sheet内容： sheet2 = workbook.sheet_by_name('hehe')

5.获取sheet的行数和列表

rows = sheet2.nrows

cols = sheet.ncols

6.获取sheet的单元格的数据

data = sheet.cell_value(row,col)　　#行和列的都是从0开始

7.写入excel (复制一份，再写入，不会覆盖掉原有的数据)

from xlutils.copy import copy
read_workbook = xlrd.open_workbook(self.filename)
write_workbook = copy(read_workbook) #复制一份
write_table = write_workbook.get_sheet(0) #通过get_sheet()获取的sheet有write()方法
write_table.write(row,col,value)
write_workbook.save(self.filename)

直接新建一个excel并写入数据

#导入模块
import xlwt
#创建workbook（其实就是excel）
workbook = xlwt.Workbook(encoding = 'ascii')
#创建表
worksheet = workbook.add_sheet('My Worksheet')
#往单元格内写入内容
worksheet.write(0, 0, label = 'Row 0, Column 0 Value')
#保存
workbook.save('Excel_Workbook.xls')

8. 封装操作excel的方法

# -*- coding: utf-8 -*-
import xlrd
from xlutils.copy import copy
class OperationExcel:
    '''def read_excel(self):
    #打开excel文件
    workbook = xlrd.open_workbook('../dataconfig/case1.xls')
    #获取第一个sheetname的名字
    sheetname = workbook.sheet_names()[0]
    page = len(workbook.sheet_names())
    #根据sheet索引获取sheet内容
    sheet = workbook.sheet_by_index(0)
    #根据sheet名称获取sheet内容
    sheet2 = workbook.sheet_by_name('hehe')
    rows = sheet2.nrows
    cols = sheet.ncols
    data = sheet.cell_value(0,5)
    print(page,sheetname,rows,cols,data)'''
    #构造函数初始化
    def __init__(self,sheet_id,file_name=None):
        if file_name:
            self.filename = file_name
            self.sheetid = sheet_id
        else:
            self.filename = '../dataconfig/case1.xls' #相对路径
            self.sheetid = sheet_id
        self.data = self.get_data()  #调用get_data()方法，获得sheet的内容

    #获取sheets的内容
    def get_data(self):
        workbook = xlrd.open_workbook(self.filename) #打开excel
        tables = workbook.sheet_by_index(self.sheetid) #通过sheet_by_index()获取的sheet没有write()方法
        return tables

    #获取单元格的行数
    def get_lines(self):
       lines = self.data.nrows
       return lines

    #获取某一个单元格的内容
    def get_cell_value(self,row,col):
        value = self.data.cell_value(row,col)
        return value

    #写入数据
    def write_value(self,row,col,value):
        read_workbook = xlrd.open_workbook(self.filename)
        write_workbook = copy(read_workbook)
        write_table = write_workbook.get_sheet(0) #通过get_sheet()获取的sheet有write()方法
        write_table.write(row,col,value)
        write_workbook.save(self.filename)


if __name__=='__main__':
    opers = OperationExcel(0)
    #print(opers.get_cell_value(2,6))
    opers.write_value(2,6,'cythia')
    print(opers.get_cell_value(2,6))