CODEWITHSUNDEEP
phpmysqlphpexcel
user3270211
user3270211

Reputation: 933

PHPExcel print in column and cell

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.';

?>

Upvotes: 1

Views: 2227

Answers (1)

Joe Pietroni
Joe Pietroni

Reputation: 826

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++;

Upvotes: 3

Related Questions