Exportando dados para Excel usando CSV em um aplicativo Rails

To see this post in English, click here.

Às vezes os usuários querem manipular os dados de diversos modos. Nesses cenários, é comum exportar os dados num formato de tabela para que se possa usar um editor de planilhas e então filtrar, particionar e mudar os dados da forma que o usuário quiser.

Generalmente, fazemos isso usando CSV, certo? O OpenOffice e outros editores podem abrir arquivos CSV sem problemas. É só clicar duas vezes no arquivo CSV e voilà, os dados aparecem.

Entretanto, o Excel não funciona exatamente da mesma forma. Se você clicar duas vezes no arquivo, o Excel vai lhe mostrar todo o conteúdo em apenas uma coluna. Claro que você poderia ir em Ferramentas > Importar dados, navegar até o arquivo, selecionar a vírgula como separador de campos e então, após alguns cliques você terá seus dados como desejado. Isso não é nada amigável para o usuário e é o tipo de coisa que não queremos explicar para nossos clientes. Nossos clientes devem ser capazes de simplemente clicar duas vezes no arquivo meus_dados.csv e ver os dados bem estruturados.

Tendo isso em mente, é assim que fazemos na Plataforma para lidar com os formatos do Excel.

É TSV, e não CSV, rapaz!

O Excel espera que seus dados venham com tabulações como separador de campo. Então, o que você precisa na verdade é um TSV (tab-separated values).

Se você está usando o FasterCSV, só precisa fazer:

tsv_str = FasterCSV.generate(:col_sep => "\t") do |tsv|
  tsv << headers
  # coloque seus bonitos dados aqui...
end

Não esqueça! Excel exige tabulações, e não vírgulas. Mas tem coisa pior vindo...

Sem quebras de linha nos campos

O Excel não gosta quando você põe um "\n" dentro dos campos. Apesar dos campos serem separados por tabulações, ele parece não entender o que a quebra de linha está fazendo ali.

Então, se você tem campos de texto no seu modelo, tome cuidado. Eles podem conter quebras de linhas e você precisa tirá-las antes de exportar seus dados para o Excel.

Esqueça UTF-8. Use UTF-16!

Uma das especificações mais escondidas do Excel é que ele espera que seus arquivos TSV sejam codificados usando UTF-16 Little Endian. Você sabia disso? Bem, nós não!

Alguns até dizem que este é o único formato Unicode suportado pelo Excel.

E qual a diferença entre UTF-8 e UTF-16? O UTF-8 é uma codificação de tamanho variável, cujos caracteres podem usar até 4 bytes, mas para idiomas ocidentais generalmente é usado um ou dois bytes. Caracteres UTF-16 usam sempre 2 bytes para serem representados (lembre-se que caracteres e bytes são duas coisas bem diferentes no mundo Unicode). Simplificando bastante as coisas, UTF-16 usa mais espaço (na maioria das vezes).

E tem também a parte do Little Endian. O UTF-16 sempre usa um par de bytes para representar um caractere. Contudo, nós precisamos saber qual a ordem certa desses bytes. Não vamos entrar em detalhes aqui, mas essa ordem é indicada pelo Byte-order Mark (BOM). Na prática, o BOM no UTF-16 vai adicionar dois bytes no começo do seu arquivo (você pode ver isso usando um editor hexadecimal).

Um dos jeitos de converter sua string TSV (gerado pelo FasterCSV) é com o Iconv, uma ferramenta escrita para a Biblioteca C do GNU. Felizmente, o Iconv está bem empacotado em pura felicidade Rubyana. Está dentro do biblioteca padrão do Ruby e vocÊ só precisar dar um require nela.

Entretando, se você converter uma string para UTF-16 Little Endiand, Iconv NÃO irá colocar o BOM no começo. Isso é compatível com a especificação do Unicode. Mas já que o Excel está totalmente fora dos padrões, você deve inserir manualmente o BOM para aumentar a compatibilidade.

Você pode usar o Iconv na hora que enviar o arquivo ao usuário dentro do seu controller. Ficaria mais ou menos assim:

require 'iconv'

class ProjectsController < ActionController::Base
  BOM = "\377\376" #Byte Order Mark

  def index
    @projects = Project.all
    respond_to do |format|
      format.html
      format.csv { export_csv(@projects) }
    end
  end

protected

  def export_csv(projects)
    filename = I18n.l(Time.now, :format => :short) + "- Projects.csv"
    content = Project.to_csv(projects)
    content = BOM + Iconv.conv("utf-16le", "utf-8", content)
    send_data content, :filename => filename
  end
end

Perceba que estamos usando CSV como extensão do arquivo. Isso é porque geralmente arquivos TSV não estão associados ao Excel. Este trecho usa um timestamp como nome de arquivo, o que é geralmente uma boa prática.

Resumindo

Essas são as 3 leis para lidar com CSVs para o Excel:

  1. Use tabulações, e não vírgulas.
  2. Campos NÃO podem conter quebras de linha.
  3. Use UTF-16 Little Endian para enviar o arquivo ao usuário. E adicione o BOM do Little Endian manualmente.

Tenha isso em mente e você nunca terá que explicar para seus clientes como abrir os dados exportados no Excel.

Há um último problema. O OpenOffice não irá abrir facilmente arquivos feitos na "especificação" do Excel. O Google Analytics resolve este problema mostrando dois links ao usuário: "Exportar para CSV" e "Exportar para Excel". O primeiro é o CSV normal e o segundo é o arquivo TSV feito especialmente para o Excel.

E culpe a Microsoft por este comportamento estranho.

UPDATE: @danielvlopes nos avisou de uma solução existente que encapsula o processo deste post, chamado csv_builder. Você só precisa configurar o @output_encoding para usar "utf-16" (preste atenção com o BOM).

@jncoward também enviou um link para a gem spreadsheet, que escreve formatos nativos do Excel. TSV é um formato mais simples e mais rápido, mas a gem spreadsheet pode lhe ajudar em casos mais complexos.

  • http://blog.seatecnologia.com.br Túlio

    Parabéns pelo blog, o excel realmente é um pé no saco…
    Só uma correção, você colocou stylesheet ao invés de spreadsheet no final, na dica do @jncoward.
    o/

  • http://blog.seatecnologia.com.br Túlio

    Parabéns pelo blog, o excel realmente é um pé no saco…
    Só uma correção, você colocou stylesheet ao invés de spreadsheet no final, na dica do @jncoward.
    o/

  • George Guimarães

    Valeu Túlio,

    Já está corrigido. =)

  • George Guimarães

    Valeu Túlio,

    Já está corrigido. =)