Double quote possibly breaking formatting

Apr 20, 2013 at 6:29 PM
I am creating a spreadsheet where, after a cell containing a double quote ("), the formatting becomes broken. Up to this point, each cell is assigned the formatting specified just fine. After this, no formatting is applied and all cells are shown with "General" formatting.
Coordinator
Apr 22, 2013 at 4:14 PM
Edited Apr 22, 2013 at 4:15 PM
Can you show an example of your format string: formatting strings containing " should work correctly as long as there are matching closing quotes; or were you trying to embed a lietral quote in the format string?
Apr 23, 2013 at 4:13 PM
// Values and Formatting
          $letter = 'A';
          $sheet->SetCellValue($letter.$i, trim($row['part_name'])); 
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->getCell($letter.$i)->setValueExplicit(trim($row['part_number']), PHPExcel_Cell_DataType::TYPE_STRING); // to avoid scientific notation for long strings
          //$sheet->SetCellValue($letter.$i, trim($row['part_number'])); 
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['part_model']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['manufacturer_name']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['system']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['code']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['part_oh_qty'])); 
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); $letter++;
          for($y=0; $y<=max($years_purch,$years_inv); $y++)
          {
            if($y<$years_purch)
            {
              $sheet->SetCellValue($letter.$i, $FYpurchdemand[$y]['OEM_orders']);
              $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0');  
              $letter++; 
            }
            if($y<$years_inv)
            {
              $sheet->SetCellValue($letter.$i, $FYinvdemand[$y]['inv_demand']);
              $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); 
              $letter++; 
            }   
          } 
         // $sheet->SetCellValue('J'.$i, '=H'.$i.'+I'.$i.'/(DATEDIF("2010-09-29", TODAY(),"d")/365)');
          $sheet->SetCellValue($letter.$i, trim($row['part_avg_cost'])); 
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['part_stock_level']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($part_lead_time));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($row['part_reorder_point']));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('0'); $letter++;
          $sheet->SetCellValue($letter.$i, trim($readiness_impact));
            $sheet->getStyle($letter.$i)->getNumberFormat()->setFormatCode('@'); 
Apr 23, 2013 at 4:14 PM
The first 3 rows are displayed with the correct formatting. The 4th row begins with a cell that has a double quote in it and the remainder of the spreadsheet has "General" formatting, instead of the formatting defined.
Apr 23, 2013 at 9:51 PM
I've spent the better part of today continuing to look into this and I'm not so sure it is a double quote which is causing the formatting to break; however, I don't know what it might be. I put together a number of different spreadsheets, all with various data, and on each the formatting broke after 3-5 rows. What might cause this? The data is correct, but the formatting is not getting applied after a certain point.
Coordinator
Apr 23, 2013 at 10:29 PM
I'm not certain what's causing it at the moment, I'm still trying to recreate the problem.

However, rather than setting the style for each cell individually immediately after you write the cell, it's more efficient to set the style for a range of cells in a single call.
$sheet->getStyle('A2:A512')->getNumberFormat()->setFormatCode('@'); 
Sep 20, 2013 at 9:00 PM
Thanks for the styling tip, Mark! I'm still having issues with formats breaking. Any ideas?