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.

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.

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 Notations

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

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

Open the hello.xlsx file using Excel software.

The numbered row-column notation is especially useful when referring to the cells programmatically. In the following code data in a pst of psts 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])

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])

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

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