AssertionError: isempty(XML_GLOBAL_ERROR_STACK)

[Jota27xd]

Today I got the "AssertionError: isempty(XML_GLOBAL_ERROR_STACK)" when I tried to export data to an Excel file.

My code to do this is (I export a lot of data in different worksheets):

archivo = "resultados5.xlsx"
XLSX.openxlsx(archivo, mode = "rw") do xf
    HojaCostos = xf[1]
    XLSX.writetable!(HojaCostos, [costos], ["Valor"], anchor_cell=XLSX.CellRef("B1"))
    HojaPlantas = xf[2]
    XLSX.writetable!(HojaPlantas, collect(DataFrames.eachcol(Plantas)), DataFrames.names(Plantas), anchor_cell=XLSX.CellRef("B1"))
    HojaPotencias = xf[3]
    XLSX.writetable!(HojaPotencias, collect(DataFrames.eachcol(indices)),  DataFrames.names(indices), anchor_cell=XLSX.CellRef("A1"))
    XLSX.writetable!(HojaPotencias, collect(DataFrames.eachcol(potencias)), vcat(Generadores, "Demanda", "DNS", "Vertimiento"), anchor_cell=XLSX.CellRef("F1"))
    HojaUnitCommitment = xf[4]
    XLSX.writetable!(HojaUnitCommitment, collect(DataFrames.eachcol(UnitCommitment)), vcat(Generadores[1:gen_Sincronos],Generadores[1:gen_Sincronos],Generadores[1:gen_Sincronos]), anchor_cell=XLSX.CellRef("D1"))
    HojaOtros = xf[5]
    XLSX.writetable!(HojaOtros, collect(DataFrames.eachcol(Otros)), DataFrames.names(Otros), anchor_cell=XLSX.CellRef("F1"))
end

and I got the following Stacktrace

Stacktrace:
 [1] macro expansion at D:\folder\.julia\packages\EzXML\ZNwhK\src\error.jl:49 [inlined]
 [2] addelement!(::EzXML.Node, ::String) at D:\folder\.julia\packages\EzXML\ZNwhK\src\node.jl:944
 [3] update_worksheets_xml!(::XLSX.XLSXFile) at D:\folder\.julia\packages\XLSX\A7wWu\src\write.jl:180
 [4] writexlsx(::String, ::XLSX.XLSXFile; overwrite::Bool) at D:\folder\.julia\packages\XLSX\A7wWu\src\write.jl:55
 [5] openxlsx(::var"#127#128", ::String; mode::String, enable_cache::Bool) at D:\folder\.julia\packages\XLSX\A7wWu\src\read.jl:133
 [6] top-level scope at D:\folder\despacho2_maestro.jl:234
 [7] include_string(::Function, ::Module, ::String, ::String) at .\loading.jl:1088
in expression starting at D:\folder\despacho2_maestro.jl:234

Yesterday this code works correctly, but today won't. I updated the XLSX package but was not useful. I created and duplicated files in an attempt to test if the problem was the original file, but it didn't work either. I need help because this is my Master Thesis, and recently this code worked.

[felipenoris]

