项目中需要将表中的数据导出,在网上找了找发现phpexcel蛮好用的.特此分享
phpexcel
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;
}
}
'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;$currentrowgetcalculatedvalue();
$sql.=''.$temp.''.,;
$currentcolumn++;
$i++;
}
for($currentcolumn='aa';$currentcolumngetcell($address)->getcalculatedvalue().''.,;
}
}else{
for($currentcolumn='a';$currentcolumngetcell($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);
}