SpringBoot integration easyExcel Excel export

< the dependency > < groupId > com. Alibaba < / groupId > < artifactId > easyexcel < / artifactId > < version > 1.1.2 - beta5 < / version > </dependency>Copy the code

After importing the template, we need to create an exported template class. First, we need to integrate BaseRowModel, set, get, @excelProperty value is the header information, index is the column, and the unannotated template will not be exported.

public class OrderExcelBO extends BaseRowModel {

    @ExcelProperty(value = {"Order ID"}, index = 0) private String id; /** */ @excelProperty (value = {"Order Description"}, index = 2) private String description; /** */ @excelProperty (value = {"Product ID"}, index = 2) private Integer productId; /** * Apple pay */ @excelProperty (value = {"Method of Payment"}, index = 3)
    private String payMethod;

    /**
     * create_time
     */
    @ExcelProperty(value = {"Time"}, index = 4) private String createTime; /** * update_time */ private String updateTime; /** * @excelProperty (value = {"User ID"}, index = 5) private Integer userId; /** * pay status: 0 not paid, 1 payment completed successfully, -1 payment failed */ @excelProperty (value = {"Payment Status"}, index = 6) private String status; /** */ @excelProperty (value = {"Cell phone Model"}, index = 7) private String platform; /** * @excelProperty (value = {"Order Serial Number"}, index = 8) private String flowNum; /** * order value */ @excelProperty (value = {"The amount"}, index = 9)
    private BigDecimal price;

    // @ExcelProperty(value = {"Receipt field"}, index = 10)
    private String receipt;

    @ExcelProperty(value = {"APP source"}, index = 10)
    private String sources;
}Copy the code

1. Figure out what data we need to export;
2. Generate Excel file name and sheet name;
3. Directly call the encapsulated utility class export file;

So let’s look at the effect of the export

If your table header is complex, you can define it by yourself. For example, if the table header is complex, how to set it

The first step is to change the template class. If the merged cells are at most 2, all the tables need to be set to 2. The unmerged cells are filled with empty strings.

Let’s take a look at the effect of export, so that we can meet the usual development needs of Excel export function. Easy to learn.

Tools:

