Bugs/probs in export to HTML/Excel omits URL-prefix for uploads / Umlaut problems

HRZ_UniBi · May 17th, 2011, 4:21 AM

Hello to all!

Currently I have an actual problem, that export to Excel still des not work (see other thread) and some new problems:

1.) When creating the export as Excel (which is HTML) or the HTML/printer-friendly export the links to the attachments are created like this:

<td>
<img src="/<FILENAME>" />
</td>

instead of

<td>
<a href="http://<SERVERNAME>/<PATH_TO_UPLOADS/<FILENAME>">
<FILENAME>"
</a>
</td>

2.) My Formtools server is running on an *nix box. When exporting/saving all Non-ASCII characters are exported in *nix format instead of Windows encoding.

Is there a way to correct these problems via settings? Or what can I do?

With regards,

Peter.

HRZ_UniBi · May 18th, 2011, 4:32 AM

Hello!

I got some infos about the problems by myself...

(May 17th, 2011, 4:21 AM)HRZ_UniBi Wrote:
1.) When creating the export as Excel (which is HTML) or the HTML/printer-friendly export the links to the attachments are created like this:

<td>
<img src="/<FILENAME>" />
</td>

instead of

<td>
<a href="http://<SERVERNAME>/<PATH_TO_UPLOADS/<FILENAME>">
<FILENAME>"
</a>
</td>

This seems to be the result of the fact, that there is any extra representation made for the field type "File". It's handled by the last method:

{else}
{$value}
{/if}

So to get the chance of having a field with a correct link a hidden field with the content
<GLOBAL_UPLOAD_URL>/<FILENAME>
has to be created. Please can anyone help how this can be done?

(May 17th, 2011, 4:21 AM)HRZ_UniBi Wrote: 2.) My Formtools server is running on an *nix box. When exporting/saving all Non-ASCII characters are exported in *nix format instead of Windows encoding.
...

Here I also got some information (or request for enhancement ;-) :
When I export to CSV and import into Excel and select UTF8 as source everything is fine. So it would be a great thing to have an export feature where I can select the export charset.

With regards,

Peter.

***Ben*** · Jun 3rd, 2011, 2:36 AM

Hey Peter,

I'm *so* sorry for not responding sooner.

Quote:So to get the chance of having a field with a correct link a hidden field with the content
<GLOBAL_UPLOAD_URL>/<FILENAME>has to be created. Please can anyone help how this can be done?

Sure, no problem! Would it be possible to post your entire Export Type smarty markup? I'd like to take a look at it and compare it with what I have to make sure we're working off the same code. This is found at this page: Modules -> Export Manager -> Edit Export Group -> Export Types tab -> Edit Export Type.

Quote:When I export to CSV and import into Excel and select UTF8 as source everything is fine. So it would be a great thing to have an export feature where I can select the export charset.

I wonder if this is related to the headers being sent for that export group? Edit the Excel Export Group in the Export Manager module and look at the first tab. Take a look in the headers section (and possibly as a meta tag in the Smarty markup). If memory serves, there's a header there telling it to dole up the info as UTF-8. Hmm... I like the fact that it's customizable, but it's not the most user-friendly thing ever... perhaps a dropdown to define the charset?

- Ben

HRZ_UniBi · Jun 6th, 2011, 1:59 AM

Hello!

Don't worry about the late reply. Here's the info from the export manager

Header:

Code:
Pragma: public

Cache-Control: max-age=0

Content-Type: application/vnd.ms-excel; charset=utf-8

Content-Disposition: attachment; filename={$filename}

Content:

Code:
<html>

<head>

</head>

<body>

{$export_type_smarty_template}

</body>

</html>

And here's the code from the standard export type:

PHP Code:
<h1>{$form_name}</h1>

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">
{* display the column headings *}
<tr>
  {foreach from=$display_fields item=column name=row}
    <th>{$column.field_title}</th>
  {/foreach}
</tr>

