search

Home  >  Article  >  php教程  >  php excel类 phpExcel使用方法介绍，excelphpexcel

php excel类 phpExcel使用方法介绍，excelphpexcel

WBOY
WBOYOriginal
2016-06-13 09:29:47795browse

php excel类 phpExcel使用方法介绍，excelphpexcel

phpExcel操作excel可以方便的加入图片，支持jpg gif png格式。

 下载地址：http://www.codeplex.com/PHPExcel 

下面是总结的几个使用方法
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类库不熟悉的朋友，可以阅读下《phpexcel中文帮助手册》中的内容，具体实例大家可以phpexcel快速开发指南中的相关例子。 
设置excel的属性：
创建人
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
最后修改人
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
标题
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
题目
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
描述
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
关键字
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
种类
$objPHPExcel->getProperties()->setCategory("Test result file");
——————————————————————————————————————–
设置当前的sheet
$objPHPExcel->setActiveSheetIndex(0);
设置sheet的name
$objPHPExcel->getActiveSheet()->setTitle('Simple');
设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue('A1′, ‘String');
$objPHPExcel->getActiveSheet()->setCellValue('A2′, 12);
$objPHPExcel->getActiveSheet()->setCellValue('A3′, true);
$objPHPExcel->getActiveSheet()->setCellValue('C5′, ‘=SUM(C2:C4)');
$objPHPExcel->getActiveSheet()->setCellValue('B8′, ‘=MIN(B2:C5)');
合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A18:E22′);
分离单元格
$objPHPExcel->getActiveSheet()->unmergeCells('A28:B28′);
保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells('A3:E13′, ‘PHPExcel');
设置格式
// Set cell number formats
echo date('H:i:s') . " Set cell number formatsn";
$objPHPExcel->getActiveSheet()->getStyle('E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4′), ‘E5:E13′ );
设置宽width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);
设置font
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->setName('Candara');
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('E1′)->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('D13′)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('E13′)->getFont()->setBold(true);
设置align
$objPHPExcel->getActiveSheet()->getStyle('D11′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D12′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('D13′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle('A18′)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle('A18′)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
设置column的border
$objPHPExcel->getActiveSheet()->getStyle('A4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('B4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('C4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('D4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('E4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
设置border的color
$objPHPExcel->getActiveSheet()->getStyle('D13′)->getBorders()->getLeft()->getColor()->setARGB('FF993300′);
$objPHPExcel->getActiveSheet()->getStyle('D13′)->getBorders()->getTop()->getColor()->setARGB('FF993300′);
$objPHPExcel->getActiveSheet()->getStyle('D13′)->getBorders()->getBottom()->getColor()->setARGB('FF993300′);
$objPHPExcel->getActiveSheet()->getStyle('E13′)->getBorders()->getTop()->getColor()->setARGB('FF993300′);
$objPHPExcel->getActiveSheet()->getStyle('E13′)->getBorders()->getBottom()->getColor()->setARGB('FF993300′);
$objPHPExcel->getActiveSheet()->getStyle('E13′)->getBorders()->getRight()->getColor()->setARGB('FF993300′);
设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle('A1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1′)->getFill()->getStartColor()->setARGB('FF808080′);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('B1′)->getFill()->getStartColor()->setARGB('FF808080′);

>>> 更多php教程内容，请关注本站php编程栏目。
加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Logo');
$objDrawing->setDescription('Logo');
$objDrawing->setPath('./images/officelogo.jpg');
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName('Paid');
$objDrawing->setDescription('Paid');
$objDrawing->setPath('./images/paid.png');
$objDrawing->setCoordinates('B15′);
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
在默认sheet后，创建一个worksheet
echo date('H:i:s') . " Create new Worksheet objectn";
$objPHPExcel->createSheet();
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter-save('php://output');

PHPExcel生成Excel不可以读取

总结 php导出Excel php导入Excel PhpExcel使用说明 PhpExcel使用手册2009/03/06 上午 02:37方法一:特点,简单,省心,

header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:attachment;filename=test_data.xls");

$tx='表头';
echo $tx."\n\n";
//输出内容如下：
echo "姓名"."\t";
echo "年龄"."\t";
echo "学历"."\t";
echo "\n";
echo "张三"."\t";
echo "25"."\t";
echo "本科"."\t";
?>

方法二: 引用google code中推荐的小类库(大体同方法一,比较复杂点)

code.google.com/p/php-excel/downloads/list

方法三: PHPEXCEL 类库,功能强大,支持win Excel2003 ,Win Excel2007.

www.codeplex.com/PHPExcel


//设置PHPExcel类库的include path
set_include_path('.'. PATH_SEPARATOR .
'D:\Zeal\PHP_LIBS' . PATH_SEPARATOR .
get_include_path());

/**
* 以下是使用示例，对于以 //// 开头的行是不同的可选方式，请根据实际需要
* 打开对应行的注释。
* 如果使用 Excel5 ，输出的内容应该是GBK编码。
*/
require_once 'PHPExcel.php';

// uncomment
////require_once 'PHPExcel/Writer/Excel5.ph......余下全文>>
 

php中还有什类似phpexcel的东西？介绍一下

phpexcel 　　PHPExcel是相当强大的 MS Office Excel 文档生成类库，当需要输出比较复杂格式数据的时候，PHPExcel 是个不错的选择。不过其使用方法相对来说也就有些繁琐。列举以记之。
-- PHP Code --

//设置PHPExcel类库的include path
set_include_path('.'. PATH_SEPARATOR .
'D:\Zeal\PHP_LIBS' . PATH_SEPARATOR .
get_include_path());
/**
* 以下是使用示例，对于以 //// 开头的行是不同的可选方式，请根据实际需要
* 打开对应行的注释。
* 如果使用 Excel5 ，输出的内容应该是GBK编码。
*/
require_once 'PHPExcel.php';
// uncomment
////require_once 'PHPExcel/Writer/Excel5.php'; // 用于其他低版本xls
// or
////require_once 'PHPExcel/Writer/Excel2007.php'; // 用于 excel-2007 格式
// 创建一个处理对象实例
$objExcel = new PHPExcel();
// 创建文件格式写入对象实例, uncomment
////$objWriter = new PHPExcel_Writer_Excel5($objExcel); // 用于其他版本格式
// or
////$objWriter = new PHPExcel_Writer_Excel2007($objExcel); // 用于 2007 格式
//$objWriter->setOffice2003Compatibility(true);
//*************************************
//设置文档基本属性
$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 ......余下全文>>
 

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：最新仿梦芭莎免费ecshop模板，芭莎ecshop模板Next article：PHP出错界面详细说明，php出错详细说明

Related articles

See more