text:
import XLSX
using Test, Dates

data_directory = joinpath(@__DIR__, "..", "data")
@assert isdir(data_directory)

@testset "read test files" begin
    ef_blank_ptbr_1904 = XLSX.readxlsx(joinpath(data_directory, "blank_ptbr_1904.xlsx"))
    ef_Book1 = XLSX.readxlsx(joinpath(data_directory, "Book1.xlsx"))
    ef_Book_1904 = XLSX.readxlsx(joinpath(data_directory, "Book_1904.xlsx"))
    ef_book_1904_ptbr = XLSX.readxlsx(joinpath(data_directory, "book_1904_ptbr.xlsx"))
    ef_book_sparse = XLSX.readxlsx(joinpath(data_directory, "book_sparse.xlsx"))
    ef_book_sparse_2 = XLSX.readxlsx(joinpath(data_directory, "book_sparse_2.xlsx"))

    @test ef_Book1.filepath == joinpath(data_directory, "Book1.xlsx")
    @test length(keys(ef_Book1.data)) > 0

    @test ef_Book_1904.filepath == joinpath(data_directory, "Book_1904.xlsx")
    @test length(keys(ef_Book_1904.data)) > 0

    @test !XLSX.isdate1904(ef_Book1)
    @test XLSX.isdate1904(ef_Book_1904)
    @test XLSX.isdate1904(ef_blank_ptbr_1904)
    @test XLSX.isdate1904(ef_book_1904_ptbr)

    @test XLSX.sheetnames(ef_Book1) == [ "Sheet1", "Sheet2" ]
    @test XLSX.sheetcount(ef_Book1) == 2
    @test ef_Book1["Sheet1"].name == "Sheet1"
    @test ef_Book1[1].name == "Sheet1"

    @test XLSX.sst_unformatted_string(ef_Book1.workbook, 0) == "B2" # index is 0-based
    @test XLSX.sst_unformatted_string(ef_Book1, 0) == "B2"
    @test XLSX.sst_unformatted_string(ef_Book1, "0") == "B2"

    @test !XLSX.has_relationship_by_type(ef_Book1.workbook, "invalid_type")

    @test XLSX.get_dimension(ef_Book1["Sheet1"]) == XLSX.range"B2:C8"
    @test XLSX.isdate1904(ef_Book1["Sheet1"]) == false

    @testset "Read XLS file error" begin
        @test_throws ErrorException XLSX.readxlsx(joinpath(data_directory, "old.xls"))
        try
            XLSX.readxlsx(joinpath(data_directory, "old.xls"))
            @test false # didn't throw exception
        catch e
            @test occursin("This package does not support XLS file format", "$e")
        end
    end

    @testset "Read invalid XLSX error" begin
        @test_throws ErrorException XLSX.readxlsx(joinpath(data_directory, "sheet_template.xml"))
        try
            XLSX.readxlsx(joinpath(data_directory, "sheet_template.xml"))
            @test false # didn't throw exception
        catch e
            @test occursin("is not a valid XLSX file", "$e")
        end
    end
end

@testset "Cell names" begin
    @test !XLSX.is_valid_cellname("A0")
    @test XLSX.is_valid_cellname("A1")
    @test !XLSX.is_valid_cellname("A")
    @test !XLSX.is_valid_cellname("1")
    @test XLSX.is_valid_cellname("XFD1048576")
    @test !XLSX.is_valid_cellname("XFD1048577")
    @test XLSX.is_valid_cellname("XFD1")
    @test !XLSX.is_valid_cellname("ZFD1")
    @test XLSX.is_valid_column_name("A")
    @test XLSX.is_valid_column_name("AZ")
    @test XLSX.is_valid_column_name("AAZ")
    @test !XLSX.is_valid_column_name("AAAZ")
    @test !XLSX.is_valid_column_name(":")
    @test !XLSX.is_valid_column_name("É")

    @test XLSX.is_valid_sheet_cellname("Sheet1!A2")
    @test !XLSX.is_valid_sheet_cellname("Sheet1!A2:B3")
    @test !XLSX.is_valid_sheet_cellname("Sheet1!A0")
    @test !XLSX.is_valid_sheet_cellname("A1")
    @test !XLSX.is_valid_sheet_cellname("Sheet1!")
    @test !XLSX.is_valid_sheet_cellname("Sheet1")
    @test !XLSX.is_valid_sheet_cellname("Sheet1!A")
    @test !XLSX.is_valid_sheet_cellname("Sheet1!1")
    @test XLSX.is_valid_sheet_cellname("NEGOCIAÇÕES Descrição!A1")

    @test XLSX.is_valid_sheet_cellrange("Sheet1!A1:B4")
    @test !XLSX.is_valid_sheet_cellrange("Sheet1!:B4")
    @test !XLSX.is_valid_sheet_cellrange("Sheet1!A1:")
    @test !XLSX.is_valid_sheet_cellrange("Sheet1!:")
    @test !XLSX.is_valid_sheet_cellrange("A1:B4")
    @test !XLSX.is_valid_sheet_cellrange("Sheet1!")
    @test !XLSX.is_valid_sheet_cellrange("Sheet1")
    @test !XLSX.is_valid_sheet_cellrange("mysheet!A1")
    @test XLSX.is_valid_sheet_cellrange("mysheet!A1:A4")

    @test XLSX.is_valid_sheet_column_range("Sheet1!A:B")
    @test XLSX.is_valid_sheet_column_range("Sheet1!AB:BC")
    @test !XLSX.is_valid_sheet_column_range("A:B")
    @test !XLSX.is_valid_sheet_column_range("Sheet1!")
    @test !XLSX.is_valid_sheet_column_range("Sheet1")

    cn = XLSX.CellRef("A1")
    @test string(cn) == "A1"
    @test XLSX.column_name(cn) == "A"
    @test XLSX.row_number(cn) == 1
    @test XLSX.column_number(cn) == 1

    cn = XLSX.CellRef("AB1")
    @test string(cn) == "AB1"
    @test XLSX.column_name(cn) == "AB"
    @test XLSX.row_number(cn) == 1
    @test XLSX.column_number(cn) == 28

    cn = XLSX.CellRef("AMI1")
    @test string(cn) == "AMI1"
    @test XLSX.column_name(cn) == "AMI"
    @test XLSX.row_number(cn) == 1
    @test XLSX.column_number(cn) == 1023

    cn = XLSX.CellRef("XFD1048576")
    @test string(cn) == "XFD1048576"
    @test XLSX.column_name(cn) == "XFD"
    @test XLSX.row_number(cn) == 1048576
    @test XLSX.column_number(cn) == 16384

    v_column_numbers = [1
    ,    15
    ,    22
    ,    23
    ,    24
    ,    25
    ,    26
    ,    27
    ,    28
    ,    29
    ,    30
    ,    38
    ,    39
    ,    40
    ,    41
    ,    42
    ,    43
    ,    44
    ,    45
    ,    46
    ,    47
    ,    48
    ,    49
    ,    50
    ,    51
    ,    52
    ,    53
    ,    54
    ,    55
    ,    56
    ,    57
    ,    58
    ,    59
    ,    60
    ,    61
    ,    74
    ,    75
    ,    76
    ,    77
    ,    78
    ,    79
    ,    80
    ,    81
    ,    82
    ,    83
    ,    84
    ,    85
    ,    86
    ,   284
    ,   285
    ,   286
    ,   287
    ,   288
    ,   289
    ,   296
    ,   297
    ,   299
    ,   300
    ,   301
    ,   700
    ,   701
    ,   702
    ,   703
    ,   704
    ,   705
    ,   706
    ,   727
    ,   728
    ,   729
    ,   730
    ,   731
    ,  1008
    ,  1013
    ,  1014
    ,  1015
    ,  1016
    ,  1017
    ,  1018
    ,  1023
    ,  1024
    ,  1376
    ,  1377
    ,  1378
    ,  1379
    ,  1380
    ,  1381
    ,  3379
    ,  3380
    ,  3381
    ,  3382
    ,  3383
    ,  3403
    ,  3404
    ,  3405
    ,  3406
    ,  3407
    , 16250
    , 16251
    , 16354
    , 16355
    , 16384]

    v_column_names = [  "A"
    , "O"
    , "V"
    , "W"
    , "X"
    , "Y"
    , "Z"
    , "AA"
    , "AB"
    , "AC"
    , "AD"
    , "AL"
    , "AM"
    , "AN"
    , "AO"
    , "AP"
    , "AQ"
    , "AR"
    , "AS"
    , "AT"
    , "AU"
    , "AV"
    , "AW"
    , "AX"
    , "AY"
    , "AZ"
    , "BA"
    , "BB"
    , "BC"
    , "BD"
    , "BE"
    , "BF"
    , "BG"
    , "BH"
    , "BI"
    , "BV"
    , "BW"
    , "BX"
    , "BY"
    , "BZ"
    , "CA"
    , "CB"
    , "CC"
    , "CD"
    , "CE"
    , "CF"
    , "CG"
    , "CH"
    , "JX"
    , "JY"
    , "JZ"
    , "KA"
    , "KB"
    , "KC"
    , "KJ"
    , "KK"
    , "KM"
    , "KN"
    , "KO"
    , "ZX"
    , "ZY"
    , "ZZ"
    , "AAA"
    , "AAB"
    , "AAC"
    , "AAD"
    , "AAY"
    , "AAZ"
    , "ABA"
    , "ABB"
    , "ABC"
    , "ALT"
    , "ALY"
    , "ALZ"
    , "AMA"
    , "AMB"
    , "AMC"
    , "AMD"
    , "AMI"
    , "AMJ"
    , "AZX"
    , "AZY"
    , "AZZ"
    , "BAA"
    , "BAB"
    , "BAC"
    , "DYY"
    , "DYZ"
    , "DZA"
    , "DZB"
    , "DZC"
    , "DZW"
    , "DZX"
    , "DZY"
    , "DZZ"
    , "EAA"
    , "WZZ"
    , "XAA"
    , "XDZ"
    , "XEA"
    , "XFD"]

    @assert length(v_column_names) == length(v_column_numbers) "Test script is wrong."

    for i in 1:length(v_column_names)
        @test XLSX.encode_column_number(v_column_numbers[i]) == v_column_names[i]
        @test XLSX.decode_column_number(v_column_names[i]) == v_column_numbers[i]
    end

    @testset "ColumnRange" begin
        c = XLSX.ColumnRange("C")
        @test c.start == 3
        @test c.stop == 3
        show(IOBuffer(), c)
    end

    @testset "CellRef" begin
        ref = XLSX.CellRef(12, 2)
        @test ref.name == "B12"
        show(IOBuffer(), ref)
    end

    cr = XLSX.range"A1:C4"
    @test string(cr) == "A1:C4"
    @test XLSX.row_number(cr.start) == 1
    @test XLSX.column_number(cr.start) == 1
    @test XLSX.row_number(cr.stop) == 4
    @test XLSX.column_number(cr.stop) == 3
    @test size(cr) == (4, 3)
    show(IOBuffer(), cr)

    cr = XLSX.range"B2:C8"
    @test XLSX.ref"B2" ∈ cr
    @test XLSX.ref"B3" ∈ cr
    @test XLSX.ref"C2" ∈ cr
    @test XLSX.ref"C3" ∈ cr
    @test XLSX.ref"C8" ∈ cr
    @test XLSX.ref"A1" ∉ cr
    @test XLSX.ref"C9" ∉ cr
    @test XLSX.ref"D4" ∉ cr
    @test size(cr) == (7, 2)

    fullrng = XLSX.range"B2:E5"
    @test fullrng ⊆ fullrng
    @test XLSX.range"B3:D4" ⊆ fullrng
    @test !issubset(XLSX.range"A1:E5", fullrng)

    @test XLSX.is_valid_cellrange("B2:C8")
    @test !XLSX.is_valid_cellrange("A:B")
    @test_throws AssertionError XLSX.CellRange("Z10:A1")
    @test_throws AssertionError XLSX.CellRange("Z1:A1")

    # hashing and equality
    @test XLSX.CellRef("AMI1") == XLSX.CellRef("AMI1")
    @test hash(XLSX.CellRef("AMI1")) == hash(XLSX.CellRef("AMI1"))
    @test XLSX.CellRange("A1:C4") == XLSX.CellRange("A1:C4")
    @test hash(XLSX.CellRange("A1:C4")) == hash(XLSX.CellRange("A1:C4"))

    # relative cell position
    rng = XLSX.range"B2:D4"
    @test XLSX.relative_cell_position(XLSX.ref"C3", rng) == (2, 2)
    @test XLSX.relative_cell_position(XLSX.ref"B2", rng) == (1, 1)
    @test XLSX.relative_cell_position(XLSX.ref"C4", rng) == (3, 2)
    @test XLSX.relative_cell_position(XLSX.ref"D4", rng) == (3, 3)
    @test XLSX.relative_cell_position(XLSX.EmptyCell(XLSX.ref"D4"), rng) == (3, 3)

    # SheetCellRef, SheetCellRange, SheetColumnRange
    ref = XLSX.SheetCellRef("Sheet1!A2")
    @test string(ref) == "Sheet1!A2"
    @test ref.sheet == "Sheet1"
    @test ref.cellref == XLSX.CellRef("A2")
    @test XLSX.SheetCellRef("Sheet1!A2") == XLSX.SheetCellRef("Sheet1!A2")
    @test hash(XLSX.SheetCellRef("Sheet1!A2")) == hash(XLSX.SheetCellRef("Sheet1!A2"))
    show(IOBuffer(), ref)

    ref = XLSX.SheetCellRange("Sheet1!A1:B4")
    @test ref.sheet == "Sheet1"
    @test ref.rng == XLSX.CellRange("A1:B4")
    @test_throws AssertionError XLSX.SheetCellRange("Sheet1!B4:A1")
    @test XLSX.SheetCellRange("Sheet1!A1:B4") == XLSX.SheetCellRange("Sheet1!A1:B4")
    @test hash(XLSX.SheetCellRange("Sheet1!A1:B4")) == hash(XLSX.SheetCellRange("Sheet1!A1:B4"))
    show(IOBuffer(), ref)

    ref = XLSX.SheetColumnRange("Sheet1!A:B")
    @test string(ref) == "Sheet1!A:B"
    @test ref.sheet == "Sheet1"
    @test ref.colrng == XLSX.ColumnRange("A:B")
    @test XLSX.SheetColumnRange("Sheet1!A:B") == XLSX.SheetColumnRange("Sheet1!A:B")
    @test hash(XLSX.SheetColumnRange("Sheet1!A:B")) == hash(XLSX.SheetColumnRange("Sheet1!A:B"))
    show(IOBuffer(), ref)

    @test XLSX.is_valid_fixed_sheet_cellname("named_ranges!\$A\$2")
    @test XLSX.is_valid_fixed_sheet_cellrange("named_ranges!\$B\$4:\$C\$5")
    @test !XLSX.is_valid_fixed_sheet_cellname("named_ranges!A2")
    @test !XLSX.is_valid_fixed_sheet_cellrange("named_ranges!B4:C5")
    @test XLSX.SheetCellRef("named_ranges!\$A\$2") == XLSX.SheetCellRef("named_ranges!A2")
    @test XLSX.SheetCellRange("named_ranges!\$B\$4:\$C\$5") == XLSX.SheetCellRange("named_ranges!B4:C5")
