python - 将数据写入xls并在python-django中下载

Question

我已成功创建 .csv 文件，现在想要为相同的数据创建 .xls。之后我也想下载它。因此，在从 python 创建 csv 文件后，我将响应发送到 ajax 函数并使用 jquery 从那里下载它。

def expense_export(request):
print(request.POST)
if request.is_ajax():
    ids = request.POST.getlist('ids[]')
    expenses = Expense.objects.filter(id__in=ids)
    data = []
    field = ['SLNO', 'Date of Recording', 'Category', 'Sub Category', 'Invoice Date', 'Invoice No',
             'GST No. Mentioned', 'Vendor Name', 'Details', 'Gross Value', 'SGST', 'CGST', 'IGST',
             'Total Invoice Value', 'TDS(if any)', 'Net Payble']
    field1 = ['', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '']

    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="Search Results.csv"'
    sno = 1
    max = 0
    for record in expenses:
        pay_data = []
        if record.record:
            curr = 0
            for pay_record in record.record:
                pay_row = [pay_record['date'], pay_record['amount'], pay_record['mode'], pay_record['ref'],
                           pay_record['bank']]
                pay_data = pay_data + pay_row
                curr = curr + 1
                if curr > max:
                    max = curr
        gst_exist = 'No'
        if record.vendor:
            if record.vendor.gst_no:
                gst_exist = 'Yes'
        igst = int(record.gst) / 100 * record.amount
        tds = int(record.tds) / 100 * record.amount
        net_amount = int(record.amount) + int(igst)
        row = [
            sno, record.timestamp.strftime('%d-%m-%Y'), record.expense_name, record.category,
            record.invoice_date, record.invoice_no, gst_exist, record.vendor_name,
            record.remark, record.amount, igst / 2, igst / 2, igst, net_amount, tds, net_amount - tds,
        ]
        row = row + pay_data
        data.append(row)
        sno = sno + 1
    print(max)
    for i in range(0, max):
        extra_field = ['Date of Payment', 'Amount Paid', 'Mode of Payment', 'Ref No.', 'Bank Account No.']
        extra_field1 = ['Payment Details ' + str(i + 1), '', '', '', '']
        field = field + extra_field
        field1 = field1 + extra_field1

    writer = csv.writer(response)  # Initialization
    writer.writerow(field1)  # Header 1
    writer.writerow(field)  # Header 2
    writer.writerows(data)  # Rows

    return response
    # return workbook

和 Jquery 函数

    $(document).on('click', '#expense_export', function () {
    // $('#overlay').show();
    ids = [];
    $('#expense_table tbody tr').each(function () {
        ids.push($(this).find('td:first').text());
    });
    $.ajax({
        type: 'post',
        url: '/invoice/expense_export/',
        data: {
            'ids': ids
        },
        enctype: 'multipart/form-data',
        success: function (result) {
            console.log(result);
            // $('#overlay').hide();
            var blob = new Blob([result]);
            var link = document.createElement('a');
            link.href = window.URL.createObjectURL(blob);
            link.download = 'QuaExpenses.csv';
            link.click();
        }
    });
});

现在我知道如何在 python 中创建 .xls 文件

        writer = csv.writer(response)  # Initialization
    writer.writerow(field1)  # Header 1
    writer.writerow(field)  # Header 2
    writer.writerows(data)  # Rows

    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("Sheet Name")
    style = xlwt.easyxf('font: bold 1, color red;')

    for i in range(len(field1)):
        sheet.write(0, i, field1[i], style)
    for i in range(len(field)):
        sheet.write(1, i, field[i], style)

    workbook.save('example.xls')

但我无法理解如何发送此工作簿作为响应，就像我对 .csv 文件所做的那样。

有人可以帮忙吗？

我还可以在 csv 文件的单元格中添加样式，例如背景颜色吗？

谢谢

Answer 1

csv 格式没有可用的样式。他们是Comma-separated values

要让 django 返回 Excel 文件，请使用 FileResponse您在其中创建 .xls 文件的 View 中的对象。

import io
from django.http import FileResponse

def some_view(request):
    # Create a file-like buffer to receive PDF data.
    buffer = io.BytesIO()

    # Create the Exel
    workbook = xlwt.Workbook()
    # add data
    # save to buffer
    workbook.save(buffer)

    # FileResponse sets the Content-Disposition header so that browsers
    # present the option to save the file.
    buffer.seek(0)
    return FileResponse(buffer, as_attachment=True, filename='example.xls')

如果使用旧版本的 Django，其中 as_attachment 参数不可用。下面的代码应该可以工作。

from django.http import HttpResponse

def some_view(request):
    # Create the HttpResponse object with the appropriate headers.
    response = HttpResponse(content_type='application/vnd.ms-excel')
    response['Content-Disposition'] = 'attachment; filename="example.xls"'

    # Create the Exel
    workbook = xlwt.Workbook()
    # add data
    # save to buffer
    workbook.save(response)
    return response