日博365体育phpexecel自己的行使记录

控制器
“`
// 导出多少
public function export() {
$result =
$this->_oaOrderModel->getAllOrderListForManager($this->store_id,
$orderSn=null, $buyer_id=null, $buyer_name=null, $consignee=null,
$phone=null, $company_name=null, $status=null, $s_time=null,
$e_time=null, $page=null, $listRows=null, $execl=true);
//这么些是获取数据的代码-model里
$orderList = $result[‘orderList’];
if (!empty($orderList)) {
$j = 1;
$stmt = array();
foreach ($orderList as $val) {
$stmt[$j][‘网站ID’] = $val[‘store_id’];
$stmt[$j][‘订单音讯’] = $val[‘order_sn’];
$stmt[$j][‘商品新闻’] = $val[‘inventory_sn_count_chinese’];
$stmt[$j][‘工艺选择’] = $val[‘craft_count_chinese’];
$stmt[$j][‘商品总数据’] = $val[‘real_goods_total_count’];
$stmt[$j][‘提交日期’] = date(“Y-m-d H:i:s”,$val[‘add_time’]);
$stmt[$j][‘客户名称’] = $val[‘company_name’];
$stmt[$j][‘联系人’] = $val[‘consignee’];
$stmt[$j][‘联系格局’] = $val[‘phone_mob’];
$stmt[$j][‘订单完结率’] = $val[‘percentage_complete’];
$stmt[$j][‘订单状态’] = $val[‘statusChinese’];
$j++;
}
$current_path = dirname(FILE);
$home_path = dirname($current_path);
require_once ROOT_PATH . ‘/includes/libraries/PHPExcel.php’;
require_once ROOT_PATH .
‘/includes/libraries/PHPExcel/IOFactory.php’;
$objPHPExcel = new PHPExcel(); //这么些点子自己下载放到公共措施里
$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”)
->setLastModifiedBy(“Maarten Balliauw”)
->setTitle(“Office 2007 XLSX Test Document”)
->setSubject(“Office 2007 XLSX Test Document”)
->setDescription(“Document for Office 2007 XLSX, generated using
PHP classes.”)
->setKeywords(“office 2007 openxml php”)
->setCategory(“Test result file”);

->setCellValue(‘D1’, ‘记录时间’);  

    // Redirect output to a client’s web browser
(Excel5)保存为excel2003格式

  设置excel的属性:

自身意识以来的品种须要导出Excel表的页面极度的多,想来以此也是大家常用的法力了,现在本身可以很在行的导出了,可是记得当时温馨率先次导出时仍旧绕了一部分弯路的,那么现在我就来记录下自己这一次用exshop框架项目下的导出(其实在差异的框架下Excel的导出原理都是大半的)

$time_name =
‘在线人数’.date(‘YmdHis’,time()).’.xls’;//设置下载时文件的默许名

   
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(‘&L&BPersonal
cash register&RPrinted on &D’);

//*************************************
//设置文档基本属性
$objProps =
$objExcel->getProperties();
$objProps->setCreator(“Zeal Li”);
$objProps->setLastModifiedBy(“Zeal Li”);
$objProps->setTitle(“Office XLS Test Document”);
$objProps->setSubject(“Office XLS Test Document, Demo”);
$objProps->setDescription(“Test document, generated by PHPExcel.”);
$objProps->setKeywords(“office excel PHPExcel”);
$objProps->setCategory(“Test”);

心得

$user_sql = “select * from `table`”;

 代码如下

  $objWriter->save(”xxx.xlsx”);

日博365体育 1

//设置标题名称
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘编号’)
->setCellValue(‘B1’, ‘在线人数’)
->setCellValue(‘C1’, ‘手机’)

       $stnm=iconv(“GBK”,”utf-8″,$stnm);

  header(”Content-Type:application/vnd.ms-execl”);

前端

// Redirect output to a client’s web browser (Excel5)
header(‘Content-Type: application/vnd.ms-excel’);
header(“Content-Disposition: attachment;filename=’$time_name'”);
header(‘Cache-Control: max-age=0’);
// If you’re serving to IE 9, then the following may be needed
header(‘Cache-Control: max-age=1’);

  合并单元格

  //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
非2007格式

日博365体育 2

//循环每个数据,若是赶上手机号码的数码导出,可以在相应的数额前、后添加一个空格,达到将数据变成字符串的法子,令execl不是讲其当成科学数据法的办法显示出来
foreach ($user_list as $k => $v) {
$num=$k+2;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A’.$num, $v[‘id’])
->setCellValue(‘B’.$num, $v[‘online’])

  设置excel的属性:

  $objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);

日博365体育 3

$user_list = $db->fetchAll($user_sql);//数据库查询

phpexcel用法介绍

// 创造一个甩卖目的实例
$objExcel = new PHPExcel();

注:小说来源雨中笑记录实习期碰到的标题与体验,转发请表明原文

require_once dirname(__FILE__) .
‘/../../class/PHPExcel.php’;//加载phpexcel.php,按照自己项目标路径调整

    // 设置页方向和范围

  分离单元格

        // 行高
        for ($i = 2; $i <= count($stmt); $i++) {
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22);
        }
        foreach ($stmt as $fid => $fval) {
            if ($fid == 1) {
                $key = 0;
                foreach ($fval as $title => $first) {
                    //如果一级标题
                    $objPHPExcel->getActiveSheet()->setCellValue(chr($key + 65) . '1', $title);
                    $objPHPExcel->getActiveSheet()->getStyle(chr($key + 65) . '1')->getFont()->setBold(true);       // 加粗
                    $key ++;
                }
            }
            $cid = 0;
            $row_id = $fid + 1;
            foreach ($fval as $cval) {
                $objPHPExcel->getActiveSheet()->setCellValue(chr($cid + 65) . (string) ($row_id), $cval);
                $cid++;
            }
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('Excel表');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //$objWriter->save('订单列表详细.xls');
        //输出到浏览器
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="订单列表.xls"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
}

```

$objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(20);//对某列单元格设置宽度

       $objExcel->getActiveSheet()->setCellValue(‘e’.$u1, “$tdz”);

  header(’Content-Disposition:attachment;filename=”resume.xls”‘);

成果图

$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”)
->setLastModifiedBy(“Maarten Balliauw”)
->setTitle(“Office 2007 XLSX Test Document”)
->setSubject(“Office 2007 XLSX Test Document”)
->setDescription(“Test document for Office 2007 XLSX, generated using
PHP classes.”)
->setKeywords(“office 2007 openxml php”)
->setCategory(“Test result file”);

    $arrs = array(‘A’,’B’,’C’,’D’,’E’,’F’);

  header(”Cache-Control:must-revalidate, post-check=0, pre-check=0″);


// Set active sheet index to the first sheet, so Excel opens this as the
first sheet
$objPHPExcel->setActiveSheetIndex(0);

   

//*************************************
//输出内容
//
$outputFileName = “output.xls”;
//到文件
////$objWriter->save($outputFileName);
//or
//到浏览器
////header(“Content-Type: application/force-download”);
////header(“Content-Type: application/octet-stream”);
////header(“Content-Type: application/download”);
////header(‘Content-Disposition:inline;filename=”‘.$outputFileName.'”‘);
////header(“Content-Transfer-Encoding: binary”);
////header(“Expires: Mon, 26 Jul 1997 05:00:00 GMT”);
////header(“Last-Modified: ” . gmdate(“D, d M Y H:i:s”) . ” GMT”);
////header(“Cache-Control: must-revalidate, post-check=0,
pre-check=0”);
////header(“Pragma: no-cache”);
////$objWriter->save(‘php://output’);

偶然遭逢那一个题材得以多牵挂,多看看它的法则,原理精晓了下次做任何的也是会的,但最重大的是要知道做笔录,大家的记得并不曾设想的那么好

// If you’re serving to IE over SSL, then the following may be needed
header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s’).’ GMT’); // always
modified
header (‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
header (‘Pragma: public’); // HTTP/1.0

    $tm=$_REQUEST[‘tm’];

//设置字体
$objFontA5 = $objStyleA5->getFont();
$objFontA5->setName(‘Courier New’);
$objFontA5->setSize(10);
$objFontA5->setBold(true);
$objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objFontA5->getColor()->setARGB(‘FF999999’);


// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(‘sheet1’);

       for($j=0;$j<$count;$j++){

  header(”Content-Type:application/octet-stream”);


$objPHPExcel = new PHPExcel();

    }

  $objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);

 <a href="javascript:;" id="export_all" class="coolbg">导出</a>

<script>
    //导出数据
    $('#export_all').click(function(){
        window.open('index.php?app=craft_order&act=export', '_blank');
    });
</script>

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,
‘Excel5’);
$objWriter->save(‘php://output’);
die;

       $rvnm = $arr[“RVNM”];

  $objPHPExcel->getActiveSheet()->setCellValue(’B8′,
‘=MIN(B2:C5)’);

留意:PHPExcel(); //这一个法子自己下载放到公共措施里

->setCellValue(‘C’.$num, ‘ ‘.$v[‘phone_num’].’ ‘)
->setCellValue(‘D’.$num, date(‘Y-m-d H:i:s’,$v[‘dateline’]));
}

   
$objExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(15);

//显式指定内容类型
$objActSheet->setCellValueExplicit(‘A5’, ‘847475847857487584’,
PHPExcel_Cell_DataType::TYPE_STRING);

?>

//从指定的单元格复制样式音信.
$objActSheet->duplicateStyle($objStyleA5, ‘B1:C22’);

  标题

  设置sheet的name

  关键字

