0.4.0 generates file but can't be opened

[rsmithlal]

I'm working with an extension of Spree 2.3. The method that generates the excel report is as follows:

def order_report
        export = !params[:q].nil?

        params[:q] ||= {}
        @show_only_completed = params[:q][:completed_at_not_null] == '1'
        params[:q][:s] ||= @show_only_completed ? 'completed_at desc' : 'created_at desc'

        # As date params are deleted if @show_only_completed, store
        # the original date so we can restore them into the params
        # after the search
        created_at_gt = params[:q][:created_at_gt]
        created_at_lt = params[:q][:created_at_lt]

        params[:q].delete(:inventory_units_shipment_id_null) if params[:q][:inventory_units_shipment_id_null] == "0"

        if !params[:q][:created_at_gt].blank?
          params[:q][:created_at_gt] = Time.zone.parse(params[:q][:created_at_gt]).beginning_of_day rescue ""
        end

        if !params[:q][:created_at_lt].blank?
          params[:q][:created_at_lt] = Time.zone.parse(params[:q][:created_at_lt]).end_of_day rescue ""
        end

        # Get the search parameters
        @search = Order.accessible_by(current_ability, :index).ransack(params[:q])

        # Restore dates
        params[:q][:created_at_gt] = created_at_gt
        params[:q][:created_at_lt] = created_at_lt
        render and return unless export

        @orders = @search.result(distinct: true) # TODO - make this a batch fetch

        # Put the orders into an Excel file
        render xlsx: "order_report", disposition: 'attachment', filename: 'order_report_' + Date.today.to_s + '.xlsx', xlsx_created_at: 0.days.ago

end

my template is called "order_report.xlsx.axlsx" and contains:

wb = xlsx_package.workbook

wb.add_worksheet(name: "Orders") do |sheet|

    titles =     [
        'Updated At',
        'Completed',
        'Order Number',
        'Name',
        'Address',
        'Phone',
        'Email',
        'Item',
        'url',
        'Quantity',
        'Order Total',
        'Status',
        'Shipping Status'
    ]

    sheet.add_row titles

    @orders.each do |order|
        first_row = true
        if order.line_items.any?
        order.line_items.each do |line_item|
            row = []

            row << (first_row ? order.updated_at : "")
            row << (first_row ? order.completed_at : "")
            row << (first_row ? order.number : "")
            if order.bill_address && first_row
                row << order.bill_address.full_name
                address_line = ""
                address_line << order.bill_address.address1 + " " if order.bill_address.address1?
                address_line << order.bill_address.address2 + " " if order.bill_address.address2?
                address_line << order.bill_address.city + " " if order.bill_address.city?
                address_line << order.bill_address.country.name + " " if order.bill_address.country_id?
                row << address_line
                row << (order.bill_address.phone? ? order.bill_address.phone : "")
            else
                row << ""
                row << ""
                row << ""
            end
            row << (first_row ? (order.email || "") : "")
            item = line_item.variant.name
            if line_item.product.individual_sale
                if line_item.material
                    item += " - " + line_item.material.name
                    item += " (" + line_item.material.options_text.gsub('&nbsp;', '') + ")" unless !line_item.material && line_item.material.option_values.empty?
                end
                if line_item.frame
                    item += " " + line_item.frame.name
                    item += " (" + line_item.frame.options_text.gsub('&nbsp;', '') + ")" unless line_item.frame.option_values.empty?
                end
                row << item 

                if line_item.product.photo_info
                    row << line_item.product.photo_info.image.url(:original)
                elsif (line_item.photo_src == 'bigstock') && !line_item.photo_key.blank?
                    if line_item.purchased_image
                        row << Bigstock::PurchasedImage.download_url(line_item.purchased_image.key)
                    else
                        row << "Bigstock image not purchased"
                    end
                elsif (line_item.photo_src == 'usrimg') && !line_item.photo_key.blank?
                    photo = UserPhoto.find_by_photo_hash line_item.photo_key
                    row << photo.photo.url(:original)
                else
                    row << (line_item.variant.images[0].attachment.url(:original)).to_s
                end
            else
                item += " (" + line_item.variant.options_text.gsub('&nbsp;', '') + ")" 
                row << item
                row << "" # cause there is no URL
            end

            row << line_item.quantity
            row << (first_row ? order.total.to_s : "")
            row << (first_row ? order.state.downcase.to_s : "")
            row << (first_row ? order.shipment_state.to_s : "")
            first_row = false
            sheet.add_row row
        end
        end
    end

end

The sheet generated using 0.3.0 using this exact code can be viewed at https://docs.google.com/a/grenadinetech.com/spreadsheets/d/17qV3gC23V-JR255_peZauy6WUBf1Ur5UJeUVD3tGPX8/edit?usp=sharing

I've had to version-lock the gem in order to continue developing the template on our extension. Any thoughts about what could have changed between versions to cause this error? Need any additional information?

[straydogstudio]

What is the full error message?

[rsmithlal]

In google spreadsheets: "Sorry, an error occurred when opening this file. Please try again." In Numbers: "“order_report.xlsx” can’t be opened." I don't get any error messages on generation.

[straydogstudio]

Try adding formats: [:xlsx]

What is the request format? Before 0.4.0 axlsx_rails would force the formats array to include xlsx. But in Rails 4.2 this array is cached, so it caused trouble everywhere else, since the default format array had been changed. I dropped that behavior. It requires that the request format come across with xlsx. This is normal behavior. Meaning, if you request /foo/bar.html and serve an xlsx file it won't work unless you force the format. The option above should force it for the one render.

[straydogstudio]

That formats parameter is for Rails 4. Which Rails are you using?

[rsmithlal]

4.1.2

[straydogstudio]

Oops, formats can be used as of Rails 3.2.3.

[rsmithlal]

What do you mean by "What is the request format?"? I posted the whole method used to generate the report. It's not using

respond_to do |format|
format.xlsx

[rsmithlal]

Where should I add formats: [:xlsx] ?

[straydogstudio]

You're right about what is the request format. So there is no issue of it not being xlsx.

Pass the formats parameter to the render command. Let me know if it changes anything.

So the only change from working to not working is going form axlsx_rails 0.3.0 to 0.4.0? Any other changes in the Gemfile?

[straydogstudio]

@rsmithlal Did that parameter change anything?

[rsmithlal]

I haven't had a chance to test it out with 0.4.0 since we first spoke about it, haven't been able to put any dev time into looking into the issue as 0.3.0 works well for us at the moment. When I get an opportunity, I'll test it out and let you know so you can update the docs.

[rsmithlal]

However, I can confirm that the only change to the gemfile was the version change for this gem.

[straydogstudio]

@rsmithlal Please let me know what rubyzip you have in your Gemfile.lock. Try fixing rubyzip on 1.1.0. Rubyzip seems to cause most of the problems.

[phyzalis]

As I was having the same issue, I tried what you suggested about updating rubyzip version but I am getting this error with bundle update:

Bundler could not find compatible versions for gem "rubyzip":
  In Gemfile:
    axlsx_rails (>= 0) ruby depends on
      axlsx (>= 2.0.1) ruby depends on
        rubyzip (~> 1.0.0) ruby

    rubyzip (~> 1.1.0) ruby

[phyzalis]

Ok I managed to update rubyzip by adding this into my Gemfile

gem 'axlsx', git: "git@github.com:randym/axlsx.git"

But even with rubyzip 1.1.7 I am still not able to open a xlsx generated file

[phyzalis]

Ok I found my issue. I was having a layout for another format which was happened to the generated xlsx file. Setting layout: false to my render call made it work. Maybe this could help someone sometime

[straydogstudio]

@phyzalis Thanks for the info. Would you post a gist of your controller code, and what versions of Rails, axlsx, axlsx_rails, rubyzip you are using? I've seen this before and will try to find out what is going on. It shouldn't be trying to use a layout.

[straydogstudio]

I have not been able to reproduce this bug. But, based on the experience of others (here and in other issues) the latest release of axlsx_rails (0.5.0) explicitly sets layout to false. Advising others to set the layout to false appears to fix these issues. I am cautiously optimistic. Please try 0.5.0 and comment on this issue (or open another) if the failure happens again.