Spreadsheet MVC

I am using Spreadsheet to produce reports. Is there a Railsy way to put Spreadsheet creation in the Views?

It's easy to put it in the Model, or the Controller, but that just doesn't seem right.

Ron

I am using Spreadsheet to produce reports. Is there a Railsy way to put Spreadsheet creation in the Views?

In what way are you creating a spreadsheet? Do you mean an excel spreadsheet? If so why would you want to create it in the view?

It's easy to put it in the Model, or the Controller, but that just doesn't seem right.

Colin

Colin Law wrote in post #968551:

I am using Spreadsheet to produce reports. Is there a Railsy way to put Spreadsheet creation in the Views?

In what way are you creating a spreadsheet? Do you mean an excel spreadsheet? If so why would you want to create it in the view?

Well, it *is* a presentation of model data...

It's easy to put it in the Model, or the Controller, but that just doesn't seem right.

Colin

Best,

What do you mean by a spreadsheet? (@marnen too) the presentation of a spreadsheet is a simple table, is the functionality that makes it special, it you want an html table with the functionality of a spreadsheet you will need a js library, if you only want the appearance you can google a css.

That rather depends on what the OP is trying to do. If he is writing an excel spreadsheet file then that is no more a view than is the database itself, it is just data stored in a file. If however he is somehow displaying the spreadsheet inline in a web page then I would agree with you. We need more information from the OP wants to do.

Colin

This , and if he is ‘displaying the spreadsheet inline’, what does he means by ‘easy to put it in […] the Controller’?

Colin Law wrote in post #968580:

Colin Law wrote in post #968551:

I am using Spreadsheet to produce reports. Is there a Railsy way to put Spreadsheet creation in the Views?

In what way are you creating a spreadsheet? Do you mean an excel spreadsheet? If so why would you want to create it in the view?

Well, it *is* a presentation of model data...

That rather depends on what the OP is trying to do. If he is writing an excel spreadsheet file then that is no more a view than is the database itself, it is just data stored in a file.

*All* views are just data stored in a file. The point is that -- whether meant for export or direct display -- this is, as far as Rails is concerned, a presentation of model data, and so is arguably a view.

Now, I probably wouldn't actually use a view file to export data to a spreadsheet in Rails; I'd generate the spreadsheet in the controller and have done with it. But that's due to Rails' overly narrow interpretation of what a view is, not to anything fundamental in MVC philosophy.

If however he is somehow displaying the spreadsheet inline in a web page then I would agree with you. We need more information from the OP wants to do.

True for Rails. Not true for generic MVC.

Colin

Best,

One could equally well build a model around the spreadsheet, one could even provide an ActiveRecord interface to the spreadsheet.

In principle, though, I agree with you. If a request of some sort comes in and the response is spreadsheet data going out then it does fit under the View umbrella. If, however, one also has an html response then one effectively has two 'views' generated, in this case I do not think it would be appropriate to generate the spreadsheet file inside the html view generation code, if that is what the OP intended.

Colin

Sorry to be so slow: our spam blocker thinks ruby-forum is evil. I wondered why there was no response!

Marnen and Colin agreed that "if a request of some sort comes in and the response is spreadsheet data going out then it does fit under the View umbrella."

I think that describes what I'm doing. A user is looking at an HTML page with a formatted "report" on it. I want them to be able to download what they're seeing as a spreadsheet with similar data, similarly formatted.

The html and the spreadsheet are both representations of the resource, so I thought putting the code in "Views" was reasonable. As it stands right now, I added a "REPORT_HEADERS" constant and a report_row method to all the models.

The reports controller is currently doing things like:

  def project     @project = Project.find(params[:id])     @report = Report.new("Project Report on: #{@project.project_name}")     @report.append_headers(Project::REPORT_HEADERS)     @report.append_data_row(@project.report_row)     @report.append_headers([''])     @report.append_headers(['Allocations'])     for allocation in @project.allocations       @report.append_headers([allocation.allocation_name], 1)       @report.append_headers(Allocation::REPORT_HEADERS, 1)       @report.append_data_row(allocation.report_row,2)     end     respond_to do |format|       format.html # project.html.erb       format.xml       format.xls {         render :text => @report.to_excel_string         headers['Content-Disposition'] = "attachment; filename=fake.xls"         headers['Cache-Control'] = ''       }#index.xls.builder     end   end

  I hope that's not too long.

