基於springmvc的easypoi簡單使用
基於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);
}
}
相關文章
- easypoi 讀取 Excel 簡單應用Excel
- SpringMVC 簡單SpringMVC
- 簡單的整合 shiro + SpringMVC 例子SpringMVC
- 關於JSON的簡單使用JSON
- 基於Opencv的簡單影像處理OpenCV
- 關於GJSON包的簡單使用JSON
- 基於SpringMVC的上傳圖片SpringMVC
- 基於websocket的簡單廣播系統Web
- 基於SSM框架的簡單問答社群SSM框架
- 基於mpvue的簡單彈窗元件mptoastVue元件AST
- 用SpringMVC來簡單的操作Excel檔案SpringMVCExcel
- SpringMVC表單驗證器的使用SpringMVC
- iOS 基於FMDB簡單封裝iOS封裝
- 基於JS簡單甘特圖JS
- springmvc簡單學習（一）-----入門SpringMVC
- SpringMVC入門就這麼簡單SpringMVC
- RabbitMQ 安裝及簡單使用（基於 docker｜白小純版）MQDocker
- 基於Python的簡單天氣爬蟲程式Python爬蟲
- 基於 Prometheus 的監控神器，簡單靈活！Prometheus
- 關於MongoDB的簡單理解（一）--基礎篇MongoDB
- 關於 RabbitMQ 的安裝與簡單使用MQ
- springMVC簡單demo集合（持續更新中……）SpringMVC
- Docker簡單使用、常用基礎命令Docker
- 基於Promise實現對Ajax的簡單封裝Promise封裝
- 基於 flask 結合 Redis 的簡單聊天室FlaskRedis
- 基於Vue.js的簡單的svg進度條Vue.jsSVG
- 一個簡單的基於Debian的開發環境。開發環境
- 基於滴滴雲的螞蟻蛋運維助手的安裝和簡單使用(一)運維
- Unity基於NGUI的簡單並可直接使用的虛擬搖桿實現（一）UnityNGUI
- redis常用demo收集(二)——基於redis的簡單使用者協同推薦Redis
- 關於shiro+springMVC整合使用的問題SpringMVC
- 基於 PhpSpreadsheet 簡單 Excel 匯入匯出PHPExcel
- 胖哥學SpringMVC:簡單跳轉專題SpringMVC
- SpringMVC學習筆記之---簡單入門SpringMVC筆記
- 對一條基於分割槽的簡單SQL的優化SQL優化
- 寫了一個基於 API 的簡單圖床 AUXPIAPI圖床UX
- 基於websocket與nodejs-websocket的簡單聊天室WebNodeJS
- 基於jsp和servlet簡單的java web開發（idea）JSServletJavaWebIdea
- 基於C#的簡單登入窗體設計C#