python - 如何使用 xlsxwriter 在没有精确行的情况下迭代写入数据 :column coordinate?

Question

我正在尝试用 xlsxwriter 替换 csv(我需要文档中某些行的样式功能)，因此我尝试在 xlsxwriter 中重新实现此逻辑:

    with open('text.csv', 'wb') as file_csv:
        fieldnames = ["Date", "Advertiser", "Offer", "Affiliate", "Affiliate Source", "Impressions", "Clicks", "Cost click", "Conversions", "Cost", "Revenue", "Profit", "Real profit"]
        writer = csv.DictWriter(file_csv, delimiter=",", fieldnames=fieldnames)
        writer.writeheader()
        for d in data:
            real_profit = str(float(d['Stat']['profit']) - (int(d['Stat']['clicks']) * 0.0004))
            writer.writerow({"Date": d['Stat']['date'], "Advertiser": d['Advertiser']['company'], "Offer": d['Offer']['name'], "Affiliate": d['Affiliate']['company'], "Affiliate Source": d['Stat']['source'], "Impressions": d['Stat']['impressions'], "Clicks": d['Stat']['clicks'], "Cost click": str(int(d['Stat']['clicks']) * 0.0004), "Conversions": d['Stat']['conversions'], "Cost": d['Stat']['payout'], "Revenue": d['Stat']['revenue'], "Profit": d['Stat']['profit'], "Real profit": real_profit})
        totals = response['data']['totals']['Stat']
        writer.writerow({"Date": "Totals", "Impressions": totals['impressions'], "Clicks": totals['clicks'], "Conversions": totals['conversions'], "Cost": totals['payout'], "Revenue": totals['revenue'], "Profit": totals['profit']})

如何在 for 循环中使用 xlsxwriter 迭代实现它并最后写入总行？

Answer 1

DictWriter 只是一个从数组 fieldnames 中查找列编号的包装器。您可以自己编写这样的包装器:

class DictWriter:
  def __init__(self, worksheet, fieldnames):
    self.worksheet =worksheet
    self.fieldnames = fieldnames
    self.nrow = 1

  def writerow(self, d):
    for k in d:
       ncol = self.fieldnames.index(k)
       self.worksheet.write(self.nrow, ncol, d[k])
    self.nrow += 1

  def writeheader(self):
    for ncol , fieldname in enumerate(self.fieldnames):
      self.worksheet.write(0, ncol, fieldname )

用法:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
fieldnames = [...]
writer = DictWriter(worksheet, fieldnames)
writer.writeheader()

# below comes your code 
for d in data:
  real_profit = str(float(d['Stat']['profit']) - (float(d['Stat']['clicks']) * 0.0004))
  writer.writerow({"Date": d['Stat']['date'], "Advertiser": d['Advertiser']['company'], "Offer": d['Offer']['name'], "Affiliate": d['Affiliate']['company'], "Affiliate Source": d['Stat']['source'], "Impressions": d['Stat']['impressions'], "Clicks": d['Stat']['clicks'], "Cost click": str(int(d['Stat']['clicks']) * 0.0004), "Conversions": d['Stat']['conversions'], "Cost": d['Stat']['payout'], "Revenue": d['Stat']['revenue'], "Profit": d['Stat']['profit'], "Real profit": real_profit})