首先上添加maven依赖:
<!-- jxsl dependency for poi -->
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-poi</artifactId>
<version>${jxsl.poi.version}</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls</artifactId>
<version>${jxls.version}</version>
</dependency>
<dependency>
<groupId>org.jxls</groupId>
<artifactId>jxls-reader</artifactId>
<version>2.0.3</version>
</dependency>
导入识别excel转实体的xml文件:
<?xml version="1.0" encoding="UTF-8"?>
<workbook>
<worksheet name="产品列表">
<section startRow="1" endRow="1" />
<loop startRow="2" endRow="2" items="items" var="item" varType="com.ismartgo.uqcode.model.UqcProduct">
<section startRow="2" endRow="2">
<mapping row="2" col="0">item.product69code</mapping>
<mapping row="2" col="1">item.productName</mapping>
<mapping row="2" col="2">item.brand</mapping>
<mapping row="2" col="3">item.subBrand</mapping>
<mapping row="2" col="4">item.series</mapping>
<mapping row="2" col="5">item.category</mapping>
<mapping row="2" col="6">item.specs</mapping>
<mapping row="2" col="7">item.standardPrice</mapping>
</section>
<loopbreakcondition>
<rowcheck offset="0">
<cellcheck offset="0"></cellcheck>
</rowcheck>
</loopbreakcondition>
</loop>
</worksheet>
</workbook>
Excel模板:
Xml文件的<worksheet name="产品列表"> 代表识别的表格名称
excel转实体类的代码:
package com.ismartgo.uqcode.common.excel;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.formula.functions.T;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.SAXException;
import com.ismartgo.uqcode.model.UqcProduct;
import com.sun.tools.extcheck.Main;
public class Excel2Product {
private final static String xmlConfig = "excelXml/productExcelConfig.xml";
public static List<UqcProduct> excel2List(InputStream inputStream) throws InvalidFormatException, IOException, SAXException {
InputStream inputXML = new BufferedInputStream(Excel2Product.class.getClassLoader().getResourceAsStream(xmlConfig));
XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
InputStream inputXLS = new BufferedInputStream(inputStream);
UqcProduct pro = new UqcProduct();
List<UqcProduct> pros = new ArrayList<UqcProduct>();
Map beans = new HashMap<>();
beans.put("item", pro);
beans.put("items", pros);
XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
return pros;
}
}