This is the 8th day of my participation in Gwen Challenge.More article challenges

Introduction to EasyExcel

EasyExcel is one of Alibaba open source POI plug-ins, mainly to solve the POI framework is complex to use, SAX parsing mode is not easy to operate, the large amount of data is easy to OOM, to solve the POI concurrent error. Main solution: load the file by decompressing the file line by line, and discard the style font and other unimportant data, reduce the occupation of memory.

EasyExcel advantage

  • Annotated custom operations.
  • I/O is simple and provides interfaces for the I/O process
  • Supports some degree of flexible operations such as cell merging

Second, common annotations

  • @excelProperty specifies that the current field corresponds to the column in Excel. Matches can be made by name or Index. You don’t have to write it, but by default the first field is index=0, and so on. Make sure you don’t write anything at all, use index at all, or match everything by name. Never mix three together, unless you know how to sort three together in the source code.
  • ExcelIgnore all fields match excel by default. This annotation will ignore the fields
  • @datetimeFormat Date conversion. This annotation is called to receive excel date format data with a String. In the value of reference to Java. Text. SimpleDateFormat
  • NumberFormat number conversion. This annotation is called when a String is used to receive excel NumberFormat data. See java.text.decimalFormat for the value inside
  • @excelignoreunannotated if ExcelProperty annotations are not added by default, they will not be added

Third, relying on

<! Easyexcel mainly relies on this one basically enough --><dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>easyexcel</artifactId>
   <version>2.1.4</version>
</dependency><! -- servlet-api --><dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>javax.servlet-api</artifactId>
   <version>4.0.1</version>
   <scope>provided</scope>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>fastjson</artifactId>
   <version>1.2.47</version>
</dependency>

Copy the code

Four, listening


/** * EasyExcel import listener */
public class ExcelListener extends AnalysisEventListener {
    // The value can be obtained by instance
    private List<Object> datas = new ArrayList<Object> (); @Override publicvoid invoke(Object o, AnalysisContext analysisContext) {
        datas.add(o);// Store the data to the list for batch processing or subsequent business logic processing.
        doSomething(o);// Do it according to your business
    }

    private void doSomething(Object object) {
        //1
    }

    public List<Object> getDatas() {
        return datas;
    }

    public void setDatas(List<Object> datas) {
        this.datas = datas;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // datas.clear(); // Resolve the end of the destruction of unused resources}}Copy the code

5. Interface import Excel

try {
            // Get the file name
            String filename = file.getOriginalFilename();
            // Get the file stream
            InputStream inputStream = file.getInputStream();
            // instantiate a class that implements the AnalysisEventListener interface
            ExcelListener listener = new ExcelListener();
            // Pass in parameters
            ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
            // Read the information
            excelReader.read(new Sheet(1.0, Test.class));
            // Get data
            List<Object> list = listener.getDatas();
            if (list.size() > 1) {
                for (int i = 0; i < list.size(); i++) {
                    Testobj = (Test) list.get(i);
                    JSONObject jo = newJSONObject(); }}}catch (Exception e) {
            System.out.println(e.getMessage());
        }

Copy the code

HttpServletResponse Response (HttpServletRequest Request)


try {
    String filenames = "111111";
    String userAgent = request.getHeader("User-Agent");
    if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
        filenames = URLEncoder.encode(filenames, "UTF-8");
    } else {
        filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setContentType("application/vnd.ms-exce");
    response.setCharacterEncoding("utf-8");
    response.addHeader("Content-Disposition"."filename=" + filenames + ".xlsx");
    EasyExcel.write(response.getOutputStream(), Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}
Copy the code

Local import and local export

List<Test> testList = new ArrayList<>();
try {
    String strUrl = "C:\\Users\\Administrator\\Desktop\\json.xlsx";
    File multipartFile = new File(strUrl);
    InputStream inputStream = new FileInputStream(multipartFile);
    // instantiate a class that implements the AnalysisEventListener interface
    ExcelListener listener = new ExcelListener();
    // Pass in parameters
    ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
    // Read the information
    excelReader.read(new Sheet(1.0, Test.class));
    // Get data
    List<Object> list = listener.getDatas();
    if (list.size() > 1) {
        for (int i = 0; i < list.size(); i++) { Testobj = (Test) list.get(i); }}}catch (Exception e) {
    System.out.println(e.getMessage());
}
try {
    String strUrl = "C:\\Users\\Administrator\\Desktop\\json"+System.currentTimeMillis()+".xlsx";
    EasyExcel.write(strUrl,Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}
Copy the code

The above is the basic use process of EasyExcel, welcome to praise attention exchange.