Spring+Struts2+Hibernate+Apache POI 实现的Excel2007导入导出功能代码分享

<strong></strong>

       由于工作需要，负责做了两个Excel的导入导出功能，由于发现之前的代码繁琐效率低下，所以自己根据网上的案例实现了一个比较简单的导入导出方法，分享出来。希望能听取到其他开发人员的意见，继续改进。

一.编写Excel中每行对应的实体类，本文案例实体类如下：

package com.ultrapower.eoms.ultrabpp.runtime.custom.model;import java.io.Serializable;import javax.persistence.Entity;import javax.persistence.GeneratedValue;import javax.persistence.Id;import javax.persistence.Table;import org.hibernate.annotations.GenericGenerator;import com.ultrapower.eoms.ultrabpp.runtime.custom.annotation.excel;@Entity@Table(name = "ITOM_RES_AIS")/** * 到货入库实体类 * @author liyao * */public class ItomResAisModel implements Serializable {/** *  */private static final long serialVersionUID = -6723406945323051088L;private String keyid;private String bppid;private String restype;private String manufacturers;private String modell;private String resnum;private String reflag;private String isflag;private String machine;private String devservi;private String devsrvcontract;private String strstarttime;private String strendtime;private String momerysize;private String netband;private String hdinfor;private String itlwh;private String erowid;private String supplierid;public ItomResAisModel() {super();// TODO Auto-generated constructor stub}@Id@GeneratedValue(generator="system_uuid")@GenericGenerator(name="system_uuid",strategy="uuid")public String getKeyid() {return keyid;}public void setKeyid(String keyid) {this.keyid = keyid;}public String getBppid() {return bppid;}public void setBppid(String bppid) {this.bppid = bppid;}    //资源类型@excel(id="restype",comment="资源类型",validator="notnull",colnum=0)public String getRestype() {return restype;}public void setRestype(String restype) {this.restype = restype;}//生产厂商@excel(id="manufacturers",comment="厂商",validator="notnull",colnum=1)public String getManufacturers() {return manufacturers;}public void setManufacturers(String manufacturers) {this.manufacturers = manufacturers;}//型号@excel(id="modell",comment="型号",validator="notnull",colnum=2)public String getModell() {return modell;}public void setModell(String modell) {this.modell = modell;}//资源数量@excel(id="resnum",comment="资源数量",validator="isnum",colnum=3)public String getResnum() {return resnum;}public void setResnum(String resnum) {this.resnum = resnum;}public String getReflag() {return reflag;}public void setReflag(String reflag) {this.reflag = reflag;}public String getIsflag() {return isflag;}public void setIsflag(String isflag) {this.isflag = isflag;}public String getMachine() {return machine;}public void setMachine(String machine) {this.machine = machine;}public String getDevservi() {return devservi;}public void setDevservi(String devservi) {this.devservi = devservi;}public String getDevsrvcontract() {return devsrvcontract;}public void setDevsrvcontract(String devsrvcontract) {this.devsrvcontract = devsrvcontract;}public String getStrstarttime() {return strstarttime;}public void setStrstarttime(String strstarttime) {this.strstarttime = strstarttime;}public String getStrendtime() {return strendtime;}public void setStrendtime(String strendtime) {this.strendtime = strendtime;}@excel(id="momerysize",comment="内存大小",validator="",colnum=5)public String getMomerysize() {return momerysize;}public void setMomerysize(String momerysize) {this.momerysize = momerysize;}    @excel(id="netband",comment="网卡带宽",validator="",colnum=6)public String getNetband() {return netband;}public void setNetband(String netband) {this.netband = netband;}@excel(id="hdinfor",comment="硬盘信息",validator="",colnum=7)public String getHdinfor() {return hdinfor;}public void setHdinfor(String hdinfor) {this.hdinfor = hdinfor;}@excel(id="itlwh",comment="规格",validator="ismeet",colnum=8)public String getItlwh() {return itlwh;}public void setItlwh(String itlwh) {this.itlwh = itlwh;}public String getErowid() {return erowid;}public void setErowid(String erowid) {this.erowid = erowid;}        @Overridepublic int hashCode() {final int prime = 31;int result = 1;result = prime * result + ((hdinfor == null) ? 0 : hdinfor.hashCode());result = prime * result + ((itlwh == null) ? 0 : itlwh.hashCode());result = prime * result+ ((manufacturers == null) ? 0 : manufacturers.hashCode());result = prime * result + ((modell == null) ? 0 : modell.hashCode());result = prime * result+ ((momerysize == null) ? 0 : momerysize.hashCode());result = prime * result + ((netband == null) ? 0 : netband.hashCode());result = prime * result + ((resnum == null) ? 0 : resnum.hashCode());result = prime * result + ((restype == null) ? 0 : restype.hashCode());return result;}@Overridepublic boolean equals(Object obj) {if (this == obj)return true;if (obj == null)return false;if (getClass() != obj.getClass())return false;ItomResAisModel other = (ItomResAisModel) obj;if (hdinfor == null) {if (other.hdinfor != null)return false;} else if (!hdinfor.equals(other.hdinfor))return false;if (itlwh == null) {if (other.itlwh != null)return false;} else if (!itlwh.equals(other.itlwh))return false;if (manufacturers == null) {if (other.manufacturers != null)return false;} else if (!manufacturers.equals(other.manufacturers))return false;if (modell == null) {if (other.modell != null)return false;} else if (!modell.equals(other.modell))return false;if (momerysize == null) {if (other.momerysize != null)return false;} else if (!momerysize.equals(other.momerysize))return false;if (netband == null) {if (other.netband != null)return false;} else if (!netband.equals(other.netband))return false;if (resnum == null) {if (other.resnum != null)return false;} else if (!resnum.equals(other.resnum))return false;if (restype == null) {if (other.restype != null)return false;} else if (!restype.equals(other.restype))return false;return true;}@excel(id="supplierid",comment="供货商",validator="",colnum=4)public String getSupplierid() {return supplierid;}public void setSupplierid(String supplierid) {this.supplierid = supplierid;}@Overridepublic String toString() {return  bppid + " " + restype + " " + manufacturers + " "+ modell + " " + resnum + " " + reflag + " " + isflag + " "+ machine + " " + devservi + " " + devsrvcontract + " "+ strstarttime + " " + strendtime + " " + momerysize + " "+ netband + " " + hdinfor + " " + itlwh;}}

可以见到在某些字段上我加了一个excel的注解，这个注解是我自定义的，代码如下，目的是维护一些导入时需要的字段信息和验证规则，注解类代码如下：

package com.ultrapower.eoms.ultrabpp.runtime.custom.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Documented@Retention(RetentionPolicy.RUNTIME)@Target(value={ElementType.METHOD})public @interface excel {public String id();public String comment();public String validator();public int colnum();}

接着是我写的一个接口：维护一些必要的动作

package com.ultrapower.eoms.ultrabpp.runtime.custom.interfaces;import java.io.Serializable;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Workbook;/** * 实现基本的动作 *  * @author Administrator *  * @param <T> */public interface ExcelTemplateInterface<T> {/** * 检测excel本身是否有重复数据 */public boolean isExcelRepeatObject(T o);/** * 检查execl表格内容，给错误信息进行赋值  *  * @param i * @param object * @return */public boolean rowValidator(int i, T o);/** * 检查是否为空行 public static boolean isEmptyRow(BomcFireWallEntity o) { if * (o.toString().trim().equals("")) { return true; } return false; } *  * @param object * @return */public boolean isEmptyRow(T object);/** * 检查是否是重复的元素  *  * @param o * @param bppid * @return */public boolean isDBRepeatObject(T o, Serializable id);/** * 将excel中数据提取出来转换为对象 *  * @return */public T toObject(Row ros);/** * 设置级联选项 */public void setCascade();/** * 设置数据有效性 */public void setvalidity(Workbook wb, int num);/** * 获取对应的名称解释 * @param id * @return */public String getComment(String id);/** * 导出数据 */public String exportExcel();/** * 导入模板 */public String importExcel();/** * 导出模板 */public String exportTemplate();/** * 导出代码写的模板 */public String exportDataTemplate();}

同时，书写了一个抽象类，抽象出来一些公用的方法和属性：

package com.ultrapower.eoms.ultrabpp.runtime.custom.template;import java.io.BufferedInputStream;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.List;import java.util.Map;import java.util.regex.Matcher;import java.util.regex.Pattern;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.CellRangeAddressList;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataValidation;import org.apache.poi.ss.usermodel.DataValidationConstraint;import org.apache.poi.ss.usermodel.DataValidationHelper;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Name;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.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFDataValidation;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.apache.struts2.ServletActionContext;import com.opensymphony.xwork2.ActionSupport;import com.ultrapower.eoms.common.core.web.IBaseAction;import com.ultrapower.eoms.ultrabpp.runtime.custom.interfaces.ExcelTemplateInterface;import com.ultrapower.eoms.ultrabpp.runtime.custom.service.CommonService;/** * 导入导出抽象类 *  * @author liyao *  */@SuppressWarnings("deprecation")public abstract class ExcelTemplateAction<T> extends ActionSupport implements ExcelTemplateInterface<T>, IBaseAction {private static final long serialVersionUID = -6202935427801369297L;protected HttpServletResponse response;// 指定excel列名的模型protected List<String> ColumnNames;protected String templateName; // 模板文件名protected String excelFileContentType; // 文件扩展名protected String excelFileFileName; // 文件名称protected String savePath; // 保存路径protected File excelFile; // 上传的文件protected String rid; // 隐藏的关联id//实体类，用于反射成员变量的set/get方法，在代码块初始化protected Class<?> clazz;//维护字段和字段解释的对应关系protected Map<String, String> map;//维护excel中列和字段的对象关系protected Map<Integer, String> xmap;// 将Excel文件解析完毕后数据存放到这个对象中protected List<T> data = new ArrayList<T>(); // 数据行//维护工作表集protected Workbook wb;//表示第一张工作表protected Sheet sheet;//错误信息protected StringBuffer errorMessage = new StringBuffer();// 针对Model的Dao初始化protected CommonService<T> service;/** * 传入模板名称 *  * @param templateName */private void setResponseHeader() {try {response.setContentType("application/octet-stream;charset=iso-8859-1");response.setHeader("Content-Disposition", "attachment;filename=" + java.net.URLEncoder.encode(this.templateName, "UTF-8"));} catch (Exception ex) {ex.printStackTrace();}}/** * 判断文件类型 *  * @param is * @return * @throws IOException */protected Workbook createWorkBook(InputStream is) throws IOException {if (excelFileFileName.toLowerCase().endsWith("xls")) {return new HSSFWorkbook(is);}if (excelFileFileName.toLowerCase().endsWith("xlsx")) {return new XSSFWorkbook(is);}return null;}/** *  * @param os  * @throws IOException */private void exportExcel(OutputStream os) throws IOException {String path = this.getClass().getClassLoader().getResource("/").getPath();path += this.templateName;// path是指欲下载的文件的路径。// 以流的形式下载文件。InputStream fis = new BufferedInputStream(new FileInputStream(path));byte[] buffer = new byte[fis.available()];fis.read(buffer);fis.close();os.write(buffer);}/** * 导出带数据模板的公用方法 */public void exportWorkbook() {setResponse();setResponseHeader();try {exportExcelWb(response.getOutputStream());OutputStream os = response.getOutputStream();os.flush();os.close();} catch (IOException e) {e.printStackTrace();}}/** * 将Workbook对象写入输出流中，发送给客户端 * @param os Response对象输出流 */public void exportExcelWb(OutputStream os) {try {wb.write(os);} catch (IOException e) {e.printStackTrace();}}/** * 导出模板 *  * @return */public String exportTemplate() {setResponse();setResponseHeader();try {exportExcel(response.getOutputStream());OutputStream os = response.getOutputStream();os.flush();os.close();} catch (IOException e) {e.printStackTrace();}return null;}protected void setResponse() {this.response = ServletActionContext.getResponse();}/** * 导入Excel文件流程方法，需要实现toObject.rowValidator.isDBRepeatObject.isExcelRepeatObject * @param bppid  资源关联id */public void importExcel(String bppid) {try {Workbook book = createWorkBook(new FileInputStream(excelFile));// book.getNumberOfSheets(); 判断Excel文件有多少个sheetSheet sheet = book.getSheetAt(0);for (int i = 1; i <= sheet.getLastRowNum(); i++) {// 获取行对象Row ros = sheet.getRow(i);// 调用方法转换为对象T ob = toObject(ros);if (rowValidator(i, ob) && !isDBRepeatObject(ob, bppid) && !isExcelRepeatObject(ob)) {data.add(ob);}}for (int i = 0; i < data.size(); i++) {T o = data.get(i);service.update(o);}} catch (Exception e) {e.printStackTrace();}}/** * 进行基础的一些导出模板 *  * @param SheetName * @return */public String exportExcel(String SheetName) {wb = new XSSFWorkbook();sheet = (XSSFSheet) wb.createSheet(SheetName);XSSFRow row = (XSSFRow) sheet.createRow((int) 0);CellStyle style = wb.createCellStyle();Font font = wb.createFont();font.setBoldweight(Font.BOLDWEIGHT_BOLD);style.setFont(font);style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式style.setFillForegroundColor(IndexedColors.AQUA.getIndex());style.setFillPattern(CellStyle.SOLID_FOREGROUND);// 写列名，视自己的需求而定for (int i = 0; i < ColumnNames.size(); i++) {XSSFCell cell = row.createCell(i);cell = row.createCell(i);cell.setCellValue(this.ColumnNames.get(i));cell.setCellStyle(style);sheet.setColumnWidth((short) i, (short) 4000);}return null;}public void creatRow(Row currentRow, String[] textList) {if (textList != null && textList.length > 0) {int i = 0;for (String cellValue : textList) {Cell userNameLableCell = currentRow.createCell(i++);userNameLableCell.setCellValue(cellValue);}}}public void creatExcelNameList(Workbook workbook, String nameCode, int order, int size, boolean cascadeFlag) {Name name;name = workbook.createName();name.setNameName(nameCode);String formula = "poihide" + "!" + creatExcelNameList(order, size, cascadeFlag);name.setRefersToFormula(formula);}public String creatExcelNameList(int order, int size, boolean cascadeFlag) {char start = 'A';if (cascadeFlag) {if (size <= 25) {char end = (char) (start + size - 1);return "$" + start + "$" + order + ":$" + end + "$" + order;} else {char endPrefix = 'A';char endSuffix = 'A';if ((size - 25) / 26 == 0 || size == 51) {// 26-51之间，包括边界（仅两次字母表计算）if ((size - 25) % 26 == 0) {// 边界值endSuffix = (char) ('A' + 25);} else {endSuffix = (char) ('A' + (size - 25) % 26 - 1);}} else {// 51以上if ((size - 25) % 26 == 0) {endSuffix = (char) ('A' + 25);endPrefix = (char) (endPrefix + (size - 25) / 26 - 1);} else {endSuffix = (char) ('A' + (size - 25) % 26 - 1);endPrefix = (char) (endPrefix + (size - 25) / 26);}}return "$" + start + "$" + order + ":$" + endPrefix + endSuffix + "$" + order;}} else {if (size <= 26) {char end = (char) (start + size - 1);return "$" + start + "$" + order + ":$" + end + "$" + order;} else {char endPrefix = 'A';char endSuffix = 'A';if (size % 26 == 0) {endSuffix = (char) ('A' + 25);if (size > 52 && size / 26 > 0) {endPrefix = (char) (endPrefix + size / 26 - 2);}} else {endSuffix = (char) ('A' + size % 26 - 1);if (size > 52 && size / 26 > 0) {endPrefix = (char) (endPrefix + size / 26 - 1);}}return "$" + start + "$" + order + ":$" + endPrefix + endSuffix + "$" + order;}}}public DataValidation getDataValidationByFormula(Sheet sheet, String formulaString, int naturalRowIndex, int naturalColumnIndex) {// 加载下拉列表内容DataValidationHelper dvHelper = sheet.getDataValidationHelper();DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formulaString);// 设置数据有效性加载在哪个单元格上。// 四个参数分别是：起始行、终止行、起始列、终止列int firstRow = naturalRowIndex - 1;int lastRow = naturalRowIndex - 1;int firstCol = naturalColumnIndex - 1;int lastCol = naturalColumnIndex - 1;CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);// 数据有效性对象DataValidation data_validation_list = dvHelper.createValidation(dvConstraint, regions);data_validation_list.setEmptyCellAllowed(false);if (data_validation_list instanceof XSSFDataValidation) {data_validation_list.setSuppressDropDownArrow(true);data_validation_list.setShowErrorBox(true);} else {data_validation_list.setSuppressDropDownArrow(false);}// 设置输入信息提示信息data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");// 设置输入错误提示信息data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");return data_validation_list;}/** * 是否是ip地址的格式 *  * @param ipAddress * @return */public boolean isIpv4(String ipAddress) {if (ipAddress == null || "".equals(ipAddress.trim())) {return false;}String ip = "^(1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|[1-9])\\." + "(00?\\d|1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\." + "(00?\\d|1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)\\."+ "(00?\\d|1\\d{2}|2[0-4]\\d|25[0-5]|[1-9]\\d|\\d)$";Pattern pattern = Pattern.compile(ip);Matcher matcher = pattern.matcher(ipAddress);return matcher.matches();}/** * 检查字符串是否是必填的要求 *  * @param str * @return */public boolean isCorrectString(String str) {if (str == null) {return false;} else if (str.trim().equals("")) {return false;} else {return true;}}public boolean isEmptyRow(T o) {if (o.toString().replaceAll( "null", " " ).trim().equals( "" )) {return true;}return false;}public String getExcelFileContentType() {return excelFileContentType;}public void setExcelFileContentType(String excelFileContentType) {this.excelFileContentType = excelFileContentType;}public String getExcelFileFileName() {return excelFileFileName;}public void setExcelFileFileName(String excelFileFileName) {this.excelFileFileName = excelFileFileName;}public String getSavePath() {return savePath;}public void setSavePath(String savePath) {this.savePath = savePath;}public StringBuffer getErrorMessage() {return errorMessage;}public void setErrorMessage(StringBuffer errorMessage) {this.errorMessage = errorMessage;}public String getTemplateName() {return templateName;}public void setTemplateName(String templateName) {this.templateName = templateName;}public File getExcelFile() {return excelFile;}public void setExcelFile(File excelFile) {this.excelFile = excelFile;}public HttpServletResponse getResponse() {return response;}protected HttpServletRequest getRequest() {return ServletActionContext.getRequest();}public String getRid() {return rid;}public void setRid(String rid) {this.rid = rid;}public CommonService<T> getService() {return service;}public void setService(CommonService<T> service) {this.service = service;}@Overridepublic Object clone() {Object result = null;try {result = super.clone();} catch (CloneNotSupportedException e) {e.printStackTrace();}return result;}}

接着：让我们来看一下一个案例具体的实现：从中便能感受到代码的简便：

package com.ultrapower.eoms.ultrabpp.runtime.extend.ITOM_RES_AIS;import java.beans.PropertyDescriptor;import java.io.Serializable;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Set;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.DataValidation;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.xssf.usermodel.XSSFDrawing;import org.hibernate.Query;import org.hibernate.Session;import org.junit.Test;import com.ultrapower.eoms.ultrabpp.runtime.custom.annotation.excel;import com.ultrapower.eoms.ultrabpp.runtime.custom.model.ItomResAisModel;import com.ultrapower.eoms.ultrabpp.runtime.custom.template.ExcelTemplateAction;import com.ultrapower.eoms.ultrabpp.runtime.dbutils.CommonBean;import com.ultrapower.eoms.ultrabpp.runtime.dbutils.LinkDataBase;/** * 新增导入导出功能 *  * @author liyao *  */public class Itom_Res_Ais_IE extends ExcelTemplateAction<ItomResAisModel> {/** *  */private static final longserialVersionUID= 6198737454407820615L;{try {clazz = Class.forName( "com.ultrapower.eoms.ultrabpp.runtime.custom.model.ItomResAisModel" );getMap();} catch (ClassNotFoundException e) {e.printStackTrace();}}public String getComment(String str) {String value = null;if (str.equals( "restype" )) {value = "【资源类型】";}if (str.equals( "manufacturers" )) {value = "【厂商】";}if (str.equals( "modell" )) {value = "【型号】";}if (str.equals( "itlwh" )) {value = "【规格】";}if (str.equals( "resnum" )) {value = "【资源数量】";}return value;}public boolean rowValidator(int i, ItomResAisModel o) {// 设置行号o.setErowid( String.valueOf( i ) );boolean fg = true;if (isEmptyRow( o )) {return false;} else {Set<String> entrySet = map.keySet();for (String str : entrySet) {if (map.get( str ).equals( "notnull" )) {if (!isCorrectString( getValue( str, o ) )) {errorMessage.append( "第" + i + "行" + getComment( str ) + "填写错误\\n" );fg = false;}}if (map.get( str ).equals( "isnum" )) {if (!isNumeric( getValue( str, o ) )) {errorMessage.append( "第" + i + "行" + getComment( str ) + "填写错误\\n" );fg = false;}}if (map.get( str ).equals( "ismeet" )) {if (!isMeetSpecifications( getValue( str, o ) )) {errorMessage.append( "第" + i + "行" + getComment( str ) + "填写错误\\n" );fg = false;}}}}return fg;}public boolean isDBRepeatObject(ItomResAisModel o, Serializable id) {o.setBppid( (String) id );List<ItomResAisModel> list = service.findById( id, "ItomResAisModel", "bppid" );for (ItomResAisModel itomResAisModel : list) {if (itomResAisModel.equals( o )) {errorMessage.append( "第" + o.getErowid() + "行数据与现有数据重复，已忽略导入\\n" );return true;}}return false;}/** * 必须要设置为null，因为Hibernate取出来的都是null */public ItomResAisModel toObject(Row ros) {ItomResAisModel ob = new ItomResAisModel();Set<Integer> set = xmap.keySet();for (int i : set) {try {ros.getCell( i ).setCellType( Cell.CELL_TYPE_STRING );setValue( xmap.get( i ), ros.getCell( i ).getStringCellValue(), ob );} catch (Exception e) {setValue( xmap.get( i ), null, ob );}}return ob;}public String exportExcel() {return null;}public String importExcel() {rid = getRequest().getParameter( "bppid" );super.importExcel( rid );return SUCCESS;}public void setColumnNames() {List<String> nameList = new ArrayList<String>();nameList.add( "资源类型" );nameList.add( "厂商" );nameList.add( "型号" );nameList.add( "资源数量" );nameList.add( "供货商" );nameList.add( "内存大小" );nameList.add( "网卡带宽" );nameList.add( "硬盘信息" );nameList.add( "规格(MM 长*宽*高)" );this.ColumnNames = nameList;}public String exportDataTemplate() {super.setTemplateName( "到货入库模板.xlsx" );super.exportExcel( "信息" );// 必填项标为红色// 创建字体对象 create Font ObjectFont font = wb.createFont();// 设置为黑色粗体，标题font.setBoldweight( Font.BOLDWEIGHT_BOLD );font.setColor( Font.COLOR_RED );// 创建单元格的样式CellStyle style = wb.createCellStyle();Row temprow = sheet.getRow( 0 );style.setFont( font );style.setAlignment( CellStyle.ALIGN_CENTER ); // 创建一个居中格式style.setFillForegroundColor( IndexedColors.AQUA.getIndex() );style.setFillPattern( CellStyle.SOLID_FOREGROUND );temprow.getCell( 0 ).setCellStyle( style );temprow.getCell( 1 ).setCellStyle( style );temprow.getCell( 2 ).setCellStyle( style );temprow.getCell( 3 ).setCellStyle( style );setCascade();setvalidity( wb, 100 );super.exportWorkbook();return null;}public String importAction() {rid = getRequest().getParameter( "rid" );return SUCCESS;}public void setCascade() {Sheet hideInfoSheet = wb.createSheet( "poihide" );// 隐藏一些信息// 在隐藏页设置选择信息String[] css = { }; // 厂商List<String> csList = new ArrayList<String>();String[] xhs = { };// 型号List<String> xhList = new ArrayList<String>();String[] zys = { };// 资源列表List<String> zyList = new ArrayList<String>();// 优化二级级联设置Map<String, Map<String, String>> cxMap = new HashMap<String, Map<String, String>>();String sql = "select distinct 厂商名称,型号名称  from CMDB_to_ITOM_vendorview_view order by 厂商名称,型号名称";CommonBean bean = LinkDataBase.selectDate( sql, "bpp" );for (int a = 0; a < bean.getRowNum(); a++) {String cs = bean.getCellStrForce( a, "厂商名称" );String xh = bean.getCellStrForce( a, "型号名称" );if (!cxMap.containsKey( cs )) {Map<String, String> xhTempMap = new HashMap<String, String>();xhTempMap.put( xh, xh );cxMap.put( cs, xhTempMap );} else {Map<String, String> xhTempMapa = cxMap.get( cs );if (!xhTempMapa.containsKey( xh )) {xhTempMapa.put( xh, xh );cxMap.put( cs, xhTempMapa );} else {}}}// endint count = 0;Row csRow = hideInfoSheet.createRow( 0 );Set<String> cssKey = cxMap.keySet();for (String cs : cssKey) {csList.add( cs );// 设置地点名称Map<String, String> xhMap = cxMap.get( cs );Set<String> xhsKey = xhMap.keySet();for (String xh : xhsKey) {xhList.add( xh );}xhs = (String[]) xhList.toArray( new String[xhList.size()] );count++;Row xhRow = hideInfoSheet.createRow( count );creatRow( xhRow, xhs );creatExcelNameList( wb, cs, count + 1, xhs.length, true );xhList.clear();}css = (String[]) csList.toArray( new String[csList.size()] );csList.clear();creatRow( csRow, css );creatExcelNameList( wb, "cs", 1, css.length, false );String sql2 = "select distinct 资源类型名称   from CMDB_to_ITOM_getcmdbcat_view";CommonBean bean2 = LinkDataBase.selectDate( sql2, "bpp" );for (int a = 0; a < bean2.getRowNum(); a++) {zyList.add( bean2.getCellStrForce( a, "资源类型名称" ) );}zys = (String[]) zyList.toArray( new String[zyList.size()] );Row zyRow = hideInfoSheet.createRow( count++ );creatRow( zyRow, zys );creatExcelNameList( wb, "zy", count++, zys.length, false );// 设置隐藏页标志wb.setSheetHidden( wb.getSheetIndex( "poihide" ), true );}public void setvalidity(Workbook wb, int num) {// 创建绘图对象int sheetIndex = wb.getNumberOfSheets();if (sheetIndex > 0) {for (int i = 0; i < sheetIndex; i++) {Sheet sheet = wb.getSheetAt( i );XSSFDrawing p = (XSSFDrawing) sheet.createDrawingPatriarch();if (!"poihide".equals( sheet.getSheetName() )) {for (int a = 2; a < num + 2; a++) {// 厂商添加验证数据DataValidation data_validation_list = getDataValidationByFormula( sheet, "cs", a, 2 );sheet.addValidationData( data_validation_list );// 型号添加验证数据DataValidation data_validation_list3 = getDataValidationByFormula( sheet, "INDIRECT($B$" + a + ")", a, 3 );sheet.addValidationData( data_validation_list3 );// 资源添加验证数据DataValidation data_validation_list4 = getDataValidationByFormula( sheet, "zy", a, 1 );sheet.addValidationData( data_validation_list4 );}}}}}public void clearValidate() {// TODO Auto-generated method stub}public boolean isExcelRepeatObject(ItomResAisModel o) {for (ItomResAisModel itomResAisModel : data) {if (itomResAisModel.equals( o )) {errorMessage.append( "第" + o.getErowid() + "行数据与Excel中第" + itomResAisModel.getErowid() + "行数据重复" + "，已忽略导入\\n" );return false;}}return true;}/** * 判断是否符合规格 *  * @param str * @return */public boolean isMeetSpecifications(String str) {if (str == null || str.trim().equals( "" )) {return true;}String[] arry = str.split( "[*]" );if (arry.length == 3) {return true;} else {return false;}}/** * 判断字符串是否为数字 *  * @param str * @return */public boolean isNumeric(String str) {if (null == str) {return false;}Pattern pattern = Pattern.compile( "[0-9]*" );Matcher isNum = pattern.matcher( str );if (!isNum.matches()) {return false;}return true;}/** * 根据反射获取某字段的值 *  * @param name * @return */public String getValue(String name, ItomResAisModel model) {String value = "";try {PropertyDescriptor pd = new PropertyDescriptor( name, clazz );Method rM = pd.getReadMethod();// 获得读方法value = (String) rM.invoke( model );} catch (Exception e) {}return value;}/** * 根据反射设置某字段的值 *  * @param name * @param value */public void setValue(String name, String value, ItomResAisModel model) {try {PropertyDescriptor pd = new PropertyDescriptor( name, clazz );Method wM = pd.getWriteMethod();// 获得写方法wM.invoke( model, value );//} catch (Exception e) {}}/** * 获取excel字段和验证信息 */public void getMap() {map = new HashMap<String, String>();xmap = new HashMap<Integer, String>();try {Class<ItomResAisModel> c = ItomResAisModel.class;// 获取对象中所有的方法Method[] methods = c.getMethods();// 进行循环for (Method method : methods) {// 判断方法上是否存在excel这个注解if (method.isAnnotationPresent( excel.class )) {excel ex = method.getAnnotation( excel.class );map.put( ex.id(), ex.validator() );xmap.put( ex.colnum(), ex.id() );this.ColumnNames.add( ex.comment() );}}} catch (Exception e) {e.printStackTrace();}}}

代码中getMap方法就是从实体类中找出有excel注解的这个字段，然后把得到的信息放置到Map中，同时还用到反射来得到Get/Set方法，以便于在遍历Excel数据行得到数据，验证得到的数据的正确性中减少代码的书写，如果不用反射的话，需要下面这种代码的书写才能达到效果，代码相关的冗余和繁琐。

public ItomResNetinModel toObject(Row ros) {ItomResNetinModel ob = new ItomResNetinModel();try {ros.getCell(0).setCellType(Cell.CELL_TYPE_STRING);ob.setRes_id(ros.getCell(0).getStringCellValue());} catch (Exception e) {ob.setRes_id(null);}try {ros.getCell(1).setCellType(Cell.CELL_TYPE_STRING);ob.setRes_type(ros.getCell(1).getStringCellValue());} catch (Exception e) {ob.setRes_type(null);}try {ros.getCell(2).setCellType(Cell.CELL_TYPE_STRING);ob.setMc(ros.getCell(2).getStringCellValue());} catch (Exception e) {ob.setMc(null);}try {ros.getCell(3).setCellType(Cell.CELL_TYPE_STRING);ob.setMod(ros.getCell(3).getStringCellValue());} catch (Exception e) {ob.setMod(null);}try {ros.getCell(4).setCellType(Cell.CELL_TYPE_STRING);ob.setSeriesnum(ros.getCell(4).getStringCellValue());} catch (Exception e) {ob.setSeriesnum(null);}try {ros.getCell(5).setCellType(Cell.CELL_TYPE_STRING);ob.setBearea(ros.getCell(5).getStringCellValue());} catch (Exception e) {ob.setBearea(null);}try {ros.getCell(6).setCellType(Cell.CELL_TYPE_STRING);ob.setBeplace(ros.getCell(6).getStringCellValue());} catch (Exception e) {ob.setBeplace(null);}try {ros.getCell(7).setCellType(Cell.CELL_TYPE_STRING);ob.setBeroom(ros.getCell(7).getStringCellValue());} catch (Exception e) {ob.setBeroom(null);}try {ros.getCell(8).setCellType(Cell.CELL_TYPE_STRING);ob.setBecabinet(ros.getCell(8).getStringCellValue());} catch (Exception e) {ob.setBecabinet(null);}try {ros.getCell(9).setCellType(Cell.CELL_TYPE_STRING);ob.setRes_status(ros.getCell(9).getStringCellValue());} catch (Exception e) {ob.setRes_status(null);}try {ros.getCell(10).setCellType(Cell.CELL_TYPE_STRING);ob.setNote(ros.getCell(10).getStringCellValue());if(ros.getCell(10).getStringCellValue().trim().equals("")){ob.setNote(null);}} catch (Exception e) {ob.setNote(null);}try {ros.getCell(11).setCellType(Cell.CELL_TYPE_STRING);ob.setSoftversionname(back(ros.getCell(11).getStringCellValue()));if(ros.getCell(11).getStringCellValue().trim().equals("")){ob.setSoftversionname(null);}} catch (Exception e) {ob.setSoftversionname(null);}try {ros.getCell(12).setCellType(Cell.CELL_TYPE_STRING);ob.setBusinessname(ros.getCell(12).getStringCellValue());if(ros.getCell(12).getStringCellValue().trim().equals("")){ob.setBusinessname(null);}} catch (Exception e) {ob.setBusinessname(null);}try {ros.getCell(13).setCellType(Cell.CELL_TYPE_STRING);ob.setIp4(ros.getCell(13).getStringCellValue());if(ros.getCell(13).getStringCellValue().trim().equals("")){ob.setIp4(null);}} catch (Exception e) {ob.setIp4(null);}try {ros.getCell(14).setCellType(Cell.CELL_TYPE_STRING);ob.setIp(ros.getCell(14).getStringCellValue());if(ros.getCell(14).getStringCellValue().trim().equals("")){ob.setIp(null);}} catch (Exception e) {ob.setIp(null);}try {ros.getCell(15).setCellType(Cell.CELL_TYPE_STRING);ob.setBusdomain(getBusDomainId(ros.getCell(15).getStringCellValue()));if(ros.getCell(15).getStringCellValue().trim().equals("")){ob.setBusdomain(null);}} catch (Exception e) {ob.setBusdomain(null);} try {ros.getCell(16).setCellType(Cell.CELL_TYPE_STRING);ob.setResdescr(ros.getCell(15).getStringCellValue());if(ros.getCell(16).getStringCellValue().trim().equals("")){ob.setResdescr(null);}} catch (Exception e) {ob.setResdescr(null);}try {ros.getCell(17).setCellType(Cell.CELL_TYPE_STRING);ob.setDevmaintainername(ros.getCell(17).getStringCellValue());if(ros.getCell(17).getStringCellValue().trim().equals("")){ob.setDevmaintainername(null);}} catch (Exception e) {ob.setDevmaintainername(null);}try {ros.getCell(19).setCellType(Cell.CELL_TYPE_STRING);ob.setId(ros.getCell(19).getStringCellValue());if(ros.getCell(19).getStringCellValue().trim().equals("")){ob.setId("");}} catch (Exception e) {ob.setId("");}try {ob.setSoftversion(getOsId(ob.getSoftversionname()));} catch (Exception e) {ob.setSoftversion(null);}try {ob.setDevattachedbusiness(getBusId(ob.getBusinessname()));} catch (Exception e) {ob.setDevattachedbusiness(null);}try {ob.setTel(getPhone(ob.getDevmaintainername()));} catch (Exception e) {ob.setTel(null);}try {ob.setDevmaintainer(getRyId(ob.getDevmaintainername()));} catch (Exception e) {ob.setDevmaintainer(null);}return ob;}

第一次用的就是这种方式，对字段都快疯了。

里面维护了一个服务类去进行Dao层操作，如下：

package com.ultrapower.eoms.ultrabpp.runtime.custom.service;import java.io.Serializable;import java.util.ArrayList;import java.util.List;import org.hibernate.Query;import org.hibernate.Session;import com.ultrapower.eoms.common.core.dao.HibernateDaoImpl;public class CommonService<T> {private HibernateDaoImpl<T>dao;public void update(T o) {dao.update( o );}public void save(T o) {dao.save( o );}public void saveList(List<T> data) {for (T t : data) {dao.save( t );}}public List<T> findById(Serializable id, String className, String pk) {List<T> data = new ArrayList<T>();Session session = dao.getSessionFactory().openSession();session.beginTransaction();try {Query query = session.createQuery( "from" + className + " where " + pk + "='" + id + "'" );data = query.list();session.getTransaction().commit();return data;} catch (Exception e) {return null;} finally {session.close();}}}

好的，代码都写完了，我们来看下导入的jsp页面以及struts

先看jsp代码：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%><%@ taglib prefix="s" uri="/struts-tags"%><%    String rid = new String(request.getParameter("rid").getBytes("ISO-8859-1"), "utf-8");%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <title>导入excel</title><meta http-equiv="pragma" content="no-cache"><meta http-equiv="cache-control" content="no-cache"><meta http-equiv="expires" content="0"><meta http-equiv="keywords" content="keyword1,keyword2,keyword3"><%@ include file="/common/core/taglibs.jsp"%><link rel="stylesheet" type="text/css"href="${ctx}/common/style/blue/css/BaseQuery.css" /><script type="text/javascript" src="${ctx}/common/javascript/AjaxBus.js"></script><script type="text/javascript"src="${ctx}/common/javascript/BaseQuery.js"></script><script type="text/javascript"src="${ctx}/ultrabpp/runtime/clientframework/util/jquery-1.7.2.js"></script></head>    <body style="background-color:white;">    <div class="title_right"><div class="title_left"><span class="title_bg"/> <span class="title_icon2">导入excel</span></span> <span class="title_xieline"></span></div><form action="" id="firstform" method="post" enctype="multipart/form-data" >    <div id="quizid">            <s:label value="选择导入文件:"></s:label>                            <span id="iexeclFile">    <s:file   name="excelFile" cssClass="width:160px" onchange="checkPerm(this);"></s:file>    </span>             <s:submit value="确定导入" onclick="iexecl();"></s:submit>                 <span id="message" style="display:none">${errorMessage}</span>    </div>    </form>    </div>  </body></html><script type="text/javascript">var curWwwPath=window.document.location.href; var pathName=window.document.location.pathname; var pos=curWwwPath.indexOf(pathName); var localhostPaht=curWwwPath.substring(0,pos); var projectName=pathName.substring(0,pathName.substr(1).indexOf('/')+1);  function iexecl(){     var a=$("#excelFile").val();     if($.trim(a)==''){     alert("请选择文件")     return false;     }     else{     document.getElementById("firstform").action=localhostPaht+projectName+"/Itom_Res_Netin_importandexport/importExcel.action?bppid="+rid;    document.getElementById("firstform").submit();      }     }         function checkLast(str){          var flag=false;          var ext=str.split('.')[str.split('.').length-1];          if(ext=='xls'|| ext=='xlsx'){              flag=true;          }          return flag;      }          function checkPerm(obj){          if(!checkLast(obj.value.toLowerCase())){              alert("上传文件格式错误！");              document.getElementById("iexeclFile").innerHTML='<s:file  id="iexeclFile" name="excelFile" cssClass="width:160px" onchange="checkPerm(this);"></s:file>';          }      }  </script><script>//Begin 传入的bppid主键var rid='<%=rid%>';//Endif(!window.name){    window.name = 'liyao';        }else{      var m = "";var a = document.getElementById('message').innerText;var c = a.split('\\n');for ( var i = 0; i < c.length; i++) {m = m + c[i] + "\n";}m = $.trim(m);if (m.length == 0) {alert("导入成功");window.close();} else {alert(m);window.close();window.opener.reload();}}</script>

然后是Struts2的配置：

<!--到货入库导入导出配置 --><package name="Itom_Res_Ais_execl" extends="struts-default"namespace="/Itom_Res_Ais_importandexport"><action name="*" method="{1}" class="Itom_Res_Ais_importandexport"><interceptor-ref name="fileUpload"><!-- 配置允许上传的文件类型 --><param name="allowedTypes">image/x-ms-bmp,image/jpeg,image/gif,image/png,image/x-png,application/excel,application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet,application/octet-stream,application/x-zip-compressed</param><!-- 配置允许上传的文件大小 --><param name="maximumSize">2048000</param></interceptor-ref><interceptor-ref name="defaultStack"></interceptor-ref><!-- 配置上传文件的保存的相对路径 --><param  name="savePath">/temp</param><result name="success">/ultrabpp/runtime/forms/ITOM_RES_AIS/script/excel.jsp?rid=${rid}</result></action></package>

代码好多啊，写文档真的是费神。就这样吧