search
Homephp教程php手册PHP与EXCEL PHPExcel
PHP与EXCEL PHPExcel
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 21, 2016 am 08:47 AM
phpexcel

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 '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');
——————————————————————————————————————–
设置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()->mergeCells('A18;E22');
$objPHPExcel->getActiveSheet()->mergeCells('A28;B28');
$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 formats\n";
$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');
加图片
$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 object\n";
$objPHPExcel->createSheet();
3、EXCEL导入

require_once 'PHPExcel/IOFactory.php';
$inputFileName = 'ceshi.xls';
$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;
}





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
Related Article
PHP开发：使用 PHPExcel 处理 Excel 文件PHP开发：使用 PHPExcel 处理 Excel 文件Jun 15, 2023 pm 03:45 PM

随着数字化时代的到来，数据已经成为了我们日常生活和工作中最重要的一部分，而Excel文件则成为数据处理的重要工具之一。相信很多PHP开发者也会在工作中经常遇到使用Excel文件进行数据处理和操作的情况。本文将为大家介绍使用PHPExcel库来处理Excel文件的方法和注意事项。什么是PHPExcel？PHPExcel是一个PHP类

完全指南：如何使用php扩展PHPExcel处理Excel文件完全指南：如何使用php扩展PHPExcel处理Excel文件Jul 28, 2023 pm 10:01 PM

完全指南：如何使用PHP扩展PHPExcel处理Excel文件引言:在处理大量数据和统计分析时，Excel文件经常被用作数据存储和交换的一种常见格式。使用PHP扩展PHPExcel，我们可以轻松地读取、写入和修改Excel文件，从而有效地处理Excel数据。本文将介绍如何使用PHP扩展PHPExcel来处理Excel文件，并提供代码示例。一、安装PHPExc

怎么用phpexcel将Excel文件转成CSV文件并打开怎么用phpexcel将Excel文件转成CSV文件并打开Mar 27, 2023 pm 04:16 PM

​PHPEXCEL是一个优秀的PHP读写Excel文件的类库，它提供了非常充分的API，能够让我们使用PHP来读写Excel文件。而有些时候，我们需要将Excel文件转换成CSV文件，在一些场合下使用。那么，本文主要讲述如何使用PHPEXCEL类库将Excel文件转换成CSV文件，并进行打开。

php如何使用PHPExcel处理Excel文件？php如何使用PHPExcel处理Excel文件？Jun 01, 2023 pm 02:01 PM

PHPExcel是一款开源的PHP库，用于处理MicrosoftExcel（.xls和.xlsx）文件。它可以读取、写入和操作Excel文件，提供了丰富的函数和方法。在PHP项目中使用PHPExcel库，可以快速方便地处理Excel文件，实现数据的导入、导出和数据处理等功能。本文将介绍如何使用PHPExcel处理Excel文件。一、安装PHPExcel要使

PHP开发技巧：如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库PHP开发技巧：如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库Jul 02, 2023 pm 02:28 PM

PHP开发技巧：如何使用PHPExcel和PHPExcel_IOFactory操作MySQL数据库概述：在Web开发中，处理Excel文件是一个常见且重要的任务。PHPExcel是一个功能强大而且易于使用的PHP库，它可以帮助我们读取和写入Excel文件。本文将介绍如何使用PHPExcel和PHPExcel_IOFactory库来操作MySQL数据库。步骤1

使用PHP和PHPExcel创建Excel文件使用PHP和PHPExcel创建Excel文件May 11, 2023 am 08:40 AM

在如今信息快速传递的时代，数据的处理和存储变得越来越重要。而Excel表格的使用是很多人的首选，这是因为Excel表格可以整合各种数据并且可以轻松地进行分析和处理。为了更加高效地完成Excel表格的创建，我们可以使用PHP和PHPExcel这两个强大的工具。在本篇文章中，我们将介绍如何使用PHP和PHPExcel创建Excel文件。1.安装PHPExcel首

phpexcel为什么成为PHP开发者关注的对象phpexcel为什么成为PHP开发者关注的对象Mar 27, 2023 pm 06:15 PM

PHPExcel是一种处理微软 Excel 文件的开源 PHP 库，可以读取、创建、修改和保存 Excel 文件。它是一个强大且高度可定制的工具，可用于处理数据分析、报告生成、数据导入和导出等任务。在本文中，将介绍PHPExcel为什么成为PHP开发者关注的对象。

PHP开发技巧：如何使用PHPExcel操作MySQL数据库PHP开发技巧：如何使用PHPExcel操作MySQL数据库Jul 02, 2023 pm 12:21 PM

PHP开发技巧：如何使用PHPExcel操作MySQL数据库随着互联网的蓬勃发展，大量的数据被存储于数据库中，并且需要进行导入、导出、处理等操作。在PHP开发中，PHPExcel是一个强大的库，可以简化与Excel文件的交互，实现数据的导入与导出。本文将介绍如何使用PHPExcel来操作MySQL数据库，实现数据的导入和导出功能。PHPExcel的安装和配置

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Show More

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
How Long Does It Take To Beat Split Fiction?
4 weeks agoByDDD
R.E.P.O. Save File Location: Where Is It & How to Protect It?
4 weeks agoByDDD
Show More

Hot Tools

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Show More

Hot Topics

Where is the login entrance for gmail email?
7360
15
Java Tutorial
1628
14
CakePHP Tutorial
1353
52
Laravel Tutorial
1265
25
PHP Tutorial
1214
29
Show More