end

@testset "getindex" begin
    f = XLSX.readxlsx(joinpath(data_directory, "Book1.xlsx"))
    show(IOBuffer(), f)
    sheet1 = f["Sheet1"]
    show(IOBuffer(), sheet1)
    @test sheet1["B2"] == "B2"
    @test isapprox(sheet1["C3"], 21.2)
    @test sheet1["B5"] == Date(2018, 3, 21)
    @test sheet1["B8"] == "palavra1"

    @test XLSX.getcell(sheet1, "B2") == XLSX.Cell(XLSX.CellRef("B2"), "s", "", "0", "")
    XLSX.getcellrange(sheet1, "B2:C3")
    XLSX.getcellrange(f, "Sheet1!B2:C3")
    @test_throws ErrorException XLSX.getcellrange(f, "B2:C3")

    # a cell can be put in a dict
    c = XLSX.getcell(sheet1, "B2")
    show(IOBuffer(), c)
    dct = Dict("a" => c)
    @test dct["a"] == XLSX.Cell(XLSX.CellRef("B2"), "s", "", "0", "")

    # equality and hash
    @test XLSX.Cell(XLSX.CellRef("B2"), "s", "", "0", "") == XLSX.Cell(XLSX.CellRef("B2"), "s", "", "0", "")
    @test hash(dct["a"]) == hash(XLSX.Cell(XLSX.CellRef("B2"), "s", "", "0", ""))

    sheet2 = f[2]
    sheet2_data = [ 1 2 3 ; 4 5 6 ; 7 8 9 ]
    @test sheet2_data == sheet2["A1:C3"]
    @test sheet2_data == sheet2[:]
    @test sheet2[:] == XLSX.getdata(sheet2)
end

@testset "Time and DateTime" begin
    @test XLSX.excel_value_to_time(0.82291666666666663) == Dates.Time(Dates.Hour(19), Dates.Minute(45))
    @test XLSX.time_to_excel_value( XLSX.excel_value_to_time(0.2) ) == 0.2
    @test XLSX.excel_value_to_datetime(43206.805447106482, false) == Date(2018, 4, 16) + Dates.Time(Dates.Hour(19), Dates.Minute(19), Dates.Second(51))
    @test XLSX.excel_value_to_datetime(XLSX.datetime_to_excel_value(Date(2018, 4, 16) + Dates.Time(Dates.Hour(19), Dates.Minute(19), Dates.Second(51)),false ), false ) == Date(2018, 4, 16) + Dates.Time(Dates.Hour(19), Dates.Minute(19), Dates.Second(51))

    dt = Date(2018,4,1)
    @test XLSX.excel_value_to_date(XLSX.date_to_excel_value( dt, false), false) == dt
    @test XLSX.excel_value_to_date(XLSX.date_to_excel_value( dt, true), true) == dt
end

@testset "number formats" begin
    XLSX.openxlsx(joinpath(data_directory, "general.xlsx")) do f
        show(IOBuffer(), f)
        sheet = f["general"]
        @test sheet["A1"] == "text"
        @test sheet["B1"] == "regular text"
        @test sheet["A2"] == "integer"
        @test sheet["B2"] == 102
        @test sheet["A3"] == "float"
        @test isapprox(sheet["B3"], 102.2)
        @test sheet["A4"] == "date"
        @test sheet["B4"] == Date(1983, 4, 16)
        @test sheet["A5"] == "hour"
        @test sheet["B5"] == Dates.Time(Dates.Hour(19), Dates.Minute(45))
        @test sheet["A6"] == "datetime"
        @test sheet["B6"] == Date(2018, 4, 16) + Dates.Time(Dates.Hour(19), Dates.Minute(19), Dates.Second(51))
        @test f["general!B7"] == -220.0
        @test f["general!B8"] == -2000
        @test f["general!B9"] == 100000000000000
        @test f["general!B10"] == -100000000000000
    end
end

