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

以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持编程小技巧。

代码技巧

转载请关注公众号:代码技巧 回复:授权

本文链接地址:https://www.oudahe.com/p/53892/