解析PHPExcel使用的常用说明以及把PHPExcel整合... - 分享生活和学习中的点点滴滴

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

前端技术 2023/09/07 PHP

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文件路径
$inputFileName = \'./sampleData/example1.xls\';
/** 用PHPExcel_IOFactory的load方法得到excel操作对象 **/
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
//得到当前活动表格，调用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();
//以二维数组形式返回该表格的数据
$sheetData = $curSheet->toArray(null,true,true,true);
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 \'<hr />\';
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()。

本文地址：https://www.stayed.cn/item/18837

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请联系我

微信
QQ好友
QQ空间
腾讯微博
新浪微博
人人网

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案

2024-02(2)
2023-06(1)
2023-05(1)
2023-04(14)
2023-03(3)
2023-01(6)
2022-12(5)
2022-11(5)
2022-07(2)
2022-06(4)
2022-05(3)
2022-03(1)
2021-12(6)
2021-11(1)
2021-10(3)
2021-09(5)
2021-07(5)
2021-02(2)
2021-01(7)
2020-12(18)
2020-11(14)
2020-10(12)
2020-09(10)
2020-08(22)
2020-07(2)
2020-06(1)
2020-04(5)
2020-03(9)
2020-02(7)
2020-01(9)
2019-12(8)
2019-11(10)
2019-10(11)
2019-09(17)
2019-08(16)
2019-07(6)
2019-06(3)
2019-04(1)
2019-03(8)
2019-02(5)
2019-01(1)
2018-11(2)
2018-10(3)
2018-09(1)
2018-08(3)
2018-07(3)
2018-06(7)
2018-04(4)
2018-03(5)
2018-02(4)
2018-01(22)
2017-12(3)
2017-11(5)
2017-10(15)
2017-09(26)
2017-08(1)
2017-07(3)