Shulou(Shulou.com)06/01 Report--

This article is about how Java imports Excel data into the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

1. Design database tables according to business requirements

2. Design an Excel template according to the database table.

Each column attribute of the template must correspond to the table field one by one

3. Environmental preparation

My project environment here is based on SpringBoot monolithic architecture, persistence layer with the corporate framework, built-in based on the MyBatis-Plus of a variety of single table operation methods.

Import dependency

Org.apache.poi poi 3.17 org.apache.poi poi-ooxml 3.17 4, entity classes corresponding to tables generated by plug-ins / * TbZbzs: watch table * @ author zs * @ date 2021-12-17 08:46:31 * * / @ Data@ApiModel (value= "watch watch table, corresponding to table tb_zbzs" Description= "suitable for adding and modifying page parameters") public class TbZbzs extends ProBaseEntity {private static final long serialVersionUID = 1L @ ApiModelProperty (value= "id") private String id; / / id @ ApiModelProperty (value= "Department") private String bm; / / Department @ ApiModelProperty (value= "Duty report") private String zbsb; / / Duty report @ ApiModelProperty (value= "Duty Officer") private String zbry / / Duty Officer @ ApiModelProperty (value= "escalation time") @ DateTimeFormat (pattern = "yyyy-MM-dd HH:mm:ss") private java.util.Date sbsj; / / reporting time @ ApiModelProperty (value= "end time") @ DateTimeFormat (pattern = "yyyy-MM-dd HH:mm:ss") private java.util.Date jssj / / end time @ ApiModelProperty (value= "contact information") private String lxfs; / / contact information @ ApiModelProperty (value= "status") private String zt; / / status / * logical deletion * / @ ApiModelProperty (value= "logical deletion") private String delFlag / * creation time * / @ ApiModelProperty (value= "creation time") @ DateTimeFormat (pattern = "yyyy-MM-dd HH:mm:ss") private Date createDate;} 5, Custom authoring tool class

