#! /usr/bin/python
# -*- coding:utf-8 -*-

import xlrd
import xlwt

'''
功能场景：excel写入数据,读取数据
说明：工作簿是excel文件，只有一个，sheet是底下各种子表，可以有多个
'''


# 设置样式
def set_style(name, height, bold=False):
    '''
    :param name:名称
    :param height: 高度
    :param bold: 粗体
    :return:
    '''
    style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = name
    font.bold = bold
    font.height = height
    font.colour_index = 4

    style.font = font
    return style


# 创建excel并写入数据
def write_excel(*args):
    '''
    :param path:
    :return:
    '''
    # 创建工作簿
    workbook = xlwt.Workbook(encoding="utf-8")
    # 创建sheet
    sheet1 = workbook.add_sheet("test1")

    field = ["姓名", "年龄", "身高", "工作"]
    data = [("张三", 20, 180, "程序猿"), ("李四", 21, 190, "测试"), ("王五", 22, 1750, "前端")]

    # 写入字段
    lenght = len(field)
    for i in range(lenght):
        sheet1.write(0, i, field[i], set_style("Times New Roman", 220, True))

    # 写入多行数据
    for v in range(len(data)):
        for k in range(lenght):
            sheet1.write(v + 1, k, data[v][k], set_style("Times New Roman", 220, True))     # 跳过第一行

    # 合并单元格写入,开始行、结束行、开始列、结束列、值、样式
    # sheet1.write_merge(r1, r2, c1, c2, value, style)
    # 示例：sheet1.write_merge(1, 2, 0, 0, '内容')     合并第二行到第三行，第一列

    # 保存文件，可选择路径，path
    workbook.save("demo.xls")


###########   读取数据    ############
def read_excel(path):
    # 打开文件
    # xlrd.open_workbook(filename=None, file_contents=file.read())  读取excel文件流的数据，前端传来的excel文件可这样读取
    rb = xlrd.open_workbook(filename=path)
    names_list = rb.sheet_names()             # sheet子表的名称
    sheet1 = rb.sheet_by_index(0)             # 通过索引获取表格
    sheet2 = rb.sheet_by_name("test1")        # 通过名字获取表格
    print(sheet1.name,sheet1.ncols,sheet1.nrows)
    print(sheet2.name,sheet2.ncols,sheet2.nrows)

    # 获取整行整列数据（返回列表）
    rows = sheet1.row_values(0)        # 获取第一行内容
    cols = sheet1.col_values(1)        # 获取第二列内容
    print(rows,cols)

    # 获取单元格内容（三种方式）
    way1 = sheet1.cell(0,1).value
    way2 = sheet1.cell_value(0,1)
    way3 = sheet1.row(1)
    way4 = sheet1.row_values(1,0,4)
    print(way1,way2,way3,way4)

    # 获取单元格内容类型
    way1 = sheet1.cell(0, 1).ctype  # ctype :  0 empty，1 string，2 number， 3 date，4 boolean，5 error
    print(way1)

if __name__ == '__main__':
    # 可设置路径
    ############ 写入 ##########
    # path = ""
    # write_excel(path)
    # print("创建成功")
    ############ 读取 #########
    read_excel("demo.xls")

优化后，支持超过65535

# -*-coding:utf-8 -*-

import xlrd
import xlwt

'''
功能场景：excel写入数据,读取数据
说明：工作簿是excel文件，只有一个，sheet是底下各种子表，可以有多个
'''


# 设置样式
def set_style(name, height, bold=False):
    '''
    :param name:名称
    :param height: 高度
    :param bold: 粗体
    :return:
    '''
    style = xlwt.XFStyle()  # 初始化样式
    font = xlwt.Font()  # 为样式创建字体
    font.name = name
    font.bold = bold
    font.height = height
    font.colour_index = 4

    style.font = font
    return style


# 创建excel并写入数据
def write_excel(path, title, data):
    '''
    :param path: 保存路径
    :param title: 表头
    :param data: 写入数据
    :return:
    '''
    # 创建工作簿
    workbook = xlwt.Workbook(encoding="utf-8")

    sheet_num = 1
    data_lenght = len(data)
    print data_lenght
    title_lenght = len(title)

    if data_lenght > 65534:
        sheet_num = (data_lenght / 65534) + 1

    for index in range(sheet_num):

        # 创建sheet
        sheet_name = "sheet" + str(index + 1)
        sheet1 = workbook.add_sheet(sheet_name)

        _style= set_style("Times New Roman", 220, True)

        # 写入表头
        for i in range(title_lenght):
            sheet1.write(0, i, title[i], _style)

        # 写入多行数据
        new_data = data[index * 65534: (index+1) * 65534]
        new_data_lenght = len(new_data)
        for v in range(new_data_lenght):
            for k in range(title_lenght):
                sheet1.write(v + 1, k, new_data[v][k], _style)  # 跳过第一行

    # 保存文件，可选择路径，path
    workbook.save(path)



