解析xml模板导出Excel

今天药忘吃喽~ 2022-06-09 10:29 294阅读 0赞

1.pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
      <modelVersion>4.0.0</modelVersion>
      <groupId>com.tiglle</groupId>
      <artifactId>excel</artifactId>
      <version>0.0.1-SNAPSHOT</version>
    
      <dependencies>
        
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
    
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.16</version>
        </dependency>
        
        <dependency>
            <groupId>jdom</groupId>
            <artifactId>jdom</artifactId>
            <version>1.1</version>
        </dependency>
    
      </dependencies>
    
    </project>

2.excelConf.xml

<?xml version="1.0" encoding="UTF-8"?>
    
    <excel id="student" name="student" excelName="学生信息导入">
        <columns>
            <column index="A" width="17em"></column>
            <column index="B" width="17em"></column>
            <column index="C" width="17em"></column>
            <column index="D" width="17em"></column>
            <column index="E" width="17em"></column>
            <column index="F" width="17em"></column>
        </columns>
    
        <title rowspan="1" colspan="6">学生信息</title>
    
        <table>
            <tr height="16px">
                <td>编号</td>
                <td>姓名</td>
                <td>年龄</td>
                <td>生日</td>
                <td>爱好</td>
                <td>性别</td>
            </tr>
        </table>
        <format height="16px" firstRow="2" finstCol="0" repeat="5">
            <td type="String" allowNull="false" maxLength="30"></td>
            <td type="String" allowNull="false" maxLength="40"></td>
            <td type="number" allowNull="false" format="##0"></td>
            <td type="date" allowNull="true" maxLength="30"></td>
            <td type="String" allowNull="true" maxLength="30"></td>
            <td type="enum" allowNull="false" format="男,女"></td>
        </format>
    </excel>

3.GenerateExcelByTemplate.java

package com.tiglle.readXml;
    
    import java.awt.Color;
    import java.io.File;
    import java.io.FileOutputStream;
    import java.net.URL;
    import java.util.List;
    
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFRow;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.util.CellRangeAddress;
    import org.jdom.Document;
    import org.jdom.Element;
    import org.jdom.input.SAXBuilder;
    
    /** * 根据xml配置(格式，样式，标题等) * 使用jdom读取xml并生成Excle * @author Administrator * */
    public class GenerateExcelByTemplate {
    
        public static void main(String[] args){
            /****读取classpath下的文件*****/
            //根据本类对象获取类加载器
            ClassLoader classLoader = new GenerateExcelByTemplate().getClass().getClassLoader();
            //根据文件名从classpath下获取文件路径
            URL pathUrl = classLoader.getResource("excelModel.xml");
            //根据URL路径获取文件对象
            File xmlFile = new File(pathUrl.getFile());
            try {
                //创建Excel
                HSSFWorkbook workbook = new HSSFWorkbook();
                //创建一个sheet页
                HSSFSheet sheet1 = workbook.createSheet("sheet第一页");
                /*****通过jdom解析xml文件,并更具读取配置生成Excle***/
                //获取解析器
                SAXBuilder build = new SAXBuilder();
                //将文件内容读进解析器,生成document对象
                Document document = build.build(xmlFile);
                //获取xml跟节点
                Element root = document.getRootElement();
                //获取excel名称(跟节点的excelName属性)
                String excelName = root.getAttribute("excelName").getValue();
                //设置列的属性和宽度
                Element cloumns = root.getChild("columns");
                //获取子元素
                List<Element> childCloumns = cloumns.getChildren("cloumn");
                //循环设置每个列
                for(int i=0;i<childCloumns.size();i++){ 
                    Element cloumn = childCloumns.get(i);
                    //获取每个列的宽度属性(width)
                    String width = cloumn.getAttribute("width").getValue();
                    //截取，得到单位
                    String unit = width.substring(width.length()-2, width.length());
                    //将单位清空,得到值
                    String num = width.replace(unit, "");
                    //根据不同的单位转换成Excel的宽度
                    int excelWitdh = 0;
                    switch(unit){
                    case "px":
                        excelWitdh = Math.round(Float.parseFloat(num)*37F);
                        break;
                    case "em":
                        excelWitdh = Math.round(Float.parseFloat(num)*267.5F);
                        break;
                    }
                    //设置列宽
                    sheet1.setColumnWidth(i, excelWitdh);
                }
    
                //设置第一行的标题
                Element title = root.getChild("title");
                //标题内容
                String titleStr = title.getText();
                //合并的行数
                int rowspan = title.getAttribute("rowspan").getIntValue()-1;
                //合并的列数
                int colspan = title.getAttribute("colspan").getIntValue()-1;
                //创建0行
                HSSFRow row0 = sheet1.createRow(0);
                //创建一个cell(单元格)
                HSSFCell titleCell = row0.createCell(0);
                //设置内容
                titleCell.setCellValue(titleStr);
                //设置格式
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                //文字居中
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                //设置字体
                HSSFFont cellFont = workbook.createFont();
                //文字颜色
                cellFont.setColor((short) 20);
                //字体加入到样式中
                cellStyle.setFont(cellFont);
                titleCell.setCellStyle(cellStyle);
                //合并单元格(startY,endY,startX,endX)
                sheet1.addMergedRegion(new CellRangeAddress(0, rowspan, 0, colspan));
    
                //设置第二行的数据标题
                Element tr = root.getChild("table").getChild("tr");
                List<Element> tds = tr.getChildren("td");
                //创建1行
                HSSFRow row1 = sheet1.createRow(1);
                //设置高度(截取单位并且转换长度)
    //          row1.setHeight((short) tr.getAttribute("height").getIntValue());
                for(int i=0;i<tds.size();i++){ 
                    Element td = tds.get(i);
                    //创建列
                    HSSFCell cell1 = row1.createCell(i);
                    //设置内容
                    cell1.setCellValue(td.getText());
                }
                //设置样式:循环读取xml配置，就不写了 TODO
    
                //生成Excel文件
                File excelFile = new File("e:/"+excelName+".xls");
                //创建
                excelFile.createNewFile();
                //将Excel内容写入文件
                FileOutputStream fos = new FileOutputStream(excelFile);
                //开始写
                workbook.write(fos);
                //关闭流
                fos.close();
                workbook.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
    }