Home >Backend Development >PHP Tutorial > PHPExcel施用的常用说明以及把PHPExcel整合进CI框架

PHPExcel施用的常用说明以及把PHPExcel整合进CI框架

WBOYOriginal: 2016-06-13 13:26:451034browse

PHPExcel使用的常用说明以及把PHPExcel整合进CI框架

excel的写入与生成操作：

include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';
//或者include 'PHPExcel/Writer/Excel5.php'; 用于输出.xls的
include 'PHPExcel/IOFactory.php';//phpexcel工厂类
//创建一个excel
$objPHPExcel = new PHPExcel();
//保存excel―2007格式
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
//也可以使用
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");

//或者$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');

//直接生成文件

$objWriterr->save(‘文件名’);

//设置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 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());
//处理中文输出问题
//需要将字符串转化为UTF-8编码，才能正常输出，否则中文字符将输出为空白，如下处理：
$str = iconv('gb2312', 'utf-8', $str);
或者你可以写一个函数专门处理中文字符串：
function convertUTF8($str)
{
if(empty($str)) return '';
return iconv('gb2312', 'utf-8', $str);
}

读取excel

1. 导入一个Excel最简单的方法是使用PHPExel的IO Factory，调用PHPExcel_IOFactory类的静态法load，它可以自动识别文档格式，包括Excel2007、Excel2003XML、OOCalcSYLK、Gnumeric、CSV。返回一个PHPExcel的实例。

//加载工厂类

include'PHPExcel/IOFactory.php'; //要读取的xls文件路径

//得到当前活动表格，调用toArray方法，得到表格的二维数组

$sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

var_dump($sheetData);

1. 创建一个ExcelReader去加载一个Excel文档

如果你知道这个Excel文档的格式，可以建立一个相应的Reader去加载要读取的Excel文档。但是如果你加载了错误的文档类型，可会产生不可预知的错误。

$inputFileName = './sampleData/example1.xls';

/** Create a new Excel5 Reader **/
$objReader = new PHPExcel_Reader_Excel5();
// $objReader = new PHPExcel_Reader_Excel2007();
// $objReader = new PHPExcel_Reader_Excel2003XML();
// $objReader = new PHPExcel_Reader_OOCalc();
// $objReader = new PHPExcel_Reader_SYLK();
// $objReader = new PHPExcel_Reader_Gnumeric();
// $objReader = new PHPExcel_Reader_CSV();
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);

//得到当前活动sheet

$curSheet =$objPHPExcel->getActiveSheet();

//以二维数组形式返回该表格的数据

$sheetData = $curSheet->toArray(null,true,true,true);

var_dump($sheetData);

也可以用PHPExcel_IOFactory的createReader方法去得到一个Reader对象，无需知道要读取文件的格式。

$inputFileType = 'Excel5';
// $inputFileType = 'Excel2007';
// $inputFileType = 'Excel2003XML';
// $inputFileType = 'OOCalc';
// $inputFileType = 'SYLK';
// $inputFileType = 'Gnumeric';
// $inputFileType = 'CSV';
$inputFileName = './sampleData/example1.xls';

/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);

//得到当前活动sheet

$curSheet = $objPHPExcel->getActiveSheet();

//以二维数组形式返回该表格的数据

var_dump($sheetData);

如果在读取文件之前，文件格式未知，你可以通过IOFactory 的 identify()方法得到文件类型，然后通过createReader()方法去穿件阅读器。

$inputFileName = './sampleData/example1.xls';

/** 确定输入文件的格式 **/
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
/** 穿件相对应的阅读器 **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** 加载要读取的文件 **/
$objPHPExcel = $objReader->load($inputFileName);

2. 设置Excel的读取选项

在使用load()方法加载文件之前，可以设置读取选项来控制load的行为.

2.1. ReadingOnly Data from a Spreadsheet File

setReadDataOnly()方法，配置阅读器不关注表格数据的数据类型，都以string格式返回

$inputFileType = 'Excel5';
$inputFileName = './sampleData/example1.xls';

/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/** 配置单元格数据都以字符串返回 **/
$objReader->setReadDataOnly(true);
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);

$sheetData =$objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

var_dump($sheetData);

返回数据：

