1. 表单中的file传入到controller层:
2.excel模板
3.
配置文件中的形式,xml的列 必须包含: excel 模板 的列
<?xml version="1.0" encoding="UTF-8" ?>
<modelMapping>
<!-- 东昌库存映射 -->
<dongChangStock>
<cloum name="经销商简称" dbname ="所属经销商" type="String" required ="true" titleLine="4"></cloum>
<cloum name="配置" dbname ="车型编号" type="String" required ="true"></cloum>
<cloum name="外饰色" dbname ="颜色" type="String" required ="true"></cloum>
<cloum name="内饰色" dbname ="内饰" type="String" required ="true"></cloum>
<cloum name="VIN" dbname ="VIN" type="String" required ="true"></cloum>
<cloum name="VIN" dbname ="生产号" type="String" required ="true"></cloum>
<cloum name="采购价格" dbname ="采购价格含税" type="String" required ="true"></cloum>
<cloum name="车厂指导价" dbname ="标配MSRP含税" type="String" required ="true"></cloum>
<cloum name="首次入库日期" dbname ="入库日期" type="String" required ="true"></cloum>
</dongChangStock>
<liveStock>
<cloum name="经销商简称" dbname ="所属经销商" type="String" required ="true" titleLine="4"></cloum>
<cloum name="配置" dbname ="车型编号" type="String" required ="true"></cloum>
<cloum name="外饰色" dbname ="颜色" type="String" required ="true"></cloum>
<cloum name="内饰色" dbname ="内饰" type="String" required ="true"></cloum>
<cloum name="VIN" dbname ="VIN" type="String" required ="true"></cloum>
</liveStock>
</modelMapping>
2.controller层直接读取流
@RequestMapping(value = "/import",method = RequestMethod.POST)
public void saveCustomerCarConfigure(MultipartHttpServletRequest request) throws Exception{
MultipartFile file = request.getFile("excel"); // key值是 传入的 表单的key值
InputStream inputStream = file.getInputStream();
String filename = file.getOriginalFilename();
Integer indexStar = filename.lastIndexOf(".") +1;
String afterLast = filename.substring(indexStar);
Map<String,Object> res = ExcelAnalysisUtil.validExcel(inputStream,afterLast,CONFIGN_ODE_NAME_DONGCHANG_STOCK);
Map<String,Object> validMap = ( Map<String,Object>)res.get("valid");
Boolean status = validMap.get("status") ==null?false:Boolean.parseBoolean(validMap.get("status").toString());
if(status){
List<Map<String,Object>> list = (List<Map<String,Object>>)res.get("list");
//return new ResponseMessage();
}
//return new ResponseMessage(String.valueOf(HttpStatusEnum.Authentication_Failure),res.get("str").toString());
}
5.工具类的方法:
ExcelAnalysisUtil.validExcel
package com.iris.live.services.common.reconsitution;
import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
* 读取excel
*/
public class ExcelAnalysisUtil {
/**
* 初始化
*/
private static Logger logger = LoggerFactory.getLogger("Excel");
public static final String TITLE_LINE ="titleLine";//参数:标题行数
public static final String XML_NAME ="StockConfig.xml";//映射模板配置文件名称
public static final String XML_EXCEL_NAME ="name";//映射模板用户对应列表标示
public static final String XML_EXCEL_DBNAME ="dbname";//映射模板实体对应标示
public static final String XML_TYPE_PARAM ="fileTypeName";//映射模板实体对应标示
public static final String EXCEL_TYPE_2003 ="xls";//检测返回message失败个数
public static final String EXCEL_TYPE_2007 ="xlsx";//检测返回message失败个数
public static final String VALID_EXCEL_ERROR ="上传文件格式不是excel!";//检测返回excel格式文本
public static final String VALID_RES_FAIL ="模板数据匹配失败条数:";//检测返回message失败个数
public static final String VALID_RES_SUSS ="模板数据匹配成功条数:";//检测返回message成功个数
public static final String VALID_RES_ERROR ="缺少下列必要属:%s请完善导入模板中对应数据后再次尝试!";//检测返回message文本
public static final String INIT_XML_FAIL ="StockConfig.xml Init Fail!";//xml初始化测失败
public static final String INIT_XML_FIRST_TITLE_LINE_FAIL ="StockConfig.xml First Row TitleLine Init Fail!";//titleLine初始化失败
public static List<Map<String,Object>> xmlList = null;//映射模板集合
/**
* 检测用户文件数据是否完整(应为同一文件流第二次读取异常的问题(文件流不能重复读取)暂时在检索的时候同步返回数据(仅成功匹配后))
* @param inputStream
* @param excelTypeName
* @return
* @throws IOException
*/
public static Map<String,Object> validExcel(InputStream inputStream,String excelTypeName,String nodeName)throws IOException {
xmlList = XmlAnalysisUtil.analysisXmlList(ExcelAnalysisUtil.XML_NAME,nodeName);
if(EXCEL_TYPE_2007.equals(excelTypeName)){
return (Map<String,Object>)validAndGetCusExcelList2007(inputStream,excelTypeName);
}
return (Map<String,Object>)validAndGetCusExcelList2003(inputStream,excelTypeName);
}
/**
* 获取上传excel数据
* @param inputStream
* @param excelTypeName
* @return
* @throws IOException
public static List<Map<String,Object>> getCusExcelList(InputStream inputStream,String excelTypeName) throws IOException {
return getSheetExcel(choiceCusExcel(inputStream,excelTypeName));
}*/
/**
* 读取sheet数据集合
* @param first
* @return
*/
private static List<Map<String,Object>> getSheetExcel(Sheet first){
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
//取得用户模板标题以及列的下标
Map<String,Object> title = Maps.newHashMap();
if(!initXmlRes()){
return list;
}
Integer titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString()) - 1;
int rowSize = first.getLastRowNum() + 1;
for (int i = 0; i < rowSize; i++) {
Row row = first.getRow(i);//获取当前行
int cellSize = row.getLastCellNum();//行中有多少个单元格,也就是有多少列
Map<String,Object> data =null;
for (int j = 0; j < cellSize; j++) {
if(i == titleLine){
if(null !=row.getCell(j)){
title.put(row.getCell(j).toString(),j);
}
}else if( i > titleLine){
data = buildMap(row,title);
}
}
//取得数据
if(null != data && data.size() > 0){
list.add(data);
}
}
return list;
}
/**
* 验证映射模板必须项
* @param first
* @return
*/
private static Map<String,Object> validExcelTitle(Sheet first){
//返回结果
Map<String,Object> res = Maps.newHashMap();
//取得用户模板标题以及列的下标
Map<String,Object> title = Maps.newHashMap();
if(!initXmlRes()){
return res;
}
Integer titleLine = Integer.parseInt(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString()) - 1;
int rowSize = first.getLastRowNum() + 1;
for (int i = 0; i < rowSize; i++) {
Row row = first.getRow(i);//获取当前行
int cellSize = row.getLastCellNum();//行中有多少个单元格,也就是有多少列
Map<String,Object> data =null;
for (int j = 0; j < cellSize; j++) {
if(i == titleLine && null !=row.getCell(j)){
title.put(row.getCell(j).toString(),j);
}
}
res = compareExcelTitle(row,title);
}
return res;
}
/**
* 按映射模板抽取一条/行数据
* @param row
* @param title
* @return
*/
private static Map<String,Object> buildMap(Row row, Map<String,Object> title){
Map<String,Object> stock = Maps.newHashMap();
Integer cellIndex = 0;
String excelTitle =null;
for (Map<String,Object> map : xmlList) {
try{
excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
if(title.containsKey(excelTitle)){
cellIndex = Integer.parseInt(title.get(excelTitle).toString());
//存入映射中配置的实体名称和当前
stock.put(map.get(ExcelAnalysisUtil.XML_EXCEL_DBNAME).toString(),row.getCell(cellIndex));
}
}catch (Exception e){
logger.error(e.getMessage());
}
}
return stock;
}
/**
* 匹配用户和模板差异
* @param row
* @param title
* @return
*/
private static Map<String,Object> compareExcelTitle(Row row, Map<String,Object> title){
Map<String,Object> res = Maps.newHashMap();
StringBuilder sb = new StringBuilder();
Integer failNum=0;
for (Map<String,Object> map : xmlList) {
String excelTitle = map.get(ExcelAnalysisUtil.XML_EXCEL_NAME).toString().trim();
if(!title.containsKey(excelTitle)){
sb.append(excelTitle + ",");
failNum = failNum + 1;
}
}
res.put("status",failNum ==0?true:false);
res.put("str",String.format(VALID_RES_ERROR,sb.toString()));
return res;
}
/**
* 模板是否初始化成功
* @return
*/
private static Boolean initXmlRes(){
//获取映射配置xml
if (null == xmlList || xmlList.size() <= 0){
logger.error(INIT_XML_FAIL);
return false;
}
//标题下标
if(!StringUtils.isNumeric(xmlList.get(0).get(ExcelAnalysisUtil.TITLE_LINE).toString())){
logger.error(INIT_XML_FIRST_TITLE_LINE_FAIL);
return false;
}
return true;
}
/**
* 获取上传excel类型动态选择解析方式
* @param inputStream
* @param excelTypeName
* @return
* @throws IOException
*/
private static Map<String,Object> validAndGetCusExcelList2003(InputStream inputStream,String excelTypeName) throws IOException {
Map<String,Object> mapRes = Maps.newHashMap();
Sheet first =null;
if(EXCEL_TYPE_2003.equals(excelTypeName)){
HSSFWorkbook hwb = new HSSFWorkbook(inputStream);
first = hwb.getSheetAt(0);
Map<String,Object> valid = validExcelTitle(first);
Boolean status = valid.get("status") ==null?false:Boolean.parseBoolean(valid.get("status").toString());
if(status){
List<Map<String,Object>> mapList = getSheetExcel(first);
mapRes.put("list",mapList);
mapRes.put(XML_TYPE_PARAM,excelTypeName);
}
mapRes.put("valid",valid);
}else{
logger.error(VALID_EXCEL_ERROR);
}
return mapRes;
}
/**
* 2007版本验证同步返回数据集合
* @param inputStream
* @param excelTypeName
* @return
* @throws IOException
*/
private static Map<String,Object> validAndGetCusExcelList2007(InputStream inputStream,String excelTypeName) throws IOException {
Map<String,Object> mapRes = Maps.newHashMap();
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
if(EXCEL_TYPE_2007.equals(excelTypeName)){
XSSFWorkbook workbook1 = new XSSFWorkbook(inputStream);
XSSFSheet sheet = workbook1.getSheetAt(0);
Map<String,Object> valid = validExcelTitle(sheet);
Boolean status = valid.get("status") ==null?false:Boolean.parseBoolean(valid.get("status").toString());
if(status){
List<Map<String,Object>> mapList = getSheetExcel(sheet);
mapRes.put("list",mapList);
mapRes.put(XML_TYPE_PARAM,excelTypeName);
}
mapRes.put("valid",valid);
}else{
logger.error(VALID_EXCEL_ERROR);
}
return mapRes;
}
}
5. 底层读出excel数据的方法
package com.iris.live.services.common.reconsitution;
import com.google.common.collect.Maps;
import org.dom4j.Attribute;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import java.io.File;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
/**
* Created by Administrator on 2017/3/29.
*/
public class XmlAnalysisUtil {
/**
* 返回映射配置集合列表
* @param filePath
* @return
*/
public static List<Map<String,Object>> analysisXmlList(String filePath,String nodeName){
Map<String,Object> map = analysisXml(filePath,nodeName);
return (List<Map<String,Object>>)map.get("list");
}
/**
* 返回映射配置中的用户EXCEL标题
* @param filePath
* @return
*/
public static List<String> analysisXmlTitleList(String filePath,String nodeName){
Map<String,Object> map = analysisXml(filePath,nodeName);
return (List<String>)map.get("titleList");
}
/**
* 解析本体返回数据
* @param filePath
* @return
* @throws Exception
*/
private static Map<String,Object> analysisXml(String filePath,String nodeName){
Map<String,Object> retuMap = Maps.newHashMap();
List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
List<String> titleList = new ArrayList<String>();
try {
SAXReader reader = new SAXReader();
// 通过reader对象的read方法加载xml文件,获取docuemnt对象。
Document document = reader.read(new File(XmlAnalysisUtil.class.getClassLoader().getResource(filePath).getPath()));
// 通过document对象获取根节点
Element model = document.getRootElement();
//指定节点
Element childModel = model.element(nodeName);
// 通过element对象的elementIterator方法获取迭代器
Iterator it = childModel.elementIterator();
// 遍历迭代器,获取根节点中的信息(书籍)
while (it.hasNext()) {
Element book = (Element) it.next();
Map<String,Object> map = Maps.newHashMap();
// 获取属性名以及 属性值
List<Attribute> bookAttrs = book.attributes();
for (Attribute attr : bookAttrs) {
map.put(attr.getName(),attr.getValue());
if(ExcelAnalysisUtil.XML_EXCEL_NAME.equals(attr.getName())){
titleList.add(attr.getValue());
}
}
list.add(map);
/* //解析子节点的信息
Iterator itt = book.elementIterator();
while (itt.hasNext()) {
Element bookChild = (Element) itt.next();
System.out.println("节点名:" + bookChild.getName() + "--节点值:" + bookChild.getStringValue());
}*/
retuMap.put("list",list);
retuMap.put("titleList",titleList);
}
} catch (DocumentException e) {
e.printStackTrace();
}
return retuMap;
}
public static void main(String[] args) {
System.out.println(analysisXmlList("StockConfig.xml","liveStock"));
System.out.println(analysisXmlTitleList("StockConfig.xml","liveStock"));
}
}