php 使用PHPEXcel導出表數據
項目中需要將表中的數據導出,在網上找了找發現phpexcel蠻好用的.特此分享
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;
}
}
PHPEXCEL ~ 13KB 下載(28)
<?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]代碼
------------------------導入操作------------------------
/**
* $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);
}
本文由用戶 ouhp9343 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。
轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。
本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！