如何使用bash脚本将多个excel文件合并到一个excel工作簿但不同的工作表中
> input: file1_1234.xlsx
file2_1234.xlsx
file3_9999.xlsx
file4_1245.xlsx
file5_9999.xlsx
file6_1245.xlsx
输出：
**outputfile_1234.xlsx** which contains
file1_1234.xlsx
file2_1234.xlsx in two different worksheets
**outputfile_9999.xlsx** which contains
file3_9999.xlsx
file5_9999.xlsx in two different worksheets
**outputfile_1245.xlsx** which contains
file4_1245.xlsx
file6_1245.xlsx in two different worksheets
有趣的问题。
我曾经能够使用xsltproc将两个FODS电子表格(代表Flat Open Document Spreadsheet(合并为一个。两个电子表格都很简单，而且非常相似。我不知道这是否适用于所有类型的电子表格。
因此，有必要首先将XLSX电子表格转换为FODS电子表格，发布：
soffice --headless --convert-to fods file*.xlsx
xsltproc命令需要一个样式表作为参数，这是下一个XSL程序：
tablemerge.xsl:
<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0"
xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0"
xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0"
xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0"
xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"
xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"
xmlns:xlink="http://www.w3.org/1999/xlink"
xmlns:dc="http://purl.org/dc/elements/1.1/"
xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0"
xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"
xmlns:presentation="urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"
xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"
xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0"
xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"
xmlns:math="http://www.w3.org/1998/Math/MathML"
xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0"
xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0"
xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0"
xmlns:ooo="http://openoffice.org/2004/office"
xmlns:ooow="http://openoffice.org/2004/writer"
xmlns:oooc="http://openoffice.org/2004/calc"
xmlns:dom="http://www.w3.org/2001/xml-events"
xmlns:xforms="http://www.w3.org/2002/xforms"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:rpt="http://openoffice.org/2005/report"
xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns:grddl="http://www.w3.org/2003/g/data-view#"
xmlns:tableooo="http://openoffice.org/2009/table"
xmlns:drawooo="http://openoffice.org/2010/draw"
xmlns:calcext="urn:org:documentfoundation:names:experimental:calc:xmlns:calcext:1.0"
xmlns:loext="urn:org:documentfoundation:names:experimental:office:xmlns:loext:1.0"
xmlns:field="urn:openoffice:names:experimental:ooo-ms-interop:xmlns:field:1.0"
xmlns:formx="urn:openoffice:names:experimental:ooxml-odf-interop:xmlns:form:1.0"
xmlns:css3t="http://www.w3.org/TR/css3-text/"
office:version="1.2"
office:mimetype="application/vnd.oasis.opendocument.spreadsheet">
<xsl:template match="table:table">
<!-- copy table:table from main file -->
<xsl:copy>
<xsl:apply-templates select="@* | node()" />
</xsl:copy>
<table:table
table:name="{document($secondfile)//table:table/@table:name}"
table:style-name="{document($secondfile)//table:table/@table:style-name}">
<!-- copy table:table from second file -->
<xsl:copy-of select="document($secondfile)//table:table/child::*" />
</table:table>
</xsl:template>
<!-- default template: identity transform -->
<xsl:template match="/ | @* | node()">
<xsl:copy>
<xsl:apply-templates select="@* | node()" />
</xsl:copy>
</xsl:template>
</xsl:stylesheet>
必须按如下方式调用此XSL程序：
xsltproc --stringparam secondfile file2.fods tablemerge.xsl file1.fods > mergedtables.fods
现在，将所有这些部分连接在一起的脚本可以是：
脚本：
#!/bin/bash
soffice --headless --convert-to fods file*.xlsx
for key in $(ls file*.xlsx | cut -d . -f 1 | cut -d _ -f 2 | sort -u)
do
outputfile=outputfile_$key.fods
inputfiles=( file*_$key.fods )
maininputfile=${inputfiles[0]}
secondinputfile=${inputfiles[1]}
xsltproc --stringparam secondfile $secondinputfile tablemerge.xsl $maininputfile
soffice --headless --convert-to xslx outputfile*.fods
done