2016-10-12 12:54:01 xiaoxiaohui520134 阅读数 1781

phpExcel将读取的单元格信息保存在内存中,我们可以通过

代码如下:

PHPExcel_Settings::setCacheStorageMethod()

来设置不同的缓存方式,已达到降低内存消耗的目的!

1、将单元格数据序列化后保存在内存中

代码如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;

2、将单元格序列化后再进行Gzip压缩,然后保存在内存中

代码如下:

PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;

3、缓存在临时的磁盘文件中,速度可能会慢一些

 代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_discISAM;

4、保存在php://temp

代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_phpTemp;

5、保存在memcache中

代码如下:

PHPExcel_CachedObjectStorageFactory::cache_to_memcache

举例:

第4中方式:

 

代码如下:

$cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp; 
$cacheSettings = array( ' memoryCacheSize '  => '8MB' 
                ); 
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

第5种:

 

代码如下:

$cacheMethod = PHPExcel_CachedObjectStorageFactory::cache_to_memcache; 
$cacheSettings = array( 'memcacheServer'  => 'localhost', 
                        'memcachePort'    => 11211, 
                        'cacheTime'       => 600 
                      ); 
PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

其它的方法

第一个方法,你可以考虑生成多个sheet的方式,不需要生成多个excel文件,根据你数据总量计算每个sheet导出多少行, 下面是PHPExcel生成多个sheet方法:

面是PHPExcel生成多个sheet方法:

代码如下:

$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A1',$x); 
$sheet->setCellValue('B1',$y);

第二个方法,你可以考虑ajax来分批导出,不用每次刷新页面。

代码如下:

<a href="#" id="export">export to Excel</a>
$('#export').click(function() { 
    $.ajax({ 
        url: "export.php",  
        data: getData(),  //这个地方你也可以在php里获取,一般读数据库 
        success: function(response){ 
            window.location.href = response.url; 
        } 
    }) 
});
代码如下:

<?php
//export.php
$data = $_POST['data'];
$xls = new PHPExcel();
$xls->loadData($formattedData);
$xls->exportToFile('excel.xls');
$response = array(
'success' => true,
'url' => $url
);
header('Content-type: application/json');
echo json_encode($response);
?>

数据量很大的话,建议采用第二种方法,ajax来导出数据,上面方法简单给了个流程,具体你自己补充!

2018-05-23 23:13:36 yw8886484 阅读数 1047

PHPExcel导出订单数据实例,含图片导出对齐方式背景颜色

<?php
//防止直接运行
$_s = $_SERVER['PHP_SELF'];
$_s = strtolower(substr($_s,strripos($_s,"/")+1,-4));
if($_s=="output"){die();}
 
set_time_limit(0);
$totalcount = count($data);
//创建、设置Excel
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()
    ->setCreator("guohua.info")
    ->setLastModifiedBy("guohua.info")
    ->setTitle("PHPExcel Test Document")
    ->setSubject("PHPExcel Test Document")
    ->setDescription("Test document for PHPExcel, generated using PHP classes.")
    ->setKeywords("office PHPExcel php")
    ->setCategory("Test result file");
//设置活跃表
$objPHPExcel->setActiveSheetIndex(0);
//设置宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(50);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(15);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(50);
 
//设置默认行高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(26);
 
//设置E列(电话)为文本格式
$objPHPExcel->getActiveSheet()->getStyle('E')->getNumberFormat()->setFormatCode("@");
    //->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
     
//第一行
$objPHPExcel->getActiveSheet()->mergeCells('A1:P1');//合并单元格
$objPHPExcel->getActiveSheet()->setCellValue('A1', "订单数据");
//第二行
$objPHPExcel->getActiveSheet()
            ->setCellValue('A2', '#')
            ->setCellValue('B2', '业务类型')
            ->setCellValue('C2', '图样')
            ->setCellValue('D2', '客户')
            ->setCellValue('E2', '联系电话')
            ->setCellValue('F2', '收件地址')
            ->setCellValue('G2', '数量')
            ->setCellValue('H2', '安排打印日期')
            ->setCellValue('I2', '预计发货日期')
            ->setCellValue('J2', '发货日期')
            ->setCellValue('K2', '单号')
            ->setCellValue('L2', '快递公司')
            ->setCellValue('M2', '价格')
            ->setCellValue('N2', '付款情况')
            ->setCellValue('O2', '商务')
            ->setCellValue('P2', '备注');
