Python操作Excel表格

 

使用xlwt + xlrd + xlutils操作Excel表格

 

# coding: utf-8
import xlwt
from xlrd import open_workbook
from xlutils.copy import copy
import os
import sys
reload(sys)
sys.setdefaultencoding('utf-8')


def save_excel(my_list):
    path = os.getcwd()
    file_path = path + os.sep + 'grade.xls'
    style_bold = xlwt.easyxf('font: color-index red, bold on')
    header_style = style_bold
    workbook = xlwt.Workbook(encoding='utf-8')
    book_sheet = workbook.add_sheet('Sheet 1', cell_overwrite_ok=True)
    i = 0
    for x, item in enumerate(my_list):
        book_sheet.write(i, x, item, header_style)
    workbook.save(file_path)


def xlutil(my_list):
    try:
        path = os.getcwd()
        file_path = path + os.sep + 'grade.xls'
        rexcel = open_workbook(file_path, formatting_info=True)  # 用wlrd提供的方法读取一个excel文件
        rows = rexcel.sheets()[0].nrows  # 用wlrd提供的方法获得现在已有的行数
        print "rows:", rows
        first_row = ['title', 'pub_time', 'aid', 'view', 'danmaku', 'favorite', 'coin', 'share', 'author', 'sex']
        style_bold = xlwt.easyxf('font: color-index red, bold on')
        header_style = style_bold
        excel = copy(rexcel)  # 用xlutils提供的copy方法将xlrd的对象转化为xlwt的对象
        table = excel.get_sheet(0)  # 用xlwt对象的方法获得要操作的sheet
        if rows == 0:
            for y, value in enumerate(first_row):
                table.write(rows, y, value.decode('utf-8'), header_style)
            excel.save(file_path)
        else:
            for y, value in enumerate(my_list):
                table.write(rows, y, value.decode('utf-8'))
            excel.save(file_path)
    except Exception, e:
        print e
        print "请先关闭grade.xls"


if __name__ == '__main__':
    # path = os.getcwd()
    # file_path = path + os.sep + 'grade.xls'
    # w = xlwt.Workbook(encoding='utf-8')
    # ws = w.add_sheet('Sheet 1', cell_overwrite_ok=True)
    # w.save(file_path)
    my_list = ['34', '2017-10-16 14:42', '44', '67', '1', '3', '0', '0', '69', '33']
    # save_excel(my_list)
    xlutil(my_list)