[SalesForce] Encode Long Text Area field

Best String Methods

I've been wondering about your use of String.remove in your above code. This method is described as follows. Thus if you have identical lines in your CSV this will for sure not be what your expecting, which i assume was just to remove the last line read.

Removes all occurrences of the specified substring and returns the String result.

You should consider using String.substring to chop your way along the string (though reviewing the ever growing array of String methods, suggests maybe String.substringAfter might also be worth a try).

Example Line Reader

I recently used the following Iterator implementation (useful for Batch Apex integration) to parse some Apex code this should work for your use case. I've modified the LINE_SEPARATOR variable to based on a CSV file I've used in the past (from Excel), though there is no standard specification on this you will have to stipulate and/or make it a configuration option.

LineReader lineReader = new LineReader(csvFile);
List<String> newLines = new List<String>();
while(lineReader.hasNext())
{
    // Read line
    String line = lineReader.next();
}

/**
 * Utility class to iterate over lines
 **/
public class LineReader 
    implements Iterator<string>, Iterable<string>
{       
    private String LINE_SEPARATOR = '\r\n';

    private String textData;

    public LineReader(String textData)
    {       
        this.textData = textData;
    }

    public Boolean hasNext()
    {
        return textData.length() > 0 ? true : false;
    }   

    public String next()
    {
        String row = null;
        Integer endPos = textData.indexOf(LINE_SEPARATOR);
        if(endPos == -1)
        {
            row = textData;
            textData = '';
        }
        else
        {
            row = textData.subString(0, endPos);
            textData = textData.subString(endPos + LINE_SEPARATOR.length(), textData.length());             
        }
        return row;
    }

    public Iterator<String> Iterator()
    {
        return this;   
    }           
} 

Batch Apex Consideration

Quite a large CSV file to parse, in the statement governor free world, this type of processing stands a better chance. Though depending on what your doing with the parsed data you may still run into issues. There is an approach described in more detail here which uses Batch Apex you might want to look at (not the global keyword is not needed these days).

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