Java中excel表数据的批量导入方法

				?

									本文实例为大家分享了java中excel表数据的批量导入，供大家参考，具体内容如下

									首先看下工具类：

									import java.awt.color; 

									import java.io.bytearrayinputstream; 

									import java.io.bytearrayoutputstream; 

									import java.io.file; 

									import java.io.fileinputstream; 

									import java.io.inputstream; 

									import java.lang.reflect.field; 

									import java.text.dateformat; 

									import java.text.decimalformat; 

									import java.text.simpledateformat; 

									import java.util.*; 

									import javax.swing.text.attributeset; 

									import javax.swing.text.element; 

									import javax.swing.text.html.css; 

									import javax.swing.text.html.htmldocument; 

									import javax.swing.text.html.htmleditorkit; 

									import cn.vrview.dev.common.exception.businessexception; 

									import org.apache.commons.lang3.stringutils; 

									import org.apache.logging.log4j.logmanager; 

									import org.apache.logging.log4j.logger; 

									import org.apache.poi.hssf.usermodel.*; 

									import org.apache.poi.hssf.util.hssfcolor; 

									import org.apache.poi.ss.usermodel.*; 

									import org.apache.poi.ss.util.cellrangeaddress; 

									import org.apache.poi.xssf.usermodel.xssfcolor; 

									import org.apache.poi.xssf.usermodel.xssffont; 

									import org.apache.poi.xssf.usermodel.xssfworkbook; 

									import org.springframework.web.util.htmlutils; 

									import cn.vrview.dev.common.util.stringutil; 

									public class exceltools { 

									  /** log */

									  private static logger log = logmanager.getlogger(); 

									  /** 

									   * 导出excel 

									   * <p> 

									   * 使用方法：<br> 

									   * <code> list<map<string, object>> datalist = new arraylist<map<string,object>>();<br> 

									   * is = exceltools.exportxls(datalist,new string[] {"createtime:日期","name:名称", "sex:性别", "remark:备注"}); 

									   * </code> 

									   * 

									   * @param collect 

									   *      待导出的数据集合 

									   * @param header 

									   *      要导出的列 

									   * @return inputstream 返回文件流 

									   */

									  public static inputstream exportxls(collection<map<string, object>> collect, string[] header) { 

									    bytearrayoutputstream out = new bytearrayoutputstream(); 

									    hssfworkbook book = new hssfworkbook(); 

									    try { 

									      // 添加一个sheet 

									      hssfsheet sheet = book.createsheet("sheet1"); 

									      // 定义要导出的列名集合 

									      set<string> columns = new hashset<string>(); 

									      // 设置单元格背景色 

									      hssfcellstyle cellstyle = book.createcellstyle(); 

									      cellstyle.setfillpattern(hssfcellstyle.solid_foreground); 

									      cellstyle.setfillforegroundcolor(new hssfcolor.yellow().getindex()); 

									      // 生成表头 

									      hssfrow row = sheet.createrow(0); 

									      hssfcell cell = row.createcell(0); 

									      cell.setcellstyle(cellstyle); 

									      cell.setcellvalue("序号"); 

									      // 列号从1开始 

									      int n = 1; 

									      // 解析头字符串 

									      for (string str : header) { 

									        string[] arr = str.split(":"); 

									        columns.add(n + "," + arr[0]);// 添加要导出的字段名并且与列号n绑定 

									        cell = row.createcell(n); 

									        cell.setcellstyle(cellstyle); 

									        cell.setcellvalue(arr[1]); 

									        n++; 

									      } 

									      // 生成数据行从1开开始，0为表头 

									      int i = 1; 

									      // 生成数据行列 

									      for (map<string, object> map : collect) { 

									        hssfrow datarow = sheet.createrow(i); 

									        // 生成序号 

									        datarow.createcell(0).setcellvalue(i); 

									        // 生成其他列 

									        for (string column : columns) { 

									          // 用逗号分割获得字段名，[0]为列号用于和表头标题对应上 

									          string columnname = column.split(",")[1]; 

									          // 生成序号列 

									          cell = datarow.createcell(integer.parseint(column.split(",")[0])); 

									          string value = ""; 

									          value = map.get(columnname) + ""; 

									          // 当value为null 时转换为"" 

									          if ("null".equals(value)) { 

									            value = ""; 

									          } 

									          richtextstring richtextstring = processhtml(book, value); 

									          cell.getcellstyle().setwraptext(false); 

									          cell.setcellvalue(richtextstring); 

									        } 

									        i++; 

									      } 

									      book.write(out); 

									      out.close(); 

									    } catch (exception e) { 

									      e.printstacktrace(); 

									    } 

									    return new bytearrayinputstream(out.tobytearray()); 

									  } 

									  /** 

									   * 获得excel文件数据<br> 

									   * 用法：<br> 

									   * sheetinfo sheetinfo = new exceltools().new sheetinfo();<br> 

									   * sheetinfo.setrowtitle(0); list<string> sheets = new arraylist<string>();<br> 

									   * string sheetname = "sheet1"; sheets.add(sheetname);<br> 

									   * sheetinfo.setsheetnames(sheets); <br> 

									   * sheetinfo.setcolumnsmapping(new string[] { "prodname:商品名称", 

									   * "prodspec:规格", "collectprice:价格:" + {@link regexpenum} 

									   * regexpenum.notempty_isnumber, "priceunit:单位", "collectmarket:报价市场", 

									   * "prodlevel:等级" }); <br> 

									   * map<string, list> data = exceltools.getexcel(new file(path), sheetinfo); 

									   * 

									   * @param 

									   * 

									   * @param sheetinfo 

									   *      初始化信息 

									   * @return map {sheet1:list} 

									   * @throws exception 

									   *       exception 

									   */

									  @suppresswarnings("rawtypes") 

									  public static map getexcel(file f, sheetinfo sheetinfo, string exceltype) throws exception { 

									    return getexcel(new fileinputstream(f), sheetinfo, exceltype); 

									  } 

									  @suppresswarnings({ "rawtypes", "unchecked" }) 

									  public static map getexcel(inputstream in, sheetinfo sheetinfo, string exceltype) throws exception { 

									    map<string, string> columnsmap = new hashmap<string, string>(); 

									    // 列验证表达式map 

									    list<string> errmsg = new arraylist<string>(); 

									    int errnum = 0;// 错误总数 

									    int errlimit = 10;// 限制错误提示数 

									    /** 用于存储excel根据指定规则读取的所有内容 */

									    map excelinfo = new hashmap(); 

									    workbook book = null; 

									    try { 

									      if (exceltype.equals("xls")) { 

									        book = new hssfworkbook(in); 

									        //throw new businessexception("excel版本太低，请使用2007以上版本（扩展名为：xlsx）"); 

									      } else { 

									        book = new xssfworkbook(in); 

									      } 

									    } catch (outofmemoryerror e) { 

									      throw new runtimeexception("当前excel文件过大，请检查各个sheet表格中是否有无效空数据，包括带有格式和线框的行列数据，请删除这些无效数据！正常excle文件大小【1m】以内"); 

									    } 

									    // checktitle(book, sheetinfo); 

									    // 获得工作表数量 

									    int sheetnum = sheetinfo.getsheetnames().size(); 

									    // 循环所有的工作表，读取里面的数据 

									    for (int sheetindex = 0; sheetindex < sheetnum; sheetindex++) { 

									      // 获得当前工作表对象 

									      string sheetname = htmlutils.htmlunescape(sheetinfo.getsheetnames().get(sheetindex)); 

									      map<string, string> validatemap = new hashmap<string, string>(); 

									      for (string mapstr : sheetinfo.getcolumnsmapping().get(sheetname)) { 

									        string[] arr = mapstr.split(":"); 

									        columnsmap.put(arr[1], arr[0]); 

									        if (arr.length == 3) {// 若果验证表达式不为空，则添加到map中 

									          validatemap.put(arr[1], arr[2]); 

									        } 

									      } 

									      sheet sheet = book.getsheet(sheetname); 

									      if (null == sheet) { 

									        throw new runtimeexception(string.format("获取表失败，请确认sheet《%s》是否存在于excel中", sheetname)); 

									      } 

									      // 用于存储所工作表中的数据内容 

									      list sheetlist = new arraylist(); 

									      // 获取当前表格的行数 

									      int rows = sheet.getlastrownum(); 

									      // 获取当前表格的列数 

									      int columns = sheet.getrow(sheetinfo.getrowtitle()).getlastcellnum(); 

									      if (rows <= sheetinfo.getrowtitle()) {// 如果当前表格没有需要的数据就继续下一次循环 

									        continue; 

									      } 

									      // 获得当前工作表标题内容 

									      list<string> titlelist = new arraylist<string>(); 

									      // 循环每一行中的每一个单元格，读取单元格内的值 

									      row titlerow = sheet.getrow(sheetinfo.getrowtitle()); 

									      for (int jj = 0; jj < columns; jj++) { 

									        cell celltitle = titlerow.getcell(jj); 

									        if (celltitle != null) { 

									          int row = celltitle.getrowindex(); 

									          int column = celltitle.getcolumnindex(); 

									          if (ismergedregion(sheet, row, column)) { 

									            titlelist.add(getmergedregionvalue(sheet, row, column)); 

									          } else { 

									            titlelist.add(getcellvalue(celltitle)); 

									          } 

									        } else { 

									          throw new runtimeexception("表头读取错误,当前设置为第" + (sheetinfo.getrowtitle() + 1) + "行<br/>表头内容为：" + titlerow + "，请检查是否正确，如有异常，可删除表头格式，重新粘贴表头！"); 

									        } 

									      } 

									      // system.out.println(titlelist); 

									      // 验证表头 

									      string[] titles = sheetinfo.getcolumnsmapping().get(sheetname); 

									      for (string s : titles) { 

									        string[] colarr = s.split(":"); 

									        // 如果excel表格中的表头缺少该字段 

									        boolean include = false; 

									        for (string t : titlelist) { 

									          if (stringutils.deletewhitespace(t).equalsignorecase(colarr[1])) { 

									            include = true; 

									            break; 

									          } 

									        } 

									        if (!include) { 

									          throw new runtimeexception("【" + colarr[1] + "】'列不存在！当前excel表头：" + titlelist); 

									        } 

									      } 

									      // 开始循环每一行，读取每一行的值,从标题下面一行开始读取 

									      for (int i = sheetinfo.getrowtitle() + 1; i <= rows; i++) { 

									        map rowmap = new hashmap(); 

									        row datarow = sheet.getrow(i); 

									        if (datarow == null) { 

									          throw new runtimeexception(string.format("excel第[%d]行为空，请检查!", i + 1)); 

									        } 

									        for (int j = 0; j < columns; j++) {// 循环每一行中的每一个单元格，读取单元格内的值 

									          string columntitle = titlelist.get(j); 

									          if ("".equals(columntitle)) { 

									            continue; 

									          } else { 

									            cell cell = datarow.getcell(j); 

									            string value = ""; 

									            string columnmapping = ""; 

									            // 单元列对应的entity属性名 

									            for (string title : columnsmap.keyset()) { 

									              if (stringutils.deletewhitespace(columntitle).equalsignorecase(title)) { 

									                columnmapping = columnsmap.get(title); 

									                break; 

									              } 

									            } 

									            if (null != cell) { 

									              cell.setcelltype(cell.cell_type_string); 

									              cellstyle cellstyle= cell.getcellstyle(); 

									              //单元格背景颜色 

									              if (exceltype.equals("xls")) { 

									                hssfcolor color=(hssfcolor) cellstyle.getfillforegroundcolorcolor(); 

									                if (j==0 && color!=null) { 

									                  rowmap.put("rowcolor", convertrgbtohex(color.gettriplet())); 

									                } 

									              } else { 

									                xssfcolor color=(xssfcolor) cellstyle.getfillforegroundcolorcolor(); 

									                if (j==0 && color!=null) { 

									                  rowmap.put("rowcolor", color.getargbhex().substring(2)); 

									                } 

									              } 

									              value = filterstr(cell + ""); 

									              int mergrow = getmergedregionrow(sheet, cell); 

									              if (mergrow > 0 && !stringutil.isempty(value)) { 

									                string rowspan=""; 

									                if (rowmap.get("rowspan")!=null) { 

									                  rowspan=rowmap.get("rowspan")+","; 

									                } 

									                rowmap.put("rowspan", rowspan+columnmapping+"-"+value+"-"+(mergrow + 1)); 

									              } 

									              if ( cell.getcellcomment()!=null) { 

									                //system.out.println(columnmapping+"@comment:"+cell.getcellcomment().getstring()); 

									                rowmap.put(columnmapping+"@comment", cell.getcellcomment().getstring()); 

									              } 

									            } 

									            // string columnmapping = columnsmap.get(columntitle); 

									            string validatereg = ""; 

									            string validateregmsg = ""; 

									            if (null != validatemap.get(columntitle)) { 

									              // 验证正则表达式 

									              regexpenum eum = regexpenum.valueof(validatemap.get(columntitle)); 

									              validatereg = eum.getvalue(); 

									              validateregmsg = eum.gettext(); 

									            } 

									            if (!stringutil.isempty(validatereg)) { 

									              if (value.matches(validatereg)) { 

									                rowmap.put(columnmapping, value); 

									              } else { 

									                errnum++; 

									                if (errnum <= errlimit) { 

									                  errmsg.add("第" + i + "行：【" + columntitle + "】数据为:'" + value.trim() + "' 不匹配！【" + validateregmsg + "】</br>\n"); 

									                } 

									              } 

									            } else { 

									              if (stringutil.isempty(columnmapping)) { 

									                continue; 

									              } else { 

									                //int row = cell.getrowindex(); 

									                ///int column = cell.getcolumnindex(); 

									                //if (ismergedregion(sheet, row, column)) { 

									                // rowmap.put(columnmapping, getmergedregionvalue(sheet, row, column)); 

									                //} else { 

									                  rowmap.put(columnmapping, value); 

									                //} 

									              } 

									            } 

									          } 

									        } 

									        sheetlist.add(rowmap); 

									      } 

									      excelinfo.put(sheet.getsheetname(), sheetlist); 

									    } 

									    in.close(); 

									    if (errmsg.size() > 0) { 

									      if (errnum > errlimit) { 

									        errmsg.add("您导入的数据模板格式错误过多(共" + errnum + "个)，请仔细检查模板数据是否正确！"); 

									      } 

									      throw new runtimeexception(errmsg.tostring().replaceall("\\[|\\]", "")); 

									    } 

									    // if (true) throw new runtimeexception("测试"); 

									    return excelinfo; 

									  } 

									  public static list<hashmap<string, string>> getexcel(inputstream in, sheetinfo sheetinfo) throws exception { 

									    map<string, string> columnsmap = new hashmap<string, string>(); 

									    // 列验证表达式map 

									    map<string, string> validatemap = new hashmap<string, string>(); 

									    list<string> errmsg = new arraylist<string>(); 

									    int errnum = 0;// 错误总数 

									    int errlimit = 10;// 限制错误提示数 

									    for (string mapstr : sheetinfo.getcolumnsmapping().get("columns")) { 

									      string[] arr = mapstr.split(":"); 

									      columnsmap.put(arr[1], arr[0]); 

									      if (arr.length == 3) {// 若果验证表达式不为空，则添加到map中 

									        validatemap.put(arr[1], arr[2]); 

									      } 

									    } 

									    /** 用于存储excel根据指定规则读取的所有内容 */

									    list excelinfo = new arraylist(); 

									    workbook book = workbookfactory.create(in); 

									    // checktitle(book, sheetinfo); 

									    // 获得工作表数量 

									    int sheetnum = book.getnumberofsheets(); 

									    // 循环所有的工作表，读取里面的数据 

									    for (int sheetindex = 0; sheetindex < sheetnum; sheetindex++) { 

									      // 获得当前工作表对象 

									      sheet sheet = book.getsheetat(sheetindex); 

									      // 用于存储所工作表中的数据内容 

									//     list sheetlist = new arraylist(); 

									      // 获取当前表格的行数 

									      int rows = sheet.getlastrownum(); 

									      // 获取当前表格的列数 

									      row titlerow = sheet.getrow(sheetinfo.getrowtitle()); 

									      if (titlerow == null){ 

									        throw new businessexception("文件格式不正确，请重新选择或者下载模板"); 

									      } 

									      int columns = titlerow.getlastcellnum(); 

									      if (columns != sheetinfo.getcolumnsmapping().get("columns").length){ 

									        throw new businessexception("文件格式不正确，请重新选择或者下载模板"); 

									      } 

									      if (rows <= sheetinfo.getrowtitle()) {// 如果当前表格没有需要的数据就继续下一次循环 

									        throw new businessexception("文件格式不正确，请重新选择或者下载模板"); 

									      } 

									      // 获得当前工作表标题内容 

									      list<string> titlelist = new arraylist<string>(); 

									      // 循环每一行中的每一个单元格，读取单元格内的值 

									      for (int jj = 0; jj < columns; jj++) { 

									        titlelist.add(titlerow.getcell(jj).getstringcellvalue()); 

									      } 

									      // 验证表头 

									      string[] titles = sheetinfo.getcolumnsmapping().get("columns"); 

									      for (string s : titles) { 

									        // 如果excel表格中的表头缺少该字段 

									        if (!titlelist.contains(s.split(":")[1])) { 

									//         errmsg.add("该excel表格的'" + sheet.getsheetname() + "'表的'" + s 

									//             + "'列不存在！"); 

									          throw new businessexception("文件格式不正确，请重新选择或者下载模板"); 

									        } 

									      } 

									      // 开始循环每一行，读取每一行的值,从标题下面一行开始读取 

									      for (int i = sheetinfo.getrowtitle() + 1; i <= rows; i++) { 

									        map rowmap = new hashmap(); 

									        row datarow = sheet.getrow(i); 

									        for (int j = 0; j < columns; j++) {// 循环每一行中的每一个单元格，读取单元格内的值 

									          string columntitle = titlelist.get(j); 

									          if ("".equals(columntitle)) { 

									            continue; 

									          } else { 

									            cell cell = datarow.getcell(j); 

									            string value = getcellvalue(cell); 

									            // 单元列对应的entity属性名 

									            string columnmapping = columnsmap.get(columntitle); 

									            string validatereg = ""; 

									            string validateregmsg = ""; 

									            if (null != validatemap.get(columntitle)) { 

									              // 验证正则表达式 

									              regexpenum eum = regexpenum.valueof(validatemap 

									                  .get(columntitle)); 

									              validatereg = eum.getvalue(); 

									              validateregmsg = eum.gettext(); 

									            } 

									            if (!stringutils.isempty(validatereg)) { 

									              if (value.matches(validatereg)) { 

									                rowmap.put(columnmapping, value); 

									              } else { 

									                errnum++; 

									                if (errnum <= errlimit) { 

									                  errmsg.add("第" + i + "行：【" + columntitle 

									                      + "】数据为:'" + value.trim() 

									                      + "' 不匹配！【" + validateregmsg 

									                      + "】</br>\n"); 

									                } 

									              } 

									            } else { 

									              rowmap.put(columnmapping, value); 

									            } 

									          } 

									        } 

									        excelinfo.add(rowmap); 

									      } 

									//     excelinfo.put(sheet.getsheetname(), sheetlist); 

									    } 

									    in.close(); 

									    if (errmsg.size() > 0) { 

									//     if (errnum > errlimit) { 

									//       errmsg.add("您导入的数据模板格式错误过多(共" + errnum + "个)，请仔细检查模板数据是否正确！"); 

									//     } 

									      throw new runtimeexception(errmsg.tostring().replaceall("\\[|\\]", 

									          "")); 

									    } 

									    return excelinfo; 

									  } 

									  /** 

									   * 

									   * 用于excel操作，表格初始化信息 

									   * 

									   * @author: 季乐 

									   * @date: 2013-12-2 下午1:43:04 

									   * @since: 1.0 

									   */

									  public class sheetinfo { 

									    /** 标题所在的行，起始行是0，不是1 */

									    private int rowtitle = 1; 

									    /** 需要读取数据字段中文名对应的entity属性名 */

									    private map<string, string[]> columnsmapping; 

									    /** 需要读取数据的sheet的名字 */

									    public list<string> sheetnames = new arraylist<string>(); 

									    public sheetinfo(list<string> sheetnames) { 

									      // 假如没有定义sheetnames，则给予其默认值”sheet1“ 

									      if (null == sheetnames || sheetnames.size() == 0) { 

									        this.sheetnames.add("sheet1"); 

									      } else { 

									        this.sheetnames = sheetnames; 

									      } 

									    } 

									    public sheetinfo() { 

									      // 假如没有定义sheetnames，则给予其默认值”sheet1“ 

									      if (null == sheetnames || sheetnames.size() == 0) { 

									        sheetnames.add("sheet1"); 

									      } 

									    } 

									    public int getrowtitle() { 

									      return rowtitle; 

									    } 

									    public void setrowtitle(int rowtitle) { 

									      this.rowtitle = rowtitle; 

									    } 

									    public map<string, string[]> getcolumnsmapping() { 

									      return columnsmapping; 

									    } 

									    public void setcolumnsmapping(map<string, string[]> columnsmapping) { 

									      this.columnsmapping = columnsmapping; 

									    } 

									    public list<string> getsheetnames() { 

									      return sheetnames; 

									    } 

									    public void setsheetnames(list<string> sheetnames) { 

									      this.sheetnames = sheetnames; 

									    } 

									  } 

									  /** 

									   * 

									   * 内部枚举类 

									   * 

									   * @author: 季乐 

									   * @date: 2013-12-2 下午1:43:24 

									   * @since: 1.0 

									   */

									  public enum regexpenum { 

									    /** 不为空 */

									    notempty("不能为空", "(?! +$).+"), 

									    /** 必须为数字 */

									    isnumber("必须为数字", "\\d*"), 

									    /** 不为空并且为数字 */

									    notempty_isnumber("不能为空且必须为数字", "\\d+"); 

									    /** text */

									    private string text; 

									    /** level */

									    private string value; 

									    public string gettext() { 

									      return text; 

									    } 

									    public string getvalue() { 

									      return value; 

									    } 

									    private regexpenum(string text, string value) { 

									      this.text = text; 

									      this.value = value; 

									    } 

									  } 

									  /** 

									   * 将html转为 richtextstring 

									   * 

									   * @param wb 

									   *      hssfworkbook 

									   * @param html 

									   *      html 

									   * @return richtextstring 

									   */

									  @suppresswarnings("unused") 

									  private static richtextstring processhtml(hssfworkbook wb, string html) { 

									    richtextstring rt = null; 

									    htmleditorkit kit = new htmleditorkit(); 

									    htmldocument doc = (htmldocument) kit.createdefaultdocument(); 

									    try { 

									      kit.inserthtml(doc, doc.getlength(), html, 0, 0, null); 

									      stringbuffer sb = new stringbuffer(); 

									      for (int lines = 0, lastpos = -1; lastpos < doc.getlength(); lines++) { 

									        // if (lines > 0) { 

									        // sb.append('\n'); 

									        // } 

									        element line = doc.getparagraphelement(lastpos + 1); 

									        lastpos = line.getendoffset(); 

									        for (int elidx = 0; elidx < line.getelementcount(); elidx++) { 

									          final element frag = line.getelement(elidx); 

									          string subtext = doc.gettext(frag.getstartoffset(), frag.getendoffset() - frag.getstartoffset()); 

									          if (!subtext.equals("\n")) { 

									            sb.append(subtext); 

									          } 

									        } 

									      } 

									      creationhelper ch = wb.getcreationhelper(); 

									      rt = ch.createrichtextstring(sb.tostring()); 

									      for (int lines = 0, lastpos = -1; lastpos < doc.getlength(); lines++) { 

									        element line = doc.getparagraphelement(lastpos + 1); 

									        lastpos = line.getendoffset(); 

									        for (int elidx = 0; elidx < line.getelementcount(); elidx++) { 

									          final element frag = line.getelement(elidx); 

									          font font = getfontfromfragment(wb, frag); 

									          rt.applyfont(frag.getstartoffset() + lines, frag.getendoffset() + lines, font); 

									        } 

									      } 

									    } catch (exception e) { 

									      log.warn(e.getmessage()); 

									      // e.printstacktrace(); 

									    } 

									    return rt; 

									  } 

									  /** 

									   * 获取字体 

									   * 

									   * @param wb 

									   *      workbook 

									   * @param frag 

									   *      frag 

									   * @return font 

									   * @throws exception 

									   *       exception 

									   */

									  private static font getfontfromfragment(workbook wb, element frag) throws exception { 

									    font font = wb.createfont(); 

									    final attributeset as = frag.getattributes(); 

									    final enumeration<?> ae = as.getattributenames(); 

									    while (ae.hasmoreelements()) { 

									      final object attrib = ae.nextelement(); 

									      if (css.attribute.color.equals(attrib)) { 

									        field f = as.getattribute(attrib).getclass().getdeclaredfield("c"); 

									        f.setaccessible(true); 

									        color c = (color) f.get(as.getattribute(attrib)); 

									        if (font instanceof xssffont) { 

									          ((xssffont) font).setcolor(new xssfcolor(c)); 

									        } else if (font instanceof hssffont && wb instanceof hssfworkbook) { 

									          hssfpalette pal = ((hssfworkbook) wb).getcustompalette(); 

									          hssfcolor col = pal.findsimilarcolor(c.getred(), c.getgreen(), c.getblue()); 

									          ((hssffont) font).setcolor(col.getindex()); 

									        } 

									      } else if (css.attribute.font_weight.equals(attrib)) { 

									        if ("bold".equals(as.getattribute(attrib).tostring())) { 

									          font.setboldweight(font.boldweight_bold); 

									        } 

									      } 

									    } 

									    return font; 

									  } 

									  public static int getmergedregionrow(sheet sheet, cell cell) { 

									    // 得到一个sheet中有多少个合并单元格 

									    int sheetmergercount = sheet.getnummergedregions(); 

									    for (int i = 0; i < sheetmergercount; i++) { 

									      // 得出具体的合并单元格 

									      cellrangeaddress ca = sheet.getmergedregion(i); 

									      // 得到合并单元格的起始行, 结束行, 起始列, 结束列 

									      int firstc = ca.getfirstcolumn(); 

									      int lastc = ca.getlastcolumn(); 

									      int firstr = ca.getfirstrow(); 

									      int lastr = ca.getlastrow(); 

									      // 判断该单元格是否在合并单元格范围之内, 如果是, 则返回 true 

									      if (cell.getcolumnindex() <= lastc && cell.getcolumnindex() >= firstc) { 

									        if (cell.getrowindex() == firstr) { 

									          return lastr - firstr; 

									        } 

									      } 

									    } 

									    return 0; 

									  } 

									  /** 

									   * 获取合并单元格的值 

									   * 

									   * @param sheet 

									   * @param row 

									   * @param column 

									   * @return 

									   */

									  public static string getmergedregionvalue(sheet sheet, int row, int column) { 

									    int sheetmergecount = sheet.getnummergedregions(); 

									    for (int i = 0; i < sheetmergecount; i++) { 

									      cellrangeaddress ca = sheet.getmergedregion(i); 

									      int firstcolumn = ca.getfirstcolumn(); 

									      int lastcolumn = ca.getlastcolumn(); 

									      int firstrow = ca.getfirstrow(); 

									      int lastrow = ca.getlastrow(); 

									      if (row >= firstrow && row <= lastrow) { 

									        if (column >= firstcolumn && column <= lastcolumn) { 

									          row frow = sheet.getrow(firstrow); 

									          cell fcell = frow.getcell(firstcolumn); 

									          return getcellvalue(fcell); 

									        } 

									      } 

									    } 

									    return null; 

									  } 

									  /** 

									   * 判断指定的单元格是否是合并单元格 

									   * 

									   * @param sheet 

									   * @param row 

									   *      行下标 

									   * @param column 

									   *      列下标 

									   * @return 

									   */

									  public static boolean ismergedregion(sheet sheet, int row, int column) { 

									    int sheetmergecount = sheet.getnummergedregions(); 

									    for (int i = 0; i < sheetmergecount; i++) { 

									      cellrangeaddress range = sheet.getmergedregion(i); 

									      int firstcolumn = range.getfirstcolumn(); 

									      int lastcolumn = range.getlastcolumn(); 

									      int firstrow = range.getfirstrow(); 

									      int lastrow = range.getlastrow(); 

									      if (row >= firstrow && row <= lastrow) { 

									        if (column >= firstcolumn && column <= lastcolumn) { 

									          return true; 

									        } 

									      } 

									    } 

									    return false; 

									  } 

									  /** 

									   * 判断sheet页中是否含有合并单元格 

									   * 

									   * @param sheet 

									   * @return 

									   */

									  @suppresswarnings("unused") 

									  private boolean hasmerged(sheet sheet) { 

									    return sheet.getnummergedregions() > 0 ? true : false; 

									  } 

									  /** 

									   * 合并单元格 

									   * 

									   * @param sheet 

									   * @param firstrow 

									   *      开始行 

									   * @param lastrow 

									   *      结束行 

									   * @param firstcol 

									   *      开始列 

									   * @param lastcol 

									   *      结束列 

									   */

									  @suppresswarnings("unused") 

									  private void mergeregion(sheet sheet, int firstrow, int lastrow, int firstcol, int lastcol) { 

									    sheet.addmergedregion(new cellrangeaddress(firstrow, lastrow, firstcol, lastcol)); 

									  } 

									  /** 

									   * 获取单元格的值 

									   * 

									   * @param cell 

									   * @return 

									   */

									  public static string getcellvalue(cell cell) { 

									    if (cell == null) 

									      return ""; 

									    if (cell.getcelltype() == cell.cell_type_string) { 

									      return cell.getstringcellvalue(); 

									    } else if (cell.getcelltype() == cell.cell_type_boolean) { 

									      return string.valueof(cell.getbooleancellvalue()); 

									    } else if (cell.getcelltype() == cell.cell_type_formula) { 

									      return cell.getcellformula(); 

									    } else if (cell.getcelltype() == cell.cell_type_numeric) { 

									      if (hssfdateutil.iscelldateformatted(cell)) {// 处理日期格式、时间格式 

									        simpledateformat sdf = new simpledateformat("yyyy-mm-dd"); 

									        date date = cell.getdatecellvalue(); 

									        return string.valueof(sdf.format(date)); 

									      } else if (cell.getcellstyle().getdataformat() == 31) { 

									        // 处理自定义日期格式：yy年mm月dd日(通过判断单元格的格式id解决，id的值是31) 

									        simpledateformat sdf = new simpledateformat("yyyy-mm-dd"); 

									        double value = cell.getnumericcellvalue(); 

									        date date = org.apache.poi.ss.usermodel.dateutil 

									            .getjavadate(value); 

									        return string.valueof(sdf.format(date)); 

									      } else { 

									        double value = cell.getnumericcellvalue(); 

									        cellstyle style = cell.getcellstyle(); 

									        decimalformat format = new decimalformat(); 

									        return string.valueof(format.format(value)); 

									      } 

									    } 

									    return ""; 

									  } 

									  public static string filterstr(string str) { 

									    str = str.replace(string.valueof((char) 160), "").replace(string.valueof((char) 65279), ""); 

									    str = str.trim(); 

									    return str; 

									  } 

									  public static void main(string[] args) { 

									    system.out.println(convertrgbtohex(hssfcolor.yellow.triplet)); 

									    system.out.println(new xssfcolor(color.yellow).getargbhex().substring(2)); 

									    system.err.println(htmlutils.htmlunescape("汇总（电视&盒子&路由器）")); 

									  } 

									  static string convertrgbtohex(short[] rgb) {   

									    int r= rgb[0],g=rgb[1],b=rgb[2]; 

									    string rfstring, rsstring, gfstring, gsstring, bfstring, bsstring, result; 

									    int red, green, blue; 

									    int rred, rgreen, rblue; 

									    red = r / 16; 

									    rred = r % 16; 

									    if (red == 10) rfstring = "a"; 

									    else if (red == 11) rfstring = "b"; 

									    else if (red == 12) rfstring = "c"; 

									    else if (red == 13) rfstring = "d"; 

									    else if (red == 14) rfstring = "e"; 

									    else if (red == 15) rfstring = "f"; 

									    else rfstring = string.valueof(red); 

									    if (rred == 10) rsstring = "a"; 

									    else if (rred == 11) rsstring = "b"; 

									    else if (rred == 12) rsstring = "c"; 

									    else if (rred == 13) rsstring = "d"; 

									    else if (rred == 14) rsstring = "e"; 

									    else if (rred == 15) rsstring = "f"; 

									    else rsstring = string.valueof(rred); 

									    rfstring = rfstring + rsstring; 

									    green = g / 16; 

									    rgreen = g % 16; 

									    if (green == 10) gfstring = "a"; 

									    else if (green == 11) gfstring = "b"; 

									    else if (green == 12) gfstring = "c"; 

									    else if (green == 13) gfstring = "d"; 

									    else if (green == 14) gfstring = "e"; 

									    else if (green == 15) gfstring = "f"; 

									    else gfstring = string.valueof(green); 

									    if (rgreen == 10) gsstring = "a"; 

									    else if (rgreen == 11) gsstring = "b"; 

									    else if (rgreen == 12) gsstring = "c"; 

									    else if (rgreen == 13) gsstring = "d"; 

									    else if (rgreen == 14) gsstring = "e"; 

									    else if (rgreen == 15) gsstring = "f"; 

									    else gsstring = string.valueof(rgreen); 

									    gfstring = gfstring + gsstring; 

									    blue = b / 16; 

									    rblue = b % 16; 

									    if (blue == 10) bfstring = "a"; 

									    else if (blue == 11) bfstring = "b"; 

									    else if (blue == 12) bfstring = "c"; 

									    else if (blue == 13) bfstring = "d"; 

									    else if (blue == 14) bfstring = "e"; 

									    else if (blue == 15) bfstring = "f"; 

									    else bfstring = string.valueof(blue); 

									    if (rblue == 10) bsstring = "a"; 

									    else if (rblue == 11) bsstring = "b"; 

									    else if (rblue == 12) bsstring = "c"; 

									    else if (rblue == 13) bsstring = "d"; 

									    else if (rblue == 14) bsstring = "e"; 

									    else if (rblue == 15) bsstring = "f"; 

									    else bsstring = string.valueof(rblue); 

									    bfstring = bfstring + bsstring; 

									    result = rfstring + gfstring + bfstring; 

									    return result; 

									  } 

									}

