I create Excel file using apache poi. but when i open the excell file it says malformed file need to recover

Question

when i open the file it says malformed file and need to recover. when i press okay it opens and the data i wrote is there. it says**(Excel completed file level validation and repair. Some parts of this workbook may have been repaired or discarded.)** i want to correct this error. how do i make this malformed excel file to well formed excel file?

here is the writing part.

 ArrayList<Schedule> schds = serviceResponce.getSchedules();
                    XSSFWorkbook workbook = new XSSFWorkbook();
                    XSSFSheet sheet = workbook.createSheet("sheet");
                    CellStyle styleHeaders;
DataFormat format = workbook.createDataFormat();
CellStyle styleDataCells;
                    DataFormatter downloadForatter=new DataFormatter();
                    styleDataCells = workbook.createCellStyle();
                    for (Schedule sch : schds) {
                        Row row = sheet.createRow(++rowCount);
                        Cell cellScheduleId = row.createCell(0);
                        Cell cellRouteId = row.createCell(1);
                        Cell cellDepTime = row.createCell(2);
                        Cell cellArrTime = row.createCell(3);
                        Cell cellFromTo = row.createCell(4);
                        Cell cellDay = row.createCell(5);
                        Cell cellStatus = row.createCell(6);

                        downloadForatter.formatCellValue(cellDay);
                        cellScheduleId.setCellValue(Integer.parseInt(sch.getSchedule_id()));
                        styleDataCells.setDataFormat(format.getFormat("0"));
                        cellScheduleId.setCellStyle(styleDataCells);

                        cellRouteId.setCellValue(Integer.parseInt(sch.getRoute_id()));
                        styleDataCells.setDataFormat(format.getFormat("0"));
                        cellRouteId.setCellStyle(styleDataCells);

                        cellDepTime.setCellValue(sch.getDeptature_time());
                        styleDataCells.setDataFormat(format.getFormat("hh:mm"));
                        cellDepTime.setCellStyle(styleDataCells);

                        cellArrTime.setCellValue(sch.getArrival_time());
                        styleDataCells.setDataFormat(format.getFormat("hh:mm"));
                        cellArrTime.setCellStyle(styleDataCells);

                        cellFromTo.setCellValue(sch.getFrom_to());
                        styleDataCells.setDataFormat(format.getFormat("@"));
                        cellFromTo.setCellStyle(styleDataCells);

                        cellDay.setCellValue(sch.getDay());
                        styleDataCells.setDataFormat(format.getFormat("@"));
                        cellDay.setCellStyle(styleDataCells);

                        if (sch.getStatus().equals("Y")) {
                            cellStatus.setCellValue("Active");
                            styleDataCells.setDataFormat(format.getFormat("@"));
                            cellStatus.setCellStyle(styleDataCells);
                        } else {
                            cellStatus.setCellValue("Inactive");
                            styleDataCells.setDataFormat(format.getFormat("@"));
                            cellStatus.setCellStyle(styleDataCells);
                        }
                    }
                    try {
                        String downloadPath = getServletContext().getRealPath("/") + "ExpSchedules.xlsx";
                        File excelFile = new File(downloadPath);
                        if (excelFile != null && excelFile.exists()) {
                            excelFile.delete();
                        }
                        excelFile.createNewFile();
                        FileOutputStream outputStream = new FileOutputStream(downloadPath);
                        workbook.write(outputStream);
                        workbook.close();
                        log.info("path " + downloadPath);

//                        
                        String original_filename = "ExpSchedules.xlsx";
                        ServletContext sc = this.getServletContext();
                        InputStream is = new FileInputStream(excelFile);
                        if (is != null && is.available() > 0) {
                            log.info("IS is not null");
                        } else {
                            log.info("IS is null");
                        }
                        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                        response.setHeader("Content-Disposition", "attachment; filename=\"" + original_filename + "\"");
//                        
//                        File file = new File(downloadPath);
//                        FileInputStream fileIn = new FileInputStream(file);
                        ServletOutputStream outA = response.getOutputStream();
//
                        byte[] outputByte = new byte[4096];
//
                        while (is.read(outputByte, 0, 4096) != -1) {
                            outA.write(outputByte, 0, 4096);
                        }
                        is.close();
                        outA.flush();
                        outA.close();

You don't close the output stream. Also, you always dump the whole buffer, at the end of the file this means you dump part of the buffer from the previous iteration - basically you append garbage to the end of the file. Consider using the `Files` utility class. — Boris the Spider, May 25 '17 at 06:11

score 3 · Accepted Answer · answered May 25 '17 at 06:20

Assuming that you're talking about the file that you download from the Servlet and not the file that you create on the server.

Issue is with the following portion of the code.

byte[] outputByte = new byte[4096];

while (is.read(outputByte, 0, 4096) != -1) {
    outA.write(outputByte, 0, 4096);
}

You're always trying to write 4096 bytes in the response output stream. Your file content won't always be a multiple of 4096, this code should be modified as below.

byte[] outputByte = new byte[4096];
int readLen = -1;

while ( (readLen = is.read(outputByte)) != -1) {
    outA.write(outputByte, 0, readLen);
}

Apart from it there's are multiple issues with this code. Check following

You shouldn't close response output stream, let the Servlet container handle it. Rule is if you didn't open it, don't close it.
You're expecting that only one user will download file at one time, but Servlet container is multithreaded environment. Meaning multiple users can call the download Servlet at the same time. Then this code will be writing to the same file in two different threads, which will either generate exception or will corrupt the file. You need to create the a random name for the output file, or better yet use File.createTempFile(). After it's flushed to the response output stream, delete it.
Finally please modulerize the code, separate the file creation code into another method.

That did work 11thdimension. Thank you. And this application will only be used by one admin user. Only he will download the file. Is it best practice to Create File.createTempFile() or should i keep it as it is?? — Neshan Manilka, May 25 '17 at 07:30
You can use temp file method, just remember to cleanup the file afterwards. Even if it's used by single user you don't know if the user won't refresh the page during the file download. Then same user will have instantiated 2 different threads on the server. Best practice would be to create different files for different requests. Another simpler method would be `synchronize` the servlet method ie. doPost/doGet — 11thdimension, May 25 '17 at 07:40
"_Another simpler method would be synchronize the servlet method ie. doPost/doGet_" - honestly, that comment deserves a downvote of the whole answer. — Boris the Spider, May 25 '17 at 21:21

I create Excel file using apache poi. but when i open the excell file it says malformed file need to recover

1 Answers1