Please check my code. There is no error. But still, I am not getting any excel downloaded. The console also shows no error and the sysout also shows the correct sizes of lists. 9 row data is coming from dao to this tpaAuthList. Please help.
@RequestMapping(value = "downloadTpaPreAuthExcel", method = RequestMethod.POST)
public void downloadTpaPreAuthExcel(@RequestParam("typevalue") String typevalue,
@RequestParam("idtypevalue") String idtypevalue, HttpServletRequest request,HttpServletResponse response, HttpSession session) {
Map<String, Object> map = new HashMap<String, Object>();
List<TpaPreAuthModel> tpaAuthList;
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=TpaPreAuth.xls");
try {
tpaAuthList = tpaPreAuthService.getTpaPreAuthSearchDataForExcel(typevalue,idtypevalue);
System.out.println("tpaAuthList size in method-->" tpaAuthList.size());
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("DataSheet");
Map<String, Object[]> data = new LinkedHashMap<>();
data.put(String.valueOf(1), new Object[]{" TPA PRE AUTH LIST"});
data.put(String.valueOf(2), new Object[]{""});
data.put(String.valueOf(3), new Object[]{""});
int i = 5;
data.put("4", new Object[]{"Transaction ID", "Patient Name", "Hospital Name", "Pre Auth Date", "RGHS Card No", "Minutes Elapsed"});
for (TpaPreAuthModel listBean : tpaAuthList) {
data.put(String.valueOf(i), new Object[]{1,2,3,4,5,6});
i ;
}
System.out.println("For loop ended--");
Set<String> keyset = data.keySet();
int rownum = 0;
System.out.println("keyset size-->" keyset.size());
for (String key : keyset) {
HSSFRow row = sheet.createRow(rownum );
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr) {
HSSFCell cell = row.createCell(cellnum );
if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
} else if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
}
}
}
System.out.println("Ket set for loop ended--");
OutputStream outputStream = response.getOutputStream();
System.out.println("Outstream--");
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
}
CodePudding user response：
You could try this:
@RequestMapping(value = "downloadTpaPreAuthExcel", method = RequestMethod.POST, produces = "application/vnd.ms-excel")
public Callable<ResponseEntity<StreamingResponseBody>> downloadTpaPreAuthExcel(
@RequestParam("typevalue") String typevalue,
@RequestParam("idtypevalue") String idtypevalue,
HttpServletRequest request,
HttpServletResponse response,
HttpSession session
) {
return new Callable<ResponseEntity<StreamingResponseBody>>() {
@Override
public ResponseEntity<StreamingResponseBody> call() throws Exception {
return toResponseBody(new Supplier<Workbook>() {
@Override
public Workbook get() {
Map<String, Object> map = new HashMap<String, Object>();
List<TpaPreAuthModel> tpaAuthList;
try {
tpaAuthList = tpaPreAuthService.getTpaPreAuthSearchDataForExcel(typevalue, idtypevalue);
System.out.println("tpaAuthList size in method-->" tpaAuthList.size());
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("DataSheet");
Map<String, Object[]> data = new LinkedHashMap<>();
data.put(String.valueOf(1), new Object[] { " TPA PRE AUTH LIST" });
data.put(String.valueOf(2), new Object[] { "" });
data.put(String.valueOf(3), new Object[] { "" });
int i = 5;
data.put("4", new Object[] { "Transaction ID", "Patient Name", "Hospital Name",
"Pre Auth Date", "RGHS Card No", "Minutes Elapsed" });
for (TpaPreAuthModel listBean : tpaAuthList) {
data.put(String.valueOf(i), new Object[] { 1, 2, 3, 4, 5, 6 });
i ;
}
System.out.println("For loop ended--");
Set<String> keyset = data.keySet();
int rownum = 0;
System.out.println("keyset size-->" keyset.size());
for (String key : keyset) {
HSSFRow row = sheet.createRow(rownum );
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr) {
HSSFCell cell = row.createCell(cellnum );
if (obj instanceof Date) {
cell.setCellValue((Date) obj);
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj);
} else if (obj instanceof String) {
cell.setCellValue((String) obj);
} else if (obj instanceof Double) {
cell.setCellValue((Double) obj);
} else if (obj instanceof Long) {
cell.setCellValue((Long) obj);
}
}
}
System.out.println("Ket set for loop ended--");
OutputStream outputStream = response.getOutputStream();
System.out.println("Outstream--");
return workbook;
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (Exception ex) {
ex.printStackTrace();
}
return null;
}
}, "TpaPreAuth");
}
};
}
private ResponseEntity<StreamingResponseBody> toResponseBody(Supplier<Workbook> workbookSupplier, String fileName) {
Workbook workbook = workbookSupplier.get();
String fileExtension = "xlsx";
return ResponseEntity.ok()
.header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" fileName "." fileExtension)
.contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
.body(new StreamingResponseBody() {
@Override
public void writeTo(OutputStream outputStream) throws IOException {
if (workbook != null) {
try (Workbook workbookRef = workbook) {
workbook.write(outputStream);
if (workbook instanceof SXSSFWorkbook ) {
((SXSSFWorkbook) workbook).dispose();
}
} finally {
exc.printStackTrace();
if (workbook instanceof SXSSFWorkbook ) {
((SXSSFWorkbook) workbook).dispose();
}
}
}
}
}
);
}
CodePudding user response：
Close workbook before it become file. See https://poi.apache.org/apidocs/dev/org/apache/poi/hssf/usermodel/HSSFWorkbook.html#close--
For better
HSSFWorkbook workbook = null;
try {
workbook = ...
//....
} catch (IOException ex) {
//...
}finally {
workbook.close();
}