1

I know this has been asked a thousand times but what I am currently doing seems to be the general answer I see online and it is still not working correctly.

I am populating an excel document from a SQL query in which one of the columns is a ISBN10 of which can start with a 0 (but not every time) The excel rows are populated with a for each loop of which I have done in the past with no issues I've then added :

    $objPHPExcel->getActiveSheet()->setCellValue('B'.$c,$row['isbn10']);
    $objPHPExcel->getActiveSheet()->getStyle('B'.$c)->getNumberFormat()
                               ->setFormatCode('0000000000');

To state that this field will be 10 digits (at least that is my current understanding that the setFormatCode will do, when the xls file is downloaded however the ISBN column still trims off all leading zeros, I'm a bit stumped with what to do as a lot of answers I've seen say this is the method you need to stop this happening?

Does anyone here have any ideas of what could possibly solve this?

Adrocks__
  • 39
  • 6
  • What Writer are you using? – Mark Baker Oct 20 '15 at 14:30
  • `getNumberFormat` feels wrong. It shouldn't be a number in the first place. – Álvaro González Oct 20 '15 at 14:30
  • @ÁlvaroG.Vicario - well the other option is to use setCellValueExplicit to force a string value – Mark Baker Oct 20 '15 at 14:31
  • @Mark Baker Currently using PHPExcel version 1.7.8, edit sorry - not as old of a version as I thought, just looked the current version is 1.8.0 – Adrocks__ Oct 20 '15 at 14:36
  • Current latest is 1.8.1 from github – Mark Baker Oct 20 '15 at 14:47
  • My bad, Thanks for letting me know I was looking at phpexcel.codeplex. Regardless not as far behind as I was first expecting. – Adrocks__ Oct 20 '15 at 14:51
  • Also, if you're using the CSV Writer, the data is probably being written correctly; but if you then use MS Excel to open that CSV file, then MS Excel will strip off those leading zeroes – Mark Baker Oct 20 '15 at 14:53
  • For the Writer part I'm using the following: $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); The format this saves to is .xls rather than the a CSV format, which is why I'm hoping I can force it to not trim the leading zero off somehow as otherwise we have to add extra stages for this process to do ISBN13 to ISBN10 conversions rather than just what we are provided (from the SQL database) :/ – Adrocks__ Oct 20 '15 at 15:06
  • If you're using the Excel5 Writer, then setting the fomat mask should work without problems, it's using native Excel functionality – Mark Baker Oct 20 '15 at 16:17
  • Would it matter that I'm populating the Excel within the for each loop then using the Excel5 writer? I.e the script currently goes SQL Query>For Each the sql query>$objwriter? – Adrocks__ Oct 20 '15 at 16:22

0 Answers0