123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412 |
- <?php
- // +----------------------------------------------------------------------
- // | 海豚PHP框架 [ DolphinPHP ]
- // +----------------------------------------------------------------------
- // | 版权所有 2016~2017 河源市卓锐科技有限公司 [ http://www.zrthink.com ]
- // +----------------------------------------------------------------------
- // | 官方网站: http://dolphinphp.com
- // +----------------------------------------------------------------------
- // | 开源协议 ( http://www.apache.org/licenses/LICENSE-2.0 )
- // +----------------------------------------------------------------------
- namespace plugins\Excel\controller;
- use app\common\controller\Common;
- use think\Db;
- require_once dirname(dirname(__FILE__)) . '/PHPExcel/PHPExcel.php';
- require_once dirname(dirname(__FILE__)) . '/PHPExcel/PHPExcel/IOFactory.php';
- /**
- * Excel控制器
- * @package plugins\Excel\controller
- */
- class Excel extends Common
- {
- /**
- * 导出Excel
- * @param string $expTitle 文件名称
- * @param array $expCellName 表头
- * @param array $expTableData 数据
- * @param array $mergeCells 合并单元格
- * @author HongPing <hongping626@qq.com>
- * @alter 蔡伟明 <314013107@qq.com>
- */
- public function export($expTitle = '', $expCellName = [], $expTableData = [], $mergeCells = [])
- {
- $expTitle == '' && $this->error('请填写文件名');
- empty($expCellName) && $this->error('请填写表头');
- $file_type = 'xls';
- if (strpos($expTitle, '.')) {
- $file_name = explode('.', $expTitle);
- if (strtolower(end($file_name)) != 'xls' && strtolower(end($file_name)) != 'xlsx') {
- $file_name = $expTitle.'.xls';
- } else {
- $file_type = end($file_name);
- $file_name = $expTitle;
- }
- } else {
- $file_name = $expTitle.'.xls';
- }
- $file_type = strtolower($file_type) == 'xls' ? 'Excel5' : 'Excel2007';
- if (ob_get_length()) ob_end_clean();
- $fileName = $file_name; //or $xlsTitle 文件名称可根据自己情况设定
- $cellNum = count($expCellName);
- $dataNum = count($expTableData);
- $objPHPExcel = new \PHPExcel();
- $cellName = ['A','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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ'];
- //合并单元格
- if (!empty($mergeCells)) {
- foreach ($mergeCells as $mergeCell) {
- $objPHPExcel->getActiveSheet()->mergeCells($mergeCell);
- }
- }
- $objSheet = $objPHPExcel->getActiveSheet();
- for($i=0;$i<$cellNum;$i++){
- $objSheet->setCellValue($cellName[$i].'1', $expCellName[$i][2]);
- //根据内容设置单元格宽度
- $cellWidth = $expCellName[$i][1] == 'auto' ? strlen($expCellName[$i][2]) : $expCellName[$i][1];
- $objSheet->getColumnDimension($cellName[$i])->setWidth($cellWidth);
- // $objPHPExcel->getActiveSheet(0)->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][1]);
- $objSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
- // if(isset($expCellName[$i][3])) {
- // switch ($expCellName[$i][3]) {
- // case 'FORMAT_NUMBER':
- // $objSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
- // break;
- // case 'FORMAT_TEXT':
- // $objSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
- // break;
- // }
- // }
- }
- // Miscellaneous glyphs, UTF-8
- for($i=0;$i<$dataNum;$i++){
- for($j=0;$j<$cellNum;$j++){
- if (isset($expTableData[$i][$expCellName[$j][0]])) {
- $objSheet->setCellValueExplicit($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]], \PHPExcel_Cell_DataType::TYPE_STRING);
- }
- // if (isset($expCellName[$i][3]) && $expCellName[$i][3] == 'FORMAT_TEXT') {
- //
- // } else {
- // $objSheet->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
- // }
- }
- }
- //下载
- 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='.$fileName);
- header("Content-Transfer-Encoding:binary");
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $file_type);
- $objWriter->save('php://output');
- exit();
- }
- /**
- * 导入Excel
- * @param string $file upload file $_FILES
- * @param null $table 导入到指定表,如果不指定,则返回拼接的数据,不执行导入
- * @param null $fields 字段名 只导入指定字段名的数据,excel表中其他在字段名找不到的,将不导入
- * 传入格式:array('id' => 'id', '用户名' => 'username'),key为数据库字段名,value为excel表头名
- * @param int $type 导入模式,0默认为增量导入(导入并跳过已存在的数据),1为覆盖导入(导入并覆盖已存在的数据)
- * @param null $where 查询依据,做配合查询使用
- * @param null $main_field 作为判断导入模式依据的主要字段,比如指定为KSH,则用KSH这个字段来判断是否已存在数据库
- * @author HongPing <hongping626@qq.com>
- * @alter 蔡伟明 <314013107@qq.com>
- * @return array
- */
- public function import($file, $table = null, $fields = null, $type = 0, $where = null, $main_field = null)
- {
- if(!file_exists($file)){
- return ["error" => 1, 'message' => '文件未找到!']; //file not found!
- }
- $file_info = explode('.', $file);
- $file_ext = strtolower(end($file_info));
- if ($file_ext != 'xls' && $file_ext != 'xlsx') {
- return ["error" => 1, 'message' => '文件类型不正确!'];
- }
- $file_type = $file_ext == 'xls' ? 'Excel5' : 'Excel2007';
- $objReader = \PHPExcel_IOFactory::createReader($file_type); //需要在前面加反斜杠,因为命名空间
- try{
- $PHPReader = $objReader->load($file);
- }catch(\Exception $e){}
- if(!isset($PHPReader)) return ["error" => 1, 'message' => '读取错误!']; //read error!
- $allWorksheets = $PHPReader->getAllSheets(); //获取所有工作表
- $i = 0;
- $array = [];
- //循环读取每个工作表
- foreach($allWorksheets as $objWorksheet){
- $sheet_name = $objWorksheet->getTitle(); //获取当前工作表名
- $allRow = $objWorksheet->getHighestRow();//当前工作表的行数
- $highestColumn = $objWorksheet->getHighestColumn();//当前工作表的列数,excel以字母表示列数,比如B表示2列
- $allColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn); //当前工作表的列数,将字母表示的列数转换为数字
- $array[$i]["Title"] = $sheet_name;
- $array[$i]["Cols"] = $allColumn;
- $array[$i]["Rows"] = $allRow;
- $arr = [];
- $isMergeCell = [];
- foreach ($objWorksheet->getMergeCells() as $cells) {//找出合并单元格
- foreach (\PHPExcel_Cell::extractAllCellReferencesInRange($cells) as $cellReference) {
- $isMergeCell[$cellReference] = true;
- }
- }
- //循环读取每行数据
- for($currentRow = 1; $currentRow <= $allRow; $currentRow++){
- $row = [];
- for($currentColumn = 0; $currentColumn < $allColumn; $currentColumn++){
- $cell = $objWorksheet->getCellByColumnAndRow($currentColumn, $currentRow);
- $afCol = \PHPExcel_Cell::stringFromColumnIndex($currentColumn+1); //后一列的索引,比如当前列为A,后一列则为B
- $bfCol = \PHPExcel_Cell::stringFromColumnIndex($currentColumn-1); //前一列的索引,如果当前列为第一列,则前一列索引为@
- $col = \PHPExcel_Cell::stringFromColumnIndex($currentColumn); //当前列的索引
- $address = $col.$currentRow;//当前单元格的位置
- $value = $objWorksheet->getCell($address)->getValue(); //当前单元格的值
- $value = trim($value); //去除首尾空格
- // 如果当前行的第一个单元格内容为空,则跳过当前行
- if ($currentColumn == 0 && ($value == '' || $value === NULL)) {
- break;
- }
- if(substr($value,0,1) == '='){//判断单元格的公式不完整的情况
- return ["error" => 1, 'message' => '无法使用公式!']; //can not use the formula!
- }
- //判断单元格是否为数字类型
- if($cell->getDataType() == \PHPExcel_Cell_DataType::TYPE_NUMERIC){
- // $cell_style_format=$cell->getParent()->getStyle( $cell->getCoordinate() )->getNumberFormat();
- $cell_style_format = $cell->getStyle($cell->getCoordinate())->getNumberFormat(); //不需要getParent
- $format_code = $cell_style_format->getFormatCode();
- if (preg_match('/^([$[A-Z]*-[0-9A-F]*])*[hmsdy]/i', $format_code)) { //判断是否为日期类型
- $value = gmdate("Y-m-d", \PHPExcel_Shared_Date::ExcelToPHP($value)); //格式化日期
- }else{
- $value = \PHPExcel_Style_NumberFormat::toFormattedString($value, $format_code); //格式化数字
- }
- }
- //处理该单元格为合并单元格的情况
- $temp = '';
- if(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$afCol.$currentRow]) && !empty($value)){
- $temp = $value;
- }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$col.($currentRow-1)]) && empty($value)){
- $value = $arr[$currentRow-1][$currentColumn];
- }elseif(isset($isMergeCell[$col.$currentRow]) && isset($isMergeCell[$bfCol.$currentRow]) && empty($value)){
- $value = $temp;
- }
- $row[$currentColumn] = $value;
- }
- //判断一行中的全部单元格是否都有数据
- //如果不是,给出提示
- // if (count($row) != 0 && count($row) != $allColumn) {
- // return ["error" => 4, 'message' => "工作表【{$sheet_name}】的第【{$currentRow}】行的数据不完整,请填写完整,并重新导入");
- // }
- $arr[$currentRow] = $row; //完整一行的数据
- }
- $array[$i]["Content"] = array_filter($arr);//过滤空行
- $i++;
- }
- // var_dump($array[0]);die;
- unset($objWorksheet);
- unset($PHPReader);
- unset($PHPExcel);
- // unlink($file);
- return ["error" => 0, 'message' => '导入成功','data'=>$array[0]];;
- // 没有指定表名,则直接返回拼接结果
- if ($table === null) {
- return ["error" => 0, "data" => $array];
- }
- if ($fields === null) {
- return ["error" => 2, 'message' => '未指定字段名!'];
- }
- if ($type != 0 && $type != 1) {
- return ["error" => 3, 'message' => '导入模式不正确!'];
- }
- if ($main_field === null) {
- return ["error" => 4, 'message' => '未指定用于判断依据的字段名!'];
- }
- if (!is_string($main_field)) {
- return ["error" => 5, 'message' => '用于判断依据的字段名只能为字符串类型!'];
- }
- //查询已经存在的数据,用于判断导入模式做对比
- $exists_list = Db::name($table)->where($where)->group($main_field)->column($main_field);
- //整理数据
- $fields = array_flip($fields); //反转键值
- $data_list = [];
- $dataAdd['list'] = [];
- $dataCover['list'] = [];
- $dataSkip['list'] = [];
- foreach ($array as $key => $value) { //循环每一张工作表
- $firstRow = [];
- foreach ($value['Content'] as $row => $col) { //循环每一行数据
- $data = [];
- if ($row == 1) { //处理excel表的第一行,即表头,用来获取表头与字段名的关系
- foreach ($col as $index => $val) { //循环每一个单元格
- if (isset($fields[$val])) {
- $firstRow[$index] = $fields[$val];
- }
- }
- } else { //这里开始是真正需要的数据
- if (empty($firstRow)) {
- return ["error" => 8, 'message' => '没有表头数据,无法导入!'];
- }
- foreach ($col as $index => $val) { //循环每一个单元格
- if (isset($firstRow[$index])) {
- $data[$firstRow[$index]] = trim($val);
- }
- }
- // 判断导入模式
- if ($type == 0) {//增量导入
- if (in_array($data[$main_field], $exists_list)) {
- $dataSkip['list'][] = $data[$main_field]; //记录跳过的数据
- continue;//跳过已存在的考生
- } else {
- $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
- }
- } else {//覆盖导入
- if (in_array($data[$main_field], $exists_list)) {
- $dataCover['list'][] = $data[$main_field]; //记录覆盖的数据
- $map[$main_field] = $data[$main_field];
- Db::name($table)->where($where)->where($map)->delete();//删除已存在的数据
- } else {
- $dataAdd['list'][] = $data[$main_field]; //记录新增的数据
- }
- }
- $data_list[] = $data;
- }
- }
- }
- if ($data_list) {
- if (Db::name($table)->insertAll($data_list)) {
- //计算新增、覆盖、跳过的数量
- $dataAdd['total'] = count($dataAdd['list']);
- $dataCover['total'] = count($dataCover['list']);
- $dataSkip['total'] = count($dataSkip['list']);
- //将新增、覆盖、跳过的数据写入缓存
- cache('dataAdd', $dataAdd);
- cache('dataCover', $dataCover);
- cache('dataSkip', $dataSkip);
- cache('nextUrl', null);
- return ["error" => 0, 'message' => '成功导入 '. count($data_list). ' 条数据。'];
- } else {
- return ["error" => 9, 'message' => '导入失败!请重新导入。'];
- }
- } else {
- return ["error" => 10, 'message' => '上传的文件中,没有需要导入的数据!'];
- }
- }
- /**
- * 导出多工作表Excel
- * @param array $datas 数据,每个数组要包含'xls_name','xls_cell','data_list'
- * @param string $fileName 导出文件名
- * @author 蔡伟明 <314013107@qq.com>
- */
- public function exportMulti($datas = [], $fileName = '')
- {
- if (empty($datas)) {
- $this->error('找不到符合条件的数据');
- }
- $file_type = 'xls';
- if (strpos($fileName, '.')) {
- $file_name = explode('.', $fileName);
- if (strtolower(end($file_name)) != 'xls' && strtolower(end($file_name)) != 'xlsx') {
- $file_name = $fileName.'.xls';
- } else {
- $file_type = end($file_name);
- $file_name = $fileName;
- }
- } else {
- $file_name = $fileName.'.xls';
- }
- $file_type = strtolower($file_type) == 'xls' ? 'Excel5' : 'Excel2007';
- $objPHPExcel = new \PHPExcel();
- $fileName = $file_name == '' ? date('Y-m-d') : $file_name;
- $cellName = ['A','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','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ'];
- //工作表序号
- $sheetIndex = 0;
- foreach ($datas as $key => $data) {
- // $xlsTitle = iconv('utf-8', 'gb2312', $data['xls_name']);//文件名称
- $xlsTitle = $data['xls_name'];//文件名称
- $cellNum = count($data['xls_cell']);
- $dataNum = count($data['data_list']);
- //如果当前工作表序号大于0,则创建新表
- if ($sheetIndex > 0) {
- $objPHPExcel->createSheet();
- }
- //设置表头
- for($i = 0; $i < $cellNum; $i++){
- //设置当前活动表,并设置值
- $objPHPExcel->setActiveSheetIndex($sheetIndex)->setCellValue($cellName[$i].'1', $data['xls_cell'][$i][2]);
- //得到当前活动的表
- $objActSheet = $objPHPExcel->getActiveSheet();
- // 给当前活动的表设置名称
- $objActSheet->setTitle($xlsTitle);
- //根据内容设置单元格宽度
- $cellWidth = $data['xls_cell'][$i][1] == 'auto' ? strlen($data['xls_cell'][$i][2]) : $data['xls_cell'][$i][1];
- $objActSheet->getColumnDimension($cellName[$i])->setWidth($cellWidth); //内容自适应
- //设置指定数据类型
- if(isset($data['xls_cell'][$i][3]) && $data['xls_cell'][$i][3] == 'FORMAT_NUMBER'){
- $objActSheet->getStyle($cellName[$i])->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
- }
- }
- //设置具体内容
- for($i=0; $i < $dataNum; $i++){
- for($j=0; $j< $cellNum; $j++){
- $objActSheet->setCellValueExplicit($cellName[$j].($i+2), $data['data_list'][$i][$data['xls_cell'][$j][0]], \PHPExcel_Cell_DataType::TYPE_STRING);
- // $objActSheet->setCellValue($cellName[$j].($i+2), $data['data_list'][$i][$data['xls_cell'][$j][0]]);
- }
- }
- $sheetIndex++;
- }
- //将当前活动表设置为第一个
- $objPHPExcel->setActiveSheetIndex(0);
- 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='.$fileName);
- header("Content-Transfer-Encoding:binary");
- $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, $file_type);
- if (ob_get_length()) ob_end_clean();
- $objWriter->save('php://output');
- exit();
- }
- }
|