getHorizontal returns alwas 'general'

Jan 29, 2011 at 9:42 PM

Hello,

I've got a problem with the alignment of cells. When I try to read an xls file the returned alignment of a cell is always 'general'.

Here's the code I use:

$align = $style->getAlignment()->getHorizontal();
echo $align."<br />";
if($align == "general")
{
	if((string)(floatval($text)) === $text)
		$align = "right";
	else
		$align = "left";
}

With the echo command I read the returned value. First I thougt, general would be the default value, but it's always returned.

 

Does anyone know, how I can solve this problem?

 

Thank you in advance!

Coordinator
Jan 29, 2011 at 10:10 PM

An alignment of "general" means that Excel chooses the alignment to use for that cell based on the datatype when it displays the cell: numbers are then displayed right justified, strings displayed left justified, and booleans in the centre of the cell, etc. Most cells in any Excel workbook will be "general" aligned, unless you have specifically set an alignment.

Jan 30, 2011 at 5:00 PM

But why it does MS Excel correctly and PHPExcel not? I've uploaded an example file:

http://docs.sxnet.eu/read.php

And see the original file in Excel:

http://docs.sxnet.eu/test.xls

Coordinator
Jan 30, 2011 at 5:39 PM
Edited Jan 30, 2011 at 5:54 PM

Put simplistically, MS Excel comprises two components: the MS Excel Engine, and the MS Excel User Interface (UI). The MS Excel Engine holds the formatting information as "general"... when you're viewing the spreadsheet through the MS Excel UI, the MS Excel UI asks the MS Excel Engine what alignment it should use to display each cell. If the MS Excel Engine tells the MS Excel UI that this cell alignment is "left" or "right" or "center" or "justified", then it is telling the MS Excel UI specifically to use that value. However, if the MS Excel Engine tells the MS Excel UI that this cell alignment is "general", then it is telling the MS Excel UI that it (the UI) should choose the aliggnment, and the MS Excel UI then interprets that in different ways, depending on the data type.

PHPExcel is not wrong, nor is it doing anything different to the MS Excel engine.

PHPExcel represents the MS Excel Engine, and it is your code that is the UI. So if PHPExcel tells you that the alignment is "right", it is instructing your code specifically to align that cell to the right". But for most cells, PHPExcel holds the alignment as "general" in exactly the same way as the MS Excel Engine. When your script (acting as the MS Excel UI) asks the Engine (PHPExcel) for a cell alignment, PHPExcel tells you that it is "general" in exactly the same way that the MS Excel Engine, and it is up to your UI code to interpret that as you want to, perhaps in the same way as the MS Excel UI (based on data type), but you could do whatever you wanted with it.

Looking at your worksheet, using the following code:

$sheet = $objPHPExcel->getActiveSheet();
$row = $sheet->getHighestRow();
$col = $sheet->getHighestColumn();
$col++;
for($r = 1; $r <= $row; ++$r) {
 for($c = 'A'; $c != $col; ++$c) {
  $hAlign = $objPHPExcel->getActiveSheet()->getStyle($c.$r)->getAlignment()->getHorizontal();
  echo $c.$r,' => ',$hAlign,'<br />';
 }
}

I get the following result:

A1 => right
B1 => right
C1 => right
A2 => right
B2 => right
C2 => right
A3 => right
B3 => right
C3 => right
A4 => right
B4 => right
C4 => right
A5 => right
B5 => right
C5 => right
A6 => right
B6 => right
C6 => right
A7 => right
B7 => right
C7 => right
A8 => right
B8 => right
C8 => right
A9 => right
B9 => right
C9 => right
A10 => right
B10 => right
C10 => right

which is exactly what I'd expect to see, because you've explicitly set all those cells to be right-aligned... and not a mention of general anywhere.

 

Jan 30, 2011 at 6:53 PM
Edited Jan 30, 2011 at 7:18 PM

Ok, thank you. Your answer helped me. Your posted code didn't solve the problem, but I found it. At the beginning of my document I used:

$objReader->setReadDataOnly(true);

 

I thougt this command sets something like a read-only flag, but it seems like it is for reading just the contents of cells or something.

When I remove the line it works.

 

Thank you for your hint!

 

//edit:

OK, but when I save the file by PHPExcel the columns are not counted by

$columns = $sheet->getHighestColumn();

This always returns the value 1. When I read a file saved by Excel, everything's ok.

(only when I remove the $objReader->setReadDataOnly(true);)

 

Check this one:

http://docs.sxnet.eu/saves/xls/test.xls

Coordinator
Jan 30, 2011 at 7:28 PM
nelf wrote:

$objReader->setReadDataOnly(true);

I thougt this command sets something like a read-only flag, but it seems like it is for reading just the contents of cells or something.

 setReadDataOnly(true) means read only the Data from a workbook, ignoring any formatting information.

 

nelf wrote:

OK, but when I save the file by PHPExcel the columns are not counted by

$columns = $sheet->getHighestColumn();

This always returns the value 1. When I read a file saved by Excel, everything's ok.

Again, I don't understand your problem. I've run the following code against your workbook:

$sheet = $objPHPExcel->getActiveSheet();
$row = $sheet->getHighestRow();
$col = $sheet->getHighestColumn();
echo 'Highest Row is ',$row,'<br />';
echo 'Highest Column is ',$col,'<br />';

and it gives:

Highest Row is 10
Highest Column is IU

 

Jan 31, 2011 at 12:58 PM

Ok, thank you.

I had an issue with getting the correct letter from a number - that was the problem.

Now everything works!