shopnc excel导出功能-CSDN博客

本文链接：https://blog.csdn.net/u011851606/article/details/46327941

1 首先你要有这个类

<?php
/**
* 生成Excel文件类
*
* @package    library* www.shopjl.com网店运维技术交流中心为你提供售后服务以便你更好的了解
*/
defined('BYshopJL') or exit('Access Invalid!');
class Excel{
   /**
   * excel文档头(返回的行)
   *
   * 依照excel xml规范。
   * @access private
   * @var string
   */
   private $header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?\>
   <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
   xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
   xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
   xmlns:html=\"http://www.w3.org/TR/REC-html40\">";

   /**
   * excel页脚
   * 依照excel xml规范。
   *
   * @access private
   * @var string
   */
   private $footer = "</Workbook>";

   //设置一列的宽度
   private $colWidth = "";

   /**
   * 文档行(行数组中)
   *
   * @access private
   * @var array
   */
   private $lines = array ();
   /**
   * 工作表(数组)
   *
   * @access private
   * @var array
   */
   private $worksheets = array ();
   /**
   * 单元格样式
   * @access private
   * @var string
   */
   private $cellstyle = array();

   /**
   * 默认单元格数据格式
   * @access private
   * @var string
   */
   private $default_cellformat = "String";

   public function __construct(){
       //设置默认样式
       $this->cellstyle['Default'] = '<Style ss:ID="Default" ss:Name="Normal">
               <Alignment ss:Vertical="Center"/>
               <Borders/>
               <Font ss:FontName="宋体" x:CharSet="134" ss:Size="11" ss:Color="#000000"/>
               <Interior/>
               <NumberFormat/>
               <Protection/>
              </Style>';
   }
   /**
   * 添加单行数据
   *
   * @access private
   * @param array 1维数组
   * @todo 行创建
   */
   private function addRow ($array)
   {
       //初始化单元格
       $cells = "";
       //构建单元格
       foreach ($array as $k => $v){
           $style_str = '';
           if(!empty($v['styleid'])){
               $style_str = 'ss:StyleID="'.$v['styleid'].'"';
           }
           $format_str = $this->default_cellformat;
           if(!empty($v['format'])){
               $format_str = $v['format'];
           }
           $cells .= "<Cell {$style_str} ><Data ss:Type=\"{$format_str}\">{$v['data']}</Data></Cell>\n";
       }
       //构建行数据
       $this->lines[] = "<Row>\n" . $cells . "</Row>\n";
   }
   /**
   * 添加多行数据
   * @access public
   * @param array 2维数组
   * @todo 构造多行
   */
   public function addArray ($array)
   {
       $this->lines = array();
       //构建行数据
       foreach ((array)$array as $k => $v){
           $this->addRow ($v);
       }
   }

