Import Package This is not much to say, directly paste the key code, JSP as long as the point of an action link on the line.
Poi bag I was using: Poi-3.11-20141221.jar
Pro-Test effective:
Effect:
Action Class Code:
Private InputStream InputStream; (Get,set method omitted) defines an input stream to catch the input stream that is generated by the service class that contains Excel
Public String exportnetworkdevicelist () throws Exception {
Setinputstream (Networkdeviceservice.exportnetworkdevicelist (Net_status, Net_model_number, NET_BUILDING, NET_FLOOR , net_location));
return "Getnetworkdeviceexportlist";
}
Service Class Code: (Generate Excel table code in service class write)
Public InputStream exportnetworkdevicelist (String netstatus,
String Netmodelnumber, String netbuilding, String Netfloor,
String netlocation) {
Hssfworkbook wb = new Hssfworkbook ();
In the second step, add a sheet in WebBook that corresponds to the sheet in the Excel file
Hssfsheet sheet = wb.createsheet ("Table i");
In the third step, add the No. 0 row of the table header to the sheet, noting that the old version of POI has a limit on the number of rows in Excel short
Hssfrow row = Sheet.createrow ((int) 0);
Fourth step, create a cell, and set the value header to center the header
Hssfcellstyle style = Wb.createcellstyle ();
Style.setalignment (Hssfcellstyle.align_center); Create a center format
Write a column name, depending on your needs
Hssfcell cell = Row.createcell (0);
Cell.setcellvalue ("Device Model");
Cell.setcellstyle (style);
Cell = Row.createcell (1);
Cell.setcellvalue ("Number of ports");
Cell.setcellstyle (style);
Cell = Row.createcell (2);
Cell.setcellvalue ("Device name");
Cell.setcellstyle (style);
Cell = Row.createcell (3);
Cell.setcellvalue ("state");
Cell.setcellstyle (style);
Cell = Row.createcell (4);
Cell.setcellvalue ("Building");
Cell.setcellstyle (style);
Cell = Row.createcell (5);
Cell.setcellvalue ("floor");
Cell.setcellstyle (style);
Cell = Row.createcell (6);
Cell.setcellvalue ("position");
Cell.setcellstyle (style);
Cell = Row.createcell (7);
Cell.setcellvalue ("interface");
Cell.setcellstyle (style);
Cell = Row.createcell (8);
Cell.setcellvalue ("IP Address");
Cell.setcellstyle (style);
Cell = Row.createcell (9);
Cell.setcellvalue ("Gateway");
Cell.setcellstyle (style);
Cell = Row.createcell (10);
Cell.setcellvalue ("Remarks");
Cell.setcellstyle (style);
Constructs the database query statement, later I use and from the DAO class to fetch the data, depends on own demand
String hql = "from Networkdevice";
if (netstatus = = null) {
} else {
if (Netstatus.equalsignorecase ("00")) {
HQL + = "n where n.net_status!=null";
} else {
HQL + = "n where n.net_status= '" + netstatus + "'";
}
;
if (!netmodelnumber.isempty ()) {
HQL + = "and N.net_model_number = '" + netmodelnumber + "'";
}
;
if (!netbuilding.isempty ()) {
HQL + = "and n.net_building = '" + netbuilding + "'";
}
;
if (!netfloor.isempty ()) {
HQL + = "and N.net_floor = '" + Netfloor + "'";
}
;
if (!netlocation.isempty ()) {
HQL + = "and N.net_location = '" + netlocation + "'";
}
;
}
HQL + = "ORDER by 1 DESC";
The fifth step, write the Entity data in the actual application of this data from the database,
List<networkdevice> exportlist = networkdevicedaoimpl.exportnetworkdevicelist (HQL);
for (int i = 0; i < exportlist.size (); i++) {
row = Sheet.createrow ((int) i + 1);
Networkdevice Netdevice = Exportlist.get (i);
Fourth step, create the cell and set the value
Row.createcell (0). Setcellvalue (Netdevice.getnet_model_number ());
Row.createcell (1). Setcellvalue (Netdevice.getnet_device_port ());
Row.createcell (2). Setcellvalue (Netdevice.getnet_device_name ());
Row.createcell (3). Setcellvalue (Netdevice.getnet_status ());
Row.createcell (4). Setcellvalue (Netdevice.getnet_building ());
Row.createcell (5). Setcellvalue (Netdevice.getnet_floor ());
Row.createcell (6). Setcellvalue (Netdevice.getnet_location ());
Row.createcell (7). Setcellvalue (Netdevice.getnet_interface ());
Row.createcell (8). Setcellvalue (Netdevice.getnet_ip ());
Row.createcell (9). Setcellvalue (Netdevice.getnet_gateway ());
Row.createcell (Setcellvalue) (Netdevice.getnet_remark ());
}
Automatically set the column width of Excel, depending on your needs
Sheet.autosizecolumn ((short) 0);
Sheet.autosizecolumn ((short) 2);
Sheet.autosizecolumn ((short) 6);
Sheet.autosizecolumn ((short) 7);
Sheet.autosizecolumn ((short) 8);
Sheet.autosizecolumn ((short) 9);
Sheet.autosizecolumn ((short) 10);
Set the file name and use the format date to generate an ID
String filepath= "";
Date dt = new Date ();
DateFormat df = new SimpleDateFormat ("Yyyymmddhhmmss");
String date = df.format (dt). toString ();
FilePath = "Netdevice" + Date + ". xls";
File File=new file (FilePath);
try{
OutputStream out=new fileoutputstream (file);
Wb.write (out);
Out.close ();
}catch (Exception e) {
E.printstacktrace ();
}
InputStream In=null;
try{
In=new fileinputstream (file);
}catch (Exception e)
{
E.printstacktrace ();
}
return in;
}
Strust2 Code:
<action name= "exportnetworkdevicelist" class= "Com.javaweb.action.NetworkDeviceAction"
method= "Exportnetworkdevicelist" >
<result name= "getnetworkdeviceexportlist" type= "Stream" >
<param name= "InputStream" >excelStream</param>
<param name= "ContentType" >application/vnd.ms-excel</param>
<param name= "contentdisposition" >filename= "Netdevice.xls" </param>
</result>
</action>
Javaweb SSH framework uses POI to export Excel, pop-up Save box