if __name__ == '__main__':
    # 可设置路径
    ############ 写入 ##########
    path = "demo.xlsx"
    title = ["姓名", "年龄", "身高", "工作"]
    data = [("张三", 20, 180, "程序猿"), ("李四", 21, 190, "测试"), ("王五", 22, 1750, "前端")] * 65535
    write_excel(path, title, data)
    # print("创建成功")
    ############ 读取 #########
    # read_excel("demo.xlsx")

# 若是django需要读取excel存储到数据库中，举例
user_list = [User(name=read[0],sex=read[1])]   # 批量创建时传列表，里边是单个对象
User.objects.bluk_create()

openpyxl

# -*-coding:utf-8 -*-

import traceback
import openpyxl

from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, colors, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

"""
office excel 2003及以下版本最大行数为65536行，最大列数256列；
office excel 2007及以上版本最大行数为1048576行，最大列数为16384列。
"""


# 读取文件示例
def eg():
    # 打开一个工作簿
    workbook = openpyxl.load_workbook("openpyxl.xlsx")

    # 获取所有的sheet名称
    sheet_names = workbook.get_sheet_names()
    print("读取的sheets：%s" % sheet_names)

    # 使用名称获取某个sheet对象
    sheet1 = workbook.get_sheet_by_name("Sheet1")
    sheet2 = workbook.get_sheet_by_name("Sheet2")

    #### openpyxl的索引是在1开始，不同于xlwt

    # 获取表格的值
    print(u"第一行、第一列的值：%s" % sheet1.cell(1, 1).value)

    # 给sheet中写入值
    sheet2.cell(1, 1).value = "列1"

    # 获取总行数
    rows = sheet1.max_row
    print("总行数：%s" % rows)

    # 获取总列数
    cols = sheet1.max_column
    print("总列数：%s" % cols)

    workbook.save("new_openpyxl.xlsx")


# 传入固定格式数据，写入数据
def write_excel(datas, file_name="write.xlsx", sheet_name="Sheet", is_create_sheet=False, height=40, width=30):
    """
    :param datas: [["表头1", "表头2", "表头3"], [value1, value2, value3] ]
    :param sheet_name: 当is_create_sheet为真时，必须传sheet_name
    :param is_create_sheet: 是否创建新的sheet页，当is_create_sheet为真时，必须传sheet_name
    :param height: 行高
    :param width: 列宽
    """
    data_lenght = len(datas)
    # 超过65536，直接提示错误
    if data_lenght > 65535:
        return {"status": 500, "message": "最多不能导出65535条数据", "data": []}
    try:
        # 实例化对象， 此时会默认生成一个Sheet
        wb = Workbook()

        # sheet对象
        if is_create_sheet:
            ws = wb.create_sheet(sheet_name)
        else:
            ws = wb.active
            ws.title = sheet_name

        # 开始写入
        for r, data in enumerate(datas, 1):
            # 单个单元格写入
            # ws.cell(1, 1, "111")
            # 直接按行写入
            ws.append(data)

        # 设置样式
        rows = data_lenght
        cols = len(datas[0])
        set_style(ws, rows, cols, height, width)

        wb.save(file_name)
    except Exception:
        print(traceback.format_exc())


def set_style(ws, rows, cols, height, width):

    # 设置水平居中，还可以使用right、left，有个全局居中的，用时再研究
    for cell in ws["A:AA"]:
        for i in cell:
            i.alignment = Alignment(horizontal='center', vertical='center')

    myfont = Font(name=u"宋体", size=10, italic=True, color=colors.BLUE)
    # 设置字体
    for cell in ws["A1:AA1"]:
        for i in cell:
            i.font = myfont
    """
    # 应用字体样式到所有单元格
    for row in ws.iter_rows(min_row=1, max_row=len(datas), min_col=1, max_col=len(datas[0])):
        for cell in row:
            cell.font = font_style
    """

    for row in range(1, rows+1):
        # 设置行高
        ws.row_dimensions[row].height = height

    # 设置列宽
    for col in range(1, cols+1):
        # 根据列的数字返回字母
        col_letter = get_column_letter(col)
        ws.column_dimensions[col_letter].width = width

    
    # 创建一个填充样式，设置背景色为红色
    fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")

    # 将填充样式应用到单元格A1
    ws['A1'].fill = fill

    # 添加边框
    border = Border(top=Side(style='thin'),
                    right=Side(style='thin'),
                    bottom=Side(style='thin'),
                    left=Side(style='thin'))
    # 将边框样式应用到单元格A1  
    ws['A1'].border = border





if __name__ == '__main__':
    datas = [["表头1", "表头2", "表头3"], ["value1", "value2", "value3"]]
    write_excel(datas)