API Reference

xl_parse(x::AbstractString) -> XLWorkbook
xl_parse(x::Vector{UInt8}) -> XLWorkbook

Parse Excel file into XLWorkbook object.

Examples

julia> raw_xlsx = read("assets/employee_sample_data.xlsx")
77626-element Vector{UInt8}:
 0x50
 0x4b
    ⋮
 0x00

julia> xl_parse(raw_xlsx)
1-element XLWorkbook:
 1001x13 XLSheet("Employee")

xl_open(file::AbstractString) -> XLWorkbook

Read the specified XL file and parse it into XLWorkbook.

xl_open(io::IO) -> XLWorkbook

Read data from the specified IO object and parse it into an XLWorkbook.

XLWorkbook <: AbstractVector{XLSheet}

Represents an Excel workbook containing XLSheet.

Fields

• sheets::Vector{XLSheet}: Workbook sheets.

Accessors

• xl_sheetnames(x::XLWorkbook): Workbook sheet names.

See also: xl_parse.

AbstractXLSheet <: AbstractArray{Any,2}

Abstract supertype for XLSheet and SubXLSheet.

XLSheet <: AbstractXLSheet

Sheet of the XLWorkbook. Supports indexing like a regular Matrix, as well as address indexing (e.g. A, A1, AB3 or range D:E, B1:C10, etc.).

The sheet slice will be converted into a SubXLSheet.

Fields

• name::String: Sheet name.
• table::Matrix{AbstractXLCell}: Table representation.

Accessors

• xl_sheetname(x::XLSheet): Sheet name.
• xl_nrow(x::XLSheet): Number of rows.
• xl_ncol(x::XLSheet): Number of columns.

See also: xl_rowtable, xl_columntable, xl_print.

Examples

julia> xlsx = xl_parse(read("assets/stock_sample_data.xlsx"))
1-element XLWorkbook:
 41x6 XLSheet("Stock")

julia> sheet = xlsx["Stock"]
41x6 XLSheet("Stock")
 Sheet │ A     B        C        D            E          F                 
───────┼──────────────────────────────────────────────────────────────────
     1 │ name  price    h24      volume       mkt        sector            
     2 │ MSFT  430.16   0.0007   1.1855456e7  3.197e12   Technology Serv…  
     3 │ AAPL  189.98   -0.0005  3.6327e7     2.913e12   Electronic Tech…  
     4 │ NVDA  1064.69  0.0045   4.2948e7     2.662e12   Electronic Tech…  
     ⋮ │ ⋮     ⋮        ⋮        ⋮            ⋮          ⋮                 
    40 │ JNJ   146.97   0.0007   7.173e6      3.5371e11  Health Technolo…  
    41 │ ORCL  122.91   -0.0003  5.984e6      3.3782e11  Technology Serv… 

SubXLSheet <: AbstractXLSheet

Slice view of the XLSheet object. Supports the same operations as XLSheet.

Fields

• data::Matrix: Table data.

Accessors

• parent(x::SubXLSheet): Parent sheet.
• xl_nrow(x::SubXLSheet): Number of rows.
• xl_ncol(x::SubXLSheet): Number of columns.

See also: xl_rowtable, xl_columntable, xl_print.

Examples

julia> xlsx = xl_parse(read("assets/stock_sample_data.xlsx"))
1-element XLWorkbook:
 41x6 XLSheet("Stock")

julia> xlsx["Stock"]["A1:D25"]
25x4 SubXLSheet("Stock")
 Sheet │ A     B        C        D            
───────┼─────────────────────────────────────
     1 │ name  price    h24      volume       
     2 │ MSFT  430.16   0.0007   1.1855456e7  
     3 │ AAPL  189.98   -0.0005  3.6327e7     
     4 │ NVDA  1064.69  0.0045   4.2948e7     
     ⋮ │ ⋮     ⋮        ⋮        ⋮              
    24 │ NVDA  1064.69  0.0045   4.2948e7     
    25 │ GOOG  176.33   -0.0006  1.1404e7  

xl_rowtable(sheet::AbstractXLSheet; kw...) -> Vector{NamedTuple}

Converts sheet rows to a Vector of NamedTuples.

Keyword arguments

• alt_keys: Alternative custom column headers (Dict{String,String} or Vector{String}).
• header::Bool = false: Use first row elements as column headers.

Examples

julia> xlsx = xl_parse(read("assets/stock_sample_data.xlsx"))
1-element XLWorkbook:
 41x6 XLSheet("Stock")

