Format Cells -> "Text"

Topics: User Forum
Jun 25, 2008 at 9:49 AM
Greetings,

I would like to format a cell as "Text." Everything I try seems to turn out as the "General" format. The reason I need the cell to be Text is because I'm outputting several LONG numbers (14-18 digits) in a field and Excel tends to muck them up by turning them into exponents to shorten the display.

Thanks!

Caio
Coordinator
Jun 27, 2008 at 11:01 AM
Even formatted as Test in Excel, longer numbers appear in their exponential form.... formatting as text simply left justifies them.
You'll need to format them using a numeric format mask such as '0'

$PHPExcelObject->getActiveSheet()->getStyle($cell)->getNumberFormat()->setFormatCode('0');

Although you may still have problems with exceptionally long numbers (more than 15 digits).
Note that this problem with long numbers applies within Excel itself, not just PHPExcel
Feb 23, 2009 at 5:12 PM
i want the same thing
not for any single cell but for whole sheet
if i apply it cell by cell the page dies out of timeout
any thing possible here?

thanks,
Sam
Oct 13, 2009 at 11:30 AM

Hi I have the same problem , I wanted to display long digits as text , nothing seems to be working 

$this->workbook->getActiveSheet()->getStyle($cell)->getNumberFormat()->setFormatCode( PHPExcel_Style_NumberFormat::FORMAT_TEXT );

Is also not working , can anyone please tell me how to change the cell format to text so that , long digits could be displayed.

Thanks,Balajee

 

 

Developer
Oct 13, 2009 at 11:52 AM

@balajee_78: What number are you are trying to store?

Oct 13, 2009 at 2:30 PM

Trying to store bank account numbers which are  16 digits long. Also i cannot get the setBold either to working

$this->workbook->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);

But setWidth seems to be working , i doubt whether getStyle even works , i am using excel 2007 format , is there any possible change needed for that.

 

 

Developer
Oct 13, 2009 at 3:21 PM
Edited Oct 13, 2009 at 3:26 PM
balajee_78 wrote:

Trying to store bank account numbers which are  16 digits long. Also i cannot get the setBold either to working

$this->workbook->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);

But setWidth seems to be working , i doubt whether getStyle even works , i am using excel 2007 format , is there any possible change needed for that.

 

Search for "Excel 15 digit limitation"

You have to store those 16-digit bank account numbers as strings. This is probably ok since you probably won't use them as numbers in formulas.

 

$excel = new PHPExcel();

$worksheet = $excel->getActiveSheet();

$worksheet->getCell('A1')->setValueExplicit('1234567890123456', PHPExcel_Cell_DataType::TYPE_STRING);
$worksheet->getStyle('A1')->getFont()->setBold(true);
$worksheet->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

$worksheet->getColumndimension('A')->setWidth(20);

$writer = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$writer->save('16-digit-string.xlsx');

16-digit-string.xlsx

May 28, 2012 at 3:51 PM

I also need to set all the cells in my sheet as "TEXT".

 

The reason of this, is that users with different locales have to fill out this spreadsheet and send it back. I don't want Excel to format the cells as it usually does in terms of dates, decimal points/comma, etc.

 

I just want whatever the user writes to simply stay there, and this can only be achieved when setting all the cells to format -> TEXT.

 

Is this possible to do with PHPExcel?

 

Thanks!!

Coordinator
May 28, 2012 at 9:30 PM
Edited May 28, 2012 at 9:32 PM

Use the setValueExplicit() method: the default dataype argument is PHPExcel_Cell_DataType::TYPE_STRING,

And set the cell number formatting style to PHPExcel_Style_NumberFormat::FORMAT_TEXT

May 29, 2012 at 9:07 PM

Thanks Mark, I got it working for one cell:

$objPHPExcel->getActiveSheet()->getCell('A2')->setValueExplicit('', PHPExcel_Cell_DataType::TYPE_STRING);
$objPHPExcel->getActiveSheet()->getStyle('A2')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

How can I apply this format to the whole sheet?

Coordinator
May 29, 2012 at 10:53 PM
Edited May 29, 2012 at 10:55 PM

You can apply a default style to the whole workbook:

$objPHPExcel->getDefaultStyle()
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

 or you can apply a style to a range of cells in one go:

$objPHPExcel->getActiveSheet()->getStyle('A1:Z26')
    ->getNumberFormat()
    ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);


Though you'll still have to set the cell datatype for each cell individually

Sep 27, 2012 at 8:51 PM

My data comes from a query so I could not just set the value explicitly. I was able to loop through the cells in the column, first getting the value then setting it explicitly as a string. I don't see why you couldn't loop through the entire sheet - but depending on the size of your report that might not be practical.

$worksheet->getStyle('A1:A'.$lastrow)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

for ($row = 1; $row <= $lastrow; $row++) {
$cellval = $worksheet->getCell('A'.$row)->getValue();
$worksheet->getCell('A'.$row)->setValueExplicit($cellval, PHPExcel_Cell_DataType::TYPE_STRING);
}