Yii使用PhpSpreadsHeet 生成 Excel 和导入 Excel 前后端分离版和未分离版
前提下载PhpSpreadsHeet依赖 :
composer require phpoffice/phpspreadsheet
1.开始正题:
1.1 这里使用这个数据库 检测工具表:
DROP TABLE IF EXISTS `test_tool`;
CREATE TABLE `test_tool` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '检测工具id',
`tool_number` varchar(50) NOT NULL COMMENT '工具编号',
`tool_name` varchar(50) NOT NULL COMMENT '工具名称',
`tool_brand` varchar(50) DEFAULT NULL COMMENT '工具品牌',
`tool_class` varchar(50) NOT NULL COMMENT '工具分类',
`tool_calibration_cycle` varchar(50) NOT NULL COMMENT '工具校验周期',
`tool_status` varchar(50) NOT NULL COMMENT '工具状态',
`tool_functions` text NOT NULL COMMENT '工具功能',
`tool_scope` varchar(255) DEFAULT NULL COMMENT '工具适用范围',
`purchase_time` datetime DEFAULT NULL COMMENT '购置日期',
`supplier` varchar(50) DEFAULT NULL COMMENT '供应商',
`storage_address` varchar(255) DEFAULT NULL COMMENT '存放地址',
`tool_notes` text COMMENT '工具备注',
`created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
`tool_file` varchar(255) DEFAULT NULL COMMENT '工具图片地址',
`create_by` int(11) DEFAULT NULL COMMENT '记录创建者user_id',
`updated_by` int(11) DEFAULT NULL COMMENT '记录更新者user_id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
INSERT INTO `test_tool` VALUES ('1', '', '弛度观测仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:35:44', '2020-05-20 10:05:34', '/uploads/5ec4906e2e768_login_bg.jpg', null, null);
INSERT INTO `test_tool` VALUES ('2', '', '电力测量工具', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:07', '2020-05-20 09:31:55', '', null, null);
INSERT INTO `test_tool` VALUES ('3', '', '弧垂弛度观测仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:11', '2020-05-20 09:31:55', '', null, null);
INSERT INTO `test_tool` VALUES ('4', '', '接地电阻测试仪', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:14', '2020-05-20 09:31:55', '', null, null);
INSERT INTO `test_tool` VALUES ('5', '', '电磁式电压互感器', null, '1', '7', '1', '剪指甲', '全部', null, null, null, '使用方便', '2020-05-06 17:36:17', '2020-05-20 09:31:55', '', null, null);
1.2 创建ExitController 这个控制器名字随便你们这么写。
<?php
namespace app\controllers;
use app\models\TestTool;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use yii\web\Controller;
class ExitController extends Controller
{
/**
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function actionSamples()
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//图片
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
//设置工作表标题名称
//$worksheet->setTitle('phpspreadsheet——检测工具');
//表头 1
$spreadsheet->getActiveSheet()->setTitle('检测工具');
//设置单元格内容
$k=1;
$worksheet->setCellValue('A' . $k, 'id');
$worksheet->setCellValue('B' . $k, '工具编号');
$worksheet->setCellValue('C' . $k, '工具名称');
$worksheet->setCellValue('D' . $k, '工具品牌');
$worksheet->setCellValue('E' . $k, '工具分类');
$worksheet->setCellValue('F' . $k, '工具校验周期');
$worksheet->setCellValue('G' . $k, '工具状态');
$worksheet->setCellValue('H' . $k, '工具功能');
$worksheet->setCellValue('I' . $k, '工具适用范围');
$worksheet->setCellValue('J' . $k, '购置日期');
$worksheet->setCellValue('K' . $k, '供应商');
$worksheet->setCellValue('L' . $k, '存放地址');
$worksheet->setCellValue('M' . $k, '工具备注');
$worksheet->setCellValue('N' . $k, '工具图片地址');
// 设置个表格宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(30);
$styleArray = [
'font' => [
'bold' => true,
'color'=>[
'argb'=>Color::COLOR_BLUE
]
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//背景颜色
$worksheet->getStyle('A1:N1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFF0000');// 设置背景色
//设置单元格样式
$worksheet->getStyle('A1:N1')->applyFromArray($styleArray)->getFont()->setSize(12);
//访问的数据库
$jzInfo = TestTool::find()->all();
$len = count($jzInfo);
$j = 0;
for ($i=0; $i < $len; $i++) {
$j = $i + 2; //从表格第3行开始
$worksheet->setCellValueByColumnAndRow(1, $j, $jzInfo[$i]['id']);
$worksheet->setCellValueByColumnAndRow(2, $j, $jzInfo[$i]['tool_number']);
$worksheet->setCellValueByColumnAndRow(3, $j, $jzInfo[$i]['tool_name']);
$worksheet->setCellValueByColumnAndRow(4, $j, $jzInfo[$i]['tool_brand']);
$worksheet->setCellValueByColumnAndRow(5, $j, $jzInfo[$i]['tool_class']);
$worksheet->setCellValueByColumnAndRow(6, $j, $jzInfo[$i]['tool_calibration_cycle']);
$worksheet->setCellValueByColumnAndRow(7, $j, $jzInfo[$i]['tool_status']);
$worksheet->setCellValueByColumnAndRow(8, $j, $jzInfo[$i]['tool_functions']);
$worksheet->setCellValueByColumnAndRow(9, $j, $jzInfo[$i]['tool_scope']);
$worksheet->setCellValueByColumnAndRow(10, $j, $jzInfo[$i]['purchase_time']);
$worksheet->setCellValueByColumnAndRow(11, $j, $jzInfo[$i]['supplier']);
$worksheet->setCellValueByColumnAndRow(12, $j, $jzInfo[$i]['storage_address']);
$worksheet->setCellValueByColumnAndRow(13, $j, $jzInfo[$i]['tool_notes']);
$worksheet->setCellValueByColumnAndRow(14, $j, $jzInfo[$i]['tool_file']);
}
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
$total_jzInfo = $len + 1;
//添加所有边框/居中
$worksheet->getStyle('A1:N'.$total_jzInfo)->applyFromArray($styleArrayBody);
//设置文件前缀
$filename = date('Y-m-d', time()) . rand(1000, 9999);
$filename = $filename.'无忧巡检工具列表.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}
public function actionImp()
{
}
}
1.3访问url 我本机是具体看你们自己的:
http://localhost/yii-excal/basic/web/index.php?r=exit/samples
就会下载这个 无忧巡检工具列表.xlsx
这里面的数据就是来自数据库,具体看我上面的代码。
1.4 创建其他有导入和导出的页面方式。
1.4.1 创建Excel
<?php
namespace app\common\tools;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
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\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
class Excel
{
# 导入excel
static 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 $data;
} catch (\Exception $e) {
throw $e;
}
}
# 导出
/**
* @param $data
* @param $title
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
static function exportExcel($data,$title)
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('检测工具');
//设置宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
//设置样式
$styleArray = [
'font' => [
'bold' => true,
'color'=>[
'argb'=>Color::COLOR_BLUE
]
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//背景颜色
$worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
//设置单元格样式
$worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
//表头 设置单元格内容
foreach ($title as $key => $value) {
$worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}
$row = 2; //从第二行开始
//样式
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//数据库的长度+1
$length= count($data)+1;
$worksheet->getStyle('A1:R'.$length)->applyFromArray($styleArrayBody);
foreach ($data as $item) {
$column = 1; //从第一列设置并初始化
foreach ($item as $value) {
$worksheet->setCellValueByColumnAndRow($column, $row, $value); //哪一列哪一行设置哪个值
$column++; //列数加1
}
$row++; //行数加1
}
//输出到浏览器
$fileName = date('Y-m-d', time()) . rand(1000, 9999);
$fileName = $fileName.'无忧巡检工具列表';
$fileType = 'Xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
self::excelBrowserExport($fileName, $fileType);
$writer->save('php://output');
}
#格式校验
static public function excelBrowserExport($fileName, $fileType)
{
//文件名称校验
if (!$fileName) {
throw new Exception('文件名不能为空');
}
//Excel文件类型校验
$type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
if (!in_array($fileType, $type)) {
throw new Exception('未知文件类型');
}
if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');
} else {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');
}
}
}
1.4.2 创建TestController
<?php
namespace app\controllers;
use app\models\TestTool;
use app\models\UploadForm;
use app\common\tools\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use Yii;
use yii\web\UploadedFile;
class TestController extends \yii\web\Controller
{
# 用来测试的index页面
public function actionIndex()
{
# 文件上传的model
$file = new UploadForm();
return $this->render('index', [
'file' => $file
]);
}
# 导入表格
public function actionImport_excel_data(){
$model = new UploadForm();
if (Yii::$app->request->isPost) {
$model->file = UploadedFile::getInstance($model, 'file');
if ($model->file && $model->validate()) {
$path = 'uploads/';
if (!file_exists($path)) {
mkdir($path, 0755, true);
}
$time = time();
$rand = rand(100,999);
$basename = $model->file->baseName;
$filename = md5($basename.$time.$rand);
$fname = $path . $filename . '.' . $model->file->extension;
if ($model->file && $model->validate()) {
$model->file->saveAs($fname);
}
$fpath = Yii::$app->BasePath.'/web/'.$fname;
// echo $fpath;
// exit;
$data = Excel::importExecl($fpath);
# 得到表头
$biaotou = $data[1];
$count = count($data);
// var_dump($data);
// exit;
if ($biaotou['A'] != 'id'||$biaotou['B'] != '工具编号'||$biaotou['C'] != '工具名称'
||$biaotou['D'] != '工具品牌'||$biaotou['E'] != '工具分类'||$biaotou['F'] != '工具校验周期'
||$biaotou['G'] != '工具状态'||$biaotou['H'] != '工具功能'||$biaotou['I'] != '工具适用范围'
||$biaotou['J'] != '购置日期'||$biaotou['K'] != '供应商'||$biaotou['L'] != '存放地址'
||$biaotou['M'] != '工具备注'||$biaotou['N'] != '记录创建时间'||$biaotou['O'] != '记录更新时间'
||$biaotou['P'] != '工具图片地址'||$biaotou['Q'] != '记录创建者'||$biaotou['R'] != '记录更新者'
){
return "表格表头不匹配";
}
# 这里要从第二行开始读 因为第一行是表头
# 拿到数据后写入数据里面 建议这里用批量写入的方式而不是再循环里面写入数据库
for ($i = 2; $i<$count+1; $i++){
var_dump($data[$i]);
// echo 'id'.$data[$i]['A']."</br>";
// echo '工具编号'.$data[$i]['B']."</br>";
// echo '工具品牌'.$data[$i]['C']."</br>";
// echo '工具分类'.$data[$i]['D']."</br>";
// echo '工具适用范围'.$data[$i]['E']."</br>";
}
}
}
return "";
}
# 导出表格
/**
* @throws \Exception
*/
public function actionExport_excel_data(){
$data=TestTool::find()->all();
$title = ['id', '工具编号', '工具名称','工具品牌','工具分类','工具校验周期','工具状态','工具功能'
,'工具适用范围','购置日期','供应商','存放地址','工具备注','记录创建时间','记录更新时间','工具图片地址',
'记录创建者','记录更新者'];
Excel::exportExcel($data,$title);
}
}
1.4.3 创建UploadForm 因为使用到了文件上传
<?php
namespace app\models;
class UploadForm extends \yii\base\Model
{
/**
* @var
*/
public $file;
/**
* @return array the validation rules.
*/
public function rules()
{
return [
[['file'], 'file'],
];
}
}
1.4.4 创建视图 viwes/test/ index.php
<?php
use yii\bootstrap\ActiveForm;
?>
<!--导入表格-->
<h2>导入表格</h2>
<?php $form = ActiveForm::begin(['options' => ['enctype' => 'multipart/form-data'],'action' => ['import_excel_data'],'method'=>'post']) ?>
<?= $form->field($file, 'file')->fileInput() ?>
<button>导入表格</button>
<?php ActiveForm::end() ?>
<!--下载表格-->
<a href="index.php?r=test/export_excel_data">下载表格</a>
完成以上就是可以访问:
http://localhost/yii-excal/basic/web/index.php?r=test/index
就可以完成导入和导出。
1.5创建前后端分离的api方式:
使用导入前下载一个模板 然后再模板中输入 对应的数据 就可以说进行导入:
1.5.1创建ExcelTools
<?php
namespace app\helper;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
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\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
class ExcelTools
{
// 导入excel
static 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 $data;
} catch (\Exception $e) {
throw $e;
}
}
// 导出
/**
* @param $data
* @param $title
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
static function exportExcel($data,$title)
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('检测工具');
//设置宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
//设置样式
$styleArray = [
'font' => [
'bold' => true,
'color'=>[
'argb'=>Color::COLOR_BLUE
]
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//背景颜色
$worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
//设置单元格样式
$worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
//表头 设置单元格内容
foreach ($title as $key => $value) {
$worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}
$row = 2; //从第二行开始
//样式
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//数据库的长度+1
$length= count($data)+1;
$worksheet->getStyle('A1:R'.$length)->applyFromArray($styleArrayBody);
foreach ($data as $item) {
$column = 1; //从第一列设置并初始化
foreach ($item as $value) {
$worksheet->setCellValueByColumnAndRow($column, $row, $value); //哪一列哪一行设置哪个值
$column++; //列数加1
}
$row++; //行数加1
}
//输出到浏览器
$fileName = date('Y-m-d', time()) . rand(1000, 9999);
$fileName = $fileName.'EWY-Tools';
$fileType = 'Xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
self::excelBrowserExport($fileName, $fileType);
$writer->save('php://output');
}
//格式校验
static public function excelBrowserExport($fileName, $fileType)
{
//文件名称校验
if (!$fileName) {
throw new Exception('文件名不能为空');
}
//Excel文件类型校验
$type = ['Excel2007', 'Xlsx', 'Excel5', 'xls'];
if (!in_array($fileType, $type)) {
throw new Exception('未知文件类型');
}
if ($fileType == 'Excel2007' || $fileType == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx"');
header('Cache-Control: max-age=0');
} else {
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $fileName . '.xls"');
header('Cache-Control: max-age=0');
}
}
/**
* 模板下载 导出
* @param $title
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
static function exportTemplate($title)
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('检测工具');
//设置宽度
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(5);
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setWidth(28);
$spreadsheet->getActiveSheet()->getColumnDimension('D')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('E')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('F')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('G')->setWidth(10);
$spreadsheet->getActiveSheet()->getColumnDimension('H')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('I')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('J')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('K')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('L')->setWidth(20);
$spreadsheet->getActiveSheet()->getColumnDimension('M')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('N')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('O')->setWidth(18);
$spreadsheet->getActiveSheet()->getColumnDimension('P')->setWidth(50);
$spreadsheet->getActiveSheet()->getColumnDimension('Q')->setWidth(15);
$spreadsheet->getActiveSheet()->getColumnDimension('R')->setWidth(15);
//设置样式
$styleArray = [
'font' => [
'bold' => true,
'color'=>[
'argb'=>Color::COLOR_BLUE
]
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//背景颜色
$worksheet->getStyle('A1:R1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('787FF6');// 设置背景色
//设置单元格样式
$worksheet->getStyle('A1:R1')->applyFromArray($styleArray)->getFont()->setSize(12);
//表头 设置单元格内容
foreach ($title as $key => $value) {
$worksheet->setCellValueByColumnAndRow($key+1, 1, $value);
}
//输出到浏览器
$fileName = date('Y-m-d', time()) . rand(1000, 9999);
$fileName = $fileName.'EWY-ToolTemplate';
$fileType = 'Xlsx';
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
self::excelBrowserExport($fileName, $fileType);
$writer->save('php://output');
}
}
1.5.2 下载工具模板
/**
*
* 下载工具模板
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public function actionDownload()
{
$title = ['id', '工具编号', '工具名称','工具品牌','工具分类','工具校验周期','工具状态','工具功能'
,'工具适用范围','购置日期','供应商','存放地址','工具备注','记录创建时间','记录更新时间','工具图片地址',
'记录创建者','记录更新者'];
ExcelTools::exportTemplate($title);
}
1.5.3 导入到数据库
public function actionImport_excel_data()
{
$params = \Yii::$app->request->getBodyParams();
$model = new UploadForm();
if (Yii::$app->request->isPost) {
$model->File = UploadedFile::getInstance($model, 'File');
if ($model->File && $model->validate()) {
$path = 'uploads/';
if (!file_exists($path)) {
mkdir($path, 0755, true);
}
$time = time();
$rand = rand(100,999);
$basename = $model->File->baseName;
$filename = md5($basename.$time.$rand);
$Fname = $path . $filename . '.' . $model->File->extension;
if ($model->File && $model->validate()) {
$model->File->saveAs($Fname);
}
$fpath = Yii::$app->BasePath.'/web/'.$Fname;
$data = ExcelTools::importExecl($fpath);
# 得到表头
$biaotou = $data[1];
$count = count($data);
if ($biaotou['A'] != 'id'||$biaotou['B'] != '工具编号'||$biaotou['C'] != '工具名称'
||$biaotou['D'] != '工具品牌'||$biaotou['E'] != '工具分类'||$biaotou['F'] != '工具校验周期'
||$biaotou['G'] != '工具状态'||$biaotou['H'] != '工具功能'||$biaotou['I'] != '工具适用范围'
||$biaotou['J'] != '购置日期'||$biaotou['K'] != '供应商'||$biaotou['L'] != '存放地址'
||$biaotou['M'] != '工具备注'||$biaotou['N'] != '记录创建时间'||$biaotou['O'] != '记录更新时间'
||$biaotou['P'] != '工具图片地址'||$biaotou['Q'] != '记录创建者'||$biaotou['R'] != '记录更新者'
){
return "表格表头不匹配";
}
# 这里要从第二行开始读 因为第一行是表头
# 拿到数据后写入数据里面 建议这里用批量写入的方式而不是再循环里面写入数据库
for ($i = 2; $i<$count+1; $i++){
$model = new TestTool();
$model->tool_number=$data[$i]['B'];
$model->tool_name=$data[$i]['C'];
$model->tool_brand=$data[$i]['D'];
$model->tool_class=$data[$i]['E'];
$model->tool_calibration_cycle=$data[$i]['F'];
$model->tool_status=$data[$i]['G'];
$model->tool_functions=$data[$i]['H'];
$model->tool_scope=$data[$i]['I'];
// $model->purchase_time=$data[$i]['J']; 待处理
$model->supplier=$data[$i]['K'];
$model->storage_address=$data[$i]['L'];
$model->tool_notes=$data[$i]['M'];
$model->save();
}
return "导入成功";
}
}
return "";
}
导出和上面的的导出基本以上这里就不展示的。
具体调试api 大家百度下PostMan excel 接口调试方式就是可以。