{* loop through all submissions in this current result set, and display each item in a manner
   appropriate to the field type *}
{foreach from=$submissions item=submission name=row}
  <tr>
    {foreach from=$display_fields item=field name=col_row}
      {assign var=field_id value=$field.field_id}
      {assign var=field_type value=$field.field_info.field_type}
      {assign var=col_name value=$field.col_name}
      {assign var=value value=$submission.$col_name}

    <td>
      {if $field_type == "select" || $field_type == "radio-buttons"}
        {smart_display_field_values field_id=$field_id selected=$value}
      {elseif $field_type == "checkboxes" || $field_type == "multi-select"}
        {smart_display_field_values field_id=$field_id selected=$value multiple=true}
      {elseif $field_type == "system"}
        {if $col_name == "submission_id"}
          {$submission.submission_id}
        {elseif $col_name == "submission_date"}
          {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}
        {elseif $col_name == "last_modified_date"}
          {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}
        {elseif $col_name == "ip_address"}
          {$submission.ip_address}
        {/if}
      {elseif $field_type == "textarea"}
        {$value|nl2br_excel}
      {else}
        {$value}
      {/if}
    </td>

    {/foreach}
  </tr>

{/foreach}

</table> 

Yes, it is set up as UTF-8. Whatever Excel thinks it is in its' mind...
Is there any difference between utf-8 and UTF-8 ;-)

With regards,

Peter.

***Ben*** · Jun 18th, 2011, 1:06 AM

Peter!

Again, sorry for not responding sooner. I do tend to forget who I'm supposed to get back to at times, so never feel bad sending an impatient email to remind me. Smile

This was surprisingly fiddly. So I got it working, but not in the most elegant fashion.

First off, use this Smarty code for your Export Type:

Code:
<h1>{$form_name}</h1>

<table cellpadding="2" cellspacing="0" width="100%" class="print_table">

{* display the column headings *}

<tr>

  {foreach from=$display_fields item=column name=row}

    <th>{$column.field_title}</th>

  {/foreach}

</tr>

{* loop through all submissions in this current result set, and display each item in a manner

   appropriate to the field type *}

{foreach from=$submissions item=submission name=row}

  <tr>

    {foreach from=$display_fields item=field name=col_row}

      {assign var=field_id value=$field.field_id}

      {assign var=field_type value=$field.field_info.field_type}

      {assign var=col_name value=$field.col_name}

      {assign var=value value=$submission.$col_name}

    <td>

      {if $field_type == "select" || $field_type == "radio-buttons"}

        {smart_display_field_values field_id=$field_id selected=$value}

      {elseif $field_type == "checkboxes" || $field_type == "multi-select"}

        {smart_display_field_values field_id=$field_id selected=$value multiple=true}

      {elseif $field_type == "system"}

        {if $col_name == "submission_id"}

          {$submission.submission_id}

        {elseif $col_name == "submission_date"}

          {$submission.submission_date|custom_format_date:$timezone_offset:$date_format}

        {elseif $col_name == "last_modified_date"}

          {$submission.last_modified_date|custom_format_date:$timezone_offset:$date_format}

        {elseif $col_name == "ip_address"}

          {$submission.ip_address}

        {/if}

      {elseif $field_type == "textarea"}

        {$value|nl2br_excel}

      {elseif $field_type == "file"}

        <a href="{$field.field_info.settings.file_upload_url}/{$value}">{$field.field_info.settings.file_upload_url}/{$value}</a>

      {else}

        {$value}

      {/if}

    </td>

    {/foreach}

  </tr>

{/foreach}

</table>

You'll see all I did was add an extra clause for "file", as you mentioned above.

The thing is, the settings for that field are only actually populated if they've been defined for that file field type. So they don't get populated with the default values. Daft! This has changed in 2.1.0 - there's proper inheritance going on for all field type settings, so this wouldn't be an issue.

Anyway, to get it working, you'll also need to click on "Options" for the File field (Edit Form -> Fields tab) and click "Customize Settings". In the table that appears, choose "File upload folder" then just click save (don't change the settings).

And that should be it! It should now provide a full clickable link to the uploaded file.

All the best -

Ben

FORUMS