Generating a CSV File using Velocity

[Tom Bednarz <li...@bednarz.ch>]

Hi,

I try to create a CSV File using Velocity. The tricky thing is handling of
CR/LF. Can anybody help?

My template looks as follows:

$response.setContentType("text/comma-separated-values")
$response.addHeader("content-disposition", "attachement;
filename=report.cvs")

#foreach ($field in $TableHeader)
    $field;
#end
#foreach ($record in $data)
    #foreach($value in $record)
        $value;
    #end
#end

$TableHeader, $data and $record are java.util.Vector objects, $field and
$value are Strings

The output is:


Server;
# of Profiles;
# of Files;
Size in MB;
   ECHNATON;
   91;
   0;
   0;
   MARS;
   53;
   0;
   0;

The correct output should be:

Server;# of Profiles;# of Files;Size in MB;
ECHNATON; 91;0;0;
MARS; 53; 0; 0;


The problem is: CR/LF after every field instead of every record and spaces
where no spaces are.

I remember there was a discussion about this some weeks ago but I could not
find it anymore. I tried the following in the template:

#foreach ($field in $TableHeader)$field;#end
#foreach ($record in $data)
#foreach($value in $record)$value;#end
#end

This outputs everything on ONE single line! Also not what I want!

The following does exactly what I want:

#foreach ($field in $TableHeader)$field;#end

#foreach ($record in $data)
#foreach($value in $record)$value;#end

#end

Is there any way to add CR/LF as I can do it in a C++ program:

printf(%s+"\n", stringvariable);

Thomas


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Generating a CSV File using Velocity (and Excel aware HTML)

[Tom Bednarz <li...@bednarz.ch>]

Hi Bill,

Many thanks for you input. Interesting approach to use HTML as input for
EXCEL. I will look into your samples. I am currently trying to create native
Excel sheets using POI (HSSF). All is experimental for the moment. Once I
tried out the various different possibilities I will decide which one I
choose for a final implementation.

Many thanks again!
Thomas


----- Original Message -----
From: "Bill Burton" <bi...@progress.com>
To: "Velocity Users List" <ve...@jakarta.apache.org>
Sent: Wednesday, May 29, 2002 8:31 AM
Subject: Re: Generating a CSV File using Velocity (and Excel aware HTML)


> Hi Tom,
>
> Since you want this for Excel, you should consider generating this output
> as HTML as an alternative to CSV.  Excel can read HTML tables converting
> them into an appropriate columns and rows.  This also allows specifying a
> header with date and other descriptive information regarding the generated
> data which you can't do with a CSV file.  Other advantages to using HTML
> are that you can control exactly what Excel format you want to be used for
> a cell by specfying an appropriate CSS predefined style.
>
> The easiest way to figure this out is to create a simple version of the
> spreadsheet with one or two rows and then select File, Save as Web
> Page..., "Selection: Sheet" to save as a single HTML file.
>
> Using a modified version of your code below, you would set the headers as:
>   $response.setContentType("application/vnd.ms-excel")
>   $response.addHeader("content-disposition", "attachement;
> filename=report.xls")
>
> I've tested this with Excel 2000 and the only problem for which I don't
> have a solution is that Excel ignores the suffix of the filename=
> attribute in the Content-Disposition header adding .xls to it.  This
> happens even when the associated suffix of .xlshtml is used.  So either
> explicitly specify .xls as above or specify no suffix.  However, when the
> user saves the file, it will save as an HTML file even though the filename
> ends with .xls.  As a result, users should always choose Save As... and
> select Save as type: as one of the .xls formats, or, change the suffix to
> .htm/.html.
>
> Attached is a .zip file with some sample files.  The ExcelHTML.html page
> demonstrates how to use the CSS styles for a variety of numeric formats.
> The ProfileDemo.* files are specific to your application.  I modified
> ProfileDemo.htm slightly from the original saved version adding a
> FILTER=ALL attribute to each of the column heading td's.  This causes a
> drop down to be available in each of the column headings allowing the user
> to filter the columns in various ways.
>
> For help on whitespace for CSV's, see below ...
>
> Tom Bednarz wrote:
> >
> > Hi,
> >
> > I try to create a CSV File using Velocity. The tricky thing is handling
of
> > CR/LF. Can anybody help?
> >
> > My template looks as follows:
> >
> > $response.setContentType("text/comma-separated-values")
> > $response.addHeader("content-disposition", "attachement;
> > filename=report.cvs")
> >
> > #foreach ($field in $TableHeader)
> >     $field;
> > #end
> > #foreach ($record in $data)
> >     #foreach($value in $record)
> >         $value;
> >     #end
> > #end
>
> With some exceptions, Velocity treats most whitespace literally.  You can
> still use an easy to read nested format for the #foreach directives by
> hiding unnessary whitespace with block comments #* ... *#.  So the above
> might be:
>
> #foreach ($field in $TableHeader)#*
>     *#$field;#*
> *##end
> #foreach ($record in $data)#*
>     *##foreach($value in $record)#*
>         *#$value;#*
>     *##end
> #end
>
> Hope this helps,
> -Bill
>


----------------------------------------------------------------------------
----


> --
> To unsubscribe, e-mail:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>


--
To unsubscribe, e-mail:   <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>

Re: Generating a CSV File using Velocity (and Excel aware HTML)

[Bill Burton <bi...@progress.com>]

Hi Tom,

Since you want this for Excel, you should consider generating this output
as HTML as an alternative to CSV.  Excel can read HTML tables converting
them into an appropriate columns and rows.  This also allows specifying a
header with date and other descriptive information regarding the generated
data which you can't do with a CSV file.  Other advantages to using HTML
are that you can control exactly what Excel format you want to be used for
a cell by specfying an appropriate CSS predefined style.

The easiest way to figure this out is to create a simple version of the
spreadsheet with one or two rows and then select File, Save as Web
Page..., "Selection: Sheet" to save as a single HTML file.

Using a modified version of your code below, you would set the headers as:
  $response.setContentType("application/vnd.ms-excel")
  $response.addHeader("content-disposition", "attachement;
filename=report.xls")

I've tested this with Excel 2000 and the only problem for which I don't
have a solution is that Excel ignores the suffix of the filename=
attribute in the Content-Disposition header adding .xls to it.  This
happens even when the associated suffix of .xlshtml is used.  So either
explicitly specify .xls as above or specify no suffix.  However, when the
user saves the file, it will save as an HTML file even though the filename
ends with .xls.  As a result, users should always choose Save As... and
select Save as type: as one of the .xls formats, or, change the suffix to
.htm/.html.

Attached is a .zip file with some sample files.  The ExcelHTML.html page
demonstrates how to use the CSS styles for a variety of numeric formats. 
The ProfileDemo.* files are specific to your application.  I modified
ProfileDemo.htm slightly from the original saved version adding a
FILTER=ALL attribute to each of the column heading td's.  This causes a
drop down to be available in each of the column headings allowing the user
to filter the columns in various ways.

For help on whitespace for CSV's, see below ...

Tom Bednarz wrote:
> 
> Hi,
> 
> I try to create a CSV File using Velocity. The tricky thing is handling of
> CR/LF. Can anybody help?
> 
> My template looks as follows:
> 
> $response.setContentType("text/comma-separated-values")
> $response.addHeader("content-disposition", "attachement;
> filename=report.cvs")
> 
> #foreach ($field in $TableHeader)
>     $field;
> #end
> #foreach ($record in $data)
>     #foreach($value in $record)
>         $value;
>     #end
> #end

With some exceptions, Velocity treats most whitespace literally.  You can
still use an easy to read nested format for the #foreach directives by
hiding unnessary whitespace with block comments #* ... *#.  So the above
might be:

#foreach ($field in $TableHeader)#*
    *#$field;#*
*##end
#foreach ($record in $data)#*
    *##foreach($value in $record)#*
        *#$value;#*
    *##end
#end

Hope this helps,
-Bill