一：该例子是笔者在实际项目应用过程中，针对项目完成的一套基于poi的导入导出例子，其中一些与项目有关的代码大家直接替换成自己的需求即可。
二：笔者在项目中使用的是poi的XSSF,对应maven的pom依赖如下：
<poi.version>3.9</poi.version>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
接下来首先是jsp页面：
<script type="text/javascript">
$(document).ready(function() {
$("#btnExport").click(function(){
top.$.jBox.confirm("确认要导出工单数据吗？","系统提示",function(v,h,f){
if(v=="ok"){
$("#searchForm").attr("action","${ctx}/wxpt/workorder/wxptWorkorderInfo/export");
$("#searchForm").submit();
}
},{buttonsFocus:1});
top.$('.jbox-body .jbox-icon').css('top','55px');
});
$("#btnImportSubmit").click(function(){
$.jBox($("#importBox").html(), {title:"导入数据", buttons:{"关闭":true},
bottomText:"导入文件不能超过5M，仅允许导入“xls”或“xlsx”格式文件！"});
});
});
</script>
<script type="text/javascript" src="${ctxStatic}/js/modules/wxpt/workorder/wxptWorkorderInfo.js"></script>
</head>
<body>
<div id="importBox">
<form id="importForm" action="${ctx}/wxpt/workorder/wxptWorkorderInfo/import" method="post" enctype="multipart/form-data"
class="form-search" style="padding-left:20px;text-align:center;" onsubmit="loading('正在导入，请稍等...');"><br/>
<input id="uploadFile" name="file" type="file" style="width:330px"/><br/><br/>
<input id="btnImportSubmit" class="btn btn-primary" type="submit" value=" 导 入 "/>
<input id="btnExport" class="btn btn-primary" type="button" value="导出"/>
<a href="${ctx}/sys/user/import/template">下载模板</a>
</form>
</div>
</body>
其中导出涉及不到表单数据的提交，笔者为了操作简便，直接将查询的表单的action换成了导出的地址，在此并没有列出来，大家换成a标签也可。
然后是控制层代码：
导出：
/** *导出数据 [email protected] 2018-04-02 */ @RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:view") @RequestMapping(value = "export", method = RequestMethod.POST) public String exportToFile(HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes, WxptWorkorderInfo wxptWorkorderInfo) { try { String fileName = DateUtils.getDate("工单yyyyMMddHHmmss") + ".xlsx"; List<WxptWorkorderInfo> list = wxptWorkorderInfoService.findList(wxptWorkorderInfo); new ExportExcel("工单", WxptWorkorderInfo.class).setDataList(list).write(response, fileName).dispose(); return null; } catch (Exception e) { addMessage(redirectAttributes, "导出工单失败！失败信息：" + e.getMessage()); } return "redirect:" + Global.getAdminPath() + "/wxpt/workorder/wxptWorkorderInfo/?repage"; }
exportExcel：（一个工具类，直接复制粘贴即可，注释很明确）
/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
*/
package com.sm.modules.wxpt.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.sm.modules.sys.utils.DictUtils;
import com.sm.utils.Encodes;
import com.sm.utils.Reflections;
import com.sm.utils.excel.annotation.ExcelField;
/**
* 导出Excel文件（导出“XLSX”格式，支持大数据量导出 @see org.apache.poi.ss.SpreadsheetVersion）
* @author 冯文哲
* @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[]>() {
@Override
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.");
//
// }
}
该导出是基于bean的导出，就要用到java的反射机制，关于bean的配置是在java实体类上通过注解来实现的：
/**
* Copyright © 2012-2016 <a href="http://www.yh3m.com">smkj</a> All rights reserved.
*/
package com.sm.modules.wxpt.workorder.entity;
import java.util.Date;
import javax.validation.constraints.NotNull;
import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.NotEmpty;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.sm.common.persistence.DataEntity;
/**
* 工单表Entity
* @author 冯文哲
* @version 2018-04-02
*/
public class WxptWorkorderInfo extends DataEntity<WxptWorkorderInfo> {
private static final long serialVersionUID = 1L;
private String systemId; // 工单导入或录入时自动生成的系统编号
private String oldSystemId; // 工单导入时原厂家自带的编号
private String uname; // 用户姓名
public WxptWorkorderInfo() {
super();
}
public WxptWorkorderInfo(String id){
super(id);
}
@Length(min=0, max=64, message="工单导入时原厂家自带的编号长度必须介于 0 和 64 之间")
@com.sm.utils.excel.annotation.ExcelField(title = "服务报告", align = 2, sort = 1)
public String getOldSystemId() {
return oldSystemId;
}
public void setOldSystemId(String oldSystemId) {
this.oldSystemId = oldSystemId;
}
@Length(min=0, max=64, message="用户姓名长度必须介于 0 和 64 之间")
@NotEmpty(message="用户姓名不能为空")
@com.sm.utils.excel.annotation.ExcelField(title = "客户信息", align = 2, sort = 3)
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
}
该注解定义：
/**
* Copyright © 2012-2016 <a href="https://github.com/thinkgem/smkj">smkj</a> All rights reserved.
*/
package com.sm.common.utils.excel.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* Excel注解定义
* @author ThinkGem
* @version 2013-03-10
*/
@Target({ElementType.METHOD, ElementType.FIELD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelField {
/**
* 导出字段名（默认调用当前字段的“get”方法，如指定导出字段为对象，请填写“对象名.对象属性”，例：“area.name”、“office.name”）
*/
String value() default "";
/**
* 导出字段标题（需要添加批注请用“**”分隔，标题**批注，仅对导出模板有效）
*/
String title();
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
int type() default 0;
/**
* 导出字段对齐方式（0：自动；1：靠左；2：居中；3：靠右）
*/
int align() default 0;
/**
* 导出字段字段排序（升序）
*/
int sort() default 0;
/**
* 如果是字典类型，请设置字典的type值
*/
String dictType() default "";
/**
* 反射类型
*/
Class<?> fieldType() default Class.class;
/**
* 字段归属组（根据分组导出导入）
*/
int[] groups() default {};
}
注解的title即导出的标题。
导入： 首先创造一个map做excel标题和实体类字段映射用，即下面代码的header。
然后调用：
bindToModels方法将excel映射到实体类上，后续代码可自行处理业务逻辑。
/**
* 导入数据
*
* @param file
* @param redirectAttributes
* @return
*/
@RequiresPermissions("wxpt:workorder:wxptWorkorderInfo:edit")
@RequestMapping(value = "import", method = RequestMethod.POST)
public String importFile(MultipartFile file, RedirectAttributes redirectAttributes) {
Map<String, String> header = new HashMap<String, String>();
header.put("服务报告", "oldSystemId");
header.put("服务类别", "serviceType");
header.put("客户信息", "uname");
header.put("市", "areaCode");
header.put("地址", "uaddress");
header.put("产品信息", "brand");
header.put("附加保修文档号", "fujiabaoxiu");
header.put("维修建议", "weixiujianyi");
header.put("配件建议", "peijianjianyi");
header.put("申请备注", "notesAndInstructions");
header.put("申请时间", "serviceTime");
header.put("技术文档", "jishuwendang");
try {
int successNum = 0;
int failureNum = 0;
StringBuilder failureMsg = new StringBuilder();
ImBeanExcel export = new ImBeanExcel(header);
export.init(file);
//List<WxptWorkorderInfo> list = ei.getDataList(WxptWorkorderInfo.class);
List<WxptWorkorderInfo> list = export.bindToModels(WxptWorkorderInfo.class, true);
if (export.hasError())
{
System.out.println(export.getError().toString());
}
for (WxptWorkorderInfo workorder : list) {
String indent = randomIndentnum.getIndent();
workorder.setSystemId(indent);
for (int j = 0; j < workorder.getUname().length(); j++) {//处理导入数据
//拆分用户和电话
if(workorder.getUname().charAt(j)>=48 && workorder.getUname().charAt(j)<=57){
String uname = workorder.getUname().substring(0,j);
String uphone = workorder.getUname().substring(j,workorder.getUname().length());
workorder.setUname(uname);
workorder.setUphone(uphone.trim());
}
}
//拆分品牌，产品类别，服务，型号
String [] bra = workorder.getBrand().split("//s+");
List<WxptBrandsDict> brandsDict = wxptBrandsDictService.findList(new WxptBrandsDict());
List<WxptProductDict> productDict = wxptProductDictService.findList(new WxptProductDict());
List<WxptServiceDict> serviceDict = wxptServiceDictService.findList(new WxptServiceDict());
workorder = ImportDivision.divisionBrand(bra,workorder,brandsDict,productDict,serviceDict);
}
for (WxptWorkorderInfo workorder : list) {
try {
if ("true".equals(checkOldSystemId("", workorder.getOldSystemId()))) {
BeanValidators.validateWithException(validator, workorder);
wxptWorkorderInfoService.save(workorder);
successNum++;
} else {
failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 已存在; ");
failureNum++;
}
} catch (ConstraintViolationException ex) {
failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败：");
List<String> messageList = BeanValidators.extractPropertyAndMessageAsList(ex, ": ");
for (String message : messageList) {
failureMsg.append(message + "; ");
failureNum++;
}
} catch (Exception ex) {
failureMsg.append("<br/>工单编号 " + workorder.getOldSystemId() + " 导入失败：" + ex.getMessage());
}
}
if (failureNum > 0) {
failureMsg.insert(0, "，失败 " + failureNum + " 条工单，导入信息如下：");
}
addMessage(redirectAttributes, "已成功导入 " + successNum + " 条工单" + failureMsg);
} catch (Exception e) {
addMessage(redirectAttributes, "导入工单失败！失败信息：" + e.getMessage());
}
return "redirect:" + adminPath + "/wxpt/workorder/wxptWorkorderInfo?repage";
}
ImBeanExcel.java工具类：
package com.sm.modules.wxpt.utils.excel;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFDataFormat;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
/**
* 将excel数据装换成数据模型
*
* @author
*
*/
public class ImBeanExcel {
private static final int HEADER = 0;
private static final int START = 1;
private Logger log = Logger.getLogger(this.getClass());
private XSSFWorkbook book;
/**
* key:excel对应标题 ,value:对象属性
*/
private Map<String, String> associations;
/**
* 装换失败的数据信息，记录行数
*/
private StringBuffer error = new StringBuffer(0);
private Map<Integer, String> header;
/**
* 默认的日期格式
*/
private String date_format = "yyyy-MM-dd";
private SimpleDateFormat format;
/**
* 初始化工作簿
*
* @param file
*/
public void init(MultipartFile file) {
FileInputStream in;
try {
in = (FileInputStream) file.getInputStream();
book = new XSSFWorkbook(in);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public ImBeanExcel(Map<String, String> associations) {
this.associations = associations;
format = new SimpleDateFormat(date_format);
}
public ImBeanExcel(Map<String, String> associations, String date_format) {
this.associations = associations;
this.date_format = date_format;
format = new SimpleDateFormat(date_format);
}
/**
*
* @return true 存在错误，false 不存在错误
*/
public boolean hasError() {
return error.capacity() > 0;
}
public StringBuffer getError() {
return error;
}
/**
* 获取第一行标题栏数据
*
* @param sheet
* @return map key：标题栏列下标（0开始） value 标题栏值
*/
private void loadHeader(XSSFSheet sheet) {
this.header = new HashMap<Integer, String>();
XSSFRow row = sheet.getRow(HEADER);
int columns = row.getLastCellNum();
for (int i = 0; i < columns; i++) {
log.debug("加载标题栏:" + row.getCell(i).getStringCellValue());
String value = row.getCell(i).getStringCellValue();
if (null == value) {
throw new RuntimeException("标题栏不能为空！");
}
header.put(i, value);
}
log.debug("<<<<<<<<<<<<标题栏加载完毕>>>>>>>>>>>");
}
/**
*
* @param clazz
* @param required
* 是否每个属性都是必须的
* @return
*/
public <T> List<T> bindToModels(Class clazz, boolean required) throws Exception {
// 获取第一页
XSSFSheet sheet = this.book.getSheetAt(0);
int rowNum = sheet.getLastRowNum();// 获取行数
if (rowNum < 1) {
return new ArrayList<T>();
}
// 加载标题栏数据
this.loadHeader(sheet);
List<T> result = new ArrayList<T>();
for (int i = START; i < rowNum; i++) {
XSSFRow row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
T instance = (T) clazz.newInstance();
for (int columns = 0; columns < cellNum; columns++) {
XSSFCell cell = row.getCell(columns);
// 判断单元格的数据类型
String value = loadCellType(cell);
// 获取单元格的值
if (null == value) {
// 如果为必填的则将错误信息记录
if (required) {
this.error.append("第" + (i + 1) + "行，" + header.get(columns) + "字段，数据为空，跳过！").append("/n");
log.debug("第" + (i + 1) + "行，" + header.get(columns) + "字段，数据为空，跳过！");
continue;
}
}else if("".equals(value.trim())){
// 如果为必填的则将错误信息记录
if (required) {
this.error.append("第" + (i + 1) + "行，" + header.get(columns) + "字段，数据为空，跳过！").append("/n");
log.debug("第" + (i + 1) + "行，" + header.get(columns) + "字段，数据为空，跳过！");
continue;
}
} else {
String key = header.get(columns);
// 加载实际值
value = value.replaceAll( "////","");
this.loadValue(clazz, instance, this.associations.get(key), value);
}
}
result.add(instance);
}
log.debug("<<<<<装换完成" + (this.hasError() ? "有错误信息" : "") + "，共有对象:" + result.size() + "个" + ">>>>>>");
return result;
}
/**
* 将单元格数据转换成string类型
*
* @param cellType
* @param cell
* @return
*/
private String loadCellType(XSSFCell cell) {
String value = null;
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BOOLEAN:
value = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
// 判断当前的cell是否为Date
if (DateUtil.isCellDateFormatted(cell)) {
value = this.formateDate(cell.getDateCellValue());
} else {
value = String.valueOf((long) cell.getNumericCellValue());
}
break;
case XSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_FORMULA:
log.debug("不支持函数！");
break;
}
return value;
}
/**
* 注入属性值
*
* @param instance
* @param pro
* 属性对象
* @param value
* 属性值
*/
@SuppressWarnings("unchecked")
private <T> void loadValue(Class clazz, T instance, String pro, String value)
throws SecurityException, NoSuchMethodException, Exception {
String getMethod = this.initGetMethod(pro);
Class type = clazz.getDeclaredMethod(getMethod, null).getReturnType();
Method method = clazz.getMethod(this.initSetMethod(pro), type);
if (type == String.class) {
method.invoke(instance, value);
} else if (type == int.class || type == Integer.class) {
method.invoke(instance, Integer.parseInt(value));
} else if (type == long.class || type == Long.class) {
method.invoke(instance, Long.parseLong(value));
} else if (type == float.class || type == Float.class) {
method.invoke(instance, Float.parseFloat(value));
} else if (type == double.class || type == Double.class) {
method.invoke(instance, Double.parseDouble(value));
} else if (type == Date.class) {
method.invoke(instance, this.parseDate(value));
}
}
private Date parseDate(String value) throws ParseException {
value = value.replaceAll("/", "-");
return format.parse(value);
}
private String formateDate(Date date) {
return format.format(date);
}
public String initSetMethod(String field) {
return "set" + field.substring(0, 1).toUpperCase() + field.substring(1);
}
public String initGetMethod(String field) {
return "get" + field.substring(0, 1).toUpperCase() + field.substring(1);
}
public String getDate_format() {
return date_format;
}
public void setDate_format(String date_format) {
this.date_format = date_format;
}
}
至此一套完整流程的导入导出基本完成，可复用。
原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/11462.html