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;    } } 

<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> 

//导入 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');       }     }]   }); } 

/**    * 导入页面    */   @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"; } 

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