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

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