以下 VBS 和 shell 脚本的组合为 Excel 电子表格中的每个工作表创建一个 CSV 文件:
$ cat xls2csv.vbs
csv_format = 6
Dim strFilename
Dim objFSO
Set objFSO = CreateObject("scripting.filesystemobject")
strFilename = objFSO.GetAbsolutePathName(WScript.Arguments(0))
If objFSO.fileexists(strFilename) Then
Call Writefile(strFilename)
Else
wscript.echo "no such file!"
End If
Set objFSO = Nothing
Sub Writefile(ByVal strFilename)
Dim objExcel
Dim objWB
Dim objws
Set objExcel = CreateObject("Excel.Application")
Set objWB = objExcel.Workbooks.Open(strFilename)
For Each objws In objWB.Sheets
objws.Copy
objExcel.ActiveWorkbook.SaveAs objWB.Path & "\" & objws.Name & ".csv", csv_format
objExcel.ActiveWorkbook.Close False
Next
objWB.Close False
objExcel.Quit
Set objExcel = Nothing
End Sub
.
$ cat xls2csv
PATH="$HOME:$PATH"
# the original XLS input file path components
inXlsPath="$1"
inXlsDir=$(dirname "$inXlsPath")
xlsFile=$(basename "$inXlsPath")
xlsBase="${xlsFile%.*}"
# The tmp dir we'll copy the XLS to and run the tool on
# to get the CSVs generated
tmpXlsDir="/usr/tmp/${xlsBase}.$$"
tmpXlsPath="${tmpXlsDir}/${xlsFile}"
absXlsPath="C:/cygwin64/${tmpXlsPath}" # need an absolute path for VBS to work
mkdir -p "$tmpXlsDir"
trap 'rm -f "${tmpXlsDir}/${xlsFile}"; rmdir "$tmpXlsDir"; exit' 0
cp "$inXlsPath" "$tmpXlsDir"
cygstart "$HOME/xls2csv.vbs" "$absXlsPath"
printf "Waiting for \"${tmpXlsDir}/~\$${xlsFile}\" to be created:\n" >&2
while [ ! -f "${tmpXlsDir}/~\$${xlsFile}" ]
do
# VBS is done when this tmp file is created and later removed
printf "." >&2
sleep 1
done
printf " Done.\n" >&2
printf "Waiting for \"${tmpXlsDir}/~\$${xlsFile}\" to be removed:\n" >&2
while [ -f "${tmpXlsDir}/~\$${xlsFile}" ]
do
# VBS is done when this tmp file is removed
printf "." >&2
sleep 1
done
printf " Done.\n" >&2
numFiles=0
for file in "$tmpXlsDir"/*.csv
do
numFiles=$(( numFiles + 1 ))
done
if (( numFiles >= 1 ))
then
outCsvDir="${inXlsDir}/${xlsBase}.csvs"
mkdir -p "$outCsvDir"
mv "$tmpXlsDir"/*.csv "$outCsvDir"
fi
现在我们执行 shell 脚本,该脚本内部调用 cygstart 运行 VBS 脚本,在 Excel 文件所在目录下的子目录中生成 CSV 文件(每张一个),该子目录根据 Excel 文件名命名(例如 Excel 文件)staff.xlsx
生成 CSV 目录staff.csvs
):
$ ./xls2csv "$D/staff.xlsx"
Waiting for "/usr/tmp/staff.2700/~$staff.xlsx" to be created:
.. Done.
Waiting for "/usr/tmp/staff.2700/~$staff.xlsx" to be removed:
. Done.
只有一张具有默认名称的工作表Sheet1
在目标 Excel 文件中"$D/staff.xlsx"
所以上面的输出是一个文件"$D/staff.csvs/Sheet1.csv"
:
$ cat "$D/staff.csvs/Sheet1.csv"
Name,Position
Sue,Manager
Bill,Secretary
Pat,Engineer
$ awk -F, -v name="Bill" '$1==name{print $2}' "$D/staff.csvs/Sheet1.csv"
Secretary
另请参阅使用 awk 有效解析 CSV 的最可靠方法是什么? https://stackoverflow.com/q/45420535/1745001了解如何操作这些 CSV。
也可以看看https://stackoverflow.com/a/58879683/1745001 https://stackoverflow.com/a/58879683/1745001了解如何执行相反的操作,即从 Windows 批处理文件调用 cygwin bash 命令。