PHP与EXCEL 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.");

keyword

$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='A';$k<=$highestColumn;$k++){

    if($k=='M'||$k=='O') //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('GregorianToJD')){

		if (is_numeric( $date )) {

		$jd = GregorianToJD( 1, 1, 1970 );

		$gregorian = JDToGregorian( $jd + intval ( $date ) - 25569 );

		$date = explode( '/', $gregorian );

		$date_str = str_pad( $date [2], 4, '0', STR_PAD_LEFT )

		."-". str_pad( $date [0], 2, '0', STR_PAD_LEFT )

		."-". str_pad( $date [1], 2, '0', STR_PAD_LEFT )

		. ($time ?

" 00:00:00" : '');

		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" : '');

	}

  return $date;

}

PHP与EXCEL PHPExcel的更多相关文章

PHP操作Excel – PHPExcel 基本用法详解
导出excel属性设置//Include classrequire_once('Classes/PHPExcel.php');require_once('Classes/PHPExcel/Writer ...
PHP操作Excel – PHPExcel 基本用法
利用PHP实现对于Excel的写入和读取,主要借助于PHPExcel插件来完成. 准备工作: 1.下载PHPExcel的SDK,下载地址:https://github.com/PHPOffice/PH ...
PHPExcel导出导入excel、csv等格式数据
<?php if(!defined('BASEPATH')) exit('No direct script access allowed'); //物资发料单明细 class Read_writ ...
PHPExcel读取excel的多个sheet存入数据库
//批量导入文章 excel读取 public function importdata ( $filename, $tmp_name ) { //设置超时时间 set_time_limit(0); $ ...
PHP使用phpexcel读取excel文件
PHP读取excel文件 require("Classes/PHPExcel.php"); require("Classes/PHPExcel/IOFactory.php ...
PHPExcel导出excel
如果导出中文时出现乱码,可以尝试将字符串转换成gb2312,例如下面就把$yourStr从utf-8转换成了gb2312: $yourStr = mb_convert_encoding("g ...
PHPExcel生成或读取excel文件
以下是详细代码示例: <?php /** * 以下是使用示例,对于以 //// 开头的行是不同的可选方式,请根据实际需要 * 打开对应行的注释. * 如果使用 Excel5 ,输出的内容应该是G ...
使用phpexcel上传下载excel文件
1. 下载 <?php /** * Created by lonm.shi. * Date: 2012-02-09 * Time: 下午4:54 * To change this templat ...
使用PHPExcel实现Excel文件的导入和导出(模板导出)
在之前有写过一篇文章讲述了使用PHP快速生成excel表格文件并下载,这种方式生成Excel文件,生成速度很快,但是有缺点是:1.单纯的生成Excel文件,生成的文件没有样式,单元格属性(填充色,宽度 ...

随机推荐

android sdk镜像站点
http://android-mirror.bugly.qq.com:8080/include/usage.html Windows A. 打开Android SDK Manager B. 设置代理 ...
【POJ 2528】Mayor’s posters（线段树+离散化）
题目给定每张海报的覆盖区间,按顺序覆盖后,最后有几张海报没有被其他海报完全覆盖.离散化处理完区间端点,排序后再给相差大于1的相邻端点之间再加一个点,再排序.线段树,tree[i]表示节点i对应区间是 ...
Unity路径规划
Unity路径规划转自:http://www.cnblogs.com/zsb517/p/4090629.html 背景酷跑游戏中涉及到弯道.不规则道路. 找来一些酷跑游戏的案例来看,很多都是只有 ...
opencv 2&period;4&period;9+pcl 1&period;6+vs2010+win7 32开发环境配置
最近在做图像方面的开发,需要对软件开发平台进行配置,我查找了关于这些方面的内容,由于软件版本很多,每个人的开发平台又不一样所以在对平台进行搭建过程中遇到了很多问题,下面我将我搭建平台的流程做一个记录. ...
kettle学习-day1-介绍、下载、安装
QQ群:306059317\ 技术支持:荷露叮咚网络学苑http://www.heludd.com/kettle 下载最新版,获取视频教程 kettle安装: 1).下载需要安装的kettle版本,官 ...
c 中可变参数的实现
我们在C语言编程中有时会遇到一些参数个数可变的函数,例如printf()函数,其函数原型为: 例一: int printf( const char* format, ... ...
数组，集合转成DataTable 方法
public static DataTable ToDataTable(IList p_List) { DataTable _DataTable = new DataTable(); if (p_Li ...
考察printf函数返回值
最近偶然间见了这样一道题: #include<stdio.h> int main() { ; printf("%d\n",printf("%d", ...
【Python】关于decode和encode
#-*-coding:utf-8 import sys ''' *首先要搞清楚,字符串在Python内部的表示是unicode编码,因此,在做编码转换时,通常需要以unicode作为中间编码, 即先将 ...
CCF-CIDR合并-201812-3
看着很长的一道题目,其实还可以...但我只有90分...可能有些细节没有注意到...难受! 思路: 数据结构: string str ; 存储32位01串 int len: 前缀长度首先将输入的 ...