公司的一个功能, 提供用户批量导入系统的模板。
具体如下:
要导入的坐标:
<!-- 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();
}
}
记录一下, 后续可以复用的代码。