You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
excel_handle/extend/excel/Excel.php

186 lines
6.9 KiB

<?php
namespace excel;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Exception;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use file\FileSystem;
class Excel
{
private static $ret = array('code' => 1, 'msg' => '操作成功');
protected static $is_active_sheet = true;
/**
* @param $file
* @return Spreadsheet
* @throws Exception
*/
public static function loadFile($file)
{
$reader = new Xlsx();
//判断文件类型
if (!$reader->canRead($file)) {
$spreadsheet = IOFactory::load($file);
return $spreadsheet;
}
$reader->setReadDataOnly(TRUE);
return $reader->load($file);
}
/**
* @param $filename
* @return bool|int
* @throws \PhpOffice\PhpSpreadsheet\Exception
*/
public static function excelToJson($filename, $export)
{
$spreadsheet = self::loadFile($filename);
$flag_row = '1';
$top_row = '1';
$body_start_row = '3';
$sheet = $spreadsheet->getSheet(0); // 读取第一个工作表(编号从 0 开始)
$total_row = $sheet->getHighestRow(); // 取得总行数
$max_column = $sheet->getHighestColumn(); // 取得总列数
print("max_column: $max_column \n");
$arr = array(1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z');
$total_column = array_search($max_column, $arr);
print("total_column: $total_column \n");
//标记行
$flag = array();
for ($column = 1; $column <= $total_column; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $flag_row)->getValue();
$flag[$column] = trim($val);
}
print('flag:'.json_encode(array_values($flag))." \n");
//键名行
$top = array();
for ($column = 1; $column <= $total_column; $column++) {
if (!$flag[$column]) {
continue;
}
$val = $sheet->getCellByColumnAndRow($column, $top_row)->getValue();
$top[$column] = trim($val);
}
print('top:'.json_encode(array_values($top))." \n");
//内容行
$body = array();
for ($row = $body_start_row; $row <= $total_row; $row++) {
$temp = array();
//获取数据
for ($column = 1; $column <= $total_column; $column++) {
if (!$flag[$column]) {
continue;
}
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
$key = $top[$column];
$temp[$key] = trim($val);
}
$body[] = $temp;
}
return FileSystem::saveFile($export, $body);
}
/**
* @param $import
* @param $export
* @return array
* @throws \PhpOffice\PhpSpreadsheet\Exception
*/
public static function batchExcelToJson($import, $export)
{
$dirs = FileSystem::scanFile($import);
foreach ($dirs as $name => $dir) {
if (is_array($dir)) {
foreach ($dir as $file) {
print("filename: $file \n");
$result[$name][] = self::excelToJson($import . $name . '/' . $file, $export . $name . '/' . $file);
}
} else {
print("filename: $dir \n");
$result[] = self::excelToJson($import . $dir, $export . $dir);
}
}
return $result;
}
/**
* 切割文件
* @param $file
* @param $num
* @return array
* @throws Exception
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public static function divisionFile($file, $num)
{
$tempDir = FileSystem::getRuntimePath() . 'tmp/';
list($fileName,) = explode('.', basename($file));
$spreadsheetReader = self::loadFile($file);
$sheet = $spreadsheetReader->getSheet(0);
$highestRow = $sheet->getHighestRow(); // e.g. 10
$highestColumn = $sheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = Coordinate::columnIndexFromString($highestColumn); // e.g. 5
$title = array_pop($sheet->rangeToArray(
"A1:{$highestColumn}1", // 要检索的工作表区域
NULL, // 空单元格返回的值
TRUE, // 计算值
TRUE, // 格式化值
TRUE // 数组应按单元格行和单元格列编制索引
));
$row_array = [];
$files = [];
$arg_num = ceil($highestRow / $num);
for ($row = 2; $row <= $highestRow; $row++) {
$row_array[] = array_pop($sheet->rangeToArray(
"A{$row}:{$highestColumn}{$row}", // 要检索的工作表区域
NULL, // 空单元格返回的值
TRUE, // 计算值
TRUE, // 格式化值
TRUE // 数组应按单元格行和单元格列编制索引
));
if($row == $arg_num) {
$saveFile = $tempDir . $fileName . '_'. $row .'.csv';
self::createFile($saveFile, array_merge($title,$row_array), 'csv');
$files[] = $saveFile;
$row_array = [];
}
}
return $files;
}
/**
* 创建文件
* @param $save_file
* @param $data
* @param $ext
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
*/
public static function createFile($save_file, $data, $ext)
{
$data = array_values($data);
if(!file_exists($save_file)){
FileSystem::saveFile($save_file);
}
$spreadsheetWriter = new Spreadsheet();
$letter = array(1 => 'A', 2 => 'B', 3 => 'C', 4 => 'D', 5 => 'E', 6 => 'F', 7 => 'G', 8 => 'H', 9 => 'I', 10 => 'J', 11 => 'K', 12 => 'L', 13 => 'M', 14 => 'N', 15 => 'O', 16 => 'P', 17 => 'Q', 18 => 'R', 19 => 'S', 20 => 'T', 21 => 'U', 22 => 'V', 23 => 'W', 24 => 'X', 25 => 'Y', 26 => 'Z');
$row_num = count($data);
for ($row = 1; $row <= $row_num; $row++) {
$data[$row] = array_values($data[$row]);
$col_num = count($data[$row]);
for ($col = 1; $col <= $col_num; $col++) {
$spreadsheetWriter->getActiveSheet()->setCellValue($letter[$col] . $row, $data[$row][$col]);
}
}
$writer = IOFactory::createWriter($spreadsheetWriter, ucfirst($ext));
$writer->save($save_file);
$spreadsheetWriter->disconnectWorksheets();
unset($spreadsheetWriter);
}
}