@testset "Defined Names" begin
    @test XLSX.is_defined_name_value_a_reference(XLSX.SheetCellRef("Sheet1!A1"))
    @test XLSX.is_defined_name_value_a_reference(XLSX.SheetCellRange("Sheet1!A1:B2"))
    @test !XLSX.is_defined_name_value_a_reference(1)
    @test !XLSX.is_defined_name_value_a_reference(1.2)
    @test !XLSX.is_defined_name_value_a_reference("Hey")
    @test !XLSX.is_defined_name_value_a_reference(missing)

    XLSX.openxlsx(joinpath(data_directory, "general.xlsx")) do f
        @test f["SINGLE_CELL"] == "single cell A2"
        @test f["RANGE_B4C5"] == Any["range B4:C5" "range B4:C5"; "range B4:C5" "range B4:C5"]
        @test f["CONST_DATE"] == 43383
        @test isapprox(f["CONST_FLOAT"], 10.2)
        @test f["CONST_INT"] == 100
        @test f["LOCAL_INT"] == 2000
        @test f["named_ranges_2"]["LOCAL_INT"] == 2000
        @test f["named_ranges"]["LOCAL_INT"] == 1000
        @test f["named_ranges"]["LOCAL_NAME"] == "Hey You"
        @test f["named_ranges_2"]["LOCAL_NAME"] == "out there in the cold"
        @test f["named_ranges"]["SINGLE_CELL"] == "single cell A2"

        @test_throws ErrorException f["header_error"]["LOCAL_REF"]
        @test f["named_ranges"]["LOCAL_REF"][1] == 10
        @test f["named_ranges"]["LOCAL_REF"][2] == 20
        @test f["named_ranges_2"]["LOCAL_REF"][1] == "local"
        @test f["named_ranges_2"]["LOCAL_REF"][2] == "reference"
    end

    @test XLSX.readdata(joinpath(data_directory, "general.xlsx"), "SINGLE_CELL") == "single cell A2"
    @test XLSX.readdata(joinpath(data_directory, "general.xlsx"), "RANGE_B4C5") == Any["range B4:C5" "range B4:C5"; "range B4:C5" "range B4:C5"]
end

@testset "Book1.xlsx" begin
    f = XLSX.readxlsx(joinpath(data_directory, "Book1.xlsx"))
    sheet = f["Sheet1"]
    @test ismissing(sheet["A1"])
    @test sheet["B2"] == "B2"
    @test sheet["C2"] == "C2"
    @test isapprox(sheet["B3"], 10.5)
    @test isapprox(sheet["C3"], 21.2)
    @test sheet["B4"] == Date(2018, 3, 21)
    @test sheet["C4"] == Date(2018, 3, 22)
    @test sheet["B5"] == Date(2018, 3, 21)
    @test sheet["C5"] == Date(2018, 3, 22)
    @test sheet["B6"] == true
    @test sheet["C6"] == false
    @test sheet["B7"] == 1
    @test sheet["C7"] == 2
    @test sheet["B8"] == "palavra1"
    @test sheet["C8"] == "palavra2"

    @test XLSX.getdata(f, XLSX.SheetCellRef("Sheet1!B2")) == "B2"
    @test XLSX.getdata(f, XLSX.SheetCellRange("Sheet1!B2:B3"))[1] == "B2"
    @test XLSX.getdata(f, XLSX.SheetCellRange("Sheet1!B2:B3"))[2] == 10.5
    @test f["Sheet1!B2"] == "B2"
    @test f["Sheet1!B2:B3"][1] == "B2"
    @test f["Sheet1!B2:B3"][2] == 10.5
    @test string(XLSX.SheetCellRange("Sheet1!B2:B3")) == "Sheet1!B2:B3"
end

@testset "book_1904_ptbr.xlsx" begin
    f = XLSX.readxlsx(joinpath(data_directory, "book_1904_ptbr.xlsx"))

    @test f["Plan1"][:] == Any[ "Coluna A" "Coluna B" "Coluna C" "Coluna D";
                                10 10.5 Date(2018, 3, 22) "linha 2";
                                20 20.5 Date(2017, 12, 31) "linha 3";
                                30 30.5 Date(2018, 1, 1) "linha 4" ]

    @test f["Plan2"]["A1"] == "Merge de A1:D1"
    @test ismissing(f["Plan2"]["B1"])
    @test f["Plan2"]["C2"] == "C2"
    @test f["Plan2"]["D3"] == "D3"
    @test f["NEGOCIAÇÕES Descrição"]["A1"] == "Negociações"
    @test f["NEGOCIAÇÕES Descrição"]["B1"] == 10
    @test f["NEGOCIAÇÕES Descrição!A1"] == "Negociações"
    @test f["NEGOCIAÇÕES Descrição!B1"] == 10
end

@testset "numbers.xlsx" begin
    f = XLSX.readxlsx(joinpath(data_directory, "numbers.xlsx"))
    floats = f["float"][:]
    for n in floats
        if !ismissing(n)
            @test isa(n, Float64)
        end
    end

    ints = f["int"][:]
    for n in ints
        if !ismissing(n)
            @test isa(n, Int)
        end
    end

    error_sheet = f["error"]
    @test error_sheet["A1"] == "errors"
    @test !XLSX.iserror(XLSX.getcell(error_sheet, "A1"))
    @test XLSX.iserror(XLSX.getcell(error_sheet, "A2"))
    @test XLSX.iserror(XLSX.getcell(f, "error!A2"))
    @test ismissing(error_sheet["A2"])
    @test ismissing(error_sheet["A3"])
    @test ismissing(error_sheet["A4"])
    emptycell = XLSX.getcell(error_sheet, "B1")
    @test !XLSX.iserror(emptycell)
    @test ismissing(XLSX.getdata(error_sheet, emptycell))
    @test XLSX.row_number(emptycell) == 1
    @test XLSX.column_number(emptycell) == 2
end

@testset "Column Range" begin
    cr = XLSX.ColumnRange("B:D")
    @test string(cr) == "B:D"
    @test cr.start == 2
    @test cr.stop == 4
    @test length(cr) == 3
    @test_throws AssertionError XLSX.ColumnRange("B1:D3")
    @test_throws AssertionError XLSX.ColumnRange("D:A")
    @test collect(cr) == [ "B", "C", "D" ]
    @test XLSX.ColumnRange("B:D") == XLSX.ColumnRange("B:D")
    @test hash(XLSX.ColumnRange("B:D")) == hash(XLSX.ColumnRange("B:D"))
end

@testset "CellRange iterator" begin
    rng = XLSX.CellRange("A2:C4")
    @test collect(rng) == [ XLSX.CellRef("A2"), XLSX.CellRef("B2"), XLSX.CellRef("C2"), XLSX.CellRef("A3"), XLSX.CellRef("B3"), XLSX.CellRef("C3"), XLSX.CellRef("A4"), XLSX.CellRef("B4"), XLSX.CellRef("C4") ]
end

# Checks wether `data` equals `test_data`
function check_test_data(data::Vector{Any}, test_data::Vector{Any})

    @test length(data) == length(test_data)

    function size_of_data(d::Vector{Any})
        isempty(d) && return (0, 0)
        return length(d[1]), length(d)
    end

    rows, cols = size_of_data(test_data)

    for col in 1:cols
        @test length(data[col]) == length(test_data[col])
    end

    for row in 1:rows, col in 1:cols
        test_value = test_data[col][row]
        value = data[col][row]
        if ismissing(test_value) || ( isa(test_value, AbstractString) && isempty(test_value) )
            @test ismissing(value) || ( isa(value, AbstractString) && isempty(value) )
        else
            if isa(test_value, Float64)
                @test isapprox(value, test_value)
            else
                @test value == test_value
            end
        end
    end

    nothing
end