再看下from.jsp页面

				?

									<body> 

									<div> 

									  <form id="mainform" action="${ctx}/bom/ciscaseaction/${action}" method="post" enctype="multipart/form-data"> 

									    <input type="file" name="file"/> 

									    <a href="${ctx}/static/案由导入模板.xls" rel="external nofollow" >下载模板</a> 

									  </form> 

									</div> 

									<script type="text/javascript"> 

									  $(function(){ 

									    $('#mainform').form({ 

									      onsubmit: function(){ 

									        var isvalid = $(this).form('validate'); 

									        return isvalid; // 返回false终止表单提交 

									      }, 

									      success:function(data){ 

									        successtip(data,dg,d); 

									      } 

									    }); 

									  }); 

									</script> 

									</body>

主界面jsp

复制代码代码如下:

	<a href="javascript(0)" rel="external nofollow" class="easyui-linkbutton" plain="true" iconcls="icon-standard-application-go" onclick="importaction()">导入</a> 

				?

									//导入 

									function importaction(){ 

									  d=$("#dlg").dialog({ 

									    title: '案由导入', 

									    width: 500, 

									    height: 500, 

									    href:'${ctx}/bom/ciscaseaction/importaction/', 

									    maximizable:true, 

									    modal:true, 

									    buttons:[{ 

									      text:'导入', 

									      handler:function(){ 

									        $('#mainform').submit(); 

									      } 

									    },{ 

									      text:'取消', 

									      handler:function(){ 

									        d.panel('close'); 

									      } 

									    }] 

									  }); 

									}

