Javaweb Project export Excel file and popup download box

Introduction

Reports are frequently involved in Java Web development, and recent projects need to implement the ability to display the data in a database as a table and to export it as an Excel file.

Related JAR Packages

Using POI can be a good solution to the import and export problems of Excel, POI:
Poi-3.6-20091214.jar

Key code

Import the above jar packages first.
When generating Excel, the General data source form is a list, and the following code is posted to generate Excel format:

/** * Generate Excel actions below * ///1.Create a workbook that corresponds to an Excel file Hssfworkbook wb = new Hssfworkbook ();//2.Add a sheet in workbook that corresponds to one sheet hssfsheet sheet = WB in Excel. Createsheet("XXX table");//3.Add a header to the sheet0Line, older POI has a limit on the number of Excel row numbers short Hssfrow row = sheet. CreateRow((int)0);//4.Create a cell, set the value header, set the table header centered Hssfcellstyle style = WB. Createcellstyle();Center format Style. Setalignment(Hssfcellstyle. ALIGN_center);Set table header Hssfcell cell = row. Createcell(0);Cell. Setcellvalue("Table header 1");Cell. Setcellstyle(style);Cell = row. Createcell(1);Cell. Setcellvalue("Table Header 2");Cell. Setcellstyle(style);Cell = row. Createcell(2);Cell. Setcellvalue("Table Header 3");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);

To write data to Excel after the Excel format is generated:

Loop writes data to Excel for (int i =0; i < lists.size (); i++) {row = Sheet. CreateRow(int) i +1);List list= Lists. Get(i);Create cell, Set 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(4). Setcellvalue(List. GetXXX());}

The generated Excel is then streamed out.
* Do not eject the download box

out =new FileOutputStream("E:/XXX.xls");wb.write(out); out.close();

* Popup Download box

String FileName ="XXX table"; Bytearrayoutputstream OS =NewBytearrayoutputstream (); Wb.write (OS);byte[] content = Os.tobytearray (); InputStream is=NewBytearrayinputstream (content);//Set the response parameter to open the download pageRes.reset (); Res.setcontenttype ("Application/vnd.ms-excel;charset=utf-8"); Res.setheader ("Content-disposition","Attachment;filename="+NewString ((FileName +". xls"). GetBytes (),"Iso-8859-1")); Servletoutputstream out= Res.getoutputstream (); Bufferedinputstream bis =NULL; Bufferedoutputstream BOS =NULL;Try{bis =NewBufferedinputstream ( is); BOS =NewBufferedoutputstream ( out);byte[] Buff =New byte[2048];intBytesread;//Simple read/write loop.                 while(-1! = (Bytesread = bis.read (Buff,0, buff.length)) {Bos.write (buff,0, bytesread); }            }Catch(Exception e) {//Todo:handle exceptionE.printstacktrace (); }finally{if(bis! =NULL) Bis.close ();if(Bos! =NULL) Bos.close (); }

Once you have done this, you can jump to another page.
At the same time POI can also be the Excel upload resolution displayed in the Web page, this other article summarizes.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Javaweb Project export Excel file and popup download box