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的值是3 1) 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); //調用同一層的插入方法} }

以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持武林網。