xls format only writes number columns

81 views
Skip to first unread message

ctammes1

unread,
Mar 9, 2017, 1:35:53 PM3/9/17
to spreadsheet
My script reads a xls file, modifies two columns and writes it back to disc.
The file has 7 columns, two of which are numbers, the others text. When I view the result at the end of the process, the contents (sheet) are correct. However, after writing to disc, only the number columns are written, the text columns are blank.
Any suggestions of how to solve this? Using gem version 1.14

My code:

Spreadsheet.client_encoding = 'UTF-8'
book
= Spreadsheet.open filename
sheet
=book.worksheet 0
sheet
.each_with_index do |row, i|
 
if i>0
    row
[5]= codes[i-1][0]
    row
[6]= codes[i-1][1]
 
end
end
out_file
= filename.basename.to_s.gsub(/(.*)\.(.*)/,'\1_1.\2')
book
.write Rails.root.join(Rails.configuration.x.download_dir, out_file).to_s


I found out that another similar spreadsheet outputs all the columns. Below is a dump of the file headers, does that give anyone a clue?

working file:
D0 CF 11 E0 A1 B1 1A E1 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 3B 00 03 00 FE FF 09 00 06 00 00 00 00 00 00 00 00 00 00 00 02 00 00 00 CF 00 00 00 00 00 00 00 00 10 00 00 CC 00 00 00 01 00 00 00 FE FF FF FF 00 00 00 00 00 00 00 00

problem file:
D0 CF 11 E0 A1 B1 1A E1 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 3E 00 03 00 FE FF 09 00 06 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 01 00 00 00 00 00 00 00 00 10 00 00 43 00 00 00 01 00 00 00 FE FF FF FF 00 00 00 00 00 00 00 00


ctammes1

unread,
Mar 9, 2017, 2:01:26 PM3/9/17
to spreadsheet
P.S. I found a difference in the file header. It seems like some data is not written. This is not the case in the other (working) file.


input file before action:


D0 CF 11 E0 A1 B1 1A E1 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 3E 00 03 00 FE FF 09 00 06 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 01 00 00 00 00 00 00 00 00 10 00 00 43 00 00 00 01 00 00 00 FE FF FF FF 00 00 00 00 00 00 00 00

output file after changes:
D0 CF 11 E0 A1 B1 1A E1 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 3B 00 03 00 FE FF 09 00 06 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 2F 00 00 00 00 00 00 00 00 10 00 00 FE FF FF FF 00 00 00 00 FE FF FF FF 00 00 00 00 30 00 00 00


Reply all
Reply to author
Forward
0 new messages