10

Goal is to generate a CSV file from VF that contains UTF-8 chars and multiline text fields. All users are Mac users.

Controller

public class MultiLineUtf8CsvMac {
    public String utf8Bom {get {return '\uFEFF';} private set;}
    public AccountCsvWrapper[] getAccountsAsCsv() {  // used by VF page
        return new List<AccountCsvWrapper> {
            new AccountCsvWrapper(new Account( name = 'chinese:熙輿網', 
                                              description='line01\r\nline02'))
                };
    }

    public class AccountCsvWrapper {    // CSV-enables an Account
        public String name      {get {return this.a.name.escapeCsv();} private set;}
        public String descrip   {get {return this.a.description.escapeCsv();} private set;}
        public String website   {get; private set;}
        private Account a;

        public AccountCsvWrapper(Account a) {
            this.a = a;
        }
    }
}

VF Page

<apex:page contentType="application/vnd.ms-excel#filename.csv; charset=UTF-8" controller="MultiLineUtf8CsvMac"
           showheader="false" sidebar="false" standardStyleSheets="false">
    Name,Description,Website
    <apex:repeat value="{!AccountsAsCsv}" var="aac">
        {!aac.Name},{!aac.descrip},{!aac.website}
    </apex:repeat>
</apex:page>

Results with Excel for Mac 2011

enter image description here

Results for Google Sheets (csv file imported into browser)

enter image description here

SFSE post here suggests adding UTF-8 BOM in front of the output but VF seems to insert a few characters (0A 20 20 20 20, manifested as line 1 in the CSV) so I can't get the controller constant utf8Bom to appear before row 1 is emitted.

Options:

  1. Tell all my users to use Google Sheets (not ideal)

  2. Generate the CSV as a large String in the controller, inserting the UTF 8 BOM at the head of the string. Not ideal, run into heap issues in controller and unconvinced it would still be inserted ahead of the first line by VF. Plus, when I patch in the hex code for the UTF-8 BOM into a CSV, Excel for Mac 2011 seems to ignore it (if I did it right, EF BB BF, using a hex editor)

  • If you insert a UTF-8 BOM in your CSV, you'll end up with Excel (for Mac) misinterpreting the BOM characters at the beginning of the file as . Not so on Mac for PC – Phil Hawthorn May 24 '16 at 20:22
  • @PhilHawthorn - yep, this is what I observed. The question is, how to get Excel for Mac to recognize a CSV with UTF-8 chars (or at least recognize accented and Asian language chars, displaying correctly - while also recognizing line breaks in text fields). I had made all this work on Windows Excel but for Mac....sigh – cropredy May 24 '16 at 20:53
  • So, the reason I knew about the UTF-8 BOM was from using FileSaver.js to auto save/download CSVs so I tried your 'chinese:熙輿網' in my CSV to see what I got. Interestingly, I get ç 輿網 in TextEdit (which is same as Mac Excel without BOM) but in Mac Excel i get 熙輿網. Using the Data Import facility almost worked, but not quite, – Phil Hawthorn May 24 '16 at 21:15
  • @PhilHawthorn - see my answer. Thoughts? – cropredy May 25 '16 at 0:55
5

OK, I have a plausible solution for Excel for Mac users.

First, the options that I considered but rejected:

Windows-1252 encoding - Doesn't support Asian languages

UTF-16LE encoding - Creates user issues because if they edit the file I produce and Save, they lose the encoding.

Generating an HTML table but declaring the output file as *.xls - Excel for Mac (or Win) happily opens HTML files as if they are worksheets and preserves the UTF-8 chars. But Excel for Mac ignores the MSFT CSS style br :{mso-data-placement:same-cell;} which works in Windows Excel. So, the Mac user loses line breaks in SFDC fields that have line breaks.

And the reasonable winning option (for now) ..

Generating an Excel 2004 XML document

Yep, there's nothing like using a 2004 specification but this is how it works:

