# 写入excel2007
title = ['id', 'name']
datas = [[1,'张三']，[2,'李四']]
create_excel07(title, datas, '雇员', dir='D:\\')
# 读取前两列
read_excel07('D:\\雇员2019-06-09.xlsx', column_end=2)

import os
import traceback

import openpyxl
import xlrd
from xlutils.copy import copy

from .mytool import get_today_str, make_dir


def int_cell_value(cell):
    """
    处理单元格整数读取的时候变成小数问题
    :param cell: 单元格
    :return: 单元格的值
    """
    cell_value = cell.value
    ctype = cell.ctype
    if ctype == 2 and cell_value % 1 == 0.0:
        cell_value = int(cell_value)
    return cell_value


def excel_append(column_title, column_content, excel_file, sheet_name):
    """
    excel文件追加
    :param column_title:excel第一行标题
    :param column_content: 内容
    :param excel_file: 文件路径
    :param sheet_name: sheet名
    :return:追加失败返回False
    """
    if not excel_file:
        print("请输入文件名！")
        return False
    if not os.path.exists(excel_file):
        print("excel文件不存在")
        return False
    try:
        r_xls = xlrd.open_workbook(excel_file)
        w_xls = copy(r_xls)
        sheet = w_xls.add_sheet(str(sheet_name))
        for i in range(len(column_title)):
            sheet.write(0, i, column_title[i])
        for j in range(len(column_content)):
            for k in range(len(column_content[j])):
                sheet.write(j + 1, k, str(column_content[j][k]))
        w_xls.save(excel_file)
        print(excel_file, '文件追加完成')
        return True
    except:
        traceback.print_exc()
        print("excel追加写入失败！")
        return False


def create_excel07(column_title, column_content, file_name='', sheet_name='Sheet1', dir=''):
    """
    2007表格生成,column_title, column_content必填
    :param column_title: excel第一行标题
    :param column_content: excel数据内容
    :param file_name: 文件名（默认添加日期.xlsx）
    :param sheet_name: sheet名称（默认Sheet1）
    :param dir: 文件目录
    :return: True新建成功/False 创建失败
    """
    try:
        wbk = openpyxl.Workbook()
        ws = wbk.active
        ws.title = sheet_name
        for i in range(len(column_title)):
            ws.cell(row=1, column=i + 1).value = column_title[i]
        for j in range(len(column_content)):
            for k in range(len(column_content[j])):
                ws.cell(row=j + 2, column=k + 1).value = str(column_content[j][k])
        excel_name = get_today_str() + '.xlsx'
        if file_name:
            excel_name = file_name + excel_name
        if dir:
            make_dir(dir)
            if not dir.endswith('/'):
                dir += '/'
            excel_name = dir + excel_name
        wbk.save(excel_name)
        print(excel_name + "导出成功")
        return True
    except:
        traceback.print_exc()
        return False


def read_excel07(file_name='', column_start=1, column_end=0, sheet_name='Sheet1'):
    """
    读取excel2007中column_start列到column_end的数据
    :param file_name: 文件名
    :param column_start: 默认第一列
    :param column_end: 默认最后一列
    :param sheet_name: 工作簿的名字
    :return:
    """
    sheet = openpyxl.load_workbook(file_name).get_sheet_by_name(sheet_name)
    if not column_start:
        column_start = 1
    if not column_end:
        column_end = sheet.max_column
    datas = []
    for i in range(1, sheet.max_row + 1):
        data = []
        for j in range(column_start, column_end + 1):
            data.append(sheet.cell(i, j).value)
        datas.append(data)
    return datas