最近工作需要，將OA系統(tǒng)的部門和職位導成EXCEL聯(lián)動菜單纹安，用于錄入職員信息尤辱，方便導入到系統(tǒng)中。
于是我開始查資料厢岂，百度根本搜不到有用的資料光督，還是得用google，我不斷的換關(guān)鍵字塔粒，改用英文搜索结借，終于找到了一個鏈接，來自 stack overflow
其實思路就是新開一個數(shù)據(jù)工作表(worksheet)卒茬，用于存放聯(lián)動的信息船老，比如部門和職位咖熟，哪個部門有什么職位按一定的規(guī)律存放好，然后在當前的工作表運用excel的格式努隙，從我們數(shù)據(jù)工作表提取信息。
首先要先下載phpexcel辜昵，建議用composer下載:
composer require --prefer-dist moonlandsoft/yii2-phpexcel "*"
附上github鏈接：phpexcel
寫了一個類荸镊，
-源碼
<?php
namespace app\modules\oa\components\excel;
use Yii;
/**
* 導出Excel數(shù)據(jù)
*/
class ExportExcelDroplist {
public function __construct() {
//引入phpexcel文件，這里要修改成你文件的路徑
include_once(Yii::$app->basePath.'/vendor/phpoffice/phpexcel/Classes/PHPExcel.php');
}
/**
* 設(shè)置架構(gòu)sheet
* @param \PHPExcel $objPHPExcel
* @param array $dataSource 格式如下：
* [
* [ 'name' => '設(shè)計部',
* 'children' => ['設(shè)計師', '設(shè)計主管']
* ],
* [ 'name' => '業(yè)務(wù)部',
* 'children' => ['業(yè)務(wù)員', '業(yè)務(wù)主管']
* ]
* ]
* @return bool
*/
public function setArchitecture(\PHPExcel $objPHPExcel, $dataSource = [])
{
if(empty($objPHPExcel)) {
return false;
}
$msgWorkSheet = new \PHPExcel_Worksheet($objPHPExcel, 'architecture'); //創(chuàng)建一個工作表
$objPHPExcel->addSheet($msgWorkSheet); //插入工作表
//列堪置，若children數(shù)據(jù)多躬存，需擴展
$col = ['B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'];
foreach ($dataSource as $key => $value) {
$num = $key + 1;
$objPHPExcel->getSheetByName('architecture')->SetCellValue("A".$num, $value['name']);
$pCount = count($value['children']) - 1;
if($pCount >= 0) {
foreach ($value['children'] as $pKey => $pValue) {
$objPHPExcel->getSheetByName('architecture')->SetCellValue($col[$pKey].$num, $pValue);
}
$objPHPExcel->addNamedRange(
new \PHPExcel_NamedRange(
$value['name'],
$objPHPExcel->getSheetByName('architecture'),
'B'.$num.':'.$col[$pCount].$num
)
);
}
}
$objPHPExcel->addNamedRange(
new \PHPExcel_NamedRange(
'department',
$objPHPExcel->getSheetByName('architecture'),
'A1:A'.count($dataSource)
)
);
return true;
}
/**
* 設(shè)置聯(lián)動下拉菜單的選項
* @param \PHPExcel $objPHPExcel
* @param int $row 行
* @param string $col1 下拉框1的列標識
* @param string $col2 下拉框2的列標識
*/
public function setRelationDropList(\PHPExcel $objPHPExcel, $row = 1, $col1 = 'A', $col2 = 'B')
{
$objValidation = $objPHPExcel->getActiveSheet()->getCell($col1.$row)->getDataValidation();
$objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('輸入錯誤');
$objValidation->setError('不在列表中的值');
$objValidation->setPromptTitle('請選擇');
$objValidation->setPrompt('請從列表中選擇一個值.');
$objValidation->setFormula1("=department");
$objValidation = $objPHPExcel->getActiveSheet()->getCell($col2.$row)->getDataValidation();
$objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST );
$objValidation->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION );
$objValidation->setAllowBlank(false);
$objValidation->setShowInputMessage(true);
$objValidation->setShowErrorMessage(true);
$objValidation->setShowDropDown(true);
$objValidation->setErrorTitle('輸入錯誤');
$objValidation->setError('不在列表中的值');
$objValidation->setPromptTitle('請選擇');
$objValidation->setPrompt('請從列表中選擇一個值.');
$objValidation->setFormula1('=INDIRECT($'.$col1.'$'.$row.')');
}
/**
* Excel導出demo帶聯(lián)動下拉框[部門和職位]
* @param string $subject 表格主題
* @param array $data 導出數(shù)據(jù)
* @param array $title 表格的字段名
* @param array $dataSource 格式如下：
* [
* [ 'name' => '設(shè)計部',
* 'children' => ['設(shè)計師', '設(shè)計主管']
* ],
* [ 'name' => '業(yè)務(wù)部',
* 'children' => ['業(yè)務(wù)員', '業(yè)務(wù)主管']
* ]
* ]
* @param string $col1 下拉框1的列標識
* @param string $col2 下拉框2的列標識
*/
function export($subject, $title, $dataSource = [], $col1 = 'A', $col2 = 'B'){
// Create new PHPExcel object
$objPHPExcel = new \PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("ctos")
->setLastModifiedBy("ctos")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
//設(shè)置標題樣式
$titleRow = array('A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1','O1','P1','Q1','R1','S1','T1','U1','V1','W1','X1','Y1','Z1','AA1','AB1','AC1','AD1');
$objPHPExcel->getActiveSheet()->getStyle($titleRow[0].':'.$titleRow[count($title)-1])->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle($titleRow[0].':'.$titleRow[count($title)-1])->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
for($a = 0; $a < count($title); $a++){
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($titleRow[$a], $title[$a]);
}
//設(shè)置架構(gòu)sheet
$this->setArchitecture($objPHPExcel, $dataSource);
//設(shè)置聯(lián)動下拉框
for($i = 2; $i < 1000; $i++){
$this->setRelationDropList($objPHPExcel, $i, $col1, $col2);
}
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean();//清除緩沖區(qū),避免亂碼
// Redirect output to a client's web browser (Excel2007)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename='.$subject.''.date('Ymd').'.xlsx');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
}
}
- 用法
/**
* 測試
*/
public function actionTest()
{
$subject = 'demo';
$tile = ['部門','職位'];
$data = [
[ 'name' => '設(shè)計部',
'children' => ['設(shè)計師', '設(shè)計主管']
],
[ 'name' => '業(yè)務(wù)部',
'children' => ['業(yè)務(wù)員', '業(yè)務(wù)主管']
]
];
$excel = new ExportExcelDroplist();
$excel->export($subject, $tile, $data);
}
- 效果
Paste_Image.png
Paste_Image.png
