php uses PHPEXcel to export table data
- 大家讲道理Original
- 2016-11-08 14:10:061167browse
In the project, I need to export the data in the table. I searched online and found that phpexcel is quite easy to use. I would like to share
PHPEXCEL
<?php
if(!defined('BASEPATH')) exit('No direct script access allowed');
//物资发料单明细
class Read_write{
/**
* $name:选择的类型(CSV,EXCEL2003,2007)
* $titles:标题数组
* $querys:查询返回的数组 $query->result_array();
* $filename:保存的文件名称
*/
function write_Factory($titles,$querys,$filename,$name="EXCEL2003"){
$CI = &get_instance();
$filename=mb_convert_encoding($filename, "GBK","UTF-8");
switch ($name) {
case "CSV":
$CI->excel->write_CSV($titles,$querys,$filename);
break;
case "EXCEL2003":
$CI->excel->write_EXCEL2003($titles,$querys,$filename);
break;
case "EXCEL2007":
$CI->excel->write_EXCEL2007($titles,$querys,$filename);
break;
}
}
/**
* $name:
*/
function read_Facotry($filePath,$sql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
$CI = &get_instance();
$name=$this->_file_extend($filePath);
switch ($name) {
case "csv":
$CI->excel->read_CSV($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
break;
case "xls":
$CI->excel->read_2003Excel($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
break;
case "xlsx":
$CI->excel->read_EXCEL2007($filePath,$sql,$sheet,$curRow,$riqi,$merge,$mergeCol);
break;
}
$CI->mytool->import_info("filePath=$filePath,sql=$sql");
}
/**
* 2012-1-14 读取工作薄名称(sheetnames)
*/
function read_sheetNames($filePath){
$CI = &get_instance();
$name=$this->_file_extend($filePath);
$sheetnames;
switch ($name) {
case "csv":
$sheetnames=$CI->excel->read_CSV_Sheet($filePath);
break;
case "xls":
$sheetnames=$CI->excel->read_2003Excel_Sheet($filePath);
break;
case "xlsx":
$sheetnames=$CI->excel->read_EXCEL2007_Sheets($filePath);
break;
}
return $sheetnames;
}
//读取文件后缀名
function _file_extend($file_name){
$extend =explode("." , $file_name);
$last=count($extend)-1;
return $extend[$last];
}
//-----------------------------------------------预备保留
//2011-12-21新增CVS导出功能
public function export_csv($filename,$title,$datas, $delim = ",", $newline = "\n", $enclosure = '"'){
$CI = &get_instance();
$cvs= $this->_csv_from_result($title,$datas,$delim,$newline,$enclosure);
$CI->load->helper('download');
$name=mb_convert_encoding($filename, "GBK","UTF-8");
force_download($name, $cvs);
}
/**
* @param $titles:标题
* @param $datas:数据
*/
function _csv_from_result($titles,$datas, $delim = ",", $newline = "\n", $enclosure = '"'){
$out = '';
// First generate the headings from the table column names
foreach ($titles as $name){
$name=mb_convert_encoding($name, "GBK","UTF-8");
$out .= $enclosure.str_replace($enclosure, $enclosure.$enclosure, $name).$enclosure.$delim;
}
$out = rtrim($out);
$out .= $newline;
// Next blast through the result array and build out the rows
foreach ($datas as $row)
{
foreach ($row as $item)
{
$item=mb_convert_encoding($item, "GBK","UTF-8");
$out .= $enclosure.str_replace($enclosure, $enclosure.$enclosure, $item).$enclosure.$delim;
}
$out = rtrim($out);
$out .= $newline;
}
return $out;
}
}<?php
/**
* PHPExcel
*
* Copyright (C) 2006 - 2010 PHPExcel
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright Copyright (c) 2006 - 2010 PHPExcel (http://www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
* @version 1.7.4, 2010-08-26
*/
/** Error reporting */
error_reporting(E_ALL);
date_default_timezone_set ('Asia/Shanghai');
/** PHPExcel */
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOFactory.php';
/**
* 输出到页面上的EXCEL
*/
/**
* CI_Excel
*
* @package ci
* @author admin
* @copyright 2011
* @version $Id$
* @access public
*/
class CI_Excel
{
//列头,Excel每列上的标识
private $cellArray = array(
1=>'A', 2=>'B', 3=>'C', 4=>'D', 5=>'E',
6=>'F', 7=>'G', 8=>'H', 9=>'I',10=>'J',
11=>'K',12=>'L',13=>'M',14=>'N',15=>'O',
16=>'P',17=>'Q',18=>'R',19=>'S',20=>'T',
21=>'U',22=>'V',23=>'W',24=>'X',25=>'Y',
26=>'Z',
27=>'AA', 28=>'AB', 29=>'AC', 30=>'AD', 31=>'AE',
32=>'AF', 33=>'AG', 34=>'AH', 35=>'AI',36=>'AJ',
37=>'AK',38=>'AL',39=>'AM',40=>'AN',41=>'AO',
42=>'AP',43=>'AQ',44=>'AR',45=>'AS',46=>'AT',
47=>'AU',48=>'AV',49=>'AW',50=>'AX',51=>'AY',
52=>'AZ', 53=>'BA', 54=>'BB', 55=>'BC', 56=>'BD', 57=>'BE',
58=>'BF', 59=>'BG', 60=>'BH', 61=>'BI', 62=>'BJ', 63=>'BK', 64=>'BL');
private $E2003 = 'E2003';
private $E2007 = 'E2007';
private $ECSV = 'ECSV';
private $tempName; //当读取合并文件时,如果第二行为空,则取第一行的名称
/*********************************导出数据开始****************************************************/
/**
* 生成Excel2007文件
*/
function write_EXCEL2007($title='',$data='',$name='')
{
$objPHPExcel=$this->_excelComm($title,$data,$name);
// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8');
header("Content-Disposition: attachment;filename=$name.xlsx");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");
$objWriter->save('php://output'); //output 允许向输出缓冲机制写入数据，和 print() 与 echo() 的方式相同。
exit;
}
/**
* 生成Excel2003文件
*/
function write_EXCEL2003($title='',$data='',$name=''){
$objPHPExcel=$this->_excelComm($title,$data,$name);
//Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel;charset=UTF-8');
header("Content-Disposition: attachment;filename=$name.xls");
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
}
/**
* 生成CSV文件
*/
function write_CSV($title='',$data='',$name=''){
$objPHPExcel=$this->_excelComm($title,$data,$name);
header("Content-Type: text/csv;charset=UTF-8");
header("Content-Disposition: attachment; filename=$name.csv");
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Expires:0');
header('Pragma:public');
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel,'CSV');
$objWriter->save("php://output");
exit;
}
function _excelComm($title,$data,$name){
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();
$objPHPExcel=$this->_writeTitle($title,$objPHPExcel);
$objPHPExcel=$this->_writeDatas($data,$objPHPExcel);
$objPHPExcel=$this->_write_comm($name,$objPHPExcel);
return $objPHPExcel;
}
//输出标题
function _writeTitle($title,$objPHPExcel){
//表头循环(标题)
foreach ($title as $tkey => $tvalue){
$tkey = $tkey+1;
$cell = $this->cellArray[$tkey].'1'; //第$tkey列的第1行,列的标识符(a..z)
// Add some data //表头
// $tvalue=mb_convert_encoding($tvalue, "UTF-8","GBK");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $tvalue); //设置第$row列的值(标题)
}
return $objPHPExcel;
}
//输出内容
function _writeDatas($data,$objPHPExcel){
//内容循环(数据库查询的返回值)
foreach($data as $key =>$value) {
$i = 1;
foreach ($value as $mkey =>$mvalue){ //返回的类型是array([0]=>array());,所以此处要循环它的value,也就是里面的array
$rows = $key+2; //开始是第二行
$mrow = $this->cellArray[$i].$rows; //第$i列的第$row行
// $mvalue=mb_convert_encoding($mvalue, "GBK","UTF-8");
// print_r($mrow."--->".$mvalue);
$objPHPExcel->setActiveSheetIndex(0)->setCellValueExplicit($mrow, $mvalue);
$i++;
}
}
return $objPHPExcel;
}
function _write_comm($name,$objPHPExcel){
// Rename sheet(左下角的标题)
//$objPHPExcel->getActiveSheet()->setTitle($name);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0); //默认显示
return $objPHPExcel;
}
/*********************************导出数据结束****************************************************/
/*********************************读取数据开始****************************************************/
/**
* 使用方法,$insertSql:insert xx (x1,x2,x3,x4) value (
*/
// function _comm_insert($objReader,$filePath,$insertSql,$sheet=2,$curRow=2,$riqi=TRUE){
function _comm_insert($objPHPExcel,$insertSql,$curRow,$merge=FALSE,$mergeCol='B'){
$CI = &get_instance();
$currentSheet = $objPHPExcel->getSheet();//得到指定的激活
/**取得一共有多少列*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
$size=strlen($allColumn);//如果超出Z,则出现不执行下去
$esql="";
for($currentRow = $curRow;$currentRow<=$allRow;$currentRow++){
$sql=$insertSql;
if($size==2){
$i=1;
$currentColumn='A';
while ($i <= 26) {
$address = $currentColumn.$currentRow;
$temp=$currentSheet->getCell($address)->getCalculatedValue();
$sql.='"'.$temp.'"'.",";
$currentColumn++;
$i++;
}
for($currentColumn='AA';$currentColumn<=$allColumn;$currentColumn++){
$address = $currentColumn.$currentRow;
$sql.='"'.$currentSheet->getCell($address)->getCalculatedValue().'"'.",";
}
}else{
for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
if($merge){//如果是读取合并的值,则判断,如果此行的值为NULL,则把前面的tempName赋值给$temp;
if($currentColumn==$mergeCol){//这里先指定从B列的名字开始读取合并了的值。以后遇到不同的再调整。
$temp=$currentSheet->getCell($mergeCol.$currentRow)->getCalculatedValue();
if(empty($temp)){
$temp=$this->tempName;
}else{
$this->tempName=$temp;
}
}else{
$address = $currentColumn.$currentRow;//getValue()
$temp=$currentSheet->getCell($address)->getCalculatedValue();
}
}else{
$address = $currentColumn.$currentRow;//getValue()
$temp=$currentSheet->getCell($address)->getCalculatedValue();
}
$sql=$sql.'"'.$temp.'"'.",";
}
}
$esql=rtrim($sql,",").')';
//echo($esql);
//return;
$CI->db->simple_query($esql);
}
}
/**
* $filePath:读取文件的路径
* $insertSql:拼写的SQL
*/
function read_EXCEL2007($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
$objs=$this->_get_PHPExcel($this->E2007,$filePath,$sheet,$insertSql,$riqi);
$this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
}
/**
* 读取2003Excel
*/
function read_2003Excel($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
$objs=$this->_get_PHPExcel($this->E2003,$filePath,$sheet,$insertSql,$riqi);
$this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge,$mergeCol);
}
/**
* 读取CSV
*/
function read_CSV($filePath,$insertSql,$sheet=0,$curRow=2,$riqi=TRUE,$merge=FALSE,$mergeCol="B"){
$objs=$this->_get_PHPExcel($this->ECSV,$filePath,$sheet,$insertSql,$riqi,$mergeCol);
$this->_comm_insert($objs["EXCEL"],$objs["SQL"],$curRow,$merge);
}
//--------------------------------读取工作薄信息开始
/**
* 读取Excel2007工作薄名称
*/
function read_EXCEL2007_Sheets($filePath){
return $this->_get_sheetnames($this->E2007,$filePath);
}
/**
* 读取2003Excel工作薄名称
*/
function read_2003Excel_Sheet($filePath){
return $this->_get_sheetnames($this->E2003,$filePath);
}
/**
* 读取CSV工作薄名称
*/
function read_CSV_Sheet($filePath){
return $this->_get_sheetnames($this->ECSV,$filePath);
}
//--------------------------------读取工作薄信息结束
/**
* 2012-1-14 --------------------------
*/
//读取Reader流
function _get_Reader($name){
$reader=null;
switch ($name) {
case $this->E2003:
$reader = new PHPExcel_Reader_Excel5();
break;
case $this->E2007:
$reader = new PHPExcel_Reader_Excel2007();
break;
case $this->ECSV:
$reader = new PHPExcel_Reader_CSV();
break;
}
return $reader;
}
//得到$objPHPExcel文件对象
function _get_PHPExcel($name,$filePath,$sheet,$insertSql,$riqi){
$reader=$this->_get_Reader($name);
$PHPExcel= $this->_init_Excel($reader,$filePath,$sheet);
if($riqi){ //如果不需要日期,则忽略.
$insertSql=$insertSql.'"'.$reader->getSheetTitle().'"'.",";//第一个字段固定是日期2012-1-9
}
return array("EXCEL"=>$PHPExcel,"SQL"=>$insertSql);
}
//得到工作薄名称
function _get_sheetnames($name,$filePath){
$reader=$this->_get_Reader($name);
$this->_init_Excel($reader,$filePath);
return $reader->getAllSheets();
}
//加载文件
function _init_Excel($objReader,$filePath,$sheet=''){
$objReader->setReadDataOnly(true);
if(!empty($sheet)){
$objReader->setSheetIndex($sheet);//读取第几个Sheet。
}
return $objReader->load("$filePath");
}
//-------------------------------2012-1-14
}
/*********************************读取数据结束****************************************************/[PHP] code
------------------------导入操作------------------------
/**
* $sql="INSERT INTO ".mymsg::WY_MMB." (dizhi,xingming) VALUES (";
*/
//先上传再读取文件
function upByFile($sql, $url, $curRow = 2, $RIQI = true,$merge = FALSE,$mergeCol='B')
{
$CI = &get_instance();
$config['allowed_types'] = '*'; //充许所有文件
$config['upload_path'] = IMPORT; // 只在文件的路径
$CI->load->library('upload', $config);
if ($CI->upload->do_upload()) { //默认名是:userfile
$data = $CI->upload->data();
$full_name = $data['full_path']; //得到保存后的路径
$full_name = mb_convert_encoding($full_name, "GBK", "UTF-8");
$sheet = $CI->input->post("sheet"); //读取第x列图表
if (empty($sheet)) {
$sheet = 0;
}
$CI->read_write->read_Facotry($full_name, $sql, $sheet, $curRow, $RIQI,$merge,$mergeCol); //执行插入命令
}
$this->alert_msg(mymsg::IMPORT_SUCCESS, site_url($url));
}
------------------------------导出操作----------------------------------
//导出指定的表字段
public function show_export(){
//-----数据库字段
$field=implode(",",$this->input->post("listCheckBox_show"));//数据库字段
//显示名称
$titleArray=$this->input->post("listCheckBox_field");//显示的字段名称(字段Comment注解名,因为传进来的有些空数组,所以必须过滤)
$title=array();
foreach ($titleArray as $key => $value) {
if (!empty($value)) {
$title[]=$value;
}
}
//---数据库表名
$table=$this->input->post("tableName");
//--数据库表名称(Comment注释)
$show_name=$this->input->post("tableComment");
//--导出类型
$type=$this->input->post("type");
//--where 年月
$y_month=$this->input->post("year_month");
if(!empty($y_month)){
$where["riqi"]=$y_month;
$datas=$this->mcom_model->queryByWhereReField($field,$where,$table);
}else{
//--写出的数据
$datas=$this->mcom_model->queryByField($field,$table);
}
//---开始导出
$this->read_write->write_Factory($title,$datas,$show_name,$type);
}Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：PHP generates friendly URL [for SEO]Next article：PHP generates friendly URL [for SEO]