mysql to excel using php

Question

I am exporting database from mysql to excel but I am getting error like this:

and my code is:

<?php 
session_start();
if(!isset($_SESSION['username']) && !isset($_SESSION['password'])) 
{

    header("location:../index.php");
}
//header('Pragma: no-cache');
include("../config.php");
//$id=$_GET['id'];
include '../PHPExcel/IOFactory.php';
include '../PHPExcel/PHPExcel.php';
$sqlquery = "SELECT  ProjectName As Project,
       OfficeContact As Office, IndiaContact As india,
       StartDate As sdate, Notes As notes,
       Status AS status, DueDate AS ddate,
       Client AS client, InHouseProject AS inhouse
    FROM  project
    WHERE  ProjectType='Current projects'
    order by  projectid";

$select_table = mysql_query($sqlquery);


// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Create a first sheet, representing sales data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Project');
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'Office Contact');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'India Contact');
$objPHPExcel->getActiveSheet()->setCellValue('D1', 'Start date');
$objPHPExcel->getActiveSheet()->setCellValue('E1', 'Notes');
$objPHPExcel->getActiveSheet()->setCellValue('F1', 'Status');
$objPHPExcel->getActiveSheet()->setCellValue('G1', 'Due Date');
$objPHPExcel->getActiveSheet()->setCellValue('H1', 'Client');
$objPHPExcel->getActiveSheet()->setCellValue('I1', 'In-house project');
$i=2;
while($row=mysql_fetch_assoc($select_table)){
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $row['Project']);
$objPHPExcel->getActiveSheet()->setCellValue('B'.$i, $row['Office']);
$objPHPExcel->getActiveSheet()->setCellValue('C'.$i, $row['india']);
$objPHPExcel->getActiveSheet()->setCellValue('D'.$i, $row['sdate']);
$objPHPExcel->getActiveSheet()->setCellValue('E'.$i, $row['notes']);
$objPHPExcel->getActiveSheet()->setCellValue('F'.$i, $row['status']);
$objPHPExcel->getActiveSheet()->setCellValue('G'.$i, $row['ddate']);
$objPHPExcel->getActiveSheet()->setCellValue('H'.$i, $row['client']);
$objPHPExcel->getActiveSheet()->setCellValue('I'.$i, $row['inhouse']);
i++;
}
// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Current projects');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="allproject.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
?>

When I try with without while loop it is working with header that I define in code. And I also check that without excel code my php code for database and it's field is working. But with above while loop it is displaying like image above.

Try not to include screenshots that show nothing more than a textual error message. It's almost always better to paste the text of the error in so people can search for it. — tadman, May 27 '16 at 17:20
read this for how to enable error reporting: http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php — Jeff Puckett, May 27 '16 at 17:24
Since Jay's suggestion went unnoticed, let me me clarify it. A "500 Internal Server Error" status code (or a blank page) means that your script is throwing an error but you haven't configured PHP to display error messages. That's something you need to fix before you go further; it's impossible to code properly without the aid of error messages. Here's a [brief explanation](http://stackoverflow.com/a/5680885/13508). The error reporting thumb rule is: show in development, log in production. Everything else is just playing a guessing game. — Álvaro González, May 30 '16 at 08:50
Side note: don't use the legacy `mysql_` extension to write new code. It's been deprecated and unmaintained for several years and it was finally removed from latest PHP branch some months ago. — Álvaro González, May 30 '16 at 08:52
Nice status report. Was there a *question* somewhere in there, or were just sharing some information with us? StackOverflow is *not* a debugging service. — spencer7593, Jun 03 '16 at 22:02

score 2 · Answer 1 · answered Jun 03 '16 at 06:35

use this code

index.php

<?php 
define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","yukti");
error_reporting(0);
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

$setCounter = 0;

$setExcelName = "download_excal_file";

$setSql = "SELECT * FROM login ORDER BY id ASC";

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "\nno matching records found\n";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";
?>

score 2 · Accepted Answer · answered Jun 03 '16 at 12:00

2

you missed $ symbol for variable i at before closing while loop(i++). Keep and check, it may work.

