Preserving Leading Zeros and Mixed Text in PHPExcel Arrays
When you use PHPExcel (or its modern successor, PHPSpreadsheet) to populate a workbook from an array using fromArray(), it is tempting to assume that the library simply dumps your data into cells. In reality, there is a distinct difference between how PHP represents a value and how Excel interprets a numeric string versus a text string.
If your array contains values like 01, 02, or 01A, and you simply pass them to fromArray(), Excel often treats the leading portion (01) as a number. Once Excel renders that number, it strips the leading zero by default, displaying just 1. This behavior happens because Excel automatically formats cells containing numbers with the general format, which suppresses insignificant leading zeros.
You might try to fix this by setting the entire column or row to text format immediately before populating the sheet. The code snippet below attempts to apply a FORMAT_TEXT style to the header row (Row 1) and then load the data into Row 2:
$objPHPExcel->setActiveSheetIndexByName('BlocksList');
$objPHPExcel->getActiveSheet()->fromArray($blockNames, null, 'A2');
$latestBLColumn = $objPHPExcel->getActiveSheet()->getHighestDataColumn();
$column = 'A';
$row = 1;
for ($column = 'A'; $column != $latestBLColumn; $column++) {
$objPHPExcel->getActiveSheet()->getStyle($column.$row)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );
}
While this approach seems logicalâloading the data first and then styling itâthe order of operations in fromArray() is critical. When you invoke fromArray(), the library calculates column widths and cell styles based on the content as it exists at that moment. If your array contains numeric strings like "01", PHPExcel often converts them to integers (1) internally before placing them into the cells, regardless of any pre-existing styles in the row. By the time you loop through the columns to set the format code to FORMAT_TEXT, the leading zeros have already been lost because the data conversion happened during the import step.
The core issue is that fromArray() does not respect the cell style of the destination row when importing mixed alphanumeric data like "01A". It evaluates the first character: if it is a number, it assumes a numeric value and strips leading zeros. If it encounters "01A", PHPExcel might treat it as text to preserve the "A", but it still converts pure numeric strings like "01" into numbers.
To solve this reliably, you must ensure that the cells intended to hold these values are explicitly defined as Text before fromArray() processes them, and more importantly, you must ensure the source data in your PHP array is treated as text throughout the process.
However, there is a specific nuance with the fromArray() method regarding how it handles mixed data types. If your $blockNames array contains values like 01, PHP will convert that to the integer 1. When you then try to style the cell as Text after the fact, the damage is already done because the cell's internal value is an integer, not a string.
The most robust solution involves ensuring your input data remains strings and applying the text format to the specific cells before calling fromArray(), or using a different import method that respects existing styles more strictly. If you are forced to use fromArray(), you must pre-configure the cell styles for the exact range of cells where the data will land, rather than guessing based on the highest column found after the fact.
Consider this alternative approach: define the style for the specific range first, then import. You can calculate the target range dynamically or hardcode it if the structure is fixed. For example, if you know your data always starts at A2, set the format for columns A through N (or however many columns you anticipate) in row 2 before calling fromArray().
$objPHPExcel->getActiveSheet()->getStyle('A:N')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
$objPHPExcel->getActiveSheet()->fromArray($blockNames, null, 'A2');
By setting the format on the column range before the import, you instruct PHPExcel to treat the incoming string values as literal text characters, preventing it from converting "01" into 1. This preserves both the leading zeros and any trailing letters like in 01A.
It is also worth noting that if your data contains mixed types (e.g., some rows are purely numeric while others have letters), you might need to handle specific columns individually. If a column contains 02, 03, 04 without letters, setting the format to Text in Excel will display them as 02, 03, 04. If you require them to look like numbers but keep the zeros, you would need to use a custom number format string like "00" or "@.0", though this gets complicated if letters are involved later in the same column.
Ultimately, the order of operations matters significantly when manipulating spreadsheets programmatically. Always prepare the cell formatting state before populating it with data that requires special character preservation.