@testset "Table" begin

    @testset "Index" begin
        index = XLSX.Index("A:B", [:First, :Second])
        @test index.column_labels == [:First, :Second]
        @test index.lookup[:First] == 1
        @test index.lookup[:Second] == 2
    end

    @testset "Bounds" begin
        f = XLSX.readxlsx(joinpath(data_directory, "book_sparse.xlsx"))
        s = f["Sheet1"]

        report = Vector{String}()
        for r in XLSX.eachrow(s)
            if !isempty(r)
                push!(report, string(XLSX.row_number(r), " - ", XLSX.column_bounds(r)))

                if XLSX.row_number(r) == 2
                    @test XLSX.last_column_index(r, 2) == 2
                elseif XLSX.row_number(r) == 3
                    @test XLSX.last_column_index(r, 3) == 4
                elseif XLSX.row_number(r) == 6
                    @test XLSX.last_column_index(r, 1) == 4
                    @test XLSX.last_column_index(r, 2) == 4
                    @test XLSX.last_column_index(r, 3) == 4
                    @test XLSX.last_column_index(r, 4) == 4
                    @test_throws AssertionError XLSX.last_column_index(r, 5)
                elseif XLSX.row_number(r) == 9
                    @test XLSX.last_column_index(r, 2) == 3
                    @test XLSX.last_column_index(r, 3) == 3
                    @test XLSX.last_column_index(r, 5) == 5
                end
            end
        end

        @test report == [ "2 - (2, 2)", "3 - (3, 4)", "6 - (1, 4)", "9 - (2, 5)"]
    end

    XLSX.openxlsx(joinpath(data_directory, "general.xlsx")) do f
        f["general"][:];
    end

    f = XLSX.readxlsx(joinpath(data_directory, "general.xlsx"))
    s = f["table"]
    s[:];
    data, col_names = XLSX.gettable(s)
    @test col_names == [ Symbol("Column B"), Symbol("Column C"), Symbol("Column D"), Symbol("Column E"), Symbol("Column F"), Symbol("Column G")]

    test_data = Vector{Any}(undef, 6)
    test_data[1] = collect(1:8)
    test_data[2] = [ "Str1", missing, "Str1", "Str1", "Str2", "Str2", "Str2", "Str2" ]
    test_data[3] = [ Date(2018, 4, 21) + Dates.Day(i) for i in 0:7 ]
    test_data[4] = [ missing, missing, missing, missing, missing, "a", "b", missing ]
    test_data[5] = [ 0.2001132319, 0.2793987377, 0.0950591677, 0.0744023067, 0.8242278091, 0.6205883578, 0.9174151018, 0.6749604883 ]
    test_data[6] = [ missing for i in 1:8 ]

    check_test_data(data, test_data)

    @test XLSX.infer_eltype(data[1]) == Int
    @test XLSX.infer_eltype(data[2]) == Union{Missing, String}
    @test XLSX.infer_eltype(data[3]) == Date
    @test XLSX.infer_eltype(data[4]) == Union{Missing, String}
    @test XLSX.infer_eltype(data[5]) == Float64
    @test XLSX.infer_eltype(data[6]) == Any
    @test XLSX.infer_eltype([1, "1", 10.2]) == Any
    @test XLSX.infer_eltype(Vector{Int}()) == Int

    data_inferred, col_names = XLSX.gettable(s, infer_eltypes=true)
    @test eltype(data_inferred[1]) == Int
    @test eltype(data_inferred[2]) == Union{Missing, String}
    @test eltype(data_inferred[3]) == Date
    @test eltype(data_inferred[4]) == Union{Missing, String}
    @test eltype(data_inferred[5]) == Float64
    @test eltype(data_inferred[6]) == Any

    function stop_function(r::XLSX.TableRow)
        v = r[Symbol("Column C")]
        return !ismissing(v) && v == "Str2"
    end

    function never_reaches_stop(r::XLSX.TableRow)
        v = r[Symbol("Column C")]
        return !ismissing(v) && v == "never was found"
    end

    data, col_names = XLSX.gettable(s, stop_in_row_function=never_reaches_stop)
    check_test_data(data, test_data)

    data, col_names = XLSX.gettable(s, stop_in_row_function=stop_function)
    @test col_names == [ Symbol("Column B"), Symbol("Column C"), Symbol("Column D"), Symbol("Column E"), Symbol("Column F"), Symbol("Column G")]

    test_data = Vector{Any}(undef, 6)
    test_data[1] = collect(1:4)
    test_data[2] = [ "Str1", missing, "Str1", "Str1" ]
    test_data[3] = [ Date(2018, 4, 21) + Dates.Day(i) for i in 0:3 ]
    test_data[4] = [ missing, missing, missing, missing ]
    test_data[5] = [ 0.2001132319, 0.2793987377, 0.0950591677, 0.0744023067 ]
    test_data[6] = [ missing for i in 1:4 ]

    check_test_data(data, test_data)

    data, col_names = XLSX.gettable(s, stop_in_empty_row=false)
    @test col_names == [ Symbol("Column B"), Symbol("Column C"), Symbol("Column D"), Symbol("Column E"), Symbol("Column F"), Symbol("Column G")]

    test_data = Vector{Any}(undef, 6)
    test_data[1] = [1, 2, 3, 4, 5, 6, 7, 8, "trash" ]
    test_data[2] = [ "Str1", missing, "Str1", "Str1", "Str2", "Str2", "Str2", "Str2", missing ]
    test_data[3] = Any[ Date(2018, 4, 21) + Dates.Day(i) for i in 0:7 ]
    push!(test_data[3], "trash")
    test_data[4] = [ missing, missing, missing, missing, missing, "a", "b", missing, missing ]
    test_data[5] = [ 0.2001132319, 0.2793987377, 0.0950591677, 0.0744023067, 0.8242278091, 0.6205883578, 0.9174151018, 0.6749604883, "trash" ]
    test_data[6] = Any[ missing for i in 1:8 ]
    push!(test_data[6], "trash")

    check_test_data(data, test_data)

    # queries based on ColumnRange
    x = XLSX.getcellrange(s, XLSX.ColumnRange("B:D"))
    @test size(x) == (11, 3)
    y = XLSX.getcellrange(s, "B:D")
    @test size(y) == (11, 3)
    @test x == y
    @test_throws AssertionError XLSX.getcellrange(s, "D:B")
    @test_throws ErrorException XLSX.getcellrange(s, "A:C1")

    d = XLSX.getdata(s, "B:D")
    @test size(d) == (11, 3)
    @test_throws ErrorException XLSX.getdata(s, "A:C1")
    @test d[1, 1] == "Column B"
    @test d[1, 2] == "Column C"
    @test d[1, 3] == "Column D"
    @test d[9, 1] == 8
    @test d[9, 2] == "Str2"
    @test d[9, 3] == Date(2018, 4, 28)
    @test d[10, 1] == "trash"
    @test ismissing(d[10, 2])
    @test d[10, 3] == "trash"
    @test ismissing(d[11, 1])
    @test ismissing(d[11, 2])
    @test ismissing(d[11, 3])

    d2 = f["table!B:D"]
    @test size(d) == size(d2)
    @test all(d .=== d2)

    @test_throws ErrorException f["table!B1:D"]
    @test_throws AssertionError f["table!D:B"]

    s = f["table2"]
    test_data = Vector{Any}(undef, 3)
    test_data[1] = [ "A1", "A2", "A3", missing ]
    test_data[2] = [ "B1", "B2", missing, "B4"]
    test_data[3] = [ missing, missing, missing, missing ]

    data, col_names = XLSX.gettable(s)

    @test col_names == [:HA, :HB, :HC]
    check_test_data(data, test_data)

    for (ri, rowdata) in enumerate(XLSX.eachtablerow(s))
        if ismissing(test_data[1][ri])
            @test ismissing(rowdata[:HA])
        else
            @test rowdata[:HA] == test_data[1][ri]
        end

        @test XLSX.table_columns_count(rowdata) == 3
        @test XLSX.row_number(rowdata) == ri
        @test XLSX.get_column_labels(rowdata) == col_names
        @test XLSX.get_column_label(rowdata, 1) == :HA
        @test XLSX.get_column_label(rowdata, 2) == :HB
        @test XLSX.get_column_label(rowdata, 3) == :HC

        @test_throws ErrorException XLSX.getdata(rowdata, :INVALID_COLUMN)
    end

    override_col_names = [:ColumnA, :ColumnB, :ColumnC]
    data, col_names = XLSX.gettable(s, column_labels=override_col_names)

    @test col_names == override_col_names
    check_test_data(data, test_data)

    data, col_names = XLSX.gettable(s, "A:B", first_row=1)
    test_data_AB_cols = Vector{Any}(undef, 2)
    test_data_AB_cols[1] = test_data[1]
    test_data_AB_cols[2] = test_data[2]
    @test col_names == [:HA, :HB]
    check_test_data(data, test_data_AB_cols)

    data, col_names = XLSX.gettable(s, "A:B")
    test_data_AB_cols = Vector{Any}(undef, 2)
    test_data_AB_cols[1] = test_data[1]
    test_data_AB_cols[2] = test_data[2]
    @test col_names == [:HA, :HB]
    check_test_data(data, test_data_AB_cols)

    data, col_names = XLSX.gettable(s, "B:B", first_row=2)
    @test col_names == [:B1]
    @test length(data) == 1
    @test length(data[1]) == 1
    @test data[1][1] == "B2"

    data, col_names = XLSX.gettable(s, "B:C")
    @test col_names == [ :HB, :HC ]
    test_data_BC_cols = Vector{Any}(undef, 2)
    test_data_BC_cols[1] = ["B1", "B2"]
    test_data_BC_cols[2] = [ missing, missing]
    check_test_data(data, test_data_BC_cols)

    data, col_names = XLSX.gettable(s, "B:C", first_row=2, header=false)
    @test col_names == [ :B, :C ]
    check_test_data(data, test_data_BC_cols)

    s = f["table3"]
    test_data = Vector{Any}(undef, 3)
    test_data[1] = [ missing, missing, "B5" ]
    test_data[2] = [ "C3", missing, missing ]
    test_data[3] = [ missing, "D4", missing ]
    data, col_names = XLSX.gettable(s)
    @test col_names == [:H1, :H2, :H3]
    check_test_data(data, test_data)
    @test_throws ErrorException XLSX.find_row(XLSX.eachrow(s), 20)

    for r in XLSX.eachrow(s)
        @test isempty(XLSX.getcell(r, "A"))
        @test XLSX.getdata(s, XLSX.getcell(r, "B")) == "H1"
        @test r[2] == "H1"
        @test r["B"] == "H1"
        break
    end

    @test XLSX._find_first_row_with_data(s, 5) == 5
    @test_throws ErrorException XLSX._find_first_row_with_data(s, 7)

    s = f["table4"]
    data, col_names = XLSX.gettable(s)
    @test col_names == [:H1, :H2, :H3]
    check_test_data(data, test_data)

    @testset "empty/invalid" begin
        XLSX.openxlsx(joinpath(data_directory, "general.xlsx")) do xf
            empty_sheet = XLSX.getsheet(xf, "empty")
            @test_throws ErrorException XLSX.gettable(empty_sheet)
            itr = XLSX.eachrow(empty_sheet)
            @test_throws ErrorException XLSX.find_row(itr, 1)
            @test_throws ErrorException XLSX.getsheet(xf, "invalid_sheet")
        end
    end

    @testset "sheets 6/7/lookup/header_error" begin
        f = XLSX.readxlsx(joinpath(data_directory,"general.xlsx"))
        tb5 = f["table5"]
        test_data = Vector{Any}(undef, 1)
        test_data[1] = [1, 2, 3, 4, 5]
        data, col_names = XLSX.gettable(tb5)
        @test col_names == [ :HEADER ]
        check_test_data(data, test_data)
        tb6 = f["table6"]
        data, col_names = XLSX.gettable(tb6, first_row=3)
        @test col_names == [ :HEADER ]
        check_test_data(data, test_data)
        tb7 = f["table7"]
        data, col_names = XLSX.gettable(tb7, first_row=3)
        @test col_names == [ :HEADER ]
        check_test_data(data, test_data)

        sheet_lookup = f["lookup"]
        test_data = Vector{Any}(undef, 3)
        test_data[1] = [ 10, 20, 30]
        test_data[2] = [ "name1", "name2", "name3" ]
        test_data[3] = [ 100, 200, 300 ]
        data, col_names = XLSX.gettable(sheet_lookup)
        @test col_names == [:ID, :NAME, :VALUE]
        check_test_data(data, test_data)

        header_error_sheet = f["header_error"]
        @test_throws AssertionError XLSX.gettable(header_error_sheet)
    end

    @testset "Consecutive passes" begin
        # Consecutive passes should yield the same results
        XLSX.openxlsx(joinpath(data_directory, "general.xlsx")) do f
            sl = f["lookup"]
            data, col_names = XLSX.gettable(sl)
            @test col_names == [:ID, :NAME, :VALUE]
            check_test_data(data, test_data)

            data, col_names = XLSX.gettable(sl)
            @test col_names == [:ID, :NAME, :VALUE]
            check_test_data(data, test_data)
        end
    end
