PHP与EXCEL PHPExcel-php手册-php.cn

1、PHPExcel简介

PHPExcel 是用来操作Office Excel 文档的一个PHP类库，它基于微软的OpenXML标准和PHP语言。可以使用它来读取、写入不同格式的电子表格，如 Excel (BIFF) .xls, Excel 2007 (OfficeOpenXML) .xlsx, CSV, Libre/OpenOffice Calc .ods, Gnumeric, PDF, HTML等等。

2、EXCEL导出文件

include &#39;PHPExcel.php&#39;;
include &#39;PHPExcel/Writer/Excel2007.php&#39;;
//或者include &#39;PHPExcel/Writer/Excel5.php&#39;; 用于输出.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(&#39;Content-Disposition;attachment;filename="resume.xls"&#39;);
header("Content-Transfer-Encoding;binary");
$objWriter->save(&#39;php;//output&#39;);
——————————————————————————————————————–
设置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(&#39;Simple&#39;);
设置单元格的值
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A1&#39;, &#39;String&#39;);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A2&#39;, 12);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;A3&#39;, true);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;C5&#39;, &#39;=SUM(C2;C4)&#39;);
$objPHPExcel->getActiveSheet()->setCellValue(&#39;B8&#39;, &#39;=MIN(B2;C5)&#39;);
合并单元格
$objPHPExcel->getActiveSheet()->mergeCells(&#39;A18;E22&#39;);
分离单元格
$objPHPExcel->getActiveSheet()->mergeCells(&#39;A18;E22&#39;);
$objPHPExcel->getActiveSheet()->mergeCells(&#39;A28;B28&#39;);
$objPHPExcel->getActiveSheet()->unmergeCells(&#39;A28;B28&#39;);
保护cell
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!
$objPHPExcel->getActiveSheet()->protectCells(&#39;A3;E13&#39;, &#39;PHPExcel&#39;);
设置格式
// Set cell number formats
echo date(&#39;H;i;s&#39;) . " Set cell number formats\n";
$objPHPExcel->getActiveSheet()->getStyle(&#39;E4&#39;)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat;;FORMAT_CURRENCY_EUR_SIMPLE);
$objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle(&#39;E4&#39;), &#39;E5;E13&#39; );
设置宽width
// Set column widths
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(12);
设置font
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setName(&#39;Candara&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setSize(20);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setUnderline(PHPExcel_Style_Font;;UNDERLINE_SINGLE);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color;;COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E1&#39;)->getFont()->getColor()->setARGB(PHPExcel_Style_Color;;COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getFont()->setBold(true);
设置align
$objPHPExcel->getActiveSheet()->getStyle(&#39;D11&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment;;HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D12&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment;;HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment;;HORIZONTAL_RIGHT);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A18&#39;)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment;;HORIZONTAL_JUSTIFY);
//垂直居中
$objPHPExcel->getActiveSheet()->getStyle(&#39;A18&#39;)->getAlignment()->setVertical(PHPExcel_Style_Alignment;;VERTICAL_CENTER);
设置column的border
$objPHPExcel->getActiveSheet()->getStyle(&#39;A4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border;;BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border;;BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;C4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border;;BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border;;BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E4&#39;)->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border;;BORDER_THIN);
设置border的color
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getLeft()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getTop()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;D13&#39;)->getBorders()->getBottom()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getBorders()->getTop()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getBorders()->getBottom()->getColor()->setARGB(&#39;FF993300&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;E13&#39;)->getBorders()->getRight()->getColor()->setARGB(&#39;FF993300&#39;);
设置填充颜色
$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFill()->setFillType(PHPExcel_Style_Fill;;FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFill()->getStartColor()->setARGB(&#39;FF808080&#39;);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFill()->setFillType(PHPExcel_Style_Fill;;FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFill()->getStartColor()->setARGB(&#39;FF808080&#39;);
加图片
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(&#39;Logo&#39;);
$objDrawing->setDescription(&#39;Logo&#39;);
$objDrawing->setPath(&#39;./images/officelogo.jpg&#39;);
$objDrawing->setHeight(36);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
$objDrawing = new PHPExcel_Worksheet_Drawing();
$objDrawing->setName(&#39;Paid&#39;);
$objDrawing->setDescription(&#39;Paid&#39;);
$objDrawing->setPath(&#39;./images/paid.png&#39;);
$objDrawing->setCoordinates(&#39;B15&#39;);
$objDrawing->setOffsetX(110);
$objDrawing->setRotation(25);
$objDrawing->getShadow()->setVisible(true);
$objDrawing->getShadow()->setDirection(45);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
在默认sheet后，创建一个worksheet
echo date(&#39;H;i;s&#39;) . " Create new Worksheet object\n";
$objPHPExcel->createSheet();

Nach dem Login kopieren

3、EXCEL导入

require_once &#39;PHPExcel/IOFactory.php&#39;;
$inputFileName = &#39;ceshi.xls&#39;;
$objReader = new PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($inputFileName);
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数

$tempArray = array();
for($j=2;$j<=$highestRow;$j++){
   for($k=&#39;A&#39;;$k<=$highestColumn;$k++){ 
    if($k==&#39;M&#39;$k==&#39;O&#39;) //M列和O列是时间
		$tempArray[] = excelTime($objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue());
	else
		$tempArray[] = $objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue();
	}
	print_r($tempArray);
	unset($tempArray);
}


function excelTime($date, $time = false) {
	if(function_exists(&#39;GregorianToJD&#39;)){
		if (is_numeric( $date )) {
		$jd = GregorianToJD( 1, 1, 1970 );
		$gregorian = JDToGregorian( $jd + intval ( $date ) - 25569 );
		$date = explode( &#39;/&#39;, $gregorian );
		$date_str = str_pad( $date [2], 4, &#39;0&#39;, STR_PAD_LEFT )
		."-". str_pad( $date [0], 2, &#39;0&#39;, STR_PAD_LEFT )
		."-". str_pad( $date [1], 2, &#39;0&#39;, STR_PAD_LEFT )
		. ($time ? " 00:00:00" : &#39;&#39;);
		return $date_str;
		}
	}else{
		$date=$date>25568?$date+1:25569;
		/*There was a bug if Converting date before 1-1-1970 (tstamp 0)*/
		$ofs=(70 * 365 + 17+2) * 86400;
		$date = date("Y-m-d",($date * 86400) - $ofs).($time ? " 00:00:00" : &#39;&#39;);
	}
  return $date;
}

Nach dem Login kopieren