Python Xlsxwriter 简明教程

Python XlsxWriter - Cell Notation & Ranges

工作簿中的每个工作表都是一个数字巨大的单元格网格，每个单元格可以存储一个数据 - 值或公式。网格中的每个单元格由其行号和列号标识。

Each worksheet in a workbook is a grid of a large number of cells, each of which can store one piece of data - either value or formula. Each Cell in the grid is identified by its row and column number.

在 Excel 的标准单元格寻址中，列用字母标识，A、B、C、…​、Z、AA、AB 等等，而行从 1 开始编号。

In Excel’s standard cell addressing, columns are identified by alphabets, A, B, C, …., Z, AA, AB etc., and rows are numbered starting from 1.

每个单元格的地址都是字母数字的，其中字母部分对应于列，数字部分对应于行。例如，地址“C5”指向列“C”和行号“5”中的单元格。

The address of each cell is alphanumeric, where the alphabetic part corresponds to the column and number corresponding to the row. For example, the address "C5" points to the cell in column "C" and row number "5".

cell notations1

Cell Notations

标准 Excel 使用字母列和基于 1 的行的字母数字序列。XlsxWriter 支持标准 Excel 表示法 ( A1 表示法) 以及 Row-column 表示法，该表示法对行和列都使用基于零的索引。

The standard Excel uses alphanumeric sequence of column letter and 1-based row. XlsxWriter supports the standard Excel notation (A1 notation) as well as Row-column notation which uses a zero based index for both row and column.

Example

在以下示例中，使用 Excel 的标准单元格地址将字符串“Hello world”写入 A1 单元格，而使用行-列表示法将“Welcome to XLSXWriter”写入单元格 C5。

In the following example, a string 'Hello world' is written into A1 cell using Excel’s standard cell address, while 'Welcome to XLSXWriter' is written into cell C5 using row-column notation.

import xlsxwriter
wb = xlsxwriter.Workbook('hello.xlsx')
ws = wb.add_worksheet()
ws.write('A1', 'Hello world')           # A1 notation
ws.write(4,2,"Welcome to XLSXWriter")   # Row-column notation
wb.close()

Output

使用 Excel 软件打开 hello.xlsx 文件。

Open the hello.xlsx file using Excel software.

cell notations2

编号的 row-column 表示法在以编程方式引用单元格时特别有用。在以下代码中，列表中的数据必须写入工作表中的单元格范围。这是通过两个嵌套循环实现的，外部循环表示行号，内部循环表示列号。

The numbered row-column notation is especially useful when referring to the cells programmatically. In the following code data in a list of lists has to be written to a range of cells in a worksheet. This is achieved by two nested loops, the outer representing the row numbers and the inner loop for column numbers.

data = [
   ['Name', 'Physics', 'Chemistry', 'Maths', 'Total'],
   ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85],
   ['Karishma', 55, 65, 75],
]
for row in range(len(data)):
   for col in range(len(data[row])):
      ws.write(row, col, data[row][col])

可以通过使用以下代码中使用的工作表对象的 write_row() 方法来实现相同的结果 −

The same result can be achieved by using write_row() method of the worksheet object used in the code below −

for row in range(len(data)):
   ws.write_row(6+row,0, data[row])

工作表对象具有 add_table() 方法，该方法将数据写入范围，并转换为 Excel 范围，在顶部行中显示自动筛选下拉箭头。

The worksheet object has add_table() method that writes the data to a range and converts into Excel range, displaying autofilter dropdown arrows in the top row.

ws.add_table('G6:J9', {'data': data, 'header_row':True})

Example

可以通过以下代码验证以上所有三个代码的输出，并显示在以下图形中 -

The output of all the three codes above can be verified by the following code and displayed in the following figure −

import xlsxwriter

wb = xlsxwriter.Workbook('ex1.xlsx')
ws = wb.add_worksheet()

data = [
   ['Name', 'Physics', 'Chemistry', 'Maths', 'Total'],
   ['Ravi', 60, 70, 80],
   ['Kiran', 65, 75, 85],
   ['Karishma', 55, 65, 75],
]
for row in range(len(data)):
   for col in range(len(data[row])):
      ws.write(row, col, data[row][col])

for row in range(len(data)):
   ws.write_row(6+row,0, data[row])

ws.add_table('G6:J9', {'data': data, 'header_row':False})

wb.close()

Output

执行以上程序并使用 Excel 软件打开 ex1.xlsx 。

Execute the above program and open the ex1.xlsx using Excel software.

cell notations3