Export Excel data based on POI

Posted by rakennedy75 on Sat, 04 Apr 2020 08:38:14 +0200

Export Excel data based on POI

There will be many report businesses in the project, which need to export data. Here we use POI based parsing. There are two ways to parse POI: HSSF(xls format) and XSSF(xlsx).

Take the waybill management interface of BOS logistics as an example:

Front-end code

Export Waybill Information and generate report:

1. Add Export button

2. Add export event

Background code implementation

The procedure of POI generating excel is the same as that of writing Excel: new excel document - new Sheet - new Row - new Cell cell Cell - write Cell data.

For Maven to import jar package information, please refer to the configuration in one click upload

Action layer code implementation

@Controller
@Scope("prototype")
@ParentPackage("json-default")
@Namespace("/")
public class ExportXlsAction extends BaseAction<WayBill>{

    @Autowired
    private WayBillService  wayBillService;
    
    @Action("report_exportXls")
    public String exportXls() throws Exception{
        //Query out,Result data meeting current conditions
        List<WayBill> waybills = wayBillService.findwayBills(model);
        
        // generate Excel File ( xls Format)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        HSSFSheet sheet = hssfWorkbook.createSheet("Waybill data");//Create a sheet object
        
        // Header (first row of table)
        HSSFRow headRow = sheet.createRow(0);//Create first line object
        headRow.createCell(0).setCellValue("Waybill No.");//The first grid is created. Its attribute is waybill number
        headRow.createCell(1).setCellValue("Sender");//Created a second grid...
        headRow.createCell(2).setCellValue("Sender's phone number");
        headRow.createCell(3).setCellValue("return address ");
        headRow.createCell(4).setCellValue("Addressee");
        headRow.createCell(5).setCellValue("Recipient phone");
        headRow.createCell(6).setCellValue("Address of addressee");
        
        // Tabular data(Traverse the obtained data, one object information corresponds to one line of records)
        for (WayBill wayBill : waybills) {//Get the last line number
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(wayBill.getWayBillNum());
            dataRow.createCell(1).setCellValue(wayBill.getSendName());
            dataRow.createCell(2).setCellValue(wayBill.getSendMobile());
            dataRow.createCell(3).setCellValue(wayBill.getSendAddress());
            dataRow.createCell(4).setCellValue(wayBill.getRecName());
            dataRow.createCell(5).setCellValue(wayBill.getRecMobile());
            dataRow.createCell(6).setCellValue(wayBill.getRecAddress());
        }

        // Download export
        // Set header information (set file type xls)
        ServletActionContext.getResponse().setContentType(
                "application/vnd.ms-excel");
        String filename = "Waybill data.xls";
        String agent = ServletActionContext.getRequest()
                .getHeader("user-agent");//Get the browser type (Firefox, 360, IE)
        filename = FileUtils .encodeDownloadFilename(filename, agent);
        ServletActionContext.getResponse().setHeader("Content-Disposition",
                "attachment;filename=" + filename);
               //filename:Set up production Excel File name  FileUtils: Chinese coding class for browser type
        ServletOutputStream outputStream = ServletActionContext.getResponse()
                .getOutputStream();//Get a response flow object
        hssfWorkbook.write(outputStream);

        // Close
        hssfWorkbook.close();

        return NONE;        
    }
}

Action layer code

package cn.itcast.bos.utils;

import java.io.IOException;
import java.net.URLEncoder;

import sun.misc.BASE64Encoder;

public class FileUtils {
        /**
         * When downloading files, code the attachment name for different browsers
         * 
         * @param filename
         *            Download filename
         * @param agent
         *            Client browser
         * @return Download attachment name after encoding
         * @throws IOException
         */
        public static String encodeDownloadFilename(String filename, String agent)
                throws IOException {
            if (agent.contains("Firefox")) { // Firefox
                filename = "=?UTF-8?B?"
                        + new BASE64Encoder().encode(filename.getBytes("utf-8"))
                        + "?=";
                filename = filename.replaceAll("\r\n", "");
            } else { // IE And other browsers
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+"," ");
            }
            return filename;
        }
}

Class FileUtils

Programmer Think

Export Excel data based on POI