Export to setNumberFormat values read as 0

Suite and Optimus Suite 5
#1

Just upgraded to v35 and downloaded the latest pdf and excel exporters.

Numbers formatted with the setNumberFormat command where I added a $ now all read 0 in excel, they are fine in pdf.

…
grid.setColTypes(“rotxt,rotxt,rotxt,rotxt,rotxt,rotxt,rotxt,rotxt,ron,ron,ron”);
grid.setNumberFormat(“0,000.00”,8); //works
grid.setNumberFormat(“0.0”,9); //works
grid.setNumberFormat(“$0,000.00”,10); //all zero
…

This used to work in v30 and previous exporter. I’m using excel 2010.

#2

Ok, I read my other post from a while back and resolved it.

//extended ron to insure $ values are exported to excel
function eXcell_rousd(cell){
this.base=eXcell_ron;
this.base(cell);
this.getContent = function(){
return this.cell.innerHTML;
};
this.setValue = function(val){
if(!val||val.toString()._dhx_trim()=="")val=“0”;
this.cell.innerHTML=this.grid._aplNF(val,this.cell._cellIndex);
};
};
eXcell_rousd.prototype=new eXcell_ron;

Applied rousd as applicable for column types.

Let me know if there’s a better way.

#3

Hi,
please, open file gridExcelWrapper.php and modify it like here:

case 'number':
				case 'num':
				case 'edn':
				case 'ron':
					$text = preg_replace("/[^\d-,\.]/", "", $text);
					$text = str_replace(",", "", $text);
					$this->excel->getActiveSheet()->getCell($this->getColName($i).$this->currentRow)->setValueExplicit($text, PHPExcel_Cell_DataType::TYPE_NUMERIC);
					break;

After that formatted numbers should be exported correctly.

#4

That works ok most the way. There is a remaining problem with formulas.

grid.setColTypes("… …,ron,ron,ron,ron,ron[=c8+c9],ron[=(c8+c9)/c7*100],ron[=c6-c10],ron[=c7-c10],ron[=c12-c13],ron,… …");
grid.setNumberFormat("$0,000",6);
grid.setNumberFormat("$0,000",7);
grid.setNumberFormat("$0,000",8);
grid.setNumberFormat("$0,000",9);
grid.setNumberFormat("$0,000",10);
grid.setNumberFormat(“0%”,11);
grid.setNumberFormat("$0,000",12);
grid.setNumberFormat("$0,000",13);
grid.setNumberFormat("$0,000",14);
grid.setNumberFormat(“0%”,15);
grid.setNumberFormat(“0%”,16);

Formula values are not interpreted as number values, nor formulas.

Result example in eXcel:

84822 76340 12268 153 $12,421 16% $72,401 $63,919 $8,482 11 100

#5

Hi,
could you provide complete demo? Described scenario works correctly machine.

#6

Please see below demo

Export to PDF and Price and Total Sales will both have $ sign in front of the value (which is wanted).

Export to Excel and Price will be a number, but Total Sales (from formula) has the $ sign.

All files referenced in this demo were downloaded last weekend.

I used the same demo for my post: “Some filters not functioning after export to pdf or excel”

Demo 
    <link rel='STYLESHEET' type='text/css' href='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/dhtmlxgrid.css'>
<link rel='STYLESHEET' type='text/css' href='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/skins/dhtmlxgrid_dhx_skyblue.css'>
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/dhtmlxcommon.js'></script>
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/dhtmlxgrid.js'></script>
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/dhtmlxgridcell.js'></script>
    
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/ext/dhtmlxgrid_filter.js'></script>
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/ext/dhtmlxgrid_math.js'></script>
<script type='text/javascript' src='../common/dhtmlxSuite_v35_pro_120822/dhtmlxGrid/codebase/ext/dhtmlxgrid_export.js'></script>

<div id="gridbox" style="width:725px; height:350px"></div>
    <br><br>
<input type="button" name="" value="Get as Excel" style="width:300px; font-weight:bold;" onclick="mygrid.toExcel('../common/dhtmlxSuite_Goodies/grid-excel-php/generate.php');"><br><br>
<input type="button" name="" value="Get as PDF" style="width:300px; font-weight:bold;" onclick="mygrid.toPDF('../common/dhtmlxSuite_Goodies/grid-pdf-php/generate.php');"><br><br>
#7

Hi,
please, try to update grid-excel-php from attachment. Provided demo works correctly with this version.
grid-excel-php.zip (789 KB)

#8

Please see the attached excel and pdf files.

The grid.xlsx file shows numbers, without $ signs, in the Price column, but Total Sales does show the $ sign and the numbers are stored as text of course.

The .pdf file shows $ signs in both cases, just like the dhtmlx grid. No issues there!

No high priority, appreciate your time. Thanks!
examples.zip (299 KB)

#9

Hi,
here is one more update, which should fix described problem.
Does it work correctly for now?
grid-excel-php.zip (790 KB)

#10

That works! Thanks!!

#11

Please update your central depository with this fix too :slight_smile: