search

viest / php-ext-xlswriter

🚀 PHP Extension for creating and reader XLSX files.
https://xlswriter.viest.me
BSD 2-Clause "Simplified" License
2.25k stars 232 forks source link

忽略空白行无效, Excel::SKIP_EMPTY_ROW #408

Closed wangchengtao closed 2 years ago

wangchengtao commented 2 years ago

环境

文件

交易订单模板-收付贝(1).xlsx

截图

excel 截图

代码

$data = $excel->openFile($file->getFilename())
                      ->openSheet('Sheet1',  Excel::SKIP_EMPTY_ROW)
                      ->setType(array_fill(0, 50, Excel::TYPE_STRING))
                      ->getSheetData();

结果

array:10 [
  0 => array:8 [
    0 => "2110101538088478"
    1 => "0324016699"
    2 => "商户_刘财"
    3 => "2021-10-10 15:38:00.0"
    4 => ""
    5 => "630"
    6 => "3.78"
    7 => "乐刷"
  ]
  1 => array:8 [
    0 => "2110101551381782"
    1 => "4360011099"
    2 => "商户_江先会"
    3 => "2021-10-10 15:51:30.0"
    4 => ""
    5 => "4630"
    6 => "27.78"
    7 => "乐刷"
  ]
  2 => array:8 [
    0 => "2110101555385915"
    1 => "5263013799"
    2 => "商户_覃婷"
    3 => "2021-10-10 15:55:30.0"
    4 => ""
    5 => "10010"
    6 => "60.06"
    7 => "乐刷"
  ]
  3 => array:8 [
    0 => "2110101559089550"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 15:59:00.0"
    4 => ""
    5 => "5041"
    6 => "30.246"
    7 => "乐刷"
  ]
  4 => array:8 [
    0 => "2110101559580400"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 15:59:52.0"
    4 => "6251254615521625"
    5 => "4033"
    6 => "24.198"
    7 => "乐刷"
  ]
  5 => array:8 [
    0 => "2110101601582527"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 16:01:58.0"
    4 => "6251254615521625"
    5 => "2843"
    6 => "17.058"
    7 => "乐刷"
  ]
  6 => array:8 [
    0 => "2110101603284007"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 16:03:25.0"
    4 => "6251254615521625"
    5 => "2758"
    6 => "16.548"
    7 => "乐刷"
  ]
  7 => array:8 [
    0 => "2110101612483733"
    1 => "4263013999"
    2 => "商户_陈中华"
    3 => "2021-10-10 16:12:45.0"
    4 => "6251254615521625"
    5 => "199"
    6 => "1.194"
    7 => "乐刷"
  ]
  8 => array:8 [
    0 => ""
    1 => ""
    2 => ""
    3 => ""
    4 => ""
    5 => ""
    6 => ""
    7 => ""
  ]
  9 => array:8 [
    0 => "2110101612583975"
    1 => "7062015299"
    2 => "商户_杨淑芸"
    3 => "2021-10-10 16:12:59.0"
    4 => "6251254615521625"
    5 => "5485"
    6 => "32.91"
    7 => "乐刷"
  ]
]

各种尝试

加上 SKIP_EMPTY_VALUE
$data = $excel->openFile($file->getFilename())
                      ->openSheet('Sheet1', Excel::SKIP_EMPTY_VALUE | Excel::SKIP_EMPTY_ROW)
                      ->setSkipRows(0)
                      ->setType(array_fill(0, 50, Excel::TYPE_STRING))
                      ->getSheetData();
