0

I am having a problem with phpExcel. I have been tasked with generating a report that contains a rather complex formula. The formula includes some concatenated strings, but phpExcel does not seem to like that. The resulting file shows part of the formula, cutting at one of the concatenation points. I removed the = sign to see what phpExcel when the formula is presented as a normal string and it does just fine. I can manually put the = sign back in and the formula works as expected.

I slapped together a test file to demonstrate what is happening. I attached an image showing what this script outputted. As we can see, the formulas in the W and X columns were cut off on one of the concatenations.

require_once('/PHPExcel1.8.0/PHPExcel.php');

$Excel = new PHPExcel();

// Set file properties
$Excel->getProperties()->setCreator('Spreadsheet Author');
$worksheet = $Excel->getActiveSheet();

// Change the name of the first worksheet
$worksheet->setTitle('Report');
$Excel->createSheet(1);

// Set the header rows
$worksheet
->setCellValue('A1', 'Report Title')
->setCellValue('A3', 'Header 1')->setCellValue('B3', 'Header 2')->setCellValue('C3', 'Header 3')->setCellValue('D3', 'Header 4')
->setCellValue('E3', 'Header 5')->setCellValue('F3', 'Header 6')->setCellValue('G3', 'Header 7')->setCellValue('H3', 'Header 8')
->setCellValue('I3', 'Header 9')->setCellValue('J3', 'Header 10')->setCellValue('K3', 'Header 11')->setCellValue('L3', 'Header 12')
->setCellValue('M3', 'Header 13')->setCellValue('N3', 'Header 14')->setCellValue('O3', 'Header 15')->setCellValue('P3', 'Header 16')
->setCellValue('Q3', 'Header 17')->setCellValue('R3', 'Header 18')->setCellValue('S3', 'Header 19')->setCellValue('T3', 'Header 20')
->setCellValue('U3', 'Header 21')
->setCellValue('V3', 'Header 22')
->setCellValue('W3', 'Formula 2 (95% CI)')
->setCellValue('X3', 'Formula 2 (95% CI)');

// Switch pages
$Excel->setActiveSheetIndex(1);
$worksheet = $Excel->getActiveSheet();
$worksheet->setTitle('f'); // Make a separate sheet called "f"

$worksheet
->setCellValue('A1', 'n')->setCellValue('B1', 'p')->setCellValue('C1', 'e')
->setCellValue('D1', 'n')->setCellValue('E1', 'p')->setCellValue('F1', 'e');

// Switch back
$Excel->setActiveSheetIndex(0);
$worksheet = $Excel->getActiveSheet();


/*
* Populate the data in the spreadsheet
***************************************/

$data = array(
    array('String', 'Name', 1427312154, 2, 7, 0, 2, 1, 0, 0, 0, 2, 1, 0),
    array('String', 'Name', 1427312154, 25, 148, 0, 85, 0, 0, 0, 53, 99, 15, 0)
);

if (count($data) == 0)
{
    echo 'No rows to report';
    exit;
}
else
{
    // Conversion arrays
    $row = 4;

    for ($i = 0; $i < count($data); $i++)
    {
        $worksheet
        ->setCellValue('A' . $row, $data[$i][0])->setCellValue('B' . $row, $data[$i][1])->setCellValue('C' . $row, date('m/j/Y', $data[2]))
        ->setCellValue('D' . $row, $data[$i][3])->setCellValue('E' . $row, $data[$i][4])->setCellValue('F' . $row, $data[$i][5])
        ->setCellValue('G' . $row, $data[$i][6])->setCellValue('H' . $row, $data[$i][7])->setCellValue('I' . $row, $data[$i][8])
        ->setCellValue('J' . $row, $data[$i][9])->setCellValue('K' . $row, $data[$i][10])->setCellValue('L' . $row, '=SUM(H' . $row . ':K' . $row . ')')
        ->setCellValue('M' . $row, $data[$i][11])->setCellValue('N' . $row, $data[$i][12])->setCellValue('O' . $row, $data[$i][13])
        ->setCellValue('P' . $row, '=SUM(L' . $row . ':O' . $row . ')')
        ->setCellValue('Q' . $row, '=ROUND(F' . $row . '/E' . $row . ',1)')
        ->setCellValue('R' . $row, '=ROUND(G' . $row . '/E' . $row . ',1)')
        ->setCellValue('S' . $row, '=ROUND(P' . $row . '/E' . $row . ',1)')
        ->setCellValue('T' . $row, '=ROUND(L' . $row . '/E' . $row . ',1)')
        ->setCellValue('U' . $row, '=ROUND(I' . $row . '/E' . $row . ',2)')
        ->setCellValue('V' . $row, '=ROUND(J' . $row . '/E' . $row . ',3)')
        // THIS IS THE TROUBLE SPOT!!!
        ->setCellValue('W' . $row, '="1:"&ROUND(M' . $row . '/L' . $row . ',1)&", (1:"&ROUND((M' . $row . '/L' . $row . ')-f!B' . $row . ',1)&" to 1: "&ROUND((M' . $row . '/L' . $row . ')+f!B' . $row . ',1)&")"')
        // THIS ONE TOO[![enter image description here][1]][1]!!!
        ->setCellValue('X' . $row, '=ROUND(N' . $row . '/M' . $row . '*100,1)&"%"&" ("&ROUND(((f!E' . $row . '-f!F' . $row . ')*f!D' . $row . ')/(f!D' . $row . '-((f!E' . $row . '-f!F' . $row . ')*f!D' . $row . '))*100,1)&"%"&" to "&ROUND(((f!E' . $row . '+f!F' . $row . ')*f!D' . $row . ')/(f!D' . $row . '-((f!E' . $row . '+f!F' . $row . ')*f!D' . $row . '))*100,1)&"%)"');

        // Switch pages
        $Excel->setActiveSheetIndex(1);
        $worksheet = $Excel->getActiveSheet();

        $worksheet
        ->setCellValue('A' . $row, '=Report!G' . $row . '+Report!H' . $row)
        ->setCellValue('B' . $row, '=Report!G' . $row . '/A' . $row)
        ->setCellValue('C' . $row, '=1.96*SQRT(((B' . $row . '*(1-B' . $row . '))/A' . $row . '))')
        ->setCellValue('D' . $row, '=Report!G' . $row . '+Report!H' . $row)
        ->setCellValue('E' . $row, '=Report!I' . $row . '/B' . $row)
        ->setCellValue('F' . $row, '=1.96*SQRT((B' . $row . '*(1-B' . $row . '))/B' . $row . ')');

        // Switch pages
        $Excel->setActiveSheetIndex(0);
        $worksheet = $Excel->getActiveSheet();

        $row++;
    }
}

