laneoc
Posts: 154
|
| Posted: 01/20/2016, 9:39 AM |
|
Looking for tips...
I'd like to modify the output before sending it to Excel. I have some "before.." event to hide links, etc. This is different.
I have cells in tables which I want to eliminate leading and trailing spaces. Example: I want this...
<td> 999 </td>
... changed to this...
<td>999</td>
I know how to use PHP string functions to make this happen, but I don't know how to grab the output before it is published.
Any tips, suggestions?
_________________
Lane |
 |
 |
eratech
Posts: 513
|
| Posted: 01/20/2016, 8:43 PM |
|
laneoc - is there a reason why you aren't trimming the spaces off in BeforeShow or in the query?
I'm assuming you have a displayed report that needs the spaces and the Excel doesn't (or converts the values in the wrong way) when you throw it to Excel?
I would suggest str_replace() but you would need to do the entire $main_block. It might help if you could put some class or other identifyer on the <td> like '<td class="trim_me">' then you could look for them only. I'm sure regex expressions would work with 'preg_replace()' to allow it.
see also: http://stackoverflow.com/questions/5210287/how-replace-...ng-preg-replace
Also - if you are passing a flag to Export, you could do the trimming based on the flag, and leave the displayed version with the spaces intact (eg: in the Grid BeforeShow do all the trimming there instead of individual fields each getting a flag check and trim)
Cheers
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 01/20/2016, 9:40 PM |
|
I went for the query solution with excel (export as .csv) as some earlier versions of excel couldn't translate the float field into currency/number when clicking from the grid.
_________________
Central Coast, NSW, Australia.
|
|
|
|
eratech
Posts: 513
|
| Posted: 01/21/2016, 2:36 AM |
|
MichaelMcDonald - I agree - I usually do a separate query/service and export as CSV. Duplicating queries but it's cleaner in the long run.
Where I can't I've always had luck with using "mso-number-format" classes in <td> which bump Excel into line most of the time, especially when needing to keep leading Zeros in invoice and payroll numbers I format as Text so 'helpful' Excel doesn't muck it up.
eg:
<td style="mso-number-format:\@">00001234</td>
see for examples of formating: http://niallodoherty.com/post.cfm/basic-html-to-excel-formatting
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
MichaelMcDonald
Posts: 640
|
| Posted: 01/21/2016, 5:47 PM |
|
One day MS will "find" a vulnerability and remove support for the format.
The biggest admission they never made was making the GUI an optional install during setup from Windows Server 2008 onwards - to (laugh) quote: Remove the scope of the attack vector from the operating system :)
Why I will always go with .csv 
_________________
Central Coast, NSW, Australia.
|
|
|
|
laneoc
Posts: 154
|
| Posted: 01/25/2016, 11:36 AM |
|
Thank you each for the thought contribution.
I'm using Eric's str_replace() on the $main_block. Here's my code in the Page_BeforeOutput event:
global $main_block;
if (ccGetParam("export","")=="1")
{ $temp_block=$main_block;
$temp_block=str_replace(">( ",">",$temp_block);
$temp_block=str_replace(" <","<",$temp_block);
$temp_block=str_replace("> ",">",$temp_block);
$temp_block=str_replace(" >","<",$temp_block);
$main_block=$temp_block;
}
Somewhat verbose but readable and maintainable by my maintenance team in the future (me).
Cheers!
Lane
_________________
Lane |
|
|
|
eratech
Posts: 513
|
| Posted: 01/26/2016, 6:28 PM |
|
Quote laneoc:
Somewhat verbose but readable and maintainable by my maintenance team in the future (me).
I have the same problem - the person most likely to read the code in 6-12-48 months is Me!
Glad you got it working.
Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia |
|
|
|
|
