第一:导入数据
1:在get()上加上@ExcelField注解
@ExcelField(title = “工会类型”, sort = 10),根据sort大小,将excel的数据列依次按顺序set到对应的属性中
package com.thinkgem.jeesite.modules.ncgh.entity;
import com.fasterxml.jackson.annotation.JsonBackReference;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.thinkgem.jeesite.common.persistence.DataEntity;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.entity.Area;
import org.hibernate.validator.constraints.Length;
import java.util.Date;
/**
* 建会管理Entity
* @author LZY
* @version 2020-11-25
*/
public class NcUnion extends DataEntity<NcUnion> {
private static final long serialVersionUID = 1L;
private String adminId; //管理员id
private String parentId; // 父id
private String name; // 工会名称
private String beforehandName; // 预建工会名称
private String type; // 工会类型
private String belongArea; // 所属区域
private String principal; // 负责人
private String creditCode; // 社会统一信用代码
private String unitName; // 单位名称
private String unitAddress; // 单位地址
private String unitNature; // 单位性质
private String unitPic; // 单位图片
private String unitBelongIndustry; // 单位所属行业
private String unitLagal; // 单位法人
private String economicType; // 经济类型
private String staffNumber; // 职工人数
private String contactPerson; // 联系人
private String contactPhone; // 联系人手机号
private Date applyTime; // 申请时间
private Date dealTime; // 办结时间
private String operator; // 经办人
private String status; // 状态
private String adjunct; // 附件
private String approvalAdvise; // 审批意见
private String dataSourse; // 数据来源
private Date beginApplyDate;
private Date endApplyDate;
private Date beginFinishDate;
private Date endFinishDate;
private Area area;
private NcUnion parent;
private String checkStatus;
@ExcelField(title = "工会类型", sort = 10)
public String getType() {
return type;
}
//根据sort大小,将excel的数据set到对应的属性中
@ExcelField(title = "工会名称", sort = 15)
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public void setType(String type) {
this.type = type;
}
public String getAdminId() {
return adminId;
}
public void setAdminId(String adminId) {
this.adminId = adminId;
}
public Date getBeginFinishDate() {
return beginFinishDate;
}
public void setBeginFinishDate(Date beginFinishDate) {
this.beginFinishDate = beginFinishDate;
}
public Date getEndFinishDate() {
return endFinishDate;
}
public void setEndFinishDate(Date endFinishDate) {
this.endFinishDate = endFinishDate;
}
public String getCheckStatus() {
return checkStatus;
}
public void setCheckStatus(String checkStatus) {
this.checkStatus = checkStatus;
}
public NcUnion() {
super();
}
public NcUnion(String id){
super(id);
}
public Area getArea() {
return area;
}
public void setArea(Area area) {
this.area = area;
}
public String getParentId() {
return parentId;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
public String getBeforehandName() {
return beforehandName;
}
public void setBeforehandName(String beforehandName) {
this.beforehandName = beforehandName;
}
public String getBelongArea() {
return belongArea;
}
public void setBelongArea(String belongArea) {
this.belongArea = belongArea;
}
public String getPrincipal() {
return principal;
}
public void setPrincipal(String principal) {
this.principal = principal;
}
public String getCreditCode() {
return creditCode;
}
public void setCreditCode(String creditCode) {
this.creditCode = creditCode;
}
public String getUnitName() {
return unitName;
}
public void setUnitName(String unitName) {
this.unitName = unitName;
}
public String getUnitAddress() {
return unitAddress;
}
public void setUnitAddress(String unitAddress) {
this.unitAddress = unitAddress;
}
public String getUnitNature() {
return unitNature;
}
public void setUnitNature(String unitNature) {
this.unitNature = unitNature;
}
public String getUnitPic() {
return unitPic;
}
public void setUnitPic(String unitPic) {
this.unitPic = unitPic;
}
public String getUnitBelongIndustry() {
return unitBelongIndustry;
}
public void setUnitBelongIndustry(String unitBelongIndustry) {
this.unitBelongIndustry = unitBelongIndustry;
}
public String getUnitLagal() {
return unitLagal;
}
public void setUnitLagal(String unitLagal) {
this.unitLagal = unitLagal;
}
public String getEconomicType() {
return economicType;
}
public void setEconomicType(String economicType) {
this.economicType = economicType;
}
public String getStaffNumber() {
return staffNumber;
}
public void setStaffNumber(String staffNumber) {
this.staffNumber = staffNumber;
}
public String getContactPerson() {
return contactPerson;
}
public void setContactPerson(String contactPerson) {
this.contactPerson = contactPerson;
}
public String getContactPhone() {
return contactPhone;
}
public void setContactPhone(String contactPhone) {
this.contactPhone = contactPhone;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getApplyTime() {
return applyTime;
}
public void setApplyTime(Date applyTime) {
this.applyTime = applyTime;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getDealTime() {
return dealTime;
}
public void setDealTime(Date dealTime) {
this.dealTime = dealTime;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getAdjunct() {
return adjunct;
}
public void setAdjunct(String adjunct) {
this.adjunct = adjunct;
}
public String getApprovalAdvise() {
return approvalAdvise;
}
public void setApprovalAdvise(String approvalAdvise) {
this.approvalAdvise = approvalAdvise;
}
public String getDataSourse() {
return dataSourse;
}
public void setDataSourse(String dataSourse) {
this.dataSourse = dataSourse;
}
public Date getBeginApplyDate() {
return beginApplyDate;
}
public void setBeginApplyDate(Date beginApplyDate) {
this.beginApplyDate = beginApplyDate;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getEndApplyDate() {
return endApplyDate;
}
public void setEndApplyDate(Date endApplyDate) {
this.endApplyDate = endApplyDate;
}
public NcUnion getParent() {
return parent;
}
public void setParent(NcUnion parent) {
this.parent = parent;
}
}
2:导入Excel工具类
/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package com.thinkgem.jeesite.common.utils.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;
import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
/**
* 导入Excel文件(支持“XLS”和“XLSX”格式)
* @author ThinkGem
* @version 2013-03-10
*/
public class ImportExcel {
private static Logger log = LoggerFactory.getLogger(ImportExcel.class);
/**
* 工作薄对象
*/
private Workbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 标题行号
*/
private int headerNum;
/**
* 构造函数
* @param path 导入文件,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum);
}
/**
* 构造函数
* @param path 导入文件对象,读取第一个工作表
* @param headerNum 标题行号,数据行号=标题行号+1
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum)
throws InvalidFormatException, IOException {
this(file, headerNum, 0);
}
/**
* 构造函数
* @param path 导入文件
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(new File(fileName), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(File file, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
}
/**
* 构造函数
* @param file 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
}
/**
* 构造函数
* @param path 导入文件对象
* @param headerNum 标题行号,数据行号=标题行号+1
* @param sheetIndex 工作表编号
* @throws InvalidFormatException
* @throws IOException
*/
public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex)
throws InvalidFormatException, IOException {
if (StringUtils.isBlank(fileName)){
throw new RuntimeException("导入文档为空!");
}else if(fileName.toLowerCase().endsWith("xls")){
this.wb = new HSSFWorkbook(is);
}else if(fileName.toLowerCase().endsWith("xlsx")){
this.wb = new XSSFWorkbook(is);
}else{
throw new RuntimeException("文档格式不正确!");
}
if (this.wb.getNumberOfSheets()<sheetIndex){
throw new RuntimeException("文档中没有工作表!");
}
this.sheet = this.wb.getSheetAt(sheetIndex);
this.headerNum = headerNum;
log.debug("Initialize success.");
}
/**
* 获取行对象
* @param rownum
* @return
*/
public Row getRow(int rownum){
return this.sheet.getRow(rownum);
}
/**
* 获取数据行号
* @return
*/
public int getDataRowNum(){
return headerNum+1;
}
/**
* 获取最后一个数据行号
* @return
*/
public int getLastDataRowNum(){
return this.sheet.getLastRowNum()+headerNum;
}
/**
* 获取最后一个列号
* @return
*/
public int getLastCellNum(){
return this.getRow(headerNum).getLastCellNum();
}
/**
* 获取单元格值
* @param row 获取的行
* @param column 获取单元格列号
* @return 单元格值
*/
public Object getCellValue(Row row, int column){
Object val = "";
try{
Cell cell = row.getCell(column);
if (cell != null){
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
val = cell.getNumericCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_STRING){
val = cell.getStringCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){
val = cell.getCellFormula();
}else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
val = cell.getBooleanCellValue();
}else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){
val = cell.getErrorCellValue();
}
}
}catch (Exception e) {
return val;
}
return val;
}
/**
* 获取导入数据列表
* @param cls 导入对象类型
* @param groups 导入分组
*/
public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
List<Object[]> annotationList = Lists.newArrayList();
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs){
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==2)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms){
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==2)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
//log.debug("Import column count:"+annotationList.size());
// Get excel data
List<E> dataList = Lists.newArrayList();
for (int i = this.getDataRowNum(); i < this.getLastDataRowNum(); i++) {
E e = (E)cls.newInstance();
int column = 0;
Row row = this.getRow(i);
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
Object val = this.getCellValue(row, column++);
if (val != null){
ExcelField ef = (ExcelField)os[0];
// If is dict type, get dict value
if (StringUtils.isNotBlank(ef.dictType())){
val = DictUtils.getDictValue(val.toString(), ef.dictType(), "");
//log.debug("Dictionary type value: ["+i+","+colunm+"] " + val);
}
// Get param type and type cast
Class<?> valType = Class.class;
if (os[1] instanceof Field){
valType = ((Field)os[1]).getType();
}else if (os[1] instanceof Method){
Method method = ((Method)os[1]);
if ("get".equals(method.getName().substring(0, 3))){
valType = method.getReturnType();
}else if("set".equals(method.getName().substring(0, 3))){
valType = ((Method)os[1]).getParameterTypes()[0];
}
}
//log.debug("Import value type: ["+i+","+column+"] " + valType);
try {
if (valType == String.class){
String s = String.valueOf(val.toString());
if(StringUtils.endsWith(s, ".0")){
val = StringUtils.substringBefore(s, ".0");
}else{
val = String.valueOf(val.toString());
}
}else if (valType == Integer.class){
val = Double.valueOf(val.toString()).intValue();
}else if (valType == Long.class){
val = Double.valueOf(val.toString()).longValue();
}else if (valType == Double.class){
val = Double.valueOf(val.toString());
}else if (valType == Float.class){
val = Float.valueOf(val.toString());
}else if (valType == Date.class){
val = DateUtil.getJavaDate((Double)val);
}else{
if (ef.fieldType() != Class.class){
val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
}else{
val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
}
}
} catch (Exception ex) {
log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
val = null;
}
// set entity value
if (os[1] instanceof Field){
Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
}else if (os[1] instanceof Method){
String mthodName = ((Method)os[1]).getName();
if ("get".equals(mthodName.substring(0, 3))){
mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
}
Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
}
}
sb.append(val+", ");
}
dataList.add(e);
log.debug("Read success: ["+i+"] "+sb.toString());
}
return dataList;
}
// /**
// * 导入测试
// */
// public static void main(String[] args) throws Throwable {
//
// ImportExcel ei = new ImportExcel("target/export.xlsx", 1);
//
// for (int i = ei.getDataRowNum(); i < ei.getLastDataRowNum(); i++) {
// Row row = ei.getRow(i);
// for (int j = 0; j < ei.getLastCellNum(); j++) {
// Object val = ei.getCellValue(row, j);
// System.out.print(val+", ");
// }
// System.out.print("\n");
// }
//
// }
}
3:调用导入接口
/**
* 导入数据
*/
@RequiresPermissions("ncgh:ncUnionInformation:edit")
@RequestMapping(value = "import", method = RequestMethod.POST)
public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
if (Global.isDemoMode()) {
addMessage(redirectAttributes, "演示模式,不允许操作!");
return "redirect:" + adminPath + "/ncgh/ncUnionInformation/?repage";
}
try {
int successNum = 0;
int failureNum = 0;
StringBuilder failureMsg = new StringBuilder();
//参数1代表第一行标题,也就是说模板要有标题,数据是从第三行开始添加的
ImportExcel ei = new ImportExcel(file, 1, 0);
List<NcUnion> list = ei.getDataList(NcUnion.class);
//需要判重,根据工会名称
//查询所有工会
/* List<NcUnion> ncUnionAllList = ncUnionInformationService.findAll();
for(int i=0; i<list.size(); i++){
if(ncUnionAllList.contains(list.get(i).getName())){
hasImported++;
list.remove(i);
}
}*/
for (NcUnion ncUnion : list) {
try {// 值转换
ncUnion.setParentId("0");
ncUnion.setDataSourse("后台录入");
ncUnion.setBelongArea("南昌县总工会");
//成立时间
ncUnion.setDealTime(new Date());
BeanValidators.validateWithException(validator, ncUnion);
ncUnionInformationService.save(ncUnion);
successNum++;
} catch (ConstraintViolationException ex) {
// failureMsg.append("<br/>登录名 " + ncUnionMember.getName() + " 导入失败:");
List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
for (String message : messageList) {
failureMsg.append(message + "; ");
failureNum++;
}
} catch (Exception ex) {
// failureMsg.append("<br/>登录名 " + ncUnionMember.getName() + " 导入失败:" + ex.getMessage());
}
}
if (failureNum > 0) {
failureMsg.insert(0, ",失败 " + failureNum + " 条工会,导入信息如下:");
}
addMessage(redirectAttributes, "已成功导入 " + successNum + " 条工会" + failureMsg);
} catch (Exception e) {
addMessage(redirectAttributes, "导入工会失败!失败信息:" + e.getMessage());
}
return "redirect:" + adminPath + "/ncgh/ncUnionInformation/?repage";
}
4:前端页面
<form id="importForm" action="${ctx}/ncgh/ncUnionInformation/import" method="post"
enctype="multipart/form-data" onsubmit="loading('正在导入,请稍等...');">
<input id="uploadFile" name="file" type="file"/>
<input id="btnImportSubmit" class="btn btn-primary" type="button" value="导入" onclick="upload()"/>
</form>
5:Excel模板
模板必须要有标题,数据是从第三行开始添加的
二、下载模板
导出模板也需要加上注解@ExcelField(title = “工会名称”,align = 2, sort = 15),title为导入模板的列。
**注意:**不需要在项目中加入模板,这个导出模板是根据实体类生成的文件。
1:导出模板图片
2:导出(下载)模板工具类
ExportExcel.java
/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/jeesite">JeeSite</a> All rights reserved.
*/
package com.thinkgem.jeesite.common.utils.excel;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.google.common.collect.Lists;
import com.thinkgem.jeesite.common.utils.Encodes;
import com.thinkgem.jeesite.common.utils.Reflections;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.utils.DictUtils;
/**
* 导出Excel文件(导出“XLSX”格式,支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion)
* @author ThinkGem
* @version 2013-04-21
*/
public class ExportExcel {
private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
/**
* 工作薄对象
*/
private SXSSFWorkbook wb;
/**
* 工作表对象
*/
private Sheet sheet;
/**
* 样式列表
*/
private Map<String, CellStyle> styles;
/**
* 当前行号
*/
private int rownum;
/**
* 注解列表(Object[]{ ExcelField, Field/Method })
*/
List<Object[]> annotationList = Lists.newArrayList();
/**
* 构造函数
* @param title 表格标题,传“空值”,表示无标题
* @param cls 实体对象,通过annotation.ExportField获取标题
*/
public ExportExcel(String title, Class<?> cls){
this(title, cls, 1);
}
/**
* 构造函数
* @param title 表格标题,传“空值”,表示无标题
* @param cls 实体对象,通过annotation.ExportField获取标题
* @param type 导出类型(1:导出数据;2:导出模板)
* @param groups 导入分组
*/
public ExportExcel(String title, Class<?> cls, int type, int... groups){
// Get annotation field
Field[] fs = cls.getDeclaredFields();
for (Field f : fs){
ExcelField ef = f.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==type)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, f});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, f});
}
}
}
// Get annotation method
Method[] ms = cls.getDeclaredMethods();
for (Method m : ms){
ExcelField ef = m.getAnnotation(ExcelField.class);
if (ef != null && (ef.type()==0 || ef.type()==type)){
if (groups!=null && groups.length>0){
boolean inGroup = false;
for (int g : groups){
if (inGroup){
break;
}
for (int efg : ef.groups()){
if (g == efg){
inGroup = true;
annotationList.add(new Object[]{ef, m});
break;
}
}
}
}else{
annotationList.add(new Object[]{ef, m});
}
}
}
// Field sorting
Collections.sort(annotationList, new Comparator<Object[]>() {
public int compare(Object[] o1, Object[] o2) {
return new Integer(((ExcelField)o1[0]).sort()).compareTo(
new Integer(((ExcelField)o2[0]).sort()));
};
});
// Initialize
List<String> headerList = Lists.newArrayList();
for (Object[] os : annotationList){
String t = ((ExcelField)os[0]).title();
// 如果是导出,则去掉注释
if (type==1){
String[] ss = StringUtils.split(t, "**", 2);
if (ss.length==2){
t = ss[0];
}
}
headerList.add(t);
}
initialize(title, headerList);
}
/**
* 构造函数
* @param title 表格标题,传“空值”,表示无标题
* @param headers 表头数组
*/
public ExportExcel(String title, String[] headers) {
initialize(title, Lists.newArrayList(headers));
}
/**
* 构造函数
* @param title 表格标题,传“空值”,表示无标题
* @param headerList 表头列表
*/
public ExportExcel(String title, List<String> headerList) {
initialize(title, headerList);
}
/**
* 初始化函数
* @param title 表格标题,传“空值”,表示无标题
* @param headerList 表头列表
*/
private void initialize(String title, List<String> headerList) {
this.wb = new SXSSFWorkbook(500);
this.sheet = wb.createSheet("Export");
this.styles = createStyles(wb);
// Create title
if (StringUtils.isNotBlank(title)){
Row titleRow = sheet.createRow(rownum++);
titleRow.setHeightInPoints(30);
Cell titleCell = titleRow.createCell(0);
titleCell.setCellStyle(styles.get("title"));
titleCell.setCellValue(title);
sheet.addMergedRegion(new CellRangeAddress(titleRow.getRowNum(),
titleRow.getRowNum(), titleRow.getRowNum(), headerList.size()-1));
}
// Create header
if (headerList == null){
throw new RuntimeException("headerList not null!");
}
Row headerRow = sheet.createRow(rownum++);
headerRow.setHeightInPoints(16);
for (int i = 0; i < headerList.size(); i++) {
Cell cell = headerRow.createCell(i);
cell.setCellStyle(styles.get("header"));
String[] ss = StringUtils.split(headerList.get(i), "**", 2);
if (ss.length==2){
cell.setCellValue(ss[0]);
Comment comment = this.sheet.createDrawingPatriarch().createCellComment(
new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6));
comment.setString(new XSSFRichTextString(ss[1]));
cell.setCellComment(comment);
}else{
cell.setCellValue(headerList.get(i));
}
sheet.autoSizeColumn(i);
}
for (int i = 0; i < headerList.size(); i++) {
int colWidth = sheet.getColumnWidth(i)*2;
sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
}
log.debug("Initialize success.");
}
/**
* 创建表格样式
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/**
* 添加一行
* @return 行对象
*/
public Row addRow(){
return sheet.createRow(rownum++);
}
/**
* 添加一个单元格
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val){
return this.addCell(row, column, val, 0, Class.class);
}
/**
* 添加一个单元格
* @param row 添加的行
* @param column 添加列号
* @param val 添加值
* @param align 对齐方式(1:靠左;2:居中;3:靠右)
* @return 单元格对象
*/
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType){
Cell cell = row.createCell(column);
String cellFormatString = "@";
try {
if(val == null){
cell.setCellValue("");
}else if(fieldType != Class.class){
cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke(null, val));
}else{
if(val instanceof String) {
cell.setCellValue((String) val);
}else if(val instanceof Integer) {
cell.setCellValue((Integer) val);
cellFormatString = "0";
}else if(val instanceof Long) {
cell.setCellValue((Long) val);
cellFormatString = "0";
}else if(val instanceof Double) {
cell.setCellValue((Double) val);
cellFormatString = "0.00";
}else if(val instanceof Float) {
cell.setCellValue((Float) val);
cellFormatString = "0.00";
}else if(val instanceof Date) {
cell.setCellValue((Date) val);
cellFormatString = "yyyy-MM-dd HH:mm";
}else {
cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
"fieldtype."+val.getClass().getSimpleName()+"Type")).getMethod("setValue", Object.class).invoke(null, val));
}
}
if (val != null){
CellStyle style = styles.get("data_column_"+column);
if (style == null){
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"+(align>=1&&align<=3?align:"")));
style.setDataFormat(wb.createDataFormat().getFormat(cellFormatString));
styles.put("data_column_" + column, style);
}
cell.setCellStyle(style);
}
} catch (Exception ex) {
log.info("Set cell value ["+row.getRowNum()+","+column+"] error: " + ex.toString());
cell.setCellValue(val.toString());
}
return cell;
}
/**
* 添加数据(通过annotation.ExportField添加数据)
* @return list 数据列表
*/
public <E> ExportExcel setDataList(List<E> list){
for (E e : list){
int colunm = 0;
Row row = this.addRow();
StringBuilder sb = new StringBuilder();
for (Object[] os : annotationList){
ExcelField ef = (ExcelField)os[0];
Object val = null;
// Get entity value
try{
if (StringUtils.isNotBlank(ef.value())){
val = Reflections.invokeGetter(e, ef.value());
}else{
if (os[1] instanceof Field){
val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
}else if (os[1] instanceof Method){
val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[] {}, new Object[] {});
}
}
// If is dict, get dict label
if (StringUtils.isNotBlank(ef.dictType())){
val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), "");
}
}catch(Exception ex) {
// Failure to ignore
log.info(ex.toString());
val = "";
}
this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
sb.append(val + ", ");
}
log.debug("Write success: ["+row.getRowNum()+"] "+sb.toString());
}
return this;
}
/**
* 输出数据流
* @param os 输出数据流
*/
public ExportExcel write(OutputStream os) throws IOException{
wb.write(os);
return this;
}
/**
* 输出到客户端
* @param fileName 输出文件名
*/
public ExportExcel write(HttpServletResponse response, String fileName) throws IOException{
response.reset();
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
write(response.getOutputStream());
return this;
}
/**
* 输出到文件
* @param fileName 输出文件名
*/
public ExportExcel writeFile(String name) throws FileNotFoundException, IOException{
FileOutputStream os = new FileOutputStream(name);
this.write(os);
return this;
}
/**
* 清理临时文件
*/
public ExportExcel dispose(){
wb.dispose();
return this;
}
// /**
// * 导出测试
// */
// public static void main(String[] args) throws Throwable {
//
// List<String> headerList = Lists.newArrayList();
// for (int i = 1; i <= 10; i++) {
// headerList.add("表头"+i);
// }
//
// List<String> dataRowList = Lists.newArrayList();
// for (int i = 1; i <= headerList.size(); i++) {
// dataRowList.add("数据"+i);
// }
//
// List<List<String>> dataList = Lists.newArrayList();
// for (int i = 1; i <=1000000; i++) {
// dataList.add(dataRowList);
// }
//
// ExportExcel ee = new ExportExcel("表格标题", headerList);
//
// for (int i = 0; i < dataList.size(); i++) {
// Row row = ee.addRow();
// for (int j = 0; j < dataList.get(i).size(); j++) {
// ee.addCell(row, j, dataList.get(i).get(j));
// }
// }
//
// ee.writeFile("target/export.xlsx");
//
// ee.dispose();
//
// log.debug("Export success.");
//
// }
}
第一:实体类
package com.thinkgem.jeesite.modules.ncgh.entity;
import com.fasterxml.jackson.annotation.JsonBackReference;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.thinkgem.jeesite.common.persistence.DataEntity;
import com.thinkgem.jeesite.common.utils.StringUtils;
import com.thinkgem.jeesite.common.utils.excel.annotation.ExcelField;
import com.thinkgem.jeesite.modules.sys.entity.Area;
import org.hibernate.validator.constraints.Length;
import java.util.Date;
/**
* 建会管理Entity
* @author LZY
* @version 2020-11-25
*/
public class NcUnion extends DataEntity<NcUnion> {
private static final long serialVersionUID = 1L;
private String adminId; //管理员id
private String parentId; // 父id
private String name; // 工会名称
private String beforehandName; // 预建工会名称
private String type; // 工会类型
private String belongArea; // 所属区域
private String principal; // 负责人
private String creditCode; // 社会统一信用代码
private String unitName; // 单位名称
private String unitAddress; // 单位地址
private String unitNature; // 单位性质
private String unitPic; // 单位图片
private String unitBelongIndustry; // 单位所属行业
private String unitLagal; // 单位法人
private String economicType; // 经济类型
private String staffNumber; // 职工人数
private String contactPerson; // 联系人
private String contactPhone; // 联系人手机号
private Date applyTime; // 申请时间
private Date dealTime; // 办结时间
private String operator; // 经办人
private String status; // 状态
private String adjunct; // 附件
private String approvalAdvise; // 审批意见
private String dataSourse; // 数据来源
private Date beginApplyDate;
private Date endApplyDate;
private Date beginFinishDate;
private Date endFinishDate;
private Area area;
private NcUnion parent;
private String checkStatus;
public String getAdminId() {
return adminId;
}
public void setAdminId(String adminId) {
this.adminId = adminId;
}
public Date getBeginFinishDate() {
return beginFinishDate;
}
public void setBeginFinishDate(Date beginFinishDate) {
this.beginFinishDate = beginFinishDate;
}
public Date getEndFinishDate() {
return endFinishDate;
}
public void setEndFinishDate(Date endFinishDate) {
this.endFinishDate = endFinishDate;
}
public String getCheckStatus() {
return checkStatus;
}
public void setCheckStatus(String checkStatus) {
this.checkStatus = checkStatus;
}
public NcUnion() {
super();
}
public NcUnion(String id){
super(id);
}
public Area getArea() {
return area;
}
public void setArea(Area area) {
this.area = area;
}
public String getParentId() {
return parentId;
}
public void setParentId(String parentId) {
this.parentId = parentId;
}
//根据sort大小,将excel的数据set到对应的属性中
@ExcelField(title = "工会名称",align = 2, sort = 15)
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getBeforehandName() {
return beforehandName;
}
public void setBeforehandName(String beforehandName) {
this.beforehandName = beforehandName;
}
@ExcelField(title = "工会类型", align = 2,sort = 10)
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getBelongArea() {
return belongArea;
}
public void setBelongArea(String belongArea) {
this.belongArea = belongArea;
}
public String getPrincipal() {
return principal;
}
public void setPrincipal(String principal) {
this.principal = principal;
}
public String getCreditCode() {
return creditCode;
}
public void setCreditCode(String creditCode) {
this.creditCode = creditCode;
}
public String getUnitName() {
return unitName;
}
public void setUnitName(String unitName) {
this.unitName = unitName;
}
public String getUnitAddress() {
return unitAddress;
}
public void setUnitAddress(String unitAddress) {
this.unitAddress = unitAddress;
}
public String getUnitNature() {
return unitNature;
}
public void setUnitNature(String unitNature) {
this.unitNature = unitNature;
}
public String getUnitPic() {
return unitPic;
}
public void setUnitPic(String unitPic) {
this.unitPic = unitPic;
}
public String getUnitBelongIndustry() {
return unitBelongIndustry;
}
public void setUnitBelongIndustry(String unitBelongIndustry) {
this.unitBelongIndustry = unitBelongIndustry;
}
public String getUnitLagal() {
return unitLagal;
}
public void setUnitLagal(String unitLagal) {
this.unitLagal = unitLagal;
}
public String getEconomicType() {
return economicType;
}
public void setEconomicType(String economicType) {
this.economicType = economicType;
}
public String getStaffNumber() {
return staffNumber;
}
public void setStaffNumber(String staffNumber) {
this.staffNumber = staffNumber;
}
public String getContactPerson() {
return contactPerson;
}
public void setContactPerson(String contactPerson) {
this.contactPerson = contactPerson;
}
public String getContactPhone() {
return contactPhone;
}
public void setContactPhone(String contactPhone) {
this.contactPhone = contactPhone;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getApplyTime() {
return applyTime;
}
public void setApplyTime(Date applyTime) {
this.applyTime = applyTime;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getDealTime() {
return dealTime;
}
public void setDealTime(Date dealTime) {
this.dealTime = dealTime;
}
public String getOperator() {
return operator;
}
public void setOperator(String operator) {
this.operator = operator;
}
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
public String getAdjunct() {
return adjunct;
}
public void setAdjunct(String adjunct) {
this.adjunct = adjunct;
}
public String getApprovalAdvise() {
return approvalAdvise;
}
public void setApprovalAdvise(String approvalAdvise) {
this.approvalAdvise = approvalAdvise;
}
public String getDataSourse() {
return dataSourse;
}
public void setDataSourse(String dataSourse) {
this.dataSourse = dataSourse;
}
public Date getBeginApplyDate() {
return beginApplyDate;
}
public void setBeginApplyDate(Date beginApplyDate) {
this.beginApplyDate = beginApplyDate;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getEndApplyDate() {
return endApplyDate;
}
public void setEndApplyDate(Date endApplyDate) {
this.endApplyDate = endApplyDate;
}
public NcUnion getParent() {
return parent;
}
public void setParent(NcUnion parent) {
this.parent = parent;
}
}
3:前端页面
<a href="${ctx}/ncgh/ncUnionInformation/import/template">下载模板</a>
4:下载接口
注意,代码中的NcUnion.class需要写对应的实体类
/**
* 下载导入工会数据模板
* @param response
* @param redirectAttributes
* @return
*/
@RequiresPermissions("ncgh:ncUnionInformation:view")
@RequestMapping(value = "import/template")
public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = "工会数据导入模板.xlsx";
List<NcUnion> list = Lists.newArrayList();
NcUnion ncUnion = new NcUnion();
//在导出模板加一条模板数据
ncUnion.setType("机关、乡镇工会");
ncUnion.setName("南昌县向塘工会联合会");
list.add(ncUnion);
new ExportExcel("工会数据", NcUnion.class, 2).setDataList(list).write(response, fileName).dispose();
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导入模板下载失败!失败信息:"+e.getMessage());
}
return "redirect:" + adminPath + "/ncgh/ncUnionInformation/list?repage";
}
Date getBeginApplyDate() {
return beginApplyDate;
}
public void setBeginApplyDate(Date beginApplyDate) {
this.beginApplyDate = beginApplyDate;
}
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
public Date getEndApplyDate() {
return endApplyDate;
}
public void setEndApplyDate(Date endApplyDate) {
this.endApplyDate = endApplyDate;
}
public NcUnion getParent() {
return parent;
}
public void setParent(NcUnion parent) {
this.parent = parent;
}
}
## 3:前端页面
```html
<a href="${ctx}/ncgh/ncUnionInformation/import/template">下载模板</a>
4:下载接口
注意,代码中的NcUnion.class需要写对应的实体类
/**
* 下载导入工会数据模板
* @param response
* @param redirectAttributes
* @return
*/
@RequiresPermissions("ncgh:ncUnionInformation:view")
@RequestMapping(value = "import/template")
public String importFileTemplate(HttpServletResponse response, RedirectAttributes redirectAttributes) {
try {
String fileName = "工会数据导入模板.xlsx";
List<NcUnion> list = Lists.newArrayList();
NcUnion ncUnion = new NcUnion();
//在导出模板加一条模板数据
ncUnion.setType("机关、乡镇工会");
ncUnion.setName("南昌县向塘工会联合会");
list.add(ncUnion);
new ExportExcel("工会数据", NcUnion.class, 2).setDataList(list).write(response, fileName).dispose();
return null;
} catch (Exception e) {
addMessage(redirectAttributes, "导入模板下载失败!失败信息:"+e.getMessage());
}
return "redirect:" + adminPath + "/ncgh/ncUnionInformation/list?repage";
}