// Put the active cell on A1
$worksheet->setSelectedCell('A1');


/*
* Close and output file
************************/

// Force download
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="report.xls"');
header('Cache-Control: max-age=0');

// Select the rendering engine and save XLS file
$saveExcel = PHPExcel_IOFactory::createWriter($Excel, 'Excel5');
$saveExcel->setPreCalculateFormulas(false);
$saveExcel->save('php://output');

enter image description here

UPDATE I swapped out the shorthand concatenation for the CONCATENATE function to see if I get better results.

->setCellValue('W' . $row, '=CONCATENATE("1:",ROUND(M' . $row . '/L' . $row . ',1),", [1:",ROUND((M' . $row . '/L' . $row . ')-f!B' . $row . ',1)," to 1: ",ROUND((M' . $row . '/L' . $row . ')+f!B' . $row . ',1),"]")')
->setCellValue('X' . $row, '=CONCATENATE(ROUND(N' . $row . '/M' . $row . '*100,1),"%"," [",ROUND(((f!E' . $row . '-f!F' . $row . ')*f!D' . $row . ')/(f!D' . $row . '-((f!E' . $row . '-f!F' . $row . ')*f!D' . $row . '))*100,1),"% to ",ROUND(((f!E' . $row . '+f!F' . $row . ')*f!D' . $row . ')/(f!D' . $row . '-((f!E' . $row . '+f!F' . $row . ')*f!D' . $row . '))*100,1),"%]")');

This produced a different problem entirely. The anomalous cells had nothing in them. No formula or output of any kind. So I started slicing the arguments out of the concat to see if any one of them was causing the problem. It turned out to be the very last argument -- literal ")" character. If I removed this piece of the concat or switched this character for anything else, everything worked perfectly. For some reason, phpExcel hates it when the last piece of a concat contains a ")" sign. Rather than effort this absurd problem any further, I switched them parentheses out for brackets. If I can get a solution to this, I will put the spreadsheet back as it was.

Tanoro
  • 871
  • 2
  • 10
  • 30
  • Don't see any images you said you would post. – Forbs Aug 24 '16 at 17:36
  • Weird. the image didn't make it in for some reason. I added it. – Tanoro Aug 24 '16 at 17:38
  • PHPExcel shouldn't have any problem with a pretty basic formula like that shown in the screenshot; but I'm struggling to understand how that formula is set in your code: is it what you're setting in `->setCellValue('W3', 'Formula 2 (95% CI)')`? Because that looks nothing like the formula in yoru image... working example to demonstrte the problem please! – Mark Baker Aug 24 '16 at 18:05
  • Keep scrolling. I stuck comments in all caps where the troublesome formulas are in code. The formula in the W column should look more like this: ="1:"&ROUND(M4/L4,1)&", (1:"&ROUND((M4/L4)-f!B4,1)&" to 1: "&ROUND((M4/L4)+f!B4,1)&")". But, as in the screenshot, it only gets to the end of the first ROUND and stops there, excluding the rest of the formula. – Tanoro Aug 24 '16 at 18:08
  • Also, the above code is a working example. Copy & paste it into a PHP file, adjust the path to the phpExcel files, run the page. You will download a file exactly like the screenshot with the erroneous formulas. – Tanoro Aug 24 '16 at 18:10
  • Odd, definitely a bug somewhere in the Excel5 formula parser that tokenises formulae for writing to BIFF format files, but works perfectly well with Excel2007 – Mark Baker Aug 24 '16 at 18:43
  • The environment I am working in does not have ZipArchive, so I haven't been able to use Excel2007. – Tanoro Aug 24 '16 at 21:06

0 Answers0