What is provided here is a basic template that can add conversion conditions according to the needs of the business.

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.usermodel.XSSFWorkbook;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.List / * New Duty schedule Import Excel tool class * zyw * / public class ImportExcelUtil {private final static String excel2003L = ".xls"; / / 2003-version excel private final static String excel2007U = ".xlsx" / / 2007 + version of excel / * description: get the data in the IO stream and assemble it into a List object * @ param in,fileName * @ return * @ throws Exception * / public static List getListByExcel (InputStream in, String fileName) throws Exception {List list = null; / / create an Excel workbook Workbook work = ImportExcelUtil.getWorkbook (in,fileName) If (null = = work) {throw new Exception ("create Excel workbook is empty!") ;} Sheet sheet= null; Row row = null; Cell cell = null; list = new ArrayList (); / / traverses all sheet for in Excel (int I = 0; I < work.getNumberOfSheets (); iTunes +) {sheet= work.getSheetAt (I); if (sheet==null) {continue } / / traverses all rows in the current sheet for (int j = sheet.getFirstRowNum (); j < sheet.getLastRowNum () + 1; jacks +) {row= sheet.getRow (j); if (row==null | | row.getFirstCellNum () = j) {continue;} / / traverses all columns List li = new ArrayList () For (int y = row.getFirstCellNum (); y < row.getLastCellNum (); yearly +) {cell = row.getCell (y); li.add (ImportExcelUtil.getCellValue (cell));} list.add (li);}} / / work.close (); return list } / * description: according to the file suffix, the version of the adaptive upload file * @ param inStr,fileName * @ return * @ throws Exception * / public static Workbook getWorkbook (InputStream inStr, String fileName) throws Exception {Workbook wb = null; String fileType = fileName.substring (fileName.lastIndexOf (".")) If (excel2003L.equals (fileType)) {wb = new HSSFWorkbook (inStr); / / 2003 -} else if (excel2007U.equals (fileType)) {wb = new XSSFWorkbook (inStr); / / 2007 +} else {throw new Exception ("parsed file format error!") ;} return wb;} / * description: format the values in the table * @ param cell * @ return * / public static Object getCellValue (Cell cell) {Object value = null; DecimalFormat df = new DecimalFormat ("0"); / / format the number String character SimpleDateFormat sdf = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss") / / SimpleDateFormat sdf = new SimpleDateFormat ("yyy-MM-dd"); / / date format / / DecimalFormat df2 = new DecimalFormat ("0.00"); / / format digit if (cellphone null) {switch (cell.getCellType ()) {case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue (). GetString () Break; case Cell.CELL_TYPE_NUMERIC: if ("General" .equals (cell.getCellStyle (). GetDataFormatString () {value = df.format (cell.getNumericCellValue ()) } else if ("m/d/yy" .equals (cell.getCellStyle (). GetDataFormatString () {value = sdf.format (cell.getDateCellValue ());} else {value = sdf.format (cell.getDateCellValue ()) } break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellValue (); break; case Cell.CELL_TYPE_BLANK: value = ""; break Default: break;}} return value;} 6. Write specific business logic Service

Main idea: parse the Excel file into a collection of Object generics through the tool class, and then loop through the collection, in traversal, fill each line of data into the object once, and then in each cycle, store the assigned object into a list collection, and finally unify the method of batch upload of the collection and store it in the database.

Public Map importTprkxx (MultipartFile file) {Map resultMap = new HashMap (); SimpleDateFormat simpleDateFormat = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss"); List tbZbzsList = new ArrayList (); try {/ / get data List olist = ImportExcelUtil.getListByExcel (file.getInputStream (), file.getOriginalFilename ()); resultMap.put ("imported successfully", 200) / / encapsulate data for (int I = 0; I < olist.size (); iTunes +) {List list = olist.get (I); if (list.get (0) = = "" | ("serial number") .equals (list.get (0)) {continue;} TbZbzs tbZbzs = new TbZbzs () TbZbzs.setId (UUID.randomUUID (). ToString (). Replace ("-", ") .substring (0,20); / / get each data of each row according to the subscript if (String.valueOf (list.get (0)) = = null) {resultMap.put (" state "," department cannot be empty "); continue } tbZbzs.setBm (String.valueOf (list.get (0); if (String.valueOf (list.get (1)) = = null) {resultMap.put ("state", "Duty report cannot be empty"); continue } tbZbzs.setZbsb (String.valueOf (list.get (1); if (String.valueOf (list.get (2)) = = null) {resultMap.put ("state", "Duty personnel cannot be empty"); continue } tbZbzs.setZbry (String.valueOf (list.get (2); if (String.valueOf (list.get (3)) = = null) {resultMap.put ("state", "Import failed, reporting time cannot be empty"); continue } String dateStr3 = String.valueOf (list.get (3)); Date date3 = simpleDateFormat.parse (dateStr3); tbZbzs.setSbsj (date3); if (String.valueOf (list.get (4)) = = null) {resultMap.put ("state", "Import failed, end time cannot be empty") Continue;} String dateStr4 = String.valueOf (list.get (4)); Date date4 = simpleDateFormat.parse (dateStr4); tbZbzs.setJssj (date4) If (String.valueOf (list.get (5)) = = null) {resultMap.put ("state", "contact information cannot be empty"); continue;} tbZbzs.setLxfs (String.valueOf (list.get (5) If (String.valueOf (list.get (6)) = = null) {resultMap.put ("state", "status cannot be empty"); continue;} tbZbzs.setZt (String.valueOf (list.get (6) If (String.valueOf (list.get (7)) = = null) {resultMap.put ("state", "logical deletion cannot be empty"); continue;} tbZbzs.setDelFlag (String.valueOf (list.get (7) If (String.valueOf (list.get (8)) = = null) {resultMap.put ("state", "Import failed, creation time cannot be empty"); continue;} String dateStr8 = String.valueOf (list.get (8)); Date date8 = simpleDateFormat.parse (dateStr8) TbZbzs.setCreateDate (date8); tbZbzsList.add (tbZbzs);} int I = tbZbzsDao.insertTbZbzsList (tbZbzsList); if (I! = 0) {resultMap.put ("state", "Import successful");} else {resultMap.put ("state", "Import failed") }} catch (Exception e) {e.printStackTrace (); resultMap.put ("state", "Import failed");} return resultMap 7. In the xml file corresponding to the dao layer Write bulk upload methods insert into tb_zbzs (id, bm, zbsb, zbry, sbsj, jssj, lxfs, zt, del_flag, create_date) VALUES (# {item.id}, # {item.bm} # {item.zbsb}, # {item.zbry}, # {item.sbsj}, # {item.jssj}, # {item.lxfs}, # {item.zt}, # {item.delFlag} # {item.createDate}) 8. Controller implements business control / * @ method name: excelProTbZbzs * @ function: import duty watch table Excel TODO: method input parameters according to page object settings * @ param file * @ return java.lang.String * @ create by zyw at 2022-03-17 16:49:31 * * / @ ApiOperation (value= "import duty watch table Excel" Notes= "returns the import situation API", response = TbZbzs.class) @ PostMapping (value = "/ excelProTbZbzs") public String excelProTbZbzs (@ RequestParam ("file") MultipartFile file) {try {return buildResultStr (service.importTprkxx (file). Get ("state"). Equals ("Import successful")? BuildSuccessResultData (): buildErrorResultData (service.importTprkxx (file). Get ("state"). ToString ());} catch (RuntimeException e) {logError (log, e); return buildResultStr (buildErrorResultData (e));}} 9, test interface through Swagger

10. View the import effect in the data and console

Thank you for reading! This is the end of the article on "how to import Excel data into the database by Java". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.