python--excel文件读写

前言

操作excel，读文件一般用xlrd，写文件用xlwt，复制文件用xlutils.copy中的copy方法

一、常用方法

1、读文件
xlrd.open_workbook # 打开文件，形成一个workbook
workbook.sheet_names() # 返回全部的sheet名列表
workbook.sheet_by_name(sheetname) # 通过指定sheetname获取sheet
workbook.sheet_by_index(index) # 通过指定index获取sheet
sheet.row_values(row) # 获取指定行的内容，row从0开始
sheet.col_values(col) # 获取指定列的内容，col从0开始
sheet.get_rows() # 以行的形式读取所有内容，返回的是一个二维list
sheet.cell_value(row,col) # 读取指定单元格的内容

2、写文件
xlwt.Workbook() #打开excel文件
workbook.add_sheet(sheetname) #添加一个sheet
sheet.wrtie(row,col,value) #向指定单元格写入内容
workbook.save(filename) #保存文件，一般最好是保存为xls的后缀，xlsx可以正常保存，但是只有wps能打开

3、修改文件
old_workbook = xlrd.open_book(filepath)
new_workbook = copy(old_workbook )#j将原文件重新复制一份
new_sheet = new_workbook.get_sheet(index)#通过index获取sheet
new_sheet .write(row,col,value) #向新sheet中写入内容
new_workbook .save(filename) #名称和原名称一致时，会覆盖原来的内容

二、封装自己的excel操作类

实现excel文件读取、写入和修改的常用方法

import os
import xlwt,xlrd,nnlog
from xlutils.copy import copy

log = nnlog.Logger('log.log')

class ReadDataFromExcel:
    def __init__(self,filepath):
        self.filename = filepath
        self.open_excel()

    def open_excel(self):
        self.workbook = xlrd.open_workbook(self.filename)

    def read_all_sheets_content(self):
        self.sheets = self.workbook.sheet_names()                              #返回所有sheet的列表
        log.debug(self.sheets)
        for sheet in self.sheets:
            self.mysheet = self.workbook.sheet_by_name(sheet)                    #通过sheet name获取指定的sheet
            for row in range(self.mysheet.nrows):                                #获取当前sheet的总行数
                for col in range(self.mysheet.ncols):                            #获取当前sheet的总列数
                    print('%ssheet%d行%d列的内容为:' % (sheet, row + 1, col + 1), self.mysheet.cell_value(row, col))       #获取指定单元格的内容

    def get_sheet_by_name(self,sheetname):
        try:
            self.mysheetbook = self.workbook.sheet_by_name(sheetname)         #通过sheet name 获取sheet
        except Exception as e:
            msg = '%s不存在:'% sheetname + str(e)
            raise Exception(msg)
        return self.mysheetbook

    def get_sheet_by_index(self,index):
        try:
            self.mysheetbook = self.workbook.sheet_by_index(index)            #通过index获取sheet
        except Exception as e:
            msg = '%d超出index范围:'% index + str(e)
            raise Exception(msg)
        return self.mysheetbook

    def read_sheet_content_row(self,sheetname,row):
        self.mysheet = self.get_sheet_by_name(sheetname)
        try:
            self.values = self.mysheet.row_values(row)                   #通过index读取指定行的内容，index从0开始
        except Exception as e:
            raise Exception('%d超出了index范围'%row)
        else:
            return self.values

    def read_sheet_content_col(self,sheetname,col):
        self.mysheet = self.get_sheet_by_name(sheetname)
        try:
            self.values = self.mysheet.col_values(col)           #通过index读取指定行的内容，index从0开始
        except Exception as e:
            raise Exception('%d超出了index范围'%col)
        else:
            return self.values

    def read_sheet_content_rows(self,sheetname):
        self.mysheet = self.get_sheet_by_name(sheetname)
        self.values = self.mysheet.get_rows()          #以行的形式读取所有内容，返回的是一个二维list
        print(len(self.values))
        return self.values

    def read_sheet_cell_content(self,sheetname,r,c):
        '''
        获取指定单元格的内容
        1、 self.mysheet.cell_value(r,c)
        2、self.mysheet.row(r)[c].value
        3、self.mysheet.cell(r,c).value
        '''
        self.mysheet = self.get_sheet_by_name(sheetname)
        try:
            self.value = self.mysheet.cell_value(r,c)
        except Exception as e:
            raise Exception('输入的%d行%d列可能不存在'%(r,c))
        else:
            return self.value

class WritDataInExcel:
    def __init__(self,filename,sname):
        self.filename = filename
        self.sname = sname
        self.open_excel(self.sname)

    def open_excel(self,sname):
        self.book = xlwt.Workbook()
        self.mys = self.book.add_sheet(sname)

    def write_data(self,r,c,value):
        self.mys.write(r,c,value)        #向单元格写入内容
        '''
        self.mys.write(r,c,xlwt.Formula("A3+B3"))      #向单元格中写入公式
        '''

    def save_data(self):
        self.book.save(self.filename)

class EditWorkbook():

    def __init__(self,filename,sheet_index):
        self.filepath = os.path.join(os.path.dirname(os.path.dirname(__file__)), 'file', filename)
        old_wb = ReadDataFromExcel(self.filepath)
        self.new_wb = copy(old_wb.workbook)
        self.new_sheet = self.new_wb.get_sheet(sheet_index)

    def write_content(self,r,c,content):
        self.new_sheet.write(r,c,content)

    def save_data(self):
        self.new_wb.save(self.filepath)

三、有关excel操作习题练习

1、将一组输入写入到excel
思路：（1）循环写入表头
（2）循环写入内容

import xlwt

result = {
	"1":["小花",99,100,98.5],
	"2":["小王",90,30.5,95],
	"3":["小明",67.5,49.6,88]
}

headers = ['学号', '姓名', '语文成绩', '数学成绩', '英语成绩', '总分', '平均分']

workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('成绩列表')

# 写入表头
for i in range(len(headers)):
    worksheet.write(0, i, headers[i])

# 写入内容
for xuehao in result:
    row_num = list(result.keys()).index(xuehao)+1
    worksheet.write(row_num, 0, xuehao)
    total = 0
    for index, score in enumerate(result.get(xuehao),1):
        if index > 1:
            total += float(score)
        worksheet.write(row_num, index, score)
    worksheet.write(row_num, len(result.get(xuehao))+1, total)
    worksheet.write(row_num, len(result.get(xuehao))+2, total/3)

workbook.save('成绩列表.xls')