//设置边框
$objBorderA5 = $objStyleA5->getBorders();
$objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorderA5->getTop()->getColor()->setARGB(‘FFFF0000’); //
color
$objBorderA5->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorderA5->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objBorderA5->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

    mssql_select_db($config[‘mssql’][‘dbname’],$conn);

  $objPHPExcel->getProperties()->setCategory(”Test result
file”);

    while($arr=mssql_fetch_array($res))

//设置宽度
$objActSheet->getColumnDimension(‘B’)->setAutoSize(true);
$objActSheet->getColumnDimension(‘A’)->setWidth(30);

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

  末了修改人

  $objWriter->save(’php://output’);

//爱慕单元格
$objExcel->getSheet(1)->getProtection()->setSheet(true);
$objExcel->getSheet(1)->protectCells(‘A1:C22’, ‘PHPExcel’);

  题目

//*************************************
//添加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(‘ZealImg’);
$objDrawing->setDescription(‘Image inserted by Zeal’);
$objDrawing->setPath(‘./zeali.net.logo.gif’);
$objDrawing->setHeight(36);
$objDrawing->setCoordinates(‘C23’);
$objDrawing->setOffsetX(10);
$objDrawing->setRotation(15);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(36);
$objDrawing->setWorksheet($objActSheet);

   

  $objPHPExcel->getProperties()->setLastModifiedBy(”Maarten
Balliauw”);

include ‘PHPExcel.php’;

  //或者include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的

  设置sheet的name

  $objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’);

    $objWriter = PHPExcel_IOFactory::createWriter($objExcel, ‘Excel5’);

  种类

    $objExcel->getProperties()->setLastModifiedBy(“office 2003
excel”);

  关键字

  直接出口到浏览器

//*************************************
//设置当前的sheet索引,用于后续的情节操作。
//一般只有在采用五个sheet的时候才须要浮现调用。
//缺省气象下,PHPExcel会自动创造第三个sheet被安装SheetIndex=0
$objExcel->setActiveSheetIndex(0);

       }else if($tdptn==’5′){

// 创造文件格式写入对象实例, uncomment
////$objWriter = new PHPExcel_Writer_Excel5($objExcel); //
用于其余版本格式
// or
////$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // 用于
2007 格式
//$objWriter->setOffice2003Compatibility(true);

  ——————————————————————————————————————–

  $objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX
Test Document”);

    }

  ——————————————————————————————————————–

          
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

  $objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

  $objPHPExcel->getActiveSheet()->setCellValue(’C5′,
‘=SUM(C2:C4)’);

$objActSheet = $objExcel->getActiveSheet();

    exit;

  $objPHPExcel->getActiveSheet()->setTitle(’Simple’);

    header( ‘Content-Disposition: attachment; filename=’.iconv(“utf-8”,
“GBK”, $excelName).’.xls’);

  合并单元格

    //设置属性(那段代码无关主要,其中的情节可以替换为您须求的)

  设置单元格的值

  $objWriter->save(”xxx.xlsx”);

//设置填充颜色
$objFillA5 = $objStyleA5->getFill();
$objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objFillA5->getStartColor()->setARGB(‘FFEEEEEE’);

  $objPHPExcel->getProperties()->setDescription(”Test document
for Office 2007 XLSX, generated using PHP classes.”);

  保存excel—2007格式

 代码如下

  include
‘PHPExcel.php’;

  $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

// uncomment
////require_once ‘PHPExcel/Writer/Excel5.php’; // 用于其余低版本xls
// or
////require_once ‘PHPExcel/Writer/Excel2007.php’; // 用于 excel-2007
格式

    $res=mssql_query($sql);

require_once ‘PHPExcel.php’;

   

  header(”Content-Type:application/download”);;

PHPEXCEL即便强大,但是使用起来相对有些繁锁,倘使须要输出较为复杂格式时,是一个不利的选料。可以到法定下载到源码。

//*************************************
//设置单元格内容
//
//由PHPExcel根据传入内容自动判断单元格内容类型
$objActSheet->setCellValue(‘A1’, ‘字符串内容’); // 字符串内容
$objActSheet->setCellValue(‘A2’, 26); // 数值
$objActSheet->setCellValue(‘A3’, true); // 布尔值
$objActSheet->setCellValue(‘A4’, ‘=SUM(A2:A2)’); // 公式

       $objExcel->getActiveSheet()->setCellValue(‘f’.$u1,
“$tdptn”);

  题目

    $objExcel->getProperties()->setSubject(“Office 2003 XLS Test
Document”);

//分离单元格
$objActSheet->unmergeCells(‘B1:C22’);

设置单元格的值,参数:单元格名称,值:
$excelSheet->setCellValue(‘A1’, ‘字符串内容’);
$excelSheet->setCellValue(‘A2’, 26); //数值
$excelSheet->setCellValue(‘A3’, true); //布尔值
$excelSheet->setCellValue(‘A4’, ‘=SUM(A2:A2)’); //公式

  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

发表评论

电子邮件地址不会被公开。 必填项已用*标注