3

I know what you're thinking, just download the data as a CSV and import into Excel.

Unfortunately what we require is for a user to choose some data from a Rails app then easily (i.e. with as little user intervention as possible - ideally a single button press) export it, and open it in an existing Excel spreadsheet that contains various formulas and macros, etc, which will be applied to the data.

My ideal solution would involve most of the heavy lifting at the Rails end, as that's where my skills lie, but I guess there could be some sort of Excel web integration stuff that might help.

Anyway, can I have some recommendations on good approaches to this?

Improve this question

3 Answers 3

Reset to default
4

axlsx is probably the most complete implementation for deep excel work. https://github.com/randym/axlsx

Here is an example that builds one of microsoft's skydrive templates: enter image description here

require 'axlsx' Axlsx::Package.new do |p| p.workbook do |wb| styles = wb.styles header = styles.add_style :bg_color => "DD", :sz => 16, :b => true, :alignment => {:horizontal => :center} tbl_header = styles.add_style :b => true, :alignment => { :horizontal => :center } ind_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => {:indent => 1} col_header = styles.add_style :bg_color => "FFDFDEDF", :b => true, :alignment => { :horizontal => :center } label = styles.add_style :alignment => { :indent => 1 } money = styles.add_style :num_fmt => 5 t_label = styles.add_style :b => true, :bg_color => "FFDFDEDF" t_money = styles.add_style :b => true, :num_fmt => 5, :bg_color => "FFDFDEDF" wb.add_worksheet do |sheet| sheet.add_row sheet.add_row [nil, "College Budget"], :style => [nil, header] sheet.add_row sheet.add_row [nil, "What's coming in this month.", nil, nil, "How am I doing"], :style => tbl_header sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] sheet.add_row [nil, "Estimated monthly net income", 500, nil, "Monthly income", "=C9"], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Financial aid", 100, nil, "Monthly expenses", "=C27"], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Allowance from mom & dad", 20000, nil, "Semester expenses", "=F19"], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Total", "=SUM(C6:C8)", nil, "Difference", "=F6 - SUM(F7:F8)"], :style => [nil, t_label, t_money, nil, t_label, t_money] sheet.add_row sheet.add_row [nil, "What's going out this month.", nil, nil, "Semester Costs"], :style => tbl_header sheet.add_row [nil, "Item", "Amount", nil, "Item", "Amount"], :style => [nil, ind_header, col_header, nil, ind_header, col_header] sheet.add_row [nil, "Rent", 650, nil, "Tuition", 200], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Utilities", 120, nil, "Lab fees", 50], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Cell phone", 100, nil, "Other fees", 10], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Groceries", 75, nil, "Books", 150], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Auto expenses", 0, nil, "Deposits", 0], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Student loans", 0, nil, "Transportation", 30], :style => [nil, label, money, nil, label, money] sheet.add_row [nil, "Other loans", 350, nil, "Total", "=SUM(F13:F18)"], :style => [nil, label, money, nil, t_label, t_money] sheet.add_row [nil, "Credit cards", 450], :style => [nil, label, money] sheet.add_row [nil, "Insurance", 0], :style => [nil, label, money] sheet.add_row [nil, "Laundry", 10], :style => [nil, label, money] sheet.add_row [nil, "Haircuts", 0], :style => [nil, label, money] sheet.add_row [nil, "Medical expenses", 0], :style => [nil, label, money] sheet.add_row [nil, "Entertainment", 500], :style => [nil, label, money] sheet.add_row [nil, "Miscellaneous", 0], :style => [nil, label, money] sheet.add_row [nil, "Total", "=SUM(C13:C26)"], :style => [nil, t_label, t_money] sheet.add_chart(Axlsx::Pie3DChart) do |chart| chart.title = sheet["B11"] chart.add_series :data => sheet["C13:C26"], :labels => sheet["B13:B26"] chart.start_at 7, 2 chart.end_at 12, 15 end sheet.add_chart(Axlsx::Bar3DChart, :barDir => :col) do |chart| chart.title = sheet["E11"] chart.add_series :labels => sheet["E13:E18"], :data => sheet["F13:F18"] chart.start_at 7, 16 chart.end_at 12, 31 end sheet.merged_cells.concat ["B4:C4","E4:F4","B11:C11","E11:F11","B2:F2"] sheet.column_widths 2, nil, nil, 2, nil, nil, 2 end end p.use_shared_strings = true p.serialize 'axlsx.xlsx' end
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

The export part is reasonably well covered at least - here's a couple of candidate gems:

I suspect (hope!) that once you have your data in XLS form, the rest should be plumbing on the client side.

Is there any value in looking at Excel's Web Query facility? If the user can define their selection criteria such that change is infrequent, then a custom page (perhaps just delivering a table) could drop the data straight into their Excel session. Just a thought...

Improve this answer

1 Comment

Thanks - these were the lines I was thinking along. Unfortunately by definition the data will be different on each request. So in some way the Excel web query bit would need to interact with the Web UI bit where they choose the data.
0

Ryan Bates just released a free short screencast at railscasts.com on Exporting CSV and Excel

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.