Excelin
A library to work with spreadsheet file (strictly .xlsx) without dependency outside of Nim compiler (and its requirement) and its development environment.
Types
Excel = ref object content: XmlNode rels: XmlNode workbook: Workbook sheets: TableRef[FilePath, Sheet] sharedStrings: SharedStrings otherfiles: TableRef[string, FileRep] embedfiles: TableRef[string, EmbedFile] sheetCount: int
- The object the represent as Excel file, mostly used for reading the Excel and writing it to Zip file and to memory buffer (at later version). Source Edit
Workbook = ref object of InternalBody path: string sheetsInfo: seq[XmlNode] rels: FileRep parent: Excel
- The object that used for managing package information of Excel file. Most users won't need to use this. Source Edit
Sheet = ref object of InternalBody parent: Excel rid: string privName: string filename: string lastRowNum: Natural
- The main object that will be used most of the time for many users. This object will represent a sheet in Excel file such as adding row getting row, and/or adding cell directly. Source Edit
Row = ref object of InternalBody sheet: Sheet
- The object that will be used for working with values within cells of a row. Users can get the value within cell and set its value. Source Edit
ExcelError = object of CatchableError
- Error when the Excel file read is invalid, specifically Excel file that doesn't have workbook. Source Edit
CellFill = enum cfSparse = "sparse", cfFilled = "filled"
- Source Edit
Formula = object equation*: string valueStr*: string
- Object exclusively working with formula in a cell. The equation is simply formula representative and the valueStr is the value in its string format, which already calculated beforehand. Source Edit
Hyperlink = object target*: string text*: string tooltip*: string
- Object that will be used to fill cell with external link. Source Edit
Font = object name*: string family*: int charset*: int size*: Positive bold*: bool italic*: bool strike*: bool outline*: bool shadow*: bool condense*: bool extend*: bool color*: string underline*: Underline verticalAlign*: VerticalAlign
- Cell font styling. Provide name if it's intended to style the cell. If no name is supplied, it will ignored. Field family and charset are optionals but in order to be optional, provide it with negative value because there's value for family and charset 0. Since by default int is 0, it could be yield different style if the family and charset are not intended to be filled but not assigned with negative value. Source Edit
Underline = enum uNone = "none", uSingle = "single", uDouble = "double", uSingleAccounting = "singleAccounting", uDoubleAccounting = "doubleAccounting"
- Source Edit
VerticalAlign = enum vaBaseline = "baseline", vaSuperscript = "superscript", vaSubscript = "subscript"
- Source Edit
Border = object edit: bool start*: BorderProp `end`*: BorderProp top*: BorderProp bottom*: BorderProp vertical*: BorderProp horizontal*: BorderProp diagonalUp*: bool diagonalDown*: bool
- The object that will define the border we want to apply to cell. Use border to initialize working border instead because the indication whether border can be edited is private. Source Edit
BorderProp = object edit: bool ## indicate whether border properties is filled style*: BorderStyle color*: string
- The object that will define the style and color we want to apply to border Use borderProp to initialize working border prop instead because the indication whether border properties filled is private. Source Edit
BorderStyle = enum bsNone = "none", bsThin = "thin", bsMedium = "medium", bsDashed = "dashed", bsDotted = "dotted", bsThick = "thick", bsDouble = "double", bsHair = "hair", bsMediumDashed = "mediumDashed", bsDashDot = "dashDot", bsMediumDashDot = "mediumDashDot", bsDashDotDot = "dashDotDot", bsMediumDashDotDot = "mediumDashDotDot", bsSlantDashDot = "slantDashDot"
- Source Edit
Fill = object edit: bool pattern*: PatternFill gradient*: GradientFill
- Fill cell style. Use fillStyle to initialize this object to indicate cell will be edited with this Fill. Source Edit
PatternFill = object edit: bool fgColor*: string bgColor: string patternType*: PatternType
- Pattern to fill the cell. Use patternFill to initialize. Source Edit
PatternType = enum ptNone = "none", ptSolid = "solid", ptMediumGray = "mediumGray", ptDarkGray = "darkGray", ptLightGray = "lightGray", ptDarkHorizontal = "darkHorizontal", ptDarkVertical = "darkVertical", ptDarkDown = "darkDown", ptDarkUp = "darkUp", ptDarkGrid = "darkGrid", ptDarkTrellis = "darkTrellis", ptLightHorizontal = "lightHorizontal", ptLightVertical = "lightVertical", ptLightDown = "lightDown", ptLightUp = "lightUp", ptLightGrid = "lightGrid", ptLightTrellis = "lightTrellis", ptGray125 = "gray125", ptGray0625 = "gray0625"
- Source Edit
GradientFill = object edit: bool stop*: GradientStop `type`*: GradientType degree*: float left*: float right*: float top*: float bottom*: float
- Gradient to fill the cell. Use gradientFill to initialize. Source Edit
GradientStop = object color*: string position*: float
- Indicate where the gradient will stop with its color at stopping position. Source Edit
GradientType = enum gtLinear = "linear", gtPath = "path"
- Source Edit
Range = (string, string)
- Range of table which consist of top left cell and bottom right cell. Source Edit
FilterType = enum ftFilter, ftCustom
- Source Edit
Filter = object case kind*: FilterType of ftFilter: valuesStr*: seq[string] of ftCustom: logic*: CustomFilterLogic customs*: seq[(FilterOperator, string)]
- Filtering that supplied to column id in sheet range. Ignored if the sheet hasn't set its auto filter range. Source Edit
FilterOperator = enum foEq = "equal", foLt = "lessThan", foLte = "lessThanOrEqual", foNeq = "notEqual", foGte = "greaterThanOrEqual", foGt = "greaterThan"
- Source Edit
CustomFilterLogic = enum cflAnd = "and", cflOr = "or", cflXor = "xor"
- Source Edit
Consts
excelinVersion = "0.5.3"
- Source Edit
Procs
proc toNum(col: string): int {...}{.raises: [], tags: [].}
-
Convert our column string to its numeric representation. Make sure the supplied column is already in upper case. 0-based e.g.: "A".toNum == 0, "C".toNum == 2 Complement of toCol.
Example:
let colnum = [("A", 0), ("AA", 26), ("AB", 27), ("ZZ", 701), ("AAA", 702), ("AAB", 703)] for cn in colnum: doAssert cn[0].toNum == cn[1]
Source Edit proc toCol(n: Natural): string {...}{.raises: [], tags: [].}
-
Convert our numeric column to string representation. The numeric should be 0-based, e.g.: 0.toCol == "A", 25.toCol == "Z" Complement of toNum.
Example:
let colnum = [("A", 0), ("AA", 26), ("AB", 27), ("ZZ", 701), ("AAA", 702), ("AAB", 703)] for cn in colnum: doAssert cn[1].toCol == cn[0]
Source Edit proc modifiedAt[T: DateTime | Time](e: Excel; t: T = now())
- Update Excel modification time. Source Edit
proc modifiedAt[Node: Workbook | Sheet; T: DateTime | Time](w: Node; t: T = now())
- Update workbook or worksheet modification time. Source Edit
proc rowNum(r: Row): Positive {...}{.raises: [], tags: [].}
- Getting the current row number of Row object users working it. Source Edit
proc `[]=`(row: Row; col: string; s: string) {...}{.raises: [ValueError, KeyError], tags: [TimeEffect].}
- Add cell with overload for value string. Supplied column is following the Excel convention starting from A - Z, AA - AZ ... Source Edit
proc `[]=`(row: Row; col: string; n: SomeNumber)
- Add cell with overload for any number. Source Edit
proc `[]=`(row: Row; col: string; b: bool) {...}{.raises: [ValueError, KeyError], tags: [TimeEffect].}
- Add cell with overload for truthy value. Source Edit
proc `[]=`(row: Row; col: string; d: DateTime | Time)
- Add cell with overload for DateTime or Time. The saved value will be in string format of yyyy-MM-dd'T'HH:mm:ss'.'fffzz e.g. 2200-10-01T11:22:33.456-03. Source Edit
proc `[]=`(row: Row; col: string; f: Formula) {...}{.raises: [ValueError, KeyError], tags: [TimeEffect].}
- Source Edit
proc `[]=`(row: Row; col: string; h: Hyperlink) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Source Edit
proc getCell[R](row: Row; col: string; conv: string -> R = nil): R
-
Get cell value from row with optional function to convert it. When conversion function is supplied, it will be used instead of default conversion function viz:
- SomeSignedInt (int/int8/int32/int64): strutils.parseInt
- SomeUnsignedInt (uint/uint8/uint32/uint64): strutils.parseUint
- SomeFloat (float/float32/float64): strutils.parseFloat
- DateTime | Time: times.format with layout yyyy-MM-dd'T'HH:mm:ss'.'fffzz
For simple usage example:
let strval = row.getCell[:string]("A") # we're fetching string value in colum A let intval = row.getCell[:int]("B") let uintval = row.getCell[:uint]("C") let dtval = row.getCell[:DateTime]("D") let timeval = row.getCell[:Time]("E") # below example we'll get the DateTime that has been formatted like 2200/12/01 # so we supply the optional custom converter function let dtCustom = row.getCell[:DateTime]("F", (s: string) -> DateTime => ( s.parse("yyyy/MM/dd")))
Any other type that other than mentioned above should provide the closure proc for the conversion otherwise it will return the default value, for example any ref object will return nil or for object will get the object with its field filled with default values.
Source Edit proc `[]`(r: Row; col: string; ret: typedesc): ret:type
-
Getting cell value from supplied return typedesc. This is overload of basic supported values that will return default value e.g.:
- string default to ""
- SomeSignedInt default to int.low
- SomeUnsignedInt default to uint.high
- SomeFloat default to NaN
- DateTime and Time default to empty object time
Other than above mentioned types, see getCell proc for supplying the converting closure for getting the value.
Source Edit proc lastCol(r: Row): string {...}{.raises: [], tags: [].}
- Fetch last column of available cells. Return empty string if the row is empty. Source Edit
proc row(s: Sheet; rowNum: Positive; fill = cfSparse): Row {...}{.raises: [KeyError], tags: [TimeEffect].}
- Add row by selecting which row number to work with. This will return new row if there's no existing row or will return an existing one. Source Edit
proc hide=(row: Row; yes: bool) {...}{.raises: [], tags: [].}
- Hide the current row Source Edit
proc hidden(row: Row): bool {...}{.raises: [], tags: [].}
- Check whether row is hidden Source Edit
proc height=(row: Row; height: Natural) {...}{.raises: [], tags: [].}
- Set the row height which sets its attribute to custom height. If the height 0, will reset its custom height. Source Edit
proc height(row: Row): Natural {...}{.raises: [], tags: [].}
- Source Edit
proc outlineLevel=(row: Row; level: Natural) {...}{.raises: [], tags: [].}
- Set the outline level for the row. Level 0 means resetting the level. Source Edit
proc outlineLevel(row: Row): Natural {...}{.raises: [], tags: [].}
- Source Edit
proc collapsed=(row: Row; yes: bool) {...}{.raises: [], tags: [].}
- Collapse the current row, usually used together with outline level. Source Edit
proc clear(row: Row) {...}{.raises: [], tags: [].}
- Clear all cells in the row. Source Edit
proc pageBreak(row: Row; maxCol, minCol = 0; manual = true) {...}{.raises: [], tags: [].}
- Add horizontal page break after the current row working on. Set the horizontal page break length up to intended maxCol. Source Edit
proc lastRow(sheet: Sheet): Row {...}{.raises: [], tags: [].}
- Fetch the last row available with option to fetch whether it's empty/hidden or not. Source Edit
proc empty(row: Row): bool {...}{.raises: [], tags: [].}
- Check whether there's no cell in row. Used to check whether proc clear was called or simply there's no cell available yet. Source Edit
proc shareStyle(row: Row; col: string; targets: varargs[string]) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Share style from source row and col string to any arbitrary cells in format {Col}{Num} e.g. A1, B2, C3 etc. Changing the shared style will affect entire cells that has shared style. Source Edit
proc copyStyle(row: Row; col: string; targets: varargs[string]) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Copy style from row and col source to targets. The difference with shareStyle proc<#shareStyle is copy will make a new same style. So changing targets cell style later won't affect the source and vice versa. Source Edit
proc fontStyle(name: string; size = 10; family, charset = -1; bold, italic, strike, outline, shadow, condense, extend = false; color = ""; underline = uNone; verticalAlign = vaBaseline): Font {...}{. raises: [], tags: [].}
- Source Edit
proc borderStyle(start, end, top, bottom, vertical, horizontal = BorderProp(); diagonalUp, diagonalDown = false): Border {...}{.raises: [], tags: [].}
-
Border initializer. Use this instead of object constructor to indicate style is ready to apply this border.
Example:
import std/with import excelin var b = border(diagonalUp = true) with b: start = borderProp(style = bsMedium) # border style diagonalDown = true doAssert b.diagonalUp doAssert b.diagonalDown doAssert b.start.style == bsMedium
Source Edit proc border(start, end, top, bottom, vertical, horizontal = BorderProp(); diagonalUp, diagonalDown = false): Border {...}{. deprecated: "use borderStyle", raises: [], tags: [].}
- Source Edit
proc borderPropStyle(style = bsNone; color = ""): BorderProp {...}{.raises: [], tags: [].}
- Source Edit
proc borderProp(style = bsNone; color = ""): BorderProp {...}{. deprecated: "use borderPropStyle", raises: [], tags: [].}
- Source Edit
proc fillStyle(pattern = PatternFill(); gradient = GradientFill()): Fill {...}{. raises: [], tags: [].}
- Source Edit
proc patternFillStyle(fgColor = $16777215; patternType = ptNone): PatternFill {...}{. raises: [], tags: [].}
- Source Edit
proc patternFill(fgColor = $16777215; patternType = ptNone): PatternFill {...}{. deprecated: "use patternFillStyle", raises: [], tags: [].}
- Source Edit
proc gradientFillStyle(stop = GradientStop(); type = gtLinear; degree, left, right, top, bottom = 0.0): GradientFill {...}{. raises: [], tags: [].}
- Source Edit
proc gradientFill(stop = GradientStop(); type = gtLinear; degree, left, right, top, bottom = 0.0): GradientFill {...}{. deprecated: "use gradientFillStyle", raises: [], tags: [].}
- Source Edit
proc style(row: Row; col: string; font = Font(size: 1); border = Border(); fill = Fill(); alignment: openArray[(string, string)] = []) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Add style to cell in row by selectively providing the font, border, fill and alignment styles. Source Edit
proc shareStyle(sheet: Sheet; source: string; targets: varargs[string]) {...}{. raises: [KeyError, ValueError], tags: [TimeEffect].}
- Share style from source {col}{row} to targets {col}{row}, i.e. sheet.shareStyle("A1", "B2", "C3") which shared the style in cell A1 to B2 and C3. Source Edit
proc copyStyle(sheet: Sheet; source: string; targets: varargs[string]) {...}{. raises: [KeyError, ValueError], tags: [TimeEffect].}
- Copy style from source {col}{row} to targets {col}{row}, i.e. sheet.shareStyle("A1", "B2", "C3") which copied style from cell A1 to B2 and C3. Source Edit
proc resetStyle(sheet: Sheet; targets: varargs[string]) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Reset any styling to default. Source Edit
proc resetStyle(row: Row; targets: varargs[string]) {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Reset any styling to default. Source Edit
proc styleFont(row: Row; col: string): Font {...}{.raises: [ValueError, KeyError], tags: [].}
- Get the style font from the cell in the row. Source Edit
proc styleFont(sheet: Sheet; colrow: string): Font {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Get the style font from the sheet to specified cell. Source Edit
proc styleFill(row: Row; col: string): Fill {...}{.raises: [ValueError, KeyError], tags: [].}
- Get the style fill from the cell in the row. Source Edit
proc styleFill(sheet: Sheet; colrow: string): Fill {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Get the style fill from the sheet to specified cell. Source Edit
proc styleBorder(row: Row; col: string): Border {...}{. raises: [ValueError, KeyError], tags: [].}
- Get the style border from the cell in the row. Source Edit
proc styleBorder(sheet: Sheet; colrow: string): Border {...}{. raises: [ValueError, KeyError], tags: [TimeEffect].}
- Get the style fill from the border to specified cell. Source Edit
proc getSheet(e: Excel; name: string): Sheet {...}{.raises: [KeyError], tags: [].}
- Source Edit
proc addSheet(e: Excel; name = ""): Sheet {...}{.raises: [ValueError, Exception], tags: [RootEffect].}
-
Add new sheet to excel with supplied name and return it to enable further working. The name can use the existing sheet name. Sheet name by default will in be "Sheet{num}" format with num is number of available sheets increased each time adding sheet. The new empty Excel file starting with Sheet1 will continue to Sheet2, Sheet3 ... each time this function is called. For example snip code below:
let (excel, sheet1) = newExcel() doAssert sheet1.name == "Sheet1" excel.deleteSheet "Sheet1" let newsheet = addSheet excel doAssert newsheet.name == "Sheet2" # instead of to be Sheet1
This is because the counter for sheet will not be reduced despite of deleting the sheet in order to reduce maintaining relation-id cross reference.
Source Edit proc deleteSheet(e: Excel; name = "") {...}{.raises: [], tags: [].}
- Delete sheet based on its name. Will ignore when it cannot find the name. Delete the first (or older) sheet when there's a same name. Check sheetNames proc to get available names. Source Edit
proc name(s: Sheet): string {...}{.raises: [], tags: [].}
- Get the name of current sheet Source Edit
proc name=(s: Sheet; newname: string) {...}{.raises: [], tags: [].}
- Update sheet's name. Source Edit
proc resetMerge(sheet: Sheet; range: Range) {...}{.raises: [KeyError, ValueError], tags: [TimeEffect].}
- Remove any merge cell with defined range. Ignored if there's no such such range supplied. Source Edit
proc ranges=(sheet: Sheet; range: Range) {...}{.raises: [], tags: [].}
- Set the ranges of data/table within sheet. Source Edit
proc autoFilter=(sheet: Sheet; range: Range) {...}{.raises: [], tags: [].}
- Add auto filter to selected range. Setting this range will override the previous range setting to sheet. Providing with range ("", "") will delete the auto filter in the sheet. Source Edit
proc autoFilter(sheet: Sheet): Range {...}{.raises: [], tags: [].}
- Retrieve the set range for auto filter. Mainly used to check whether the range for set is already set to add filtering to its column number range (0-based). Source Edit
proc filterCol(sheet: Sheet; colId: Natural; filter: Filter) {...}{.raises: [], tags: [].}
- Set filter to the sheet range. Ignored if sheet hasn't set its auto filter range. Set the col with default Filter() to reset it. Source Edit
proc mergeCells=(sheet: Sheet; range: Range) {...}{.raises: [KeyError, ValueError], tags: [TimeEffect].}
- Merge cells will remove any existing values within range cells to be merged. Will only retain the topleft cell value when merging the range. Source Edit
proc hideCol(sheet: Sheet; col: string; hide: bool) {...}{.raises: [], tags: [].}
- Hide entire column in the sheet. Source Edit
proc outlineLevelCol(sheet: Sheet; col: string; level: Natural) {...}{.raises: [], tags: [].}
- Set outline level for the entire column in the sheet. Source Edit
proc collapsedCol(sheet: Sheet; col: string; collapsed: bool) {...}{.raises: [], tags: [].}
- Set whether the column is collapsed or not. Source Edit
proc isCollapsedCol(sheet: Sheet; col: string): bool {...}{.raises: [], tags: [].}
- Check whether the column in sheet is collapsed or not. Source Edit
proc widthCol(sheet: Sheet; col: string; width: float) {...}{.raises: [], tags: [].}
-
Set the entire column width. Set with 0 width to reset it. The formula to count what's the width is as below: float(int({NumOfChars}*{MaxDigitPixel}+{5 pixel padding}) / {MaxDigitPixel} * 256) / 256 .
For example Calibri has maximum width of 11 point, i.e. 7 pixel at 96 dpi at default style. If we want to set the column support 8 chars, the value would be: doAssert float((8*7+5) / 7 * 256) / 256 == 8.714285714285714
Source Edit proc bestFitCol(sheet: Sheet; col: string; yes: bool) {...}{.raises: [], tags: [].}
- Set the column width with best fit which the column is not set manually or not default width. Best fit means the column width will automatically resize its width to display. Source Edit
proc pageBreakCol(sheet: Sheet; col: string; maxRow, minRow = 0; manual = true) {...}{. raises: [], tags: [].}
- Set vertical page break on the right of column. Set the maximum row for the vertical length of the page break. Source Edit
proc hide=(sheet: Sheet; hide: bool) {...}{.raises: [], tags: [].}
- Hide sheet from workbook view. Source Edit
proc hidden(sheet: Sheet): bool {...}{.raises: [], tags: [].}
- Check whether sheet is hidden or not. Source Edit
proc readExcel(path: string): Excel {...}{.raises: [IOError, OSError, ZippyError, ValueError, Exception, XmlError, ExcelError, IOError, OSError, ValueError, Exception, XmlError, ExcelError, KeyError], tags: [ReadIOEffect, RootEffect, WriteIOEffect].}
- Read Excel file from supplied path. Will raise OSError in case path is not exists, IOError when system errors during reading the file, ExcelError when the Excel file is not valid (Excel file that has no workbook). Source Edit
proc prop=(e: Excel; prop: varargs[(string, string)]) {...}{.raises: [KeyError], tags: [].}
- Add information property to Excel file. Will add the properties to the existing. Source Edit
proc createdAt(excel: Excel; at: DateTime | Time = now())
- Set the created at properties to our excel. Useful when we're creating an excel from template so we set the creation date to current date which different with template created date. Source Edit
proc newExcel(appName = "Excelin"): (Excel, Sheet) {...}{.raises: [IOError, OSError, ZippyError, ValueError, Exception, XmlError, ExcelError, KeyError], tags: [ReadIOEffect, RootEffect, WriteIOEffect, TimeEffect].}
- Return a new Excel and Sheet at the same time to work for both. The Sheet returned is by default has name "Sheet1" but user can use name= proc to change its name. Source Edit
proc writeFile(e: Excel; targetpath: string) {...}{.raises: [IOError, ZippyError], tags: [TimeEffect, WriteIOEffect].}
- Write Excel to file in target path. Raise OSError when it can't write to the intended path. Source Edit
proc `$`(e: Excel): string {...}{.raises: [ValueError, IOError, ZippyError], tags: [ TimeEffect, ReadEnvEffect, ReadIOEffect, WriteIOEffect, WriteDirEffect].}
- Get Excel file as string. Currently implemented by writing to temporary dir first because there's no API to get the data directly. Source Edit
proc sheetNames(e: Excel): seq[string] {...}{.raises: [], tags: [].}
- Return all availables sheet names within an Excel file. Source Edit
Iterators
iterator cols(r: Row): string {...}{.closure, raises: [], tags: [].}
- Iterate available cell columns has filled with values. Return its column. Use proc lastCol to get its last column cell. Source Edit
iterator rows(sheet: Sheet): Row {...}{.closure, raises: [], tags: [].}
- rows will iterate each row in the supplied sheet regardless whether it's empty or hidden. Source Edit
Templates
template `$`(r: Range): string
- Source Edit
template getCellIt[R](r: Row; col: string; body: untyped): untyped
-
Shorthand for getCell with injected it in body. For example:
from std/times import parse, year, month, DateTime, Month, monthday # the value in cell is "2200/12/01" let dt = row.getCell[:DateTime]("F", (s: string) -> DateTime => ( s.parse("yyyy/MM/dd"))) doAssert dt.year == 2200 doAssert dt.month == mDec doAssert dt.monthday = 1
Source Edit