Apex Code Development You need to sign in to do that
Don't have an account?
Visualforce , Excel and line breaks
Problem:
You have a VF page that you want to output as Excel but the line breaks in your TextArea fields cause Excel to create new rows at each line break rather than preserve the line breaks within the cell as if you had used ALT-ENTER (or your line breaks are lost altogether and the text is run on with no breaks)
Example:
SFDC TextArea field contains:
"Line 1
Line 2"
(or "Line 1\nLine 2")
Your output in Excel should be one row with the cell preserving the line breaks but you either:
a) get two rows, broken at the line break
b) one row but the line break is lost and the text is run-on
Solution follows in the next post
Here's the solution:
1. If using a VF controller, replace your \n in strings with <br/> as in:
fieldC.replaceAll('\n','<br/>');2. Use a VF page similar to this one. Note the style for the br tag. The example also shows how to get UTF-8 output
<!-- ------------------------------------------------------------ --> <!-- VF PAGE AS EXCEL WITH LINE BREAKS PRESERVED --> <!-- ------------------------------------------------------------ --> <apex:page controller="MyController" cache="true" contenttype="application/vnd.ms-excel#myList.xls" showheader="false"> <!-- head tag required (and showheader="false" also required in order to specify UTF-8 charset as output --> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /> <style> br {mso-data-placement:same-cell;} <!-- MSFT style causes Excel to treat <br/> as soft carriage return 'ALT-ENTER' when constructing cells from SFDC HTML output </style> </head> <apex:pageBlock > <apex:pageBlockTable style="font-size: xx-small;" value="{!myList}" var="line" columns="3"> <apex:column headerValue="Field A">{!line.fieldA}</apex:column> <apex:column headerValue="Field B">{!line.fieldB}</apex:column> <apex:column headerValue="Field C">{!line.fieldC}</apex:column> </apex:pageBlockTable> </apex:pageBlock> </apex:page>If you do a web search on mso-data-placement you'll see many references on HTML -> EXCEL discussing this topic. I just applied it to VF.
All Answers
Here's the solution:
1. If using a VF controller, replace your \n in strings with <br/> as in:
fieldC.replaceAll('\n','<br/>');2. Use a VF page similar to this one. Note the style for the br tag. The example also shows how to get UTF-8 output
<!-- ------------------------------------------------------------ --> <!-- VF PAGE AS EXCEL WITH LINE BREAKS PRESERVED --> <!-- ------------------------------------------------------------ --> <apex:page controller="MyController" cache="true" contenttype="application/vnd.ms-excel#myList.xls" showheader="false"> <!-- head tag required (and showheader="false" also required in order to specify UTF-8 charset as output --> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /> <style> br {mso-data-placement:same-cell;} <!-- MSFT style causes Excel to treat <br/> as soft carriage return 'ALT-ENTER' when constructing cells from SFDC HTML output </style> </head> <apex:pageBlock > <apex:pageBlockTable style="font-size: xx-small;" value="{!myList}" var="line" columns="3"> <apex:column headerValue="Field A">{!line.fieldA}</apex:column> <apex:column headerValue="Field B">{!line.fieldB}</apex:column> <apex:column headerValue="Field C">{!line.fieldC}</apex:column> </apex:pageBlockTable> </apex:pageBlock> </apex:page>If you do a web search on mso-data-placement you'll see many references on HTML -> EXCEL discussing this topic. I just applied it to VF.
Eric,
Thanx a lot. Your solution works gr8 for the line breaks but the columns still have problem.
Do you have nay idea, how to set the values in separate columns? Currently am using \t and it not working as intended.
Thanx in advance for ur help.
Thanx
Cheers
Here is a fairly general way to do this, especially if you have more than 1000 rows to output. It relies on VF controller code to build a List of Lists of your output data so you don't run afoul of the 1000 element collection size limit. You may run into other limits like HeapSize if you have a huge output file
<!-- VF PAGE AS EXCEL --> <apex:page controller="MyController" cache="true" contenttype="application/vnd.ms-excel#myFile.xls" showheader="false"> <!-- head tag required (and showheader="false" also required in order to specify UTF-8 charset as output --> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" /> <style> br {mso-data-placement:same-cell;} <!-- MSFT style causes Excel to treat <br/> as soft carriage return 'ALT-ENTER' when constructing cells from SFDC HTML output --> td {font-size: xx-small;} </style> </head> <apex:pageBlock > <table> <tbody> <tr> <td>My Col 01 Header</td> <td>My Col 02 Header</td> </tr> <!-- Artifice to work around 1000 elm limit on apex component collection size --> <apex:repeat value="{!allPagesOfData}" var="outerSet"> <apex:repeat value="{!outerSet}" var="line" > <!-- Inner set is at most 1000 elms while outerset is list of lists --> <tr> <td>{!line.myCol01}</td> <td>{!line.MyCol02}</td> </tr> </apex:repeat> </apex:repeat> </tbody> </table> </apex:pageBlock> </apex:page>For the columns thing I have been using CSS (as a static resource) to stylize my PDF but you might be able to use it for andthing else.