PhpSpreadsheet处理Excel 导入 导出并设置样式

PhpSpreadsheet处理Excel

PHPOffice

是一个用来处理办公相关软件的php类库

PhpSpreadsheet

专门处理excle的类库

导出如下：

引入类库

composer require maatwebsite/excel

（"maatwebsite/excel": "^3.1"）

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

public static function excelWriter($fileName, $style = 1, $columnName = null, $data = null, $sheetIndex = 0, $cell = null)

{

$spreadsheet = new Spreadsheet();

//默认为26列，如需要再自定义加

if (!isset($cell)) {

$cell = array('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');

}

$sheet = $spreadsheet->getActiveSheet();

//设置excel的sheet，默认0

$spreadsheet->setActiveSheetIndex($sheetIndex);

switch ($style) {

case 1:

//常规格式

self::setExcelStyleOne($sheet, $columnName, $data, $cell);

break;

case 2:

//特别格式

self::setExcelStyleTwo($sheet, $cell);

break;

}

//下载输出excel页面

// // Redirect output to a client’s web browser (Excel5)

// header('Content-Type: application/vnd.ms-excel');

// header('Content-Disposition: attachment;filename="' . $fileName . '"');

// header('Cache-Control: max-age=0');

// // If you're serving to IE 9, then the following may be needed

// header('Cache-Control: max-age=1');

//

// // If you're serving to IE over SSL, then the following may be needed

// header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past

// header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified

// header('Cache-Control: cache, must-revalidate'); // HTTP/1.1

// header('Pragma: public'); // HTTP/1.0

// 保存到本地服务器文件

$filename = date('YmdHis') . '-' . $fileName . '-' . uniqid() . '.xlsx';

$path = public_path() . '/download/excel/' . date('Ym') . '/';

$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');

// File::makeDirectory($path);

File::makeDirectory($path, $mode = 0777, true, true);

$objWriter->save($path . $filename);

$res = array(

'path' => $path,

'filename' => $filename

);

return $res;

//$objWriter->save('php://output');

}

protected static function setExcelStyleOne($sheet, $columnName, $data, $cell)

{

//列表标题

//$columnName = array('标题1','标题2');

$count = count($columnName);

//列表标题=>输入到excel第一行中

foreach ($columnName as $k => $v) {

$sheet->setCellValue($cell[$k] . '1', $v);

}

//列表标题对应的数据=>输入到excel第二行以下

foreach ($data as $key => $value) {

$i = $key + 1;

for ($j = 0; $j < $count; $j++) {

$sheet->setCellValue($cell[$j] . ($i + 1), $value[$j]);

}

}

}

protected static function setExcelStyleTwo($sheet, $cell)

{

//样式设置 - 水平、垂直居中

$styleArray = [

'alignment' => [

'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER

],

];

$sheet->getStyle('A1:H8')->applyFromArray($styleArray);

//样式设置 - 字体

$sheet->getStyle('A10:A24')->getFont()->setBold(true)->setName('Arial')->setSize(10);

$sheet->getStyle('A18:A24')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //设置单元格A1的字体颜色

//样式设置 - 边框

$styleArray = [

'borders' => [

'outline' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

'color' => ['argb' => '000000'],

],

'inside' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

]

],

];

$sheet->getStyle('A1:F2')->applyFromArray($styleArray);

//设置宽度

foreach ($cell as $k => $v) {

$sheet->getColumnDimension($v)->setWidth(13);

}

$sheet->getColumnDimension('A')->setWidth(20);

//样式设置 - 合并和拆分

$sheet->mergeCells('A1:A2'); //合并单元格

//$sheet -> unmergeCells('C3:G3'); //拆分单元格

$sheet->setCellValue('A1', '人员概况');

$column_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M');

$sheet->setCellValue('A10', '人员对比(正式工+毕业生)');

foreach ($column_arr as $key => $val) {

$mon = $key + 1;

$sheet->setCellValue($column_arr[$key] . '10', $mon . '月');

}

$sheet->setCellValue('N10', '合计');

$sheet->setCellValue('O10', '平均');

$data_arr = array('B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O');

$data_row_arr = array('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24');

$sheet->setCellValue('A11', '人数(不含外包派遣)');

foreach ($data_row_arr as $kkk => $vvv) {

foreach ($data_arr as $kk => $vv) {

$sheet->setCellValue($data_arr[$kk] . $vvv, 0);

}

}

}

导入如下：

use PhpOffice\PhpSpreadsheet\IOFactory;

public function upload(Request $request)

{

$file = $request->file('file');

t_log($file);

if ($file->isValid()) {

// 获取文件相关信息

$originalName = $file->getClientOriginalName(); // 文件原名

$ext = $file->getClientOriginalExtension(); // 扩展名

$realPath = $file->getRealPath(); //临时文件的绝对路径

$type = $file->getClientMimeType(); // image/jpeg

if (!in_array($ext, ['xls', 'xlsx', 'csv'])) {

return error('文件类型错误！');

}

// 上传文件

$filename = date('YmdHis') . '-' . uniqid() . '.' . $ext;

// 使用我们新建的uploads本地存储空间（目录）

//这里的uploads是配置文件的名称

//把临时文件移动到指定的位置，并重命名

$path = public_path() . '/uploads/excel/' . date('Ym') . '/';

$bool = $file->move($path, $filename);

if ($bool) {

$img_path = public_path() . '/uploads/excel/' . date('Ym') . '/' . $filename;

$res = $this->_readExcel($img_path);

if ($res === true) {

return success('上传存储成功');

} else {

return success($res);

}

} else {

return success('上传文件失败');

}

}

return success('没有获取到文件');

}

private function _readExcel($filePath = '')

{

$data = array();

$end_row = null;

$objPHPExcelReader = IOFactory::load($filePath);

$sheet = $objPHPExcelReader->getSheet(0);

$highestRow = $sheet->getHighestRow(); // 取得最大行数

$highestColumn = $sheet->getHighestColumn(); // 取得最大列数

$arr = ['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', 'ZZ']; // 目标列标号

$start_row = 2;//从第二行开始

$row_arr = [];

for ($column = 0; $arr[$column] != 'ZZ'; $column++) {

$vale = $sheet->getCellByColumnAndRow($column, 1)->getValue();

if (is_null($vale)) {

continue;

}

$temp_keys[] = $vale;

}

$value_key = WorkersService::getReportField(WorkersService::MAIN, true);

//当前操作人的组织id

$org_id = $this->admin->org_id;

for ($row = $start_row; $row <= $highestRow; $row++) { // 一次读取一列

$row_arr_one = $row_arr_other = array();

for ($column = 0; $arr[$column] != 'ZZ'; $column++) {

$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();

if (is_null($val)) {

continue;

}

if (isset($value_key[$temp_keys[$column]])) {

$keys = $value_key[$temp_keys[$column]];

//$row_arr_one判断两个唯一字段是user_id，user_name否表中已存在的数据

if($keys=='user_id'){

$row_arr_one[$keys] = $val;

}

if($keys=='user_name'){

$row_arr_one[$keys] = $val;

}

$row_arr_one['org_id'] = $org_id;

if(in_array($keys,['entry_date','trial_end','first_time','quit_time'])){

$row_arr_other[$keys] =$this->changeTime($val);

}else{

//有则更新的数组

$row_arr_other[$keys] = $val;

}

unset($row_arr_other['user_id']);

unset($row_arr_other['user_name']);

}

}

DB::connection('kg_report')->table('workers')->updateOrInsert($row_arr_one, $row_arr_other);

}

return true;

}