PHPExcel print in column and cell

Question

I am new to PHPexcel, trying to fetch data from my database and print the id and title values in it's own columns but right now it's just printing it like this, I want each value to be in each cell:

This is how the excel looks:

enter image description here

Code:

 <?php error_reporting(E_ALL); $username="root"; $password=""; $database="nih_bw"; $sqlsrv="localhost"; date_default_timezone_set('US/Central'); $currenttime=date("m-d-Y"); require_once '../Classes/PHPExcel.php'; $objPHPExcel = new PHPExcel(); $objPHPExcel->getProperties(); function num2alpha($n) { for($r = ""; $n >= 0; $n = intval($n / 26) - 1) $r = chr($n%26 + 0x41) . $r; return $r; } $viewinv = mysql_connect($sqlsrv,$username,$password); if (!$viewinv) { die('Could not connect to SQL server. Contact administrator.'); } mysql_select_db($database, $viewinv) or die('Could not connect to database. Contact administrator.'); $query = "select id, title from measurements;"; $result = mysql_query($query); if ($result = mysql_query($query) or die(mysql_error())) { $objPHPExcel = new PHPExcel(); $objPHPExcel->getActiveSheet()->setTitle('CYImport'.$currenttime.''); $rowNumber = 1; $headings = array('id','Title'); $objPHPExcel->getActiveSheet()->fromArray(array($headings),NULL,'A'.$rowNumber); $rowNumber++; while ($row = mysql_fetch_row($result)) { $col = '0'; foreach($row as $cell) { $objPHPExcel->getActiveSheet()->setCellValue(num2alpha($col).$rowNumber,$cell); $col++; } $rowNumber++; } $objWriter = new PHPExcel_Writer_CSV($objPHPExcel); $objWriter->setDelimiter("\t"); $objWriter->setEnclosure(''); $objWriter->setLineEnding("\r\n"); $objWriter->setSheetIndex(0); $objWriter->save('blah '.$currenttime.'.csv'); header('Content-type: text/csv'); header('Content-Disposition: attachment;filename="CY Import '.$currenttime.'"..csv"'); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); exit(); } echo 'Contact your Administrator. No data received from server.'; ?>

Instead of your num2alpha(), consider using PHPExcel's built-in PHPExcel_Cell::stringFromColumnIndex() method — Mark Baker
– Mark Baker, Commented Apr 26, 2014 at 16:50
But why are you setting the delimiter for a CSV to ''? and the enclosure to ''..... this is the likely cause of your problem: if you have to change the delimiter, at least change it to a valid character like a "\t" that will still be recognised when the generated file is loaded into MS Excel — Mark Baker
– Mark Baker, Commented Apr 26, 2014 at 16:52
Changed it to "\t", see edit. This is how it looks. In one cell. I want it to put id in one column and title in another. dropbox.com/s/y9fk125v1z34gzs/howitlooks.png — user3270211
– user3270211, Commented Apr 26, 2014 at 17:10
I really can't understand what your reader is doing: you're generating a perfectly valid CSV file (if you open it in a text editor, you can see for yourself), but Excel is simply failing to read it correctly.... as you're using PHPExcel, have you tried using the Excel5 or Excel2007 Writers instead of CSV? — Mark Baker
– Mark Baker, Commented Apr 26, 2014 at 17:20

Joe Pietroni · Accepted Answer · 2014-04-26 13:44:15Z

3

The problem is that $col has to be a character if you use setCellValue - to use a number you can use setCellValueByColumnAndRow instead, eg:-

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$rowNumber,$cell);

Alternatively you can convert $col into a character by using the function below:-

function num2alpha($n) { for($r = ""; $n >= 0; $n = intval($n / 26) - 1) $r = chr($n%26 + 0x41) . $r; return $r; }

and then call it as before:-

$col = 0; foreach($row as $cell) { $objPHPExcel->getActiveSheet()->setCellValue(num2alpha($col).$rowNumber,$cell); $col++; } $rowNumber++;

answered Apr 26, 2014 at 13:44

Joe Pietroni

8265 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user3270211 Over a year ago

Thanks, that worked. But now I am not getting the id's out in the excel. Only the titles.

Joe Pietroni Over a year ago

Are you still setting $col = 'A' before the loop ? If so then set it to 0

user3270211 Over a year ago

I have updated my code up, change it to 0. But I want to print the id values in it's own column and the title values in it's own. On the left side how it looks now and the right side show how I want it to look: dropbox.com/s/mi7becx85610rln/howIwant.png

Joe Pietroni Over a year ago

You are calling $objWriter->setDelimiter(''); but you are outputting to CSV. A CSV needs a delimiter or else the fields will run together, so just delete this line.

user3270211 Over a year ago

I sat it to (,) but that didn't make the id and title to come in each column. Can you please help me solve that ?

|

Collectives™ on Stack Overflow

PHPExcel print in column and cell

1 Answer 1

10 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Related