如何使用PHPExcel将数据导出到excel文件？
如何使用PHPExcel将数据导出到excel文件？
提问于 2018-10-30 05:57:59
我从limesurvey获取了源代码并将PHPExcel库添加到我的limesurvey代码中，以便在单击链接后将数据导出到excel文件。目前，excel文件打开时包含一些虚拟数据，没有任何问题。我需要能够在用户输入调查信息后从Web服务器动态添加数据。我已经查看了一些我找到的网站，但我没有多少运气。谁能帮我吗？
编辑
<?php
$dbhost= "mysql"; //your MySQL Server
$dbuser = "survey"; //your MySQL User Name
$dbpass = "password"; //your MySQL Password
$dbname = "database";
//your MySQL Database Name of which database to use this
$tablename = "questions"; //your MySQL Table Name which one you have to create excel file
// your mysql query here , we can edit this for your requirement
$sql = "Select * from $table ";
//create code for connecting to mysql
$Connect = @mysql_connect($dbhost, $dbuser, $dbpass)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($dbname, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
error_reporting(E_ALL);
require_once '../Classes/PHPExcel.php';
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
// Initialise the Excel row number
$rowCount = 1;
//start of printing column names as names of MySQL fields
$column = 'A';
for ($i = 1; $i < mysql_num_fields($result); $i++)
{
$objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
$column++;
}
//end of adding column names
//start while loop to get data
$rowCount = 2;
while($row = mysql_fetch_row($result))
{
$column = 'A';
for($j=1; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$value = NULL;
elseif ($row[$j] != "")
$value = strip_tags($row[$j]);
else
$value = "";
$objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
$column++;
}
$rowCount++;
}
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="results.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
回答 2
Stack Overflow用户
发布于 2018-10-30 14:42:29
如果你直接复制了这个，那么：
->setCellValue('B2', Ackermann')
应该
->setCellValue('B2', 'Ackermann')
在回答你的问题时：
从limesurvey获取您想要的数据，并使用setCellValue（）将这些数据值存储在您要存储它的单元格中。
/ Tests中的Quadratic.php示例文件可能有助于作为起点：它从输入表单中获取数据并将其设置为Excel工作簿中的单元格。
编辑
一个非常简单的例子：
// Create your database query
$query = "SELECT * FROM myDataTable";
// Execute the database query
$result = mysql_query($query) or die(mysql_error());
// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
// Initialise the Excel row number
$rowCount = 1;
// Iterate through each result from the SQL query in turn
// We fetch each database result row into $row in turn
while($row = mysql_fetch_array($result)){
// Set cell An to the "name" column from the database (assuming you have a column called name)
// where n is the Excel row number (ie cell A1 in the first row)
$objPHPExcel->getActiveSheet()->SetCellValue('A'.$rowCount, $row['name']);
// Set cell Bn to the "age" column from the database (assuming you have a column called age)
// where n is the Excel row number (ie cell A1 in the first row)
$objPHPExcel->getActiveSheet()->SetCellValue('B'.$rowCount, $row['age']);
// Increment the Excel row counter
$rowCount++;
}
// Instantiate a Writer to create an OfficeOpenXML Excel .xlsx file
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
// Write the Excel file to filename some_excel_file.xlsx in the current directory
$objWriter->save('some_excel_file.xlsx');
编辑＃2
使用现有代码作为基础
// Instantiate a new PHPExcel object
$objPHPExcel = new PHPExcel();
// Set the active Excel worksheet to sheet 0
$objPHPExcel->setActiveSheetIndex(0);
// Initialise the Excel row number
$rowCount = 1;
//start of printing column names as names of MySQL fields
$column = 'A';
for ($i = 1; $i < mysql_num_fields($result); $i++)
{
$objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, mysql_field_name($result,$i));
$column++;
}
//end of adding column names
//start while loop to get data
$rowCount = 2;
while($row = mysql_fetch_row($result))
{
$column = 'A';
for($j=1; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$value = NULL;
elseif ($row[$j] != "")
$value = strip_tags($row[$j]);
else
$value = "";
$objPHPExcel->getActiveSheet()->setCellValue($column.$rowCount, $value);
$column++;
}
$rowCount++;
}
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Limesurvey_Results.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
Stack Overflow用户
发布于 2018-10-30 15:01:14
请尝试以下完整示例
<?php
$objPHPExcel = new PHPExcel();
$query1 = "SELECT * FROM employee";
$exec1 = mysql_query($query1) or die ("Error in Query1".mysql_error());
$serialnumber=0;
//Set header with temp array
$tmparray =array("Sr.Number","Employee Login","Employee Name");
//take new main array and set header array in it.
$sheet =array($tmparray);
while ($res1 = mysql_fetch_array($exec1))
{
$tmparray =array();
$serialnumber = $serialnumber + 1;
array_push($tmparray,$serialnumber);
$employeelogin = $res1['employeelogin'];
array_push($tmparray,$employeelogin);
$employeename = $res1['employeename'];
array_push($tmparray,$employeename);
array_push($sheet,$tmparray);
}
header('Content-type: application/vnd.ms-excel');
header('Content-Disposition: attachment; filename="name.xlsx"');
$worksheet = $objPHPExcel->getActiveSheet();
foreach($sheet as $row => $columns) {
foreach($columns as $column => $data) {
$worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);
}
}
//make first row bold
$objPHPExcel->getActiveSheet()->getStyle("A1:I1")->getFont()->setBold(true);
$objPHPExcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
?>
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/-100006191
复制相关文章
相似问题