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');
//物資發(fā)料單明細
class Read_write{
/**
* $name:選擇的類型(CSV,EXCEL2003,2007)
* $titles:標題數(shù)組
* $querys:查詢返回的數(shù)組 $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];
}
//-----------------------------------------------預(yù)備保留
//2011-12-21新增CVS導(dǎo)出功能
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:數(shù)據(jù)
*/
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; //當讀取合并文件時,如果第二行為空,則取第一行的名稱
/*********************************導(dǎo)出數(shù)據(jù)開始****************************************************/
/**
* 生成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 允許向輸出緩沖機制寫入數(shù)據(jù)，和 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){
//表頭循環(huán)(標題)
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); //設(shè)置第$row列的值(標題)
}
return $objPHPExcel;
}
//輸出內(nèi)容
function _writeDatas($data,$objPHPExcel){
//內(nèi)容循環(huán)(數(shù)據(jù)庫查詢的返回值)
foreach($data as $key =>$value) {
$i = 1;
foreach ($value as $mkey =>$mvalue){ //返回的類型是array([0]=>array());,所以此處要循環(huán)它的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;
}
/*********************************導(dǎo)出數(shù)據(jù)結(jié)束****************************************************/
/*********************************讀取數(shù)據(jù)開始****************************************************/
/**
* 使用方法,$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,則出現(xiàn)不執(zhí)行下去
$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列的名字開始讀取合并了的值。以后遇到不同的再調(diào)整。
$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);
}
//--------------------------------讀取工作薄信息結(jié)束
/**
* 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
}
/*********************************讀取數(shù)據(jù)結(jié)束****************************************************/[PHP] code
------------------------導(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()) { //默認名是: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));
}
------------------------------導(dǎo)出操作----------------------------------
//導(dǎo)出指定的表字段
public function show_export(){
//-----數(shù)據(jù)庫字段
$field=implode(",",$this->input->post("listCheckBox_show"));//數(shù)據(jù)庫字段
//顯示名稱
$titleArray=$this->input->post("listCheckBox_field");//顯示的字段名稱(字段Comment注解名,因為傳進來的有些空數(shù)組,所以必須過濾)
$title=array();
foreach ($titleArray as $key => $value) {
if (!empty($value)) {
$title[]=$value;
}
}
//---數(shù)據(jù)庫表名
$table=$this->input->post("tableName");
//--數(shù)據(jù)庫表名稱(Comment注釋)
$show_name=$this->input->post("tableComment");
//--導(dǎo)出類型
$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{
//--寫出的數(shù)據(jù)
$datas=$this->mcom_model->queryByField($field,$table);
}
//---開始導(dǎo)出
$this->read_write->write_Factory($title,$datas,$show_name,$type);
}Statement of this Website
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
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Guide: Stellar Blade Save File Location/Save File Lost/Not Saving
4 weeks ago
By DDD
Oguri Cap Build Guide | A Pretty Derby Musume
2 weeks ago
By Jack chen
Agnes Tachyon Build Guide | A Pretty Derby Musume
1 weeks ago
By Jack chen
Dune: Awakening - Advanced Planetologist Quest Walkthrough
3 weeks ago
By Jack chen
Date Everything: Dirk And Harper Relationship Guide
4 weeks ago
By Jack chen
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
