0

I'm using CodeIgniter 2 and using PHPExcel 1.8 to generate data to Excel 2007

I have convert all the cells to text before calling to fill the cell. And fill column B cell with data like 123456.54321. But the result cell I saw from Ms. Excel 2016, it shows 123456,543 while the value itself in Formula Bar shows 123456,54321.

EDIT : What I expect is, it keeps as 123456.54321 because it's not numerical value. It is an ID number. And i've read this question, but it still not a good way because user needs to setup the Excel

Meanwhile the column cell C with value like 123.4567.54321 still written as is.

All value stored from database are VARCHAR of course, no number at those fields

Here's my code snippet I use

    $excel->setActiveSheetIndex(0);

    $excel->getActiveSheet()->setTitle('Transaction Report');

    $i = 1;
    foreach ($data->result() as $row) {
        $excel->getActiveSheet()->getStyle('A'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $excel->getActiveSheet()->setCellValue('A'.$i, $row->id_transaction);
        $excel->getActiveSheet()->getStyle('B'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $excel->getActiveSheet()->setCellValue('B'.$i, $row->id_courrier);
        $excel->getActiveSheet()->getStyle('C'.$i)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
        $excel->getActiveSheet()->setCellValue('C'.$i, $row->tax_id);
        $i++;
    }

    $filename = 'reporting'.$date_start.'_to_'.$date_end.'.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');

    $objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
    $objWriter->save('php://output');

My question is, how to preserve those dot value when store the data to Excel with format XXXX.XXXXX and if possible, how to make it compatible to all Excel 2007 and later version and locale.

FYI, $row->id_courrier is an ID number which just happen with format like that. 2 group of numbers with dot as separation. That is not numerical value.

Also, if possible, is there any non-hacking way to prevent this misguiding insert to Ms Excel with that kind of format ?

hakre
  • 193,403
  • 52
  • 435
  • 836
poring91
  • 393
  • 7
  • 22
  • The number seems correct, the display in Excel seems correct, too (it's rounding the number while keeping the value) - so perhaps it's just that you want something that is just not appropriate? Decimal separator in your Excel is the comma, not the dot. Why do you want to change that? You might need to change your Desktop systems locale settings to change that btw. - Not a programming problem I conclude. – hakre Jun 18 '17 at 07:35
  • No, that's not what I meant. That is code id which is happen formatted like that (2 group of number with dot as separation) and that id_courrier is like primary key. Not number value. Also, why it is flagged as duplicate from irrelevant question ? – poring91 Jun 18 '17 at 09:25
  • What is the format code within Excel? – hakre Jun 18 '17 at 09:34
  • `setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);` already create the cell as Text and the Excel file generated from that code states that the cell format is Text. But somehow, when it is inserted from that code, the value is still treated as number and it converts dot as comma – poring91 Jun 18 '17 at 09:41
  • 1
    Then it is nothing with the format code. I think you should add a single quote in front to pass the data as verbatim text. -- Are you looking for this? https://stackoverflow.com/a/5046204/367456 – hakre Jun 18 '17 at 09:43
  • I think that will do. Thanks – poring91 Jun 18 '17 at 09:59

0 Answers0