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 http://www.ruby-forum.com/topic/664248#968592 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",
  '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",
  '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