本文实例为大家分享了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" 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); //调用同一层的插入方法 } } 以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持武林网。