Data in column float to the right side

Topics: Developer Forum
Apr 26, 2015 at 6:30 PM
Hello everyone,

yesterday I downloaded PHPExcel for the first time and made the first steps.
I have a Database with persons in it. Every Person has a name, class and code. I want to print this data into a .xlsx file.

Pretty much everything works fine, but somehow the data in a few columns floats to the right side.
(I'm using LibreOffice to open the file)

Image

This is my code:
<?
//..
// Fetching data from Database and storing it in variable "raw"
// Then continue to create the document

header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="codes_' . $pid . '.xlsx"');
header('Pragma: no-cache');

include '../PHPExcel/Classes/PHPExcel.php';
include '../PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';

$excel = new PHPExcel();



$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(50);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$col = 1;
$class = $raw[0][1].$raw[0][2];

foreach ($raw as $val) {
    
    if($val[1].$val[2] != $class)
    {
        $class = $val[1].$val[2];
        $col = $col + 3;
    }
    
    $excel->getActiveSheet()->SetCellValue('A'.strval($col), $val[0]);
    $excel->getActiveSheet()->SetCellValue('B'.strval($col), $val[1].$val[2]);
    $excel->getActiveSheet()->SetCellValue('C'.strval($col), $val[3]);
    
    $col++;
}

$objWriter = new PHPExcel_Writer_Excel2007($excel);
$objWriter->save("php://output");
?>
I hope someone can help me.

Thank you very much!
Developer
Apr 27, 2015 at 3:02 PM
Hi there,

Excel and LibreOffice's Calc both apply cell alignment based on a cell's value if there is no cell style applied on that specific cell. In your case C1 is right-aligned because it contains a number whereas most other cells like C5 contain strings and are thus left-aligned. If you are going for left-aligned cell content, the easiest solution would be to prepend a single quote to the cell value which will cause Excel/Calc to see a string even if the cell contains a number (example: 1234 would become '1234).

Another way would be to add alignment programmatically:
$phpExcel->getActiveSheet()
    ->getStyle( $phpExcel->getActiveSheet()->calculateWorksheetDimension() )
    ->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
(based on Mark's answer here: http://stackoverflow.com/questions/12614864/phpexcel-how-to-apply-alignment-for-the-whole-document-created-from-mysql-table )

Regards,

Sascha