CODEWITHSUNDEEP
excelspring-bootspring-mvcapache-poithymeleaf
Nital
Nital

Reputation: 6114

Not able to generate Excel file correctly using Spring Boot and Apache POI

I have a very basic SpringBoot and Thymeleaf based application that is tied with Oracle DB.

My application currently reads from a table and displays it on the page via a Controller and Thymeleaf UI template. I also have a link at the bottom of this table which says "Export to Excel" which calls a method in service layer and generates and even downloads that .XLS document successfully.

However, the problem is that the excel getting generated is an exact replica of what is on the page (with some extra empty columns on the right) even though I have created the sheet manually by writing code which adds an extra column and a different title.

Not sure why isn't my code getting invoked and why is my HTML table data just getting exported to the excel sheet as is.

ExcelReportController.java

@Controller
@Log4j2
public class ExcelReportController {

    private static final String EXCEL_FILE_NAME = "applications.xls";
    @Autowired
    private LinkApplService linkApplService;

    @GetMapping("/excel")
    public ModelAndView showPage() {
        return new ModelAndView("applications", "linkAppls", linkApplService.getAllLinkAppls());
    }

    @GetMapping("/download")
    public ModelAndView download(HttpServletResponse response) {
        response.setHeader("Content-disposition", "attachment; filename=" + EXCEL_FILE_NAME);
        return new ModelAndView("applications", "linkAppls", linkApplService.getAllLinkAppls());
    }

}

ExcelBuilderService.java

@Service
public class ExcelBuilderService extends AbstractXlsView {

    @Override
    protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request,
                                      HttpServletResponse response) {
        Iterable<LinkAppl> linkAppls = (Iterable<LinkAppl>) model.get("linkAppls");
        //THIS SHEET WITH TITLE NOT GETTING SHOWN
        Sheet sheet = workbook.createSheet("All Applications List");
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("DESC");
        header.createCell(2).setCellValue("DESC");
        header.createCell(3).setCellValue("LINK_NOTES"); //THIS COLUMN NOT GETTING DISPLAYED
        int rowNum = 1;
        for (LinkAppl la : linkAppls) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(la.getApplId());
            row.createCell(1).setCellValue(la.getApplDeTx());
            row.createCell(2).setCellValue(la.getApplActvCd());
            row.createCell(3).setCellValue(la.getLinkNotesTx());
        }
    }

}

LinkAppl.java

@Entity
@Table(name = "LINK_APPL")
public class LinkAppl {
    private String applId;
    private String applDeTx;
    private String seqNbResetCd;
    private String intfMsgFrmtCd;
    private String sndRcvCd;
    private String applAcptMsgFrmtCd;
    private String applActvCd;
    private String sodEodIn;
    private String linkNotesTx;
    private String lastModByUsrId;
    private Timestamp lastModTs;
    private String sndCnctStsCd;
    private Long maxSeqNb;
    private String batIntfIn;
    private String gfpSrcSiteCd;
    private String rcvRterAckmentIn;
    private String rcvMqCodIn;
    private String fxApplIn;
    private String rcvEodpAckmentIn;

    //getters and setters go here

}

applications.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="utf-8">
        <meta http-equiv="x-ua-compatible" content="ie=edge">
        <title>Link Statistics Report</title>
        <meta name="viewport" content="width=device-width, initial-scale=1">
    </head>
    <body>
        <h4>Excel Generation Demo</h4>
        <table border="1">
            <thead>
                <tr>
                    <td>App ID</td>
                    <td>Name</td>
                    <td>Active</td>
                </tr>
            </thead>
            <tbody>
                <tr th:each="a : ${linkAppls}">
                    <td th:text="${a.applId}">App ID</td>
                    <td th:text="${a.getApplDeTx()}">Description</td>
                    <td th:text="${a.applActvCd}">Active</td>
                </tr>
            </tbody>
            <tfoot>
                <tr>
                    <td colspan="7">
                        <a href="/download">Export to Excel</a>
                    </td>
                </tr>
            </tfoot>
        </table>
    </body>
</html>

pom.xml