Hi @Jota27xd !
That error indicates that either the file got corrupted, or it is using XML extentions (I've seen some files that use some ActiveX components that trigger this error). Did you edit the file without using Julia? Can you revert to a backup file? It is a good idea to split input and output files because there's always a risk of files being corrupted during the write process, and that applies to any software.

[Jota27xd]

Did you edit the file without using Julia?

Yes, but never in my year using Julia I got this error message.

Can you revert to a backup file?

I created 2 different files in an attempt to use my code. The first file was a copy of the original, where I erase all the cells that Julia will write, and the second was a brand-new file, completely in blank, and I got the same error

It is a good idea to split input and output files because there's always a risk of files being corrupted during the write process, and that applies to any software.

They are splitted. In fact, when I got the AssertionError, after I ran all my code, and I got the error in the Import Data section. So, I restarted Julia in the Juno IDLE, and I could import my data. The code I use to import data is:

datos = XLSX.readdata("datos.xlsx", "Datos", "C2:C30")

[felipenoris]

@Jota27xd I tried the code below on an blank file with 3 worksheets. I reused the file to write on it many times, and it works just fine.
Unless I'm able to reproduce the error, I can't do much to help you. Can you come up with a self-contained code that triggers this error?

import XLSX
using DataFrames
fp = "Pasta1.xlsx"
@assert isfile(fp)

df1 = DataFrame(A = [1,2,3], B = ["Hey", "You", "dddddd"])
df2 = DataFrame(COL1 = [1,2,3,4], COL2 = [10.2, 30.2, 10.2, 30.3], COL3 = ["A", "B", "C", "D"])

XLSX.openxlsx(fp, mode="rw") do xf
    xf1 = xf[1]
    XLSX.writetable!(xf1, collect(DataFrames.eachcol(df1)), DataFrames.names(df1), anchor_cell=XLSX.CellRef("B1"))
    
    xf2 = xf[2]
    XLSX.writetable!(xf2, collect(DataFrames.eachcol(df2)), DataFrames.names(df2), anchor_cell=XLSX.CellRef("B1"))
end

[Jota27xd]

Hi!

With your code I get the same error. I wrote an example code of the export section that I'm using (show below) and I got the error too.

Here is the code:

using XLSX      # Leer y escribir Excel
using DataFrames    # Tablitas

costos = [1e6; [1e5; 1e5; 1e5]; [1e5; 1e5; 1e5]; [500; 500; 500]]

Plantas = DataFrame()
Plantas.Existentes = [0; 0; 0; 0; 0; 0; 0; 0; 0]
Plantas.Nuevas = [1; 2; 3; 4; 5; 6; 7; 8; 9]
Plantas.Capacidad = [200; 500; 357.68; 145.2; 0; 0; 98; 560.12; 78.1]

archivo = "test.xlsx"
XLSX.openxlsx(archivo, mode = "rw") do xf
    HojaCostos = xf[1]
    XLSX.writetable!(HojaCostos, [costos], ["Valor"], anchor_cell=XLSX.CellRef("B1"))
    HojaPlantas = xf[2]
    XLSX.writetable!(HojaPlantas, collect(DataFrames.eachcol(Plantas)), DataFrames.names(Plantas), anchor_cell=XLSX.CellRef("B1"))
end

[Jota27xd]

Hi! I'm still have the error, but maybe I found the reason. The file that I use have a lot of cells with formulas, and the data obtained from Julia fill many cells, which are used in the calculations. So when I export the data to Excel that have the columns G to N of the first sheet ("Costos"), XLSX gives me the error we are talking about. If the destination file does not have such columns, the error does not occur.

I attach the file here, and the code is similar to what I wrote some answers before, only it writes on 4 sheets, and the input data is obtained from an optimization.

I don't know if this is a planned feature or just a bug. I hope it helps you, in case this problem occurs again. Cheers!

PS: I also noticed that when you execute writing in mode =" w " instead of mode =" rw ", you cannot export data in different sheets, because the newly created file only has one sheet.

[ghost]

I'm getting this issue just trying to read a very basic test file (attached).

julia> test_xlsx
"C:\\Users\\dwales\\Downloads\\test.xlsx"

julia> xf = XLSX.readxlsx(test_xlsx)
ERROR: AssertionError: isempty(XML_GLOBAL_ERROR_STACK)
Stacktrace:
 [1] macro expansion at C:\Users\dwales\.julia\packages\EzXML\ZNwhK\src\error.jl:49 [inlined]
 [2] readxml(::ZipFile.ReadableFile) at C:\Users\dwales\.julia\packages\EzXML\ZNwhK\src\document.jl:154
 [3] internal_xml_file_read(::XLSX.XLSXFile, ::String) at C:\Users\dwales\.julia\packages\XLSX\A7wWu\src\read.jl:427
 [4] open_or_read_xlsx(::String, ::Bool, ::Bool, ::Bool) at C:\Users\dwales\.julia\packages\XLSX\A7wWu\src\read.jl:207
 [5] readxlsx(::String) at C:\Users\dwales\.julia\packages\XLSX\A7wWu\src\read.jl:37
 [6] top-level scope at REPL[95]:1

The test file simply contains:

Test	File
1	4
2	5
3	6

Edit:

I just tested the exact same file on my Mac, and it worked without error.
I can only reproduce the error on Windows.

Julia: 1.5.2
XLSX: 0.7.3
Windows: 10, build 1909
macOS: 10.13

Edit 2:

All tests pass on Mac, but I'm getting a failing test when I run on Windows.

See test output.

``` julia> Pkg.test("XLSX") Testing XLSX Status `C:\Users\dwales\AppData\Local\Temp\jl_UtSRwc\Project.toml` [a93c6f00] DataFrames v0.22.1 [8f5d6c58] EzXML v1.1.0 [bd369af6] Tables v1.2.1 [fdbf4ff8] XLSX v0.7.3 [a5390f91] ZipFile v0.9.3 [ade2ca70] Dates [de0858da] Printf [8dfed614] Test Status `C:\Users\dwales\AppData\Local\Temp\jl_UtSRwc\Manifest.toml` [56f22d72] Artifacts v1.3.0 [324d7699] CategoricalArrays v0.9.0 [34da2185] Compat v3.23.0 [a8cc5b0e] Crayons v4.0.4 [9a962f9c] DataAPI v1.4.0 [a93c6f00] DataFrames v0.22.1 [864edb3b] DataStructures v0.18.8 [e2d170a0] DataValueInterfaces v1.0.0 [8f5d6c58] EzXML v1.1.0 [59287772] Formatting v0.4.1 [41ab1584] InvertedIndices v1.0.0 [82899510] IteratorInterfaceExtensions v1.0.0 [692b3bcd] JLLWrappers v1.1.3 [682c06a0] JSON v0.21.1 [94ce4f54] Libiconv_jll v1.16.0+7 [e1d29d7a] Missings v0.4.4 [bac558e1] OrderedCollections v1.3.2 [69de0a69] Parsers v1.0.12 [2dfb63ee] PooledArrays v0.5.3 [08abe8d2] PrettyTables v0.10.1 [189a3867] Reexport v0.2.0 [a2af1166] SortingAlgorithms v0.3.1 [856f2bd8] StructTypes v1.1.0 [3783bdb8] TableTraits v1.0.0 [bd369af6] Tables v1.2.1 [fdbf4ff8] XLSX v0.7.3 [02c8fc9c] XML2_jll v2.9.10+3 [a5390f91] ZipFile v0.9.3 [83775a58] Zlib_jll v1.2.11+18 [2a0f44e3] Base64 [ade2ca70] Dates [8bb1440f] DelimitedFiles [8ba89e20] Distributed [9fa8497b] Future [b77e0a4c] InteractiveUtils [76f85450] LibGit2 [8f399da3] Libdl [37e2e46d] LinearAlgebra [56ddb016] Logging [d6f4376e] Markdown [a63ad114] Mmap [44cfe95a] Pkg [de0858da] Printf [3fa0cd96] REPL [9a3f8284] Random [ea8e919c] SHA [9e88b42a] Serialization [1a1011a3] SharedArrays [6462fe0b] Sockets [2f01184e] SparseArrays [10745b16] Statistics [8dfed614] Test [cf7118a7] UUIDs [4ec0a83e] Unicode Test Summary: | Pass Total read test files | 22 22 Test Summary: | Pass Total Cell names | 326 326 Test Summary: | Pass Total getindex | 12 12 Test Summary: | Pass Total Time and DateTime | 6 6 Test Summary: | Pass Total number formats | 16 16 Test Summary: | Pass Total Defined Names | 24 24 Test Summary: | Pass Total Book1.xlsx | 22 22 Test Summary: | Pass Total book_1904_ptbr.xlsx | 9 9 Test Summary: | Pass Total numbers.xlsx | 75 75 Test Summary: | Pass Total Column Range | 9 9 Test Summary: | Pass Total CellRange iterator | 1 1 Test Summary: | Pass Total Table | 471 471 Test Summary: | Pass Total Helper functions | 26 26 Test Summary: | Pass Total Write | 57 57 Test Summary: | Pass Total Edit Template | 3 3 Test Summary: | Pass Total addsheet! | 10 10 Test Summary: | Pass Total Edit | 13 13 Test Summary: | Pass Total writetable | 106 106 Test Summary: | Pass Total Styles | 74 74 Test Summary: | Pass Total filemodes | 200 200 Test Summary: | Pass Total escape | 84 84 Test Summary: | Pass Total row_index | 1 1 Test Summary: | show xlsx | No tests Test Summary: | Pass Total relative paths | 5 5 Test Summary: | Pass Total windows compatibility | 3 3 Test Summary: | Pass Total whitespace nodes | 8 8 Test Summary: | Pass Total inlineStr | 20 20 Tables.jl integration: Test Failed at C:\Users\dwales\.julia\packages\XLSX\A7wWu\test\runtests.jl:1803 Expression: length(ct) == 0 Evaluated: 3 == 0 Stacktrace: [1] top-level scope at C:\Users\dwales\.julia\packages\XLSX\A7wWu\test\runtests.jl:1803 [2] top-level scope at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Test\src\Test.jl:1115 [3] top-level scope at C:\Users\dwales\.julia\packages\XLSX\A7wWu\test\runtests.jl:1771 Test Summary: | Pass Fail Total Tables.jl integration | 20 1 21 Tables.jl with DataFrames | 4 4 ERROR: LoadError: Some tests did not pass: 20 passed, 1 failed, 0 errored, 0 broken. in expression starting at C:\Users\dwales\.julia\packages\XLSX\A7wWu\test\runtests.jl:1770 ERROR: Package XLSX errored during testing Stacktrace: [1] pkgerror(::String) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\Types.jl:52 [2] test(::Pkg.Types.Context, ::Array{Pkg.Types.PackageSpec,1}; coverage::Bool, julia_args::Cmd, test_args::Cmd, test_fn::Nothing) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\Operations.jl:1578 [3] test(::Pkg.Types.Context, ::Array{Pkg.Types.PackageSpec,1}; coverage::Bool, test_fn::Nothing, julia_args::Cmd, test_args::Cmd, kwargs::Base.Iterators.Pairs{Union{},Union{},Tuple{},NamedTuple{(),Tuple{}}}) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:327 [4] test(::Pkg.Types.Context, ::Array{Pkg.Types.PackageSpec,1}) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:314 [5] #test#61 at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:67 [inlined] [6] test at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:67 [inlined] [7] #test#60 at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:66 [inlined] [8] test at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:66 [inlined] [9] test(::String; kwargs::Base.Iterators.Pairs{Union{},Union{},Tuple{},NamedTuple{(),Tuple{}}}) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:65 [10] test(::String) at D:\buildbot\worker\package_win64\build\usr\share\julia\stdlib\v1.5\Pkg\src\API.jl:65 [11] top-level scope at REPL[103]:1 ```

[ghost]

Hi, I am using XLSX a lot and see this error message all the time. I do not yet know the exact source of the problem but it pops up after (almost) each manipulation of the file in Excel. Workaround that works for me (but is pretty annoying) is to copy all worksheets into new file. I would be happy to help debugging if you have any ideas.

[felipenoris]

@pnalysz-ipl Hi! It would be helpful if you could post the problematic file, like others did in this thread.

[ghost]

I am attaching sample file.
sample.xlsx
Thanks.