python3往excel表中写数据（可以设置文件目录，文件名称

1、前述介绍

我在测试一个智能对话项目时需要评估对话的准确率，就设计了一些问题放到excel表中，读取问题并触发问答后把响应信息按需要的数据写入到另外一个excel中。基于这个，我分别写了读excel函数和写excel函数。
写入文件的内容content示例说明：
格式：{key:[[list]]}，key为sheet名称，value中的最外层的列表元素为行数据，最内层的list元素为列数据
如：

{
   '第1个sheet表':[[第1行第1列内容, 第1行第2列内容, ..., 第1行第n列内容], [第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]],  
   '第2个sheet表':[[第1行第1列内容, 第1行第2列内容, ..., 第1行第n列内容], [第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]], 
   ...
   '第n个sheet表':[[第1行第1列内容, 第1行第2列内容, ..., 第1行第n列内容], [第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]]
}

使用方法、注意事项、参数介绍等均在源码中有注释，不在这里赘述，下面直接看源码。

读excel表文章地址：https://www.jianshu.com/p/f380dbd9cb73

2、源码

# 写excel文件
def writeExcel(writeExcelPath='', writeExcelName='', sheetHeaders=None, content=None):
    import xlwt, datetime, random, os, time, json
    # 参数说明
    # writeExcelPath  写入文件的绝对路径，如果不指定，默认在当前目录下生成文件，如r"E:\test"
    # writeExcelName   生成的文件名称（aa.xls aa.xlsx），如果不指定或者与路径下的文件名称重复，名称默认为当前时间加随机数字

    # excelHeaders  sheet表的表头信息，必须时列表格式，否则表头为空，顺序为列表中元素的顺序，格式：
    # [[sheet1_h1, sheet1_h2, ..., sheet1_hn], [sheet2_h1, sheet2_h2, ..., sheet2_hn], ...]

    # content   要写入的内容，从第二行开始写入，默认为空，格式支持字典格式和json格式。格式：
    # {sheetname1:[第一行[第一列content1, 第二列content2, ...], 第二行[第一列content3, 第二列content4, ...], ...], ...}

    # 注意1：如果路径中出现转义字符，如\t，\n等，路径前面加r，如 r"E:\test\aa.txt"
    # 注意2：不能往已有文件中写入数据，只能新增一个文件
    # 注意3：如果len(sheetHeaders)大于len(content)会生成部分只有表头的sheet，如果两个相等则生成的sheet都带表头，否则会有部分不带表头

    writeExcelPath = str(writeExcelPath)
    writeExcelName = str(writeExcelName)

    try:
        # 获取当前目录
        currentPath = os.path.dirname(os.path.abspath(__file__))

        # 如果sheetHeaders不是列表，转为列表，并将表头中的数字转为字符串
        if not isinstance(sheetHeaders, list):
            sheetHeaders = [[sheetHeaders]]
        if sheetHeaders == []:
            sheetHeaders = [sheetHeaders]
        sheetHeadersTem = []
        for x in range(0, len(sheetHeaders)):
            tem = sheetHeaders[x]
            if not isinstance(tem, list):
                aa = []
                aa.append(str(tem))
                sheetHeadersTem.append(aa)
            else:
                bb = []
                for temItem in tem:
                    bb.append(str(temItem))
                sheetHeadersTem.append(bb)
        sheetHeaders = sheetHeadersTem

        # 如果conten是json格式，转为字典，否则将提示信息写入到excel的第一个sheet中
        if not isinstance(content, dict):
            try:
                content = json.loads(content)
            except:
                # return 'content格式不正确，请按格式设置。'
                content = {
                    '':[
                        ["写入的内容格式不正确，写入失败。"],
                        ["格式：{sheetname1:[第一行[第一列content1, 第二列content2, ...], 第二行[第一列content3, 第二列content4, ...], ...], ...}"],
                        [
                            '''示例：{
                                   '第1个sheet表':[[第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], [第3行第1列内容, 第3行第2列内容, ..., 第3行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]],  
                                   '第2个sheet表':[[第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], [第3行第1列内容, 第3行第2列内容, ..., 第3行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]], 
                                   ...
                                   '第n个sheet表':[[第2行第1列内容, 第2行第2列内容, ..., 第2行第n列内容], [第3行第1列内容, 第3行第2列内容, ..., 第3行第n列内容], ..., [第m行第1列内容, 第m行第2列内容, ..., 第m行第n列内容]]
                                }'''
                        ]
                    ]
                }

        # 处理写入文件的目录和文件名称
        if writeExcelPath == '' or not os.path.isdir(os.path.abspath(writeExcelPath)):
            writeExcelPath = currentPath
        writeExcelPath = writeExcelPath.rstrip(r'\\') + r'\\'

        isFileExist = os.path.isfile(writeExcelPath + writeExcelName)
        isNoName = writeExcelName.split('.')[0]
        isRightExtension = writeExcelName.endswith('.xls') or writeExcelName.endswith('.xlsx')
        if isFileExist or not isNoName or not isRightExtension:
            writeExcelName = datetime.datetime.now().strftime('%Y%m%d%H%M%S') + ''.join(random.sample('04512346789567012389', 4)) + ".xls"

        filePath = writeExcelPath + writeExcelName

        # 设置sheet表头样式
        wb = xlwt.Workbook(encoding='utf-8')
        font = xlwt.Font()
        font.bold = True
        style = xlwt.easyxf()
        style.font = font

        # 处理非字符串类型的sheetName（其他类型的sheetName会报错）
        sheetNameList = list(content.keys())

        # 处理非字符串的sheetname，否则会报错
        for k in range(0, len(sheetNameList)):
            if not isinstance(sheetNameList[k], str):
                content[str(sheetNameList[k])] = content.pop(sheetNameList[k])

        # 按内容中的sheetname的个数和sheet表头的个数来确定sheet表的最大数量
        contentSheets, headerSheets = len(content), len(sheetHeaders)
        if contentSheets > headerSheets:
            for j in range(0, contentSheets - headerSheets):
                sheetHeaders.append([])
        elif contentSheets < headerSheets:
            for jj in range(0, headerSheets - contentSheets):
                sheetNameRand = 'randomSheet' + str(jj+1)
                if sheetNameRand in sheetNameList:
                    sheetNameRand = 'randomSheet' + ''.join(random.sample('019280190192837465', random.randint(1, 3))) + ''.join(random.sample('019280190192837465', random.randint(1, 3)))
                content[sheetNameRand] = []
                sheetNameList.append(sheetNameRand)

        # 准备写入excel
        for i in range(0, len(content)):
            sheetName = str(sheetNameList[i])
            contentList = content[sheetName]
            # sheetName为空时自动生成一个，如果sheetName已存在，则在后面添加加下划线和随机数
            if not sheetName:
                sheetName1 = 'randomSheet'+ ''.join(random.sample('045123467804512346704512346789567012389895670123899567012389', random.randint(1,6)))
                ws = wb.add_sheet(sheetName1)
            else:
                ws = wb.add_sheet(sheetName)

            # 设置sheet表头信息
            if i <= headerSheets:
                for ii in range(0, len(sheetHeaders[i])):
                    ws.write(0, ii, sheetHeaders[i][ii], style)

            # 处理写入的行数据，如果行数据类型不是列表则默认写入第二行第一列的单元格
            if not isinstance(contentList, list):
                ws.write(1, 0, contentList)
                continue
            for row in range(0, len(contentList)):
                # 处理写入的列数据，如果列数据类型不是列表则默认写入当前行第一列的单元格
                if not isinstance(contentList[row], list):
                    ws.write(row+1, 0, contentList[row])
                    continue
                # 写入数据
                for col in range(0, len(contentList[row])):
                    ws.write(row+1, col, contentList[row][col])

        # 生成excel文件
        try:
            f = open(filePath, 'r')
            f.close()
        except IOError:
            f = open(filePath, 'w')
        wb.save(filePath)
        return {'结果': '写文件成功', '文件地址': filePath}
    except Exception as e:
        return {'结果': '写文件失败', '错误信息': e}