   //设置一列的宽度   列号    宽度值要在addWorksheet 前使用可以参考member.php 写法
   public function setWidth($index,$value){
       $this->colWidth .="<Column ss:Index=\"$index\" ss:AutoFitWidth=\"0\" ss:Width=\"$value\" />\n";
   }
   /**
   * 添加工作表
   * @access public
   * @param string $sheettitle 工作表名
   * @todo 构造工作表XML
   */
   public function addWorksheet($sheettitle)
   {
       //剔除特殊字符
       $sheettitle = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $sheettitle);
       //现在,将其减少到允许的长度
       //$sheettitle = substr ($sheettitle, 0, 50);
       $str = "\n<Worksheet ss:Name=\"$sheettitle\">\n<Table ss:DefaultRowHeight=\"20\">\n";
       $str .= $this->colWidth;
       $str .= implode ("\n", $this->lines).
                   "</Table>\n</Worksheet>\n";
       $this->worksheets[] = $str;
   }
   /**
   * 设置单元格样式
   *
   * @access public
   * @param array 样式数组例如： array('id'=>'s_title','Font'=>array('FontName'=>'宋体','Size'=>'12','Bold'=>'1'));
   * 当id为Default时，为表格的默认样式
   */
   public function setStyle ($style_arr){
       if(empty($style_arr)){
           return false;
       }
       $id = $style_arr['id'];
       unset($style_arr['id']);
       $style_str = "<Style ss:ID=\"$id\">";
       foreach($style_arr as $k=>$v){
           $tmp = '';
           foreach((array)$v as $k_item=>$v_item){
               $tmp .= (" ss:$k_item=\"$v_item\"");
           }
           $style_str .= "<$k ".$tmp.'/>';
       }

       $this->cellstyle[$id] = $style_str.'</Style>';
   }
   /**
   * 设置默认单元格格式
   *
   * @access public
   * @param string
   */
   public function setDefaultFormat ($format_str){
       if(empty($style_arr)){
           return false;
       }
       $this->default_cellformat = $format_str;
   }
   /**
   * 生成excel文件
   * 最后生成excel文件,并使用header()函数来将它交付给浏览器。
   * @access public
   * @param string $filename 文件名称
   */
   public function generateXML ($filename)
   {
       $encoded_filename = urlencode($filename);
       $encoded_filename = str_replace("+", "%20", $encoded_filename);
       //头
       $ua = $_SERVER["HTTP_USER_AGENT"];
       header("Content-Type: application/vnd.ms-excel");
       if(preg_match("/MSIE/", $ua)){
           header('Content-Disposition: attachment; filename="'.$encoded_filename.'.xls"');
       }else if(preg_match("/Firefox/", $ua)){
           header('Content-Disposition: attachment; filename*="utf8\'\''.$filename.'.xls"');
       }else{
           header('Content-Disposition: attachment; filename="'.$filename.'.xls"');
       }
       header('Cache-Control: max-age=0');
       echo stripslashes ($this->header);
       //样式
       echo "\n<Styles>";
       foreach((array)$this->cellstyle as $k=>$v){
           echo "\n".$v;
       }
       echo "\n</Styles>";
       //工作表
       echo implode ("\n", $this->worksheets);
       echo $this->footer;
   }

   /**
   * 转码函数
   *
   * @param mixed $content
   * @param string $from
   * @param string $to
   * @return mixed
   */
    public function charset($content, $from='gbk', $to='utf-8') {
        $from = strtoupper($from) == 'UTF8' ? 'utf-8' : $from;
        $to = strtoupper($to) == 'UTF8' ? 'utf-8' : $to;
        if (strtoupper($from) === strtoupper($to) || empty($content)) {
            //如果编码相同则不转换
            return $content;
        }
        if (function_exists('mb_convert_encoding')) {
            if (is_array($content)){
               $content = var_export($content, true);
               $content = mb_convert_encoding($content, $to, $from);
               eval("\$content = $content;");return $content;
           }else {
               return mb_convert_encoding($content, $to, $from);
           }
        } elseif (function_exists('iconv')) {
            if (is_array($content)){
               $content = var_export($content, true);
               $content = iconv($from, $to, $content);
               eval("\$content = $content;");return $content;
           }else {
               return iconv($from,$to,$content);
           }
        } else {
            return $content;
        }
    }
}
?>