//设置行高
$objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(26);
//设置填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle("A2:P2")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle("A2:P2")->getFill()->getStartColor()->setARGB('FF808080');
//对齐方式
$objPHPExcel->getActiveSheet()->getStyle("A2:O2")->getAlignment()
    ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平方向上两端对齐
$objPHPExcel->getActiveSheet()->getStyle("A2:O2")->getAlignment()
    ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直方向上中间居中
 
$index = 3;
foreach($data as $k=>$v){
    //设置行高
    $objPHPExcel->getActiveSheet()->getRowDimension($index)->setRowHeight(60);
    //对齐方式
    $objPHPExcel->getActiveSheet()->getStyle("A{$index}:O{$index}")->getAlignment()
        ->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//水平方向上两端对齐
    $objPHPExcel->getActiveSheet()->getStyle("A{$index}:P{$index}")->getAlignment()
        ->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直方向上中间居中
 
    $objPHPExcel->getActiveSheet()->setCellValue('A'.$index, $v['id']);
    $objPHPExcel->getActiveSheet()->setCellValue('B'.$index, $v['category']);
 
    /*实例化excel图片处理类*/
    $objDrawing = new PHPExcel_Worksheet_Drawing();//每张图都需要实例化一个
    $objDrawing->setPath($v['img_url']);/*设置图片路径 切记:只能是本地图片,不能带http*/
    $objDrawing->setOffsetX(1);/*设置图片所在单元格的起始坐标*/
    $objDrawing->setOffsetY(1);
    $objDrawing->setWidth(60);/*设置图片宽高*/
    $objDrawing->setHeight(60);
    $objDrawing->setCoordinates("C$index");/*插入单元格*/
    $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());//不可少
     
    $objPHPExcel->getActiveSheet()->setCellValue("D{$index}", $v['cname']);
    $objPHPExcel->getActiveSheet()->setCellValue("E{$index}", $v['tel']);
    $objPHPExcel->getActiveSheet()->setCellValue("F{$index}", $v['addr']);
    $objPHPExcel->getActiveSheet()->setCellValue("G{$index}", $v['amount']);
    $time_print = intval($v['time_print'])<1?'':date('Y-m-d',$v['time_print']);
    $objPHPExcel->getActiveSheet()->setCellValue("H{$index}", $time_print);
    $time_fahuo0 =intval($v['time_fahuo0'])<1?'':date('Y-m-d',$v['time_fahuo0']);
    $objPHPExcel->getActiveSheet()->setCellValue("I{$index}", $time_fahuo0);
    $time_fahuo1 =intval($v['time_fahuo1'])<1?'':date('Y-m-d',$v['time_fahuo1']);
    $objPHPExcel->getActiveSheet()->setCellValue("J{$index}", $time_fahuo1);
    $objPHPExcel->getActiveSheet()->setCellValue("K{$index}", $v['express_no']);
    $objPHPExcel->getActiveSheet()->setCellValue("L{$index}", c_getexpress($v['express_id']));
    $objPHPExcel->getActiveSheet()->setCellValue("M{$index}", $v['price']);
    $objPHPExcel->getActiveSheet()->setCellValue("N{$index}", c_getpayed($v['status_pay']));
    $objPHPExcel->getActiveSheet()->setCellValue("O{$index}", $v['username']);
    $objPHPExcel->getActiveSheet()->setCellValue("P{$index}", $v['remark']);
 
    $index++;
}
// 表名称
$objPHPExcel->getActiveSheet()->setTitle('订单数据');
// 设置第一张表为活跃表
$objPHPExcel->setActiveSheetIndex(0);
//弹窗式保存
ob_end_clean();//清除缓冲区,避免乱码
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");;
header('Content-Disposition:attachment;filename="data.xlsx"');
header("Content-Transfer-Encoding:binary");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');//$objWriter->save('shite.xlsx');//这种方式默认保存在php文件同一文件夹下
www.guohua.info

