3 Write

write a xlsx file use xlsx% class.

use add-data-sheet method to add data type sheet to xlsx.

use add-chart-sheet method to add chart type sheet to xlsx.

3.1 xlsx%

struct (struct xlsx% ())

xlsx% class represent a whole xlsx file’s data.

it contains data sheet or chart sheet.

3.2 Data Sheet

data sheet is a sheet contains data only.

add data sheet:

sheet data just a list contains list: (list (list cell ...) (list cell ...)...).

(let ([xlsx (new xlsx%)])

(send xlsx add-data-sheet

#:sheet_name "Sheet1"

#:sheet_data '(("chenxiao" "cx") (1 2))))

3.2.1 Set Col Width

column width is be set automatically by content’s width.

if you want to set it manually, use set-data-sheet-col-width! method

3.2.2 Set Row Height

3.2.3 Freeze Pane

use set-data-sheet-freeze-pane! to set a freeze range: ’(rows . cols)

;; freeze 1 row and 1 col

(send xlsx set-data-sheet-freeze-pane! #:sheet_name "DataSheet" #:range '(1 . 1))

3.3 Add Style to Data Sheet

you can add various style to a data sheet.

includes background color, font style, number format, border style, date format.

add-data-sheet-cell-style! set cells styles.

add-data-sheet-row-style! set rows styles.

add-data-sheet-col-style! set cols styles.

you can set cell, row, col style any times, it’s a pile effect.

it means:

if the latter style has same style property, it’ll overwrite this property.

if not, it’ll add this property.

it also means the order you set style is important.

(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") ))

3.3.1 Background Color

rgb color or color name.

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "A2-B3"

#:style '( (backgroundColor . "00C851") ))

3.3.2 Font Style

fontSize: integer? default is 11.

fontColor: rgb color or colorname.

fontName: system font name.

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "B3-C4"

#:style '( (fontSize . 20) (fontName . "Impact") (fontColor . "FF8800") ))

3.3.3 Number Format

numberPrecision: non-exact-integer?

numberPercent: boolean?

numberThousands: boolean?

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "E2-E2"

#:style '(

(numberPercent . #t)

(numberPrecision . 2)

(numberThousands . #t)

))

3.3.4 Border Style

borderColor: rgb color or color name.

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "B2-C4"

#:style '( (borderStyle . dashed) (borderColor . "blue")))

3.3.5 Date Format

year: yyyy, month: mm, day: dd

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "F2-F2"

#:style '( (dateFormat . "yyyy-mm-dd") ))

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle"

#:cell_range "F2-F2"

#:style '( (dateFormat . "yyyy/mm/dd") ))

3.3.6 Cell Alignment

horizontalAlign: ’left ’right ’center

verticalAlign: ’top ’bottom ’middle

(send xlsx add-data-sheet-cell-style!

#:sheet_name "DataSheetWithStyle2"

#:cell_range "G5"

#:style '( (horizontalAlign . left) ))

3.4 Chart Sheet

chart sheet is a sheet contains chart only.

chart sheet use data sheet’s data to constuct chart.

chart type now can have: linechart, linechart3d, barchart, barchart3d, piechart, piechart3d

3.4.1 add chart sheet

default chart_type is linechart or set chart type

chart type is one of these: line, line3d, bar, bar3d, pie, pie3d

(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")

set-chart-x-data! and add-chart-serail!:

use this two methods to set chart’s x axis data and y axis data

only one x axis data and multiple y axis data

(send xlsx set-chart-x-data!

#:sheet_name "LineChart1"

#:data_sheet_name "DataSheet"

#:data_range "B1-D1")

(send xlsx add-chart-serial!

#:sheet_name "LineChart1"

#:data_sheet_name "DataSheet"

#:data_range "B2-D2" #:y_topic "CAT")

3.5 write file