操作-写入excel

xlwt模块

封装

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import xlwt
import xlrd
from xlutils.copy import copy

class ExcelWrite(object):
    def __init__(self, dir, file):
        self.file_name = dir + file

    def set_style(self, name, colour_index, height, underline=False, italic=False, bold=False):
        """
        :param name:            字体，如微软雅黑，Times New Roman等类型字体
        :param colour_index:    字体颜色，如4
        :param height:          字体大小
        :param underline:       下划线
        :param italic:          斜体
        :param bold:            粗体
        :return:                返回字体样式
        """
        style = xlwt.XFStyle()  # 初始化样式
        font = xlwt.Font()  # 创建字体
        font.name = name  # 设置字体类型
        font.colour_index = colour_index  # 字体颜色
        font.height = height  # 字体大小
        font.underline = underline
        font.italic = italic
        font.bold = bold  # 粗体
        style.font = font
        return style

    """
    按行插入数据
    [["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"]]
    """

    def write_excel_by_rows(self, rows, sheet, style=None):
        f = xlwt.Workbook()
        sheet = f.add_sheet(sheet, cell_overwrite_ok=True)
        rows_length = len(rows)
        for i in range(0, rows_length):
            row = rows[i]
            row_length = len(row)
            for j in range(0, row_length):
                if style is None:
                    sheet.write(i, j, row[j])
                else:
                    sheet.write(i, j, row[j], style)

        f.save(self.file_name)

    """
    按列插入数据
    [["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"],["姓名", "年龄", "出生日期", "爱好"]]
    """

    def write_excel_by_columns(self, columns, sheet, style=None):
        f = xlwt.Workbook()
        sheet = f.add_sheet(sheet, cell_overwrite_ok=True)
        columns_length = len(columns)
        for i in range(0, columns_length):
            column = columns[i]
            column_length = len(column)
            for j in range(0, column_length):
                if style is None:
                    sheet.write(j, i, column[j])
                else:
                    sheet.write(j, i, column[j], style)

        f.save(self.file_name)

    # 追加值
    def append_value(self, index_or_name, row, col, value):        # 知识点
        """
        :param index_or_name: 第几个表格或者表格的名字
        :param row:         第几行-1
        :param col:         第几列-1
        :param value:       填入的值
        """
        wb = xlrd.open_workbook(filename=self.file_name)
        write_data = copy(wb)
        write_data.get_sheet(index_or_name).write(row, col, value)  # 行，列，值
        write_data.save(self.file_name)

设置单元格宽度

sheet.col(0).width = 300 #设置某列的单元格宽度

写入时间格式

import xlwt
import datetime
#初始化一个excel
excel = xlwt.Workbook(encoding='utf-8')
#新建一个sheet
sheet = excel.add_sheet('xlwt_sheet1')
style = xlwt.XFStyle()
style.num_format_str = 'M/D/YY'
sheet.write(2,0,datetime.datetime.now(),style)
excel.save('C:/Users/ms/Desktop/test_xlwt.xls')

# 写入指定时间
date_time = datetime.datetime.strptime('2013-08-28','%Y-%m-%d') 
sheet.write(2,0,date_time,style)  

合并单元格

 write_merge的用法：sheet1.write_merge(6, 6, 1, 2, '内容',style)
 
 # write_merge(x, x + h, y, w + y, string, style)
 
 # x表示行，y表示列，w表示向右跨列个数，h表示向下跨行个数
 
 x=6,h=0,y=1,w=1
 
 insert_bitmap(img, x, y, x1, y1, scale_x=0.8, scale_y=1)同理