VF page (must be at V19.0 or earlier

<apex:page controller="MultiLineUtf8ExcelMac" cache="true"
           contenttype="application/vnd.ms-excel#AccountExport.xls; charset=UTF-8" 
           showheader="false">  
<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="12" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID="s62">
   <Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
  </Style>
 </Styles>
<Worksheet ss:Name="AccountExport">
<Table>
   <Row>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Description</Data></Cell>
    <Cell><Data ss:Type="String">Website</Data></Cell>
   </Row>
   <apex:repeat value="{!AccountsAsXml}" var="aax">
      <Row>
        <Cell><Data ss:Type="String">{!aax.name}</Data></Cell>
        <Cell ss:StyleID="s62"><Data ss:Type="String">{!aax.descrip}</Data>Cell>
        <Cell><Data ss:Type="String">{!aax.website}</Data></Cell>
      </Row>
    </apex:repeat>
  </Table>
</Worksheet>
</Workbook>

Controller

public class MultiLineUtf8ExcelMac {
    public AccountXmlWrapper[] getAccountsAsXml() {  // used by VF page
        return new List<AccountXmlWrapper> {
            new AccountXmlWrapper(new Account( name = 'chinese:熙輿網', 
                                              description='line01\r\nline02'))
                };
    }

    public class AccountXmlWrapper {    // Excel XML-enables an Account
        public String name      {get {return this.a.name;} private set;}
        public String descrip   {
           get {
            return this.a.description.replaceAll('\n','&#13;')                                                           
                                     .replaceAll('\r','');
           } 
           private set;
        }
        public String website   {get; private set;}
        private Account a;

        public AccountXmlWrapper(Account a) {
            this.a = a;
        }
    }
}

So why and how does this work?

  1. VF page has to be at V19.0 or earlier otherwise the compiler rejects <?xml version="1.0" encoding="UTF-8"?>. There are workarounds to this by having your controller or a custom component controller return the XML directives in merge fields.
  2. The controller has to replace SFDC \r\n or \r\n in any text field with something compliant with XML, in this case &#13;. This gets you the line breaks.
  3. You need to use StyleID="s62" on any cell that you want to have text wrap. This style name could be anything but I just copied from an example Excel for Mac document saved as a .xml file.
  4. You need to make the name of the file downloaded suffixed by .xls otherwise Excel will not be the default application to open the downloaded file. This is equivalent to the trick one uses to output HTML tables in VF and have Excel open the file.

Caveats

  1. The MSFT Excel XML format cannot be imported directly into Google Sheets. The user must first save as the downloaded file as .xlsx - which is a good thing to do anyway if they are going to manipulate the file and then save.

There may be better (and easier) solutions out there but for VF to generate the trifecta of Excel for Mac, Asian languages, and line breaks - this may be it.

  • Dear oh dear, the only apps I ever seem to have problems with on a Mac are Microsoft..... – Phil Hawthorn May 25 '16 at 7:28
  • Brilliant, I wish I had more upvotes to give. Useful resource for those who choose to take the plunge and need to figure out formatting, etc: A guide to SpreadsheetML: m8y.org/Microsoft_Office_2003_XML_Reference_Schemas/Help/html/… – Jason Clark Jun 21 '18 at 16:45
  • 1
    @JasonClark - thanks for the upvote - this took me quite a while to arrive at this "solution" and I was fortunate to have the time as otherwise I might have just abandoned it out of frustration. The back of my mind suspects there is a better solution but I couldn't find it. – cropredy Jun 21 '18 at 17:26
0

Perhaps use an alternative approach of not using Visualforce to create the CSV, but instead use JavaScript.

A possible solution could be:

  • load the necessary data using JavaScript remoting
  • construct the CSV output in JavaScript
  • request the browser to download the output as a file

A quick search on Google shows several results on how to create a CSV in JavaScript. One GitHub gist shows a simple approach;

https://gist.github.com/adilapapaya/9787842

For information on JavaScript remoting check out the help article on Salesforce:

https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_js_remoting.htm

Hope this helps!

  • intriguing but I'm not sure how this addresses telling Excel for Mac that the file contains UTF-8 chars; last comment in the gist seems to raise that issue as well – cropredy May 21 '16 at 15:49
  • I found another post which seems to address this issue for you: stackoverflow.com/questions/19492846/… – Adam May 22 '16 at 8:52
  • Hmm.. windows-1252 won't work because I have chinese chars but the UTF-16 suggestion is worth a try. Thanks; it may take me a bit to see if this works as I deployed the code as is with a note in line 1 of the CSV telling users to import to Google Sheets if they see 'garbled chars' - lame, yes but I needed to work on other stuff. Another thought I recently had was to try outputting as Excel XML and, once users open file, they can save as normal .xlsx . I had tried outputting as .htm but excel for mac wouldn't deal with line wrapped styles unlike windows excel – cropredy May 22 '16 at 18:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.