Java Export Excel Report

1.java Export Excel Report:

Package cn.jcenterhome.util;

Import Java.io.OutputStream;
Import java.util.List;
Import Java.util.Map;
Import Javax.servlet.http.HttpServletResponse;
Import JXL. Workbook;
Import jxl.format.Alignment;
Import Jxl.format.Border;
Import Jxl.format.BorderLineStyle;
Import jxl.format.VerticalAlignment;
Import Jxl.write.Label;
Import Jxl.write.WritableCellFormat;
Import Jxl.write.WritableFont;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
public class Exportexcel {

Public final static String Exportexcel (list<string> header, list<map<string, object>> list,string Xlsname,string type,httpservletresponse response) {
String result= "System tip: Excel file exported successfully! ";
The following start output to Excel
try {
Define the output stream to open the Save dialog box ______________________begin
Response.reset ();//emptying the output stream
Response.setheader ("Content-disposition", "attachment; Filename= "+ New String (Xlsname.getbytes (" GB2312 ")," iso8859-1 "));
Set Output file header
Response.setcontenttype ("Application/octet-stream");//define Output type
OutputStream OS = Response.getoutputstream ();//Get output stream
Define the output stream to open the Save dialog box _______________________end
/** ********** Create Workbook ************ */
Writableworkbook workbook = workbook.createworkbook (OS);
/** ************ Set Cell font ************** */
Writablefont Normalfont = new Writablefont (writablefont.arial, 10);
Writablefont Boldfont = new Writablefont (writablefont.arial, 10,writablefont.bold);
/** ************ The following three kinds of cell styles, flexible standby ************ */
Center for Title
Writablecellformat wcf_center = new Writablecellformat (Boldfont);
Wcf_center.setborder (Border.all, Borderlinestyle.thin); Lines
Wcf_center.setverticalalignment (Verticalalignment.centre); Vertical alignment of text
Wcf_center.setalignment (Alignment.centre); Horizontal alignment of text
Wcf_center.setwrap (FALSE); Text is wrapped
For the body left
Writablecellformat wcf_left = new Writablecellformat (Normalfont);
Wcf_left.setborder (Border.none, Borderlinestyle.thin); Lines
Wcf_left.setverticalalignment (Verticalalignment.centre); Vertical alignment of text
Wcf_left.setalignment (Alignment.left); Horizontal alignment of text
Wcf_left.setwrap (FALSE); Text is wrapped
Writablesheet [] sheets = null;
if (List!=null&&list.size () >0) {
/** ********** Creating Worksheets ************ */
int scount = List.size ()/65535 + ((list.size ()%65535) >0?1:0);
sheets = new Writablesheet[scount];
for (int i=0;i<scount;i++) {
Writablesheet sheet = workbook.createsheet ("sheet" +i+1, i);
/** ********** Set Vertical and horizontal printing (default is vertical), printer paper ***************** */
Jxl. Sheetsettings Sheetset = Sheet.getsettings ();
Sheetset.setprotected (FALSE);
Sheets[i]=sheet;
}
/** *************** The following is the beginning of Excel headlines, temporarily omit ********************* */
Sheet.mergecells (0, 0, colwidth, 0);
Sheet.addcell (New Label (0, 0, "xx report", Wcf_center));
/** *************** The following is the first row of Excel headings ********************* */
for (int j=0;j<scount;j++) {
for (int i=0;iSheets[j].addcell (New Label (I, 0,header.get (i), wcf_center));
}
}
}else{
sheets = new writablesheet[header.size ()];
Writablesheet sheet = workbook.createsheet ("sheet" +1, 0);
/** ********** Set Vertical and horizontal printing (default is vertical), printer paper ***************** */
Jxl. Sheetsettings Sheetset = Sheet.getsettings ();
Sheetset.setprotected (FALSE);
Sheets[0]=sheet;
for (int i=0;iSheets[0].addcell (New Label (I, 0,header.get (i), wcf_center));
}
}
/** *************** The following is the Excel body data ********************* */
if (list!=null) {

for (int i = 0; i < list.size (); i++)
{
Map<string, object> value=list.get (i);
int s = ((i+1)/65535) + (((i+1)%65535) >0?1:0)-1;
SYSTEM.OUT.PRINTLN ("Export I position is:" +i+ ", S value is:" +s);
int j = i%65535;
Writablesheet sheet = sheets[s];

if (Type.equals ("Exportallorder")) {
Sheet.addcell (New Label (0, J+1,value.get ("username") ==null? ": Value.get (" username "). toString (), wcf_left));
Sheet.addcell (New Label (1, J+1,value.get ("OrderNo") ==null? ": Value.get (" OrderNo "). ToString (), wcf_left));
Sheet.addcell (New Label (2, J+1,value.get ("Ordertime") ==null? ": Value.get (" Ordertime "). ToString (), wcf_left));
Sheet.addcell (New Label (3, J+1,value.get ("Ordermoney") ==null? ": Value.get (" Ordermoney "). ToString (), wcf_left));
Sheet.addcell (New Label (4, J+1,value.get ("Realmoneys") ==null? ": Value.get (" Realmoneys "). ToString (), wcf_left));
String orderstatu=value.get ("Orderstatu") ==null? ": Value.get (" Orderstatu "). ToString ();
String orderstatus= "";
if (Orderstatu.equals ("0")) {
Orderstatus= "not paid";
}else if (orderstatu.equals ("1")) {
Orderstatus= "Paid";
}else if (orderstatu.equals ("1")) {
Orderstatus= "Canceled";
}else if (Orderstatu.equals ("2")) {
Orderstatus= "has lapsed";
}
Sheet.addcell (New Label (5, j+1,orderstatus,wcf_left));
Sheet.addcell (New Label (6, J+1,value.get ("Paymentway") ==null? ": Value.get (" Paymentway "). ToString (), wcf_left));
}
}
}
/** ********** writes the contents of the above cache to an Excel file ******** */
Workbook.write ();
/** ********* Close File ************* */
Workbook.close ();
} catch (Exception e) {
result= "System tip: Excel file export failed, Reason:" + e.tostring ();
SYSTEM.OUT.PRINTLN (result);
E.printstacktrace ();
}finally{
if (list!=null) list.clear ();
}
return result;
}

2. Calling the Export method

List<string> header=new arraylist<string> ();
Header.add ("User name");
Header.add ("Order number");
Header.add ("Order Time");
Header.add ("Order Amount");
Header.add ("actual amount");
Header.add ("Order Status");
Header.add ("payment method");
Exportexcel.exportexcel (Header,list, "order form. xls", "Exportallorder", response);
return null;

3.jsp page, click Export

<a href= "Access to background export path ( Access 2)" > Export </a>

4. The jar package used Jxl-2.6.jar

: Http://files.cnblogs.com/chiyj/excel%E5%AF%BC%E5%87%BAjar.rar

Java Export Excel Report