This article shares the batch import of excel table data in Java for your reference. The specific content is as follows: 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(); /** * Export excel * <p> * Usage: <br> * <code> List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();<br> * is = ExcelTools.exportXLS(dataList,new String[] {"createTime:Date","name:Name", "sex:Gender", "remark:Remark"}); * </code> * * @param collect * Data collection to be exported* @param header * Column to be exported* @return InputStream Return file stream*/ public static InputStream exportXLS(Collection<Map<String, Object>> collect, String[] header) { ByteArrayOutputStream out = new ByteArrayOutputStream(); HSSFWorkbook book = new HSSFWorkbook(); try { // Add a sheet HSSFSheet sheet = book.createSheet("Sheet1"); // Define the set of column names to be exported Set<String> columns = new HashSet<String>(); // Set the cell background color HSSFCellStyle cellStyle = book.createCellStyle(); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setFillForegroundColor(new HSSFColor.YELLOW().getIndex()); // Generate header HSSFRow row = sheet.createRow(0); HSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue("sequence"); // Column number starts from 1 int n = 1; // parse header string for (String str : header) { String[] arr = str.split(":"); columns.add(n + "," + arr[0]);// Add the field name to export and bind to column number n cell = row.createCell(n); cell.setCellStyle(cellStyle); cell.setCellValue(arr[1]); n++; } // Generate data rows starting from 1, 0 is the header int i = 1; // Generate data rows for (Map<String, Object> map : collect) { HSSFRow dataRow = sheet.createRow(i); // Generate the sequence number dataRow.createCell(0).setCellValue(i); // Generate other columns for (String column : columns) { // Use commas to obtain the field name, [0] is the column number to correspond to the header header. String columnName = column.split(",")[1]; // Generate the sequence number column cell = dataRow.createCell(Integer.parseInt(column.split(",")[0])); String value = ""; value = map.get(columnName) + ""; // Convert to "" 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()); } /** * Get excel file data<br> * Usage: <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:Product Name", * "prodSpec:Specification", "collectPrice:Price:" + {@link RegExpEnum} * RegExpEnum.NOTEMPTY_ISNUMBER, "priceUnit:Unit", "collectMarket:quotation market", * "prodLevel:level" }); <br> * Map<String, List> data = ExcelTools.getExcel(new File(path), sheetInfo); * * @param * * @param sheetInfo * Initialization information* @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>(); // Column verification expression map List<String> errMsg = new ArrayList<String>(); int errNum = 0;// Total number of errors int errLimit = 10;// Limit the number of error prompts/** Used to store all contents that Excel reads according to the specified rules*/ Map excelInfo = new HashMap(); Workbook book = null; try { if (excelType.equals("xls")) { book = new HSSFWorkbook(in); //throw new BusinessException("Excel version is too low, please use version 2007 or above (extension: xlsx)"); } else { book = new XSSFWorkbook(in); } } catch (OutOfMemoryError e) { throw new RuntimeException("The current excel file is too large, please check whether there is invalid empty data in each sheet table, including row and column data with format and wireframe, please delete these invalid data! Normal excel file size [within 1M]"); } // checkTitle(book, sheetInfo); // Get the number of worksheets int sheetNum = sheetInfo.getSheetNames().size(); // Loop all worksheets and read the data inside for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) { // Get the current worksheet object 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) {// If the verification expression is not empty, add it to the map to validateMap.put(arr[1], arr[2]); } } Sheet sheet = book.getSheet(sheetName); if (null == sheet) { throw new RuntimeException(String.format("Failed to obtain the table, please confirm whether the Sheet "%s" exists in excel", sheetName)); } // Used to store the data content in the worksheet List sheetList = new ArrayList(); // Get the number of rows in the current table int rows = sheet.getLastRowNum(); // Get the number of columns in the current table int columns = sheet.getRow(sheetInfo.getRowTitle()).getLastCellNum(); if (rows <= sheetInfo.getRowTitle()) {// If the current table does not have the required data, continue the next loop; } // Get the current worksheet title content List<String> titleList = new ArrayList<String>(); // Loop every cell in each row and read the value in the cell 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("Table header read error, currently set to "th" + (sheetInfo.getRowTitle() + 1) + "row<br/>The content of the table header is: " + titleRow + ", please check whether it is correct. If there is an exception, you can delete the header format and repaste the header!"); } } // System.out.println(titleList); // Verify the header String[] titles = sheetInfo.getColumnsMapping().get(sheetName); for (String s : titles) { String[] colArr = s.split(":"); // If the header in Excel table is missing this field boolean include = false; for (String t : titleList) { if (StringUtils.deleteWhitespace(t).equalsIgnoreCase(colArr[1])) { include = true; break; } } if (!include) { throw new RuntimeException("【" + colArr[1] + "]' column does not exist! The current Excel header: " + titleList); } } // Start looping each row, reading the value of each row, starting from the line below the title 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's [%d]th behavior is empty, please check!", i + 1)); } for (int j = 0; j < columns; j++) {// Loop every cell in each row and read the value in the cell String columnTitle = titleList.get(j); if ("".equals(columnTitle)) { continue; } else { Cell cell = dataRow.getCell(j); String value = ""; String columnMapping = ""; // The entity attribute name corresponding to the cell column is 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(); //Cell background color 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)) { // Verify regular expression 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("th" + i + "line: [" + columnTitle + "】Data is:'" + value.trim() + "' Does not match! [" + 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("The data template you imported is too much format error (total" + errNum + "items), please carefully check whether the template data is correct!"); } throw new RuntimeException(errMsg.toString().replaceAll("//[|//]", "")); } // if (true) throw new RuntimeException("test"); return excelInfo; } public static List<HashMap<String, String>> getExcel(InputStream in, SheetInfo sheetInfo) throws Exception { Map<String, String> columnsMap = new HashMap<String, String>(); // Column validation expression map Map<String, String> validateMap = new HashMap<String, String>(); List<String> errMsg = new ArrayList<String>(); int errNum = 0;// Total number of errors int errLimit = 10;// Limit the number of error prompts for (String mapstr : sheetInfo.getColumnsMapping().get("columns")) { String[] arr = mapstr.split(":"); columnsMap.put(arr[1], arr[0]); if (arr.length == 3) {// If the verification expression is not empty, add it to the map to validateMap.put(arr[1], arr[2]); } } /** Used to store all contents that Excel reads according to the specified rules*/ List excelInfo = new ArrayList(); Workbook book = WorkbookFactory.create(in); // checkTitle(book, sheetInfo); // Get the number of worksheets int sheetNum = book.getNumberOfSheets(); // Loop all worksheets and read the data in it for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) { // Get the current worksheet object Sheet sheet = book.getSheetAt(sheetIndex); // Used to store the data content in the worksheet// List sheetList = new ArrayList(); // Get the number of rows int int rows = sheet.getLastRowNum(); // Get the number of columns in the current table Row titleRow = sheet.getRow(sheetInfo.getRowTitle()); if (titleRow == null){ throw new BusinessException("The file format is incorrect, please reselect or download the template"); } int columns = titleRow.getLastCellNum(); if (columns != sheetInfo.getColumnsMapping().get("columns").length){ throw new BusinessException("The file format is incorrect, please reselect or download the template"); } if (rows <= sheetInfo.getRowTitle()) {// If the current table does not have the required data, continue to loop the next time throw new BusinessException("The file format is incorrect, please reselect or download the template"); } // Get the current worksheet title content List<String> titleList = new ArrayList<String>(); // Loop every cell in each row and read the value in the cell for (int jj = 0; jj < columns; jj++) { titleList.add(titleRow.getCell(jj).getStringCellValue()); } // Verify the table header String[] titles = sheetInfo.getColumnsMapping().get("columns"); for (String s : titles) { // If the table header in the Excel table is missing the field if (!titleList.contains(s.split(":")[1])) { // errMsg.add("'" + sheet.getSheetName() + "'Table'" + s // + "'Column does not exist!"); throw new BusinessException("The file format is incorrect, please reselect or download the template"); } } // Start looping every row, reading the value of each row, and reading from the line below the title 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++) {// Loop every cell in each row and read the value in the cell String columnTitle = titleList.get(j); if ("".equals(columnTitle)) { continue; } else { Cell cell = dataRow.getCell(j); String value = getCellValue(cell); // The entity attribute name corresponding to the cell column String columnMapping = columnsMap.get(columnTitle); String validateReg = ""; String validateRegMsg = ""; if (null != validateMap.get(columnTitle)) { // Verify the regular expression 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("Th" + i + "Line: [" + columnTitle + "] The data is:'" + value.trim() + "' Does not match! [" + 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("The data template you imported is too much format error (total" + errNum + "items), please check carefully whether the template data is correct!"); // } throw new RuntimeException(errMsg.toString().replaceAll("//[|//]", "")); } return excelInfo; } /** * * Used for excel operations, table initialization information* * @author: Ji Le* @date: 2013-12-2 1:43:04 pm * @since: 1.0 */ public class SheetInfo { /** The line where the title is located, the starting line is 0, not 1 */ private int rowTitle = 1; /** The entity attribute name corresponding to the Chinese name of the data field needs to be read */ private Map<String, String[]> columnsMapping; /** The name of the sheet that needs to be read */ public List<String> sheetNames = new ArrayList<String>(); public SheetInfo(List<String> sheetNames) { // If sheetNames is not defined, the default value is given "Sheet1" if (null == sheetNames || sheetNames.size() == 0) { this.sheetNames.add("Sheet1"); } else { this.sheetNames = sheetNames; } } public SheetInfo() { // If sheetNames is not defined, the default value is given "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; } } /** * * Internal enum class* * @author: Jile* @date: 2013-12-2 1:43:24 pm * @since: 1.0 */ public enum RegExpEnum { /** Not empty*/ NOTEMPTY("can't be empty", "(?! +$).+"), /** Must be a number*/ ISNUMBER("can't be a number", "//d*"), /** Not empty and a number*/ NOTEMPTY_ISNUMBER("can't be empty and a number", "//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; } } /** * Convert html to RichTextString * * @param wb * HSSFWorkbook * @param 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 fragment = line.getElement(elIdx); String subtext = doc.getText(frag.getStartOffset(), fragment.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 fragment = line.getElement(elIdx); Font font = getFontFromFragment(wb, fragment); rt.applyFont(frag.getStartOffset() + lines, fragment.getEndOffset() + lines, font); } } } catch (Exception e) { log.warn(e.getMessage()); // e.printStackTrace(); } return rt; } /** * Get font* * @param wb * Workbook * @param frag * frag * @return Font * @throws Exception */ private static Font getFontFromFragment(Workbook wb, Element fragment) throws Exception { Font font = wb.createFont(); final AttributeSet as = fragment.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) { // Get how many merged cells there are int sheetmergerCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetmergerCount; i++) { // Get the specific merged cell CellRangeAddress ca = sheet.getMergedRegion(i); // Get the start row of the merged cell, end row, start column, End column int firstC = ca.getFirstColumn(); int lastC = ca.getLastColumn(); int firstR = ca.getFirstRow(); int lastR = ca.getLastRow(); // Determine whether the cell is within the range of merged cells. If so, it returns true if (cell.getColumnIndex() <= lastC && cell.getColumnIndex() >= firstC) { if (cell.getRowIndex() == firstR) { return lastR - firstR; } } } return 0; } /** * Get the value of the merged cell * * @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; } /** * Determine whether the specified cell is a merged cell* * @param sheet * @param row * Row subscript* @param column * Column subscript* @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; } /** * Determine whether the sheet page contains merged cells* * @param sheet * @return */ @SuppressWarnings("unused") private boolean hasMerged(Sheet sheet) { return sheet.getNumMergedRegions() > 0 ? true : false; } /** * Merge cells* * @param sheet * @param firstRow * Start row* @param lastRow * End row* @param firstCol * Start column* @param lastCol * End column*/ @SuppressWarnings("unused") private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol)); } /** * Get the value of the cell* * @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)) {// Handle date format and time format SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); Date date = cell.getDateCellValue(); return String.valueOf(sdf.format(date)); } else if (cell.getCellStyle().getDataFormat() == 31) { // Process custom date format: yy year mm month dd date (solved by judging the format id of the cell, the value of id is 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("Summary (TV & Box & Router)")); } 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; } } Look at the from.jsp page again
<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/Case import template.xls" rel="external nofollow" >Download template</a> </form> </div> <script type="text/javascript"> $(function(){ $('#mainform').form({ onSubmit: function(){ var isValid = $(this).form('validate'); return isValid; // Return false to terminate form submission}, success:function(data){ successTip(data,dg,d); } }); }); </script> </body>Main interface jsp
Copy the code as follows: <a href="javascript(0)" rel="external nofollow" plain="true" iconCls="icon-standard-application-go" onclick="importAction()">Import</a>
//Import function importAction(){ d=$("#dlg").dialog({ title: 'Case import', width: 500, height: 500, href:'${ctx}/bom/ciscaseaction/importAction/', maximizable:true, modal:true, buttons:[{ text:'Import', handler:function(){ $('#mainform').submit(); } },{ text:'Cancel', handler:function(){ d.panel('close'); } }] }); }The effect of clicking on the page is that clicking on import will jump to the from.jsp page
Look at the controller layer again
/** * Import page*/ @RequestMapping(value = "importAction", method = RequestMethod.GET) public String importForm( Model model) { model.addAttribute("action", "import"); return "system/cisCaseActionImoportForm"; } /** * Import*/ @RequestMapping(value = "import", method = RequestMethod.POST) @ResponseBody public String importForm(@RequestParam("file") MultipartFile multipartFile, Model model) throws Exception { cisCaseActionService.upload(multipartFile); return "success"; } Service layer
/** * Import case*/ @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:Case name", "violateLaw:Break of law", "punishBasis: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("Th" + (i+2) + "The name of the case cannot be empty"); } String violationLaw = map.get("violateLaw"); String punishmentBasis = map.get("punishBasis"); CisCaseAction cisCaseAction=new CisCaseAction(); cisCaseAction.setName(name); cisCaseAction.setViolateLaw(violateLaw); cisCaseAction.setPunishBasis(punishBasis); this.insert(cisCaseAction); //Calling the insertion method of the same layer} } The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.