Excel Import and Export

POI-Excel Import and Export

Foreword: A report needs to be made in the first two days of work, so it is necessary to import and export tabular data. In fact, this requirement is not so simple, it is based on excel import and export, but the requirement is that users in the page can write documents in rich text editor, and then export the data content of all documents in the list. The difficulty is that rich text editor can define font size, format, emoticons, and even pictures; So it really isn't good to export all of this locally. There are a lot of tool classes on the web, but what I found doesn't really meet my needs. (I haven't finished all of these yet (let's talk more about this after I finish this function). This article will first tell you about the import and export of excel in simple format; in fact, most statistical reports are just like this, not so complicated.)

Prerequisite: excel import and export are implemented on the springboot project based on POI libraries (not really important, mostly code understanding introduced)

There are many POI packaged tools on the Internet that can be used directly.

1. Introduction to POI

First, POI provides Java programs with the ability to read and write Microsoft Office format files, so it can not only read and write excel, but also word documents. Here, only report operations are described.

Excel has two formats, one is.xls and the other is.xlsx;

The HSSF package of POI provides read and write functions for tables in xls format, and the XSSF package provides read and write functions for tables in xlsx format.

Following is a description of POI's common classes for excel read-write operations:

Class nameExplain
HSSFWorkbookexcel document object
HSSFSheetsheet form
HSSFRowRows of the form
HSSFCellCells of the form
HSSFFontForm Font
HSSFCellStyleStyle of Cells
HSSFDataFormatDate format of cells
HSSFHeaderHeader of the form
HSSFFooterFooter of the form

Knowing the meaning of these commonly used classes, you can basically understand the logic of the tool class, or use someone else's tool class, but if the results are not the same as your own needs, we can at least make some modifications to the tool class to meet our own requirements.

First paste the existing ExcelUtil tool classes on the web directly, then describe what each process and logic does.

package com.leejie.utils;

import com.leejie.bean.ExcelData;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

import static org.apache.poi.ss.usermodel.CellType.*;

/**
 * Path: com.example.demo.utils
 * Class name:
 * Function: Import and Export
 */
public class ExcelUtil {

    public static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * Method name: exportExcel
     * Function: Export Excel
     */
    public static void exportExcel(HttpServletResponse response, ExcelData data) {
        log.info("Export parsing begins, fileName:{}",data.getFileName());
        try {
            //Instantiate HSSFWorkbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            //Create an Excel form with the name of the sheet as the argument
            HSSFSheet sheet = workbook.createSheet("sheet");
            //Set Header
            setTitle(workbook, sheet, data.getHead());
            //Set Cells and Assign Values
            setData(sheet, data.getData());
            //Set up browser download
            setBrowser(response, workbook, data.getFileName());
            log.info("Export parsed successfully!");
        } catch (Exception e) {
            log.info("Export parsing failed!");
            e.printStackTrace();
        }
    }

    /**
     * Method name: setTitle
     * Function: Set header
     */
    private static void setTitle(HSSFWorkbook workbook, HSSFSheet sheet, String[] str) {
        try {
            HSSFRow row = sheet.createRow(0);
            //Set the column width, the second parameter of setColumnWidth is multiplied by 256, which is in 1/256 character width
            for (int i = 0; i <= str.length; i++) {
                sheet.setColumnWidth(i, 15 * 256);
            }
            //Set to Centered Bold, Format Time Format
            HSSFCellStyle style = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            style.setFont(font);
            style.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
            //Create Header Name
            HSSFCell cell;
            for (int j = 0; j < str.length; j++) {
                cell = row.createCell(j);
                cell.setCellValue(str[j]);
                cell.setCellStyle(style);
            }
        } catch (Exception e) {
            log.info("Failed to set header on export!");
            e.printStackTrace();
        }
    }