<!-- for handling .xls files (older format) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- for handling .xlsx files (newer format) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

enter image description here

Upvotes: 1

Views: 6563

Answers (2)

Nital
Nital

Reputation: 6114

So finally with immense help from @Metroids I was able to download an excel file via controller. I also experimented with downloading an existing file located in src/main/resources folder. Here is the source code for better understanding.

applications.html

 <a href="/download">Export to Excel</a>&nbsp;
 <a href="/buildNDownload">Export to Excel 2</a>

ExcelReportController.java

@Controller
@Log4j2
public class ExcelReportController {

    private static final String EXCEL_FILE_NAME = "applications.xls";
    @Autowired
    private LinkApplService linkApplService;

    @GetMapping("/excel")
    public ModelAndView showPage() {
        return new ModelAndView("applications", "linkAppls", linkApplService.getAllLinkAppls());
    }

    @GetMapping(value = "/download")
    public void download(HttpServletResponse response) {
        response.addHeader("Content-Disposition", "attachment; filename=" + EXCEL_FILE_NAME);
        try {
            //download an existing file located in src/main/resources folder
            File file = ResourceUtils.getFile("classpath:" + EXCEL_FILE_NAME);
            InputStream inputStream = new FileInputStream(file);
            IOUtils.copy(inputStream, response.getOutputStream());
            response.flushBuffer();
            inputStream.close();
        } catch (IOException e) {
            log.error("Error while locating file", e);
        }
    }

    @GetMapping(value = "/buildNDownload")
    public void buildNDownload(HttpServletResponse response) throws IOException {
        response.setHeader("Content-disposition", "attachment; filename=applications_new.xlsx");
        Iterable<LinkAppl> linkAppls = linkApplService.getAllLinkAppls();
        //build a file from scratch and then download
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("All Applications List");
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("ID");
        header.createCell(1).setCellValue("DESC");
        header.createCell(2).setCellValue("ACTIVE");
        header.createCell(3).setCellValue("LINK_NOTES");
        int rowNum = 1;
        for (LinkAppl la : linkAppls) {
            Row row = sheet.createRow(rowNum++);
            row.createCell(0).setCellValue(la.getApplId());
            row.createCell(1).setCellValue(la.getApplDeTx());
            row.createCell(2).setCellValue(la.getApplActvCd());
            row.createCell(3).setCellValue(la.getLinkNotesTx());
        }
        workbook.write(response.getOutputStream());
    }

}

pom.xml

<!-- for handling .xls files (older format) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- for handling .xlsx files (newer format) -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>

Upvotes: 0

Metroids
Metroids

Reputation: 20487

The two ModelAndViews in your controllers are exactly the same.

return new ModelAndView("applications", "linkAppls", linkApplService.getAllLinkAppls());

is exactly the same in the download controller:

return new ModelAndView("applications", "linkAppls", linkApplService.getAllLinkAppls());

Setting the content-disposition doesn't change anything in the way spring is handling your request, and if you opened up applications.xls in a text editor, you would discover that you've just downloaded the html of your page renamed to be an .xls file. You need to figure out how to correctly wire up and use the AbstractXlsView that you've created.

Downloading a file:

@GetMapping("/download")
public void download(HttpServletResponse response) throws Exception {
    Iterable<LinkAppl> linkAppls = linkApplService.getAllLinkAppls();

    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("All Applications List");
    Row header = sheet.createRow(0);
    header.createCell(0).setCellValue("ID");
    header.createCell(1).setCellValue("DESC");
    header.createCell(2).setCellValue("DESC");
    header.createCell(3).setCellValue("LINK_NOTES");
    int rowNum = 1;
    for (LinkAppl la : linkAppls) {
        Row row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(la.getApplId());
        row.createCell(1).setCellValue(la.getApplDeTx());
        row.createCell(2).setCellValue(la.getApplActvCd());
        row.createCell(3).setCellValue(la.getLinkNotesTx());
    }

    response.setHeader("Content-disposition", "attachment; filename=" + EXCEL_FILE_NAME);
    workbook.write(response.getOutputStream());
}

Upvotes: 2

Related Questions