​

directory

preface

Maven with EasyExcel

Second, read excel code examples

1. Beans need to correspond to excel columns

demo

2, the Controller layer

demo

3, the service layer

demo

4, the listener layer

demo

5, the dao layer

demo

Export excel code examples

preface

We should all use the function of importing and exporting Excel, such as importing Excel data into the database by reading Excel. Of course, there are many ways to achieve, today I introduce the use of Ali open source easyExcel project to complete the function. You can also see their own easyExcel documents for development. Of course, here because just work when used, so will write their own demo to share, we can save time to complete the function faster, we can refer to, can also be directly used, in fact, in your own development process appropriate modification.

EasyExcel is a Java – based simple, save – memory Excel reading and writing open source project. Save as much memory as possible to support reading and writing Excel 100 MB.

Making address: github.com/alibaba/eas…

Document address: Alibaba-easyExcel. Github. IO /

Maven with EasyExcel

<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.1</version> </dependency> < the dependency > < groupId > org. Projectlombok < / groupId > < artifactId > lombok < / artifactId > < version > 1.18.8 < / version > </dependency>Copy the code

In addition to the simplest three-tier architecture, DAO, Service and Controller, listeners are also needed

​

Second, read excel code examples

1. Beans need to correspond to excel columns

It is possible to use index and name to match beans to Excel columns, but it is not recommended to use index and name together. As the name implies, you should use either index or name in a bean

eg:@ExcelProperty(index = 2)

Eg: @excelProperty (” date title “)

demo

package com.xxx.xxxx.xxxx;


import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
 * Created by yjl on 2019/12/25.
 */
@Data
public class RoomMonitorCoverDataBean {


    private String TIME_ID; //时间

    @ExcelProperty(index = 0)
    private String PROV_NAME; //省

    @ExcelProperty(index = 1)
    private String AREA_NAME; //地市

    @DateTimeFormat("yyyy年MM月")
    @ExcelProperty(index = 2)
    private String MONTH_DESC; //月份，DateTimeFormat表示对日期进行格式化，不要的可以去掉

    


    public String getTIME_ID() {
        return TIME_ID;
    }

    public void setTIME_ID(String TIME_ID) {
        this.TIME_ID = TIME_ID;
    }

    public String getPROV_NAME() {
        return PROV_NAME;
    }

    public void setPROV_NAME(String PROV_NAME) {
        this.PROV_NAME = PROV_NAME;
    }

    public String getAREA_NAME() {
        return AREA_NAME;
    }

    public void setAREA_NAME(String AREA_NAME) {
        this.AREA_NAME = AREA_NAME;
    }

    public String getMONTH_DESC() {
        return MONTH_DESC;
    }

    public void setMONTH_DESC(String MONTH_DESC) {
        this.MONTH_DESC = MONTH_DESC;
    }

  
}
Copy the code

2, the Controller layer

The impL interface is the same name as the impL interface. The impL interface is the same name as the impL interface. The impL interface is the impL interface. Of course, you can get rid of that interface

demo

@RequestMapping("/mrePortController") public class MrePortControllerCSVImpl implements IMrePortControllerCSV { @Autowired private MrePortServiceCSV mrePortServiceCSV; @RequestMapping(value = "/saveRoomMonitorCoverData", method = RequestMethod.POST) @Override public JSONObject saveRoomMonitorCoverData(MultipartFile file, String timeType, String userArea) { return mrePortServiceCSV.saveRoomMonitorCoverData(file, timeType,userArea); }}Copy the code

3, the service layer

So here we’re making a logical judgment, we can make a judgment about an input or something else,

The main function is

// There is an important point xxxxxListener that cannot be managed by Spring. To read excel, new is required every time, and then spring can construct a method to pass it in. Read the first sheet file by default flows automatically closed EasyExcel. Read (the file. The getInputStream (), RoomMonitorCoverDataBean. Class, new RoomMonitorCoverDataListener(mrePortDao,tableName,timeType)).sheet().doRead();Copy the code

demo

