我有多个这种格式的 excel 文件。
它们是上班和下班的日期和时间。
--------------------------------- | 姓名 | 时间 | --------------------------------- | 人 A | 2017 年 7 月 3 日上午 8:15 | | 人 A | 2017 年 7 月 3 日上午 10:32 | | 人 A | 2017 年 7 月 3 日下午 1:56 | | 人 A | 2017 年 7 月 3 日下午 6:15 | | 人 A | 2017 年 7 月 4 日上午 8:29 | | 人 A | 2017 年 7 月 4 日上午 8:58 | | 人 A | 2017 年 7 月 4 日上午 9:43 | | 人 A | 2017 年 7 月 4 日下午 1:03 | | 人 A | 2017 年 7 月 4 日下午 2:17 | | 人 A | 2017 年 7 月 4 日下午 5:58 | 。 。 。 | 人 A | 2017 年 7 月 31 日上午 7:45 | | 人 A | 2017 年 7 月 31 日上午 8:10 | | 人 A | 2017 年 7 月 31 日下午 3:26 | | 人 A | 2017 年 7 月 31 日下午 7:29 | ---------------------------------
我想要提取这些数据并将其保存为以下格式的新 Excel 文件:
--------------------------------------------- | 姓名 | 日期 | 入场时间 | 出场时间 | --------------------------------------------- | 人 A | 2017 年 7 月 3 日 | 上午 8:15 | 下午 6:15 | | 人 A | 2017 年 7 月 4 日 | 上午 8:29 | 下午 5:58 | 。 。 。 | 人 A | 2017 年 7 月 31 日 | 上午 7:45 | 下午 7:29 | ---------------------------------------------
基本上,它是按日期排列数据一个条目,以该日期的最早时间为 ,以Time In
该日期的最晚时间为Time Out
。
这种格式的 Excel 文件有很多,如果手动操作的话会花费太长时间。
如果您想将它们转换为.csv
第一个编辑,然后将它们转换回.xlsx
,这很酷。
附言:悬赏 200 点声望。
答案1
答案2
我将文件转换为,csv
并使用 PHP 脚本解析内容,将其创建为我真正想要的样子。然后将结果保存在一个新文件中,再将这些文件转换回来,xls
然后合并到一个笔记本中。
转换和合并部分是手动完成的。这不是最好的解决方案,但目前还有效。
脚本如下:
// 从数据目录获取文件列表 $文件 = array_diff(scandir('./data'), array('.', '..')); foreach($files 作为 $file): // 从 csv 文件获取所有数据并保存在 $data 数组中 $csvFile = 文件('数据/'.$file); $数据 = $列表 = []; foreach($csvFile 作为 $line){ $数据[] = str_getcsv($line); } 取消设置($data[0]); // 解析数据数组并获取不同的部分:名称、日期和时间 foreach($数据为$v){ $date = strtotime($v[1]); $list[date('dmY',$date)][] = 数组( '名称'=>$v[0], '日期'=>日期('d/m/Y',$日期), ‘in’=>$日期 (英文): } // 创建一个新数组,并使用标题列保存解析后的数据 $new = array(array('名称','日期','开始时间','结束时间')); foreach($list 作为 $k => $v) { $out = max(数组列($v,'in')); $名称 = $v[0]['名称']; $new[] = 数组( '名称'=>ucwords(strtolower($name)), '日期'=>$v[0]['日期'], 'in'=>date('h:i A', $v[0]['in']), 'out'=>date('h:i A', $out) (英文): } // 使用此文件名在新目录中新建一个文件的名称 $filename = str_replace('.csv', '', basename($file)); $fn = strtolower($文件名.'-log.csv'); // 打开文件并将新数组输出为 CSV $out = fopen('new/'.$fn,'w'); foreach($new 作为 $l) { fputcsv($out,$l,“,”,“”'); } fclose ($输出); 结束每个;
答案3
感谢 @rinzwind,我成功创建了以下 bash 脚本,该脚本可解压 xlsx,使用 sed 将某些字符串替换为其他字符串,然后重新压缩。该脚本将自动计算设置为开启,这样如果您有任何公式,它们也会更新(默认情况下不会更新)。
#!/bin/bash
if ( [ -z $1 ] || [ -z $2 ] ); then
echo "Usage: create-xlsx.sh <PLACEHOLDER_A> <PLACEHOLDER_B>"
exit 1
fi
DIR=/tmp/mydir
rm -rf $DIR
mkdir -p $DIR
cd $DIR
#Note! xlsx is a zip-file
#Note! Original xlsx needs to have autocalculate set
#See https://stackoverflow.com/questions/18355691/set-xlsx-to-recalculate-formulae-on-open
unzip -d $DIR /path/to/original.xlsx
sed -i "s/calcPr iterateCount=\"100\"/calcPr calcMode=\"auto\" fullCalcOnLoad=\"1\" iterateCount=\"100\"/g" $DIR/xl/workbook.xml
sed -i "s/PLACEHOLDER_A/$1/g" $DIR/xl/worksheets/sheet1.xml
sed -i "s/PLACEHOLDER_B/$2/g" $DIR/xl/worksheets/sheet1.xml
zip -r /tmp/output.xlsx *