import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import java.util.ArrayList; import java.util.List; Public class ExcelListener extends AnalysisEventListener {/** * Custom used to temporarily store data. */ private List<Object> datas = new ArrayList<>(); /** * You can also get the current sheet from the AnalysisContext object, */ @override public void invoke(Object Object, AnalysisContext context) {// Data is stored in a list for batch processing or subsequent business logic processing. datas.add(object); // According to the businessdo something
      doSomething(); /* If the data is too large, quantitative batch processing can be carried outif(datas.size()<=100){
            datas.add(object);
        }else {
            doSomething(); datas = new ArrayList<Object>(); } */} /** * Implement the method according to the business */ private voiddoSomething() {
   }

   @Override
   public void doAfterAllAnalysed(AnalysisContext context) { /* datas.clear(); */} public List<Object>getDatas() {
      return datas;
   }

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

import com.alibaba.excel.ExcelReader; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.metadata.BaseRowModel; import com.alibaba.excel.metadata.Font; import com.alibaba.excel.metadata.Sheet; import com.alibaba.excel.metadata.TableStyle; import com.alibaba.excel.support.ExcelTypeEnum; import com.mochu.exception.ExcelException; import org.apache.poi.poifs.filesystem.FileMagic; import org.apache.poi.ss.usermodel.IndexedColors; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.BufferedInputStream; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.List; Public class ExcelUtil {/** * read Excel(multiple sheets) ** @param Excel file * @param rowModel entity class mapping, inheriting BaseRowModel class * @returnPublic static list <Object>readExcel(MultipartFile excel, BaseRowModel rowModel) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);

        if (reader == null) {
            return null;
        }

        for (Sheet sheet : reader.getSheets()) {
            if(rowModel ! = null) { sheet.setClazz(rowModel.getClass()); } reader.read(sheet); }returnexcelListener.getDatas(); } /** * Read the Excel of a sheet ** @param Excel file * @param rowModel entity class map, Inherit the BaseRowModel class * @Param sheetNo Sheet from 1 * @returnPublic static list <Object>readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo) {
        return readExcel(excel, rowModel, sheetNo, 1); } /** * Read the Excel of a sheet ** @param Excel file * @param rowModel entity class map, Inherits the BaseRowModel class * @Param sheetNo Sheet from 1 * @Param headLineNum Number of header rows. The default value is 1 * @returnPublic static list <Object>readExcel(MultipartFile excel, BaseRowModel rowModel, int sheetNo, int headLineNum) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader reader = getReader(excel, excelListener);

        if (reader == null) {
            return null;
        }

        reader.read(new Sheet(sheetNo, headLineNum, rowModel.getClass()));

        returnexcelListener.getDatas(); } /** * Export Excel: A sheet with a header * * @param response HttpServletResponse * @param list Data list, Each element is a BaseRowModel * @param fileName the exported fileName * @param sheetName the sheet name of the imported file * @param object mapping entity class, Public static void writeExcel(HttpServletResponse Response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, object.getClass()); sheet.setSheetName(sheetName); TableStyle tableStyle = new TableStyle(); tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE); Font font = new Font(); font.setFontHeightInPoints((short) 9); tableStyle.setTableHeadFont(font); tableStyle.setTableContentFont(font); sheet.setTableStyle(tableStyle); writer.write(list, sheet); writer.finish(); } /** * Export Excel: * * @param Response HttpServletResponse * @param list Data list, Each element is a BaseRowModel * @param fileName the exported fileName * @param sheetName the sheet name of the imported file * @param object mapping entity class, Public static ExcelWriterFactory writeExcelWithSheets(HttpServletResponse Response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) { ExcelWriterFactory writer = new ExcelWriterFactory(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, object.getClass()); sheet.setSheetName(sheetName); sheet.setTableStyle(getTableStyle()); writer.write(list, sheet);returnwriter; } /** ** Export the sheet ** @param response * @param list * @param fileName * @param sheetName * @param object */ public static void writeFinanceRepayment(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, object.getClass()); sheet.setSheetName(sheetName); sheet.setTableStyle(getTableStyle()); writer.write(list, sheet);for(int i = 1; i <= list.size(); i += 4) { writer.merge(i, i + 3, 0, 0); writer.merge(i, i + 3, 1, 1); } writer.finish(); } /** * private static OutputStream getOutputStream(String fileName, HttpServletResponse Response) {// Create a local file fileName = fileName +".xls";

        try {
            fileName = new String(fileName.getBytes(), "ISO-8859-1");
            response.addHeader("Content-Disposition"."filename=" + fileName);

            return response.getOutputStream();
        } catch (Exception e) {

            throw new ExcelException("Export exception!"); }} /** * return ExcelReader ** @param excel Excel file to parse * @param excelListener new excelListener () */ private static ExcelReader getReader(MultipartFile excel, ExcelListener excelListener) { String filename = excel.getOriginalFilename();if(filename == null || (! filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".xlsx"))) {
            throw new ExcelException("File format error!");
        }
        InputStream inputStream;

        try {
            inputStream = new BufferedInputStream(excel.getInputStream());

            return new ExcelReader(inputStream, null, excelListener, false);
        } catch (IOException e) {
            e.printStackTrace();
        }

        returnnull; } /** * A sheet with a header * * @param response HttpServletResponse * @param list Data list, Each element is a BaseRowModel * @param fileName the exported fileName * @param sheetName the sheet name of the imported file * @param object mapping entity class, Excel model */ public static voidexportFundBudgetExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName, String sheetName, BaseRowModel object) throws IOException { ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLSX); Sheet sheet = new Sheet(1, 0, object.getClass()); sheet.setSheetName(sheetName); sheet.setTableStyle(getTableStyle()); writer.write(list, sheet); writer.merge(2, 3, 0, 0); writer.merge(4, 13, 0, 0); writer.merge(14, 14, 0, 1); writer.finish(); } /** * read the Excel table data and encapsulate it into an entity ** @param inputStream * @Param clazz * @param sheetNo * @Param headLineMun * @return
     */
    public static Object readExcel(InputStream inputStream, Class<? extends BaseRowModel> clazz, Integer sheetNo,
                                   Integer headLineMun) {
        if (null == inputStream) {

            throw new NullPointerException("the inputStream is null!");
        }

        ExcelListener listener = new ExcelListener();
        ExcelReader reader = new ExcelReader(inputStream, valueOf(inputStream), null, listener);
        reader.read(new Sheet(sheetNo, headLineMun, clazz));

        returnlistener.getDatas(); } /** * Determine whether the input stream is XLS or XLSX. This method originally existed in ExcelTypeEnum of easyExcel 1.1.0. */ public static ExcelTypeEnum valueOf(InputStream inputStream) { try { FileMagic fileMagic = FileMagic.valueOf(inputStream);if (FileMagic.OLE2.equals(fileMagic)) {
                return ExcelTypeEnum.XLS;
            }

            if (FileMagic.OOXML.equals(fileMagic)) {
                return ExcelTypeEnum.XLSX;
            }

            throw new ExcelException("excelTypeEnum can not null"); } catch (IOException e) { throw new RuntimeException(e); }} /** * Sets the global style ** @return
     */
    private static TableStyle getTableStyle() {
        TableStyle tableStyle = new TableStyle();

        tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
        Font font = new Font();
        font.setBold(true);
        font.setFontHeightInPoints((short) 9);
        tableStyle.setTableHeadFont(font);
        Font fontContent = new Font();
        fontContent.setFontHeightInPoints((short) 9);
        tableStyle.setTableContentFont(fontContent);

        returntableStyle; }}Copy the code

import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

public class ExcelWriterFactory extends ExcelWriter {
   private OutputStream outputStream;
   private int sheetNo = 1;

   public ExcelWriterFactory(OutputStream outputStream, ExcelTypeEnum typeEnum) {
      super(outputStream, typeEnum); this.outputStream = outputStream; } public ExcelWriterFactory write(List<? extends BaseRowModel> list, String sheetName, BaseRowModel object) { this.sheetNo++; try { Sheet sheet = new Sheet(sheetNo, 0, object.getClass()); sheet.setSheetName(sheetName); this.write(list, sheet); } catch(Exception ex) { ex.printStackTrace(); try { outputStream.flush(); } catch(IOException e) { e.printStackTrace(); }}return this;
   }

   @Override
   public void finish() { super.finish(); try { outputStream.flush(); } catch(IOException e) { e.printStackTrace(); }}}Copy the code

For space reasons, if you need to share the Excel import function, please leave a comment below.