dynamic formula?

Topics: Developer Forum, User Forum
Jun 10, 2009 at 3:53 PM

Hi, my first post, I have been using the PEAR Spreadsheet_Writer for a while now but am starting to convert to PHPExcel.

I am trying to write a formula to column K but can not get it working, the spreadsheet has 13 columns and maybe 12,000 rows.

<!-- this is the formula I need in column K =IF(I' .$row. '>0.01,(100-I' .$row. ')*G' .$row. '%,J' .$row. '*H' .$row. ') -->

My code;

    $objPHPExcel = new PHPExcel();
   
    $objPHPExcel->getActiveSheet()->setCellValue('A1','PRCSHID');
    $objPHPExcel->getActiveSheet()->setCellValue('B1','GROUP NO');
    $objPHPExcel->getActiveSheet()->setCellValue('C1','GROUP DESC');
    $objPHPExcel->getActiveSheet()->setCellValue('D1','ITEM NO');
    $objPHPExcel->getActiveSheet()->setCellValue('E1','ITEM DESC');
    $objPHPExcel->getActiveSheet()->setCellValue('F1','LIST PRICE');
    $objPHPExcel->getActiveSheet()->setCellValue('G1','PRICE PER');
    $objPHPExcel->getActiveSheet()->setCellValue('H1','PER');
    $objPHPExcel->getActiveSheet()->setCellValue('I1','%DISCOUNT');
    $objPHPExcel->getActiveSheet()->setCellValue('J1','NETT PRICE');
    $objPHPExcel->getActiveSheet()->setCellValue('K1','NETT PER');
    $objPHPExcel->getActiveSheet()->setCellValue('L1','COST');
    $objPHPExcel->getActiveSheet()->setCellValue('M1','MARGIN');
   
    $objPHPExcel->getActiveSheet()->freezePane('A2');
   
    $row = 2;
    while ($data = mssql_fetch_row($result)) {
        $col = 0;
        foreach ($data as $key => $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);
            $col++;

        }
        $row++;
    }

    // close and write the excel file
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($dir . $filename);

Developer
Jun 11, 2009 at 12:11 PM

PHPExcel_Writer_Excel5 is based on PEAR Spreadsheet_Excel_Writer. Formula bugs have not been fixed yet.
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7895

Can you try to avoid % in the formula, perhaps divide by 100 instead? It is not working with Excel5 writer. I think PHPExcel_Writer_2007 will be ok, however.

 

Jun 11, 2009 at 4:17 PM

I can use the 2007 writer instead, but how would i add that formula to my code so it was in column K?

Developer
Jun 11, 2009 at 4:30 PM

Is it not working with e.g. ?

$objPHPExcel->getActiveSheet()->setCellValue('K2', '=IF(I' . $row . '>0.01, (100-I' . $row. ')*G' . $row . '%, J' . $row . '*H' . $row . ')');

Jun 12, 2009 at 3:20 PM

But that will just repeat the formula in cell K2?

How can i dynamically add it to column K on every row?

Developer
Jun 13, 2009 at 2:30 AM

Not sure what you mean by dynamically? To write the formula in other rows, you would need some loop to handle this.

Jun 15, 2009 at 9:44 AM

I have tried to put it in the while loop i already have which writes the rows but can not get it to work correctly;

 

while ($data = mssql_fetch_row($result)) {
        $col = 0;
        foreach ($data as $key => $value) {
            $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value);

$objPHPExcel->getActiveSheet()->setCellValue('K' .$row. ', '=IF(I' . $row . '>0.01, (100-I' . $row. ')*G' . $row . '%, J' . $row . '*H' . $row . ')');

            $col++;        

}

 

Developer
Jun 15, 2009 at 11:09 PM

Can you try to run this code? It is working ok for me?

<?php
$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$row = 1;

$sheet->setCellValue('G1', 60);
$sheet->setCellValue('I1', 0.05);
$sheet->setCellValue('K' . $row, '=IF(I' . $row . '>0.01, (100-I' . $row. ')*G' . $row . '%, J' . $row . '*H' . $row . ')');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('write.xlsx');
?>

Jun 16, 2009 at 12:03 PM

Hi,

Looks like it was the Excel5 writer causing the problem, the following code works with Excel2007;

    $row = 2;
    while ($data = mssql_fetch_row($result)) {
        $col = 0;
        foreach ($data as $key => $value) {
            $sheet->setCellValueByColumnAndRow($col, $row, $value);
            $col++;
        }
        $sheet->setCellValue('K' . $row, '=IF(I' . $row . '>0.01, (100-I' . $row. ')*G' . $row . '%, J' . $row . '*H' . $row . ')');
        $row++;
    }

 

Thanks for your help.

Developer
Jun 16, 2009 at 12:39 PM

Excel5 writer does unfortunately not support % in formulas. Use this workaround instead:

<?php

$excel = new PHPExcel();
$sheet = $excel->getActiveSheet();

$row = 1;

$sheet->setCellValue('G1', 60);
$sheet->setCellValue('I1', 0.05);
$sheet->setCellValue('K' . $row, '=IF(I' . $row . '>0.01, (100-I' . $row. ')*G' . $row . '/100, J' . $row . '*H' . $row . ')');

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$writer->save('write.xls');
?>


Jun 17, 2009 at 7:33 AM

Thanks koyama,

without starting a new thread, is it easy to set a columns data type to "text"?

In this work sheet i have some numbers with leading 0's and these get trimmed when using the general or default cell data type.

I have attempted;

    $styleArray = array (
        'code' => PHPExcel_Cell_DataType::TYPE_STRING
    );
   
    $range1 = 'B2:B' .$row;
    $sheet->getStyle($range1)->applyFromArray($styleArray);

but this does not work.

Developer
Jun 17, 2009 at 12:03 PM

You have a couple of choices. I think the best method is to preserve numbers as numbers (column A in example). But you can also store as text (column B).

Using latest source code (allowing range syntax in getStyle() ):

$sheet->getCell('A1')->setValue(12);
$sheet->getCell('A2')->setValue(324);
$sheet->getCell('A3')->setValue(4);
$sheet->getStyle('A1:A3')->getNumberFormat()->setFormatCode('00000');

$sheet->getCell('B1')->setValueExplicit('00012', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->getCell('B2')->setValueExplicit('00324', PHPExcel_Cell_DataType::TYPE_STRING);
$sheet->getCell('B3')->setValueExplicit('00004', PHPExcel_Cell_DataType::TYPE_STRING);




You can unfortunately not yet set the style for a whole column in PHPExcel. There is a feature request for this:
http://phpexcel.codeplex.com/WorkItem/View.aspx?WorkItemId=7333

For now, expand the cell range in getStyle('A1:A100') to the necessary row index.

 

Jun 17, 2009 at 12:30 PM

Thanks,

I worked it out and used option B as this seemed easier using the code below;

    while ($data = mssql_fetch_row($result)) {
        $col = 0;
        foreach ($data as $key => $value) {
            if ($key == 1 OR $key == 3) {
                $sheet->setCellValueExplicitByColumnAndRow($col, $row, $value, PHPExcel_Cell_DataType::TYPE_STRING);
            } else {               
                $sheet->setCellValueByColumnAndRow($col, $row, $value);
            }
            $col++;
        }
        $row++;
    }