CODEWITHSUNDEEP
ruby-on-railsexcelencodingcharacter
pinkfloyd90
pinkfloyd90

Reputation: 688

How to fix character encoding in a Rails exported Excel (.xls) file?

I'm working with a controller that exports a .xls file with several fields that contain translated text in Spanish.

Character encoding seems off, as I get words like Débito Automático instead of Débito Automático all over the file.

I think excel defaults to iso-8859-1 character encoding but I don't know how to change or specify that from the Rails controller.

def show
    ...
      format.xls do
        @enriched_invoices = @enriched_invoices.includes(user: { address: :city })
        end
      end
    end
  end

html that responds to .xls format:

<table border="1">
    <tr>
        <th>Fecha Factura</th>
        <th>ID Cliente</th>
        <th>user_external_reference</th>
        <th>Cliente</th>
        <th>Domicilio</th>
        <th>Código Postal</th>
        <th>Ciudad</th>
        <th>DNI</th>
        <th>Email</th>
        <th>ID Factura</th>
        <th>Motivo</th>
        <th>Descripción</th>
    <th>Estado</th>
    <th>Importe del Recibo</th>
    <th>Metodo de pago</th>
    <th>Estado Ultimo Pago</th>
    <th>Descripcion estado Ultimo Pago</th>
    <th>Fecha de creación del Pago</th>
    <th>Fecha de aprobación del Pago</th>
    <th>Monto del pago</th>
    <th>Forma de pago</th>
    </tr>
    <% @enriched_invoices.each do |invoice| %>
        <tr>
            <td><%= invoice.due_date.strftime("%Y-%m-%d") %></td>
            <td><%= invoice.user_id %></td>
            <td><%= invoice.user.external_reference %></td>
            <td><%= invoice.user.full_name %></td>
            <td><%= invoice.user.address&.full_address %></td>
            <td><%= invoice.user.address&.cp %></td>
            <td><%= invoice.user.address&.city_name.presence || invoice.user.address&.city&.name %></td>
            <td><%= invoice.user.doc_number %></td>
            <td><%= invoice.user.email %></td>
            <td><%= invoice.id %></td>
      <td><%= invoice.subscription? ? 'Subscription' : invoice.invoiceable_type %></td>
            <td><%= invoice.invoice_items.order(:id).pluck(:description).join('. ') %></td>
      <td><%= invoice.status %></td>
      <td><%= humanized_money_with_symbol invoice.amount %></td>
      <td><%= invoice.payment_method %></td>
      <td><%= invoice.last_payment_status %> </td>
      <td><%= invoice.last_payment_status_detail %> </td>
      <td><%= invoice.last_payment_date_created&.strftime("%Y-%m-%d") %> </td>
      <td><%= invoice.last_payment_date_approved&.strftime("%Y-%m-%d") %> </td>
      <td><%= humanized_money_with_symbol(Money.new(invoice.last_payment_amount_cents, invoice.amount.currency)) %></td>
      <td><%= invoice.last_payment_payment_type %></td>
        </tr>
    <% end %>
</table>

Is there a way to do it without a gem?

Upvotes: 0

Views: 299

Answers (1)

rewritten
rewritten

Reputation: 16435

You might be able to solve it in one of these methods (by increased likelihood of working)

  1. Use a specific layout for your response, that explicitly declares the content to be in UTF-8. There is no guarantee that Excel will oblige, and there is zero documentation about it anyways.

  2. Force your rendering pipeline to use specific options for :xls format. Check https://api.rubyonrails.org/classes/ActionController/Renderers.html#method-c-add

  3. Expose your data as CSV instead (again no guarantee that excel parses it correctly)

  4. Use html entities instead fo utf-8 byte sequences: "Descripci&oacute;n" or also use https://rubygems.org/gems/htmlentities and HTMLEntities.new.encode(string, :named) if you have content in code (instead of in templates)

Upvotes: 1

Related Questions