1,985

python操作Excel之合并单元格、单元格样式设置——xlsxwriter

最近需要对Web前端DataTable表格进行导出下载,针对中规中矩没有合并单元格、且没有任何样式的表格可以直接用已有的JS框架下载导出。

下面也展示一个小Demo,前端代码:

Name Position Office Age Start date Salary
Name Position Office Age Start date Salary
Tiger Nixon System Architect Edinburgh 61 2011/04/25 $320,800
Garrett Winters Accountant Tokyo 63 2011/07/25 $170,750
Ashton Cox Junior Technical Author San Francisco 66 2009/01/12 $86,000
Cedric Kelly Senior Javascript Developer Edinburgh 22 2012/03/29 $433,060
Airi Satou Accountant Tokyo 33 2008/11/28 $162,703
Brielle Williamson Integration Specialist New York 61 2012/12/02 $372,000
Herrod Chandler Sales Assistant San Francisco 59 2012/08/06 $137,500
Rhona Davidson Integration Specialist Tokyo 55 2010/10/14 $327,900
Colleen Hurst Javascript Developer San Francisco 39 2009/09/15 $205,500
Sonya Frost Software Engineer Edinburgh 23 2008/12/13 $103,600
Jena Gaines Office Manager London 30 2008/12/19 $90,560
Quinn Flynn Support Lead Edinburgh 22 2013/03/03 $342,000
Charde Marshall Regional Director San Francisco 36 2008/10/16 $470,600
Haley Kennedy Senior Marketing Designer London 43 2012/12/18 $313,500
Tatyana Fitzpatrick Regional Director London 19 2010/03/17 $385,750

事先先导入必要的JS库:

jquery.min.js、bootstrap.min.js、jquery.dataTables.min.js、dataTables.buttons.min.js、buttons.html5.min.js

							

"use strict"; 
$(document).ready(function() {
	$('#example').DataTable( {
		dom: 'Bfrtip',
		buttons: [
			'csv', 'excel'
		]
	});
});

如果想自定义下载文件的标题,按钮文字。那可以这种实例化DataTable:

$('#example').DataTable({
   dom: 'Bfrtip',
       buttons: [
            {
                extend: 'excel',
                text: '导出数据',
                title: '标题', //导出的excel标题
            },
        ],
    "aaSorting": [], //是否排序["desc", "asc"]
   'paging': true, //是否导出分页内容 true、false
});
    

结果如下图:

结果

这种方式很便捷,但是不能导出合并单元格的数据。比如下面这个表格

这种表格要是用上面那种JS框架的方式行不通,会报错,报错信息如下:

错误信息

针对这种表格的导出,我采用的后台先生成好Excel再下载,具体Demo后台接口代码:

import xlsxwriter

@test.route('/export_depot', methods=['POST', 'GET'])
@login_required
def export_depot():
	time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    name = time + str(current_user.id)
    path = BASE_PATH + "/app/static/download/"+name+".xls"
    load_path = "/static/download/"+name+".xls"

    files = xlsxwriter.Workbook(path)  # 创建excel文件
 
    worksheet1 = files.add_worksheet('sheet1')  # 括号内为工作表表名
     
    bold = files.add_format({
        'bold': True,  # 字体加粗
        'border': 1,  # 单元格边框宽度
        'align': 'center',
        'valign': 'vcenter',  # 字体对齐方式
        'fg_color': '#F4B084',  # 单元格背景颜色
        'text_wrap': True,  # 是否自动换行
        'size': 16 # 字体大小设置
    })
  	# 设置单元格宽度(0,0,20)表示第一列宽度设置成20,(1, 1, 50)表示第二列,以此类推。
    worksheet1.set_column(0, 0, 20) 
    worksheet1.set_column(1, 1, 50)
    worksheet1.set_column(2, 2, 25)
    worksheet1.set_column(3, 3, 25)
    worksheet1.set_column(4, 4, 25)

    headings = ['收入', '运营费用', '开发采购支出', '物流仓储支出', '往来支出', '行政费用支出']
    third_head = ['收入', '支出']
    worksheet1.set_column('A:C', 12)  # 设置B-D的单元格宽度为12
    worksheet1.set_row(0, 50)  # 设置第2行的高度为30
    for i in range(1, 100):
        worksheet1.set_row(i, 20)
    # merge_range('A1:E1','A1到E1合并成单元格') 表示合并单元格
    worksheet1.merge_range('A1:E1', "2019年10月资金流向汇总表", bold)
    worksheet1.merge_range('A2:C2', "编制单位:福州唐龙电子商务有限公司", bold)
    # write('E2', "单位:元") 表示常规填写单个单元格
    worksheet1.write('D2', "", bold)
    worksheet1.write('E2', "单位:元", bold)
    worksheet1.merge_range('A3:A4', "大类", bold)
    worksheet1.merge_range('B3:B4', "小类", bold)
    worksheet1.merge_range('C3:D3', "本月发生额", bold)
    worksheet1.write('E3', "余额", bold)
    worksheet1.write('C4', "收", bold)
    worksheet1.write('D4', "支", bold)
    worksheet1.merge_range('A5:D5', "期初余额", bold)
    worksheet1.merge_range('E4:E5', float("%.2f" % init_balance), bold) # 银行期初
    files.close()


    results = {'success': 'success', 'path': load_path}

    return json.dumps(results)

