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

Question

I'm trying to change the datatype for a whole column (for eg: i need to change for "M" Column to general format). Its displaying as 2.00 in quantity field,I need to change this whole column to general format ie. display as "2". But its not changing the datatype.

Here is the code:

$objPHPExcel->getActiveSheet()
    ->setCellValue(
        $aCells[$eExcelColumn] . $eExcelRow, 
        $sCellData , 
        PHPExcel_Cell_DataType::TYPE_NUMERIC
    );

$objPHPExcel->getActiveSheet()->getStyle('M1:M97')
    ->getNumberFormat()
    ->setFormatCode('0');

How to do for the whole column "M" as general format

Answer 1

PHPExcel doesn't support column or row styling: you need to set the style for a range of cells, exactly as you are doing with

$objPHPExcel->getActiveSheet()->getStyle('M1:M97')
->getNumberFormat()
->setFormatCode('0');

if you want General format rather than '0', then set it to General Instead:

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

or

$objPHPExcel->getActiveSheet()->getStyle('M1:M97')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);

Answer 2

You can do a column like this:

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

A complete working example. The result in column A has two 2, column B two 2.00

$sheet = $objPHPExcel->getActiveSheet();

$sheet->getStyle('A:A')
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_GENERAL);

$sheet->getStyle('B:B')
        ->getNumberFormat()
        ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);

$sheet->setCellValue('A1', 2);
$sheet->setCellValue('A2', 2.0);
$sheet->setCellValue('B1', 2);
$sheet->setCellValue('B2', 2.0);

A few more examples for column and row selections that I pulled from my actual base:

$objPHPExcel->getActiveSheet()->getStyle('1:1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A:A')->getNumberFormat()->setFormatCode('yyyy/mm');
$objPHPExcel->getActiveSheet()->getStyle('D:D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('E:E')->getNumberFormat()->setFormatCode('$#,###');