answered Jun 03 '16 at 12:00

Phaneendra Kasalanati

398
5
9

3

More important is for OP to develop *debugging* skills. StackOverflow is *not* a debugging service. Posting questions on StackOverflow is *not* a suitable substitute for debugging. [**https://ericlippert.com/2014/03/05/how-to-debug-small-programs/**](https://ericlippert.com/2014/03/05/how-to-debug-small-programs/) – spencer7593 Jun 03 '16 at 21:58

score 1 · Answer 3 · answered May 30 '16 at 10:03

For as far as I can see, your code looks fine. From my own experience, PHPExcel throws easily 500 errors. Sometimes due to memory shortage, special cell markup, complex formulas, ..

I use almost exactly the same code as you for exporting queries to Excel. This code creates a full export from a select query (with the same headers). Where

$oResult

is your mysqli_query result.

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Me");
$objPHPExcel->getProperties()->setTitle("Export");
$objPHPExcel->getProperties()->setSubject("Export");
$objPHPExcel->getProperties()->setDescription("Export");

$objPHPExcel->setActiveSheetIndex(0);
$iColHeaders = 0;
// Set Headers
while($oFields = mysqli_fetch_field($oResult)) {
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($iColHeaders, 1, $oFields->name);
    $iColHeaders++;
}

// Set data
$iRow=1;
while($oRow = mysqli_fetch_array($oResult)){
    $iRow++; //Skip header row
    for($iCol=0;$iCol<$iColHeaders;$iCol++){
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($iCol, $iRow, $oRow[$iCol]);
    }
}

$objPHPExcel->getActiveSheet()->setTitle('Export');

$sPath = dirname(__FILE__).'/files/';
$sFilename = 'Export_'.date('His').'.xlsx';
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save($sPath.$sFilename, __FILE__);

And this works like a charm, the only difference i see is the

setCellValueByColumnAndRow

instead of the

setCellValue

Hopefully this helps with your issue.

score 0 · Answer 4 · answered Jun 05 '16 at 15:59

0

500 error means you are facing php execution issue. There is now issue in code. This problem was due to disabled virtual Directory Support and that's solved with this command.

sudo chmod -R 755 /var/www

answered Jun 05 '16 at 15:59

Rituraj

24
5

score -1 · Answer 5 · answered Jun 01 '16 at 12:01

You can use this and please format it according to yours:

<?php 
define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS","");
define ("DB_NAME","stock1");
error_reporting(0);
$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
$db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

$setCounter = 0;

$setExcelName = "download_excal_file";   //downloaded excel file name

$setSql = "SELECT * FROM roll_table ORDER BY id ASC";   //to select the data from the database

$setRec = mysql_query($setSql);

$setCounter = mysql_num_fields($setRec);

for ($i = 0; $i < $setCounter; $i++) {
    $setMainHeader .= mysql_field_name($setRec, $i)."\t";
}

while($rec = mysql_fetch_row($setRec))  {
  $rowLine = '';
  foreach($rec as $value)       {
    if(!isset($value) || $value == "")  {
      $value = "\t";
    }   else  {
//It escape all the special charactor, quotes from the data.
      $value = strip_tags(str_replace('"', '""', $value));
      $value = '"' . $value . '"' . "\t";
    }
    $rowLine .= $value;
  }
  $setData .= trim($rowLine)."\n";
}
  $setData = str_replace("\r", "", $setData);

if ($setData == "") {
  $setData = "\nno matching records found\n";
}

$setCounter = mysql_num_fields($setRec);



//This Header is used to make data download instead of display the data
 header("Content-type: application/octet-stream");

header("Content-Disposition: attachment; filename=".$setExcelName."_Reoprt.xls");

header("Pragma: no-cache");
header("Expires: 0");

//It will print all the Table row as Excel file row with selected column name as header.
echo ucwords($setMainHeader)."\n".$setData."\n";
?>

why the downvote?The answer is totally correct.i used the same — aarju mishra, Jun 03 '16 at 09:08

mysql to excel using php

5 Answers5