Worksheet direction

Topics: Developer Forum
Nov 5, 2012 at 9:15 PM

Hi,

I have a script where someone uploads an xlsx file, which is then read and minor changes made before the script then sends it back to them.  When I get the file back, the direction of the spreadsheet has changed to right to left.  The data is still in the correct cells, but cell A1 is shown in the top right corner, and B1 is to the left of it and so on.

 

What have I done wrong?  This is my code that outputs the file back:


	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header('Content-Disposition: attachment;filename="validated.xlsx"');
	header('Cache-Control: max-age=0');
	$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter->save('php://output');	
	exit();

Coordinator
Nov 6, 2012 at 9:08 PM

Right-to-left orientation of worksheets is generally only enabled by calling

// right-to-left worksheet
$objPHPExcel->getActiveSheet()->setRightToLeft(true);

This is picked up by the Excel2007 Reader if the sheetview that is defined for that worksheet has the "rightToLeft" attribute set with a value that equates to boolean TRUE.

If you could upload a (sanitized) version of this workbook where a sheet demonstrates this issue, then I could investigate further why the uploaded file is left-to-right but the modified file is right-to-left; but in the interim you could force the worksheet back to the more normal left-to-right by using $objPHPExcel->getActiveSheet()->setRightToLeft(false);

Nov 6, 2012 at 9:36 PM

Hi Mark,

Thanks for you help.  Adding setRightToLeft(false) in has fixed the problem and I'm going to leave that in there indefinitely now anyway, but if you do want to see the sheets, I've uploaded them to here:

http://www.freefilehosting.net/phpexcelproblem

There are two files in the zip. new_entries.xlsx is the file I am uploading. validated.xlsx is the file that is then downloaded after I've changed some background colors and added a value to two cells.  I'm using version 1.7.8, and in-case it matters, I'm using Libre Office on Linux.

Coordinator
Nov 6, 2012 at 10:22 PM

Problem (at least) identified from those files: for boolean attributes (such as the right-to-left setting), MS Excel uses 0 and 1, whereas the file you sent me has the words "false" and "true".... I'm pretty sure that Open Office uses 0 and 1 like Excel itself, but this seems to be one of the ways in which Libre Office has changed the way it saves as xlsx.

I'm not sure why "false" is being treated as a boolean TRUE; but knowing the problem, I can fix it. It will take a while to work through all boolean attributes and settings in the Excel 2007 reader code changing them in such a way that either the word or the numeric will be evaluated correctly; but I'll try and make sure that it's completed before the 1.7.9 release (which should be sometime around New Year).