Please quote when replying.

Ron Phillips wrote in post #968643:

Sorry to be so slow: our spam blocker thinks ruby-forum is evil.

There's a Web interface you can use.

I wondered why there was no response!

Marnen and Colin agreed that "if a request of some sort comes in and the response is spreadsheet data going out then it does fit under the View umbrella."

I think that describes what I'm doing. A user is looking at an HTML page with a formatted "report" on it. I want them to be able to download what they're seeing as a spreadsheet with similar data, similarly formatted.

Note that providing a spreadsheet file for download is seldom a good idea. Please see Spreadsheet gem character encoding - Rails - Ruby-Forum for some other suggestions.

The html and the spreadsheet are both representations of the resource, so I thought putting the code in "Views" was reasonable.

It might be. Depends on what your spreadsheet view is doing.

As it stands right now, I added a "REPORT_HEADERS" constant and a report_row method to all the models.

What are these for?

The reports controller is currently doing things like:

  def project     @project = Project.find(params[:id])     @report = Report.new("Project Report on: #{@project.project_name}")     @report.append_headers(Project::REPORT_HEADERS)     @report.append_data_row(@project.report_row)     @report.append_headers([''])     @report.append_headers(['Allocations'])     for allocation in @project.allocations       @report.append_headers([allocation.allocation_name], 1)       @report.append_headers(Allocation::REPORT_HEADERS, 1)       @report.append_data_row(allocation.report_row,2)     end     respond_to do |format|       format.html # project.html.erb       format.xml       format.xls {         render :text => @report.to_excel_string         headers['Content-Disposition'] = "attachment; filename=fake.xls"         headers['Cache-Control'] = ''       }#index.xls.builder     end   end

  I hope that's not too long.

It's far too long. All the @report stuff should probably be in a view somewhere. Display text generally does not belong in the controller.

Best,

My personal preference is for CSV, and I am providing xml, too. However, my users want Excel spreadsheets, and my boss is one of my users.

The "REPORT_HEADERS" constant is just a convenience. The report_row method lets me fix things up for Excel display (turn everything to 'ISO-8859-1', handle nulls, substitute human-readable content for _id fields, etc.)

"All the @report stuff should probably be in a view somewhere." Sounds good: that's kind of what I meant when I posted. How do I put it in a view?

erb is a natural choice for putting things in html, but not for creating a spreadsheet representation. It seems clumsy to wrap everything in <%%> for erb to make a spreadsheet. Builder isn't suitable, either, is it?

I guess I could put nearly everything in a helper, but that seems kludgy as well.

Thanks, Ron

Marnen Laibow-Koser wrote in post #968663:

Please don't top-post.

Ron Phillips wrote in post #968689:

My personal preference is for CSV, and I am providing xml, too. However, my users want Excel spreadsheets, and my boss is one of my users.

If CSV will not do the trick, then consider the extended HTML format as I suggested in the other thread.

The "REPORT_HEADERS" constant is just a convenience. The report_row method lets me fix things up for Excel display (turn everything to 'ISO-8859-1', handle nulls, substitute human-readable content for _id fields, etc.)

And what are you using these methods for? It's not clear.

"All the @report stuff should probably be in a view somewhere." Sounds good: that's kind of what I meant when I posted. How do I put it in a view?

erb is a natural choice for putting things in html,

No, it really isn't. Haml works much better for that.

but not for creating a spreadsheet representation. It seems clumsy to wrap everything in <%%> for erb to make a spreadsheet.

How so? You need *some* sort of template language, unless you're forgoing templates altogether.

Builder isn't suitable, either, is it?

Only for XML and HTML.

I guess I could put nearly everything in a helper, but that seems kludgy as well.

What's kludgy about that? What's "everything" in this context?

Usually helpers are a good way to keep display logic out of your views.

Thanks, Ron

Best,

I used the following way to generate an Excel file for reports:

1; In the controller:

