phpExcel常用方法详解【附有php导出excel加超级链接】
开发包Tests目录有详细使用实例
支持中文,注意文件编码,文件保存为utf-8
写excel
//Include class
require_once(‘Classes/PHPExcel.php’);
require_once(‘Classes/PHPExcel/Writer/Excel2007.php’);
$objPHPExcel = new PHPExcel();
//Set properties 设置文件属性
$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”);
//Add some data 添加数据
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue(‘A1′, ‘Hello’);//可以指定位置
$objPHPExcel->getActiveSheet()->setCellValue(‘A2′, true);
$objPHPExcel->getActiveSheet()->setCellValue(‘A3′, false);
$objPHPExcel->getActiveSheet()->setCellValue(‘B2′, ‘world!’);
$objPHPExcel->getActiveSheet()->setCellValue(‘B3′, 2);
$objPHPExcel->getActiveSheet()->setCellValue(‘C1′, ‘Hello’);
$objPHPExcel->getActiveSheet()->setCellValue(‘D2′, ‘world!’);
//循环
for($i = 1;$i<200;$i++) {
$objPHPExcel->getActiveSheet()->setCellValue(‘A’ . $i, $i);
$objPHPExcel->getActiveSheet()->setCellValue(‘B’ . $i, ‘Test value’);
}
//日期格式化
$objPHPExcel->getActiveSheet()->setCellValue(‘D1′, time());
$objPHPExcel->getActiveSheet()->getStyle(‘D1′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
//Add comment 添加注释
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->setAuthor(‘PHPExcel’);
$objCommentRichText = $objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(‘PHPExcel:’);
$objCommentRichText->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(“\r\n”);
$objPHPExcel->getActiveSheet()->getComment(‘E11′)->getText()->createTextRun(‘Total amount on the current invoice, excluding VAT.’);
//Add rich-text string 添加文字 可设置样式
$objRichText = new PHPExcel_RichText( $objPHPExcel->getActiveSheet()->getCell(‘A18′) );
$objRichText->createText(‘This invoice is ‘);
$objPayable = $objRichText->createTextRun(‘payable within thirty days after the end of the month’);
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setItalic(true);
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_DARKGREEN ) );
$objRichText->createText(‘, unless specified otherwise on the invoice.’);
//Merge cells 合并分离单元格
$objPHPExcel->getActiveSheet()->mergeCells(‘A18:E22′);
$objPHPExcel->getActiveSheet()->unmergeCells(‘A18:E22′);
//Protect cells 保护单元格
$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 数字格式化
$objPHPExcel->getActiveSheet()->getStyle(‘E4′)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(‘E4′), ‘E5:E13′ );
//Set column widths 设置列宽度
$objPHPExcel->getActiveSheet()->getColumnDimension(‘B’)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(12);
//Set fonts 设置字体
$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);
//Set alignments 设置对齐
$objPHPExcel->getActiveSheet()->getStyle(‘D11′)->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);
$objPHPExcel->getActiveSheet()->getStyle(‘A3′)->getAlignment()->setWrapText(true);
//Set column borders 设置列边框
$objPHPExcel->getActiveSheet()->getStyle(‘A4′)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(‘A10′)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(‘E10′)->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(‘D13′)->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
$objPHPExcel->getActiveSheet()->getStyle(‘E13′)->getBorders()->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THICK);
//Set border colors 设置边框颜色
$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()->getRight()->getColor()->setARGB(‘FF993300′);
//Set fills 设置填充
$objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(‘A1′)->getFill()->getStartColor()->setARGB(‘FF808080′);
//Add a hyperlink to the sheet 添加链接
$objPHPExcel->getActiveSheet()->setCellValue(‘E26′, ‘www.phpexcel.net’);
$objPHPExcel->getActiveSheet()->getCell(‘E26′)->getHyperlink()->setUrl(‘http:
补充：Web开发 , php ,
