编写原因
有个开发任务,是我这边要提供一个分类信息模板给合作方公司作为数据对接用的,分类数据是我数据库里面的。这个模板实际上就是一个excel文件。那我就要用php代码生成一份文件了。但是 我 不想使用phpexcel那个插件,因为他太重量了。所以我github上面找到一个轻量级的类库,用来生成。
类库代码
<?php
/**
* Excel_XML
*/
/**
* Class Excel_XML
*
* A simple export library for dumping array data into an excel
* readable format. Supports OpenOffice Calc as well.
*
* @author Oliver Schwarz <oliver.schwarz@gmail.com>
*/
class Excel_XML
{
/**
* MicrosoftXML Header for Excel
* @var string
*/
const sHeader = "<?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\">";
/**
* MicrosoftXML Footer for Excel
* @var string
*/
const sFooter = "</Workbook>";
/**
* Worksheet & Data
* @var array
*/
private $aWorksheetData;
/**
* Output string
* @var string
*/
private $sOutput;
/**
* Encoding to be used
* @var string
*/
private $sEncoding;
/**
* Constructor
*
* Instanciates the class allowing a user-defined encoding.
*
* @param string $sEncoding Charset encoding to be used
*/
public function __construct($sEncoding = 'UTF-8')
{
$this->sEncoding = $sEncoding;
$this->sOutput = '';
}
/**
* Add a worksheet
*
* Creates a new worksheet and adds the given data to it.
* @param string $title Title of worksheet
* @param array $data 2-dimensional array of data
*/
public function addWorksheet($title, $data)
{
$this->aWorksheetData[] = array(
'title' => $this->getWorksheetTitle($title),
'data' => $data
);
}
/**
* Send workbook to browser
*
* Sends the finished workbook to the browser using PHP's header
* directive.
*
* @param string $filename Filename to use for sending the workbook
*/
public function sendWorkbook($filename)
{
if (!preg_match('/\.(xml|xls)$/', $filename)):
throw new Exception('Filename mimetype must be .xml or .xls');
endif;
$filename = $this->getWorkbookTitle($filename);
$this->generateWorkbook();
if (preg_match('/\.xls$/', $filename)):
header("Content-Type: application/vnd.ms-excel; charset=" . $this->sEncoding);
header("Content-Disposition: inline; filename=\"" . $filename . "\"");
else:
header("Content-Type: application/xml; charset=" . $this->sEncoding);
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
endif;
echo $this->sOutput;
}
/**
* Write workbook to file
*
* Writes the workbook into the file/path given as a parameters.
* The method checks whether the directory is writable and the
* file is not existing and writes the file.
*
* @param string $filename Filename to use for writing (must contain mimetype)
* @param string $path Path to use for writing [optional]
*/
public function writeWorkbook($filename, $path = '')
{
$this->generateWorkbook();
$filename = $this->getWorkbookTitle($filename);
if (!$handle = @fopen($path . $filename, 'w+')):
throw new Exception(sprintf("Not allowed to write to file %s", $path . $filename));
endif;
if (@fwrite($handle, $this->sOutput) === false):
throw new Exception(sprintf("Error writing to file %s", $path . $filename));
endif;
@fclose($handle);
return sprintf("File %s written", $path . $filename);
}
/**
* Get workbook output
*
* Just returns the generated workbook content.
*
* @return string Output generated by the class
*/
public function getWorkbook()
{
$this->generateWorkbook();
return $this->sOutput;
}
/**
* Compatibility: Add an array
*
* This method implements compatibility to version 1.1. Though using
* self::addWorksheet surely is an improvement, nobody should be
* forced to rewrite his code.
*
* @param array $data Data to be added
*/
public function addArray($data)
{
$this->addWorksheet('Table1', $data);
}
/**
* Compatibility: Generate XML
*
* This method implements compatibility to version 1.1 and generates
* the excel file.
*
* @param string $filename Filename (without mimetype)
*/
public function generateXML($filename)
{
$filename = $this->getWorkbookTitle($filename);
$filename .= '.xls';
$this->sendWorkbook($filename);
}
/**
* Workbook title correction
*
* Corrects filename (if necessary) stripping out non-allowed
* characters.
*
* @param string $filename Desired filename
* @return string Corrected filename
*/
private function getWorkbookTitle($filename)
{
return preg_replace('/[^aA-zZ0-9\_\-\.]/', '', $filename);
}
/**
* Worksheet title correction
*
* Corrects the worksheet title (given by the user) by the allowed
* characters by Excel.
*
* @param string $title Desired worksheet title
* @return string Corrected worksheet title
*/
private function getWorksheetTitle($title)
{
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
return substr ($title, 0, 31);
}
/**
* Generate the workbook
*
* This is the main wrapper to generate the workbook.
* It will invoke the creation of worksheets, rows and
* columns.
*/
private function generateWorkbook()
{
$this->sOutput .= stripslashes(sprintf(self::sHeader, $this->sEncoding)) . "\n";
foreach ($this->aWorksheetData as $item):
$this->generateWorksheet($item);
endforeach;
$this->sOutput .= self::sFooter;
}
/**
* Generate the Worksheet
*
* The second wrapper generates the worksheet. When the worksheet
* data seems to be more than the excel allowed maximum lines, the
* array is sliced.
*
* @param array $item Worksheet data
* @todo Add a security check to testify whether this is an array
*/
private function generateWorksheet($item)
{
$this->sOutput .= sprintf("<Worksheet ss:Name=\"%s\">\n <Table>\n", $item['title']);
if (count($item['data']))
$item['data'] = array_slice($item['data'], 0, 65536);
foreach ($item['data'] as $k => $v):
$this->generateRow($v);
endforeach;
$this->sOutput .= " </Table>\n</Worksheet>\n";
}
/**
* Generate the single row
* @param array Item with row data
*/
private function generateRow($item)
{
$this->sOutput .= " <Row>\n";
foreach ($item as $k => $v):
$this->generateCell($v);
endforeach;
$this->sOutput .= " </Row>\n";
}
/**
* Generate the single cell
* @param string $item Cell data
*/
private function generateCell($item)
{
$type = 'String';
if (is_numeric($item)):
$type = 'Number';
if ($item{0} == '0' && strlen($item) > 1 && $item{1} != '.'):
$type = 'String';
endif;
endif;
$item = str_replace(''', ''', htmlspecialchars($item, ENT_QUOTES));
$this->sOutput .= sprintf(" <Cell><Data ss:Type=\"%s\">%s</Data></Cell>\n", $type, $item);
}
/**
* Deconstructor
* Resets the main variables/objects
*/
public function __destruct()
{
unset($this->aWorksheetData);
unset($this->sOutput);
}
}
?>
演示代码
<?php
// 引入类库文件
require "php-excel.class.php";
header("Content-type: text/html; charset=utf-8");
// 文件头部
$array[] = ['一级分类编码','一级分类名称','二级分类编码','二级分类名称','三级分类编码','三级分类名称'];
// 文件数据
$array[] = ['101','aaa','102','bbb','103','ccc'];
$xls = new \Excel_XML();
$xls->addWorksheet('Names', $array);
$xls->sendWorkbook('pmp.xls');
效果展示
是不是很简单。代码量很少。所以个人感觉很方便,大家多多学习一起进步