    /**
     * Method name: setData
     * Function: Table assignment
     */
    private static void setData(HSSFSheet sheet, List<String[]> data) {
        try{
            int rowNum = 1;
            for (int i = 0; i < data.size(); i++) {
                HSSFRow row = sheet.createRow(rowNum);
                for (int j = 0; j < data.get(i).length; j++) {
                    row.createCell(j).setCellValue(data.get(i)[j]);
                    log.info("No. "+ (i+1) +" That's ok"+", No. "+ (j+1) +" Column: --->  "+row.getCell(j));
                }
                rowNum++;
            }
            log.info("Table assignment succeeded!");
        }catch (Exception e){
            log.info("Table assignment failed!");
            e.printStackTrace();
        }
    }

    /**
     * Method name: setBrowser
     * Function: Download using browser
     */
    private static void setBrowser(HttpServletResponse response, HSSFWorkbook workbook, String fileName) {
        try {
            //Empty response
            response.reset();
            //Set up a Header for the response
            response.addHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("utf8"),"ISO8859-1")+".xlsx");
            response.setContentType("application/octet-stream");
            OutputStream os = new BufferedOutputStream(response.getOutputStream());
//            response.setContentType("application/vnd.ms-excel;charset=gb2312");
            //Write excel to output stream
            workbook.write(os);
            os.flush();
            os.close();
            log.info("Setup Browser Download Successful!");
        } catch (Exception e) {
            log.info("Setup Browser Download Failed!");
            e.printStackTrace();
        }

    }


    /**
     * Method name: importExcel
     * Function: Import
     *
     */
    public static List<Object[]> importExcel(MultipartFile file) {
        log.info("Import parsing begins, fileName:{}",file.getName());
        try {
            List<Object[]> list = new ArrayList<>();
//            InputStream inputStream = new FileInputStream(fileName);
            //Get input stream to file
            InputStream inputStream = file.getInputStream();
            //Create a workbook from excel file input stream
            Workbook workbook = WorkbookFactory.create(inputStream);
            //Get the first sheet table of the workbook
            Sheet sheet = workbook.getSheetAt(0);
            //Get the number of rows for a sheet
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 0; i < rows; i++) {
                //Filter header rows (default is header (list attribute))
                if (i == 0) {
                    continue;
                }
                //Get data for the current row (starting with the first row)
                Row row = sheet.getRow(i);
                //Create an array whose length is the number of columns in the current row
                Object[] objects = new Object[row.getPhysicalNumberOfCells()];
                int index = 0;
                for (Cell cell : row) {
                    //Determine the type of each cell in the current row and assign the cell type to the array [i]
                    if (cell.getCellType() == NUMERIC.getCode()) {
                        objects[index] = (int) cell.getNumericCellValue();
                    }
                    if (cell.getCellType()==STRING.getCode()) {
                        objects[index] = cell.getStringCellValue();
                    }
                    if (cell.getCellType()==BOOLEAN.getCode()) {
                        objects[index] = cell.getBooleanCellValue();
                    }
                    if (cell.getCellType()==ERROR.getCode()) {
                        objects[index] = cell.getErrorCellValue();
                    }
                    index++;
                }
                list.add(objects);
            }
            log.info("Import file parsed successfully!");
            return list;
        }catch (Exception e){
            log.info("Import file parsing failed!");
            e.printStackTrace();
        }
        return null;
    }

}

To be honest, this tool class is really good, ideal for learning excel import and export, for general reports, and the code logic is clear. You can see that each of the more important codes is annotated (some were added when I understood them), and list the important ones, such as creating workbooks, sheet tables, getting rows, columns, cells, determining the type of each cell, and so on.

Yes, if you look at this tool class, you will find a class of ExcelData, which is used to store file names, headers and data.

public class ExcelData {
	//Omit getter, setter method, depending on the type of data used
    private String fileName;
    private String[] head;
    private List data;
}

Export:

Since it is an export, you must create your own excel workbook, sheet tables, and headers (attribute names for each column) and store the data in each cell in order.

  1. Create a workbook (note that HSSF is the package used here and the default is to generate documents in xls format)

    HSSFWorkbook workbook = new HSSFWorkbook();
    
  2. Create a sheet form (sheet names can be customized)

     HSSFSheet sheet = workbook.createSheet("sheet");
    
  3. Setting the header (implementation, etc.)

    setTitle(workbook, sheet, data.getHead());
    
  4. Assigning values to cells (implementation, etc.)

    setData(sheet, data.getData());
    
  5. Setting up browser download information (implementation, etc.)

    setBrowser(response, workbook, data.getFileName());
    

The whole process is just these 5 steps, let's not mention creating workbooks and forms. Overall, you'll use the methods of the common classes described above.

  1. Create the first row of the table through the HSSFSheet class, which is the length of the header array;
  2. The HSSFCellStyle class sets the header style, the HSSFFont sets the font, and the HSSFDataFormat sets the time format. HSSFCell creates cells and assigns them (that is, adds data from the header array in our ExcelData to the cell in the header, and if no header is needed here, you can leave the header unassigned)
  3. Assign a value to the specific content of the table (add the list of data in our ExcelData to the cell). This is to write two for loops, loop through each row and determine a cell in each column, row = sheet.createRow() to create each row, iterate through each row, add a data value to cell = row.createCell(), and assign row.setCellValue(data.get(i)[j]to the cell;
  4. Set up the browser information for the response because we need to respond to excel to the user. I actually had a previous article about the response body http send request In the simple introduction (even if you can't understand it, there is also a link to the recommended article to point ~~), back to the code, this is to set the response header (which sets the file name exported by excel) and the response body type, and then send the workbook to the browser through the output stream.

Import:

Now that excel is imported into the system, it traverses each cell to read its contents.

  1. Front-end transmits files of type MultipartFile to our control layer (where the front-end sends requests and the back-end processes requests as well as see my previous article oh~), back-end receives files into streams.

    InputStream inputStream = file.getInputStream();
    
  2. Generate a workbook by inputStream input stream

    Workbook workbook = WorkbookFactory.create(inputStream);
    
  3. Get the first sheet table of the workbook (if there are multiple sheets, we can also iterate through each sheet directly, there is a way to get the length of the sheet)

    Sheet sheet = workbook.getSheetAt(0);
    
  4. Get the number of rows in the table

    int rows = sheet.getPhysicalNumberOfRows();
    
  5. Walk through each row (if there is a header, we need to filter out row 0)

  6. Get data for the current row

    Row row = sheet.getRow(i);
    
  7. Create an array to hold the data for each cell, the length being the number of columns in the current row

    Object[] objects = new Object[row.getPhysicalNumberOfCells()];
    
  8. Traverse each column of the current row (that is, each cell), determine the type of each cell (string, boolean, numeric...), and assign it to the array created by 7.

  9. Finally, the array created by each row is added to the list list list, and the list list list is returned, so that each row of data in excel exists in the array, and each array exists in the list. If we need to save it in the database, we can directly iterate through each array in the list and assign it to an object, then we can execute sql to save the database.

Links to recommended articles about the source of this tool class and related excel import and export: https://blog.csdn.net/typ1805/article/details/83279532

https://blog.csdn.net/tanwenfang/article/details/94463709

summary

Slowly, I can see the tool classes that I understand, so it's certainly not difficult. Click on each class source to see what methods, constructors, or source logic are in this class, because I think this understanding is clearer and deeper. 12 o'clock, it's time to sleep~~
Yes, I just posted out a tool class that I uploaded to gitee. Because I was lazy, I wrote the import and export of this excel and the upload and download columns of the previous file on one project, which I would like to see. It's very simple to implement, it's just to experience these functions, simply record them. Work is actually much more complicated, but how to say, the more complex is this process, you can't miss it. Gitee address: https://gitee.com/leejiezh/springboot-file-upload

Next article: Encoding and decoding for java

Tags: Java Excel Spring Boot

Posted on Wed, 24 Nov 2021 13:29:58 -0500 by TTT