JAVA中excel表数据的批量导入

「爱情、让人受尽委屈。」 2021-07-09 13:30 474阅读 0赞

首先看下工具类：

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操作，表格初始化信息
    	 * 
    	 */
    	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;
    		}
    	}
    
    	/**
    	 * 
    	 * 内部枚举类
    	 * 
    	 */
    	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">下载模板</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

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