其次你要能用
代码大概如下

   //导出全部会员信息
   public function member_export_allOp(){
       $model = Model("member");

      //这段都不用太看就是组合查询条件
       $member_grade = $model->getMemberGradeArr();

       if ($_GET['search_field_value'] != '') {
           switch ($_GET['search_field_name']){
               case 'member_name':
                   $condition['member_name'] = array('like', '%' . trim($_GET['search_field_value']) . '%');
                   break;
               case 'member_email':
                   $condition['member_email'] = array('like', '%' . trim($_GET['search_field_value']) . '%');
                   break;
               case 'member_truename':
                   $condition['member_truename'] = array('like', '%' . trim($_GET['search_field_value']) . '%');
                   break;
           }
       }
       switch ($_GET['search_state']){
           case 'no_informallow':
               $condition['inform_allow'] = '2';
               break;
           case 'no_isbuy':
               $condition['is_buy'] = '0';
               break;
           case 'no_isallowtalk':
               $condition['is_allowtalk'] = '0';
               break;
           case 'no_memberstate':
               $condition['member_state'] = '0';
               break;
       }
       //会员等级
       $search_grade = intval($_GET['search_grade']);
       if ($search_grade >= 0 && $member_grade){
            $condition['member_exppoints'] = array(array('egt',$member_grade[$search_grade]['exppoints']),array('lt',$member_grade[$search_grade+1]['exppoints']),'and');
       }
       //排序
       $order = trim($_GET['search_sort']);
       if (empty($order)) {
            $order = 'member_id desc';
       }

       if (!is_numeric($_GET['curpage'])){
           $count = $model->where($condition)->count();
           $array = array();
           if ($count > self::EXPORT_SIZE ){   //显示下载链接
               $page = ceil($count/self::EXPORT_SIZE);
               for ($i=1;$i<=$page;$i++){
                   $limit1 = ($i-1)*self::EXPORT_SIZE + 1;
                   $limit2 = $i*self::EXPORT_SIZE > $count ? $count : $i*self::EXPORT_SIZE;
                   $array[$i] = $limit1.' ~ '.$limit2 ;
               }
               Tpl::output('list',$array);
               Tpl::output('murl','index.php?act=member&op=member');
               Tpl::showpage('export.excel');
           }else{   //如果数量小，直接下载
               $data = $model->where($condition)->order('member_id desc')->limit(self::EXPORT_SIZE)->select();
               $this->createExcel($data);
           }
       }else{   //下载
           $limit1 = ($_GET['curpage']-1) * self::EXPORT_SIZE;
           $limit2 = self::EXPORT_SIZE;
           $data = $model->where($condition)->order('member_id desc')->limit("{$limit1},{$limit2}")->select();
           $this->createExcel($data);
       }
   }


   private function createExcel($data = array()){
       Language::read('export');
       import('libraries.excel');
       $excel_obj = new Excel();
       $excel_data = array();
       //设置样式

       $excel_obj->setStyle(array('id'=>'s_title','Font'=>array('FontName'=>'宋体','Size'=>'12','Bold'=>'1')));
       //header
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'会员');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'真实姓名');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'登录次数');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'最后登录时间');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'最后登录ip');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'积分');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'可用预存款');
       $excel_data[0][] = array('styleid'=>'s_title','data'=>'冻结预存款');

       foreach ((array)$data as $k=>$v){
           $tmp = array();
           $tmp[] = array('data'=>$v['member_name']);
           $tmp[] = array('data'=>$v['member_truename']);
           $tmp[] = array('data'=>$v['member_login_num']);
           $tmp[] = array('data'=>empty($v['member_login_time'])?"":date('Y-m-d H:i:s',$v['member_login_time']));
           $tmp[] = array('data'=>$v['member_login_ip']);
           $tmp[] = array('data'=>$v['member_points']);
           $tmp[] = array('data'=>'可用'.$v['available_predeposit']);
           $tmp[] = array('data'=>"冻结".$v['freeze_predeposit']);
           $excel_data[] = $tmp;
       }

//设置每列宽度

        $excel_obj->setWidth(1,100);
       $excel_obj->setWidth(2,100);
       $excel_obj->setWidth(3,100);
       $excel_obj->setWidth(4,140);
       $excel_obj->setWidth(5,100);
       $excel_obj->setWidth(6,100);
       $excel_obj->setWidth(7,100);
       $excel_obj->setWidth(8,100);

       $excel_data = $excel_obj->charset($excel_data,CHARSET);
       $excel_obj->addArray($excel_data);
       $excel_obj->addWorksheet($excel_obj->charset('会员信息',CHARSET));
       $excel_obj->generateXML($excel_obj->charset('会员信息',CHARSET).$_GET['curpage'].'-'.date('Y-m-d-H',time()));
   }

行吧就这样吧大概就是 xml 被 excel 识别了