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

11thdimension 11thdimension · Accepted Answer · 2017-05-25T06:20:04

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.

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

1 Answers