国产99久久久国产精品潘金莲,成人午夜福利视频后入,国产99久久久国产精品免费看

首頁

後端開發(fā)

php教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 25, 2016 am 08:41 AM

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

PHPEXCEL

if(!defined('BASEPATH')) exit('No direct script access allowed');//物資發(fā)料>//物資發(fā)料單明細
class Read_write{
/**
* $name:選擇的類型(CSV,EXCEL2003,2007)
* $titles:標題數(shù)組
* $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;
}
}
/**
* $名稱：
*/
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 = 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
*
* 版權所有(C) 2006 - 2010 PHPExcel
*
* 該函式庫是免費軟體；您可以重新散佈它和/或
* 根據(jù)GNU Lesser General Public 的條款修改它
* 由自由軟體基金會發(fā)布的授權；
* 授權的2.1 版，或（由您選擇）任何更高版本。
*
* 分發(fā)此程式庫的目的是希望它有用，
* 但不提供任何保證;甚至沒有默示保證
*適銷性或特定用途的適用性。請參閱 GNU
* 較小通用公共授權以了解更多詳細資訊。
*
* 您應該已收到GNU 小通用公共許可證
* 與此庫一起的許可證；如果沒有，請寫信給自由軟體
* Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
*
* @category PHPExcel
* @package PHPExcel
* @copyright 版權所有(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
*/
/**錯誤報告*/
error_reporting(E_ALL);
date_default_timezone_set('預設/預設');
/**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;
/**
* 輸出到頁面上的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; //指定特定類型，產(chǎn)生預設值，產(chǎn)生預設值
/*********************************匯出資料開始**************** ************************************/
/** * 產(chǎn)生Excel2007檔案*/ function write_EXCEL20 07( $title=' ',$data ='',$name='') { $objPHPExcel=$this->_excelComm($title,$data='',$name); //將Application基於網(wǎng)頁設計器(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() 方法的預設值。
exit;
}
/**
* 產(chǎn)生Excel2003檔
*/
function write_EXCEL2003($title='',$data='',$name=''){
$objPHPExcel=$this->_excelComm($title,$data,$name);
// 將輸出重新導向到客戶端的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)容處置：附件；檔案名稱=$name. csv");
header('Cache-Control:必須重新驗證,post-check=0,pre-check=0');
header('過期時間：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 ;
}
// 輸出標題
function _writeTitle($title,$objPHPExcel){
//表頭循環(huán)(標題) $tkey = $tkey 1;
$cell = $this->cellArray[$tkey].'1'; //第$tkey欄的第1行,列的識別碼(a..z)
// 增加一些資料//表頭
// $tvalue=mb_convert_encoding($tvalue, "UTF-8 ","GBK");
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cell, $tvalue); //設定第$row列的值(標題)
}
return $objPHPExcel;
}
/ /輸出內(nèi)容
function _writeDatas($data,$objPHPExcel){
//內(nèi)容循環(huán)(資料庫查詢的回傳值)
foreach($data as $key =>$value) {
$ i = 1;
foreach ($value as $mkey =>$ mvalue){ //回傳的型別是array([0]=>array());，所以這裡要循環(huán)它的值，如果裡面的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 $objPHPHPExcel>; function _write_comm($name,$objPHPExcel){
// 重新命名sheet(左下角的標題)
// $objPHPExcel->getActiveSheet()->setTitle($name);
// 將活動工作表索引設定為第一個工作表，因此Excel 將其作為第一個工作表開啟
$objPHPExcel->setActiveSheetIndex(0 ）； // 預設顯示
return $objPHPExcel;
}
/*********************************匯出資料結(jié)束**************** ************************************/
/*********************************讀取資料開始****************** *************************************/
/**
* 使用方法,$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();//取得指定的啟動
/**取得一共有多少列*/
$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列的名稱開始讀取合併的值。
$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);
}
//--------------------------------讀取工作薄資訊開始
/**
* 讀取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);
}
/**>??> /
function read_CSV_Sheet($filePath){
return $this->_get_sheetnames($this->ECSV,$filePath);
}
//---- ------ ----------------------讀取工作薄資訊結(jié)束
/**
* 讀取CSV工作薄名稱
*/
//讀取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);
}
//取得工作薄名稱
function _get_sheetnames($名稱,$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
}
/*********************************讀取資料結(jié)束****************** *************************************/

複製程式碼

[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); //執(zhí)行插入指令
}
$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註解名稱,因為傳進來的有些空數(shù)組,所以必須過濾)
$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);
}