array(8) {

[1]=>

array(6) {

["A"]=>

string(15) "Integer Numbers"

["B"]=>

string(3)"123"

["C"]=>

string(3)"234"

["D"]=>

string(4)"-345"

["E"]=>

string(3)"456"

["F"]=>

NULL

}

[2]=>

array(6) {

["A"]=>

string(22) "Floating PointNumbers"

["B"]=>

string(4) "1.23"

["C"]=>

string(5) "23.45"

["D"]=>

string(10) "0.00E+0.00"

["E"]=>

string(6) "-45.68"

["F"]=>

string(7) "￡56.78"

}

[3]=>

array(6) {

["A"]=>

string(7) "Strings"

["B"]=>

string(5) "Hello"

["C"]=>

string(5) "World"

["D"]=>

NULL

["E"]=>

string(8) "PHPExcel"

["F"]=>

NULL

}

[4]=>

array(6) {

["A"]=>

string(8) "Booleans"

["B"]=>

bool(true)

["C"]=>

bool(false)

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[5]=>

array(6) {

["A"]=>

string(5) "Dates"

["B"]=>

string(16) "19 December 1960"

["C"]=>

string(15) "10 October 2010"

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[6]=>

array(6) {

["A"]=>

string(5) "Times"

["B"]=>

string(4) "9:30"

["C"]=>

string(5) "23:59"

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[7]=>

array(6) {

["A"]=>

string(8) "Formulae"

["B"]=>

string(3) "468"

["C"]=>

string(7) "-20.998"

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[8]=>

array(6) {

["A"]=>

string(6) "Errors"

["B"]=>

string(4) "#N/A"

["C"]=>

string(7) "#DIV/0!"

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

如果不设置则返回：

array(8) {

[1]=>

array(6) {

["A"]=>

string(15) "Integer Numbers"

["B"]=>

float(123)

["C"]=>

float(234)

["D"]=>

float(-345)

["E"]=>

float(456)

["F"]=>

NULL

}

[2]=>

array(6) {

["A"]=>

string(22) "Floating Point Numbers"

["B"]=>

float(1.23)

["C"]=>

float(23.45)

["D"]=>

float(3.45E-6)

["E"]=>

float(-45.678)

["F"]=>

float(56.78)

}

[3]=>

array(6) {

["A"]=>

string(7) "Strings"

["B"]=>

string(5) "Hello"

["C"]=>

string(5) "World"

["D"]=>

NULL

["E"]=>

string(8) "PHPExcel"

["F"]=>

NULL

}

[4]=>

array(6) {

["A"]=>

string(8) "Booleans"

["B"]=>

bool(true)

["C"]=>

bool(false)

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[5]=>

array(6) {

["A"]=>

string(5) "Dates"

["B"]=>

float(22269)

["C"]=>

float(40461)

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[6]=>

array(6) {

["A"]=>

string(5) "Times"

["B"]=>

float(0.39583333333333)

["C"]=>

float(0.99930555555556)

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[7]=>

array(6) {

["A"]=>

string(8) "Formulae"

["B"]=>

float(468)

["C"]=>

float(-20.99799655)

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

[8]=>

array(6) {

["A"]=>

string(6) "Errors"

["B"]=>

string(4) "#N/A"

["C"]=>

string(7) "#DIV/0!"

["D"]=>

NULL

["E"]=>

NULL

["F"]=>

NULL

}

Reading Only Data from a SpreadsheetFile applies to Readers:

Excel2007 YES Excel5 YES Excel2003XML YES

OOCalc YES SYLK NO Gnumeric YES

CSV NO

2.2. ReadingOnly Named WorkSheets from a File

setLoadSheetsOnly(),设置要读取的worksheet，接受worksheet的名称作为参数。

/** PHPExcel_IOFactory */

include'PHPExcel/IOFactory.php';

$inputFileType = 'Excel5';

// $inputFileType = 'Excel2007';

// $inputFileType = 'Excel2003XML';

// $inputFileType = 'OOCalc';

// $inputFileType = 'Gnumeric';

$inputFileName ='./sampleData/example1.xls';

$sheetname = 'Data Sheet #2';

echo 'Loading file',pathinfo($inputFileName,PATHINFO_BASENAME),' using IOFactory with a definedreader type of ',$inputFileType,'
';

$objReader = PHPExcel_IOFactory::createReader($inputFileType);

echo 'Loading Sheet"',$sheetname,'" only
';

$objReader->setLoadSheetsOnly($sheetname);

$objPHPExcel =$objReader->load($inputFileName);

echo '

echo$objPHPExcel->getSheetCount(),' worksheet',(($objPHPExcel->getSheetCount()== 1) ? '' : 's'),' loaded

';

$loadedSheetNames =$objPHPExcel->getSheetNames();

foreach($loadedSheetNames as$sheetIndex => $loadedSheetName) {

echo $sheetIndex,' -> ',$loadedSheetName,'
';

}

如果想读取多个worksheet，可以传递一个数组

$inputFileType = 'Excel5'; 
$inputFileName = './sampleData/example1.xls'; 
$sheetnames = array('Data Sheet #1','Data Sheet #3'); 

/** Create a new Reader of the type defined in $inputFileType **/
$objReader = PHPExcel_IOFactory::createReader($inputFileType); 
/** Advise the Reader of which WorkSheets we want to load **/
$objReader->setLoadSheetsOnly($sheetnames); 
/** Load $inputFileName to a PHPExcel Object **/
$objPHPExcel = $objReader->load($inputFileName);

如果想读取所有worksheet，可以调用setLoadAllSheets()。

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：帮小弟我把这函数精简下啊 Next article： Cannot load or save configuration解决方法

See more

PHPExcel施用的常用说明以及把PHPExcel整合进CI框架

1. 创建一个ExcelReader去加载一个Excel文档

2. 设置Excel的读取选项

2.1. ReadingOnly Data from a Spreadsheet File

2.2. ReadingOnly Named WorkSheets from a File

Related articles