使⽤PhpSpreadsheet导⼊导出Excel（适⽤各种Excel操作场景）PHP对Excel导⼊&导出操作
最近公司要做报表功能，各种财务报表、⼯资报表、考勤报表等，复杂程度让⼈头⼤，于是特地封装适⽤各⼤场景的导⼊&导出操作，希望各界⼤神⽀出不⾜之处，以便⼩弟继续完善。
phpspreadsheet 引⼊
由于PHPExcel早就停⽌更新维护，所以适⽤phpspreadsheet。不知道如何通过composer拉取项⽬包的同学，可以查看⼀⽂。引⼊⽅法：
composer require phpoffice/phpspreadsheet
引⼊命名空间
由于本⼈项⽬中需要居中、背景、单元格格式等各种操作，所以引⼊较多，⼤家使⽤的时候，可以根据⾃⼰实际需要引⼊。
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
Excel导⼊操作(importExcel)
除了单纯的处理Excel数据外，还可以将Excel中的合并项、公式项、单元格格式提取，提取后可根据业务需求做对应处理后存储起来，以便后续的各种操作。
/**
* 使⽤PHPEXECL导⼊
*
* @param string $file ⽂件地址
* @param int $sheet ⼯作表sheet(传0则获取第⼀个sheet)
* @param int $columnCnt 列数(传0则⾃动获取最⼤列)
* @param array $options 操作选项
* array mergeCells 合并单元格数组
* array formula 公式数组
* array format 单元格格式数组
*
* @return array
* @throws Exception
*/
function importExecl(string $file='', int $sheet=0, int $columnCnt=0,&$options=[])
{
try{
/* 转码 */
$file=iconv("utf-8","gb2312",$file);
if(empty($file)OR!file_exists($file)){
throw new\Exception('⽂件不存在!');
}
/** @var Xlsx $objRead */
$objRead= IOFactory::createReader('Xlsx');
if(!$objRead->canRead($file)){
/** @var Xls $objRead */
$objRead= IOFactory::createReader('Xls');
if(!$objRead->canRead($file)){
throw new\Exception('只⽀持导⼊Excel⽂件！');
}
}
/* 如果不需要获取特殊操作，则只读内容，可以⼤幅度提升读取Excel效率 */ empty($options)&&$objRead->setReadDataOnly(true);
/* 建⽴excel对象 */
$obj=$objRead->load($file);
/* 获取指定的sheet表 */
$currSheet=$obj->getSheet($sheet);
if(isset($options['mergeCells'])){
/* 读取合并⾏列 */
$options['mergeCells']=$currSheet->getMergeCells();
}
if(0==$columnCnt){
/* 取得最⼤的列号 */
$columnH=$currSheet->getHighestColumn();
/
* 兼容原逻辑，循环时使⽤的是⼩于等于 */
$columnCnt= Coordinate::columnIndexFromString($columnH);
}
/* 获取总⾏数 */
$rowCnt=$currSheet->getHighestRow();
$data=[];
/* 读取内容 */
for($_row=1;$_row<=$rowCnt;$_row++){
$isNull=true;
for($_column=1;$_column<=$columnCnt;$_column++){
$cellName= Coordinate::stringFromColumnIndex($_column);
$cellId=$cellName.$_row;
$cell=$currSheet->getCell($cellId);
if(isset($options['format'])){
/* 获取格式 */
$format=$cell->getStyle()->getNumberFormat()->getFormatCode();
/* 记录格式 */
$options['format'][$_row][$cellName]=$format;
}
if(isset($options['formula'])){
/* 获取公式，公式均为=号开头数据 */
$formula=$currSheet->getCell($cellId)->getValue();
if(0===strpos($formula,'=')){
$options['formula'][$cellName.$_row]=$formula;
}
}
if(isset($format)&&'m/d/yyyy'==$format){
/* ⽇期格式翻转处理 */
$cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
}
$data[$_row][$cellName]=trim($currSheet->getCell($cellId)->getFormattedValue());
if(!empty($data[$_row][$cellName])){
$isNull=false;
}
}
/* 判断是否整⾏数据为空，是的话删除该⾏数据 */
if($isNull){
unset($data[$_row]);
}
}
return$data;
}catch(\Exception $e){
throw$e;
}
}
将数据处理好后，可以通过额外配置，将导出的Excel做各种不同的配置，例如打印样式、锁定⾏、背景⾊、宽度等。
Excel导出操作(exportExcel)
/**
* Excel导出，TODO 可继续优化
*
* @param array $datas 导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
* @param string $fileName 导出⽂件名称
* @param array $options 操作选项，例如：
* bool print 设置打印格式
* string freezePane 锁定⾏数，例如表头为第⼀⾏，则锁定表头输⼊A2
* array setARGB 设置背景⾊，例如['A1', 'C1']
* array setWidth 设置宽度，例如['A' => 30, 'C' => 20]
* bool setBorder 设置单元格边框
* array mergeCells 设置合并单元格，例如['A1:J1' => 'A1:J1']
* array formula 设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
* array format 设置格式，整列设置，例如['A' => 'General']
* array alignCenter 设置居中样式，例如['A1', 'A2']
* array bold 设置加粗样式，例如['A1', 'A2']
* string savePath 保存路径，设置后则⽂件保存到服务器，不通过浏览器下载
*/
function exportExcel(array$datas, string $fileName='',array$options=[]): bool
{
try{
if(empty($datas)){
return false;
}
set_time_limit(0);
/** @var Spreadsheet $objSpreadsheet */
$objSpreadsheet=app(Spreadsheet::class);
/* 设置默认⽂字居左，上下居中 */
$styleArray=[
'alignment'=>[
'horizontal'=> Alignment::HORIZONTAL_LEFT,
'vertical'=> Alignment::VERTICAL_CENTER,
],
];
$objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
/* 设置Excel Sheet */
$activeSheet=$objSpreadsheet->setActiveSheetIndex(0);
/* 打印设置 */
if(isset($options['print'])&&$options['print']){
/* 设置打印为A4效果 */
$activeSheet->getPageSetup()->setPaperSize(PageSetup::PAPERSIZE_A4);
/
* 设置打印时边距 */
$pValue=1/2.54;
$activeSheet->getPageMargins()->setTop($pValue/2);
$activeSheet->getPageMargins()->setBottom($pValue*2);
getsavefilename$activeSheet->getPageMargins()->setLeft($pValue/2);
$activeSheet->getPageMargins()->setRight($pValue/2);
}
/* ⾏数据处理 */
foreach($datas as$sKey=>$sItem){
/* 默认⽂本格式 */
$pDataType= DataType::TYPE_STRING;
/
* 设置单元格格式 */
if(isset($options['format'])&&!empty($options['format'])){
$colRow= Coordinate::coordinateFromString($sKey);
/* 存在该列格式并且有特殊格式 */
if(isset($options['format'][$colRow[0]])&&
NumberFormat::FORMAT_GENERAL!=$options['format'][$colRow[0]]){ $activeSheet->getStyle($sKey)->getNumberFormat()
->setFormatCode($options['format'][$colRow[0]]);
if(false!==strpos($options['format'][$colRow[0]],'0.00')&&
is_numeric(str_replace(['￥',','],'',$sItem))){
/* 数字格式转换为数字单元格 */
$pDataType= DataType::TYPE_NUMERIC;
$sItem=str_replace(['￥',','],'',$sItem);
}
}elseif(is_int($sItem)){
$pDataType= DataType::TYPE_NUMERIC;
}
}
$activeSheet->setCellValueExplicit($sKey,$sItem,$pDataType);
/* 存在：形式的合并⾏列，列⼊A1:B2，则对应合并 */
if(false!==strstr($sKey,":")){
$options['mergeCells'][$sKey]=$sKey;
}
}
unset($datas);
/* 设置锁定⾏ */
if(isset($options['freezePane'])&&!empty($options['freezePane'])){
$activeSheet->freezePane($options['freezePane']);
unset($options['freezePane']);
}
/* 设置宽度 */
if(isset($options['setWidth'])&&!empty($options['setWidth'])){
foreach($options['setWidth']as$swKey=>$swItem){
$activeSheet->getColumnDimension($swKey)->setWidth($swItem);
}
unset($options['setWidth']);
}
/* 设置背景⾊ */
if(isset($options['setARGB'])&&!empty($options['setARGB'])){
foreach($options['setARGB']as$sItem){
$activeSheet->getStyle($sItem)
->getFill()->setFillType(Fill::FILL_SOLID)
->getStartColor()->setARGB(Color::COLOR_YELLOW);
}
unset($options['setARGB']);
}
/* 设置公式 */
if(isset($options['formula'])&&!empty($options['formula'])){
foreach($options['formula']as$fKey=>$fItem){
$activeSheet->setCellValue($fKey,$fItem);
}
unset($options['formula']);
}
/* 合并⾏列处理 */
if(isset($options['mergeCells'])&&!empty($options['mergeCells'])){
$activeSheet->setMergeCells($options['mergeCells']);
unset($options['mergeCells']);
}
/* 设置居中 */
if(isset($options['alignCenter'])&&!empty($options['alignCenter'])){
$styleArray=[
'alignment'=>[
'horizontal'=> Alignment::HORIZONTAL_CENTER,
'vertical'=> Alignment::VERTICAL_CENTER,
],
];
foreach($options['alignCenter']as$acItem){
$activeSheet->getStyle($acItem)->applyFromArray($styleArray);
}
unset($options['alignCenter']);
}
/* 设置加粗 */
if(isset($options['bold'])&&!empty($options['bold'])){
foreach($options['bold']as$bItem){
$activeSheet->getStyle($bItem)->getFont()->setBold(true);
}
unset($options['bold']);
}
/
* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最⼤⾏列 */
if(isset($options['setBorder'])&&$options['setBorder']){
$border=[
'borders'=>[
'allBorders'=>[
'borderStyle'=> Border::BORDER_THIN,// 设置border样式
'color'=>['argb'=>'FF000000'],// 设置border颜⾊
],
],
];
$setBorder='A1:'.$activeSheet->getHighestColumn().$activeSheet->getHighestRow(); $activeSheet->getStyle($setBorder)->applyFromArray($border);
unset($options['setBorder']);
}
$fileName=!empty($fileName)?$fileName:(date('YmdHis').'.xlsx');
if(!isset($options['savePath'])){
/* 直接导出Excel，⽆需保存到本地，输出07Excel⽂件 */
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论