end

@testset "Helper functions" begin

    test_data = Vector{Any}(undef, 3)
    test_data[1] = [ missing, missing, "B5" ]
    test_data[2] = [ "C3", missing, missing ]
    test_data[3] = [ missing, "D4", missing ]

    data, col_names = XLSX.readtable(joinpath(data_directory, "general.xlsx"), "table4")
    @test col_names == [:H1, :H2, :H3]
    check_test_data(data, test_data)

    @test XLSX.readdata(joinpath(data_directory, "general.xlsx"), "table4", "E12") == "H1"
    test_data = Array{Any, 2}(undef, 2, 1)
    test_data[1, 1] = "H2"
    test_data[2, 1] = "C3"

    @test XLSX.readdata(joinpath(data_directory, "general.xlsx"), "table4", "F12:F13") == test_data

    @testset "readtable select single column" begin
        data, col_names = XLSX.readtable(joinpath(data_directory, "general.xlsx"), "table4", "F")
        @test col_names == [ :H2 ]
        @test data == Any[Any["C3"]]
    end

    @testset "readtable select column range" begin
        data, col_names = XLSX.readtable(joinpath(data_directory, "general.xlsx"), "table4", "F:G")
        @test col_names == [ :H2, :H3 ]
        test_data = Any[Any["C3", missing], Any[missing, "D4"]]
        check_test_data(data, test_data)
    end
end

@testset "Write" begin
    f = XLSX.open_xlsx_template(joinpath(data_directory, "general.xlsx"))
    filename_copy = "general_copy.xlsx"
    XLSX.writexlsx(filename_copy, f)
    @test isfile(filename_copy)

    f_copy = XLSX.readxlsx(filename_copy)

    s = f_copy["table"]
    s[:];
    data, col_names = XLSX.gettable(s)
    @test col_names == [ Symbol("Column B"), Symbol("Column C"), Symbol("Column D"), Symbol("Column E"), Symbol("Column F"), Symbol("Column G")]

    test_data = Vector{Any}(undef, 6)
    test_data[1] = collect(1:8)
    test_data[2] = [ "Str1", missing, "Str1", "Str1", "Str2", "Str2", "Str2", "Str2" ]
    test_data[3] = [ Date(2018, 4, 21) + Dates.Day(i) for i in 0:7 ]
    test_data[4] = [ missing, missing, missing, missing, missing, "a", "b", missing ]
    test_data[5] = [ 0.2001132319, 0.2793987377, 0.0950591677, 0.0744023067, 0.8242278091, 0.6205883578, 0.9174151018, 0.6749604883 ]
    test_data[6] = [ missing for i in 1:8 ]
    check_test_data(data, test_data)
    rm(filename_copy)
end

@testset "Edit Template" begin
    new_filename = "new_file_from_empty_template.xlsx"
    f = XLSX.open_empty_template()
    f["Sheet1"]["A1"] = "Hello"
    f["Sheet1"]["A2"] = 10
    XLSX.writexlsx(new_filename, f, overwrite=true)
    @test !XLSX.isopen(f)

    f = XLSX.readxlsx(new_filename)
    @test f["Sheet1"]["A1"] == "Hello"
    @test f["Sheet1"]["A2"] == 10

    rm(new_filename)
end

@testset "addsheet!" begin
    new_filename = "template_with_new_sheet.xlsx"
    f = XLSX.open_empty_template()
    s = XLSX.addsheet!(f, "new_sheet")
    s["A1"] = 10

    @testset "check invalid sheet names" begin
        invalid_names = [
                         "new_sheet",
                         "aaaaaaaaaabbbbbbbbbbccccccccccd1",
                         "abc:def",
                         "abcdef/",
                         "\\aaaa",
                         "hey?you",
                         "[mysheet]",
                         "asteri*"
                        ]

        for invalid_name in invalid_names
            @test_throws AssertionError XLSX.addsheet!(f, invalid_name)
        end
    end

    big_sheetname = "aaaaaaaaaabbbbbbbbbbccccccccccd"
    s2 = XLSX.addsheet!(f, big_sheetname)

    XLSX.writexlsx(new_filename, f, overwrite=true)
    @test !XLSX.isopen(f)

    f = XLSX.readxlsx(new_filename)
    @test XLSX.sheetnames(f) == [ "Sheet1", "new_sheet" , big_sheetname ]
    rm(new_filename)
end

@testset "Edit" begin
    f = XLSX.open_xlsx_template(joinpath(data_directory, "general.xlsx"))
    s = f["general"]
    @test_throws ErrorException s["A1"] = :sym
    XLSX.rename!(s, "renamed_sheet")
    s["A1"] = "Hey You!"
    s["B1"] = "Out there in the cold..."
    s["A2"] = "Getting lonely getting old..."
    s["B2"] = "Can you feel me?"
    s["A3"] = 1000
    s["B3"] = 99.99

    # create a new sheet
    s = XLSX.addsheet!(f, "my_new_sheet_1")
    s = XLSX.addsheet!(f, "my_new_sheet_2")
    s["B1"] = "This is a new sheet"
    s["B2"] = "This is a new sheet"
    s = XLSX.addsheet!(f)
    s["B1"] = "unnamed sheet"

    XLSX.writexlsx("general_copy_2.xlsx", f, overwrite=true)
    @test isfile("general_copy_2.xlsx")

    XLSX.openxlsx("general_copy_2.xlsx") do f
        s = f["renamed_sheet"]
        @test s["A1"] == "Hey You!"
        @test s["B1"] == "Out there in the cold..."
        @test s["A2"] == "Getting lonely getting old..."
        @test s["B2"] == "Can you feel me?"
        @test s["A3"] == 1000
        @test s["B3"] == 99.99
        f["my_new_sheet_1"];
        @test f["my_new_sheet_2"]["B1"] == "This is a new sheet"
        @test f["my_new_sheet_2"]["B2"] == "This is a new sheet"
        @test f["Sheet1"]["B1"] == "unnamed sheet"
    end

    rm("general_copy_2.xlsx")
end

@testset "writetable" begin

    @testset "single" begin
        col_names = ["Integers", "Strings", "Floats", "Booleans", "Dates", "Times", "DateTimes"]
        data = Vector{Any}(undef, 7)
        data[1] = [1, 2, missing, 4]
        data[2] = ["Hey", "You", "Out", "There"]
        data[3] = [101.5, 102.5, missing, 104.5]
        data[4] = [ true, false, missing, true]
        data[5] = [ Date(2018, 2, 1), Date(2018, 3, 1), Date(2018,5,20), Date(2018, 6, 2)]
        data[6] = [ Dates.Time(19, 10), Dates.Time(19, 20), Dates.Time(19, 30), Dates.Time(0, 0) ]
        data[7] = [ Dates.DateTime(2018, 5, 20, 19, 10), Dates.DateTime(2018, 5, 20, 19, 20), Dates.DateTime(2018, 5, 20, 19, 30), Dates.DateTime(2018, 5, 20, 19, 40)]

        XLSX.writetable("output_table.xlsx", data, col_names, overwrite=true, sheetname="report", anchor_cell="B2")
        @test isfile("output_table.xlsx")

        read_data, read_column_names = XLSX.readtable("output_table.xlsx", "report")
        @test length(read_column_names) == length(col_names)
        for c in 1:length(col_names)
            @test Symbol(col_names[c]) == read_column_names[c]
        end
        check_test_data(read_data, data)
    end

    @testset "multiple" begin
        report_1_column_names = ["HEADER_A", "HEADER_B"]
        report_1_data = Vector{Any}(undef, 2)
        report_1_data[1] = [1, 2, 3]
        report_1_data[2] = ["A", "B", ""]

        report_2_column_names = ["COLUMN_A", "COLUMN_B"]
        report_2_data = Vector{Any}(undef, 2)
        report_2_data[1] = [Date(2017,2,1), Date(2018,2,1)]
        report_2_data[2] = [10.2, 10.3]

        XLSX.writetable("output_tables.xlsx", overwrite=true, REPORT_A=(report_1_data, report_1_column_names), REPORT_B=(report_2_data, report_2_column_names))
        @test isfile("output_tables.xlsx")

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_A")
        @test labels[1] == :HEADER_A
        @test labels[2] == :HEADER_B
        check_test_data(data, report_1_data)

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_B")
        @test labels[1] == :COLUMN_A
        @test labels[2] == :COLUMN_B
        check_test_data(data, report_2_data)

        XLSX.writetable("output_tables.xlsx", [ ("REPORT_A", report_1_data, report_1_column_names), ("REPORT_B", report_2_data, report_2_column_names) ], overwrite=true)

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_A")
        @test labels[1] == :HEADER_A
        @test labels[2] == :HEADER_B
        check_test_data(data, report_1_data)

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_B")
        @test labels[1] == :COLUMN_A
        @test labels[2] == :COLUMN_B
        check_test_data(data, report_2_data)

        report_1_column_names = [:HEADER_A, :HEADER_B]
        report_2_column_names = [:COLUMN_A, :COLUMN_B]

        XLSX.writetable("output_tables.xlsx", [ ("REPORT_A", report_1_data, report_1_column_names), ("REPORT_B", report_2_data, report_2_column_names) ], overwrite=true)

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_A")
        @test labels[1] == :HEADER_A
        @test labels[2] == :HEADER_B
        check_test_data(data, report_1_data)

        data, labels = XLSX.readtable("output_tables.xlsx", "REPORT_B")
        @test labels[1] == :COLUMN_A
        @test labels[2] == :COLUMN_B
        check_test_data(data, report_2_data)
    end

    # delete files created by this testset
    delete_files = ["output_table.xlsx", "output_tables.xlsx"]
    for f in delete_files
        isfile(f) && rm(f)
    end
