3 Write

procedure
(write-xlsx-file xlsx path) → void?
xlsx : (is-a?/c xlsx%)
path : path-string?

Save the spreadsheet in xlsx to a ".xlsx" file. If the file exists it will be silently overwritten.

procedure
(from-read-to-write-xlsx read_xlsx) → (is-a?/c xlsx%)
read_xlsx : (is-a?/c read-xlsx%)

Converts a read-xlsx% object (the kind produced within the body of with-input-from-xlsx-file) to a “writeable” xlsx% object.

In general, the workflow to modify an existing ".xlsx" file is:

(with-input-from-xlsx-file
"test.xlsx"
(lambda (xlsx)
   (let ([write_xlsx (from-read-to-write-xlsx xlsx)])
     (send write_xlsx set-data-sheet-col-width!
           #:sheet_name "DataSheet"
           #:col_range "A-F" #:width 20)
     (write-xlsx-file write_xlsx "write_back.xlsx"))))

3.1 xlsx%

class
xlsx% : class?

superclass: object%

The xlsx% class provides methods for changing a spreadsheet’s data, contained in either data sheets or chart sheets.

method
(send a-xlsx add-data-sheet #:sheet_name sheet
#:sheet_data cells) → void?
  sheet : string?
  cells : (listof (listof any/c))
Adds a data sheet (as opposed to a chart sheet), which holds normal data in cells.
Example:
(let ([xlsx (new xlsx%)])
  (send xlsx add-data-sheet
    #:sheet_name "Sheet1"
    #:sheet_data '(("chenxiao" "cx") (1 2))))
method
(send a-xlsx set-data-sheet-col-width! #:sheet_name sheet
#:col_range cols
#:width width)
→ void?
  sheet : string?
  cols : string?
  width : number?
Manually set the width of one or more columns.
Note that by default, column widths are set automatically by their content. Use this method to override the automatic sizing.
Example:
;; set column A, B width: 50
(send xlsx set-data-sheet-col-width!
  #:sheet_name "DataSheet"
  #:col_range "A-B" #:width 50)
method
(send a-xlsx set-data-sheet-row-height! #:sheet_name sheet
#:row_range rows
#:height height)
→ void?
  sheet : string?
  rows : string?
  height : number?
Set the height of specified rows.
Example:
(send xlsx set-data-sheet-row-height!
  #:sheet_name "DataSheetWithStyle2"
  #:row_range "2-4" #:height 30)
method
(send a-xlsx set-data-sheet-freeze-pane! #:sheet_name sheet
#:range range)
→ void?
  sheet : string?
   range :
(cons/c exact-nonnegative-integer?
        exact-nonnegative-integer?)
“Freezes” the given number of rows (counting from the top) and columns (counting from the left).
Example:
;; freeze 1 row and 1 col
(send xlsx set-data-sheet-freeze-pane! #:sheet_name "DataSheet" #:range '(1 . 1))
method
(send a-xlsx add-data-sheet-cell-style! #:sheet_name sheet
#:cell_range range
#:style style)
→ void?
  sheet : string?
  range : string?
  style : (listof (cons symbol? any/c))
Sets the cell style for specific cells. The range string should be either a single cell or a range of cells in “A1” reference style: "C4" or "B2-C3".
method
(send a-xlsx add-data-sheet-row-style! #:sheet_name sheet
#:row_range range
#:style style)
→ void?
  sheet : string?
  range : string?
  style : (listof (cons symbol? any/c))
Sets the cell style for an entire row or range of rows. The range string should contain either a single integer ("1") or a range like "2-4".
method
(send a-xlsx add-data-sheet-col-style! #:sheet_name sheet
#:col_range range
#:style style)
→ void?
  sheet : string?
  range : string?
  style : (listof (cons symbol? any/c))
Sets the cell style for an entire column or range of columns. The range string should contain either a single integer ("1") or a range like "2-4".
method
(send a-xlsx add-chart-sheet #:sheet_name sheet
[ #:chart_type type]
#:topic topic
#:x_topic x-topic) → void?
  sheet : string?
   type :
(or/c 'linechart
      'linechart3d
      'barchart
      'barchart3d
      'piechart
      'piechart3d)
= 'linechart
  topic : string?
  x-topic : string?
Adds a chart sheet to the spreadsheet, which is a sheet containing only a chart. A chart sheet draws its data from a data sheet.
Examples:
(send xlsx add-chart-sheet
  #:sheet_name "LineChart1"
  #:topic "Horizontal Data"
  #:x_topic "Kg")

(send xlsx add-chart-sheet
  #:sheet_name "LineChart1"
  #:chart_type 'bar
  #:topic "Horizontal Data"
  #:x_topic "Kg")
method
(send a-xlsx set-chart-x-data! #:sheet_name sheet
#:data_sheet_name data-sheet
#:data_range range)
→ void?
  sheet : string?
  data-sheet : string?
  range : string?
Set the x-axis data for a chart sheet’s chart.
Example:
(send xlsx set-chart-x-data!
  #:sheet_name "LineChart1"
  #:data_sheet_name "DataSheet"
  #:data_range "B1-D1")
method
(send a-xlsx add-chart-x-serial! #:sheet_name sheet
#:data_sheet_name data-sheet
#:data_range range
#:y_topic y-topic)
→ void?
  sheet : string?
  data-sheet : string?
  range : string?
  y-topic : string?
Adds a data range as as y-axis (series) data for a chart sheet’s chart.
Example:
(send xlsx add-chart-serial!
  #:sheet_name "LineChart1"
  #:data_sheet_name "DataSheet"
  #:data_range "B2-D2" #:y_topic "CAT")

3.2 Cell Styles

A cell style is a list of pairs, where each pair is a property/value pair according to this grammar:

setting	=	(backgroundColor . color-string)
	\|	(fontSize . positive-integer)
	\|	(fontName . string)
	\|	(fontColor . color-string)
	\|	(numberPrecision . positive-integer)
	\|	(numberPercent . boolean)
	\|	(numberThousands . boolean)
	\|	(formatCode . string)
	\|	(borderStyle . border-line)
	\|	(borderDirection . dir)
	\|	(borderColor . color-string)
	\|	(dateFormat . ymd-format-string)
	\|	(horizontalAlign . h-alignment)
	\|	(verticalAlign . v-alignment)

border-line	=	thin
	\|	medium
	\|	thick
	\|	dashed
	\|	thinDashed
	\|	mediumDashed
	\|	thickDashed
	\|	double
	\|	hair
	\|	dotted
	\|	dashDot
	\|	dashDotDot
	\|	slantDashDot
	\|	mediumDashDot
	\|	mediumDashDotDot

dir	=	left
	\|	right
	\|	top
	\|	bottom
	\|	all

h-alignment	=	left
	\|	right
	\|	center

v-alignment	=	top
	\|	bottom
	\|	middle

color-string	=	hex-rgb-string
	\|	color-name

When you change a cell’s style, the settings you give will add to or overwrite the previous values. Each affected cell retains its previous settings for any properties not identified.

This means the order in which you set styles is important.

formatCode is Number Format Code String.

You can define complex format for number, text or

Note: in some use cases, need use entity like &quote;(") to avoid conflict.

Example:

(send xlsx add-data-sheet-cell-style!
  #:sheet_name "DataSheet"
  #:cell_range "B2-C3"
  #:style '( (backgroundColor . "FF0000") ))

(send xlsx add-data-sheet-cell-style!
  #:sheet_name "DataSheet"
  #:cell_range "C3-D4"
  #:style '( (fontSize . 30) ))

(send xlsx add-data-sheet-row-style!
  #:sheet_name "DataSheetWithStyle2"
  #:row_range "1-3" #:style '( (backgroundColor . "00C851") ))

(send xlsx add-data-sheet-col-style!
  #:sheet_name "DataSheetWithStyle2"
  #:col_range "4-6" #:style '( (backgroundColor . "AA66CC") ))

After the above operations:

C2’s style is '((backgroundColor . "AA66CC")).
D3’s style is '((backgroundColor . "AA66CC") (fontSize . 30))
C3’s style is '((backgroundColor . "00C851") (fontSize . 30))

1	Install
2	Read
3	Write
4	Complete Example