xls files with greater than 140kB are tagged as malware by Gmail and MS Excel

[yugendran]

spreadsheet (1.0.7)

I have been using the Spreadsheet gem to create xls files and zip them for download using rubyzip gem in my application.

Below is the code snippet of my lib class that generates xls files

def worksheet_test
  excel_file = Spreadsheet::Workbook.new     
  sheet1 = excel_file.create_worksheet :name => 'LCB Melbourne Attendance'
  wk_hash = Hash.new{|hsh,key| hsh[key]=[]}

  size = @single_array_size - 4
  mothly_size = @student_monthly_attn_weeks.uniq.flatten.compact.count + 2 + 2
  merge_size = (size- (@wk_array.uniq.count+mothly_size))/2

  #Week number Row -1
  wk_arry = []
  wk_name = []
  4.times {wk_name << " "}

  @temp_hash1.keys.each_with_index do |v,i|
    DateTime.parse(v.to_s).cweek
    wk_hash[DateTime.parse(v.to_s).cweek] << v
  end

  wk_hash.each do |k,v|
    wk_arry << v.size
  end

  row_initial = 4
  row_final = 0
  wk_arry.each_with_index do |v,i|
    row_final = row_initial+((v.to_i*2)-1)
    sheet1.merge_cells(0, row_initial, 0, row_final)
    row_initial = (row_final+3)
  end

  #row0 week number added here after four empty places
  wk_hash.each do |k,v|
    size = v.size
    #pp "---------Week Number for initial Column---"
    wk_name << "WEEK "+k.to_s
    ((size*2)+1).times {wk_name << " "}
  end

  sheet1.insert_row(0,wk_name)
  sheet1.row(0).height = 30
  format1 = Spreadsheet::Format.new ({:weight=> :bold, :pattern_fg_color => :grey, :pattern => 2, :size=> 12, :horizontal_align => :center,:text_wrap => true}) #
  col_initial = 0  
  wk_name.size.times do
    sheet1.row(0).set_format(col_initial,format1)
    col_initial +=1
  end

  #Row1 i.e. Column class name is added here
  default_col1 = ['FirstName','NickName','StudentID','Total Minutes']
  week_num_array = []
  week_num_array << DateTime.parse(@temp_hash1.keys.uniq.first.to_s).cweek

  @temp_hash1.keys.uniq.each_with_index do|v,i|

    wk_no =  DateTime.parse(v.to_s).cweek
    "Previous Array"
    week_num_array
    week_num_array.last == wk_no ? "Week num & Array are equal": default_col1 << " " << " "
    "After push operation Array is"         
    week_num_array << wk_no

    default_col1 << @temp_hash1[v][:name]+" "+@temp_hash1[v][:started_at].strftime("%Y-%b-%d")+" "+@temp_hash1[v][:duration].to_s+"hrs"
    default_col1 << " "
    "--------Unique Week Ends Here----------"    
  end

  default_col1 << " "<< " "
  week_num_array.uniq.each_with_index do |v,i|
    default_col1 << "Week-"+v.to_s+" Attendance"
  end

  default_col1 << " "<< " "
  month_num_array = @student_monthly_attn_weeks.uniq.flatten.compact 
  month_num_array.each_with_index do |v,i|

    default_col1 <<  "Month-"+v.to_s+" Attendance"
  end
   default_col1 <<  " " << "Total Attendance"

  row1_initial = 4
  row1_final = 5
  1.step(merge_size,1) do
    sheet1.merge_cells(1, row1_initial, 1, row1_final)
    row1_initial +=2 ; row1_final +=2
  end

  sheet1.insert_row(1,default_col1)
  sheet1.row(1).height = 120
  format = Spreadsheet::Format.new ({:weight=> :bold, :pattern_fg_color => :silver, :pattern => 2, :size=> 10.5, :horizontal_align => :center, :rotation=> 90, :border_color=> :blue,:text_wrap => true}) #
  cell_initial = 0  
  default_col1.size.times do
    sheet1.row(1).set_format(cell_initial,format)
    cell_initial +=1
  end

  counter = 0
  size = @final_array.size - 1
  format1 = Spreadsheet::Format.new ({:pattern_fg_color => :red, :pattern => 2,:horizontal_align => :center})
  @final_array.each_with_index do|v,i|
    arry1 = []
    counter +=1

    v.each_with_index do |v,i| 
      if (v.is_a?(String) && (v.include? 'ab'))
        arry1 << i
      end
    end

    sheet1.insert_row(counter+1, @final_array[i].flatten)
    arry1.each_with_index do |v,i|
      counter+1
      sheet1.row(counter+1).set_format(v,format1)
    end

  end 
  #t = Time.now.strftime('%H:%M:%S')
  send_file = excel_file.write("/home/user/Code/files/excel/Attendance.xls")
  send_file
end

And I zip the file like this in my controller

     files = Dir.glob("#{Rails.root}/files/excel/*.xls")
     @original_file = File.basename(files.first)
     @original_path = "#{Rails.root}/files/excel"

     Zip:: File.open(@filename, Zip::File::CREATE) do |z|
    z.add(@original_file,@original_path+'/'+@original_file)
    z.get_output_stream("Other details.txt") { |os| os.write "This is a system generated excel sheet" }
  end
send_file @filename, :type => 'application/zip', :filename => "Attendance.zip", :stream=> false, :x_sendfile=>true

This approach works very well when I download the zip file with by Ubuntu desktop and read the xls file with LibreOffice. The zip is extracted properly without any issues and the xls sheet is read without any warnings for corruption.

But when I download the same zip file with Windows 7 and unzip with File explorer, the xls file when opened with MS Excel the "Error 0x80070057: The parameters are incorrect" error pops up with the below dialog

Excel found unreadable content in 'Attendance.xls'. Do you want to recover the contents of this workbook? If you trust the source of this Workbook, click Yes

Also when the xls files are uploaded as attachments to Gmail, it is being tagged as virus and is deleted.

This happens only for the xls files which are greater than 140kB in size created by Spreadsheet gem

I have attached the zip file that includes the xls

Environment:-

Production

Ubuntu 14.04 server
rvm 1.26.11
Ruby 2.2.2

Development

Ubuntu 14.04
rvm 1.26.11
Ruby 2.1.6

Gems version used:-

Production

rubyzip (1.1.7)
spreadsheet (1.0.7)
rubygems-bundler (1.4.4)
ruby-ole (1.2.11.8)

Development

rubyzip (1.1.7)
spreadsheet (1.0.7)
rubygems-update (2.4.8)
ruby-ole (1.2.11.8)

The mime type for above xls file created using Spreadsheet Gem is

application/CDFV2-corrupt; charset=binary

[zdavatz]

Well my guess is, the zipping causes this error. What happens if you do not zip the file?

[zdavatz]

what OS are you using?

[yugendran]

The OS is Ubuntu 14.04 server. The zipping doesn't cause the corruption, this has been tested without zipping and the corruption still existed. Also I have updated the issue thread with complete environment.

[zdavatz]

Ok, please supply me with a Ruby Script (that creates a mentioned XLS file) with no Rails dependency whatsoever. Upload the script to gist.github.com and link it here.