#!/usr/bin/python

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

import os

import xlwt

import xlrd

excel_Path = (r"D:\python\The company plug-in\10.13.xlsx")

data = xlrd.open_workbook(excel_Path)

table = data.sheets()[0]

table = data.sheet_by_index(0)

# table = data.sheet_by_name(u"03")

a = table.row_values(2)   #整行数据

a = table.col_values(4)   #整列数据

# for i in a:

#     print i.encode("gb2312") #表格读取中文是要加这个进行转码

# nrows = table.nrows

# ncols = table.nclos

def set_style(name, height, bold=False):

    style = xlwt.XFStyle()  #初始化样式

    font = xlwt.Font()  #为样式创建字体

    font.name = name    #"Times New Roman"

    font.bold = bold

    font.color_index = 4

    font.height = height

    style.font = font

    return style

def write_excel():

    f = xlwt.Workbook() #创建工作薄

    """

    创建工作薄

    """

    sheet1 = f.add_sheet(u"sheet1", cell_overwrite_ok=True)   #创建sheet

    row0 = [u"业务", u"状态", u"北京", u"上海", u"广州", u"深圳",u"状态小计", u"合计"]   #列   输入内容列表

    column0 = [u"机票", u"船票", u"火车票", u"汽车票", u"其它"]

    status = [u"预定", u"出票", u"退票", u"业务小计"]

    for i in range(0, len(row0)):

        sheet1.write(0, i, row0[i], set_style("Times New Roman", 300, True))#1：竖着坐标  2：横着坐标 3：写入内容 4：设置字体和字体大小

        # sheet1.write_merge(1, 1, 6, 6)#5

        # sheet1.write_merge(1, 3, 6, 6)#6 # ()括号里的数值 分别为 1：  2：竖着数几行  3：

        # sheet1.write_merge(1, 5, 5, 6)# 7 # ()括号里的数值 分别为 1：  2：竖着数几行  3和4：从那列到那列进行合并

        # sheet1.write_merge(0, 5, 5, 6)# 8 # ()括号里的数值 分别为 1：竖着从第几行开始  2：竖着数几行格式化  3和4：从那列到那列进行合并

    i, j = 1, 0

    while i < 4 * len(status) and j < len(column0):

        # print "yes"

        sheet1.write_merge(i, i + 3, 0, 0, column0[j], set_style("Times New Roman", 250, True))

        #分别为 1：竖着从第几行开始  2：竖着数几行格式化  3和4：从那列到那列进行合并 5：写入内容  6：设置字体和大小

        sheet1.write_merge(i, i + 3, 7, 7,)  # 最后一列"合计"

        i += 4

        j += 1

    i = 0

    while i < len(status) * len(status):

        for a in status:

            # print status[i]

            sheet1.write(i + 1, 1, a, set_style("Times New Roman", 200, True))

            i += 1

    print i

    sheet1.write_merge(23, 23, 0, 1, row0[7], set_style("Times New Roman", 300, True))

    f.save("test23.xlsx")

#     #生成第一行

#

#     for i in range(0, len(row0)):

#         # print (0, i, row0[i], set_style("Times New Roman", 220, True))

#         sheet1.write(0, i, row0[i], set_style("Times New Roman", 220, True))

#

#     #生成第一列和最后一列(合并4行)

#     i, j = 1, 0

#     while i < 4 * len(column0) and j < len(column0):

#         sheet1.write_merge(i, i+3, 0, 0, column0[j], set_style("Arial", 220, True))    #第一列

#         sheet1.write_merge(i, i + 3, 7, 7,) #最后一列"合计"

#         i += 4

#         j += 1

#     sheet1.write_merge(21, 21, 0, 1, u"合计", set_style("Times New Roman", 220, True))

#

#     #生成第二列

#     i = 0

#     while i < 4 * len(column0):

#         for j in range(0, len(status)):

#             sheet1.write(j + i + 1, 1, status[j])

#         i += 4

#     f.save("demo1.xlsx") #保存文件

#

if __name__ == "__main__":   # 测试，判断是不是被人导入，被人当模块使用的时候 就等于文件名

    #generate_workbook()

    #read_rxcel()

    write_excel()

# for i in os.listdir(excel_Path):

#     if "20170602_A" in i:

#         j = os.path.join(excel_Path, i)

#         # k = xlrd.open_workbook(j)

#         print j