PHPExcel 简单封装类 [ [ Excel.class ] ]
程序员文章站
2022-06-14 08:10:18
...
实现功能：
（1）读出read();
（2）写入数据write()，可以连续写入。
（3）导出或保存export();
<?php defined('BASEPATH') OR exit('No direct script access allowed');
class Excel {
public $is_down = FALSE;
public $file_name = '';
public $save_path = './';
private $excel;
private $_row = 1;
private $cell_name = array(
'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV',
'AW', 'AX', 'AY', 'AZ'
);
public function __construct() {
require_once APPPATH . 'third_party/PHPExcel.php';
$this->excel = new PHPExcel();
}
function reset()
{
$this->_row = 1;
return $this;
}
function write($title = array(), $data = array(), $options = array())
{
if (isset($options['sheet_name']) && $options['sheet_name'])
{
$this->excel->getActiveSheet(0)->setTitle($options['sheet_name']);
}
if (isset($options['export_time']) && $options['export_time'])
{
$_cnt = count($title);
$this->excel->getActiveSheet(0)->mergeCells('A' . $this->_row . ':' . $this->cell_name[$_cnt - 1] . $this->_row);
$this->excel->setActiveSheetIndex(0)->setCellValue('A' . $this->_row, 'Data Export Time:' . date('Y-m-d H:i:s'));
$this->_row++;
}
if ($title) {
$i = 0;
foreach ($title AS $v) {
$this->excel->setActiveSheetIndex(0)->setCellValue($this->cell_name[$i] . $this->_row, $v);
$i++;
}
$this->_row++;
}
if ($data) {
foreach ($data AS $_v) {
$j = 0;
foreach ($_v AS $_cell) {
$this->excel->getActiveSheet(0)->setCellValue($this->cell_name[$j] . $this->_row, $_cell);
$j++;
}
$i++;
$this->_row++;
}
}
return $this;
}
function export()
{
if (empty($this->file_name)) {
$this->file_name = uniqid(time(), true);
}
$objWrite = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
if ($this->is_down) {
header('pragma:public');
header("Content-Disposition:attachment;filename={$this->file_name}.xls");
$objWrite->save('php://output');
exit;
}
$_savePath = $this->save_path . $this->file_name . '.xlsx';
$objWrite->save($_savePath);
}
function read($file = '', $sheet = 0){
if(empty($file) OR !file_exists($file)) {
die('file not exists!');
}
$objRead = new PHPExcel_Reader_Excel2007();
if(!$objRead->canRead($file)){
$objRead = new PHPExcel_Reader_Excel5();
if(!$objRead->canRead($file)){
die('No Excel!');
}
}
$obj = $objRead->load($file);
$currSheet = $obj->getSheet($sheet);
$columnH = $currSheet->getHighestColumn();
$columnCnt = array_search($columnH, $this->cell_name);
$rowCnt = $currSheet->getHighestRow();
$data = array();
for($_row = 1; $_row <= $rowCnt; $_row++){
for($_column = 0; $_column <= $columnCnt; $_column++){
$cellId = $this->cell_name[$_column].$_row;
$cellValue = $currSheet->getCell($cellId)->getValue();
if($cellValue instanceof PHPExcel_RichText){
$cellValue = $cellValue->__toString();
}
$data[$_row][$this->cell_name[$_column]] = $cellValue;
}
}
return $data;
}
} // end of class.参考如下：http://www.php.cn/php-weizijiaocheng-391228.html
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Excel {
private $excel;
public function __construct() {
require_once APPPATH . 'third_party/PHPExcel.php';
$this->excel = new PHPExcel();
}
/**
* 数据导出
* @param array $title 标题行名称
* @param array $data 导出数据
* @param string $fileName 文件名
* @param string $savePath 保存路径
* @param $type 是否下载 false--保存 true--下载
* @return string 返回文件全路径
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*
* php```
* $this->exportExcel(array('姓名','年龄'), array(array('a',21),array('b',23)), $filename, $path);
* ```
*/
function exportExcel($title = array(), $data = array(), $fileName = '', $savePath = './', $isDown = FALSE)
{
//横向单元格标识
$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
$this->excel->getActiveSheet(0)->setTitle('sheet名称'); //设置sheet名称
$_row = 1; //设置纵向单元格标识
if ($title) {
$_cnt = count($title);
$this->excel->getActiveSheet(0)->mergeCells('A' . $_row . ':' . $cellName[$_cnt - 1] . $_row); //合并单元格
$this->excel->setActiveSheetIndex(0)->setCellValue('A' . $_row, '数据导出：' . date('Y-m-d H:i:s')); //设置合并后的单元格内容
$_row++;
$i = 0;
foreach ($title AS $v) { //设置列标题
$this->excel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . $_row, $v);
$i++;
}
$_row++;
}
//填写数据
if ($data) {
$i = 0;
foreach ($data AS $_v) {
$j = 0;
foreach ($_v AS $_cell) {
$this->excel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + $_row), $_cell);
$j++;
}
$i++;
$_row++;
}
}
//文件名处理
if (!$fileName) {
$fileName = uniqid(time(), true);
}
$objWrite = PHPExcel_IOFactory::createWriter($this->excel, 'Excel2007');
if ($isDown) { //网页下载
header('pragma:public');
header("Content-Disposition:attachment;filename=$fileName.xls");
$objWrite->save('php://output');
exit;
}
$_fileName = iconv("utf-8", "gb2312", $fileName); //转码
$_savePath = $savePath . $_fileName . '.xlsx';
$objWrite->save($_savePath);
return $savePath . $fileName . '.xlsx';
}
/**
* 数据导入
* @param string $file excel文件
* @param string $sheet
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importExecl($file = '', $sheet = 0){
$file = iconv("utf-8", "gb2312", $file); //转码
if(empty($file) OR !file_exists($file)) {
die('file not exists!');
}
$objRead = new PHPExcel_Reader_Excel2007(); //建立reader对象
if(!$objRead->canRead($file)){
$objRead = new PHPExcel_Reader_Excel5();
if(!$objRead->canRead($file)){
die('No Excel!');
}
}
$cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
$obj = $objRead->load($file); //建立excel对象
$currSheet = $obj->getSheet($sheet); //获取指定的sheet表
$columnH = $currSheet->getHighestColumn(); //取得最大的列号
$columnCnt = array_search($columnH, $cellName);
$rowCnt = $currSheet->getHighestRow(); //获取总行数
$data = array();
for($_row=1; $_row<=$rowCnt; $_row++){ //读取内容
for($_column=0; $_column<=$columnCnt; $_column++){
$cellId = $cellName[$_column].$_row;
$cellValue = $currSheet->getCell($cellId)->getValue();
//$cellValue = $currSheet->getCell($cellId)->getCalculatedValue(); #获取公式计算的值
if($cellValue instanceof PHPExcel_RichText){ //富文本转换字符串
$cellValue = $cellValue->__toString();
}
$data[$_row][$cellName[$_column]] = $cellValue;
}
}
return $data;
}
} // end of class.另一个类如下：
<?php
if (!defined('BASEPATH'))
exit('No direct script access allowed');
class Excel {
private $excel;
public function __construct() {
require_once APPPATH . 'third_party/PHPExcel.php';
$this->excel = new PHPExcel();
}
public function load($path) {
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$this->excel = $objReader->load($path);
return $this->excel;
}
public function save($path) {
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save($path);
}
public function stream($filename, $data = null, $datetime_columns = array(), $amount_columns = array()) {
$this->write($data, $datetime_columns, $amount_columns);
header('Content-type: application/ms-excel');
header("Content-Disposition: attachment; filename=\"" . $filename . "\"");
header("Cache-control: private");
$objWriter = PHPExcel_IOFactory::createWriter($this->excel, 'Excel5');
$objWriter->save('php://output');
}
public function write($data = null, $datetime_columns = array(), $amount_columns = array()) {
if ($data == null) {
return;
}
$col = 'A';
foreach ($data[0] as $key => $val) {
$objRichText = new PHPExcel_RichText();
$objPayable = $objRichText->createTextRun(str_replace("_", " ", $key));
$this->excel->getActiveSheet()->getCell($col . '1')->setValue($objRichText);
$col++;
}
$rowNumber = 2;
foreach ($data as $row) {
$col = 'A';
foreach ($row as $cell) {
// format the first column in DateTime format
if (!empty($datetime_columns) && in_array($col, $datetime_columns)) {
$dateValue = PHPExcel_Shared_Date::PHPToExcel($cell + date('Z', $cell));
$this->excel->getActiveSheet()
->setCellValue($col . $rowNumber, $dateValue)
->getStyle($col . $rowNumber)
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::toFormattedString('MM/DD/YYYY HH:MM:SS'));
} else if (!empty($amount_columns) && in_array($col, $amount_columns)) {
// format amount columns in two decimal float type
$this->excel->getActiveSheet()
->setCellValue($col . $rowNumber, $cell)
->getStyle($col . $rowNumber)
->getNumberFormat()
->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_00);
} else {
$this->excel->getActiveSheet()->setCellValueExplicit($col . $rowNumber, $cell, PHPExcel_Cell_DataType::TYPE_STRING);
}
$col++;
}
$rowNumber++;
}
}
public function __call($name, $arguments) {
if (method_exists($this->excel, $name)) {
return call_user_func_array(array($this->excel, $name), $arguments);
}
return null;
}
}
推荐阅读
-
PHP封装类似thinkphp连贯操作数据库Db类与简单应用示例
-
PHP封装的简单连接MongoDB类示例
-
Python操作Oracle数据库的简单方法和封装类实例
-
基于ECMAScript6即ECMAScript2015的javascriptclass类封装的简单使用实例讲解
-
PHP封装的分页类与简单用法示例
-
php简单数据库操作类的封装
-
python使用线程封装的一个简单定时器类实例
-
PHP封装类似thinkphp连贯操作数据库Db类与简单应用示例
-
初探JDBC及简单的查询工具类封装（附带MAVEN的安装及使用）
-
javascript 3d网页 封装简单的Gui控制器视图类 和 ThreeBSP网格组合 ( three.js r114 初探 三)