基于DOM4j和POI实现的XML文件转换为XLS(即标准EXCEL)的JAVA程序
#第一次写博客有点小紧张,希望各位大牛多多包涵
大四狗最近来公司实习,经理大大给了一个测试开发能力的任务,读取xml文件并转换为xls文件,该xml可以通过修改后缀名用xls打开。所以要求即为读取xml格式的文件并且将其内的数据通过xml里的style格式改写成EXCEL标准单元格格式。
注:该程序有部分讨巧,部分公共格式并不是读取出来的而是直接设置好的,如介意请勿取
完整项目链接地址:
https://download.csdn.net/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" s