JS代码:

$(function(){
    $('#export_data').on('click',function(){
        swal({   
              title: "数据导出中!",   
              text: "请耐心等待...",  
              showConfirmButton: false 
          });
        $.ajax({
            url:'{{ url_for("test.export_depot") }}',
            method:'POST'
            success: function(response){
                var success = $.parseJSON(response)['success']
                var path = $.parseJSON(response)['path']
                if(success == 'success'){
                    swal({   
                      title: '导出成功',   
                      type: "success",
                      timer: 1500,
                      showConfirmButton: false
                  });
                    window.location.href = path
                }else{
                    alert('没有数据可导出!!!')
                }
            }
        })
    })
})

合并结果

这种方式是自己亲测的结果,可以实现特殊单元格的下载导出,做个总结方便后期查阅,如有疑议请留言。

有时候把链接或者想在某个单元格设置超链接写入EXCEL,一开始我直接用:

def setStyle(name, height, color, bgcolor, bold=False):
    style = xlwt.XFStyle()  # 初始化样式
    badBG = xlwt.Pattern()
    borders = xlwt.Borders()
    font = xlwt.Font()  # 为样式创建字体
    # 字体类型:比如宋体、仿宋也可以是汉仪瘦金书繁
    font.name = name
    # 设置字体颜色
    font.colour_index = color
    # 字体大小
    font.height = height
    # 定义格式
    style.font = font
    #设置背景色
    badBG.pattern = badBG.SOLID_PATTERN
    badBG.pattern_fore_colour = bgcolor
    style.pattern = badBG
    borders.left = xlwt.Borders.MEDIUM  # 添加边框-虚线边框
    borders.right = xlwt.Borders.MEDIUM  # 添加边框-虚线边框
    borders.top = xlwt.Borders.MEDIUM  # 添加边框-虚线边框
    borders.bottom = xlwt.Borders.MEDIUM  # 添加边框-虚线边框
    borders.left_colour = 0 # 边框上色
    borders.right_colour = 0
    borders.top_colour = 0
    borders.bottom_colour = 0
    style.borders = borders

    return style
def writeExcel():
	file = xlwt.Workbook(encoding='utf8', style_compression=2)  # 创建工作簿
	#创建sheet,并指定可以重复写入数据的情况.设置行高度
	sheet1 = file.add_sheet(u'sheet', cell_overwrite_ok=False)
	link = 'HYPERLINK("%s";"%s")' % ("链接网址", 单元格内容)
	sheet1.write(0, 1, link, setStyle('宋体', 250, 颜色, 22, False))

以上这种方式虽然可以正常写入,但是最终结果是单元格会显示”‘HYPERLINK(“链接网址”;”单元格内容”)'”,而且需要双击单元格才可以正常跳转,这显然不是预期的效果,经改良后,应该把写入的方式改成:

	sheet1.write(0, 1, xlwt.Formula(link), setStyle('宋体', 250, 颜色, 22, False))

经过以上的改良就可以正常写入超链接了。最后附加一个关于取两字符的相似度的三方库实例:

import difflib #相似度三方库
def get_similar(str1, str2):
    return difflib.SequenceMatcher(None, str1.upper(), str2.upper()).quick_ratio()
实例:
>>> import difflib
>>> a='python'
>>> b='IpythonCode'
>>> c=difflib.SequenceMatcher(None, a, b).quick_ratio()
>>> c
0.7058823529411765
>>>

注:本文为原创作品,如需转载请注明出处 http://www.lhcx821.com/?p=578。

发表评论

邮箱地址不会被公开。 必填项已用*标注