Repository files navigation

Small gem for generatingreal Excel spreadsheets from existing CSV data andHTML tables that fully supports UTF-8 characters.

Well, when you're starting up and things are small and then things grow andthen people want reports and they'd like them in Excel most of the time andthen you realize you can just throw some cheap CSV views at them and Excelwill be fine with them and then you get even bigger and you start getting,like, friggin'international and then the French dudes are like, "My umlautslook like chewed croissant" and you try prefixing your CSV with a UTF-8 BOMand that makes some Excels happy (Windows) but not others (Mac) and you thinkman I totally want to just, y'know,eat the croissant like it was meant tobe eaten y'know? and there's a zillion spreadsheet gems out there alreadyexcept they all like merge back to that one spreadsheet gem, and you could goXMLDoc maybe, but then it's just like lemme use the CSV I've already got andtry to not make me work much?

The heart of this gem has a couple of small methods to handle thetransformations. If that's all you need, you're good to go.

###CSV

CallExcelinator.csv_to_xls(csv_content). The csv_content will be parsed byFasterCSV and converted to Excel spreadsheet contents ready to be saved tofile or sent across the wire.

If you have a lot of CSV content and don't want to do all of this work inmemory, callExcelinator.csv_to_xls_file(csv_path, file), passing the path tothe CSV file and a path to the .xls file you'd like the workbook saved to.(contributed bymaxwell)

If you want the CSV Data not to be separated by ',' (as it is by default) you can callExcelinator.csv_to_xls_file(csv_path, file, ";") orExcelinator.csv_to_xls(csv_content, ";") or with any other separator char.

###HTML

CallExcelinator.html_as_xls(html_content). The table element from the HTMLcontent is extracted, a meta tag indicating utf-8 encoding is prepended andthat's it. The resulting content isn't actually an Excel spreadsheet, just theHTML data. But write this out to a file with an .xls extension and Excel willopen the contents and translate the<table> for you, formatting and all.

NOTE: While some spreadsheet programs (e.g. Google Docs) will not translateHTML tables like this, both Excel on Windows and Mac will as well asOpenOffice.

As you wish. As always,TMTOWTDI,but here are a few usage options. All examples work in Rails 2 and 3, exceptwhere noted.

If you want to make an explicit xls view that has CSV content in it:

classFooController <ApplicationControllerdefreportrespond_todo |format|format.htmlformat.csvformat.xls{render:xls=>'foo_report.xls'}endendend

Rails 2 doesn't support custom renderers, but the guts of our :xls rendererare mixed into the controllers, so you can call it directly this way:

format.xls{send_xls_data'foo_report.xls'}

If you want to re-use an existing HTML view:

format.xls{send_xls_data'foo_report.xls',:file=>'foo/report.html.erb'}

:template also works in place of:file in Rails 3.render :xls => alsoworks in place ofsend_xls_data in Rails 3.

Also note,send_xls_data (the guts ofrender :xls) will parse the givencontent and detect CSV or HTML, so no need to specify which is being passed in.

You can even go with just an explicit xls view and no controller code, butyou'll need to convert the CSV content yourself inside the view:

<%=Excelinator.csv_to_xls(render:file=>'foo/xls_view.csv.erb').html_safe%>

:template works in place of:file here as well in Rails 3.

Or ... refactor the CSV content to a format-less partial:

# _report.erb<%=generate_csv_report%># report.csv.erb<%= render :partial =>'report'%># report.xls.erb<%= Excelinator.csv_to_xls(render :partial =>'report').html_safe %>

There are test apps included in the source repo that exercise these differentoptions.

###You lied when you said "real Excel spreadsheets from ... HTML tables." What about converting HTML tables to areal Excel file?

I did, and I apologize. Lemme know when your pull request is ready.

###Are there any options to re-use CSV/HTML views with No additional controller/view code?

I've tinkered with it, but it requires a bit of duck punching of theRails rendering code. Checkoutexceliderp and see if I'vepushed it up there yet.

###What if I want to generate a real Excel spreadsheet from scratch with all sorts of awesome in it?

This gem usesspreadsheet under the covers. There are also others thatsupport a wide variety of Excel features:

• https://github.com/zdavatz/spreadsheet
• http://surpass.ananelson.com/
• https://github.com/randym/axlsx

With any of these, you can create specific .xls views and have them use theclasses in these gems that let you define a Workbook with multiple Worksheetswith rows and columns of formatted formulas.

For support higher up the ladder within Rails and/or ActiveRecord, here are afew options I've found, though I can't vouch for any. Search rubygems andgithub for 'spreadsheet' 'excel' and 'xls' and you'll find lots of additionalprojects. Most appear to use either the above Spreadsheet gem or generateXMLDoc.

• https://github.com/glebm/to_spreadsheet
• https://github.com/splendeo/to_xls
• https://github.com/asanghi/excel_rails
• https://github.com/hallelujah/rails-excel
• https://github.com/liangwenke/to_xls-rails

###Some of the links in the test Rails apps don't work

They're not all supposed to work. Think of it more as a workshop for examplecode.

Merged PRs (effectively and actually) from ikusei and maxwell to remove the versioning onthe dependentspreadsheet gem and allow for a custom separator.

Addedcsv_to_xls_file(csv_path, file) (contributed bymaxwell)

Ruby 2 support

Added Ruby 1.9 support.