Spreadsheet MVC

Rails
1

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

2

On 15 December 2010 12:00, Ron P. [email protected] wrote:

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

3

Colin L. wrote in post #968551:

On 15 December 2010 12:00, Ron P. [email protected] wrote:

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

4

On Wed, Dec 15, 2010 at 8:00 AM, Ron P. [email protected]
wrote:

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.

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.

5

On Wed, Dec 15, 2010 at 10:50 AM, Colin L. [email protected]
wrote:

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. 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.

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

6

Colin L. wrote in post #968580:

On 15 December 2010 14:38, Marnen Laibow-Koser [email protected]
wrote:

Colin L. wrote in post #968551:

On 15 December 2010 12:00, Ron P. [email protected] wrote:

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

Sent from my iPhone

7

On 15 December 2010 15:01, Marnen Laibow-Koser [email protected]
wrote:

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.

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

8

On 15 December 2010 14:38, Marnen Laibow-Koser [email protected]
wrote:

Colin L. wrote in post #968551:

On 15 December 2010 12:00, Ron P. [email protected] wrote:

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. 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

9

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.

10

Please quote when replying.

Ron P. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

11

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 quote when replying.

Ron P. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

12

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

1; In the controller:

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

In the view ‘exports.rxml’

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.
13

Serguei C. wrote in post #968794:

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

1; In the controller:

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

In the view ‘exports.rxml’

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.
>

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

14

Please don’t top-post.

Ron P. 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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]