3、调用及结果（8种情况）

3.1 正常写入-内容中的sheet比表头中的sheet多

if __name__ == '__main__':
    content = {'商品订单':[['001','002'], ['003','004'], ['005','006', '007']],
               '下单人及支付金额':[['秦始皇',32], ['乾隆',18.96], ['李世民']],
               '商品列表': ['', ['西红柿', '5元/kg'], ['火龙果', '18.9元/kg']]
               }
    sheetHeaders = [['主单号','子单号'],['姓名','金额']]
    bb = writeExcel(writeExcelPath=r'', writeExcelName='', content=content,sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\test\\mytest\\\\202009251709435280.xls'}

打开 202009251709435280.xls 如下

202009251709435280.png

3.2 正常写入-内容中的sheet比表头中的sheet少

if __name__ == '__main__':
    content = {'商品订单':[['001','002'], ['003','004'], ['005','006', '007']],
               '下单人及支付金额':[['秦始皇',32], ['乾隆',18.96], ['李世民']],
               '商品列表': ['人参果', ['西红柿', '5元/kg'], ['火龙果', '18.9元/kg']]
               }
    sheetHeaders = [['主单号','子单号'], ['姓名','金额'], ['商品名称','单价', '规格'], ['日收入总额','周收入总额','月收入总额']]
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\商品及订单信息表.xls'}

打开 商品及订单信息表.xls 如下

商品及订单信息表.png

3.3 正常写入-内容中的sheet与表头中的sheet相等

if __name__ == '__main__':
    content = {'商品订单':[['001','002'], ['003','004'], ['005','006', '007']],
               '下单人及支付金额':[['秦始皇',32], ['乾隆',18.96], ['李世民']],
               '商品列表': ['人参果', ['西红柿', '5元/kg'], ['火龙果', '18.9元/kg']]
               }
    sheetHeaders = [['主单号','子单号'], ['姓名','金额'], ['商品名称','单价', '规格']]
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\202009251730313144.xls'}
说明：由于3.2中已经生成了“商品及订单信息表.xls”，重名的时候会自动生成新文件

打开 202009251730313144.xls 文件如下

202009251730313144.png

3.4 正常写入-写入数据为空

if __name__ == '__main__':
    content = {}
    sheetHeaders = [['主单号','子单号'], ['姓名','金额'], ['商品名称','单价', '规格']]
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\202009251742189129.xls'}

打开 202009251742189129.xls 文件如下

202009251742189129.png

3.5 正常写入-sheet表头为空

if __name__ == '__main__':
    content = {'商品订单':[['001','002'], ['003','004'], ['005','006', '007']],
               '':[['秦始皇',32], ['乾隆',18.96], ['李世民']],
               '商品列表': ['人参果', ['西红柿', '5元/kg'], ['火龙果', '18.9元/kg']]
               }
    sheetHeaders = []
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\202009251749380755.xls'}

打开 202009251749380755.xls 文件如下

202009251749380755.png

3.6 正常写入-写入数据和sheet表头为空

if __name__ == '__main__':
    content = {}
    sheetHeaders = []
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\商品及订单信息表-空表.xls'}

打开 商品及订单信息表-空表.xls 文件如下

商品及订单信息表-空表.png

3.7 格式错误-目录不存在

if __name__ == '__main__':
    content = {}
    sheetHeaders = []
    bb = writeExcel(writeExcelPath=r'E:\aa\bbc', writeExcelName='商品及订单信息表-空表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\test\\mytest\\\\商品及订单信息表-空表.xls'}
说明：E:\aa\bbc目录不存在，默认将文件生成到了项目文件所在的目录下E:\\test\\mytest

目录及打开文件的截图如下

目录不存在.png
商品及订单信息表-空表.png

3.8 格式错误-内容格式错误

if __name__ == '__main__':
    content = 'test'
    sheetHeaders = []
    bb = writeExcel(writeExcelPath=r'E:\aa\bb', writeExcelName='商品及订单信息表.xls', content=content, sheetHeaders=sheetHeaders)
    print(bb)
结果：{'结果': '写文件成功', '文件地址': 'E:\\aa\\bb\\\\202009251802139493.xls'}
说明：由于content给了字符串，不是字典类型，内容写入失败，并将错误信息写入到excel中。（如果使用集成工具，如pycharm，传参不符合类型时会有提示，见下图2）

打开 202009251802139493.xls 文件如下

202009251802139493.png 图2.png

附：E:\aa\bb 和 E:\test\mytest 目录下的文件截图

image.png
image.png