结果
array:9 [
  0 => array:7 [
    0 => "2110101538088478"
    1 => "0324016699"
    2 => "商户_刘财"
    3 => "2021-10-10 15:38:00.0"
    5 => "630"
    6 => "3.78"
    7 => "乐刷"
  ]
  1 => array:7 [
    0 => "2110101551381782"
    1 => "4360011099"
    2 => "商户_江先会"
    3 => "2021-10-10 15:51:30.0"
    5 => "4630"
    6 => "27.78"
    7 => "乐刷"
  ]
  2 => array:7 [
    0 => "2110101555385915"
    1 => "5263013799"
    2 => "商户_覃婷"
    3 => "2021-10-10 15:55:30.0"
    5 => "10010"
    6 => "60.06"
    7 => "乐刷"
  ]
  3 => array:7 [
    0 => "2110101559089550"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 15:59:00.0"
    5 => "5041"
    6 => "30.246"
    7 => "乐刷"
  ]
  4 => array:8 [
    0 => "2110101559580400"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 15:59:52.0"
    4 => "6251254615521625"
    5 => "4033"
    6 => "24.198"
    7 => "乐刷"
  ]
  5 => array:8 [
    0 => "2110101601582527"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 16:01:58.0"
    4 => "6251254615521625"
    5 => "2843"
    6 => "17.058"
    7 => "乐刷"
  ]
  6 => array:8 [
    0 => "2110101603284007"
    1 => "2661014599"
    2 => "商户_李春燕"
    3 => "2021-10-10 16:03:25.0"
    4 => "6251254615521625"
    5 => "2758"
    6 => "16.548"
    7 => "乐刷"
  ]
  7 => array:8 [
    0 => "2110101612483733"
    1 => "4263013999"
    2 => "商户_陈中华"
    3 => "2021-10-10 16:12:45.0"
    4 => "6251254615521625"
    5 => "199"
    6 => "1.194"
    7 => "乐刷"
  ]
  8 => array:8 [
    0 => "2110101612583975"
    1 => "7062015299"
    2 => "商户_杨淑芸"
    3 => "2021-10-10 16:12:59.0"
    4 => "6251254615521625"
    5 => "5485"
    6 => "32.91"
    7 => "乐刷"
  ]
]

结论

  1. 当只设置 Excel::SKIP_EMPTY_ROW 时, 空行没有被忽略, 不起作用.
  2. 当同时设置 Excel::SKIP_EMPTY_VALUE | Excel::SKIP_EMPTY_ROW 两个选项时, 空行和空单元格都被忽略.
viest commented 2 years ago

SKIP_EMPTY_ROW 是忽略未声明的单元格，但是很多情况下单元格是处于声明状态，只不过里面没有填充数据而已，所以需要借助 SKIP_EMPTY_VALUE。

wangchengtao commented 2 years ago

@viest 加上SKIP_EMPTY_VALUE 之后，空单元格会被忽略，导致本可以为空的数据被删掉。例如上文一行本应该有 8 列，但是现在只有 7列， 原始数据： empty 加上 SKIP_EMPTY_VALUE 之后，获取到的数据：

array:1 [
  0 => array:7 [
    0 => "2110101538088478"
    1 => "0324016699"
    2 => "商户_刘财"
    3 => "2021-10-10 15:38:00.0"
    5 => "630"
    6 => "3.78"
    7 => "乐刷"
  ]
]

期望数据:

array:1 [
  0 => array:8 [
    0 => "2110101538088478"
    1 => "0324016699"
    2 => "商户_刘财"
    3 => "2021-10-10 15:38:00.0"
    4 => ""
    5 => "630"
    6 => "3.78"
    7 => "乐刷"
  ]
]
viest commented 2 years ago

@wangchengtao 你注意看，数组下标并没有因为忽略而改变，和你单元格还是可以一一对应的。

wangchengtao commented 2 years ago

@viest 如果这样的话，在提取数据的时候只能根据下标比对，不能使用 array_combine 函数，不太方便呢

viest commented 2 years ago

这个方案也是之前设计的时候能想到的方案中比较好的一个了，你有比较好的想法吗？

我在设计的时候利用了坐标这个概念，可以通过坐标定位单元格，既然单元格可以很轻松定位了，那么与之对应的关系也就很容易关联上了。

你可以使用下标来关联表头，这是一个比较精确的方法。

wangchengtao commented 2 years ago

@viest 目前没有想法 🤣 ，只能按这样子做了，谢谢你的回答。