python使用xlsxwriter模块在excel中绘制表格

1、安装xlsxwriter模块

• 使用pip install XlsxWriter命令进行安装

2、使用xlsxwriter模块

import xlsxwriter
# 创建文件
wb = xlsxwriter.Workbook("demo.xlsx")
# 创建sheet页
ws = wb.add_worksheet("sheet-1")
# 往表格中写入数据
ws.writer("A1", "hello, world")
wb.close()

3、常用用法

• 写入单个单元格用write，可以使用A1或者0, 0表示第1行第1列的单元格

  ws.writer("A1", "hello, A1", format)
  ws.writer(0, 1, "hello, (0, 1)", format)
  

• 设置单元格的格式使用add_format

  format = wb.add_format(
  	{
  		"bold": False,
  		"border": 1,
  		"align": "center",  # 水平居中
  		"valign": "vcenter",  # 垂直居中
  		"fg_color": "#FFFF00"  # 颜色填充
  	}
  )
  ws.write("A1", "hello", format)
  

• 使用write_row写入行

  rows = ["第一轮bug", "第二轮bug", "第三轮bug", "第四轮bug"]
  ws.write_row("A3", rows, format)  # 写入单元格 A3: B3: C3: D3: E3
  

• 使用write_column写入列

  cols = ["1号人员", "2号人员", "3号人员", "4号人员"]
  ws.write_column("A4", cols, format)  # 写入单元格 A4: A5: A6: A7
  

• 使用merge_range合并单元格

  ws.merge_range("B4:E7", "数据", format)
  

4、其他函数

def set_column(self, first_col, last_col, width=None, cell_format=None,
                options=None):
    """
    Set the width, and other properties of a single column or a
    range of columns.

    Args:
        first_col:   First column (zero-indexed).
        last_col:    Last column (zero-indexed). Can be same as first_col.
        width:       Column width. (optional).
        cell_format: Column cell_format. (optional).
        options:     Dict of options such as hidden and level.

    Returns:
        0:  Success.
        -1: Column number is out of worksheet bounds.
    """

def set_row(self, row, height=None, cell_format=None, options=None):
    """
    Set the width, and other properties of a row.

    Args:
        row:         Row number (zero-indexed).
        height:      Row height. (optional).
        cell_format: Row cell_format. (optional).
        options:     Dict of options such as hidden, level and collapsed.

    Returns:
        0:  Success.
        -1: Row number is out of worksheet bounds.
    """

def set_default_row(self, height=None, hide_unused_rows=False):
    """
    Set the default row properties.

    Args:
        height:           Default height. Optional, defaults to 15.
        hide_unused_rows: Hide unused rows. Optional, defaults to False.

    Returns:
        Nothing.

    """