github地址
使用文档
使用示例
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx'); //实例化阅读器对象。
$spreadsheet = $reader->load($filename); //将文件读取到到$spreadsheet对象中
$sheet = $spreadsheet->getSheet(0);//sheet
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
$highestRow = $sheet->getHighestRow(); // 取得总行数
for ($col = 1; $col <= $highestColumnIndex; ++$col) //列数是以A列开始
{
$column_name = $sheet->getCellByColumnAndRow($col, 1)->getFormattedValue();
if(in_array($column_name,array_keys($format_column)))$column[] = ['clo' => $col,'key' => $format_column[$column_name]];
}
// 2 根据以上确定的列直接循环取值
for ($row = 2; $row <= $highestRow; ++$row) //行号从1开始
{
$row_data = [];
foreach ($column as $item){
switch ($item['key']) {
case "tax":
$value = $sheet->getCellByColumnAndRow($item['clo'], $row)->getFormattedValue();
if(strpos($value, "%") > 0) $value = (float)$value/100;
$row_data[$item['key']] = $value;
break;
case "num" :
$row_data[$item['key']] = (int)$sheet->getCellByColumnAndRow($item['clo'], $row)->getCalculatedValue(true);
break;
case "money":
$res = $sheet->getCellByColumnAndRow($item['clo'], $row)->getFormattedValue();
$row_data[$item['key']] = str_replace(['¥', ','], '', $res);//一般先取出结果,然后自己处理比较方便。
break;
case "time":
$date = $sheet->getCellByColumnAndRow($item['clo'], $row)->getValue();
if(!$date){
$row_data[$item['key']] = null;
}else{
$row_data[$item['key']] = gmdate('Y-m-d', ($date - 25569) * 24 * 3600); //gmdate返回UTC的时间
}
break;
default :
$row_data[$item['key']]= Common::trim($sheet->getCellByColumnAndRow($item['clo'], $row)->getFormattedValue());
}
}
if(count($row_data) != count($format_column)) throw new ApiException(Code::EXCEL_FORMAT_ERROR);
//空数据过滤
$res_data[] = $row_data;
}
$this->filterData($res_data);
return array_values($res_data);
//过滤空数据
private function filterData(&$row_data)
{
foreach ($row_data as $key => $item){
$is_null = false;
foreach ($item as $iv) {
if(!empty($iv)) {
$is_null = true; break;
}
}
if($is_null == false ) unset($row_data[$key]);
}
}
网友评论