1. Introduction
Reports are often involved in Java Web development. In recent projects, we need to display the data in the database as a table and export it as an Excel file.
2. Related jar packages
Using POI can solve the problems of Excel import and export well. POI download address:
poi-3.6-20091214.jar
3. Key code
First import the above jar package.
When generating excel, the general data source form is a List. The following is the code that generates Excel format:
/** * The following is the generation of Excel operation*/ // 1. Create a workbook, corresponding to an Excel file, HSSFWorkbook wb = new HSSFWorkbook(); // 2. Add a sheet in the workbook, corresponding to a sheet in Excel HSSFSheet sheet = wb.createSheet("XXX table"); // 3. Add the 0th row of the table header in the sheet. The old version poi has a limit on the number of excel rows short HSSFRow row = sheet.createRow((int) 0); // 4. Create a cell, set the value table header, and set the table header to center HSSFCellStyle style = wb.createCellStyle(); // Centering format style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // Set the table header HSSFCell cell = row.createCell(0); cell.setCellValue("Table header1"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("Table header2"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("Table header3"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("Table header 4"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("Table header 5"); cell.setCellStyle(style);After generating the excel format, you need to write the data to excel:
// Loop to write data to Excel for (int i = 0; i < lists.size(); i++) { row = sheet.createRow((int) i + 1); List list= lists.get(i); // Create a cell and set the value row.createCell(0).setCellValue(list.getXXX()); row.createCell(1).setCellValue(list.getXXX()); row.createCell(2).setCellValue(list.getXXX()); row.createCell(3).setCellValue(list.getXXX()); row.createCell(3).setCellValue(list.getXXX()); row.createCell(4).setCellValue(list.getXXX()); } The generated Excel will then be output as stream.
*No download box pops up
FileOutputStream out =new FileOutputStream("E:/XXX.xls");wb.write(out); out.close();*Popt up the download box
String fileName = "XXX table"; ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // Set the response parameter to open the download page res.reset(); res.setContentType("application/vnd.ms-excel;charset=utf-8"); res.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1")); ServletOutputStream out = res.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (Exception e) { // TODO: handle exception e.printStackTrace(); } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } After completing the above operations, you can jump to other pages.
At the same time, POI can also display Excel upload and parsing on the web page. Please stay tuned for this other article summary!
The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.