使用phpExcel库构建一个导出excel的通用类

来源：互联网发布：加州枪击知乎编辑：程序博客网时间：2024/05/17 18:19

废话不多说，直接上代码：
phpExcel库可以去官网上下载

<?php/** * Created by PhpStorm. * User: Administrator * Date: 2016/8/26 * Time: 15:09 */  class Excel{      /**       * @param $map excel中的列与数据库列名的映射       * @param $map excel中的列与列名的映射       * @param $data 数据       * @throws PHPExcel_Exception       * @throws PHPExcel_Reader_Exception       */        public static function export($map,$firstRow,$title,$data){            require_once 'PHPExcel.php';            require_once 'PHPExcel/Writer/Excel2007.php';//如果要导出xls而不是xlsx，则改为require_once 'PHPExcel/Writer/Excel5.php';            $objPHPExcel = new PHPExcel();            $objPHPExcel->getProperties()->setCreator('http://www.style.net')                ->setLastModifiedBy('http://www.style.net')                ->setTitle('Office 2007 XLSX Document')                ->setSubject('Office 2007 XLSX Document')                ->setDescription('Document for Office 2007 XLSX, generated using PHP classes.')                ->setKeywords('office 2007 openxml php')                ->setCategory('Result file');            //设置列的宽度，第一行加粗居中            foreach ($map as $k=>$v){                $objPHPExcel->getActiveSheet()->getColumnDimension($k)->setWidth(22);                $objPHPExcel->getActiveSheet()->getStyle($k.'1')->getFont()->setBold(true);                $objPHPExcel->getActiveSheet()->getStyle($k.'1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);            }            //设置列名            foreach ($firstRow as $k=>$v){                $objPHPExcel->setActiveSheetIndex(0)->setCellValue($k,$v);            }            $i = 2;            foreach ($data as $k=>$v) {                foreach ($map as $col=>$name) {                    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($col . $i, $v[$name]);                }                $i++;            }            $objPHPExcel->getActiveSheet()->setTitle($title);            $objPHPExcel->setActiveSheetIndex(0);            $filename1 = urlencode('导出_'.$title) . '_' . date('Y-m-dHis');//生成xlsx文件            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');            header('Content-Disposition: attachment;filename="' . $filename1 . '.xlsx"');            header('Cache-Control: max-age=0');            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');//生成xls文件            /*            header('Content-Type: application/vnd.ms-excel');            header('Content-Disposition: attachment;filename="'.$filename.'.xls"');            header('Cache-Control: max-age=0');            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');            */            $objWriter->save('php://output');        }    }?>

解释一下export方法中的传入参数：

这样传进去：

$this->load->library('excel');$tableName="sense_native_data";            $data = $this->sisa_model->getTableData($tableName,$domain, $projectid, $begin, $end);            $map = array('A' => 'domain',                'B' => 'project_id',                'C' => 'business_group_id',                'D' => 'media_type_id'            );            $firstRow = array('A1' => '域名',                'B1' => '项目id',                'C1' => '业务组ID',                'D1' => '媒体类型ID'            );            $title = "数据表";excel::export($map, $firstRow, $title, $data);

$map的key就是excel中的A,B,C,D,E,F……,value是对应数据库表中的字段名（其实只要是$data数据中关联数组的key就好了，即$data[0][value]能获取到数据就OK）

$firstRow的key就是excel的第一行单元格的列，即A1， B1，C1……，value就是希望excel第一行中呈现的数据，其实第一行就是表头嘛！

$title就是excel导出文件的文件名。

0 0