模板导入导出 excel

公司的一个功能， 提供用户批量导入系统的模板。

具体如下：

要导入的坐标：

        <!-- poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- XML操作相关 -->
        <dependency>
            <groupId>dom4j</groupId>
            <artifactId>dom4j</artifactId>
            <version>1.6</version>
        </dependency>

在resources/templates下创建一个叫importEXCEL.xml的文件。

<root>
    <ConsAccount>
        <cons_name default="1" example="张三">用户名称</cons_name>
    </ConsAccount>
</root>

写一个config

package com.techen.ami.config;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.context.annotation.Configuration;
import org.springframework.stereotype.Component;
import org.springframework.util.ResourceUtils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.List;

/**
 *
 * @Modify: EXCEL导入模板操作类
 * @author: lihao
 * @date: 2021/5/12 17:33
 * @param paraMap
 * @return ResponseResult<Object>
 *
 */
@Configuration
@Component
public class EXCELConfig {

    Document document;

    public EXCELConfig() throws FileNotFoundException, DocumentException {
        File file = ResourceUtils.getFile("classpath:templates/importEXCEL.xml");
        InputStream in = new FileInputStream(file);
        SAXReader reader = new SAXReader();
        document = reader.read(in);
    }

    /**
     *
     * @Modify: 获取模板配置中的所有模板名称
     * @author: lihao
     * @date: 2021/5/15 13:55
     * @param paraMap
     * @return ResponseResult<Object>
     *
     */
    public StringBuffer getAllExcelModelName(){
        StringBuffer result = new StringBuffer();
        // 获取xml文档根节点
        Element root = document.getRootElement();
        List<Element> listElement = root.elements();
        for(int i=0; i<listElement.size(); i++)
        {
            String elementName = listElement.get(i).getName();
            result.append(elementName);
        }
        return  result;
    }

    /**
     *
     * @Modify: 按模版名称获取模版的excel文件
     * @author: lihao
     * @date: 2021/5/15 14:12
     * @param paraMap
     * @return ResponseResult<Object>
     *
     */
    public byte[] getExcelModelByName(String modelName)
    {
        // 获取xml文档根节点
        Element root = document.getRootElement();
        Element element = root.element(modelName);
        if(element == null){
            return null;
        }
        //构造excel文件
        HSSFWorkbook excel =new HSSFWorkbook();
        HSSFSheet sheet0=excel.createSheet(element.getName());
        HSSFRow rowNameExcel = sheet0.createRow(0);
        HSSFRow rowDataExcel = sheet0.createRow(1);
        List<Element> colList = element.elements();
        for(int i=0; i<colList.size(); i++)
        {
            String data = colList.get(i).getData().toString();
            // 标头
            HSSFCell cellExcel = rowNameExcel.createCell(i);
            cellExcel.setCellValue(data);
            // 示例内容
            HSSFCell cellExample = rowDataExcel.createCell(i);
            Attribute attribute = colList.get(i).attribute("example");
            if(attribute != null)
            {
                cellExample.setCellValue(attribute.getValue());
            }
        }
        return excel.getBytes();
    }
}

记录一下， 后续可以复用的代码。