Hello, I’m developing an app that basically, receives a 10MB or less
XLSX
files with +30000 rows or so, and another XLSX file with about 200rows,
I
have to read one row of the smallest file, look it up on the largest
file
and write data from both files to a new one.
I just did a test reading all the rows from the largest file using ROO
(Spreadsheet doesn’t support XSLX and Creek look good but I can’t find a
way to read row by row) and write it to a XLS file (no comparisons or
anything) and it basically made my computer crash, the server crashed, I
tried rebooting it and it said It was already started, anyway, it was a
disaster.
So, my question was, is there gem that works best with large XLSX files
or
is there another way to approach this withouth crashing my computer?
This is what I had (It’s very possible I’m doing it wrong, help is
welcome)
What i was trying to do here, was to process the files and create the
new
XLS file after both of the XLSX files were uploaded:
require ‘roo’
require ‘spreadsheet’
class UploadFiles < ActiveRecord::Base
after_commit :process_files
attr_accessible :inventory, :material_list
has_one :inventory
has_one :material_list
has_attached_file :inventory, :url=>"/:current_user/inventory",
:path=>":rails_root/tmp/users/uploaded_files/inventory/inventory.:extension"
has_attached_file :material_list,
:url=>"/:current_user/material_list",
:path=>":rails_root/tmp/users/uploaded_files/material_list/material_list.:extension"
validates_attachment_presence :material_list
accepts_nested_attributes_for :material_list, :allow_destroy => true
accepts_nested_attributes_for :inventory, :allow_destroy => true
validates_attachment_content_type :inventory, :content_type =>
[“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”],
:message => “Only .XSLX files are accepted as Inventory”
validates_attachment_content_type :material_list, :content_type =>
[“application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”],
:message => “Only .XSLX files are accepted as Material List”
def process_files
inventory = Roo::Excelx.new(Rails.root.to_s +
“/tmp/users/uploaded_files/inventory/inventory.xlsx”)
material_list = Roo::Excelx.new(Rails.root.to_s +
“/tmp/users/uploaded_files/material_list/material_list.xlsx”)
inventory.default_sheet = inventory.sheets.first
scl = Spreadsheet::Workbook.new
sheet1 = scl.create_worksheet
sheet1.row(1).concat %w{ProjectCode ProjectName ContractNo Item Qty
ItemDescription}
2.upto(5) do |line|
@projectCode = inventory.cell(line,‘B’)
@ProjectName = inventory.cell(line,‘C’)
@ContractNo = inventory.cell(line,‘D’)
@Item = inventory.cell(line,‘N’)
@AviQty = inventory.cell(line,‘Q’)
@ItemDescription = inventory.cell(line,‘AA’)
sheet1.row(line).push(@projectCode, @ProjectName, @ContractNo,
@Item,
@AviQty, @ItemDescription)
end
scl.write(Rails.root.to_s +
“/tmp/users/generated/siteconfigurationlist.xls”)
end
end
And it works, but only for small files. If i upload the real files and
not
the test ones, the browser stays “Waiting for localhost” the server
console
doesn’t show any errors, and the output file doesn’t seem to save at
all.
The output is never created.