Recently, the company's operation platform needs to upload Excel files and analyze and import them into the database. After the development, an unexpected production bug appears. The following are the specific reasons:
1. When using POI to parse excel, by default, if there is no data in Excel cell and cell Style has no border, it is null, so it only determines whether the cell is null
As a result, if Excel cell style has a border and the cell content is null or empty, it will be parsed normally. The specific reason for the problem is that I added notes to the following code snippets. There are two snippets of code. xlsx and. xls. The specific content is the same
Solution: I searched the API and found that XSSFRow can get the style of the current cell, so there must be a way to operate the style,
XSSFCellStyle cellStyle = xssfRow.getCell(0).getCellStyle(); / / use XSSFCellStyle to get the current cell style of the current row
Then do something about style
cellStyle.setBorderTop(BorderStyle.NONE); / / remove the cell border style above
cellStyle.setBorderBottom(BorderStyle.NONE); / / remove the cell border style below
cellStyle.setBorderLeft(BorderStyle.NONE); / / remove the left cell border style
cellStyle.setBorderRight(BorderStyle.NONE); / / remove the border style of the right cell
This solves the problem of border
Just in case, I also made some if judgments
Some people say that you are a little redundant. You can directly determine whether the map of the loading parameters below is empty for a long time. Everyone has different ways of thinking to solve this problem. It's normal. I always want to study this problem.
I just met the problem and tried to solve it. It's also a kind of promotion. The code is a small demo that I wrote temporarily. Some places are messy. Don't spray.
Code snippet:
1 public static List<Map<String,Object>> readXls(InputStream is) throws IOException{ 2 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is); 3 List<Map<String,Object>> readMap = new ArrayList<>(); 4 // Read the Sheet 5 for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) { 6 HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet); 7 if (hssfSheet == null) { 8 continue; 9 } 10 // Read the Row 11 for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { 12 HashMap paramMap = new HashMap(); 13 HSSFRow xssfRow = hssfSheet.getRow(rowNum); 14 if (xssfRow != null) { 15 if(null!=xssfRow.getCell(0) && xssfRow.getCell(0).getStringCellValue().length()>0 && xssfRow.getCell(0).getStringCellValue().trim().length()>0){ 16 logger.info("============productCode==========={}",xssfRow.getCell(0)); 17 HSSFCellStyle cellStyle = xssfRow.getCell(0).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 18 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 19 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 20 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 21 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 22 paramMap.put("productCode", getValue(xssfRow.getCell(0)).trim()); 23 } 24 if(null!=xssfRow.getCell(1) && xssfRow.getCell(1).getStringCellValue().length()>0 && xssfRow.getCell(1).getStringCellValue().trim().length()>0) { 25 logger.info("============rejectCode==========={}",xssfRow.getCell(1)); 26 HSSFCellStyle cellStyle = xssfRow.getCell(1).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 27 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 28 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 29 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 30 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 31 paramMap.put("rejectCode", getValue(xssfRow.getCell(1)).trim()); 32 } 33 if(null!=xssfRow.getCell(2) && xssfRow.getCell(2).getStringCellValue().length()>0 && xssfRow.getCell(2).getStringCellValue().trim().length()>0) { 34 logger.info("============rejectDescFront==========={}",xssfRow.getCell(2)); 35 HSSFCellStyle cellStyle = xssfRow.getCell(2).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 36 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 37 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 38 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 39 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 40 paramMap.put("rejectDescFront", getValue(xssfRow.getCell(2)).trim()); 41 42 } 43 if(null!=xssfRow.getCell(3) && xssfRow.getCell(3).getStringCellValue().length()>0 && xssfRow.getCell(3).getStringCellValue().trim().length()>0) { 44 logger.info("============rejectDesc==========={}",xssfRow.getCell(3)); 45 HSSFCellStyle cellStyle = xssfRow.getCell(3).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 46 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 47 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 48 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 49 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 50 paramMap.put("rejectDesc", getValue(xssfRow.getCell(3)).trim()); 51 } 52 logger.info("paramMap======{}",paramMap); 53 if(!paramMap.isEmpty()){ 54 readMap.add(paramMap); 55 } 56 } 57 } 58 } 59 if (hssfWorkbook != null) { 60 hssfWorkbook.close(); 61 } 62 return readMap; 63 }
1 public static List<Map<String,Object>> readXlsx(InputStream is) throws IOException { 2 XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is); 3 ExcelData excelData = null; 4 List<Map<String,Object>> readMap = new ArrayList<>(); 5 String regex="^[+]?\\d+(\\.\\d+)?$"; 6 // Read the Sheet 7 for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) { 8 XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet); 9 if (xssfSheet == null) { 10 continue; 11 } 12 // Read the Row 13 for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) { 14 HashMap paramMap = new HashMap(); 15 XSSFRow xssfRow = xssfSheet.getRow(rowNum); 16 if (xssfRow != null) { 17 excelData = new ExcelData(); 18 if(null!=getValue(xssfRow.getCell(0)) && StringUtils.isNotBlank(xssfRow.getCell(0).toString())){ 19 XSSFCellStyle cellStyle = xssfRow.getCell(0).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 20 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 21 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 22 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 23 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 24 excelData.setCustomerId(getValue(xssfRow.getCell(0)).trim()); 25 paramMap.put("productCode",getValue(xssfRow.getCell(0)).trim());//Get data in cell 26 logger.info("productCode======={}",paramMap.get("productCode").toString()); 27 } 28 if(null!=getValue(xssfRow.getCell(1)) && StringUtils.isNotBlank(xssfRow.getCell(1).toString())){ 29 XSSFCellStyle cellStyle = xssfRow.getCell(1).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 30 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 31 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 32 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 33 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 34 excelData.setTemplateName(getValue(xssfRow.getCell(1)).trim()); 35 paramMap.put("rejectCode", getValue(xssfRow.getCell(1)).trim()); 36 logger.info("rejectCode======={}", paramMap.get("rejectCode").toString()); 37 } 38 if(null!=getValue(xssfRow.getCell(2)) && StringUtils.isNotBlank(xssfRow.getCell(2).toString())){ 39 XSSFCellStyle cellStyle = xssfRow.getCell(2).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 40 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 41 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 42 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 43 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 44 excelData.setTemplateName(getValue(xssfRow.getCell(2)).trim()); 45 paramMap.put("rejectDescFront", getValue(xssfRow.getCell(2)).trim()); 46 logger.info("rejectDescFront======={}", paramMap.get("rejectDescFront").toString()); 47 } 48 if(null!=getValue(xssfRow.getCell(3)) && StringUtils.isNotBlank(xssfRow.getCell(3).toString())){ 49 XSSFCellStyle cellStyle = xssfRow.getCell(3).getCellStyle();//use XSSFCellStyle Gets the style of the current cell in the current row 50 cellStyle.setBorderTop(BorderStyle.NONE);//Remove top cell border style 51 cellStyle.setBorderBottom(BorderStyle.NONE);//Remove cell border style below 52 cellStyle.setBorderLeft(BorderStyle.NONE);//Remove left cell border style 53 cellStyle.setBorderRight(BorderStyle.NONE);//Remove right cell border style 54 excelData.setTemplateName(getValue(xssfRow.getCell(3)).trim()); 55 paramMap.put("rejectDesc", getValue(xssfRow.getCell(3)).trim()); 56 logger.info("rejectDesc======={}", paramMap.get("rejectDesc").toString()); 57 } 58 logger.info("paramMap======={}",paramMap); 59 if(!paramMap.isEmpty()){ //This is where the problem lies, before new One map To store the parsed data in the cell style With a border, I will also empty map to add enter readMap In order to readMap When returning to the business layer for processing, it is found that the data is not matched with the data in it. If there is no border, we will neither parse nor put the value into it map 60 readMap.add(paramMap); 61 } 62 } 63 } 64 } 65 if (xssfWorkbook != null) { 66 xssfWorkbook.close(); 67 } 68 return readMap; 69 }