验证中...
PHP使用PhpSpreadsheet导入导出
Raw Copy
<?php
/**
* Desc:
* User: hollowcg@foxmail.com
* Date: 2019/5/26
*/
namespace app\admin\controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
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;
class Office
{
/**
* 导出excel表
* $data:要导出excel表的数据,接受一个二维数组
* $name:excel表的表名
* $head:excel表的表头,接受一个一维数组
* $key:$data中对应表头的键的数组,接受一个一维数组
* 备注:此函数缺点是,表头(对应列数)不能超过26;
*循环不够灵活,一个单元格中不方便存放两个数据库字段的值
*/
public function outdata($name='测试表', $data=[], $head=[], $keys=[])
{
$count = count($head); //计算表头数量
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始,循环设置表头:
$sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
}
/*--------------开始从数据库提取信息插入Excel表中------------------*/
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
];
foreach ($data as $key => $item) { //循环设置单元格:
//$key+2,因为第一行是表头,所以写到表格时 从第二行开始写
for ($i = 65; $i < $count + 65;$i++) { //数字转字母从65开始:
if (isset($item[$keys[$i - 65]])){
$sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
$spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
}
}
}
$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(1,1,18,count($data)+1)->applyFromArray($styleArray);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空:
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* 使用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( $file = '', $sheet = 0, $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 ['code'=>0,'data'=>$data];
} catch (\Exception $e) {
throw $e;
}
}
}
Office.php
Raw Copy
<?php
/**
* Desc:
* User: hollowcg@foxmail.com
* Date: 2019/5/26
*/
namespace app\admin\controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
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;
class Office
{
/**
* 导出excel表
* $data:要导出excel表的数据,接受一个二维数组
* $name:excel表的表名
* $head:excel表的表头,接受一个一维数组
* $key:$data中对应表头的键的数组,接受一个一维数组
* 备注:此函数缺点是,表头(对应列数)不能超过26;
*循环不够灵活,一个单元格中不方便存放两个数据库字段的值
*/
public function outdata($name='测试表', $data=[], $head=[], $keys=[])
{
$count = count($head); //计算表头数量
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
for ($i = 65; $i < $count + 65; $i++) { //数字转字母从65开始,循环设置表头:
$sheet->setCellValue(strtoupper(chr($i)) . '1', $head[$i - 65]);
}
/*--------------开始从数据库提取信息插入Excel表中------------------*/
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
],
];
foreach ($data as $key => $item) { //循环设置单元格:
//$key+2,因为第一行是表头,所以写到表格时 从第二行开始写
for ($i = 65; $i < $count + 65;$i++) { //数字转字母从65开始:
if (isset($item[$keys[$i - 65]])){
$sheet->setCellValue(strtoupper(chr($i)) . ($key + 2), $item[$keys[$i - 65]]);
$spreadsheet->getActiveSheet()->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
}
}
}
$spreadsheet->getActiveSheet()->getStyleByColumnAndRow(1,1,18,count($data)+1)->applyFromArray($styleArray);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
//删除清空:
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
/**
* 使用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( $file = '', $sheet = 0, $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 ['code'=>0,'data'=>$data];
} catch (\Exception $e) {
throw $e;
}
}
}

Comment list( 0 )

Sign in for post a comment

Help Search