CODEWITHSUNDEEP
phpexcel
Shibu
Shibu

Reputation: 510

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

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

Upvotes: 9

Views: 59126

Answers (4)

Stefano Mtangoo
Stefano Mtangoo

Reputation: 6550

For PHPSpreadsheet the solution is

$sheet->getStyle('B:B')
     ->getNumberFormat()
     ->setFormatCode(NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);

Upvotes: 0

Sonu Chohan
Sonu Chohan

Reputation: 273

$mfisinArr = $this->isObject->getAllDataMfisinQuery();
if(isset($mfisinArr) && !empty($mfisinArr))
{
    $excel_row = 2;
    foreach ($mfisinArr as $key => $value) 
    {
        // PHPExcel Style getting default number format
        $object->getActiveSheet()->getStyle('A'.$excel_row)->getNumberFormat()->setFormatCode('0');
        $object->getActiveSheet()->setCellValueByColumnAndRow(0, $excel_row, sprintf('%09d',$value['security']));
        $object->getActiveSheet()->setCellValueByColumnAndRow(1, $excel_row, $value['name']);
        $object->getActiveSheet()->setCellValueByColumnAndRow(2, $excel_row, $value['mf_isin']);
        $object->getActiveSheet()->setCellValueByColumnAndRow(3, $excel_row, empty($value['mat_dt'])?'':date('d-m-Y',strtotime($value['mat_dt'])));
        $excel_row++;
    }
}

Upvotes: 0

Klompenrunner
Klompenrunner

Reputation: 783

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('$#,###');

Upvotes: 10

Mark Baker
Mark Baker

Reputation: 212412

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);

Upvotes: 14

Related Questions