Preface:
Recently, PM has come to a requirement. In short, it is very troublesome to insert data into the system one by one. Let me implement a method of importing all data directly through excel at one time. There are many examples of importing and exporting excel on the internet, but most of them learn from each other. After thinking, it is believed that 100 customers will have 100 format versions when entering excel, so before realizing this function, we should unify the format of excel. So a download function of general excel template is provided. When all customers input data with template and upload it to the system, the back end parses excel, and then persists to the database.
Summary:
Several features of this tool class
Basic import and export
2. Provide excel template automatic generation and download function
3. The process of creating templates is simple and general, requiring only annotations on entity classes.
Spring MVC Framework
5. Templates can generate drop-down box selection columns
No more nonsense, code it...
1. Introducing poi-related dependencies (and spring upload file-related configuration, no longer explained)
1 <!-- apache poi start --> 2 <poi.version>3.14</poi.version> 3 <dependency> 4 <groupId>org.apache.poi</groupId> 5 <artifactId>poi</artifactId> 6 <version>${poi.version}</version> 7 </dependency> 8 9 <dependency> 10 <groupId>org.apache.poi</groupId> 11 <artifactId>poi-scratchpad</artifactId> 12 <version>${poi.version}</version> 13 </dependency> 14 15 <dependency> 16 <groupId>org.apache.poi</groupId> 17 <artifactId>poi-ooxml</artifactId> 18 <version>${poi.version}</version> 19 </dependency> 20 <!-- apache poi end -->
2. Implementing excel Import and Export Tool Class
1 /** 2 * @Description 3 * @author zhaomin E-mail:min.zhao@mljr.com 4 * @date Creation time: February 14, 2017, 2:13:30 p.m. 5 * @version 1.0 6 */ 7 public class ImportExcelUtil { 8 final static String notnullerror = "Please fill in the first paragraph.{0}Row{1},{2}Can not be empty"; 9 final static String errormsg = "The first{0}Row{1}Data Import Error"; 10 11 /** 12 * Import Excel 13 * 14 * @param clazz 15 * @param xls 16 * @return 17 * @throws Exception 18 */ 19 @SuppressWarnings("rawtypes") 20 public static List importExcel(Class<?> clazz, InputStream xls) throws Exception { 21 try { 22 // Obtain Excel 23 HSSFWorkbook wb = new HSSFWorkbook(xls); 24 HSSFSheet sheet = wb.getSheetAt(0); 25 Field[] fields = clazz.getDeclaredFields(); 26 List<Field> fieldList = new ArrayList<Field>(fields.length); 27 for (Field field : fields) { 28 if (field.isAnnotationPresent(ModelProp.class)) { 29 ModelProp modelProp = field.getAnnotation(ModelProp.class); 30 if (modelProp.colIndex() != -1) { 31 fieldList.add(field); 32 } 33 } 34 } 35 EmployeeDTO employee = new EmployeeDTO(); 36 // Circulate 37 List<ImportModel> modelList = new ArrayList<ImportModel>(sheet.getPhysicalNumberOfRows() * 2); 38 for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) { 39 // data model 40 ImportModel model = (ImportModel) clazz.newInstance(); 41 int nullCount = 0; 42 Exception nullError = null; 43 for (Field field : fieldList) { 44 ModelProp modelProp = field.getAnnotation(ModelProp.class); 45 HSSFCell cell = sheet.getRow(i).getCell(modelProp.colIndex()); 46 try { 47 if (cell == null || cell.toString().length() == 0) { 48 nullCount++; 49 if (!modelProp.nullable()) { 50 nullError = new Exception(StringUtil.format(notnullerror, 51 new String[] { "" + (1 + i), modelProp.name(), modelProp.name() })); 52 53 } 54 } else if (field.getType().equals(Date.class)) { 55 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 56 BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell)))); 57 } else { 58 BeanUtils.setProperty(model, field.getName(), 59 new Date(cell.getDateCellValue().getTime())); 60 61 } 62 } else if (field.getType().equals(Timestamp.class)) { 63 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 64 BeanUtils.setProperty(model, field.getName(), 65 new Timestamp(parseDate(parseString(cell)))); 66 } else { 67 BeanUtils.setProperty(model, field.getName(), 68 new Timestamp(cell.getDateCellValue().getTime())); 69 } 70 71 } else if (field.getType().equals(java.sql.Date.class)) { 72 if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 73 BeanUtils.setProperty(model, field.getName(), 74 new java.sql.Date(parseDate(parseString(cell)))); 75 } else { 76 BeanUtils.setProperty(model, field.getName(), 77 new java.sql.Date(cell.getDateCellValue().getTime())); 78 } 79 } else if (field.getType().equals(java.lang.Integer.class)) { 80 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 81 BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue()); 82 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 83 BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell))); 84 } 85 } else if (field.getType().equals(java.math.BigDecimal.class)) { 86 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 87 BeanUtils.setProperty(model, field.getName(), 88 new BigDecimal(cell.getNumericCellValue())); 89 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 90 BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell))); 91 } 92 } else { 93 if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) { 94 BeanUtils.setProperty(model, field.getName(), 95 new BigDecimal(cell.getNumericCellValue())); 96 } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) { 97 BeanUtils.setProperty(model, field.getName(), parseString(cell)); 98 } 99 } 100 } catch (Exception e) { 101 e.printStackTrace(); 102 throw new Exception(StringUtil.format(errormsg, new String[] { "" + (1 + i), modelProp.name() }) 103 + "," + e.getMessage()); 104 } 105 } 106 if (nullCount == fieldList.size()) { 107 break; 108 } 109 if (nullError != null) { 110 throw nullError; 111 } 112 modelList.add(model); 113 } 114 return modelList; 115 116 } finally { 117 xls.close(); 118 } 119 } 120 121 private final static int colsizeN = 630; 122 private final static int colsizeM = 1000; 123 124 /** 125 * Download Excel template 126 * 127 * @param clazz 128 * @param map 129 * @param rowSize 130 * @return 131 */ 132 public static InputStream excelModelbyClass(Class<?> clazz, Map<Integer, String[]> map, Integer rowSize) { 133 try { 134 if (!clazz.isAnnotationPresent(ModelTitle.class)) { 135 throw new Exception("Please add in this type ModelTitle annotation"); 136 } 137 if (rowSize == null) { 138 rowSize = 1000; 139 } 140 HSSFWorkbook wb = new HSSFWorkbook(); 141 HSSFSheet sheet = wb.createSheet(); 142 /** 143 * Setting header style 144 */ 145 HSSFCellStyle titleStyle = wb.createCellStyle(); 146 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 147 HSSFFont font = wb.createFont(); 148 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 149 font.setFontHeight((short) 400); 150 titleStyle.setFont(font); 151 HSSFCell titleCell = sheet.createRow(0).createCell(0); // Create the first row, and create cells in that row, setting the content as the heading line 152 /** 153 * Get title 154 */ 155 ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class); 156 titleCell.setCellValue(new HSSFRichTextString(modelTitle.name())); 157 titleCell.setCellStyle(titleStyle); 158 159 Field[] fields = clazz.getDeclaredFields(); 160 HSSFRow headRow = sheet.createRow(1); 161 int colSzie = 0; 162 /** 163 * Setting Header Style 164 */ 165 HSSFCellStyle headStyle = wb.createCellStyle(); 166 headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); 167 HSSFFont headFont = wb.createFont(); 168 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); 169 headFont.setFontHeight((short) 240); 170 headStyle.setFont(headFont); 171 List<Integer> cells = new ArrayList<Integer>(); 172 173 for (Field field : fields) { 174 if (field.isAnnotationPresent(ModelProp.class)) { 175 ModelProp modelProp = field.getAnnotation(ModelProp.class); 176 if (modelProp.colIndex() == -1) 177 continue; 178 cells.add(modelProp.colIndex()); 179 HSSFCell cell = headRow.createCell(modelProp.colIndex()); 180 cell.setCellValue(new HSSFRichTextString(modelProp.name())); 181 cell.setCellStyle(headStyle); 182 colSzie++; 183 sheet.autoSizeColumn((short) modelProp.colIndex()); 184 sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN + colsizeM); 185 186 // Set the column to drop-down box format 187 if (map != null && map.get(new Integer(modelProp.colIndex())) != null) { 188 DVConstraint constraint = DVConstraint 189 .createExplicitListConstraint(map.get(modelProp.colIndex())); 190 CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(), 191 modelProp.colIndex()); 192 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); 193 sheet.addValidationData(dataValidation); 194 } 195 } 196 } 197 HSSFCellStyle cellStyle = wb.createCellStyle(); 198 HSSFDataFormat format = wb.createDataFormat(); 199 cellStyle.setDataFormat(format.getFormat("@")); 200 for (int i = 2; i < rowSize; i++) { 201 HSSFRow row = sheet.createRow(i); 202 for (Integer integer : cells) { 203 HSSFCell cell = row.createCell(integer); 204 cell.setCellStyle(cellStyle); 205 } 206 } 207 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1)); 208 if (map != null) { 209 for (Integer colIndex : map.keySet()) { 210 DVConstraint constraint = DVConstraint.createExplicitListConstraint(map.get(colIndex)); 211 CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex); 212 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); 213 sheet.addValidationData(dataValidation); 214 } 215 } 216 217 ByteArrayOutputStream os = new ByteArrayOutputStream(); 218 try { 219 wb.write(os); 220 } catch (IOException e) { 221 e.printStackTrace(); 222 } 223 224 byte[] b = os.toByteArray(); 225 226 ByteArrayInputStream in = new ByteArrayInputStream(b); 227 return in; 228 } catch (Exception e) { 229 e.printStackTrace(); 230 return null; 231 } 232 } 233 234 private static String parseString(HSSFCell cell) { 235 return String.valueOf(cell).trim(); 236 } 237 238 private static long parseDate(String dateString) throws ParseException { 239 if (dateString.indexOf("/") == 4) { 240 return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime(); 241 } else if (dateString.indexOf("-") == 4) { 242 return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime(); 243 } else if (dateString.indexOf("year") == 4) { 244 return new SimpleDateFormat("yyyy year MM month dd").parse(dateString).getTime(); 245 } else if (dateString.length() == 8) { 246 return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime(); 247 } else { 248 return new Date().getTime(); 249 } 250 } 251 252 }
3. Custom spring annotations
1 @Retention(RetentionPolicy.RUNTIME) 2 @Target(ElementType.FIELD) 3 public @interface ModelProp{ 4 public String name(); 5 public int colIndex() default -1; 6 public boolean nullable() default true; 7 public String interfaceXmlName() default ""; 8 }
1 @Retention(RetentionPolicy.RUNTIME) 2 @Target(ElementType.TYPE) 3 public @interface ModelTitle{ 4 public String name(); 5 }
Definition of entity class parent class
1 public class ImportModel { 2 3 }
V. Defining Entity Classes
1 @ModelTitle(name="Personnel list") 2 public class EmployeeDTO extends ImportModel implements Serializable { 3 4 private static final long serialVersionUID = -3434719712955859295L; 5 6 private Long id; 7 @ModelProp(name = "Telephone", colIndex = 1, nullable = false) 8 private String telephone; 9 10 @ModelProp(name = "Name", colIndex = 0, nullable = false) 11 private String name; 12 13 @ModelProp(name = "Gender", colIndex = 2, nullable = false) 14 private Integer sex; 15 }
6. Definition of controller
1 @RestController 2 @RequestMapping("/api/excelOpera") 3 public class ImportEmployeeController extends BaseController { 4 5 private static Logger logger = LoggerFactory.getLogger(ImportEmployeeController.class); 6 /** 7 * Import excel table 8 * @version 1.0 9 * @since 1.0 10 */ 11 @RequestMapping(path = "/importEmployee", method = RequestMethod.POST) 12 public RespMsg uploadExcel(@RequestParam("file") MultipartFile file, HttpServletRequest request) { 13 try{ 14 15 List<EmployeeDTO> employeeDTOList = ImportExcelUtil.importExcel(EmployeeDTO.class, file.getInputStream()); 16 //Can do persistent operation, now only print observation 17 for(EmployeeDTO employeeDTO : employeeDTOList){ 18 logger.info("name=" + employeeDTO.getName() + ",telephone=" + employeeDTO.getTelephone()+",sex=" + employeeDTO.getSex()); 19 } 20 }catch(Exception e){ 21 logger.error(e.getMessage()); 22 } 23 return null; 24 } 25 /** 26 * Export excel template 27 * @version 1.0 28 * @since 1.0 29 */ 30 @RequestMapping(path = "/downloadEmployeeModel", method = RequestMethod.GET) 31 public RespMsg downloadEmployeeModel(HttpServletResponse response) { 32 try{ 33 response.setContentType("application/xls"); 34 response.addHeader("Content-Disposition", "attachment;filename="+new String(("eeelist").getBytes("UTF-8"),"iso-8859-1")+".xls"); 35 Map<Integer,String[]> paramMap = new HashMap<Integer,String[]>(); 36 //excel Third Behavior Drop-down Selection Box 37 paramMap.put(2, new String[]{"man","women"}); 38 BufferedInputStream input = new BufferedInputStream(ImportExcelUtil.excelModelbyClass(EmployeeDTO.class, paramMap, null)); 39 byte buffBytes[] = new byte[1024]; 40 OutputStream os = response.getOutputStream(); 41 int read = 0; 42 while ((read = input.read(buffBytes)) != -1) { 43 os.write(buffBytes, 0, read); 44 } 45 os.flush(); 46 os.close(); 47 input.close(); 48 return success("Download success!"); 49 }catch(Exception e){ 50 logger.error("downloadEmployeeModel() catch Exception ",e); 51 return fail("Download failed!"); 52 } 53 } 54 55 } 56
So far, the implementation of all tool classes has been completed, you can request access to verify the results.
Downloaded excel template
Fill in data and upload
Background console prints output results
Is it very simple that the test results can achieve the expected effect and can be used? Welcome the gods to give their opinions. Thank you, little girl.