使用表格使用MySQL数据和PHP创建格式化的Excel电子表格 [英] create formatted excel spreadsheet with MySQL data and PHP using tables
本文介绍了使用表格使用MySQL数据和PHP创建格式化的Excel电子表格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在将数据从MySQL数据库导出到Excel电子表格中.我之前已经找到了执行此操作的代码，并且效果很好.它不会格式化电子表格.这也是我创建的第一个PHP/MySQL网站，数据不必很漂亮.当前项目需要格式化的数据.
I am working on exporting data from a MySQL database into an Excel spreadsheet. I have found code for doing this before and it works quite well. It does not format the spreadsheet. It was also the first PHP/MySQL site I created and the data didn't need to be pretty. The current project requires formatted data.
昨天，我发现一篇文章可能使我能够做我需要做的事情.像个白痴一样，我没有将链接复制下来.我今天找不到.
Yesterday, I found an article that may have allowed me to do what I need to do. Like an idiot, I didn't copy the link down. I can't find it today.
这是我的代码-
<?php
require_once("includes/connection.php");
require_once("includes/functions.php");
// set $closed to 0 for development
$closed = 0;
//create a sting to allow the user to see if s/he is looking at open or closed items
if ($closed) {
$filename = FILENAME."_closed";
} else {
$filename = FILENAME."_open";
}
// $data will hold the result
$data = '<table>';
// is the row a header?
$th = FALSE;
// define the separator character
$sep = '\t';
// array for keys
$thKey = array();
// are we in the first row?
$firstRow = TRUE;
// create the query
$query = "SELECT ";
$query .= "training_requirements.Training, mechanism.mechName, location.locationName, impacted_employees.groupName, ";
$query .= "training_requirements.DateReceived, training_requirements.DateStart, training_requirements.DateDue, ";
$query .= "requester.lastName, requester.firstName, impact.impactName, training_requirements.TimeNeeded, ";
$query .= "priority.priority, training_requirements.Notes ";
$query .= "FROM ";
$query .= "training_requirements, impact, impacted_employees, location, mechanism, requester, priority ";
$query .= "WHERE impact.impactId = training_requirements.impactId ";
$query .= "AND impacted_employees.groupId = training_requirements.impEmpId ";
$query .= "AND location.locationId = training_requirements.trainLocId ";
$query .= "AND mechanism.mechId = training_requirements.mechId ";
$query .= "AND requester.requesterId = training_requirements.requesterId ";
$query .= "AND priority.id = training_requirements.Priority ";
$query .= "AND training_requirements.Closed = $closed ";
$query .= "AND training_requirements.Deleted = 0";
// run the query
$result = executeQuery($connection, $query);
// process the query
if (mysqli_num_rows($result) > 0) {
while ($resource = mysqli_fetch_assoc($result)) {
if (empty($thKey)) {
foreach($resource as $key => $value) {
$thKey[] = $key;
}
}
$data .= '<tr>';
for ($i = 0; $i < count($resource); $i++) {
if ($firstRow) {
// create the header
for ($j = 0; $j < count($resource); $j++) {
$data .= '<th>';
$data .= $thKey[$j];
$data .= '</th>';
}
$data .= '</tr><tr>';
$firstRow = FALSE;
}
$data .= '<td>';
if (isset($resource[$thKey[$i]])) {
$data .= $resource[$thKey[$i]];
} else {
$data .= ' ';
}
$data .= '</td>';
}
$data .= '</tr>';
}
$data .= '</table>';
echo $data;
}
//header('Content-type: application/excel');
//header("Content-Disposition: attachment; filename={$filename}.xls");
//header("Pragma: no-cache");
//header("Expires: 0");
?>
代码继续显示，使我可以在浏览器中显示结果.我在Firefox中找到了合适的表格.当我取消注释底部的标题语句时，我得到一个空白的Excel文件.没有细胞，什么都没有.
The code continues on to allow me to display the results in a browser. I get a proper looking table in Firefox. When I un-comment the header statements at the bottom, I get a blank Excel file. No cells, no nothing.
使用Excel 2007(12.0.6715.5000)SP3 MSO(12.0.6721.5000)，PHP 5.4.24和MySQL 5.5.40.不能使用插件和库.
Using Excel 2007(12.0.6715.5000) SP3 MSO (12.0.6721.5000), PHP 5.4.24, and MySQL 5.5.40. Using plugins and libraries is not an option.
Vern
推荐答案
您的代码仅是来自MySQL的数据，而不是任何excel文件.
your code is just fech data from MySQL and not make any excel file .
在这里，您是制作execl文件的好班
here you are a great class for making execl file
具有纯XML代码，没有任何扩展名
with pure XML code without any extension
<?php
/********************************/
/* Code By Mr Korosh Raoufi */
/* WwW.k2-4u.CoM */
/********************************/
/**
* Simple excel generating from PHP5
*
* @package Utilities
* @license http://www.opensource.org/licenses/mit-license.php
* @author Oliver Schwarz <oliver.schwarz@gmail.com>
* @version 1.0
*/
/**
* Generating excel documents on-the-fly from PHP5
*
* Uses the excel XML-specification to generate a native
* XML document, readable/processable by excel.
*
* @package Utilities
* @subpackage Excel
* @author Oliver Schwarz <oliver.schwarz@vaicon.de>
* @version 1.1
*
* @todo Issue #4: Internet Explorer 7 does not work well with the given header
* @todo Add option to give out first line as header (bold text)
* @todo Add option to give out last line as footer (bold text)
* @todo Add option to write to file
*/
class Excel_XML
{
/**
* Header (of document)
* @var string
*/
private $header = "<?xml version=\"1.0\" encoding=\"%s\"?\>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
/**
* Footer (of document)
* @var string
*/
private $footer = "</Workbook>";
/**
* Lines to output in the excel document
* @var array
*/
private $lines = array();
/**
* Used encoding
* @var string
*/
private $sEncoding;
/**
* Convert variable types
* @var boolean
*/
private $bConvertTypes;
/**
* Worksheet title
* @var string
*/
private $sWorksheetTitle;
/**
* Constructor
*
* The constructor allows the setting of some additional
* parameters so that the library may be configured to
* one's needs.
*
* On converting types:
* When set to true, the library tries to identify the type of
* the variable value and set the field specification for Excel
* accordingly. Be careful with article numbers or postcodes
* starting with a '0' (zero)!
*
* @param string $sEncoding Encoding to be used (defaults to UTF-8)
* @param boolean $bConvertTypes Convert variables to field specification
* @param string $sWorksheetTitle Title for the worksheet
*/
public function __construct($sEncoding = 'UTF-8', $bConvertTypes = false, $sWorksheetTitle = 'Table1')
{
$this->bConvertTypes = $bConvertTypes;
$this->setEncoding($sEncoding);
$this->setWorksheetTitle($sWorksheetTitle);
}
/**
* Set encoding
* @param string Encoding type to set
*/
public function setEncoding($sEncoding)
{
$this->sEncoding = $sEncoding;
}
/**
* Set worksheet title
*
* Strips out not allowed characters and trims the
* title to a maximum length of 31.
*
* @param string $title Title for worksheet
*/
public function setWorksheetTitle ($title)
{
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
$title = substr ($title, 0, 31);
$this->sWorksheetTitle = $title;
}
/**
* Add row
*
* Adds a single row to the document. If set to true, self::bConvertTypes
* checks the type of variable and returns the specific field settings
* for the cell.
*
* @param array $array One-dimensional array with row content
*/
private function addRow ($array)
{
$cells = "";
foreach ($array as $k => $v):
$type = 'String';
if ($this->bConvertTypes === true && is_numeric($v)):
$type = 'Number';
endif;
$v = htmlentities($v, ENT_COMPAT, $this->sEncoding);
$cells .= "<Cell><Data ss:Type=\"$type\">" . $v . "</Data></Cell>\n";
endforeach;
$this->lines[] = "<Row>\n" . $cells . "</Row>\n";
}
/**
* Add an array to the document
* @param array 2-dimensional array
*/
public function addArray ($array)
{
foreach ($array as $k => $v)
$this->addRow ($v);
}
/**
* Generate the excel file
* @param string $filename Name of excel file to generate (...xls)
*/
public function generateXML ($filename = 'excel-export')
{
// correct/validate filename
$filename = preg_replace('/[^aA-zZ0-9\_\-]/', '', $filename);
// deliver header (as recommended in php manual)
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");
// print out document to the browser
// need to use stripslashes for the damn ">"
echo stripslashes (sprintf($this->header, $this->sEncoding));
echo "\n<Worksheet ss:Name=\"" . $this->sWorksheetTitle . "\">\n<Table>\n";
foreach ($this->lines as $line)
echo $line;
echo "</Table>\n</Worksheet>\n";
echo $this->footer;
}
}
?>
用法:
$xls = new Excel_XML('UTF-8', false, 'title text');
$data = array(
1 => array ('column1 text', 'coloumn2 text'),
1 => array ('column1 text', 'coloumn2 text'),
1 => array ('column1 text', 'coloumn2 text'),
);
$xls -> addArray($data);
$xls -> generateXML('file Name'); // its print code to browser use requred header to download
这篇关于使用表格使用MySQL数据和PHP创建格式化的Excel电子表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
