Previously we have realized in the background management system, the configuration of the data to add and delete check. However, only one data can be added each time. In actual production, it is unrealistic to add a large amount of data one by one manually. In this chapter, we will implement the function of importing configuration data through Excel. Here, map data is used as an example for other configuration items.

The functions involved mainly include office document programming and file uploading functions. The flowchart is roughly as follows:

Add dependencies

Parsing Office documents recommends using the free open source component POI, which already meets 80% of the functional requirements. Uploading files depends on the Commons-Fileupload package. We add the following code to the POM:

<! --> <dependency> <groupId>org.apache.poi</groupId> <artifactId> <version>4.1.0</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId> Poi-ooxml </artifactId> <version>4.1.0</version>  </dependency> <! <dependency> <groupId> Commons -fileupload</groupId> <artifactId> Commons -fileupload</artifactId> The < version > 1.4 < / version > < / dependency >Copy the code

In addition, the MVC view parser we configured earlier can only parse simple views, upload files need to support multipart. Add the following configuration to spring-mVC.xml:

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
    <property name="defaultEncoding" value="UTF-8"></property>
    <property name="maxUploadSize" value="10485770"></property>
    <property name="maxInMemorySize" value="10485760"></property>
</bean>
Copy the code

The maximum upload limit is set at 10MB, which is sufficient for Excel uploads.

Two, file upload, analysis, database

In the MapController, we add three methods

@ResponseBody @RequestMapping(value = "/importExcel", method = RequestMethod.POST) public Object importExcel(HttpServletRequest request) { try { ServletContext servletContext  = request.getServletContext(); String uploadPath = servletContext.getRealPath("/upload"); File dir = new File(uploadPath); if (! dir.exists()) { dir.mkdir(); } CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(servletContext); if (multipartResolver.isMultipart(request)) { MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request; Iterator<String> iter = multiRequest.getFileNames(); while (iter.hasNext()) { MultipartFile file = multiRequest.getFile(iter.next()); if (file.getSize() > 0) { String fileName = file.getOriginalFilename(); String extension = fileName.substring(fileName.lastIndexOf(".")); if (! extension.toLowerCase().equals(".xls") && ! Extension.tolowercase ().equals(".xlsx")) {throw new Exception(" Unsupported document format! Please upload the document in.xls or.xlsx format!" ); } String destFileName = fileName + "_" + System.currentTimeMillis() + extension; File destFile = new File(uploadPath, destFileName); file.transferTo(destFile); List<WowMap> dataList = this.loadExcelData(destFile.getPath()); this.saveExcelData(dataList); if (! Destfile.delete ()) {logger.warn(" temporary file deletion failed: "+ destfile.getabsolutePath ()); } } } } return CommonResult.success(); } catch (Exception ex) { logger.error(ex.getMessage(), ex); return CommonResult.fail(); } } protected List<WowMap> loadExcelData(String excelPath) throws Exception { FileInputStream fileInputStream = new FileInputStream(excelPath); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); Sheet = workbook.getsheet (" map "); List<WowMap> wowMapList = new ArrayList<>(); String createUser = this.currentUsername (); for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) { XSSFRow row = (XSSFRow) sheet.getRow(rowNum); String name = PoiUtil.getCellValue(row.getCell(2)); DataDict.Occupy occupy = DataDict.Occupy.getByDesc(PoiUtil.getCellValue(row.getCell(4))); WowMap wowMap = new WowMap(); wowMap.setName(name); wowMap.setOccupy(occupy.getCode()); wowMap.setDescription(""); wowMap.setCreateUser(createUser); wowMapList.add(wowMap); } fileInputStream.close(); return wowMapList; } protected void saveExcelData(List<WowMap> dataList) { wowMapManager.batchInsert(dataList); }Copy the code

MapController.java

In this method, we define the temporary file upload path, verify the file name suffix, save the uploaded file to the server, and delete the temporary file after the operation. LoadExcelData method, the use of POI components to read and parse Excel data, Excel data how we can be free to define, here when reading the free adjustment of the corresponding row and column, this example of the use of Excel in the end of the text given in the source can be found; The saveExcelData method saves the parsed data list to the database. The batchInsert batch add method called here has been implemented in advance in the previous section.

In addition, when reading Excel data using POI components, we need to determine the cell format first. To do this, we created a utility class PoiUtil, which can be used in other projects in the future. We extracted it and put it in the Util module as our general tool kit for future use. Create a package com.idlewow.util. Poi in util and add the PoiUtil class:

package com.idlewow.util.poi; import org.apache.commons.lang3.StringUtils; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.DateUtil; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; public class PoiUtil { public static String getCellValue(Cell cell) { CellType cellType = cell.getCellType(); if (cellType.equals(CellType.STRING)) { return cell.getStringCellValue(); } else if (cellType.equals(CellType.NUMERIC)) { if (DateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); return date == null ? "" : new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(date); } else { return new DecimalFormat("0.##").format(cell.getNumericCellValue()); } } else if (cellType.equals(CellType.FORMULA)) { if (StringUtils.isNotBlank(cell.getStringCellValue())) { return cell.getStringCellValue(); } else { return cell.getNumericCellValue() + ""; } } else if (cellType.equals(CellType.BOOLEAN)) { return cell.getBooleanCellValue() ? "TRUE" : "FALSE"; } else { return ""; }}}Copy the code

PoiUtil.java

After the utility classes are extracted into the util module, you need to add a dependency on Poi in the util module as well as util in the RMS module. In the util module, the scope of the dependency is provided, which is used only at compile time, because the POI dependency must have been introduced in the module that references the toolkit and does not need to be packaged again:

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> The < version > 4.1.0 < / version > < scope > provided < / scope > < / dependency > < / dependencies >Copy the code

Third, modify the front page

In the map list page, list.jsp, add the import Excel button.

The < form >..................... ..................... <div class="layui-inline layui-show-xs-block"> <button type="button" class="layui-btn" Onclick ="xadmin.open(' add map ','add',500,500)"> < I class="layui-icon"></ I > add map </button> </div> <div class="layui-upload layui-inline layui-show-xs-block"> <button type="button" class="layui-btn layui-btn-normal" Id ="btnSelectFile"> Select Excel</button> <button type="button" class="layui-btn" id="btnImport"> </button> </div> </form>Copy the code

In list.js of the list page, bind the corresponding button event.

Layui. Use ([' upload ', 'table', 'form'], function () {..................... ..................... layui.upload.render({ elem: '#btnSelectFile', url: '/manage/map/importExcel', accept: 'file', exts: 'xls|xlsx', auto: false, bindAction: '#btnImport', done: function (result) { if (result.code === 1) { layer.alert(result.message, {icon: 6}, function () { layui.layer.closeAll(); layui.table.reload('datatable'); }); } else { layer.alert(result.message, {icon: 5}); }}}); });Copy the code

Four, the operation effect

Now that the excel import function is complete, let’s run it to see what it looks like:

　　

summary

This chapter realizes the function of batch input by importing Excel files.

The source code download address: idlestudio.ctfile.com/fs/14960372…

In this paper, the original address: www.cnblogs.com/lyosaki88/p…

In the next chapter, it is expected to implement parameter verification when adding and modifying.

Project Exchange Group: 329989095