基于springmvc的easypoi简单使用
最近要做一些excel导入导出的工作,就花时间研究了一下,官方文档实在是有点不足,在这里分享一下研究成果
项目框架,SSM框架,基础框架是直接从mybatis-plus项目上 clone下来的
使用eclipse开发
官方资料
maven基本依赖
<dependency>
<groupId>org.jeecg</groupId>
<artifactId>easypoi-base</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>org.jeecg</groupId>
<artifactId>easypoi-web</artifactId>
<version>2.3.1</version>
</dependency>
<dependency>
<groupId>org.jeecg</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>2.3.1</version>
</dependency>
springmvc的配置
默认视图级别设置低点
<!-- 默认的视图解析器 在上边的解析错误时使用 (默认使用html)- -->
<bean id="defaultViewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver"
p:order="3">
<property name="viewClass"
value="org.springframework.web.servlet.view.JstlView" />
<property name="contentType" value="text/html" />
<property name="prefix" value="/webpage/" />
<property name="suffix" value=".jsp" />
</bean>
Bean视图设置级别高一些,然后把我们的4个试图配置上,就完成了
<!-- Bean解析器,级别高于默认解析器,寻找bean对象进行二次处理 -->
<bean id="beanNameViewResolver"
class="org.springframework.web.servlet.view.BeanNameViewResolver" p:order="0">
</bean>
<!-- Excel 处理 根据用户输入进行对象处理 -->
<bean id="jeecgExcelView" class="org.jeecgframework.poi.excel.view.JeecgSingleExcelView" />
<bean id="jeecgTemplateExcelView" class="org.jeecgframework.poi.excel.view.JeecgTemplateExcelView" />
<bean id="jeecgTemplateWordView" class="org.jeecgframework.poi.excel.view.JeecgTemplateWordView" />
<bean id="jeecgMapExcelView" class="org.jeecgframework.poi.excel.view.JeecgMapExcelView" />
2.0.8版本后加上了@Controller 里面只要在
<context:component-scan base-package="org.jeecgframework.poi.excel.view">
加入就可以了
完成了这些配置以后就可以开始开发了
导出
###1. Map方式导出
ExcelExportEntity的构造函数是ExcelExportEntity(String name, Object key, int width)
指定了column名和property名,用于生成表头,但是不知道这个width有什么作用。
然后设置了文件名,title,second title,sheet名等
@RequestMapping("/MapExportExcel")
public String exportMerchantProfitQuery(ModelMap modelMap, HttpServletRequest request) {
List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
entityList.add(new ExcelExportEntity("用户ID", "id", 35));
entityList.add(new ExcelExportEntity("用户名", "name", 15));
entityList.add(new ExcelExportEntity("用户年龄", "age", 15));
List<Map<String, String>> dataResult = getData();
modelMap.put(MapExcelConstants.ENTITY_LIST, entityList);
modelMap.put(MapExcelConstants.MAP_LIST, dataResult);
modelMap.put(MapExcelConstants.FILE_NAME, "商户利润");
Date now = new Date();
modelMap.put(NormalExcelConstants.PARAMS, new ExportParams("商户利润详情", "创建时间" + now.toLocaleString(), "商户"));
return MapExcelConstants.JEECG_MAP_EXCEL_VIEW;
}
private List<Map<String, String>> getData() {
List<Map<String, String>> dataResult = new ArrayList<Map<String, String>>();
Map<String, String> u1 = new LinkedHashMap<String, String>();
u1.put("id", "1");
u1.put("name", "cyf");
u1.put("age", "21");
Map<String, String> u2 = new LinkedHashMap<String, String>();
u2.put("id", "2");
u2.put("name", "cy");
u2.put("age", "22");
dataResult.add(u1);
dataResult.add(u2);
return dataResult;
}
2.注解导出
注解导出需要在实体类上先加上注解
这里先简要介绍
两个实体类
package com.baomidou.springmvc.model.system;
import java.io.Serializable;
import java.util.Date;
import org.jeecgframework.poi.excel.annotation.Excel;
public class Product implements Serializable{
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
@Excel(name = "商品名", needMerge = true)
private String name;
@Excel(name = "价格", needMerge = true)
private double price;
@Excel(name = "购买时间", exportFormat="yyyy/mm/dd", needMerge = true)
private Date time;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
}
package com.baomidou.springmvc.model.system;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
import org.jeecgframework.poi.excel.annotation.Excel;
import org.jeecgframework.poi.excel.annotation.ExcelCollection;
import org.jeecgframework.poi.excel.annotation.ExcelEntity;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;
import com.baomidou.mybatisplus.annotations.TableName;
/**
*
* 系统用户表
*
*/
@ExcelTarget("User")
@TableName("sys_user")
public class User implements Serializable {
private static final long serialVersionUID = 1L;
/** 用户ID */
@Excel(name = "用户id" , needMerge = true)
private Long id;
/** 用户名 */
@Excel(name = "用户名", needMerge = true)
private String name;
/** 用户年龄 */
@Excel(name = "年龄", needMerge = true)
private Integer age;
@ExcelEntity(name = "商品")
private Product product;
/**购买的商品*/
@ExcelCollection(name = "商品序列")
private List<Product> products;
/**创建时间*/
@Excel(name = "创建时间" ,exportFormat="yyyy-mm-dd" , needMerge = true )
private Date time;
/**性别*/
@Excel(name="性别" , replace={"男_1","女_0"}, needMerge = true)
private int sex;
public List<Product> getProducts() {
return products;
}
public void setProducts(List<Product> products) {
this.products = products;
}
public Product getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
public Long getId() {
return this.id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Integer getAge() {
return this.age;
}
public void setAge(Integer age) {
this.age = age;
}
}
controller 这里需要注意的一点是
setExclusions 写的是实际列名,而不是属性名
@RequestMapping("/excelAnno")
public String excelAnno(ModelMap map1) {
List<User> list = getUsers();
map1.put(NormalExcelConstants.CLASS, User.class);
map1.put(NormalExcelConstants.FILE_NAME, "用户导出测试");
ExportParams ep = new ExportParams("历史总包滚存分析1", "历史总包滚存分析2");
ep.setExclusions(new String[] { "年龄" });// 这里填替换后的
map1.put(NormalExcelConstants.PARAMS, ep);
map1.put(NormalExcelConstants.DATA_LIST, list);
return NormalExcelConstants.JEECG_EXCEL_VIEW;
}
private List<User> getUsers() {
Product p1 = new Product();
Product p2 = new Product();
p1.setId(1);
p1.setName("apple");
p1.setPrice(10);
p1.setTime(new Date());
p2.setId(2);
p2.setName("pear");
p2.setPrice(30);
p2.setTime(new Date());
User u1 = new User();
u1.setAge(21);
u1.setId(Long.parseLong("1"));
u1.setName("cyf");
u1.setProduct(p1);
u1.setSex(1);
List<Product> products = new ArrayList<Product>();
products.add(p2);
products.add(p1);
u1.setProducts(products);
u1.setTime(new Date());
User u2 = new User();
u2.setAge(23);
u2.setId(Long.parseLong("2"));
u2.setName("cy");
u2.setProduct(p2);
u2.setSex(1);
u2.setProducts(products);
u2.setTime(new Date());
List<User> users = new ArrayList<User>();
users.add(u1);
users.add(u2);
return users;
}
导出结果
###3.多sheet导出
@RequestMapping("/multiplyexcelAnno")
public void multiplyexcelAnno(HttpServletRequest req, HttpServletResponse resp) throws UnsupportedEncodingException {
Map<String, Object> map1 = getTestMap();
Map<String, Object> map2 = getTestMap();
List<Map<String,Object>> list1 = new ArrayList<Map<String,Object>>();
list1.add(map1);
list1.add(map2);
Workbook workbook = exportExcel(list1, ExcelType.HSSF);
req.setCharacterEncoding("UTF-8");
resp.setCharacterEncoding("UTF-8");
resp.setContentType("application/x-download");
String filedisplay = "product.xls";
filedisplay = URLEncoder.encode(filedisplay, "UTF-8");
resp.addHeader("Content-Disposition", "attachment;filename=" + filedisplay);
try {
OutputStream out = resp.getOutputStream();
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private Map<String, Object> getTestMap() {
Map<String,Object> map1 = new LinkedHashMap<String,Object>();
List<User> list = getUsers();
map1.put(NormalExcelConstants.CLASS, User.class);
map1.put(NormalExcelConstants.FILE_NAME, "用户导出测试");
ExportParams ep = new ExportParams("历史总包滚存分析1", "111"+(1000*Math.random()));
ep.setExclusions(new String[] { "年龄" });// 这里填替换后的
map1.put(NormalExcelConstants.PARAMS, ep);
map1.put(NormalExcelConstants.DATA_LIST, list);
return map1;
}
public static Workbook exportExcel(List<Map<String, Object>> list, ExcelType type) {
Workbook workbook;
if (ExcelType.HSSF.equals(type)) {
workbook = new HSSFWorkbook();
} else {
workbook = new XSSFWorkbook();
}
for (Map<String, Object> map : list) {
ExcelExportServer server = new ExcelExportServer();
ExportParams params = (ExportParams) map.get("params");
Class<?> entry = (Class<?>) map.get("entity");
Collection<?> data = (Collection<?>) map.get("data");
server.createSheet(workbook, params,entry ,data);
}
return workbook;
}
导出结果
导入
导入的时候也是利用了注解,基本上就是导出的反操作
需要说明的是目前官方的导入集合还没修复,好在我在pull request里找到了解决的方法,封装了拿来用,果然可以
@RequestMapping(value = "/import", method = RequestMethod.POST)
@ResponseBody
public void importExcel(MultipartFile file,HttpServletRequest request) {
try {
ImportParams params = new ImportParams();
params.setTitleRows(1);
params.setHeadRows(2);
params.setNeedSave(true);
String path = request.getSession().getServletContext().getRealPath("");
File f = new File(path+"/excel/"+file.getOriginalFilename());
if(!f.exists()){
try {
File dir = new File(path+"/excel/");
dir.mkdirs();
if(f.createNewFile()){
System.out.println("创建文件成功");
}else{
System.out.println("创建文件失败");
}
} catch (IOException e) {
e.printStackTrace();
}
}
file.transferTo(f);
List<User> list = WrapperUtil.warpedImportExcel( f, User.class, params);
System.out.println(JSON.toJSONString(list));
} catch (Exception e) {
e.printStackTrace();
}
}
前端页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<c:set var="ctx" value="${pageContext.request.contextPath}"/>
<html>
<head>
<title>用户列表</title>
</head>
<body>
<input type="button" value="Map导出" onclick="download1()"/>
<input type="button" value="anno导出" onclick="download2()"/>
<input type="button" value="mulanno导出" onclick="download3()"/>
<form action="import" method="POST" enctype="multipart/form-data">
<input type="file" name="file"/>
<input type="submit" value="上传" />
</form>
</body>
<script type="text/javascript">
function download1(){
window.open('/mybatisplus-spring-mvc/MapExportExcel');
}
function download2(){
window.open('/mybatisplus-spring-mvc/excelAnno');
}
function download3(){
window.open('/mybatisplus-spring-mvc/multiplyexcelAnno');
}
</script>
</html>
本来想补档的 但是实在找不到了demo了 只能去实际项目中把相关的类补充下 (其中ExcelImportServerWrapper 不是我原创的 保留原作者信息 ) 希望能帮到大家
import java.io.File;
import java.io.FileInputStream;
import java.util.List;
import org.apache.poi.util.IOUtils;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.exception.excel.ExcelImportException;
public class WrapperUtil {
public static <T> List<T> warpedImportExcel(File file, Class<?> pojoClass, ImportParams params) {
FileInputStream in = null;
try {
in = new FileInputStream(file);
return new ExcelImportServerWrapper().importExcelByIs(in, pojoClass, params).getList();
} catch (ExcelImportException e) {
throw new ExcelImportException(e.getType(), e);
} catch (Exception e) {
throw new ExcelImportException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(in);
}
}
}
/**
* Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com)
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.PushbackInputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.poi.excel.annotation.ExcelTarget;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.excel.entity.params.ExcelCollectionParams;
import org.jeecgframework.poi.excel.entity.params.ExcelImportEntity;
import org.jeecgframework.poi.excel.entity.result.ExcelImportResult;
import org.jeecgframework.poi.excel.entity.result.ExcelVerifyHanlderResult;
import org.jeecgframework.poi.excel.imports.CellValueServer;
import org.jeecgframework.poi.excel.imports.base.ImportBaseService;
import org.jeecgframework.poi.exception.excel.ExcelImportException;
import org.jeecgframework.poi.exception.excel.enums.ExcelImportEnum;
import org.jeecgframework.poi.handler.inter.IExcelModel;
import org.jeecgframework.poi.util.PoiPublicUtil;
import org.jeecgframework.poi.util.PoiReflectorUtil;
import org.jeecgframework.poi.util.PoiValidationUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
/**
* Excel 导入服务
*
* @author JueYue
* 2014年6月26日 下午9:20:51
*/
@SuppressWarnings({ "rawtypes", "unchecked", "hiding" })
public class ExcelImportServerWrapper extends ImportBaseService {
private final static Logger LOGGER = LoggerFactory.getLogger(ExcelImportServerWrapper.class);
private CellValueServer cellValueServer;
private boolean verfiyFail = false;
/**
* 异常数据styler
*/
private CellStyle errorCellStyle;
public ExcelImportServerWrapper() {
this.cellValueServer = new CellValueServer();
}
/***
* 向List里面继续添加元素
*
* @param object
* @param param
* @param row
* @param titlemap
* @param targetId
* @param pictures
* @param params
*/
private void addListContinue(Object object, ExcelCollectionParams param, Row row,
Map<Integer, String> titlemap, String targetId,
Map<String, PictureData> pictures,
ImportParams params) throws Exception {
Collection collection = (Collection) PoiReflectorUtil.fromCache(object.getClass())
.getValue(object, param.getName());
Object entity = PoiPublicUtil.createObject(param.getType(), targetId);
String picId;
boolean isUsed = false;// 是否需要加上这个对象
for (int i = row.getFirstCellNum(); i < titlemap.size(); i++) {
Cell cell = row.getCell(i);
String titleString = (String) titlemap.get(i);
if (param.getExcelParams().containsKey(titleString)) {
if (param.getExcelParams().get(titleString).getType() == 2) {
picId = row.getRowNum() + "_" + i;
saveImage(object, picId, param.getExcelParams(), titleString, pictures, params);
} else {
saveFieldValue(params, entity, cell, param.getExcelParams(), titleString, row);
}
isUsed = true;
}
}
if (isUsed) {
collection.add(entity);
}
}
/**
* 获取key的值,针对不同类型获取不同的值
*
* @author JueYue
* 2013-11-21
* @param cell
* @return
*/
private String getKeyValue(Cell cell) {
Object obj = null;
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
obj = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_BOOLEAN:
obj = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
obj = cell.getNumericCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
obj = cell.getCellFormula();
break;
default:
cell.setCellType(Cell.CELL_TYPE_STRING);
obj = cell.getStringCellValue();
}
return obj == null ? null : obj.toString().trim();
}
/**
* 获取保存的真实路径
*
* @param excelImportEntity
* @param object
* @return
* @throws Exception
*/
private String getSaveUrl(ExcelImportEntity excelImportEntity, Object object) throws Exception {
String url = "";
if (excelImportEntity.getSaveUrl().equals("upload")) {
if (excelImportEntity.getMethods() != null
&& excelImportEntity.getMethods().size() > 0) {
object = getFieldBySomeMethod(excelImportEntity.getMethods(), object);
}
url = object.getClass().getName()
.split("\\.")[object.getClass().getName().split("\\.").length - 1];
return excelImportEntity.getSaveUrl() + "/"
+ url;
}
return excelImportEntity.getSaveUrl();
}
private <T> List<T> importExcel(Collection<T> result, Sheet sheet, Class<?> pojoClass,
ImportParams params,
Map<String, PictureData> pictures) throws Exception {
List collection = new ArrayList();
Map<String, ExcelImportEntity> excelParams = new HashMap<String, ExcelImportEntity>();
List<ExcelCollectionParams> excelCollection = new ArrayList<ExcelCollectionParams>();
String targetId = null;
if (!Map.class.equals(pojoClass)) {
Field fileds[] = PoiPublicUtil.getClassFields(pojoClass);
ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
if (etarget != null) {
targetId = etarget.value();
}
getAllExcelField(targetId, fileds, excelParams, excelCollection, pojoClass, null);
}
Iterator<Row> rows = sheet.rowIterator();
for (int j = 0; j < params.getTitleRows(); j++) {
rows.next();
}
Map<Integer, String> titlemap = getTitleMap(rows, params, excelCollection);
checkIsValidTemplate(titlemap, excelParams, params, excelCollection);
Row row = null;
Object object = null;
String picId;
while (rows.hasNext()
&& (row == null
|| sheet.getLastRowNum() - row.getRowNum() > params.getLastOfInvalidRow())) {
row = rows.next();
// 判断是集合元素还是不是集合元素,如果是就继续加入这个集合,不是就创建新的对象
// keyIndex 如果为空就不处理,仍然处理这一行
if (params.getKeyIndex() != null
&& (row.getCell(params.getKeyIndex()) == null
|| StringUtils.isEmpty(getKeyValue(row.getCell(params.getKeyIndex()))))
&& object != null) {
for (ExcelCollectionParams param : excelCollection) {
addListContinue(object, param, row, titlemap, targetId, pictures, params);
}
} else {
object = PoiPublicUtil.createObject(pojoClass, targetId);
try {
for (int i = row.getFirstCellNum(), le = titlemap.size(); i < le; i++) {
Cell cell = row.getCell(i);
String titleString = (String) titlemap.get(i);
if (excelParams.containsKey(titleString) || Map.class.equals(pojoClass)) {
if (excelParams.get(titleString) != null
&& excelParams.get(titleString).getType() == 2) {
picId = row.getRowNum() + "_" + i;
saveImage(object, picId, excelParams, titleString, pictures,
params);
} else {
saveFieldValue(params, object, cell, excelParams, titleString, row);
}
}
}
for (ExcelCollectionParams param : excelCollection) {
addListContinue(object, param, row, titlemap, targetId, pictures, params);
}
if (verifyingDataValidity(object, row, params, pojoClass)) {
collection.add(object);
}
} catch (ExcelImportException e) {
if (!e.getType().equals(ExcelImportEnum.VERIFY_ERROR)) {
throw new ExcelImportException(e.getType(), e);
}
}
}
}
return collection;
}
/**
* 校验数据合法性
* @param object
* @param row
* @param params
* @param pojoClass
* @return
*/
private boolean verifyingDataValidity(Object object, Row row, ImportParams params,
Class<?> pojoClass) {
boolean isAdd = true;
Cell cell = null;
if (params.isNeedVerfiy()) {
String errorMsg = PoiValidationUtil.validation(object);
if (StringUtils.isNotEmpty(errorMsg)) {
cell = row.createCell(row.getLastCellNum());
cell.setCellValue(errorMsg);
if (object instanceof IExcelModel) {
IExcelModel model = (IExcelModel) object;
model.setErrorMsg(errorMsg);
} else {
isAdd = false;
}
verfiyFail = true;
}
}
if (params.getVerifyHanlder() != null) {
ExcelVerifyHanlderResult result = params.getVerifyHanlder().verifyHandler(object);
if (!result.isSuccess()) {
if (cell == null)
cell = row.createCell(row.getLastCellNum());
cell.setCellValue((StringUtils.isNoneBlank(cell.getStringCellValue())
? cell.getStringCellValue() + "," : "") + result.getMsg());
if (object instanceof IExcelModel) {
IExcelModel model = (IExcelModel) object;
model.setErrorMsg((StringUtils.isNoneBlank(model.getErrorMsg())
? model.getErrorMsg() + "," : "") + result.getMsg());
} else {
isAdd = false;
}
verfiyFail = true;
}
}
if (cell != null)
cell.setCellStyle(errorCellStyle);
return isAdd;
}
/**
* 获取表格字段列名对应信息
* @param rows
* @param params
* @param excelCollection
* @return
*/
private Map<Integer, String> getTitleMap(Iterator<Row> rows, ImportParams params,
List<ExcelCollectionParams> excelCollection) {
Map<Integer, String> titlemap = new HashMap<Integer, String>();
Iterator<Cell> cellTitle;
String collectionName = null;
ExcelCollectionParams collectionParams = null;
Row row = null;
for (int j = 0; j < params.getHeadRows(); j++) {
row = rows.next();
if (row == null) {
continue;
}
cellTitle = row.cellIterator();
while (cellTitle.hasNext()) {
Cell cell = cellTitle.next();
String value = getKeyValue(cell);
int i = cell.getColumnIndex();
//用以支持重名导入
if (StringUtils.isNotEmpty(value)) {
if (titlemap.containsKey(i)) {
collectionName = titlemap.get(i);
collectionParams = getCollectionParams(excelCollection, collectionName);
titlemap.put(i, collectionName + "_" + value);
} else if (StringUtils.isNotEmpty(collectionName) && collectionParams
.getExcelParams().containsKey(collectionName + "_" + value)) {
titlemap.put(i, collectionName + "_" + value);
} else {
collectionName = null;
collectionParams = null;
}
if (StringUtils.isEmpty(collectionName)) {
titlemap.put(i, value);
}
}
}
}
return titlemap;
}
/**
* 获取这个名称对应的集合信息
* @param excelCollection
* @param collectionName
* @return
*/
private ExcelCollectionParams getCollectionParams(List<ExcelCollectionParams> excelCollection,
String collectionName) {
for (ExcelCollectionParams excelCollectionParams : excelCollection) {
if (collectionName.equals(excelCollectionParams.getExcelName())) {
return excelCollectionParams;
}
}
return null;
}
/**
* Excel 导入 field 字段类型 Integer,Long,Double,Date,String,Boolean
*
* @param inputstream
* @param pojoClass
* @param params
* @return
* @throws Exception
*/
public ExcelImportResult importExcelByIs(InputStream inputstream, Class<?> pojoClass,
ImportParams params) throws Exception {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug("Excel import start ,class is {}", pojoClass);
}
List<T> result = new ArrayList<T>();
Workbook book = null;
boolean isXSSFWorkbook = true;
if (!(inputstream.markSupported())) {
inputstream = new PushbackInputStream(inputstream, 8);
}
if (POIFSFileSystem.hasPOIFSHeader(inputstream)) {
book = new HSSFWorkbook(inputstream);
isXSSFWorkbook = false;
} else if (POIXMLDocument.hasOOXMLHeader(inputstream)) {
book = new XSSFWorkbook(OPCPackage.open(inputstream));
}
createErrorCellStyle(book);
Map<String, PictureData> pictures;
for (int i = params.getStartSheetIndex(); i < params.getStartSheetIndex()
+ params.getSheetNum(); i++) {
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(" start to read excel by is ,startTime is {}", new Date().getTime());
}
if (isXSSFWorkbook) {
pictures = PoiPublicUtil.getSheetPictrues07((XSSFSheet) book.getSheetAt(i),
(XSSFWorkbook) book);
} else {
pictures = PoiPublicUtil.getSheetPictrues03((HSSFSheet) book.getSheetAt(i),
(HSSFWorkbook) book);
}
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(" end to read excel by is ,endTime is {}", new Date().getTime());
}
result.addAll(importExcel(result, book.getSheetAt(i), pojoClass, params, pictures));
if (LOGGER.isDebugEnabled()) {
LOGGER.debug(" end to read excel list by pos ,endTime is {}", new Date().getTime());
}
}
if (params.isNeedSave()) {
saveThisExcel(params, pojoClass, isXSSFWorkbook, book);
}
return new ExcelImportResult(result, verfiyFail, book);
}
/**
* 检查是不是合法的模板
* @param titlemap
* @param excelParams
* @param params
* @param excelCollection
*/
private void checkIsValidTemplate(Map<Integer, String> titlemap,
Map<String, ExcelImportEntity> excelParams,
ImportParams params,
List<ExcelCollectionParams> excelCollection) {
if (params.getImportFields() != null) {
for (int i = 0, le = params.getImportFields().length; i < le; i++) {
if (!titlemap.containsValue(params.getImportFields()[i])) {
throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
}
}
} else {
Collection<ExcelImportEntity> collection = excelParams.values();
for (ExcelImportEntity excelImportEntity : collection) {
if (excelImportEntity.isImportField()
&& !titlemap.containsValue(excelImportEntity.getName())) {
LOGGER.error(excelImportEntity.getName() + "必须有,但是没找到");
throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
}
}
for (int i = 0, le = excelCollection.size(); i < le; i++) {
ExcelCollectionParams collectionparams = excelCollection.get(i);
collection = collectionparams.getExcelParams().values();
for (ExcelImportEntity excelImportEntity : collection) {
if (excelImportEntity.isImportField() && !titlemap.containsValue(
collectionparams.getExcelName() + "_" + excelImportEntity.getName())) {
throw new ExcelImportException(ExcelImportEnum.IS_NOT_A_VALID_TEMPLATE);
}
}
}
}
}
/**
* 保存字段值(获取值,校验值,追加错误信息)
*
* @param params
* @param object
* @param cell
* @param excelParams
* @param titleString
* @param row
* @throws Exception
*/
private void saveFieldValue(ImportParams params, Object object, Cell cell,
Map<String, ExcelImportEntity> excelParams, String titleString,
Row row) throws Exception {
Object value = cellValueServer.getValue(params.getDataHanlder(), object, cell, excelParams,
titleString);
if (object instanceof Map) {
if (params.getDataHanlder() != null) {
params.getDataHanlder().setMapValue((Map) object, titleString, value);
} else {
((Map) object).put(titleString, value);
}
} else {
setValues(excelParams.get(titleString), object, value);
}
}
/**
*
* @param object
* @param picId
* @param excelParams
* @param titleString
* @param pictures
* @param params
* @throws Exception
*/
private void saveImage(Object object, String picId, Map<String, ExcelImportEntity> excelParams,
String titleString, Map<String, PictureData> pictures,
ImportParams params) throws Exception {
if (pictures == null) {
return;
}
PictureData image = pictures.get(picId);
if(image == null) {
return;
}
byte[] data = image.getData();
String fileName = "pic" + Math.round(Math.random() * 100000000000L);
fileName += "." + PoiPublicUtil.getFileExtendName(data);
if (excelParams.get(titleString).getSaveType() == 1) {
String path = PoiPublicUtil
.getWebRootPath(getSaveUrl(excelParams.get(titleString), object));
File savefile = new File(path);
if (!savefile.exists()) {
savefile.mkdirs();
}
savefile = new File(path + "/" + fileName);
FileOutputStream fos = new FileOutputStream(savefile);
try {
fos.write(data);
} finally {
IOUtils.closeQuietly(fos);
}
setValues(excelParams.get(titleString), object,
getSaveUrl(excelParams.get(titleString), object) + "/" + fileName);
} else {
setValues(excelParams.get(titleString), object, data);
}
}
private void createErrorCellStyle(Workbook workbook) {
errorCellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setColor(Font.COLOR_RED);
errorCellStyle.setFont(font);
}
}