phpexcel -How to change data type for whole column of an excel

phpexcel -How to change data type for whole column of an excel

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Controlling Column-Wide Data Types in PHPExcel

When generating Excel files with PHPExcel (now PHPSpreadsheet), distinguishing between internal data storage and visual presentation is critical. A common stumbling block occurs when numeric values appear as decimals (e.g., 2.00) instead of integers (2), or when you need to force a column like "M" into General format to handle mixed data types correctly. The root issue usually lies in the interaction between cell value types and number formatting styles.

The code snippet provided in your scenario highlights two distinct mechanisms at play: setting the internal value type and applying a number format. You are using PHPExcel_Cell_DataType::TYPE_NUMERIC to store the value, which is correct for numbers. However, simply changing the format string via getStyle('M1:M97')->getNumberFormat()->setFormatCode('0') only affects the visual representation; it does not alter how PHPExcel interprets the underlying data or handles automatic type coercion during import/export cycles.

To change the datatype behavior for an entire column, you must address the style configuration dynamically rather than hardcoding row ranges like M1:M97. Hardcoding specific rows limits your solution and breaks if your dataset grows beyond that range. Instead, you should apply the style to the entire column using a wildcard or by targeting the whole sheet's column definition.

Here is the corrected approach to target the entire column "M":

$objPHPExcel->getActiveSheet()
    ->getStyle('M:M')
    ->getNumberFormat()
    ->setFormatCode('General');

Using 'General' as the format code tells Excel to display numbers without forced decimals and to show text as-is. If you specifically need integer representation (no decimals), use '0'. Note that in PHPExcel, setting the format does not automatically convert existing numeric strings into actual float values if they were loaded as strings from a source.

Handling Automatic Type Coercion

A subtle but frequent pitfall involves how Excel handles cell types upon opening. If you set a column to General format and feed it integer data (like 2), Excel might still display 2.00 depending on the locale or if the underlying value is stored as a float. This happens because PHPExcel's internal representation of numbers often defaults to floating-point precision.

To ensure the column behaves strictly as integers visually and logically, you must combine the format setting with a validation rule or simply rely on the formatting string '0'. However, if your goal is to prevent Excel from auto-converting these cells back to scientific notation or forcing decimals later, sticking to the General format is often safer for mixed columns.

If you are writing this logic inside a loop, avoid iterating row-by-row to apply styles. It is inefficient and unnecessary. The style object applies to the whole column once:

$column = 'M'; // Or any other column letter

$objPHPExcel->getActiveSheet()
    ->getStyle($column . ':M') // Syntax error in original thought, corrected below
    ->getNumberFormat()
    ->setFormatCode('General');

Correction: The correct syntax to target a single column for styling is $column.':'.$column. However, if you strictly want the whole column without specifying start/end explicitly in the range string, 'M:M' is the standard convention.

Trade-offs and Best Practices

When deciding between TYPE_NUMERIC and TYPE_STRING, consider your downstream needs. If the data represents quantities that must be summed later in Excel, forcing them to strings via General format without an underlying numeric type will break formulas. PHPExcel stores numbers as floats internally. Setting the format to '0' or 'General' changes how they look but keeps them mathematically valid for summation.

If you encounter issues where 2.00 persists despite setting the format, check your source data. If the input is a string like "2.00", PHPExcel treats it as text unless explicitly cast. In that case, you must ensure the value passed to setCellValue is actually a numeric type (int or float), not a string representation of a number.

// Correct: ensures internal numeric storage
$objPHPExcel->getActiveSheet()
    ->setCellValue('M1', 2); // Pass int/float, not "2"

$objPHPExcel->getActiveSheet()
    ->getStyle('M:M')
    ->getNumberFormat()
    ->setFormatCode('General');

By separating the data type logic from the presentation layer and applying styles to the full column range dynamically, you ensure consistency regardless of dataset size. This approach scales cleanly and avoids the brittle hardcoded ranges that often cause maintenance headaches in larger applications.

Our network