基于DOM4j和POI实现的XML文件转换为XLS(即标准EXCEL)的JAVA程序
#第一次写博客有点小紧张,希望各位大牛多多包涵
大四狗最近来公司实习,经理大大给了一个测试开发能力的任务,读取xml文件并转换为xls文件,该xml可以通过修改后缀名用xls打开。所以要求即为读取xml格式的文件并且将其内的数据通过xml里的style格式改写成EXCEL标准单元格格式。
注:该程序有部分讨巧,部分公共格式并不是读取出来的而是直接设置好的,如介意请勿取
完整项目链接地址:
https://download.youkuaiyun.com/download/amazon_king/10981005
实现过程
首先贴上xml格式的文件:
<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">
<Styles>
<Style ID="HyperlinkId" ss:Name="Hyperlink">
<Font ss:Color="#0000ff"/>
</Style>
<Style ss:ID="37">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="29">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="30">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="40">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="27">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
<Interior ss:Color="#c0c0c0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="21">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="23">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="34">
<Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
<NumberFormat ss:Format="#,##0.00"/>
</Style>
<Style ss:ID="26">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="1">
<NumberFormat ss:Format="yyyy-M-d HH:mm:ss AM/PM"/>
</Style>
<Style ss:ID="22">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="16.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="31">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="25">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="39">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="32">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="20">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="28">
<Alignment ss:Horizontal="Center" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
<Interior ss:Color="#c0c0c0" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="24">
<Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left"/>
<Border ss:Position="Right"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Bold="1" ss:Color="#000000"/>
</Style>
<Style ss:ID="38">
<Alignment ss:Horizontal="Left" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="36">
<Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="35">
<Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
</Style>
<Style ss:ID="33">
<Alignment ss:Horizontal="Right" ss:Vertical="Top" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1" ss:Color="#000000"/>
<Border ss:Position="DiagonalLeft"/>
</Borders>
<Font ss:FontName="serif" ss:Size="10.0" ss:Color="#000000"/>
<NumberFormat ss:Format="#,##0.00"/>
</Style>
</Styles>
<Names>
<NamedRange ss:Name="__bookmark_1" ss:RefersTo="Report!R1C1:R38C21"/>
</Names>
<Worksheet ss:Name="Report">
<ss:Table>
<ss:Column ss:Width="69.376" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="31.36" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="38.016" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="62.72" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="40.192" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="1.92" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="38.4" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="28.672" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="11.648" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="40.32" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="13.44" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="26.88" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="46.464" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="54.272" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="15.104" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="73.344" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="12.288" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="61.056" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="39.68" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="22.656" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="68.608" ss:AutoFitWidth="0"/>
<ss:Column ss:Width="9.472" ss:AutoFitWidth="0"/>
<Row ss:AutoFitHeight="0" ss:Height="18.75">
<Cell ss:Index="1" ss:StyleID="21" ss:MergeAcross="20"/>
<Cell ss:Index="22" ss:StyleID="20" ss:MergeDown="37"/>
</Row>
<Row ss:AutoFitHeight="1">
<Cell ss:Index="1" ss:StyleID="22" ss:M