setting cell properties

Topics: User Forum
Sep 25, 2009 at 8:46 AM

How can avoid text wraping in  a .xlsx file?

The file i am creating having long column name and auto filter is also set for these fields.

and in one column I want to store the  numbers like 0001, 0002, 0003

but I am only getting the numbers without 0 like 1, 2, 3 ..

 

Please suggest what to do these issues?

 

Coordinator
Sep 25, 2009 at 9:48 AM
$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('0000');

should work

Sep 25, 2009 at 12:13 PM

I tried this but this is not printing all number in the desired format. 

again I tried with

$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit($recordsRow['emp_id'], PHPExcel_Cell_DataType::TYPE_STRING);

 

This gives the desired out put.

Developer
Sep 25, 2009 at 1:35 PM

@vishalsahu: Generally, you should not store numbers as strings. What is not working with the code Mark posted?

Coordinator
Sep 25, 2009 at 2:07 PM

Storing numbers as string values can have some interesting side effects if you subsequently want to use these values in formulae, so it's not something that we'd recommend

 

/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
require_once '../Classes/PHPExcel.php';
/** PHPExcel_IOFactory */
require_once '../Classes/PHPExcel/IOFactory.php';
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
// Add some data
$objPHPExcel->setActiveSheetIndex(0);
for ($i = 1; $i <= 10000; ++$i) {
 $objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $i);
}
$objPHPExcel->getActiveSheet()->getStyle('A1:A10000')->getNumberFormat() ->setFormatCode('0000');
// Save Excel file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save(str_replace('.php', '.xls', __FILE__));
Sep 26, 2009 at 6:13 AM
koyama wrote:

@vishalsahu: Generally, you should not store numbers as strings. What is not working with the code Mark posted? 

 

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('0000');

When I used the above mentioned code,

I was not getting all the numbers formatted in the desired format.

Only few numbers were changed. Rests were again printed without preceding 0.

Coordinator
Sep 26, 2009 at 6:28 PM
vishalsahu wrote:
koyama wrote:

@vishalsahu: Generally, you should not store numbers as strings. What is not working with the code Mark posted? 

 

$objPHPExcel->getActiveSheet()->getStyle('A1')->getNumberFormat() ->setFormatCode('0000');

When I used the above mentioned code,

I was not getting all the numbers formatted in the desired format.

Only few numbers were changed. Rests were again printed without preceding 0.

 Very unusual.

The code I used above formatted correctly for every number between 1 and 10,000. Can you give us any idea of what values weren't formatted correctly?

Mar 12, 2013 at 5:26 PM
Edited Mar 12, 2013 at 5:30 PM
greetings,
costs me some hours... ( at least i wanted to format a cell as currency and didnt work so this simple test, and sory for replying in this old post, but i thought it fits here )

$objPHPExcel = new PHPExcel();
...
$objPHPExcel->getActiveSheet()->setCellValue('I26' , '013');
$objPHPExcel->getActiveSheet()->getStyle('I26')->getNumberFormat()->setFormatCode('0000');
->cell is not formated... when its the last cell in the sheet... or the onlyone?!

doing this formats both:

$objPHPExcel->getActiveSheet()->setCellValue('I27' , '013');
$objPHPExcel->getActiveSheet()->getStyle('I27')->getNumberFormat()->setFormatCode('0000');

same issue as: ( nothing in the sheet )

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->getCell('A1')->setValueExplicit($psRechnungszeitraum ,PHPExcel_Cell_DataType::TYPE_STRING);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save($loRechnung->lsFilePath . $loRechnung->lsFilename);

setting another cell, both appear...