end

@testset "Styles" begin

    using XLSX: CellValue, id, getcell, setdata!, CellRef
    xfile = XLSX.open_empty_template()
    wb = XLSX.get_workbook(xfile)
    sheet = xfile["Sheet1"]

    datefmt = XLSX.styles_add_numFmt(wb, "yyyymmdd")
    numfmt = XLSX.styles_add_numFmt(wb, "\$* #,##0.00;\$* (#,##0.00);\$* \"-\"??;[Magenta]@")

    #Check format id numbers dont intersect with predefined formats or each other
    @test datefmt == 164
    @test numfmt == 165

    font = XLSX.styles_add_font(wb, XLSX.FontAttribute["b", "sz"=>("val"=>"24")])
    xroot = XLSX.styles_xmlroot(wb)
    fontnodes = findall("/xpath:styleSheet/xpath:fonts/xpath:font", xroot, XLSX.SPREADSHEET_NAMESPACE_XPATH_ARG)
    fontnode = fontnodes[font+1] # XML is zero indexed so we need to add 1 to get the right node

    # Check the font was written correctly
    @test string(fontnode) == "<font><b/><sz val=\"24\"/></font>"

    textstyle = XLSX.styles_add_cell_xf(wb, Dict("applyFont"=>"true", "fontId"=>"$font"))
    datestyle = XLSX.styles_add_cell_xf(wb, Dict("applyNumberFormat"=>"1", "numFmtId"=>"$datefmt"))
    numstyle = XLSX.styles_add_cell_xf(wb, Dict("applyFont"=>"1", "applyNumberFormat"=>"1", "fontId"=>"$font", "numFmtId"=>"$numfmt"))

    xf = XLSX.styles_get_cellXf_with_numFmtId(wb, 1000)
    @test xf == XLSX.EmptyCellDataFormat()
    @test isempty(xf)
    @test id(xf) == ""

    @test textstyle isa XLSX.CellDataFormat
    @test !isempty(textstyle)
    @test id(textstyle) == "1"

    @test XLSX.styles_get_cellXf_with_numFmtId(wb, datefmt) == datestyle
    @test XLSX.styles_numFmt_formatCode(wb, string(datefmt)) == "yyyymmdd"
    @test datestyle isa XLSX.CellDataFormat
    @test !isempty(datestyle)
    @test id(datestyle) == "2"

    @test XLSX.styles_get_cellXf_with_numFmtId(wb, numfmt) == numstyle
    @test XLSX.styles_numFmt_formatCode(wb, string(numfmt)) == "\$* #,##0.00;\$* (#,##0.00);\$* "-"??;[Magenta]@"
    @test numstyle isa XLSX.CellDataFormat
    @test !isempty(numstyle)
    @test id(numstyle) == "3"

    setdata!(sheet, CellRef("A1"), CellValue(Date(2011, 10, 13), datestyle))
    setdata!(sheet, CellRef("A2"), CellValue(1000, numstyle))
    setdata!(sheet, CellRef("A3"), CellValue(1000.10, numstyle))
    setdata!(sheet, CellRef("A4"), CellValue(-1000.10, numstyle))
    setdata!(sheet, CellRef("A5"), CellValue(0, numstyle))
    setdata!(sheet, CellRef("A6"), CellValue("hello", numstyle))
    setdata!(sheet, CellRef("B1"), CellValue("hello world", textstyle))

    @test sheet["A1"] == Date(2011, 10, 13)
    cell = getcell(sheet, "A1")
    @test cell.style == id(datestyle)
    formatid = XLSX.styles_cell_xf_numFmtId(wb, parse(Int, cell.style))
    @test formatid == datefmt

    cellstyle = getcell(sheet, "A2").style
    @test cellstyle == id(numstyle)
    formatid = XLSX.styles_cell_xf_numFmtId(wb, parse(Int, cellstyle))
    @test formatid == numfmt

    @test sheet["A2"] == 1000
    @test sheet["A3"] == 1000.10
    @test XLSX.getcell(sheet, "A3").style == cellstyle
    @test sheet["A4"] == -1000.10
    @test XLSX.getcell(sheet, "A4").style == cellstyle
    @test sheet["A5"] == 0
    @test XLSX.getcell(sheet, "A5").style == cellstyle
    @test sheet["A6"] == "hello"
    @test XLSX.getcell(sheet, "A6").style == cellstyle

    @test sheet["B1"] == "hello world"
    @test XLSX.getcell(sheet, "B1").style == id(textstyle)

    sheet["B2"] = CellValue("hello world", textstyle)
    @test sheet["B2"] == "hello world"
    @test XLSX.getcell(sheet, "B2").style == id(textstyle)

    # Check CellDataFormat only works with CellValues
    @test_throws MethodError XLSX.CellValue([1,2,3,4], textstyle)

    close(xfile)

    using XLSX: styles_is_datetime, styles_add_numFmt, styles_add_cell_xf
    # Capitalized and single character numfmts
    xfile = XLSX.open_empty_template()
    wb = XLSX.get_workbook(xfile)
    sheet = xfile["Sheet1"]

    fmt = styles_add_numFmt(wb, "yyyy m d")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "h:m:s")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "0.00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "[red]yyyy m d")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)
    fmt = styles_add_numFmt(wb, "[red] h:m:s")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)
    fmt = styles_add_numFmt(wb, "[red] 0.00; [magenta] 0.00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "YYYY M D")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)
    fmt = styles_add_numFmt(wb, "H:M:S")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "m")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "M")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "y")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "[s]")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "am/pm")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "a/p")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test styles_is_datetime(wb, style)

    fmt = styles_add_numFmt(wb, "\"Monday\"")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test !XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "0.00*m")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "0.00_m")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "0.00\\d")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !styles_is_datetime(wb, style)
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "[red][>1.5]000")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "0.#")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "\"hello.\" ###")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, ".??")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "#e+00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "0e00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "# ??/??")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "*.00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "\\.00")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !XLSX.styles_is_float(wb, style)

    fmt = styles_add_numFmt(wb, "00_.")
    style = styles_add_cell_xf(wb, Dict("numFmtId"=>"$fmt"))
    @test !XLSX.styles_is_float(wb, style)

    close(xfile)
end