[code] def exports     @projects = Project.find(params[:project_ids])     headers['Content-Type'] = "application/vnd.openxmlformats- officedocument.spreadsheetml.sheet.main+xml"     headers['Content-Disposition'] = "inline; filename=\"reports- #{Date.today.to_s}.xls\""     headers['Cache-Control'] = ''

    render :layout => false   end [/code]

In the view 'exports.rxml'

[code] xml.instruct! :xml, :version=>"1.0", :encoding=>"UTF-8"

workbook_xmlns = {   'xmlns' => "urn:schemas-microsoft-com:office:spreadsheet",   'xmlns:o' => "urn:schemas-microsoft-com:office:office",   'xmlns:x' => "urn:schemas-microsoft-com:office:excel",   'xmlns:html' => "http://www.w3.org/TR/REC-html40&quot;,   'xmlns:ss' => "urn:schemas-microsoft-com:office:spreadsheet" }

xml.Workbook(workbook_xmlns) do

  xml.Styles do     xml.Style 'ss:ID' => 's22' do       xml.NumberFormat 'ss:Format' => 'yyyy\-mm\-dd'     end     xml.Style 'ss:ID' => 's21' do       xml.NumberFormat 'ss:Format' => '#,##0.00\ [$€]'     end   end

  xml.DocumentProperties({'xmlns' => "urn:schemas-microsoft- com:office:office"}) do     xml.Author current_user.login     xml.Created Time.now     xml.Company 'UCB'     xml.Version '11.4920'   end

  xml.Worksheet 'ss:Name' => 'Reports' do

    xml.Table do

      # Header       xml.Row do         xml.Cell do           xml.Data "Tax/Non-tax", 'ss:Type' => 'String'         end ... and so on, adding cells, rows wgatever you want.

[/code]

Serguei Cambour wrote in post #968794:

I used the following way to generate an Excel file for reports:

1; In the controller:

[code] def exports     @projects = Project.find(params[:project_ids])     headers['Content-Type'] = "application/vnd.openxmlformats- officedocument.spreadsheetml.sheet.main+xml"     headers['Content-Disposition'] = "inline; filename=\"reports- #{Date.today.to_s}.xls\""     headers['Cache-Control'] = ''

    render :layout => false   end [/code]

In the view 'exports.rxml'

[code] xml.instruct! :xml, :version=>"1.0", :encoding=>"UTF-8"

workbook_xmlns = {   'xmlns' => "urn:schemas-microsoft-com:office:spreadsheet",   'xmlns:o' => "urn:schemas-microsoft-com:office:office",   'xmlns:x' => "urn:schemas-microsoft-com:office:excel",   'xmlns:html' => "http://www.w3.org/TR/REC-html40&quot;,   'xmlns:ss' => "urn:schemas-microsoft-com:office:spreadsheet" }

xml.Workbook(workbook_xmlns) do

  xml.Styles do     xml.Style 'ss:ID' => 's22' do       xml.NumberFormat 'ss:Format' => 'yyyy\-mm\-dd'     end     xml.Style 'ss:ID' => 's21' do       xml.NumberFormat 'ss:Format' => '#,##0.00\ [$]'     end   end

  xml.DocumentProperties({'xmlns' => "urn:schemas-microsoft- com:office:office"}) do     xml.Author current_user.login     xml.Created Time.now     xml.Company 'UCB'     xml.Version '11.4920'   end

  xml.Worksheet 'ss:Name' => 'Reports' do

    xml.Table do

      # Header       xml.Row do         xml.Cell do           xml.Data "Tax/Non-tax", 'ss:Type' => 'String'         end ... and so on, adding cells, rows whatever you want.

[/code]

Thanks, Sergui and Marnen. It looks like providing the xml file with the Excel 'decorations' may be the best way. It's pretty seamless in Excel 2003.

The Spreadsheet gem only accepts text for cell contents, so you end up with a lot of "number stored as text" notifications. I guess if I needed to read/modify an existing Excel document, Spreadsheet might have been my best bet.

For now, though, I guess the MS-xml approach might be best. I will be assimilated. At least I can put the builder code in the views, where I think we all agree it belongs.

Ron

Ron