|
|
<?php
|
|
|
|
|
|
namespace excel;
|
|
|
|
|
|
use PhpOffice\PhpSpreadsheet\IOFactory;
|
|
|
use PhpOffice\PhpSpreadsheet\Spreadsheet;
|
|
|
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
|
|
|
|
|
|
|
|
|
class Excel
|
|
|
{
|
|
|
/**
|
|
|
* 表格导出
|
|
|
*/
|
|
|
public function excelExport($fileName, $headArr, $data, $msg)
|
|
|
{
|
|
|
//对数据进行检验
|
|
|
if (empty($data) || !is_array($data)) {
|
|
|
die("data must be a array");
|
|
|
}
|
|
|
|
|
|
//检查文件名
|
|
|
if (empty($fileName)) {
|
|
|
die("filename must be existed");
|
|
|
}
|
|
|
|
|
|
//获取总列数
|
|
|
$totalColumn = count($headArr);
|
|
|
if ($totalColumn > 26) {
|
|
|
$charColumn = chr(intval($totalColumn / 26) + 64) . chr(intval($totalColumn % 26) + 64); //超过26个字母时才会启用
|
|
|
} else {
|
|
|
$charColumn = chr($totalColumn + 64);
|
|
|
}
|
|
|
$date = date("Y-m-d", time());
|
|
|
$fileName .= "_{$date}.xls";
|
|
|
|
|
|
$spreadsheet = new Spreadsheet();
|
|
|
$phpstyle = new \PhpOffice\PhpSpreadsheet\Style\Color();
|
|
|
$sheet = $spreadsheet->getActiveSheet();
|
|
|
//表头变颜色
|
|
|
$sheet->getStyle('A1:' . $charColumn . '1')->getFont()->getColor()->setARGB($phpstyle::COLOR_BLUE); //设置颜色
|
|
|
//设置批注
|
|
|
if (!empty($msg)) {
|
|
|
$sheet->getStyle('A2')->getFont()->getColor()->setARGB($phpstyle::COLOR_RED);
|
|
|
$sheet->setCellValue('A2', $msg); //给单个单元格设置内容
|
|
|
$sheet->mergeCells('A2:' . $charColumn . '2'); //合并单元格
|
|
|
}
|
|
|
|
|
|
//设置表头
|
|
|
$key = ord("A");
|
|
|
$key2 = ord("@"); //@--64
|
|
|
// $key = foo('a');
|
|
|
foreach ($headArr as $v) {
|
|
|
if ($key > ord("Z")) {
|
|
|
$key2 += 1;
|
|
|
$key = ord("A");
|
|
|
$colum = chr($key2) . chr($key); //超过26个字母时才会启用
|
|
|
} else {
|
|
|
if ($key2 >= ord("A")) {
|
|
|
$colum = chr($key2) . chr($key); //超过26个字母时才会启用
|
|
|
} else {
|
|
|
$colum = chr($key);
|
|
|
}
|
|
|
}
|
|
|
// $colum = chr($key);
|
|
|
$sheet->setCellValue($colum . '1', $v);
|
|
|
$sheet->getColumnDimension($colum)->setWidth(20);
|
|
|
$key++;
|
|
|
}
|
|
|
|
|
|
//写入数据
|
|
|
if (!empty($msg)) {
|
|
|
$column = 3;
|
|
|
} else {
|
|
|
$column = 2;
|
|
|
}
|
|
|
|
|
|
$image_array = [];
|
|
|
foreach ($data as $key => $rows) { //行写入
|
|
|
$span = ord("A");
|
|
|
$span2 = ord("@"); //@--64
|
|
|
foreach ($rows as $keyName => $value) { // 列写入
|
|
|
$judge = stristr($keyName, 'img');
|
|
|
$judge_url = stristr($keyName, 'url');
|
|
|
|
|
|
if ($span > ord("Z")) {
|
|
|
$span2 += 1;
|
|
|
$span = ord("A");
|
|
|
$j = chr($span2) . chr($span); //超过26个字母时才会启用
|
|
|
} else {
|
|
|
if ($span2 >= ord("A")) {
|
|
|
$j = chr($span2) . chr($span); //超过26个字母时才会启用
|
|
|
} else {
|
|
|
$j = chr($span);
|
|
|
}
|
|
|
}
|
|
|
if ($judge != false) {
|
|
|
$sheet->getRowDimension($column)->setRowHeight(60); //设置行高
|
|
|
|
|
|
if (is_array($value)) {
|
|
|
$count = count($value);
|
|
|
foreach ($value as $k => $v) {
|
|
|
if (is_file($v)) {
|
|
|
$m = $k + 1;
|
|
|
$image_array[] = $v;
|
|
|
$objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
|
|
|
//图片路径,项目目录下就行
|
|
|
$objDrawing->setPath($v);
|
|
|
$objDrawing->setCoordinates($j . $column);
|
|
|
$objDrawing->setWidth(50);
|
|
|
$objDrawing->setHeight(50);
|
|
|
//图片偏移距离
|
|
|
$objDrawing->setOffsetX($m * 10 + $k * 50);
|
|
|
$objDrawing->setOffsetY(10);
|
|
|
$objDrawing->setWorksheet($spreadsheet->getActiveSheet());
|
|
|
}
|
|
|
//设置列宽
|
|
|
}
|
|
|
$spreadsheet->getActiveSheet(0)->getColumnDimension($j)->setWidth(60);
|
|
|
} else {
|
|
|
if (is_file($value)) {
|
|
|
$objDrawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
|
|
|
$objDrawing->setPath($value);
|
|
|
$objDrawing->setWidth(50);
|
|
|
$objDrawing->setHeight(50);
|
|
|
$objDrawing->setOffsetX(10);
|
|
|
$objDrawing->setOffsetY(10);
|
|
|
$objDrawing->setCoordinates($j . $column);
|
|
|
$objDrawing->setWorksheet($spreadsheet->getActiveSheet());
|
|
|
} else {
|
|
|
$sheet->setCellValue($j . $column, $value);
|
|
|
}
|
|
|
}
|
|
|
} elseif ($judge_url != false) {
|
|
|
$sheet->setCellValue($j . $column, $value);
|
|
|
$sheet->getCell($j . $column)->getHyperlink()->setUrl($value);
|
|
|
} else {
|
|
|
$sheet->setCellValue($j . $column, $value);
|
|
|
}
|
|
|
$span++;
|
|
|
}
|
|
|
$column++;
|
|
|
}
|
|
|
|
|
|
//处理中文输出问题
|
|
|
$fileName = iconv("utf-8", "gb2312", $fileName);
|
|
|
|
|
|
//接下来当然是下载这个表格了，在浏览器输出就好了
|
|
|
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');
|
|
|
foreach ($image_array as $value) {
|
|
|
unlink($value);
|
|
|
}
|
|
|
exit;
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取表格数据
|
|
|
*/
|
|
|
public function getData($file_dir)
|
|
|
{
|
|
|
$array = explode('aliyuncs.com/', $file_dir);
|
|
|
$object = $array[1];
|
|
|
$array = explode('.', $file_dir);
|
|
|
$postfix = end($array);
|
|
|
$local_file = './uploads/uid' . 2 . '/download/' . md5(microtime(true)) . '.' . $postfix;
|
|
|
create_directory($local_file);
|
|
|
$oss_param = get_ali_oss_config(2);
|
|
|
$oss_logic = new \ali\oss\Oss($oss_param);
|
|
|
$file_dir = $oss_logic->getObject($object, $local_file);
|
|
|
|
|
|
if($postfix == 'xls'){
|
|
|
$PHPReader = IOFactory::createReader('Xls');
|
|
|
}else{
|
|
|
$PHPReader = IOFactory::createReader('Xlsx');
|
|
|
}
|
|
|
if (!$PHPReader->canRead($local_file)) {
|
|
|
return array(0, '请上传文档');
|
|
|
}
|
|
|
|
|
|
//载入文件
|
|
|
$PHPExcel = $PHPReader->load($local_file);
|
|
|
|
|
|
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
|
|
|
$currentSheet = $PHPExcel->getSheet(0);
|
|
|
|
|
|
//获取总行数
|
|
|
$allRow = $currentSheet->getHighestRow();
|
|
|
$allColumn = $currentSheet->getHighestColumn();
|
|
|
$allColumn = ord($allColumn);
|
|
|
|
|
|
for ($j = 2; $j <= $allRow; $j++) {
|
|
|
for ($i = 65; $i <= $allColumn; $i++) {
|
|
|
$colum = chr($i);
|
|
|
$cell = $currentSheet->getCell($colum . $j)->getValue();
|
|
|
if ($cell instanceof RichText) { //富文本转换字符串
|
|
|
$cell = $cell->__toString();
|
|
|
}
|
|
|
|
|
|
$data[$j][$colum] = $cell;
|
|
|
}
|
|
|
}
|
|
|
unlink($local_file);
|
|
|
return array(1, $data);
|
|
|
}
|
|
|
|
|
|
/**
|
|
|
* 获取表格数据
|
|
|
*/
|
|
|
public function getDataFromLocalFile($file_path)
|
|
|
{
|
|
|
$array = explode('.', $file_path);
|
|
|
$postfix = end($array);
|
|
|
$local_file = $file_path;
|
|
|
|
|
|
if($postfix == 'xls'){
|
|
|
$PHPReader = IOFactory::createReader('Xls');
|
|
|
}else{
|
|
|
$PHPReader = IOFactory::createReader('Xlsx');
|
|
|
}
|
|
|
if (!$PHPReader->canRead($local_file)) {
|
|
|
return array(0, '请上传文档');
|
|
|
}
|
|
|
|
|
|
//载入文件
|
|
|
$PHPExcel = $PHPReader->load($local_file);
|
|
|
|
|
|
//获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
|
|
|
$currentSheet = $PHPExcel->getSheet(0);
|
|
|
|
|
|
//获取总行数
|
|
|
$allRow = $currentSheet->getHighestRow();
|
|
|
$allColumn = $currentSheet->getHighestColumn();
|
|
|
$allColumn = ord($allColumn);
|
|
|
|
|
|
for ($j = 2; $j <= $allRow; $j++) {
|
|
|
for ($i = 65; $i <= $allColumn; $i++) {
|
|
|
$colum = chr($i);
|
|
|
$cell = $currentSheet->getCell($colum . $j)->getValue();
|
|
|
if ($cell instanceof RichText) { //富文本转换字符串
|
|
|
$cell = $cell->__toString();
|
|
|
}
|
|
|
|
|
|
$data[$j][$colum] = $cell;
|
|
|
}
|
|
|
}
|
|
|
// unlink($local_file);
|
|
|
return $data;
|
|
|
}
|
|
|
}
|
