Spring MVC + POI export web data to Excel

Spring MVC + POI export web data to Excel

Apache POI is an open source library of the Apache Software Foundation. POI provides the functions of reading and writing Microsoft Office files to Java programs.
Next, I will release an example of Excel export based on spring MVC + Maven + poi. Welcome to criticize and correct!!

Let's see the effect first

Data:
effect:
Step 1: download the jar package of related poi using maven

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

Step 2: write a tool class

package com.jyc.entity;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;


/**
 * Export Excel common methods
 * @version 1.0
 * 
 * @author wangcp
 *
 */
public class ExportExcel{

    //Title of the displayed export table
    private String title;
    //Column name of export table
    private String[] rowName ;

    private List<Object[]>  dataList = new ArrayList<Object[]>();

    HttpServletResponse  response;

    //Constructor, passing in data to export
    public ExportExcel(String title,String[] rowName,List<Object[]>  dataList,HttpServletResponse  response){
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
        this.response = response;  
    }

    /*
     * Export data
     * */
    public void export() throws Exception{
        try{
            HSSFWorkbook workbook = new HSSFWorkbook();                        // Create workbook object
            HSSFSheet sheet = workbook.createSheet(title);                     // Create sheet

            // Generate table header row
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);

            //sheet style definition [getColumnTopStyle()/getStyle() are all user-defined methods - below - extensible]
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//Get column header style object
            HSSFCellStyle style = this.getStyle(workbook);                    //Cell style object

            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length-1)));  
            cellTiltle.setCellStyle(columnTopStyle);
            cellTiltle.setCellValue(title);

            // Define the number of columns required
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(2);                // Create row at index 2 (second row from top row)

            // Set column headers to cells in sheet
            for(int n=0;n<columnNum;n++){
                HSSFCell  cellRowName = rowRowName.createCell(n);                //Create cells corresponding to the number of column headers
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);                //Set the data type of the column header cell
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text);                                    //Set the value of the column header cell
                cellRowName.setCellStyle(columnTopStyle);                        //Set column header cell style
            }

            //Set the queried data to the cell corresponding to the sheet
            for(int i=0;i<dataList.size();i++){

                Object[] obj = dataList.get(i);//Traverse each object
                HSSFRow row = sheet.createRow(i+3);//Number of rows required to create

                for(int j=0; j<obj.length; j++){
                    HSSFCell  cell = null;   //Set cell data type
                    if(j == 0){
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i+1);    
                    }else{
                        cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                        if(!"".equals(obj[j]) && obj[j] != null){
                            cell.setCellValue(obj[j].toString());                        //Set cell value
                        }
                    }
                    cell.setCellStyle(style);                                    //Set cell style
                }
            }
            //Let the column width automatically adapt to the exported column length
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //The current row has not been used
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if(colNum == 0){
                    sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
                }else{
                    sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
                }
            }

            if(workbook !=null){
                try
                {
                    String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls";
                    String headStr = "attachment; filename=\"" + fileName + "\"";
                    response.setContentType("APPLICATION/OCTET-STREAM");
                    response.setHeader("Content-Disposition", headStr);
                    OutputStream out = response.getOutputStream();
                    workbook.write(out);
                    out.close();
                }
                catch (IOException e)
                {
                    e.printStackTrace();
                }
            }

        }catch(Exception e){
            e.printStackTrace();
        }

    }

    /* 
     * Column header cell style
     */    
      public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

            // Set font
          HSSFFont font = workbook.createFont();
          //Set font size
          font.setFontHeightInPoints((short)11);
          //Bold font
          font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
          //Set font name 
          font.setFontName("Courier New");
          //Set the style; 
          HSSFCellStyle style = workbook.createCellStyle();
          //Set the bottom border; 
          style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
          //Set the bottom border color;  
          style.setBottomBorderColor(HSSFColor.BLACK.index);
          //Set the left border;   
          style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
          //Set the left border color; 
          style.setLeftBorderColor(HSSFColor.BLACK.index);
          //Set the right border; 
          style.setBorderRight(HSSFCellStyle.BORDER_THIN);
          //Set the right border color; 
          style.setRightBorderColor(HSSFColor.BLACK.index);
          //Set the top border; 
          style.setBorderTop(HSSFCellStyle.BORDER_THIN);
          //Set the top border color;  
          style.setTopBorderColor(HSSFColor.BLACK.index);
          //Use the font set by the application in the style;  
          style.setFont(font);
          //Set auto wrap; 
          style.setWrapText(false);
          //Set the style of horizontal alignment to center alignment;  
          style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
          //Set the vertical alignment style to center alignment; 
          style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

          return style;

      }

      /*  
     * Column data information cell style
     */  
      public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
            // Set font
            HSSFFont font = workbook.createFont();
            //Set font size
            //font.setFontHeightInPoints((short)10);
            //Bold font
            //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            //Set font name 
            font.setFontName("Courier New");
            //Set the style; 
            HSSFCellStyle style = workbook.createCellStyle();
            //Set the bottom border; 
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            //Set the bottom border color;  
            style.setBottomBorderColor(HSSFColor.BLACK.index);
            //Set the left border;   
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            //Set the left border color; 
            style.setLeftBorderColor(HSSFColor.BLACK.index);
            //Set the right border; 
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);
            //Set the right border color; 
            style.setRightBorderColor(HSSFColor.BLACK.index);
            //Set the top border; 
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);
            //Set the top border color;  
            style.setTopBorderColor(HSSFColor.BLACK.index);
            //Use the font set by the application in the style;  
            style.setFont(font);
            //Set auto wrap; 
            style.setWrapText(false);
            //Set the style of horizontal alignment to center alignment;  
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            //Set the vertical alignment style to center alignment; 
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

            return style;

      }
}

Step 3: control layer code

@RequestMapping("exportExcel")
    @ResponseBody
    public void exportExcel(String pro_id,String pro_name,HttpServletResponse response) throws Exception{
        int start=0;
        int length=-1;
        String title ="Commodity information";
        String[] rowsName = new String[]{"No","Item No","name","Price"};
        List<Product> products=service.selectProductBypage(start, length, pro_id, pro_name);
        List<Object[]>  dataList = new ArrayList<Object[]>();
        Object[] objs = null;
        for (int i = 0; i < products.size(); i++) {
            Product  product=products.get(i);
             objs = new Object[rowsName.length];
             objs[0] = i;
             objs[1] =product.getPro_id();
             objs[2] = product.getPro_name();
             objs[3] =product.getS_price();
             dataList.add(objs);

        }
        ExportExcel ex=new ExportExcel(title, rowsName, dataList,response);
        ex.export();
    }

Step 4: js code and HTML button

    function exportExcel(){
                pro_id=$("#pro_id").val();
                pro_name=$("#pro_name").val();
                location.href="${pageContext.request.contextPath}/product/exportExcel.dopro_id="+pro_id+"&pro_name="+pro_name;
//Can't use ajax, the file download request can't be written in Ajax!
            }

<button class="btn  btn-success btn-sm"  onclick="exportExcel()">
    <i class="icon-zoom-in align-top bigger-110">export Excel</i>
    </button>

Note: because of the response, the request browser usually processes the response output from the server, such as generating png, downloading files, etc. However, the ajax request is only a "character" request, that is, the content of the request is stored in the text type. The file is downloaded in binary form. Although the returned response can be read, it is only read and cannot be executed. The white point is that js cannot call the download processing mechanism and program of the browser.

Keywords: Apache Excel Java Spring

Added by Optimo on Sat, 23 May 2020 17:49:36 +0300