@testset "filemodes" begin

    sheetname = "New Sheet"
    filename = "test_file.xlsx"
    if isfile(filename)
        rm(filename)
    end

    data = [
        1 "a" Date(2018, 1, 1);
        2 missing Date(2018, 1, 2);
        missing "c" Date(2018, 1, 3);
    ]

    # can't read or edit a file that does not exist
    @test_throws AssertionError XLSX.openxlsx(filename, mode="r") do xf
        error("This should fail.")
    end

    @test_throws AssertionError XLSX.openxlsx(filename, mode="rw") do xf
        error("This should fail.")
    end

    # test create new file
    XLSX.openxlsx(filename, mode="w") do xf
        sheet = xf[1]
        XLSX.rename!(sheet, sheetname)

        sheet["A1"] = data[1, :]
        sheet[2, :] = data[2, :]
        sheet[2, 1] = "test overwrite"
        sheet[3, 2:3] = data[3, 2:3]
    end

    @test isfile(filename)
    XLSX.openxlsx(filename) do xf
        sheet = xf[sheetname]
        read_data = sheet[:]

        @test isequal(read_data[1, :], data[1, :])
        @test isequal(read_data[2, :], vcat(["test overwrite"], data[2, 2:end]))
        @test isequal(read_data[3, :], data[3, :])
    end

    # test overwrite file
    @test isfile(filename)
    new_data = [1 2 3;]
    XLSX.openxlsx(filename, mode="w") do xf
        sheet = xf[1]
        sheet[1, :] = new_data[1,:]
    end

    XLSX.openxlsx(filename) do xf
        sheet = xf[1]
        read_data = sheet[:]

        @test isequal(read_data, new_data)
    end

    # test edit file
    XLSX.openxlsx(filename, mode="rw") do xf
        sheet=xf[1]
        sheet[1, 2] = "hello"
        sheet["B6"] = 5
    end

    XLSX.openxlsx(filename) do xf
        sheet = xf[1]
        read_data = sheet[:]

        @test read_data[1, 1] == new_data[1, 1]
        @test read_data[1, 2] == "hello"
        @test read_data[1, 3] == new_data[1, 3]
        @test read_data[6, 2] == 5
    end

    # test writing throws error if flag not set
    XLSX.openxlsx(filename) do xf
        sheet = xf[1]
        @test_throws AssertionError sheet[1, 1] = "failure"
    end

    @testset "write column" begin
        col_data = collect(1:50)

        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            sheet[:, 2] = col_data
            sheet[51:100, 3] = col_data

            @static if VERSION < v"1.0"
                setindex!(sheet, col_data, 2, 4, dim=1)
            else
                sheet[2, 4, dim=1] = col_data
            end
        end

        XLSX.openxlsx(filename) do xf
            sheet = xf[1]

            for (row, val) in enumerate(col_data)
                @test sheet[row, 2] == val
                @test sheet[50 + row, 3] == val
                @test sheet[row + 1, 4] == val
            end
        end
    end

    @testset "write matrix with anchor cell" begin
        test_data = [ 1 2 3 ; 4 5 6 ; 7 8 9]
        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            sheet["A7"] = test_data
        end

        XLSX.openxlsx(filename) do xf
            sheet = xf[1]
            rows, cols = size(test_data)
            for c in 1:cols, r in 1:rows
                @test sheet[r+6, c] == test_data[r, c]
            end
        end
    end

    @testset "write matrix with range" begin
        test_data = [ 1 2 3 ; 4 5 6 ; 7 8 9]
        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            sheet["A7:C9"] = test_data
        end

        XLSX.openxlsx(filename) do xf
            sheet = xf[1]
            rows, cols = size(test_data)
            for c in 1:cols, r in 1:rows
                @test sheet[r+6, c] == test_data[r, c]
            end
        end
    end

    @testset "write matrix with range mismatch" begin
        test_data = [ 1 2 3 ; 4 5 6 ; 7 8 9]
        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            @test_throws AssertionError sheet["A7:C10"] = test_data
        end
    end

    @testset "write matrix with heterogeneous data types" begin
        # issue #97
        test_data = ["A" "B"; 1 2; "a" "b"; 0 "x"]
        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            sheet["B2"] = test_data
        end

        XLSX.openxlsx(filename, mode="r") do xf
            sheet = xf[1]
            @test sheet["B2"] == "A"
            @test sheet["C2"] == "B"
            @test sheet["B3"] == 1
            @test sheet["C3"] == 2
            @test sheet["B4"] == "a"
            @test sheet["C4"] == "b"
            @test sheet["B5"] == 0
            @test sheet["C5"] == "x"
        end
    end

    @testset "doctest for writetable!" begin
        columns = Vector()
        push!(columns, [1, 2, 3])
        push!(columns, ["a", "b", "c"])

        labels = [ "column_1", "column_2"]

        XLSX.openxlsx(filename, mode="w") do xf
            sheet = xf[1]
            XLSX.writetable!(sheet, columns, labels, anchor_cell=XLSX.CellRef("B2"))
        end

        # read data back
        XLSX.openxlsx(filename) do xf
            sheet = xf[1]
            @test sheet["B2"] == "column_1"
            @test sheet["C2"] == "column_2"
            @test sheet["B3"] == 1
            @test sheet["B4"] == 2
            @test sheet["B5"] == 3
            @test sheet["C3"] == "a"
            @test sheet["C4"] == "b"
            @test sheet["C5"] == "c"
        end
    end

    @testset "openxlsx without do-syntax" begin
        let
            xf = XLSX.openxlsx(filename)
            sheet = xf[1]
            @test sheet["B2"] == "column_1"
            close(xf)
        end

        let
            xf = XLSX.openxlsx(filename, mode="w")
            sheet = xf[1]
            sheet["A1"] = "openxlsx without do-syntax"
            XLSX.writexlsx(filename, xf, overwrite=true)
        end

        let
            xf = XLSX.openxlsx(filename)
            sheet = xf[1]
            @test sheet["A1"] == "openxlsx without do-syntax"
            close(xf)
        end
    end

    rm(filename)
end

