使用python庫(kù)xlsxwriter庫(kù)來(lái)輸出各種xlsx文件的示例

import xlsxwriter

filename = '/Users/piperck/Desktop/axiba.xlsx'
test_book = xlsxwriter.Workbook(filename)
worksheet = test_book.add_worksheet()
test_book.close()

import xlsxwriter

filename = '/Users/piperck/Desktop/axiba.xlsx'
test_book = xlsxwriter.Workbook(filename)
worksheet = test_book.add_worksheet('what')

expenses = (
  ['Rent', 1000],
  ['Gas',  100],
  ['Food', 300],
  ['Gym',  50],
)

# 定義起始的行列 會(huì)在這個(gè)基礎(chǔ)上 行列各加一 作為初始行列
row = 0
col = 0

for item, cost in expenses:
  worksheet.write(row, col, item)
  worksheet.write(row, col+1, cost)
  row += 1

worksheet.write(row, col, '=sum(B0:B4)')
test_book.close()

import xlsxwriter

filename = '/Users/piperck/Desktop/axiba.xlsx'
test_book = xlsxwriter.Workbook(filename)
worksheet = test_book.add_worksheet('what')
bold = test_book.add_format({'bold': True})

test_book.add_format()
expenses = (
  ['Rent', 1000],
  ['Gas',  100],
  ['Food', 300],
  ['Gym',  50],
)

# 定義起始的行列 會(huì)在這個(gè)基礎(chǔ)上 行列各加一 作為初始行列
row = 0
col = 0

for item, cost in expenses:
  worksheet.write(row, col, item, bold)
  worksheet.write(row, col+1, cost)
  row += 1

test_book.close()

#coding=utf-8

def get_excel():
  """
  生成excel
  :return: 
  """
  import xlsxwriter
  workbook = xlsxwriter.Workbook("test.xlsx")
  worksheet = workbook.add_worksheet()
  # 樣式
  formats = Struct() # 字典轉(zhuǎn)化為點(diǎn)語(yǔ)法
  formats.base = {"font_name": u"宋體", "font_size": 11, "align": "center", "valign": "vcenter", "text_wrap": True}
  # formats.condition = dict_merge(formats.base, {"align": "left"})
  formats.bold = {"bold": True} # 加粗
  formats.row = dict_merge(formats.base, {"border": 1})
  formats.first_row = dict_merge(formats.row, {"bold": True}) # 首行
  formats.more_row = dict_merge(formats.row, {}) # 普通行
  formats.more_row_even = dict_merge(formats.row, {"bg_color": "#dddddd"}) # 普通行-奇數(shù)
  # 篩選條件行
  worksheet.merge_range('A1:F1', "") # 合并單元格
  conditions_list = [] # 條件
  province = '省'
  city = '市'
  county = '地區(qū)'
  name = '姓名'
  phone = '電話'
  date = '2018-6'
  if province or city or county:
    area_name = province + city + county
    conditions_list.append(workbook.add_format(formats.bold))
    conditions_list.append(u'地區(qū)：')
    conditions_list.append(u'%s ' % area_name)
  if name:
    conditions_list.append(workbook.add_format(formats.bold))
    conditions_list.append(u'姓名：')
    conditions_list.append(u'%s ' % name)
  if phone:
    conditions_list.append(workbook.add_format(formats.bold))
    conditions_list.append(u'手機(jī)：')
    conditions_list.append(u'%s ' % phone)
  if date:
    year, month = date[0:4], date[5:7]
    conditions_list.append(workbook.add_format(formats.bold))
    conditions_list.append(u'創(chuàng)建時(shí)間：')
    conditions_list.append(u'%s/%s ' % (year, month))
  if conditions_list: # 如果有條件
    worksheet.write_rich_string('A1', *conditions_list) # 首行

  # 表格首行
  cols = ["姓名", "電話", "地區(qū)"]
  for col_index, col in enumerate(cols):
    worksheet.write(1, col_index, col, workbook.add_format(formats.first_row)) # 第二行，col_index列, col_index從0開(kāi)始,也就是第一列開(kāi)始

  data_list = [{"name": "Spencer", "tel": "13888888888", "reg": "中國(guó)"},{"name": "Jeff", "tel": "139999999999", "reg": "臺(tái)灣省"}]
  # 表格其余行
  for row_index, u in enumerate(data_list, start=2): # 因?yàn)榍皟尚卸急徽加昧?，所以從第三行第一列開(kāi)始
    # 斑馬條
    if row_index % 2 != 0:
      row_format = formats.more_row # excel格式普通行
    else:
      row_format = dict_merge(formats.more_row_even) # excel格式奇數(shù)行
    
    # 日期格式
    date_format = dict_merge(row_format, {"num_format": "yyyy/mm/dd hh:mm"})
    # 靠左
    left_format = dict_merge(row_format, {"align": "left"}) 

    # 第一個(gè)參數(shù)：行，第二個(gè)參數(shù)：列，第三個(gè)參數(shù)：數(shù)據(jù)，第四個(gè)參數(shù)：屬性
    worksheet.write(row_index, 0, u['name'], workbook.add_format(row_format))
    worksheet.write(row_index, 1, u['tel'], workbook.add_format(row_format))
    worksheet.write(row_index, 2, u['reg'], workbook.add_format(row_format))

    # 列寬 
    # 第一個(gè)參數(shù)是第幾列開(kāi)始，第二個(gè)人參數(shù)是從第幾列結(jié)束
    # 比如下方第一個(gè)就是設(shè)置第一列為20，第二個(gè)就是設(shè)置第二列為10，第三個(gè)就是設(shè)置3到6列為20
  worksheet.set_column(0, 0, 20)
  worksheet.set_column(1, 1, 10)
  worksheet.set_column(2, 5, 20)
  workbook.close()

def dict_merge(*args):
  """
  功能說(shuō)明：合并字典
  """
  all = {}
  for arg in args:
    if not isinstance(arg, dict):
      continue
    all.update(arg)
  return all

class Struct(dict):
  """
  - 為字典加上點(diǎn)語(yǔ)法. 例如:
  >>> o = Struct({'a':1})
  >>> o.a
  >>> 1
  >>> o.b
  >>> None
  """

  def __init__(self, dictobj={}):
    self.update(dictobj)

  def __getattr__(self, name):
    # 如果有則返回值，沒(méi)有則返回None
    if name.startswith('__'):
      raise AttributeError
    return self.get(name)

  def __setattr__(self, name, val):
    self[name] = val

  def __hash__(self):
    return id(self)

if __name__ == '__main__':
  get_excel()