PHPExcel类的使用讲解
2024-08-28 08:20:38
下面是总结的几个使用方法
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
创建一个excel
$objPHPExcel = new PHPExcel();
保存excel—2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
$objWriter->save("xxx.xlsx");
直接输出到浏览器
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
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="resume.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output'); PHPExcel基本操作:
定义EXCEL实体
即定义一个PHPEXCEL对象,并设置EXCEL对象内显示内容
// Excel开始
// 准备EXCEL的包括文件
// Error reporting
error_reporting(0);
// PHPExcel
require_once dirname(__FILE__) . 'PHPExcel.php';
// 生成新的excel对象
$objPHPExcel = new PHPExcel();
// 设置excel文档的属性
$objPHPExcel->getProperties()->setCreator("Sam.c")
->setLastModifiedBy("Sam.c Test")
->setTitle("Microsoft Office Excel Document")
->setSubject("Test")
->setDescription("Test")
->setKeywords("Test")
->setCategory("Test result file");
// 开始操作excel表
// 操作第一个工作表
$objPHPExcel->setActiveSheetIndex(0);
// 设置工作薄名称
$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'phpexcel测试'));
// 设置默认字体和大小
$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', '宋体'));
$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);
输出文件
// 如果需要输出EXCEL格式
if($m_exportType=="excel"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// 从浏览器直接输出$filename
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-excel;");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$filename);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
// 如果需要输出PDF格式
if($m_exportType=="pdf"){
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->setSheetIndex(0);
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/pdf");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=".$m_strOutputPdfFileName);
header("Content-Transfer-Encoding:binary");
$objWriter->save("php://output");
}
设置一列的宽度:
1
|
$objPHPExcel ->getActiveSheet()->getColumnDimension( 'A' )->setWidth(15); |
设置一行的高度:
1
|
$objPHPExcel ->getActiveSheet()->getRowDimension( '6' )->setRowHeight(30); |
合并单元格:
1
|
$objPHPExcel ->getActiveSheet()->mergeCells( 'A1:P1' ); |
设置A1单元格加粗,居中:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
$styleArray1 = array ( 'font' => array ( 'bold' => true, 'size' =>12, 'color' => array ( 'argb' => '00000000' , ), ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ), ); // 将A1单元格设置为加粗,居中 $objPHPExcel ->getActiveSheet()->getStyle( 'A1' )->applyFromArray( $styleArray1 ); $objPHPExcel ->getActiveSheet()->getStyle( 'B1' )->getFont()->setBold(true); |
给特定单元格中写入内容:
1
|
$objPHPExcel ->getActiveSheet()->setCellValue( 'A1' , 'Hello Baby' ); |
设置单元格样式(水平/垂直居中):
1
2
|
$objPHPExcel ->getActiveSheet()->getStyle( 'A1' )->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel ->getActiveSheet()->getStyle( 'A1' )->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); |
设置单元格样式(黑色字体):
1
|
$objPHPExcel ->getActiveSheet()->getStyle( 'H5' )->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_BLACK); // 黑色 |
设置单元格格式(背景):
1
|
$objPHPExcel ->getActiveSheet()->getStyle( 'H5' )->getFill()->getStartColor()->setARGB( '00ff99cc' ); // 将背景设置为浅粉色 |
设置单元格格式(数字格式):
1
|
$objPHPExcel ->getActiveSheet()->getStyle( 'F' . $iLineNumber )->getNumberFormat()->setFormatCode( '0.000' ); |
给单元格中放入图片:
1
2
3
4
5
6
7
8
9
|
// 将数据中心图片放在J1单元格内 $objDrawing = new PHPExcel_Worksheet_Drawing(); $objDrawing ->setName( 'Logo' ); $objDrawing ->setDescription( 'Logo' ); $objDrawing ->setPath( 'test.jpg' ); $objDrawing ->setWidth(400); $objDrawing ->setHeight(123); $objDrawing ->setCoordinates( 'J1' ); $objDrawing ->setWorksheet( $objPHPExcel ->getActiveSheet()); |
在单元格中设置超链接:
1
2
|
$objPHPExcel ->getActiveSheet()->setCellValue( 'H8' , iconv( 'gbk' , 'utf-8' , '燕南天' )); |
设置单元格边框
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$styleThinBlackBorderOutline = array ( 'borders' => array ( 'outline' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN, //设置border样式 //'style' => PHPExcel_Style_Border::BORDER_THICK, 另一种样式 'color' => array ( 'argb' => 'FF000000' ), //设置border颜色 ), ), ); $objPHPExcel ->getActiveSheet()->getStyle( 'A4:E10' )->applyFromArray( $styleThinBlackBorderOutline ); //添加一个新的worksheet $objExcel ->createSheet(); $objActSheet = $objExcel ->getSheet( $s ); $objActSheet ->setTitle( '表' . $GSheet ); |
最新文章
- Javascript中bind、call、apply函数用法
- check_env函数解析
- redis基础学习(不定期更新)
- JavaWeb技术(一):JDBC简介
- seaJS常用语法
- CSS3 text-rendering属性
- jquery animate() 防止多次执行
- 某xss挑战赛闯关笔记
- PPT分享 | 怎么在区块链上保护隐私?
- pycharm配置可视化数据库
- 利用JAVA API函数实现数据的压缩与解压缩
- 关于maven环境变量的配置问题
- 【剑指offer】数组中只出现一次的数字
- jenkins部署java项目在本地(三)
- Linux下Bind error: Address already in use处理
- OpenCV学习(12) 图像的腐蚀与膨胀(3)
- angular HTML属性绑定
- Gecko Robotics, Inc. SE II Test OA -- 菜到扣脚
- VS2017生成类库选择Release失效的问题
- Jackson是线程安全的吗
热门文章
- eclipse中SSH三大框架环境搭建<;一>;
- windows 修改hosts 脚本
- Spring MVC学习笔记——注解式控制器
- java中的各个数据结构区别
- 409. Longest Palindrome
- Redis命令大全&;中文解释&;在线测试命令工具&;在线中文文档
- 二、oracle pctfree和pctused详解
- iOS-开启arc之后 NSNotificationCenter removeObserver 是否需要调用
- \r,\n,\r\n的区别
- Markdown 写作工具选择