@testset "escape" begin

    @test XLSX.xlsx_escape("hello&world<'") == "hello&world<'"

    esc_filename  = "output_table_escape_test.xlsx"
    esc_col_names = ["&; & " < > ' ", "I❤Julia", "\"<'&O-O&'>\"", "<&>"]
    esc_sheetname = string( esc_col_names[1],esc_col_names[2],esc_col_names[3],esc_col_names[4])
    esc_data = Vector{Any}(undef, 4)
    esc_data[1] = ["11&&",    "12"&",    "13<&",    "14>&",    "15'&"    ]
    esc_data[2] = ["21&&&&",  "22&"&&",  "23&<&&",  "24&>&&",  "25&'&&"  ]
    esc_data[3] = ["31&&&&&&","32&&"&&&","33&&<&&&","34&&>&&&","35&&'&&&"]
    esc_data[4] = ["41& &; &&",   "42\" \"; \"\"","43< <; <<",  "44> >; >>",  "45' '; ''"    ]
    XLSX.writetable(esc_filename, esc_data, esc_col_names, overwrite=true, sheetname=esc_sheetname)

    r1_data, r1_col_names = XLSX.readtable(esc_filename, esc_sheetname)
    check_test_data(r1_data, esc_data)
    @test r1_col_names[4] == Symbol( esc_col_names[4] )
    @test r1_col_names[3] == Symbol( esc_col_names[3] )
    @test r1_col_names[2] == Symbol( esc_col_names[2] )
    @test r1_col_names[1] == Symbol( esc_col_names[1] )
    rm(esc_filename)

    # compare to the backup version: escape.xlsx
    r2_data, r2_col_names = XLSX.readtable(joinpath(data_directory, "escape.xlsx"), esc_sheetname)
    check_test_data(r2_data, esc_data)
    check_test_data(r2_data, r1_data)
    @test r2_col_names[4] == Symbol( esc_col_names[4] )
    @test r2_col_names[3] == Symbol( esc_col_names[3] )
    @test r2_col_names[2] == Symbol( esc_col_names[2] )
    @test r2_col_names[1] == Symbol( esc_col_names[1] )
end

# issue #67
@testset "row_index" begin
    filename = "test_pr67.xlsx"
    XLSX.openxlsx(filename, mode="w") do xf
        xf[1]["A2"] = 5
        xf[1]["A1"] = 7
    end
    @test isfile(filename)
    rm(filename)
end

# issues #62, #75
@testset "relative paths" begin
    let
        xf = XLSX.readxlsx(joinpath(data_directory, "openpyxl.xlsx"))
        @test XLSX.sheetnames(xf) == [ "Sheet", "Test1" ]
        @test xf["Test1"]["A1"] == "One"
        @test xf["Test1"]["A2"] == 1
        show(IOBuffer(), xf)
        show(IOBuffer(), xf["Sheet"])
        show(IOBuffer(), xf["Test1"])
    end

    let
        data, col_names = XLSX.readtable(joinpath(data_directory, "openpyxl.xlsx"), "Test1")
        @test data == [ [1, 3], [2, 4]]
        @test col_names == [:One, :Two]
    end
end

# issues #62, #71
@testset "windows compatibility" begin
    xf = XLSX.open_xlsx_template(joinpath(data_directory, "issue62_71.xlsx"))
    @test xf["Sheet1"]["A1"] == "One"
    @test xf["Sheet1"]["A2"] == 1

    @test collect(keys(xf.binary_data)) == ["xl/printerSettings/printerSettings1.bin"]
end


text:
export kronecker

"""
    kronecker(
        scale::Int64,
        edgefactor::Int64;
        a::Float64=0.57,
        b::Float64=0.19,
        c::Float64=0.19
    )

Generates edges for a Kronecker generator graph. Returns an array of 2 rows
with 1st being the start edge and 2nd row having the end edge.
"""
function kronecker(
    scale::Int64,
    edgefactor::Int64;
    a::Float64=0.57,
    b::Float64=0.19,
    c::Float64 = 0.19
    )
#Ported from https://gitorious.org/graph500/graph500?p=graph500:graph500.git;a=blob;f=octave/kronecker_generator.m;h=064b6d4f42194cc95ec4ab375dcbee7c3243b233;hb=01a32a9c16994c3cf30bbf4caabf9be128379416.
    n = 2 ^ scale
    m = edgefactor * n
    a,b,c = 0.57, 0.19, 0.19
    ij = ones(Int64, 2, m)
    ab = a + b
    cnorm = c/(1-ab)
    anorm = a/ab

    for ib in 1:scale
        ii_bit = rand(1, m) .> ab
        jj_bit = rand(1, m) .> ( cnorm * ii_bit + anorm * .!(ii_bit) )
        ij = ij .+ 2^(ib-1) .* [ii_bit; jj_bit]
    end

    p = randperm(n)
    ij = p[ij]

    p = randperm(m)
    ij = ij[:, p]

    return ij .- 1 #Convert to zero index for Stinger
end

ext: jl


text:
infile = "./src/Day9/input.txt"
input = parse.(Int,readlines(infile))

# part 1, check that each number can be the sum of any 2 of the prior 25
# find first anomaly

function checkpairs(bank, target)
    for i in 1:(length(bank)-1), j in (i+1):length(bank)
        check = bank[i] + bank[j] == target
        if check
            return true
        end
    end
    return false
end

bank = input[1:25]
target = input[26]
checkpairs(bank, target)

function findanomaly(input)
    bank = input[1:25]
    for test in 26:length(input)
        target = input[test]
        if !checkpairs(bank, target)
            return target, test
        end
        discard = popfirst!(bank)
        push!(bank, target)
    end
    return 0, 0
end
anomaly, pos = findanomaly(input)

function breakencryption(input, anomaly)
    for start in 1:(length(input) - 1)
        for finish in (start+1):length(input)
            adds = sum(input[start:finish])
            if adds > anomaly
                break # end finish loop
            end
            if adds == anomaly
                return start, finish, minimum(input[start:finish]), maximum(input[start:finish])
            end
        end
    end
    return 0,0,0,0
end

start, finish, small, big = breakencryption(input, anomaly)
sum(input[518:534])
small + big


text:
@testset "$(rpad("Nonlinear solvers",80))" begin

    n = 1
    T = Float64

    function F(x::Vector, b::Vector)
        b[:] = x.^2
    end

    function J(x::Vector, A::Matrix)
        A[:,:] = 2x
    end

    for Solver in (NewtonSolver, QuasiNewtonSolver)
        x = ones(T, n)
        nl = Solver(x, F)
        solve!(nl)
        # println(nl.status.i, ", ", nl.status.rₐ,", ",  nl.status.rᵣ,", ",  nl.status.rₛ)
        for x in nl.x
            @test x ≈ 0 atol=1E-7
        end

        x = ones(T, n)
        nl = Solver(x, F, J=J)
        solve!(nl)
        # println(nl.status.i, ", ", nl.status.rₐ,", ",  nl.status.rᵣ,", ",  nl.status.rₛ)
        for x in nl.x
            @test x ≈ 0 atol=1E-7
        end
    end

end


text:
@testset "unit tests" begin
@testset "projection" begin
    x0 = Line(Point(0, 10), O)
    p = Point(10, 20)
    @test projection(p, x0) == Point(0, 20)

    y0 = Line(Point(10, 0), O)
    @test projection(p, y0) == Point(10, 0)
end

@testset "translation" begin
    video = Video(500, 500)
    # dummy action doesn't need a real function
    action = Action(1:100, ()->1, Translation(Point(1,1), Point(100, 100)))
    # needs internal translation as well
    push!(action.internal_transitions, Javis.InternalTranslation(O))
    Javis.compute_transformation!(action, video, 1)
    @test action.internal_transitions[1].by == Point(1,1)
    Javis.compute_transformation!(action, video, 50)
    @test action.internal_transitions[1].by == Point(50,50)
    Javis.compute_transformation!(action, video, 100)
    @test action.internal_transitions[1].by == Point(100,100)
end

@testset "Relative frames" begin
    video = Video(500, 500)
    # dummy action doesn't need a real function
    Action(1:100, ()->1, Translation(Point(1,1), Point(100, 100)))
    action = Action(Rel(10), ()->1, Translation(Point(1,1), Point(100, 100)))
    @test action.frames == 101:110
end

@testset "translation from origin" begin
    video = Video(500, 500)
    # dummy action doesn't need a real function
    action = Action(1:100, ()->1, Translation(Point(99, 99)))
    # needs internal translation as well
    push!(action.internal_transitions, Javis.InternalTranslation(O))

    Javis.compute_transformation!(action, video, 1)
    @test action.internal_transitions[1].by == O
    Javis.compute_transformation!(action, video, 50)
    @test action.internal_transitions[1].by == Point(49,49)
    Javis.compute_transformation!(action, video, 100)
    @test action.internal_transitions[1].by == Point(99,99)

    video = Video(500, 500)
    # dummy action doesn't need a real function
    action = Action(1:100, ()->1, Translation(99, 99))
    # needs internal translation as well
    push!(action.internal_transitions, Javis.InternalTranslation(O))

    Javis.compute_transformation!(action, video, 1)
    @test action.internal_transitions[1].by == O
    Javis.compute_transformation!(action, video, 50)
    @test action.internal_transitions[1].by == Point(49,49)
    Javis.compute_transformation!(action, video, 100)
    @test action.internal_transitions[1].by == Point(99,99)
end

@testset "rotations" begin
    video = Video(500, 500)
    # dummy action doesn't need a real function
    action = Action(1:100, ()->1, Rotation(2π))
    # needs internal translation as well
    push!(action.internal_transitions, Javis.InternalRotation(0.0, O))

    Javis.compute_transformation!(action, video, 1)
    @test action.internal_transitions[1].angle == 0.0
    Javis.compute_transformation!(action, video, 100)
    @test action.internal_transitions[1].angle == 2π

    video = Video(500, 500)
    # dummy action doesn't need a real function
    action = Action(1:100, ()->1, Rotation(2π, Point(2.0, 5.0)))
    # needs internal translation as well
    push!(action.internal_transitions, Javis.InternalRotation(0.0, O))

    Javis.compute_transformation!(action, video, 1)
    @test action.internal_transitions[1].angle == 0.0
    @test action.internal_transitions[1].center == Point(2.0, 5.0)
    Javis.compute_transformation!(action, video, 100)
    @test action.internal_transitions[1].angle == 2π
    @test action.internal_transitions[1].center == Point(2.0, 5.0)
end

@testset "Frames errors" begin
    video = Video(500, 500)
    # throws because the frames of the first action need to be defined explicitly
    @test_throws ArgumentError Action((args...)->1, Translation(Point(1,1), Point(100, 100)))
    # throws because the frames of the first action need to be defined explicitly
    @test_throws ArgumentError Action(Rel(10), (args...)->1, Translation(Point(1,1), Point(100, 100)))
    Action(1:100, (args...)->1, Translation(Point(1,1), Point(100, 100)))
    # throws because :some is not supported as Symbol for `frames`
    @test_throws ArgumentError Action(:some, :id, (args...)->1, Translation(Point(1,1), Point(100, 100)))
end

@testset "Unspecified symbol error" begin
    video = Video(500, 500)
    # throws because the frames of the first action need to be defined explicitly
    @test_throws ErrorException javis(video, [
        Action(1:100, (args...)->1),
        Action(1:100, (args...)->line(O, pos(:non_existent), :stroke))
    ])

    video = Video(500, 500)
    # throws because the frames of the first action need to be defined explicitly
    @test_throws ErrorException javis(video, [
        Action(1:100, (args...)->1),
        Action(1:100, (args...)->line(O, ang(:non_existent), :stroke))
    ])
end
end


text:
mad_not_normalized(x) = mad(x, normalize=false)
mad_normalized(x) = mad(x, normalize=true)

for T1 in (:T, :(Union{Missing,T}))
    for (R,F) in ((:rollmin, :minimum), (:rollmax, :maximum),
                  (:rollmean, :mean), (:rollmedian, :median),
                  (:rollvar, :var), (:rollstd, :std),
                  (:rollskewness, :skewness), (:rollkurtosis, :kurtosis),
                  (:rollsem, :sem),
                  (:rollmad, :mad_not_normalized),
                  (:rollmad_normalized, :mad_normalized),
                  (:rollvariation, :variation))
        @eval begin
            $R(data::V, windowspan::Int) where {T, V<:AbstractVector{$T1}} =
                rolling($F, data, windowspan)
            $R(data::V, windowspan::Int, weighting::AbstractVector{S}) where {T, V<:AbstractVector{$T1}, S} =
                rolling($F, data, windowspan, weighting)
            $R(data::V, windowspan::Int, weighting::AbstractWeights) where {T, V<:AbstractVector{$T1}} =
                rolling($F, data, windowspan, weighting.values)
        end
    end
end

rollcor(data1::V1, data2::V2, windowspan::Int) where {T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                         V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cor, data1, data2, windowspan)

rollcov(data1::V1, data2::V2, windowspan::Int) where {T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                         V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cov, data1, data2, windowspan)

rollcor(data1::V1, data2::V2, windowspan::Int, weighting::AbstractVector{S}) where {S, T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                                                V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cor, data1, data2, windowspan, weighting)

rollcov(data1::V1, data2::V2, windowspan::Int, weighting::AbstractVector{S}) where {S, T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                                                V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cov, data1, data2, windowspan, weighting)

#=
repeatedly calls itself

rollcor(data1::V1, data2::V2, windowspan::Int, weighting::AbstractWeights) where {S, T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                                                V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cor, data1, data2, windowspan, weighting.values)

rollcov(data1::V1, data2::V2, windowspan::Int, weighting::AbstractWeights) where {S, T, V1<:Union{AbstractVector{T}, AbstractVector{Union{Missing,T}}},
                                                                                V2<:Union{AbstractVector{T},AbstractVector{Union{Missing,T}}}} =
    rolling(cov, data1, data2, windowspan, weighting.values)
=#


text:
"""
Dipole type.

#### Parameters

* coord_system: The coordinate system that the locations are stored in
* x,y,z: Location of dipole
* x,y,z/ori: Orientation of dipole
* color: Color of dipole for plotting
* state: State of dipol
* size: size of dipole

"""
mutable struct Dipole
    coord_system::AbstractString
    x::typeof(1.0u"m")
    y::typeof(1.0u"m")
    z::typeof(1.0u"m")
    xori::Number
    yori::Number
    zori::Number
    color::Number
    state::Number
    size::Number
end

import Base.show
function Base.show(io::IO, d::Dipole)
    @printf("Dipole with coordinates x = % 6.2f m, y = % 6.2f m, z = % 6.2f m, size = % 9.5f\n",
        ustrip(d.x), ustrip(d.y), ustrip(d.z), ustrip(d.size))
end

function Base.show(io::IO, dips::Array{Dipole})
    @printf("%d dipoles\n", length(dips))
    for d in dips
        @printf("  Dipole with coordinates x = % 6.2f m, y = % 6.2f m, z = % 6.2f m and size = % 9.5f\n",
            ustrip(d.x), ustrip(d.y), ustrip(d.z), ustrip(d.size))
    end
end