页面点击的效果是，点击导入会跳入from.jsp页面

再看controller层

				?

									/** 

									   * 导入页面 

									   */

									  @requestmapping(value = "importaction", method = requestmethod.get) 

									  public string importform( model model) { 

									    model.addattribute("action", "import"); 

									    return "system/ciscaseactionimoportform"; 

									  } 

									  /** 

									   * 导入 

									   */

									  @requestmapping(value = "import", method = requestmethod.post) 

									  @responsebody

									  public string importform(@requestparam("file") multipartfile multipartfile, model model) throws exception { 

									    ciscaseactionservice.upload(multipartfile); 

									    return "success"; 

									}

service层

				?

									/** 

									 * 导入案由 

									*/

									    @suppresswarnings({ "rawtypes", "unchecked" }) 

									    public void upload(multipartfile multipartfile) throws exception { 

									      inputstream inputstream = multipartfile.getinputstream(); 

									      exceltools exceltools = new exceltools(); 

									      exceltools.sheetinfo sheetinfo = exceltools.new sheetinfo(); 

									      sheetinfo.setrowtitle(0); 

									      map columns = new hashmap(); 

									      columns.put("columns",new string[]{"name:案由名称", "violatelaw:违反法律", "punishbasis:处罚依据"}); 

									      sheetinfo.setcolumnsmapping(columns); 

									      list<hashmap<string, string>> maplist = exceltools.getexcel(inputstream, sheetinfo); 

									      for (int i = 0; i < maplist.size(); i++){ 

									        hashmap<string, string> map = maplist.get(i); 

									        string name = map.get("name"); 

									        if (stringutils.isempty(name)){ 

									          throw new businessexception("第" + (i+2) + "案由名称不能为空"); 

									        } 

									        string violatelaw = map.get("violatelaw"); 

									        string punishbasis = map.get("punishbasis"); 

									        ciscaseaction ciscaseaction=new ciscaseaction(); 

									        ciscaseaction.setname(name); 

									        ciscaseaction.setviolatelaw(violatelaw); 

									        ciscaseaction.setpunishbasis(punishbasis);  

									        this.insert(ciscaseaction); //调用同一层的插入方法  

									      } 

									    }