Example of jsp using POI to generate an Excel file and export it on the page, poiexcel

Example of jsp using POI to generate an Excel file and export it on the page, poiexcel

In java, Excel can be exported using two components: jxl, POI, and POI. You can generate a file on the server and download it. You can also use the output stream to directly display a dialog box on the webpage prompting you to save or download the file. The file generation method will cause spam files to exist in the server, and the implementation method is not very elegant. So here I am using the method of output stream directly.

1. Modify CONF/WEB. xml of the web server and add Xml code.

<mime-mapping>     <extension>xls</extension>     <mime-type>application/vnd.ms-excel</mime-type>  </mime-mapping> 

If this is not added, the JSP file is downloaded from the web page.

2. download. jsp file

<% @ Page contentType = "application/vnd. ms-excel "language =" java "import =" java. util. *, com. shangyu. action. writeExcel "pageEncoding =" GBK "%> <% response. setHeader ("Content-Disposition", "attachment=filename=test123.xls"); // specify the downloaded file name response. setContentType ("application/vnd. ms-excel "); WriteExcel we = new WriteExcel (); we. getExcel ("111.xls", response. getOutputStream (); %>

Do not have html code, except for the code in the middle. Do not have spaces in other places. Otherwise, an exception will occur in the background during file export. Although it does not affect the use of the program, it looks uncomfortable.

3. WriteExcel. java generates the Excel JavaBean. For complex applications, see the API

Package com. shangyu. action; import java. io. *; import org. apache. poi. hssf. usermodel. HSSFWorkbook; import org. apache. poi. hssf. usermodel. HSSFSheet; import org. apache. poi. hssf. usermodel. HSSFRow; import org. apache. poi. hssf. usermodel. HSSFCell; public class WriteExcel {public void getExcel (String sheetName, OutputStream output) {HSSFWorkbook wb = new HSSFWorkbook (); HSSFSheet sheet1 = wb. createSheet ("sheet1"); HSSFRow row = sheet1.createRow (short) 0); HSSFCell cell = row. createCell (short) 0); cell. setCellValue (1); row. createCell (short) 1 ). setCellValue (2); row. createCell (short) 2 ). setCellValue (3); row. createCell (short) 3 ). setCellValue ("Chinese character"); row = sheet1.createRow (short) 1); cell = row. createCell (short) 0); cell. setCellValue (1); row. createCell (short) 1 ). setCellValue (2); row. createCell (short) 2 ). setCellValue (3); row. createCell (short) 3 ). setCellValue ("Chinese character"); // FileOutputStream fileout = new FileOutputStream ("workbook.xls"); try {output. flush (); wb. write (output); output. close ();} catch (IOException e) {e. printStackTrace (); System. out. println ("Output is closed ");}}}

In the preceding three steps, you can directly generate an Excel file for download or storage, which is useful in some information systems.