PHPExcel的使用

1.当然是下载PHPexcel的插件了 http://phpexcel.codeplex.com/

2.应用插件我把插件和需要用到的excel模板放的是不同文件夹的,excel我放在public下的upload文件夹里,excel插件我放在vendor文件里

PHPExcel的使用

3.控制器的引用方法,这些应该是必须的

注:因为我用的是thinkcmf5,里面有自带的phpexcel的方法,所以你们用其他框架的我不知道可不可以用,如果不可以用,请把Loader改为

 vendor('PHPExcel.Classes.PHPExcel');

 vendor('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');

上面的如果读不出来,可能就是里面的路径没写对

   $dir = 'upload/';         //模板存放目录

             $templateName = 'account.xlsx';             //模板表

             $path = dirname(__FILE__);               //找到当前脚本所在路径

             Loader::import('PHPExcel.Classes.PHPExcel');

             Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');

             $PHPExcel = new \PHPExcel();

             $PHPReader = new \PHPExcel_Reader_Excel2007();

             //读取Excel

             $PHPExcel = $PHPReader->load($dir.$templateName);

             $currentSheet = $PHPExcel->getSheet(0);

4.样式设置,你也可以不设置

  //设置水平居中             $PHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setHorizontal( \PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

             $PHPExcel->getActiveSheet()->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

5.把数据导入到excel模板里面,我的是个二维数组

 for($i=0;$i<count($deal);$i++){

                 $currentSheet->setCellValue('A'.($i+3), date('Y-m-d H:i:s', $deal[$i]['create_time'])); //日期

                 $currentSheet->setCellValue('B'.($i+3),$deal[$i]['deal_type']); //交易类型

                 $currentSheet->setCellValue('C'.($i+3),$deal[$i]['roll_name']); //支出账户名称

                 $currentSheet->setCellValue('D'.($i+3),$deal[$i]['roll_user']); //支出账号

                 $currentSheet->setCellValue('E'.($i+3),$deal[$i]['receive_name']); //收入账号名称

                 $currentSheet->setCellValue('F'.($i+3),$deal[$i]['receive_user']); //收入账户

                 $currentSheet->setCellValue('G'.($i+3),$deal[$i]['account']); //交易金额

                 $currentSheet->setCellValue('H'.($i+3),$deal[$i]['roll_account']); //支出账号交易后余额

                 $currentSheet->setCellValue('I'.($i+3),$deal[$i]['receive_account']); //收入账号交易后余额

                 $currentSheet->setCellValue('J'.($i+3),$deal[$i]['user']); //操作员

                 $currentSheet->setCellValue('K'.($i+3),$deal[$i]['remark']); //订单备注

             }

6.这样就可以下载下来了,有两种输出格式,excel输出是有表格线条的,网页输出没有表格线条

   　　　　　　ob_clean();

             ob_start();

             $objWriteHTML = new \PHPExcel_Writer_Excel2007($PHPExcel); //输出EXCLl格式的对象

             header("Content-type: application/octet-stream;charset=utf-8");

             header('Content-Type: application/vnd.ms-excel');

             header('Content-Disposition: attachment;filename="funds.xlsx"');  //日期为文件名后缀

             header('Cache-Control: max-age=0');

             $objWriteHTML->save("php://output");

             exit;//退出

7.完成

相关文章