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

public class ExportXlsAction extends BaseAction<WayBill>{

    private WayBillService  wayBillService;
    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(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);

        // Download export
        // Set header information (set file type xls)
        String filename = "Waybill data.xls";
        String agent = ServletActionContext.getRequest()
                .getHeader("user-agent");//Get the browser type (Firefox, 360, IE)
        filename = FileUtils .encodeDownloadFilename(filename, agent);
                "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

        // 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
public List<WayBill> findwayBills(WayBill wayBill) {
        if (StringUtils.isBlank(wayBill.getWayBillNum())
                && StringUtils.isBlank(wayBill.getSendAddress())
                && StringUtils.isBlank(wayBill.getRecAddress())
                && StringUtils.isBlank(wayBill.getSendProNum())
                && (wayBill.getSignStatus() == null || wayBill.getSignStatus() == 0)) {
            // Unconditional query, query database
            return wayBillRepository.findAll();
        } else {
            // query criteria
            // must Conditions must be met and
            // must not Conditions must not be met not
            // should Conditions can be established or
            BoolQueryBuilder query = new BoolQueryBuilder(); // Boolean query
            // Add criteria to a composite query object
            if (StringUtils.isNoneBlank(wayBill.getWayBillNum())) {
                // Waybill No. query
                QueryBuilder tempQuery = new TermQueryBuilder("wayBillNum",
            if (StringUtils.isNoneBlank(wayBill.getSendAddress())) {
                //Fuzzy query of delivery place
                //Case 1: input "North" as part of the query term, and use fuzzy matching term to query
                QueryBuilder wildcardQuery = new WildcardQueryBuilder(
                        "sendAddress", "*" + wayBill.getSendAddress() + "*");
                //Situation 2: input "Haidian District, Beijing" is a combination of multiple entries, and match each entry after segmentation
                QueryBuilder   queryStringQueryBuilder = new QueryStringQueryBuilder(wayBill.getSendAddress())
                //Two cases or relationship
                BoolQueryBuilder boolQueryBuilder = new BoolQueryBuilder();
            if (StringUtils.isNoneBlank(wayBill.getRecAddress())) {
                // Fuzzy query of receiving place
                QueryBuilder wildcardQuery = new WildcardQueryBuilder(
                        "recAddress", "*" + wayBill.getRecAddress() + "*");
            if (StringUtils.isNoneBlank(wayBill.getSendProNum())) {
                // Express type equivalent query
                QueryBuilder termQuery = new TermQueryBuilder("sendProNum",
            if (StringUtils.isNoneBlank(wayBill.getSendProNum())) {
                // Express type equivalent query
                QueryBuilder termQuery = new TermQueryBuilder("sendProNum",
            if (wayBill.getSignStatus() != null && wayBill.getSignStatus() != 0) {
                // Sign in status query
                QueryBuilder termQuery = new TermQueryBuilder("signStatus",

            SearchQuery searchQuery = new NativeSearchQuery(query);
            // Integer.MAX_VALUE: Easy access to all queried data
            Pageable pageable = new PageRequest(0, Integer.MAX_VALUE);
            searchQuery.setPageable(pageable ); // Paging effect
            // Conditional query, query index library
            return wayBillIndexRepository.search(searchQuery).getContent();
Service layer code
package cn.itcast.bos.dao.index;

import org.springframework.data.elasticsearch.repository.ElasticsearchRepository;

import cn.itcast.bos.domain.take_delivery.WayBill;
//Multi condition query is caching
public interface WayBillIndexRepository extends ElasticsearchRepository<WayBill, Integer> {

Dao layer code implementation

Final effect:


Because we don't know much about the API of POI, the layout of Excel data printed out is adjusted manually.


Topics: Java Excel Firefox IE