MySQL的到Excel使用PHP

问题描述：

我从mysql的导出数据库Excel中但我正在逐渐错误这样的：MySQL的到Excel使用PHP

，我的代码是：

<?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'); 
?>

当我尝试使用while循环时，它使用我在代码中定义的标题。我也检查，没有Excel代码我的php代码数据库，它的领域正在工作。但以上while循环它显示像上面的图像。

查看错误日志中的实际错误。 –

尽量不要包含仅显示文本错误消息的屏幕截图。粘贴错误文本几乎总是更好，以便人们可以搜索它。 – tadman

阅读本文以了解如何启用错误报告：http://*.com/questions/845021/how-to-get-useful-error-messages-in-php –

答

你错过了可变$符号我在关闭while循环（i ++）之前。保持并检查，它可能工作。

更重要的是为OP开发*调试*技能。 *是*不*调试服务。在*上发布问题*不适合替代调试。 [** HTTPS：//ericlippert.com/2014/03/05/how-to-debug-small-programs/**]（https://ericlippert.com/2014/03/05/how-to-debug - 小的程序/） – spencer7593

答

就我所见，您的代码看起来很好。从我自己的经验来看，PHPExcel很容易引发500个错误。有时由于内存不足，特殊的单元格标记，复杂的公式，...

我使用几乎完全相同的代码来将查询导出到Excel。此代码从选择查询（使用相同的标题）创建完整导出。其中

$oResult

是您的mysqli_query结果。

$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__);

这就像一个魅力，我看到的唯一的区别是

setCellValueByColumnAndRow

代替

setCellValue

希望这有助于您解决问题。

答

您可以根据您的使用，并请格式化：

<?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"; 
?>

为什么downvote？答案是完全正确的。我使用相同 –

答

使用此代码

的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"; 
?>

答

500错误意味着您正面临php执行问题。代码中现在有问题。这个问题是由于禁用了虚拟目录支持，并且这个命令解决了这个问题。

sudo chmod -R 755/var/www

MySQL的到Excel使用PHP

相关推荐