julia> xl_rowtable(xlsx["Stock"]["A1:C30"], header = true)
29-element Vector{NamedTuple{(:name, :price, :h24)}}:
 (name = "MSFT", price = 430.16, h24 = 0.0007)
 (name = "AAPL", price = 189.98, h24 = -0.0005)
 (name = "NVDA", price = 1064.69, h24 = 0.0045)
 (name = "GOOG", price = 176.33, h24 = -0.0006)
 ⋮
 (name = "LLY", price = 807.43, h24 = -0.0024)
 (name = "AVGO", price = 1407.84, h24 = 0.0036)

xl_columntable(sheet::AbstractXLSheet; kw...) -> Vector{NamedTuple}

Converts sheet columns to a Vector of NamedTuples.

Keyword arguments

• alt_keys: Alternative custom column headers (Dict{String,String} or Vector{String}).
• header::Bool = false: Use first row elements as column headers.

Examples

julia> xlsx = xl_parse(read("assets/stock_sample_data.xlsx"))
1-element XLWorkbook:
 41x6 XLSheet("Stock")

julia> alt_keys = Dict("A" => "Name", "B" => "Price", "C" => "H24");

julia> xl_columntable(xlsx["Stock"][2:end, 1:3]; alt_keys)
(
    Name = Any["MSFT", "AAPL"  …  "JNJ", "ORCL"],
    Price = Any[430.16, 189.98  …  146.97, 122.91],
    H24 = Any[0.0007, -0.0005  …  0.0007, -0.0003],
)

eachrow(x::AbstractXLSheet; kw...)

Creates a table row iterator. Row slices are returned as NamedTuple.

Keyword arguments

• alt_keys: Alternative custom column headers (Dict{String,String} or Vector{String}).
• header::Bool = false: Use first row elements as column headers.

Examples

julia> xlsx = xl_parse(read("assets/stock_sample_data.xlsx"))
1-element XLWorkbook:
 41x6 XLSheet("Stock")

julia for row in eachrow(xlsx["Stock"]; header = true)
          println(row)
      end
(name = "MSFT", price = 430.16, h24 = 0.0007, volume = 1.1855456e7, ...)
(name = "AAPL", price = 189.98, h24 = -0.0005, volume = 3.6327e7, ...)
(name = "NVDA", price = 1064.69, h24 = 0.0045, volume = 4.2948e7, ...)
(name = "GOOG", price = 176.33, h24 = -0.0006, volume = 1.1404e7, ...)
 ⋮
(name = "JNJ", price = 146.97, h24 = 0.0007, volume = 7.173e6, ...)
(name = "ORCL", price = 122.91, h24 = -0.0003, volume = 5.984e6, ...)

xl_print([io::IO], sheet::AbstractXLSheet; kw...)

Print a sheet as a table representation.

Keyword arguments

• title::AbstractString = "Sheet": Table title in upper left corner.
• header::Bool = false: Use first row elements as column headers.
• max_len::Int = 16: Maximum length of an element in a cell.
• compact::Bool = true: Omit rows and columns to save space.

Examples

julia> xlsx = xl_parse(read("assets/employee_sample_data.xlsx"))
1-element XLWorkbook:
 1001x13 XLSheet("Employee")

julia> xl_print(xlsx["Employee"]; header = true)
 Sheet │ eeid    full_name        job_title         ⋯  city       exit_date        
───────┼───────────────────────────────────────────────────────────────────────────
     2 │ E02387  Emily Davis      Sr. Manger        ⋯  Seattle    2021-10-16T00:0… 
     3 │ E04105  Theodore Dinh    Technical Archi…  ⋯  Chongqing  nothing          
     4 │ E02572  Luna Sanders     Director          ⋯  Chicago    nothing          
     5 │ E02832  Penelope Jordan  Computer System…  ⋯  Chicago    nothing          
     ⋮ │ ⋮       ⋮                ⋮                 ⋯  ⋮          ⋮                  
  1000 │ E02521  Lily Nguyen      Sr. Analyst       ⋯  Chengdu    nothing          
  1001 │ E03545  Sofia Cheng      Vice President    ⋯  Miami      nothing 

format_description(code::Int) -> String

Returns a formatting description by its code (see Number Format).

julia> format_description(0)
"General"

julia> format_description(3)
"#,##0"

julia> format_description(11)
"0.00E+00"

julia> format_description(19)
"h:mm:ss AM/PM"

index_to_column_letter(inx::Int) -> String

Converts a numerical index into its corresponding Excel column letter ("A", "B", ..., "Z", "AA", etc.).

Examples

julia> index_to_column_letter(1)
"A"

julia> index_to_column_letter(26)
"Z"

julia> index_to_column_letter(27)
"AA"

column_letter_to_index(letter::AbstractString) -> Int

Converts an Excel column letter ("A", "B", ..., "Z", "AA", etc.) to its corresponding numerical index.

Examples

julia> column_letter_to_index("A")
1

julia> column_letter_to_index("Z")
26

julia> column_letter_to_index("AA")
27