超碰人人超碰人人,gogogo高清免费观看日本,99久久99久久免费精品小说

首頁(yè)

後端開(kāi)發(fā)

php教程

php 使用PHPEXcel匯出表格數(shù)據(jù)

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 25, 2016 am 08:41 AM

專(zhuān)案中需要將表格中的資料匯出,網(wǎng)路上找了找發(fā)現(xiàn)phpexcel蠻好用的.特此分享

PHPEXCEL

if(!defined('BASEPATH')) exit('No direct script access allowed');//物資發(fā)料>//物資發(fā)料單明細(xì)
class Read_write{
/**
* $name:選擇的類(lèi)型(CSV,EXCEL2003,2007)
* $titles:標(biāo)題數(shù)組
* $querys:查詢(xún)傳回的陣列$query->result_array();
* $ filename:儲(chǔ)存的檔案名稱(chēng)
*/
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;
}
}
/**
* $名稱(chēng)：
*/
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 讀取工作薄名稱(chēng)(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];
}
//-------------------------------------------------- -----預(yù)備保留
//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:標(biāo)題
* @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 = rtrimim;
}
$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)

/**
* PHPExcel
*
* 版權(quán)所有(C) 2006 - 2010 PHPExcel
*
* 該函式庫(kù)是免費(fèi)軟體；您可以重新散佈它和/或
* 根據(jù)GNU Lesser General Public 的條款修改它
* 由自由軟體基金會(huì)發(fā)布的授權(quán)；
* 授權(quán)的2.1 版，或（由您選擇）任何更高版本。
*
* 分發(fā)此程式庫(kù)的目的是希望它有用，
* 但不提供任何保證;甚至沒(méi)有默示保證
*適銷(xiāo)性或特定用途的適用性。請(qǐng)參閱 GNU
* 較小通用公共授權(quán)以了解更多詳細(xì)資訊。
*
* 您應(yīng)該已收到GNU 小通用公共許可證
* 與此庫(kù)一起的許可證；如果沒(méi)有，請(qǐng)寫(xiě)信給自由軟體
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright 版權(quán)所有(c) 2006 - 2010 cel /www.codeplex.com/PHPExcel)
* @license http://www.gnu.org/licenses/old-licenses/lgpl- 2.1.txt LGPL
* @版本1.7.4, 2010-08 -26
*/
/**錯(cuò)誤報(bào)告*/
error_reporting(E_ALL);
date_default_timezone_set('預(yù)設(shè)/預(yù)設(shè)');
/**PHPExcel*/
require_once 'Classes/PHPExcel.php';
require_once 'Classes/PHPExcel/IOy.';
require_once 'Classes/PHPExcel/IOy.';
require_once 'Classes/PHPExcel/IOy.';
require_once 'Classes/PHPExcel/IOy.';
require_once 'Classes/PHPExcel/IOy.'PExcel/IOphp;
/**
* 輸出到頁(yè)面上的EXCEL
*/
/**
* CI_Excel
*
* @package ci
* @author admin
* @copyright 2011
* @version $Id$
* @access public
* @version $Id$
* @access public
* @version $Id$
* @access public
* @version $Id$
* @access public
* @version $Id$
* @access public
*/
class CI_Excel
{
//指定，ExcelOriginalSpecification
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',2 =>'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=>'啊', 35=>'AI',36=>'AJ',
37=>'AK',38=>'AL',39=>'AM',40= >'AN',41=>'AO',
42=>'AP',43=>'AQ',44=>'AR',45=>'AS',4 =>'AT',
47=>'AU',48=>'AV',49=>'是',50=>'AX',51=>'否',
52 => 'AZ', 53= >'BA', 54=>'BB', 55=>'BC', 56=>'BD', 57=>'BE',
58=>'BF', =>'BG', 60 =>'BH', 61=>'BI', 62=>'BJ', 63=>'BK', 64=>'BL'); E2003';
private $E2007 = 'E2007';
私人$ECSV = 'ECSV';
私人$tempName; //指定特定類(lèi)型，產(chǎn)生預(yù)設(shè)值，產(chǎn)生預(yù)設(shè)值
/*********************************匯出資料開(kāi)始**************** ************************************/
/** * 產(chǎn)生Excel2007檔案*/ function write_EXCEL20 07( $title=' ',$data ='',$name='') { $objPHPExcel=$this->_excelComm($title,$data='',$name); //將Application基於網(wǎng)頁(yè)設(shè)計(jì)器(Excel 2007) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8'); 版面： Open;filename=$name.xlsx") ; header('Cache-Control: max-age=0'); ") ; $objWriter->save('php://output'); //output 是 print() 和 echo() 方法的預(yù)設(shè)值。
exit;
}
/**
* 產(chǎn)生Excel2003檔
*/
function write_EXCEL2003($title='',$data='',$name=''){
$objPHPExcel=$this->_excelComm($title,$data,$name);
// 將輸出重新導(dǎo)向到客戶(hù)端的Web 瀏覽器(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');
}
}
/**
* 產(chǎn)生CSV檔
*/
function write_CSV($title='',$data='',$name=''){
$objPHPExcel=$this->_excelComm($title ,$data,$name);
header("Content-Type: text/csv;charset=UTF-8");
header("內(nèi)容處置：附件；檔案名稱(chēng)=$name. csv");
header('Cache-Control:必須重新驗(yàn)證,post-check=0,pre-check=0');
header('過(guò)期時(shí)間：0');
header( 'Pragma:public');
$objWriter = new PHPExcel_Writer_CSV($objPHPExcel,'CSV');
$objWriter->save("php://output");
exit;
function _excelComm($title,$data,$name){
// 建立新的PHPExcel 物件
$objPHPExcel = new PHPExcel();
$objPHPExcel=$this- >_writeTitle($title, $objPHPExcel);
$objPHPExcel=$this->_writeDatas($data,$objPHPExcel);
$objPHPExcel=$this->_write_comm($name,$objPHPexcel); > return $objPHPExcel ;
}
// 輸出標(biāo)題
function _writeTitle($title,$objPHPExcel){
//表頭循環(huán)(標(biāo)題) $tkey = $tkey 1;
$cell = $this->cellArray[$tkey].'1'; //第$tkey欄的第1行,列的識(shí)別碼(a..z)
// 增加一些資料//表頭
// $tvalue=mb_convert_encoding($tvalue, "UTF-8 ","GBK");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $tvalue); //設(shè)定第$row列的值(標(biāo)題)
}
return $objPHPExcel;
}
/ /輸出內(nèi)容
function _writeDatas($data,$objPHPExcel){
//內(nèi)容循環(huán)(資料庫(kù)查詢(xún)的回傳值)
foreach($data as $key =>$value) {
$ i = 1;
foreach ($value as $mkey =>$ mvalue){ //回傳的型別是array([0]=>array());，所以這裡要循環(huán)它的值，如果裡面的array
$rows = $key 2; //開(kāi)始是第二行
$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 $objPHPHPExcel>; function _write_comm($name,$objPHPExcel){
// 重新命名sheet(左下角的標(biāo)題)
// $objPHPExcel->getActiveSheet()->setTitle($name);
// 將活動(dòng)工作表索引設(shè)定為第一個(gè)工作表，因此Excel 將其作為第一個(gè)工作表開(kāi)啟
$objPHPExcel->setActiveSheetIndex(0 ）； // 預(yù)設(shè)顯示
return $objPHPExcel;
}
/*********************************匯出資料結(jié)束**************** ************************************/
/*********************************讀取資料開(kāi)始****************** *************************************/
/**
* 使用方法,$insertSql:insert xx (x1,x2,x3,x4) value (
*/
// 函數(shù)_comm_insert($objReader , $filePath,$insertSql,$sheet=2,$curRow=2,$riqi=TRUE){
函數(shù)_comm_insert($objPHPExcel,$insertSql,$curRow,$merge=FALSE,$mergeCol='B' ) {
$CI = &get_instance();
$currentSheet = $objPHPExcel->getSheet();//取得指定的啟動(dòng)
/**取得一共有多少列*/
$allColumn = $ currentSheet ->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
$size=strlen($allColumn);//如果超出Z，則出現(xiàn)不執(zhí)行下去
$esql="";
for($currentRow = $curRow;$currentRow $sql=$insertSql;
if($size==2){
$i=1;
$currentColumn='A';
while ($i $address = $currentColumn.$currentRow;
$temp=$currentSheet->getCell($address)->getCalculatedValue();
$sql. ='"'.$temp.'"'.",";
$currentColumn ;
$i ;
}
for($currentColumn='AA';$currentColumn $address = $currentColumn.$currentRow;
$sql.='"'.$currentSheet->getCell($address)->getCalculatedValue().'"'." ," ;
}
}else{
for($currentColumn='A';$currentColumn if($merge){//如果是$currentColumn ){
if($merge){//如果是讀取合併的值，則判斷，如果此行的值為NULL，則把前面的tempName賦值給$temp; if($currentColumn==$mergeCol){//這裡先指定從B列的名稱(chēng)開(kāi)始讀取合併的值。
$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)->getCalculated();
}
$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,$merge= "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,$merge= "B"){
$objs=$this->_get_PHPExcel($this->ECSV,$filePath,$sheet,$insertSql,$riqi,$mergeCol);
$this->_comm_insert ($objs ["EXCEL"],$objs["SQL"],$curRow,$merge);
}
//--------------------------------讀取工作薄資訊開(kāi)始
/**
* 讀取Excel2007工作薄名
*/
function read_EXCEL2007_Sheets($filePath){
return $this->_get_sheetnames($this->E2007,$filePath);
}
}
* 讀取2003Excel工作薄名稱(chēng)
*/
function read_2003Excel_Sheet($filePath){
return $this->_get_sheetnames($this->E2003,$filePath);
}
/**>??> /
function read_CSV_Sheet($filePath){
return $this->_get_sheetnames($this->ECSV,$filePath);
}
//---- ------ ----------------------讀取工作薄資訊結(jié)束
/**
* 讀取CSV工作薄名稱(chēng)
*/
//讀取Reader流
function _get_Reader($name){
$reader=null;
switch ($name) {
case $this->E2003:
$reader = new PHPExcel_Reader_Excel5 (); 案例$this->E2007:
$reader = new PHPExcel_Reader_Excel2007();
中斷;
案例$this->ECSV:
$ reader = new??> 案例$this->ECSV:
$ reader = new PHPcm. > 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){ //如果不需要日期，則忽略。
}
return array("EXCEL"=>$PHPExcel,"SQL"=>$insertSql);
}
//取得工作薄名稱(chēng)
function _get_sheetnames($名稱(chēng),$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);//讀取第幾個(gè)Sheet。
}
return $objReader->load("$filePath");
}
//--------------------- ----------2012-1-14
}
/*********************************讀取資料結(jié)束****************** *************************************/

複製程式碼

[PHP]代碼

------------------------導(dǎo)入操作------------ ------------
/**
* $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()) { //預(yù)設(shè)名稱(chēng)是:userfile
$data = $CI->upload->data();
$full_name = $ data['full_path']; //得到儲(chǔ)存後的路徑
$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); //執(zhí)行插入指令
}
$this->alert_msg(mymsg ::IMPORT_SUCCESS, site_url($url));
}
----------------------------- -匯出操作----------------------------------
//匯出指定的表格欄位
public function show_export(){
//-----資料庫(kù)欄位
$field=implode(",",$this->input->post("listCheckBox_show"));//資料庫(kù)欄位
//顯示名稱(chēng)
$titleArray=$this->input->post("listCheckBox_field");//顯示的欄位名稱(chēng)(欄位Comment註解名稱(chēng),因?yàn)閭鬟M(jìn)來(lái)的有些空數(shù)組,所以必須過(guò)濾)
$title=array();
foreach ($titleArray as $key => $value) {
if (!empty($value)) {
$title[]=$value;
}
}
//---資料庫(kù)表名
$table=$this->input->post("tableName");
//--資料庫(kù)表名稱(chēng)( 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{
//--寫(xiě)出的資料
$datas=$this->mcom_model->queryByField($field,$table);
}
//---開(kāi)始匯出
$this-> read_write->write_Factory($title,$datas,$show_name,$type);
}