Unable to parse this excel file

[javierav]

The XLS file used is a report of the spanish electric operator from this url:
71-PVPC_DETALLE_DD-2018-01-14T23_59_59+00_00.xls.zip

Preview of excel:

The code used:

#!/usr/bin/env ruby

require 'spreadsheet'
require 'awesome_print'

file = ARGV[0]
book = Spreadsheet.open(file)
sheet = book.worksheet(0)

20.times do |t|
  ap sheet.row(t), multiline: false
end

The output that I have:

[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil ]
[ "Hora", nil, nil, nil, "Componentes PVPC", nil, nil, "Coeficiente pérdidas", nil, "Total", "Otros costes", nil, nil, nil, nil, nil, nil, nil, nil, nil, "Mercados ajuste sistema", nil, nil, nil, nil, "Mercados diario e intradiario 1", nil, nil, nil, "Perfil" ]
[ "Día", "Hora", "Peaje", "Periodo", "Término energía PVPC\nFEU = TEU + TCU\n€/MWh consumo", "Peaje acceso\nTEU\n€/MWh consumo", "Precio producción\nTCU=CP x(1+PERD/100)\n€/MWh consumo", "% coeficiente \npérdidas PVPC\nPERD", "% coeficiente \npérdidas estándar", "Coste producción\nCP\n€/MWh bc", "Total\nOC\n€/MWh bc", "Financiación OS\n€/MWh bc", "Financiación OM\n€/MWh bc", "Cargo capacidad\n€/MWh bc", "Servicio \ninterrumpibilidad\n€/MWh bc", "CCVh coste comercialización  RCVtovph\n€/MWh bc", "CCVh coste comercialización  RFE\n€/MWh bc", "CCVh coste comercialización  RMRv\n€/MWh bc", "CCVh coste comercialización  Runitaria\n€/MWh bc", "Total\nSAH\n€/MWh bc", "Otros sistema\n€/MWh bc", "Coste desvíos\n€/MWh bc", "Coste banda\n€/MWh bc", "Coste  reserva\n€/MWh bc", "Coste restricciones\ntécnicas diario\n€/MWh bc", "Total\nPMH\n€/MWh bc", "Componente\nintradiario 1\n€/MWh bc", "Mercado\ndiario\n€/MWh bc", nil, "Coeficiente\nperfilado" ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f97750472e8 @shared=false, @data="*\x00D\x05\x00\x1C\xC0A\x81\x00\x19\x02\a\x00\x17\x00\x00\x19\b\x16\x00D\x05\x00\x1C\xC0\x1E\n\x00\x1E\x14\x00AQ\x01\x06\x19\b\x03\x00B\x03\x01\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f9775046a50 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f9775045c68 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f9775044660 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f97750441b0 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503fc00 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503f890 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503f4d0 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503f138 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503ed00 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503e6c0 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503e0d0 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503da90 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503d400 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]
[ nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, nil, #<Spreadsheet::Formula:0x00007f977503cfc8 @shared=true, @data="\x05\x00\x01\x06\x00\x1D\x00"> ]

Spreadsheet gem is unable to retrieve the cell values, such as dates or numbers. Any idea?

[zdavatz]

Which software created the original file, that you try to parse with spreadsheet gem?

[javierav]

I don`t know the software used to generate the excel file. It's generated by a public company of the government of Spain 😢

[zdavatz]

Ok, please open the file you get from the gov, then save it with libreoffice and then try to edit it with spreadsheet gem.