Write Spreadsheet

Command description

Writes data to a cell, row, or column using append, insert, or overwrite mode.

Command Input Parameters

input parameters	Input parameter type	Description
Excel Object	ExcelApp	Spreadsheet object to write to
Write Range	enum	Row, Column, Cell
Row Number	str	One-based starting row; -n means the nth row from the bottom
Column Name	str	Starting column letter such as A or one-based column number; -n means the nth column from the right
Write Mode	enum	Append, Insert, Overwrite. Required when Write Range is Row or Column
Content to Write	list/list[list]/any	Content to write. Two-dimensional lists are written row-first; for example, [[1, 2], [3, 4]] fills a two-row, two-column range
sheet	str	Worksheet name. Defaults to the active worksheet

Command Output Parameters

None

Description

• Row Number and Column Name:

  • When writing a row, Column Name specifies the first column to write.
  • When writing a column, Row Number specifies the first row to write.

• Write Mode:

  • Append: Add a new row after the last row, or a new column after the last column.
  • Insert: Insert a row or column at the specified position and shift existing data accordingly.
  • Overwrite: Replace data in the specified row, column, or cell.

• Content to Write:

  • Normal values are written to a single cell.
  • A one-dimensional list such as ['a', 'b', 'c'] is written as one row or one column, according to Write Range.
  • A two-dimensional list is written row-first into a rectangular range. For example, [[1, 2], [3, 4]] writes two rows and two columns starting at the specified cell.
  • If its rows have different lengths, shorter rows are padded with empty strings.
  • Cells to the left of the starting column or above the starting row are not modified.

tip

The default openpyxl mode does not require the installation of Microsoft Office or Kingsoft WPS. If the target file is being opened by Office/WPS, saving may fail. Please close the window occupying the file and try again.

Example

• raw data

	A	B	C	D
1		Apple	Banana	Tomato
2		Car	Truck	Taxi
3		Ball	Lego	Doll

• Writing range: Row, column name: C, writing method: Append, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	Truck	Taxi
3		Ball	Lego	Doll
4			a	b	c	d

• Writing range: Row, column name: C, writing method: Insert, row number: 2, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2			a	b	c	d
3		Car	Truck	Taxi
4		Ball	Lego	Doll
5

• Writing range: Row, column name: C, writing method: Overwrite, row number: 2, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	a	b	c	d
3		Ball	Lego	Doll
4

• Writing range: Column, row number: 2, writing method: Append, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	Truck	Taxi	a
3		Ball	Lego	Doll	b
4					c
5					d

• Writing range: Column, row number: 2, writing method: Insert, column name C, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple		Banana	Tomato
2		Car	a	Truck	Taxi
3		Ball	b	Lego	Doll
4			c
5			d

• Writing range: Column, row number: 2, writing method: Overwrite, column name C, writing content ['a','b','c','d']

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	a	Taxi
3		Ball	b	Doll
4			c
5			d

• Writing range: Cell, row number: 2, column name C, writing content 'a'

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	a	Taxi
3		Ball	Lego	Doll
4

• Writing range: Cell, row number: 3, column name D, writing content [[1, 2], [3, 4]]

	A	B	C	D	E	F
1		Apple	Banana	Tomato
2		Car	Truck	Taxi
3		Ball	Lego	1	2
4				3	4

This example will only modify the D3:E4 area, and the A:C column will not be modified.

Reference

Read Spreadsheet Displayed Data