How Springboot uploads excel to store data

This article mainly introduces Springboot how to upload excel storage data, the article is very detailed, has a certain reference value, interested friends must read it!

First, import dependency

Apache's POI plug-in is still used here, which is now basically used in general springboot parsing excel.

Org.apache.poi poi-ooxml 5.0.0 org.apache.poi poi 5.0.0 2. The frontend implements  file import / * file upload-batch import * / layui.use ('upload' Function () {var upload = layui.upload Var layer = layui.layer; / / layer.msg ('uploading', {icon: 16, time: 0MagneShade: 0.3}) / / execute instance var uploadInst = upload.render ({elem:'# bookFileUpload', / / binding element url:'/ library/book/batchAddBooks/', / / upload API exts: 'xlsx | xls' / / restrict file type done: function (res) {/ / callback if (res.code = ='1') {successMsg ("imported successfully") after uploading } else {errorMsg ("Import failed" + res);}}, error: function (res) {/ / request exception callback errorMsg ("system exception" + res);});})

To be clear, here is still using the framework of layui to achieve, layui is I have used the front-end framework in a relatively small number of holes in a good simple and easy to start the framework.

Third, background logic

1. First write a model entity class for importing data, corresponding to a table in the database.

/ * Book entity * / @ Datapublic class Book {/ / key id private int bid; / / title private String bookname; / / author private String author; / / the type of book such as martial arts, romance, etc. Private String booktype; / / Publishing private String publisher; / / publication time private String publicationdate; / / release price private int price; / / release status private String bookstate / / remarks private String comment;}

Lombok's plug-in tool is used here, so you can omit to write the setget method, just @ Data.

Lombok dependency, whether you use it or not, depends on yourself:

Org.projectlombok lombok true

two。 Then write about the front-end upload excel, the background parsing processing logic.

Here are the main points:

Package com.example.library.utils.fileUtiles;import com.example.library.model.Book;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.streaming.SXSSFWorkbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.slf4j.Logger;import org.slf4j.LoggerFactory Import org.springframework.web.multipart.MultipartFile;import java.io.*;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List;/** * @ Author Summer_DM * @ Summary TODO tools for operating Excel * @ Version 1.0 * @ Date 03:44 on 2021-9-16 * * / public class ExcelUtils {private static final Logger logger = LoggerFactory.getLogger (ExcelUtils.class); / / Map map = new HashMap () Private static final String XLS = "xls"; private static final String XLSX = "xlsx"; private final static String DATE_FORMAT = "yyyy-MM-dd" / * get the corresponding workbook object according to the file suffix type * @ param inputStream reads the input stream of the file * @ param fileType file suffix type (xls or xlsx) * @ return the workbook object containing file data * @ throws IOException * / public static Workbook getWorkbook (InputStream inputStream, String fileType) throws Exception {Workbook workbook = null / / obtain different Workbook implementation class objects if (fileType.equalsIgnoreCase (XLS)) {/ / 2003 workbook = new HSSFWorkbook (inputStream) according to different file suffix names (xls and xlsx);} else if (fileType.equalsIgnoreCase (XLSX)) {/ / 2007 and above workbook = new XSSFWorkbook (inputStream) } else {throw new Exception ("Please upload excel file!") ;} return workbook;} / * read the contents of the Excel file * @ the path where the Excel file to be read by param fileName * @ return reads the list of results. If reading fails, it returns null * / public static List readExcel (String fileName) {Workbook workbook = null; FileInputStream inputStream = null Try {/ / get Excel suffix name String fileType = fileName.substring (fileName.lastIndexOf (".") + 1, fileName.length (); / / get Excel file File excelFile = new File (fileName); if (! excelFile.exists ()) {logger.warn ("specified Excel file does not exist!") ;} / / get Excel workbook inputStream = new FileInputStream (excelFile); workbook = getWorkbook (inputStream, fileType); / / read data in excel List resultDataList = parseExcel (workbook); return resultDataList } catch (Exception e) {logger.warn ("failed to parse Excel, file name:" + fileName + "error message:" + e.getMessage ()); return null;} finally {try {if (null! = workbook) {workbook.close () } if (null! = inputStream) {inputStream.close ();}} catch (Exception e) {logger.warn ("turn off data outflow error! Error message: "+ e.getMessage (); return null;} / * * read the contents of Excel file * @ Excel file uploaded by param file * @ return read the list of results. If reading fails, return null * / public static List readExcel (MultipartFile file) {Workbook workbook = null Try {/ / determine whether the file exists if (null = = file) {logger.warn ("failed to parse Excel, the file does not exist!") ; return null;} / / get the Excel suffix name String fileName = file.getOriginalFilename (); if (fileName = = null | | fileName.isEmpty () | fileName.lastIndexOf (".")

< 0) { logger.warn("解析Excel失败，因为获取到的Excel文件名非法！"); return null; } String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()); // 获取Excel工作簿 workbook = getWorkbook(file.getInputStream(), fileType); // 读取excel中的数据 List resultDataList = parseExcel(workbook); return resultDataList; } catch (Exception e) { logger.warn("解析Excel失败，文件名：" + file.getOriginalFilename() + " 错误信息：" + e.getMessage()); return null; } finally { try { if (null != workbook) { workbook.close(); } } catch (Exception e) { logger.warn("关闭数据流出错！错误信息：" + e.getMessage()); return null; } } } /** * 解析Excel数据 * @param workbook Excel工作簿对象 * @return 解析结果 */ private static List parseExcel(Workbook workbook) { List resultDataList = new ArrayList(); //获取所有的工作表的的数量 int numOfSheet = workbook.getNumberOfSheets(); System.out.println(numOfSheet+"--->