excel
Reading and writing Excel (xlsx), OpenDocument (ods) and CSV spreadsheets: sheets, cells, styles, formulas and dates.
import excel
A workbook holds several sheets, and each sheet holds cells by row and column, from zero. Turn Excel references such as C5 into a row and column with CellRef and ParseCellRef.
Open and Save work the format out from the file extension. Formulas are stored and read with their last calculated result, but Salam does not evaluate a formula itself.
Use it when people hand you spreadsheets or expect one back: an upload form for price lists, a monthly report for the finance team, an export button. When the data is only ever read by programs, a CSV file with encoding.csv is simpler; for analysis on columns of numbers, load the rows into a table with data.
What trips people up
- Old
.xlsfiles are read. BIFF8 workbooks (Excel 97 to 2003), including OLE2 streams stored in the mini stream, are decoded into sheets, strings, numbers, booleans, dates, errors, merged cells and cached formula values.Savestill writes xlsx, ods or csv only. - The extension decides the format.
DetectFormat,OpenandSavelook only at the file name, so an xlsx saved asreport.datwill not open. Rename uploads, or check the first bytes (PKfor xlsx and ods) yourself. - Formulas are not evaluated. A formula cell carries the result stored with it; when you write one, compute and pass the result yourself.
- Rows and columns count from zero, while Excel shows 1 and A.
ParseCellRefandCellRefconvert. SheetToJSONgives strings only: an array of rows, each an array of the cells' text. ReadCell.kindwhen you need the numbers.
The guide below covers the jobs that come up in real programs: reading a sheet someone else filled in, building a formatted report, working with dates, turning a workbook into CSV files, and finding values by reference or by content.
Reading rows from a sheet someone else filled in
A spreadsheet that people edit by hand is never quite regular: columns move, rows are left empty, a number cell holds a word. Find each column by its header text instead of trusting its position, skip rows with no key, and look at Cell.kind before you treat a value as a number. get_string always gives text, whatever the cell holds, so it is safe for messages. Open reports a file it cannot read through ok and err.
import excel
func header_col(sheet: excel.Sheet*, name: str): int:
repeat sheet.col_count() in c:
if sheet.get_string(0, c) == name: ret c end
end
ret -1
end
func make_file():
mut book := excel.NewWorkbook()
s := book.add_sheet("grades")
each c, title in ["Name", "City", "Score"]: s.set_string(0, c, title) end
s.set_string(1, 0, "Ali")
s.set_string(1, 1, "Tehran")
s.set_number(1, 2, 17.5)
s.set_string(2, 0, "Sara")
s.set_string(2, 1, "Shiraz")
s.set_number(2, 2, 19)
s.set_string(4, 0, "Reza")
s.set_string(4, 2, "absent")
book.save("grades.xlsx")
book.Free()
end
func main:
make_file()
mut ok := false
mut err := ""
mut book := excel.Open("grades.xlsx", ok, err)
if ok == false:
println "cannot open:", err
ret 1
end
sheet := book.sheet_at(0)
name_col := header_col(sheet, "Name")
score_col := header_col(sheet, "Score")
mut total := 0.0
mut counted := 0
repeat sheet.row_count() - 1 in i:
r := i + 1
if sheet.has_cell(r, name_col) == false: continue end
cell := sheet.get_cell(r, score_col)
if cell.kind != excel.CellNumber:
println sheet.get_string(r, name_col), "skipped:", sheet.get_string(r, score_col)
continue
end
println sheet.get_string(r, name_col), cell.num
total += cell.num
counted++
end
println "average of", counted, "=", total / (counted as f64)
book.Free()
ret 0
endRows count from zero, so the header is row 0 and data starts at row 1. row_count is one past the last row that has a cell, which is why the empty row 3 still appears in the loop and is skipped by has_cell.
Building a formatted report
A Style describes bold text, font size, background colour, alignment and number format. Register each style once with book.styles.get_or_add, which returns an id, and give that id to set_style for as many cells as you like. merge_cells joins a range for a title, and set_formula stores a formula together with the value you computed, because Salam writes formulas but does not evaluate them.
import excel
func main:
mut book := excel.NewWorkbook()
mut sheet := book.add_sheet("March")
title := book.styles.GetOrAdd(excel.Style { bold = true, font_size = 14, align_h = excel.AlignCenter })
head := book.styles.GetOrAdd(excel.Style { bold = true, bg_color = "DDEBF7" })
money := book.styles.GetOrAdd(excel.Style { num_format = "#,##0.00" })
sheet.set_string(0, 0, "Sales, March 2026")
sheet.merge_cells(0, 0, 0, 3)
sheet.set_style(0, 0, title)
each col, name in ["Item", "Qty", "Price", "Total"]:
sheet.set_string(1, col, name)
sheet.set_style(1, col, head)
end
items := ["Tea", "Sugar", "Rice"]
qty := [12, 30, 5]
price := [4.5, 1.25, 9.8]
mut sum := 0.0
repeat 3 in i:
r := i + 2
line := (qty[i] as f64) * price[i]
sheet.set_string(r, 0, items[i])
sheet.set_int(r, 1, qty[i] as i64)
sheet.set_number(r, 2, price[i])
sheet.set_formula(r, 3, excel.CellRef(r, 1) + "*" + excel.CellRef(r, 2), line)
sheet.set_style(r, 3, money)
sum += line
end
sheet.set_string(5, 2, "Sum")
sheet.set_formula(5, 3, "SUM(D3:D5)", sum)
sheet.set_style(5, 3, money)
println "saved:", book.save("march.xlsx")
mut ok := false
mut err := ""
mut back := excel.Open("march.xlsx", ok, err)
s := back.sheet("March")
println s.get_string(0, 0), "merged:", s.is_merged(0, 3)
println s.get_formula(5, 3), "=", s.get_number(5, 3)
println "D3 style:", back.styles.Get(s.get_cell(2, 3).style_id).num_format
back.Free()
book.Free()
ret 0
endExcel recalculates the formulas when it opens the file. Programs that read the file without Excel, this package included, see the value you stored, so always pass the correct result as the last argument of set_formula.
Dates are numbers with a format
Excel stores a date as a serial number: days since 30 December 1899, with the time of day as the fraction. set_date takes Unix seconds and stores the matching serial with a date format, EpochToSerial and SerialToEpoch convert in both directions, and a cell written with set_date reads back with kind == CellDate. Because serials are plain day counts, the difference of two dates is a number of days, which is what the formula B2-B1 stores.
import excel
import time
func show(label: str, epoch: i64):
println label, time.YearUTC(epoch), time.MonthUTC(epoch), time.DayUTC(epoch)
end
func main:
start := 1774008000 as i64
finish := 1775131200 as i64
mut book := excel.NewWorkbook()
mut s := book.add_sheet("project")
s.set_string(0, 0, "Start")
s.set_date(0, 1, start)
s.set_string(1, 0, "Finish")
s.set_date(1, 1, finish)
s.set_formula(2, 1, "B2-B1", excel.EpochToSerial(finish) - excel.EpochToSerial(start))
book.save("plan.xlsx")
mut ok := false
mut err := ""
mut back := excel.Open("plan.xlsx", ok, err)
sheet := back.sheet("project")
cell := sheet.get_cell(0, 1)
println "kind is date:", cell.kind == excel.CellDate
println "serial number:", cell.num
show("start:", excel.SerialToEpoch(cell.num))
show("finish:", excel.SerialToEpoch(sheet.get_number(1, 1)))
println "days between:", sheet.get_number(2, 1)
println "serial of 1970-01-01:", excel.EpochToSerial(0)
back.Free()
book.Free()
ret 0
endThe program prints the parts of each date with the UTC functions of time. get_string on a date cell formats it in the local time zone, so near midnight it can show a different day from the UTC value.
From a workbook to CSV files
Open reads xlsx, ods and csv files and picks the reader from the file extension, which is also what DetectFormat looks at, so a file with the wrong extension will not open. SheetToCSV writes one sheet as CSV text with commas and quotes escaped, so exporting every sheet of a workbook is a short loop over sheet_names. Going the other way, CSVToSheet turns CSV text into a sheet.
import excel
import io
func export_sheets(path: str): int:
mut ok := false
mut err := ""
mut book := excel.Open(path, ok, err)
if ok == false:
println "cannot open", path, ":", err
ret 0
end
names := book.sheet_names()
each i, name in names:
out := name + ".csv"
io.WriteFile(out, excel.SheetToCSV(book.sheets.Get(i)))
println "wrote", out
end
book.Free()
ret names.Len()
end
func main:
mut book := excel.NewWorkbook()
mut users := book.add_sheet("users")
users.set_string(0, 0, "name")
users.set_string(0, 1, "note")
users.set_string(1, 0, "Ali")
users.set_string(1, 1, "likes tea, not coffee")
mut stock := book.add_sheet("stock")
stock.set_string(0, 0, "item")
stock.set_string(0, 1, "count")
stock.set_string(1, 0, "pen")
stock.set_int(1, 1, 40)
excel.WriteODS(book, "office.ods")
println "format is ods:", excel.DetectFormat("office.ods") == excel.FormatODS
println "sheets exported:", export_sheets("office.ods")
println io.ReadFile("users.csv")
println io.ReadFile("stock.csv")
println "sheets exported:", export_sheets("nothing.xlsx")
book.Free()
ret 0
endFinding values by reference or by content
People talk about cells as C3, programs use row and column numbers. ParseCellRef turns a reference into zero based numbers and returns false for something that is not a reference, CellRef goes back, and ColumnIndex and ColumnLabel do the same for a column alone. To search by content, filter_rows_equals returns the rows whose cell in one column has a given text and find returns every cell with that text.
import excel
func value_at(sheet: excel.Sheet, ref: str): str:
mut row := 0
mut col := 0
if excel.ParseCellRef(ref, row, col) == false: ret "bad reference" end
if sheet.has_cell(row, col) == false: ret "empty" end
ret sheet.get_string(row, col)
end
func main(): int throws:
mut s := try excel.CSVToSheet("order,city,item\n101,Tehran,pen\n102,Tabriz,book\n103,Tehran,book\n104,Shiraz,pen\n", "orders")
println "C3 =", value_at(s, "C3"), "| A9 =", value_at(s, "A9"), "| 3C =", value_at(s, "3C")
rows := s.filter_rows_equals(1, "Tehran")
each r in rows:
println "Tehran order", s.get_string(r, 0), "is a", s.get_string(r, 2)
end
hits := s.Find("book")
println "book appears", hits.Len(), "times"
city := excel.ColumnIndex("B")
println "column B holds", s.get_column(city).Len(), "cells, the last column is", excel.ColumnLabel(s.col_count() - 1)
s.Free()
ret 0
endfind returns its matches in no particular order, so sort them or, as here, use only the count when the order matters.
Types
FileMeta
struct FileMeta| Name | Type | Description |
|---|---|---|
path | str = "" | The path. |
format | int = 0 | The format. |
exists | bool = false | The file is there. |
size | i64 = 0 | The size in bytes. |
sheet_names | Vector | The sheet names. |
sheet_rows | Vector | The number of rows of each sheet. |
sheet_cols | Vector | The number of columns of each sheet. |
import excel
func main:
mut book := excel.NewWorkbook()
mut a := book.add_sheet("A")
a.set_string(0, 0, "hello")
a.set_number(0, 1, 2.5)
mut b := book.add_sheet("B")
b.set_int(0, 0, 42)
println book.sheet_count(), book.sheet_names(), book.sheet_index("B"), book.sheet_at(0).name
println book.rename_sheet(1, "second"), book.sheet("second").get_number(0, 0)
println book.set_active(1), book.active().name, book.active_sheet
println excel.WriteXLSX(book, "book.xlsx"), excel.WriteODS(book, "book.ods"), book.save("copy.xlsx"), excel.Save(book, "book.csv")
println excel.DetectFormat("book.xlsx") == excel.FormatXLSX, excel.DetectFormat("book.ods") == excel.FormatODS, excel.DetectFormat("a.txt"), excel.FormatXLS, excel.FormatCSV, excel.FormatUnknown
mut ok := false
mut err := ""
mut read := excel.Open("book.xlsx", ok, err)
println ok, read.sheet_names(), read.sheet("A").get_string(0, 0), read.format == excel.FormatXLSX
mut copy := excel.ReadXLSX("copy.xlsx", ok, err)
mut ods := excel.ReadODS("book.ods", ok, err)
println ok, ods.sheet("second").get_number(0, 0), copy.sheet_count()
mut meta := excel.Inspect("book.xlsx", ok, err)
println ok, meta.exists, meta.size > 0, meta.sheet_names, meta.sheet_rows, meta.sheet_cols
meta.Free()
mut csv := excel.ReadCSVWorkbook("book.csv", ok, err)
println ok, csv.sheet_at(0).get_string(0, 0), excel.WriteCSVWorkbook(csv, "again.csv")
_ := excel.Open("missing.xlsx", ok, err)
println ok, err != ""
_ := excel.ReadXLS("missing.xls", ok, err)
println ok, excel.IsXLS(null, 0)
println book.remove_sheet_named("A"), book.remove_sheet(5), book.remove_sheet(0), book.sheet_count()
mut fresh := excel.New()
println fresh.sheet_count()
fresh.Free()
read.Free()
copy.Free()
ods.Free()
csv.Free()
book.Free()
endFree
func Free()free
func free()The example for this item is shown with the ones above: excel-Workbook.salam
Cell
struct Cell| Name | Type | Description |
|---|---|---|
row | int = 0 | The row, from zero. |
col | int = 0 | The column, from zero. |
kind | int = 0 | The kind; one of the Cell constants. |
text | str = "" | The text of a string cell, or the error code. |
num | f64 = 0 | The number; for a date, the Excel serial number. |
flag | bool = false | The boolean value. |
formula | str = "" | The formula text. |
style_id | int = -1 | The style index; -1 means the default. |
import excel
func main:
println excel.ColumnLabel(0), excel.ColumnLabel(25), excel.ColumnLabel(26), excel.ColumnLabel(703)
println excel.ColumnIndex("A"), excel.ColumnIndex("AA"), excel.CellRef(4, 2)
mut row := 0
mut col := 0
println excel.ParseCellRef("C5", row, col), row, col, excel.ParseCellRef("5C", row, col)
serial := excel.EpochToSerial(0)
println serial, excel.SerialToEpoch(45371.0)
text := excel.NewStringCell(0, 0, "some text")
number := excel.NewNumberCell(0, 1, 3.5)
flag := excel.NewBoolCell(0, 2, false)
date := excel.NewDateCell(0, 3, 0)
formula := excel.NewFormulaCell(0, 4, "A1*2", 7.0)
println text.kind == excel.CellString, number.num, flag.flag, date.kind == excel.CellDate, formula.formula, formula.num
println excel.CellEmpty, excel.CellNumber, excel.CellBool, excel.CellFormula
mut styles := excel.NewStyleSheet()
bold := excel.Style { bold = true, bg_color = "FFFF00", align_h = excel.AlignCenter }
id := styles.GetOrAdd(bold)
println id, styles.GetOrAdd(bold) == id, styles.count(), styles.Get(id).bold
println bold.equals(styles.Get(id)), bold.font_name, bold.font_size
println excel.AlignDefault, excel.AlignLeft, excel.AlignRight, excel.AlignTop, excel.AlignMiddle, excel.AlignBottom
styles.Free()
endCellPos
struct CellPos| Name | Type | Description |
|---|---|---|
row | int = 0 | The row. |
col | int = 0 | The column. |
import excel
func main:
mut sheet := excel.NewSheet("sales")
sheet.set_string(0, 0, "item")
sheet.set_string(0, 1, "count")
sheet.set_cell(1, 0, "tea")
sheet.set_int(1, 1, 12)
sheet.set_cell(2, 0, "sugar")
sheet.set_number(2, 1, 4.5)
sheet.set_bool(3, 0, true)
sheet.set_date(3, 1, 1710943509)
sheet.set_formula(4, 1, "SUM(B2:B3)", 16.5)
sheet.set_error(5, 0, "#DIV/0!")
println sheet.name, sheet.row_count(), sheet.col_count()
println sheet.get_string(1, 0), sheet.get_number(1, 1), sheet.get_number(2, 1), sheet.get_bool(3, 0), sheet.get_formula(4, 1)
cell := sheet.get_cell(5, 0)
println cell.kind == excel.CellError, cell.text, sheet.get_cell(3, 1).kind == excel.CellDate
println sheet.has_cell(1, 1), sheet.has_cell(9, 9)
sheet.clear_cell(5, 0)
println sheet.has_cell(5, 0)
mut row := Vector {} as Vector<str>
row.Push("bread")
row.Push("7")
sheet.set_row(6, row)
sheet.set_column(3, row)
mut table := Vector {} as Vector<str>
each value in ["a", "b", "c", "d"]:
table.Push(value)
end
sheet.set_table(8, 0, table, 2)
println sheet.get_row(6).Len(), sheet.get_column(3).Len(), sheet.get_all().Len()
println sheet.get_string(9, 1)
found := sheet.Find("bread")
println found.Len(), found[0].row, found[0].col
println sheet.filter_rows_equals(0, "tea")
sheet.merge_cells(0, 0, 0, 1)
mut hit := false
range := sheet.merged_range(0, 1, hit)
println sheet.is_merged(0, 1), hit, range.row1, range.col2, sheet.merges.Len()
sheet.set_style(1, 1, 2)
println sheet.get_cell(1, 1).style_id, sheet.max_row, sheet.max_col, sheet.cells.Size() > 0
sheet.Free()
endMergeRange
struct MergeRange| Name | Type | Description |
|---|---|---|
row1 | int = 0 | The starting row. |
col1 | int = 0 | The starting column. |
row2 | int = 0 | The ending row. |
col2 | int = 0 | The ending column. |
The example for this item is shown with the ones above: excel-Sheet.salam
Sheet
struct Sheet| Name | Type | Description |
|---|---|---|
name | str = "" | The sheet name. |
cells | HashMap | The cells by their reference. |
max_row | int = -1 | The greatest filled row number; -1 for an empty sheet. |
max_col | int = -1 | The greatest filled column number. |
merges | Vector | The merged ranges. |
rtl | bool = false | Shows the sheet right to left, as for Persian or Arabic data. It is written to xlsx files only. |
freeze_rows | int = 0 | How many top rows stay in view while scrolling; set it with freeze. It is written to xlsx files only. |
freeze_cols | int = 0 | How many of the first columns stay in view while scrolling. |
col_widths | Vector | The column widths in characters, by column number; 0 keeps the default width. They are written to xlsx files only. |
auto_filter | str = "" | The range that has filter buttons, such as A1:F20; empty for none. It is also read back from xlsx files. |
The example for this item is shown with the ones above: excel-Sheet.salam
SetString
func SetString(row: int, col: int, value: str)set_string
func set_string(row: int, col: int, value: str)The example for this item is shown with the ones above: excel-Sheet.salam
SetCell
func SetCell(row: int, col: int, value: str)set_cell
func set_cell(row: int, col: int, value: str)set_string; the value is stored as a string.The example for this item is shown with the ones above: excel-Sheet.salam
SetNumber
func SetNumber(row: int, col: int, value: f64)set_number
func set_number(row: int, col: int, value: f64)The example for this item is shown with the ones above: excel-Sheet.salam
SetInt
func SetInt(row: int, col: int, value: i64)set_int
func set_int(row: int, col: int, value: i64)The example for this item is shown with the ones above: excel-Sheet.salam
SetBool
func SetBool(row: int, col: int, value: bool)set_bool
func set_bool(row: int, col: int, value: bool)The example for this item is shown with the ones above: excel-Sheet.salam
SetDate
func SetDate(row: int, col: int, epoch: i64)set_date
func set_date(row: int, col: int, epoch: i64)The example for this item is shown with the ones above: excel-Sheet.salam
SetFormula
func SetFormula(row: int, col: int, formula: str, cached: f64)set_formula
func set_formula(row: int, col: int, formula: str, cached: f64)The example for this item is shown with the ones above: excel-Sheet.salam
SetError
func SetError(row: int, col: int, code: str)set_error
func set_error(row: int, col: int, code: str)#DIV/0!.The example for this item is shown with the ones above: excel-Sheet.salam
SetStyle
func SetStyle(row: int, col: int, style_id: int)set_style
func set_style(row: int, col: int, style_id: int)The example for this item is shown with the ones above: excel-Sheet.salam
ClearCell
func ClearCell(row: int, col: int)clear_cell
func clear_cell(row: int, col: int)The example for this item is shown with the ones above: excel-Sheet.salam
HasCell
func HasCell(row: int, col: int): boolhas_cell
func has_cell(row: int, col: int): boolThe example for this item is shown with the ones above: excel-Sheet.salam
GetCell
func GetCell(row: int, col: int): Cellget_cell
func get_cell(row: int, col: int): CellThe example for this item is shown with the ones above: excel-Sheet.salam
GetString
func GetString(row: int, col: int): strget_string
func get_string(row: int, col: int): strThe example for this item is shown with the ones above: excel-Sheet.salam
GetNumber
func GetNumber(row: int, col: int): f64GetBool
func GetBool(row: int, col: int): boolget_bool
func get_bool(row: int, col: int): boolThe example for this item is shown with the ones above: excel-Sheet.salam
GetFormula
func GetFormula(row: int, col: int): strget_formula
func get_formula(row: int, col: int): strThe example for this item is shown with the ones above: excel-Sheet.salam
RowCount
func RowCount(): introw_count
func row_count(): intThe example for this item is shown with the ones above: excel-Sheet.salam
ColCount
func ColCount(): intcol_count
func col_count(): intThe example for this item is shown with the ones above: excel-Sheet.salam
SetRow
func SetRow(row: int, values: Vector<str>)set_row
func set_row(row: int, values: Vector<str>)The example for this item is shown with the ones above: excel-Sheet.salam
SetColumn
func SetColumn(col: int, values: Vector<str>)set_column
func set_column(col: int, values: Vector<str>)The example for this item is shown with the ones above: excel-Sheet.salam
SetTable
func SetTable(start_row: int, start_col: int, flat: Vector<str>, num_cols: int)set_table
func set_table(start_row: int, start_col: int, flat: Vector<str>, num_cols: int)num_cols of them per row.The example for this item is shown with the ones above: excel-Sheet.salam
GetRow
func GetRow(row: int): Vector<Cell>get_row
func get_row(row: int): Vector<Cell>The example for this item is shown with the ones above: excel-Sheet.salam
GetColumn
func GetColumn(col: int): Vector<Cell>get_column
func get_column(col: int): Vector<Cell>The example for this item is shown with the ones above: excel-Sheet.salam
GetAll
func GetAll(): Vector<Cell>get_all
func get_all(): Vector<Cell>The example for this item is shown with the ones above: excel-Sheet.salam
Find
func Find(value: str): Vector<CellPos>find
func find(value: str): Vector<CellPos>The example for this item is shown with the ones above: excel-Sheet.salam
FilterRowsEquals
func FilterRowsEquals(col: int, value: str): Vector<int>filter_rows_equals
func filter_rows_equals(col: int, value: str): Vector<int>The example for this item is shown with the ones above: excel-Sheet.salam
MergeCells
func MergeCells(row1: int, col1: int, row2: int, col2: int)merge_cells
func merge_cells(row1: int, col1: int, row2: int, col2: int)The example for this item is shown with the ones above: excel-Sheet.salam
IsMerged
func IsMerged(row: int, col: int): boolis_merged
func is_merged(row: int, col: int): boolThe example for this item is shown with the ones above: excel-Sheet.salam
MergedRange
func MergedRange(row: int, col: int, found &: bool): MergeRangemerged_range
func merged_range(row: int, col: int, found &: bool): MergeRangeThe example for this item is shown with the ones above: excel-Sheet.salam
SetColumnWidth
func SetColumnWidth(col: int, width: f64)set_column_width
func set_column_width(col: int, width: f64)import excel
func main:
mut book := excel.NewWorkbook()
mut sheet := book.add_sheet("prices")
sheet.set_string(0, 0, "item")
sheet.set_string(0, 1, "price")
sheet.set_string(1, 0, "green tea, 500 g")
sheet.set_number(1, 1, 4.5)
sheet.set_string(2, 0, "sugar")
sheet.set_number(2, 1, 1.25)
sheet.rtl = true
sheet.freeze(1, 1)
sheet.set_auto_filter(0, 0, 2, 1)
println sheet.rtl, sheet.freeze_rows, sheet.freeze_cols, sheet.auto_filter
sheet.auto_fit()
println sheet.col_widths
sheet.set_column_width(3, 30.0)
println sheet.col_widths.Len(), sheet.col_widths[2], sheet.col_widths[3]
framed := excel.Style { border = true, wrap = true }
id := book.styles.GetOrAdd(framed)
sheet.set_style(0, 0, id)
println excel.WriteXLSX(book, "prices.xlsx")
mut ok := false
mut err := ""
mut read := excel.Open("prices.xlsx", ok, err)
if ok:
back := read.sheet("prices")
println ok, back.auto_filter, read.styles.Get(back.get_cell(0, 0).style_id).wrap
else:
println ok, err
end
read.Free()
book.Free()
endAutoFit
func AutoFit()auto_fit
func auto_fit()The example for this item is shown with the ones above: excel-View.salam
Freeze
func Freeze(rows: int, cols: int)freeze
func freeze(rows: int, cols: int)freeze(1, 0) pins a header row.The example for this item is shown with the ones above: excel-View.salam
SetAutoFilter
func SetAutoFilter(row1: int, col1: int, row2: int, col2: int)set_auto_filter
func set_auto_filter(row1: int, col1: int, row2: int, col2: int)The example for this item is shown with the ones above: excel-View.salam
Free
func Free()free
func free()The example for this item is shown with the ones above: excel-Sheet.salam
Style
struct Style| Name | Type | Description |
|---|---|---|
bold | bool = false | Bold. |
italic | bool = false | Italic. |
underline | bool = false | Underlined. |
font_name | str = "Calibri" | The font name. |
font_size | int = 11 | The font size. |
font_color | str = "" | The text colour as hex, such as FF0000. |
bg_color | str = "" | The background colour. |
align_h | int = 0 | The horizontal alignment. |
align_v | int = 0 | The vertical alignment. |
num_format | str = "" | The number display format, such as 0.00. |
border | bool = false | A thin border on all four sides. |
wrap | bool = false | Long text breaks onto more lines inside the cell, and \n starts a new line. |
The example for this item is shown with the ones above: excel-Cell.salam
Equals
func Equals(other: Style): boolequals
func equals(other: Style): boolThe example for this item is shown with the ones above: excel-Cell.salam
StyleSheet
struct StyleSheet| Name | Type | Description |
|---|---|---|
styles | Vector | The styles. |
The example for this item is shown with the ones above: excel-Cell.salam
GetOrAdd
func GetOrAdd(s: Style): intThe example for this item is shown with the ones above: excel-Cell.salam
get_or_add
func get_or_add(s: Style): intGet
func Get(id: int): Styleget
func get(id: int): StyleThe example for this item is shown with the ones above: excel-Cell.salam
Count
func Count(): intcount
func count(): intThe example for this item is shown with the ones above: excel-Cell.salam
Free
func Free()free
func free()The example for this item is shown with the ones above: excel-Cell.salam
Workbook
struct Workbook| Name | Type | Description |
|---|---|---|
sheets | Vector | The sheets in order. |
styles | StyleSheet | The styles shared by the cells. |
active_sheet | int = 0 | The index of the active sheet. |
source_path | str = "" | The path of the file the workbook was read from. |
format | int = 0 | The file format; one of the Format constants. |
last_error | str = "" | The last error message. |
The example for this item is shown with the ones above: excel-Workbook.salam
AddSheet
func AddSheet(name: str): Sheet*add_sheet
func add_sheet(name: str): Sheet*The example for this item is shown with the ones above: excel-Workbook.salam
RemoveSheet
func RemoveSheet(idx: int): boolremove_sheet
func remove_sheet(idx: int): boolidx; false when the index is wrong.The example for this item is shown with the ones above: excel-Workbook.salam
RemoveSheetNamed
func RemoveSheetNamed(name: str): boolremove_sheet_named
func remove_sheet_named(name: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam
RenameSheet
func RenameSheet(idx: int, new_name: str): boolrename_sheet
func rename_sheet(idx: int, new_name: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam
SheetIndex
func SheetIndex(name: str): intsheet_index
func sheet_index(name: str): intThe example for this item is shown with the ones above: excel-Workbook.salam
SheetCount
func SheetCount(): intsheet_count
func sheet_count(): intThe example for this item is shown with the ones above: excel-Workbook.salam
SheetNames
func SheetNames(): Vector<str>sheet_names
func sheet_names(): Vector<str>The example for this item is shown with the ones above: excel-Workbook.salam
SheetAt
func SheetAt(idx: int): Sheet*sheet_at
func sheet_at(idx: int): Sheet*idx.The example for this item is shown with the ones above: excel-Workbook.salam
Sheet
func Sheet(name: str): Sheet*sheet
func sheet(name: str): Sheet*The example for this item is shown with the ones above: excel-Workbook.salam
Active
func Active(): Sheet*active
func active(): Sheet*The example for this item is shown with the ones above: excel-Workbook.salam
SetActive
func SetActive(idx: int): boolset_active
func set_active(idx: int): boolThe example for this item is shown with the ones above: excel-Workbook.salam
Save
func Save(path: str): boolsave
func save(path: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam
Free
func Free()free
func free()The example for this item is shown with the ones above: excel-Workbook.salam
Constants and variables
Cell kinds Cell…
kind of each cell.| Name | Value |
|---|---|
CellEmpty | 0 |
CellString | 1 |
CellNumber | 2 |
CellBool | 3 |
CellDate | 4 |
CellFormula | 5 |
CellError | 6 |
The example for this item is shown with the ones above: excel-Cell.salam
File formats Format…
| Name | Value |
|---|---|
FormatUnknown | 0 |
FormatXLSX | 1 |
FormatXLS | 2 |
FormatODS | 3 |
FormatCSV | 4 |
The example for this item is shown with the ones above: excel-Workbook.salam
Alignment Align…
align_h (left, centre, right) and align_v (top, middle, bottom). Zero means the default.| Name | Value |
|---|---|
AlignDefault | 0 |
AlignLeft | 1 |
AlignCenter | 2 |
AlignRight | 3 |
AlignTop | 1 |
AlignMiddle | 2 |
AlignBottom | 3 |
The example for this item is shown with the ones above: excel-Cell.salam
Functions
SheetToCSV
func SheetToCSV(sheet: Sheet): strimport excel
func main throws:
mut sheet := try excel.CSVToSheet("name,score\nAli,17\n\"Sara, A\",19.5\n", "scores")
println sheet.name, sheet.row_count(), sheet.get_string(2, 0), sheet.get_number(2, 1)
println excel.SheetToCSV(sheet)
println excel.SheetToJSON(sheet)
mut from_json := excel.JSONToSheet(`[["a", "b"], ["1", "x"], ["2", "y"]]`, "data")
println from_json.row_count(), from_json.col_count(), from_json.get_string(0, 1), from_json.get_string(2, 0)
xml := "<row r=\"2\"><c r=\"A2\" t=\"s\"><v>0</v></c><br/></row>"
start := excel.XmlFind(xml, "c", 0)
println start, excel.XmlAttr(xml, start, "r"), excel.XmlTagEnd(xml, start)
println excel.XmlInner(xml, "v", 0), excel.XmlSelfClosing(xml, excel.XmlFind(xml, "br", 0))
println excel.XmlFindClose(xml, "row", 10)
println excel.XmlEscape("a<b&c"), excel.XmlUnescape("a<b&c")
sheet.Free()
from_json.Free()
endCSVToSheet
func CSVToSheet(text: str, sheet_name: str): Throws<Sheet>The example for this item is shown with the ones above: excel-Convert.salam
ReadCSVWorkbook
func ReadCSVWorkbook(path: str, ok &: bool, err &: str): Workbookok is false and err holds the reason, with the line and column for broken CSV.The example for this item is shown with the ones above: excel-Workbook.salam
WriteCSVWorkbook
func WriteCSVWorkbook(book &: Workbook, path: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam
ReadXLSX
func ReadXLSX(path: str, ok &: bool, err &: str): WorkbookThe example for this item is shown with the ones above: excel-Workbook.salam
Inspect
func Inspect(path: str, ok &: bool, err &: str): FileMetaThe example for this item is shown with the ones above: excel-Workbook.salam
NewStringCell
func NewStringCell(row: int, col: int, value: str): CellThe example for this item is shown with the ones above: excel-Cell.salam
NewNumberCell
func NewNumberCell(row: int, col: int, value: f64): CellThe example for this item is shown with the ones above: excel-Cell.salam
NewBoolCell
func NewBoolCell(row: int, col: int, value: bool): CellThe example for this item is shown with the ones above: excel-Cell.salam
NewDateCell
func NewDateCell(row: int, col: int, epoch: i64): CellThe example for this item is shown with the ones above: excel-Cell.salam
NewFormulaCell
func NewFormulaCell(row: int, col: int, formula: str, cached: f64): CellThe example for this item is shown with the ones above: excel-Cell.salam
ColumnLabel
func ColumnLabel(col: int): strThe example for this item is shown with the ones above: excel-Cell.salam
ColumnIndex
func ColumnIndex(label: str): intThe example for this item is shown with the ones above: excel-Cell.salam
CellRef
func CellRef(row: int, col: int): strC5, from a zero based row and column.The example for this item is shown with the ones above: excel-Cell.salam
ParseCellRef
func ParseCellRef(ref: str, row_out &: int, col_out &: int): boolC5 into a zero based row and column.The example for this item is shown with the ones above: excel-Cell.salam
EpochToSerial
func EpochToSerial(epoch: i64): f64The example for this item is shown with the ones above: excel-Cell.salam
SerialToEpoch
func SerialToEpoch(serial: f64): i64The example for this item is shown with the ones above: excel-Cell.salam
IsXLS
func IsXLS(p: void*, n: int): boolThe example for this item is shown with the ones above: excel-Workbook.salam
ReadXLS
func ReadXLS(path: str, ok &: bool, err &: str): Workbookok false, with an err that suggests converting the file to xlsx or ods.The example for this item is shown with the ones above: excel-Workbook.salam
SheetToJSON
func SheetToJSON(sheet: Sheet): strThe example for this item is shown with the ones above: excel-Convert.salam
JSONToSheet
func JSONToSheet(text: str, sheet_name: str): SheetThe example for this item is shown with the ones above: excel-Convert.salam
ReadODS
func ReadODS(path: str, ok &: bool, err &: str): WorkbookThe example for this item is shown with the ones above: excel-Workbook.salam
NewSheet
func NewSheet(name: str): SheetThe example for this item is shown with the ones above: excel-Sheet.salam
get_number
func get_number(row: int, col: int): f64WriteODS
func WriteODS(book &: Workbook, path: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam
DetectFormat
func DetectFormat(path: str): intThe example for this item is shown with the ones above: excel-Workbook.salam
New
func New(): WorkbookNewWorkbook.The example for this item is shown with the ones above: excel-Workbook.salam
Open
func Open(path: str, ok &: bool, err &: str): Workbookok is false and err holds the message.The example for this item is shown with the ones above: excel-Workbook.salam
Save
func Save(book &: Workbook, path: str): bool.xlsx, .ods or .csv (for csv only the active sheet is written). Any other extension, .xls included, returns false.The example for this item is shown with the ones above: excel-Workbook.salam
NewStyleSheet
func NewStyleSheet(): StyleSheetThe example for this item is shown with the ones above: excel-Cell.salam
XmlEscape
func XmlEscape(s: str): strThe example for this item is shown with the ones above: excel-Convert.salam
XmlUnescape
func XmlUnescape(s: str): strThe example for this item is shown with the ones above: excel-Convert.salam
XmlFind
func XmlFind(xml: str, tag: str, from: int): intfrom onwards.The example for this item is shown with the ones above: excel-Convert.salam
XmlTagEnd
func XmlTagEnd(xml: str, start: int): int> of the tag that starts at start.The example for this item is shown with the ones above: excel-Convert.salam
XmlSelfClosing
func XmlSelfClosing(xml: str, start: int): boolThe example for this item is shown with the ones above: excel-Convert.salam
XmlAttr
func XmlAttr(xml: str, start: int, attr: str): strThe example for this item is shown with the ones above: excel-Convert.salam
XmlFindClose
func XmlFindClose(xml: str, tag: str, content_start: int): intThe example for this item is shown with the ones above: excel-Convert.salam
XmlInner
func XmlInner(xml: str, tag: str, from: int): strThe example for this item is shown with the ones above: excel-Convert.salam
NewWorkbook
func NewWorkbook(): WorkbookThe example for this item is shown with the ones above: excel-Workbook.salam
WriteXLSX
func WriteXLSX(book &: Workbook, path: str): boolThe example for this item is shown with the ones above: excel-Workbook.salam