2018-01-08 18:19:59 qq_36607076 阅读数 3841

1.下载phpexcel类库文件,放在vendor下面。

2.php


 public function daochu(){
       $data = "查询出数组";
        Vendor('phpexcel.PHPExcel');//调用类库,路径是基于vendor文件夹的
        Vendor('phpexcel.PHPExcel.Worksheet.Drawing');
        Vendor('phpexcel.PHPExcel.Writer.Excel2007');
        $objExcel = new \PHPExcel();
        //set document Property
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');

        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter =explode(',',"A,B,C,D,E,F,G,H");
        $arrHeader =  array('时间','店铺id','店铺名称','分店名称','销售量','销售额','应打款金额','状态');;
        //填充表头信息
        $lenth =  count($arrHeader);
        for($i = 0;$i < $lenth;$i++) {
            $objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
        };
        //填充表格信息
        foreach($data as $k=>$v){
            $k +=2;
            $objActSheet->setCellValue('A'.$k,$v['gap']);
            $objActSheet->setCellValue('B'.$k, $v['store_id']);
            // // 图片生成
            // $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
            // $objDrawing[$k]->setPath('public/static/admin/images/profile_small.jpg');
            // // 设置宽度高度
            // $objDrawing[$k]->setHeight(40);//照片高度
            // $objDrawing[$k]->setWidth(40); //照片宽度
            // /*设置图片要插入的单元格*/
            // $objDrawing[$k]->setCoordinates('C'.$k);
            // // 图片偏移距离
            // $objDrawing[$k]->setOffsetX(30);
            // $objDrawing[$k]->setOffsetY(12);
            // $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
            // 表格内容
            $objActSheet->setCellValue('C'.$k, $v['store_name']);
            $objActSheet->setCellValue('D'.$k, $v['branch']);
            $objActSheet->setCellValue('E'.$k, $v['snum']);
            $objActSheet->setCellValue('F'.$k, $v['pay_ables']);
            $objActSheet->setCellValue('G'.$k, $v[0]);
            $objActSheet->setCellValue('H'.$k, $v['storetrade_status']);



            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }

        $width = array(20,20,15,10,10,30,10,15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[0]);
        $objActSheet->getColumnDimension('B')->setWidth($width[3]);
        $objActSheet->getColumnDimension('C')->setWidth($width[5]);
        $objActSheet->getColumnDimension('D')->setWidth($width[3]);
        $objActSheet->getColumnDimension('E')->setWidth($width[3]);
        $objActSheet->getColumnDimension('F')->setWidth($width[3]);
        $objActSheet->getColumnDimension('G')->setWidth($width[3]);
        $objActSheet->getColumnDimension('H')->setWidth($width[5]);


        $outfile = "商家交易结算".date("Y-m-d").".xls";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$outfile.'"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
3.静态页面内容
   <div>
    <a style="text-decoration:none"  href="{:url('daochu')}" class="btn btn-info radius" id="daochu"><i class="fa fa-search"></i>导出数据</a>
    </div>

4.已完成

2017-04-17 19:42:38 u013410771 阅读数 2199

!!!修改完配置后要重启服务器才有效

问题):Cannot use output buffering in output buffering display handlers
解决:把Thinkphp/Command/common.php下trace方法中的$info =($label?$label.':':'').print_r($value,true);注册掉之后会有详细错误信息


问题1:Allowed memory size of 134217728 bytes exhausted
服务器内存不足的问题
解决:在php.ini文件下修改memory_limit ,默认为128M,可以适当增加内存空间,如256M


问题2:Fatal error: Maximum execution time of 30 seconds exceeded
服务器响应时间限制为30秒
解决:在php.ini文件下修改max_execution_time,默认为30s,可适当增加时间,若改为0则响应时间无限制

没有更多推荐了,返回首页