菜单

phpexecel自己的行使记录

2019年1月29日 - 金沙编程资讯

金沙国际 1

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

    for($i=0;$i<($rows+1);$i++){

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

控制器
“`
// 导出多少
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”);

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

           $tdptn=’落’;

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

心得

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

   
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(‘&L&B’
. $objExcel->getProperties()->getTitle() . ‘&RPage &P of &N’);

  创建人


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

   

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


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

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

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

注:小说来源雨中笑记录实习期境遇的难点与体会,转发请表达原文

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

  分离单元格

  $objPHPExcel->setActiveSheetIndex(0);

自身意识以来的类型须要导出Excel表的页面格外的多,想来这几个也是大家常用的效劳了,现在我得以很熟练的导出了,不过记得及时协调首先次导出时仍然绕了有的弯路的,那么现在本身就来记录下我这次用exshop框架项目下的导出(其实在分化的框架下Excel的导出原理都是基本上的)

// 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

       $objExcel->getActiveSheet()->setCellValue($arrs[$i].”1″,
“$k[$i]”);

  种类

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

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

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

生成Excel常用艺术

成果图

$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”);

  描述

//*************************************
//设置单元格样式
//

 <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>

// 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’);

  设置sheet的name

  header(”Content-Transfer-Encoding:binary”);


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

    //设置Excel的名字

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

金沙国际 2

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

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

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

金沙国际 3

$objPHPExcel = new PHPExcel();

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

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

        // 行高
        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');
    }
}

```

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

   

//设置对齐格局
$objAlignA5 = $objStyleA5->getAlignment();
$objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

前端

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

    // 重命名表

  设置excel的属性:

突发性境遇这么些题材可以多思考,多看看它的法则,原理领悟了下次做任何的也是会的,但最主要的是要通晓做笔录,我们的纪念并没有设想的那么好

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

  $objPHPExcel = new PHPExcel();

  标题

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

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

  设置单元格的值

  题目

    $rows = mssql_num_rows($res);

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

   
$objExcel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(10);

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

  成立一个excel

//*************************************
//输出内容
//
$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’);

 

  设置单元格的值

    {

  直接出口到浏览器

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

  保存excel—2007格式

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

  $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

    /*——–从数据库读取数据——-*/

<?
//设置PHPExcel类库的include path
set_include_path(‘.’. PATH_SEPARATOR .
‘D:ZealPHP_LIBS’ . PATH_SEPARATOR .
get_include_path());

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

  设置当前的sheet

全副代码如下(值得注意的是表头用了$orderCellData记录了每个商户编号的各样,为了在表体把相应的多少取出):

  最终修改人

           $tdptn=’涨’;

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

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

//设置当前移动sheet的名称
$objActSheet->setTitle(‘测试Sheet’);

    $k = array(‘站码’,’站名’,’河系’,’来报时间’,’水位’,’水势’);

  关键字

    // 设置页眉和页脚。如若没有两样的标题奇数/即便是利用单头假定.

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

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

  合并单元格

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

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

    $excelName = ‘实时潮汐意况(‘.$tm.’)’;

  $objPHPExcel->getProperties()->setKeywords(”office
2007 openxml php”);

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

//*************************************
//添加图片
$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);

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

  分离单元格

?>

  header(”Expires: 0″);

   
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

  include
‘PHPExcel.php’;

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

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

   

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

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

//*************************************
//设置当前的sheet索引,用于后续的内容操作。
//一般只有在行使四个sheet的时候才须要浮现调用。
//缺省气象下,PHPExcel会自动创造首个sheet被设置SheetIndex=0
$objExcel->setActiveSheetIndex(0);

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

  $objPHPExcel = new PHPExcel();

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

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

    while($arr=mssql_fetch_array($res))

  $objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);

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

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

  header(”Content-Transfer-Encoding:binary”);

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

    //添加表头

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

相关文章

发表评论

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

网站地图xml地图