On this page:
3.1 xlsx%
3.2 Data Sheet
3.2.1 add data sheet
3.2.2 set col width
3.3 Add Style to Data Sheet
3.3.1 background  Color
3.3.2 font  Style
3.3.3 number  Format
3.3.4 border  Style
3.3.5 date  Format
3.4 Chart Sheet
3.4.1 add chart sheet
3.4.2 set-chart-x-data! and add-chart-serail!
3.5 write file

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%

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.

3.2.1 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.2 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

for example:

;; set column A, B width: 50

(send xlsx set-data-sheet-col-width!

  #:sheet_name "DataSheet"

  #:col_range "A-B" #:width 50)

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.

use add-data-sheet-cell-style to add style to cells.

the last parameter style is pair list.

for example:

'( (background . "FF0000") (fontSize . 20) )

you can use add-data-sheet-cell-style multiple times, to a cell, it’s a pile effect.

for example:

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

  #:sheet_name "DataSheet"

  #:cell_range "B2-C3"

  #:style '( (background . "FF0000") ))

 

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

  #:sheet_name "DataSheet"

  #:cell_range "C3-D4"

  #:style '( (fontSize . 30) ))

the C2’s style is

'( (background . "FF0000") )

the D3’s style is

'( (fontSize . 30) )

the C3’s style is

( (background . "FF0000") (fontSize . 30) )

if set a cell the same style property multiple times, the last one works.

for example:

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

  #:sheet_name "DataSheet"

  #:cell_range "B2-C3"

  #:style '( (background . "FF0000") ))

 

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

  #:sheet_name "DataSheet"

  #:cell_range "C3-D4"

  #:style '( (background . "0000FF") ))

the C3’s style is ’( (background . "0000FF") ).

3.3.1 backgroundColor

rgb color or color name.

for example:

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

  #:sheet_name "DataSheetWithStyle"

  #:cell_range "A2-B3"

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

3.3.2 fontStyle

fontSize: integer? default is 11.

fontColor: rgb color or colorname.

fontName: system font name.

for example:

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

  #:sheet_name "DataSheetWithStyle"

  #:cell_range "B3-C4"

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

3.3.3 numberFormat

numberPrecision: non-exact-integer?

numberPercent: boolean?

numberThousands: boolean?

for example:

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

  #:sheet_name "DataSheetWithStyle"

  #:cell_range "E2-E2"

  #:style '(

            (numberPercent . #t)

            (numberPrecision . 2)

            (numberThousands . #t)))

3.3.4 borderStyle

borderDirection:

'left 'right 'top 'bottom 'all

boderStyle:

'thin 'medium 'thick 'dashed 'thinDashed

 

'mediumDashed 'thickDashed 'double 'hair 'dotted

 

'dashDot 'dashDotDot 'mediumDashDot 'mediumDashDotDot

 

'slantDashDot

borderColor: rgb color or color name.

for example:

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

  #:sheet_name "DataSheetWithStyle"

  #:cell_range "B2-C4"

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

3.3.5 dateFormat

year: yyyy, month: mm, day: dd

for example:

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

3.4.2 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

procedure

(write-xlsx-file xlsx path)  void?

  xlsx : (xlsx%)
  path : (path-string?)
write xlsx% to xlsx file.