@Component public class MrePortServiceCSVImpl implements MrePortServiceCSV { @Autowired private MrePortDao mrePortDao; private Logger logger = LoggerFactory.getLogger(this.getClass()); @Override public JSONObject saveRoomMonitorCoverData(MultipartFile file, String timeType,String userArea) { JSONObject jo = new JSONObject(); Try {// Can be used to verify mandatory parameters, etc. // Small fish small Lin _ zhejiang 201912. XLSX String fileName = file. GetOriginalFilename (). The replaceAll (" \. XLSX | \. XLS ", ""); System.out.println("file.getName():"+file.getName()+",file.getOriginalFilename():"+file.getOriginalFilename()); String[] split = fileName.split("_"); String file_tableName = split[0]; String file_areaNameAndDate = split[1]; //String file_version = split[2]; / / file name version number if (" small fish small Lin ". The equals (file_tableName)) {/ / can only upload the file name String file_areaName = file_areaNameAndDate. Substring (0, 2); / / file name String of region-county file_date = file_areaNameAndDate. Substring (2); If (userarea.equals (file_areaName)){if (userarea.equals (file_areaName)){if (userarea.equals (file_areaName)) Long.parseLong(getCurrentDate("yyyyMM")); String currentDate = getCurrentDate("yyyyMM"); String tableName = "XXXXXXXXX "; // There is an important point xxxxxListener that cannot be managed by Spring. To read excel, new is required every time, and then spring can construct a method to pass it in. Read the first sheet file by default flows automatically closed EasyExcel. Read (the file. The getInputStream (), RoomMonitorCoverDataBean. Class, new RoomMonitorCoverDataListener(mrePortDao,tableName,timeType)).sheet().doRead(); Jo. put(" MSG "," import successful "); jo.put("resultCode",0); jo.put("response","success"); }else {jo.put(" MSG "," you can only upload current "+currentDate+" data "); jo.put("resultCode",-1); jo.put("response",""); return jo; }}else {jo.put(" MSG "," you don't have permission to upload "+file_areaName+"); jo.put("resultCode",-1); jo.put("response",""); return jo; }}else {jo.put(" MSG "," please select "xiao Xiao Lin" file to upload); jo.put("resultCode",-1); jo.put("response",""); return jo; } }catch (IOException i){ i.printStackTrace(); Jo. put(" MSG ","IOException, please try again "); jo.put("resultCode",-1); jo.put("response",""); } catch (Exception e){ e.printStackTrace(); Jo. put(" MSG "," import exception, please try again "); jo.put("resultCode",-1); jo.put("response",""); } return jo; }}Copy the code

4, the listener layer

Note that this is not the MVC three-tier architecture, the listener is added here because easyExcel is needed, in fact, the process of parsing Excel is implemented here, if you want to parse each row of data or one of the columns for another judgment or other processing, is to write logic in this class

RoomMonitorCoverDataListener.java

demo

/** * Created by yjl on 2019/12/27. The public no. : zygxsq */ public class RoomMonitorCoverDataListener extends AnalysisEventListener<RoomMonitorCoverDataBean> { private Logger LOGGER = LoggerFactory.getLogger(this.getClass()); private static final int BATCH_COUNT = 1000; List<RoomMonitorCoverDataBean> list = new ArrayList<RoomMonitorCoverDataBean>(); MrePortDao mrePortDao; String timeType; String tableName; /** * If spring is used, use the parameter constructor. Each time you create the Listener need to spring management class incoming * / public RoomMonitorCoverDataListener () {} public RoomMonitorCoverDataListener(MrePortDao mrePortDao) { this.mrePortDao = mrePortDao; } public RoomMonitorCoverDataListener(MrePortDao mrePortDao,String timeType) { this.mrePortDao = mrePortDao; this.timeType = timeType; } public RoomMonitorCoverDataListener(MrePortDao mrePortDao,String tableName,String timeType) { this.mrePortDao = mrePortDao; this.tableName = tableName; this.timeType = timeType; } /** Override public void invoke(RoomMonitorCoverDataBean) Data, AnalysisContext AnalysisContext) {logger. info(" Parse to a data :{}", json.tojsonString (data)); / / -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- begin -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- / / here to excel table data logic, filtering and so on, if you don't need to process the data to the table, String area_name = data.getarea_name (); String month_desc = data.getMONTH_DESC(); // Delete the existing table, To save the new mrePortDao. DeleteTableData (tableName, timeType "AREA_NAME = '" + +" AREA_NAME "AND MONTH_DESC ='" + MONTH_DESC + "'"); //-------------------end----------------------- list.add(data); OOM if (list.size() >= BATCH_COUNT) {saveData(); if (list.size() >= BATCH_COUNT) {saveData(); List list.clear(); }} @override public void doAfterAllAnalysed(analysisContext) AnalysisContext) {// saveData here too, making sure the last remaining data is also stored in the database saveData(); Logger. info(" All data parsed!" ); } private void saveData() {logger. info("{} "); , list.size()); mrePortDao.saveRoomMonitorCoverData(list,timeType); Logger. info(" Database saved successfully!" ); }}Copy the code

5, the dao layer

Because we use here is springJPA, so everyone can see I handle the excel data above, want to delete the original data in the database, or a joining together of that kind of format, here we want to combine their own framework to believe as a technical person, you can change or, if you have encountered will not change, You can follow my official account: ZygxSQ. There is my wechat way in the official account. You can contact me

demo

/** * Created by yjl on 2019/12/20. */ @Component public class MrePortDaoImpl implements MrePortDao { @PersistenceContext private EntityManager em; @Autowired protected JdbcTemplate jdbcTemplate; private Logger logger = LoggerFactory.getLogger(this.getClass()); @Transactional @Override public Integer saveRoomMonitorCoverData(List<RoomMonitorCoverDataBean> params, String timeType) { String tableName = "xxxxxxx"; StringBuilder sql = new StringBuilder(); Sql.append ("INSERT INTO ").appEnd (tableName).append("(").append("TIME_ID,") // time.append ("PROV_NAME,") // province Append (" AREA_NAME ") / / cities. Append (" MONTH_DESC, ") / / month. Append (") VALUES ("), append ("? ,? ,? That?" ) .append(")") ; int[] len = jdbcTemplate.batchUpdate(sql.toString(), new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { RoomMonitorCoverDataBean pojo = params.get(i); String month_desc = pojo.getMONTH_DESC(); String time_id = month_desc.replaceAll("\D","")+"000000"; ps.setString(1,time_id); Ps / / time. SetString (2, pojo getPROV_NAME ()); / / province ps. SetString (3, pojo getAREA_NAME ()); / / cities ps. SetString (4, month_desc); } @override public int getBatchSize() {return params.size(); }}); return len.length; } @Transactional @Modifying @Query @Override public Integer deleteTableData(String tableName, String timeType, String whereInfo) { String sql="DELETE FROM "+tableName+" WHERE 1=1 AND "+whereInfo; Logger. info(" Delete table "+tableName+" whereInfo+" SQL: "+ SQL); int deleteCnt = em.createNativeQuery(sql).executeUpdate(); Logger. info(" successfully deleted "+tableName+" the "+deleteCnt+" in the table whereInfo+"); return deleteCnt; }}Copy the code

Export excel code examples

To be updated

​