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); //调用同一层的插入方法  
      } 
    } 

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。