Export excel reports in java and javaexcel reports

Export excel reports in java and javaexcel reports

1. Export an excel report in java:

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 prompt: Excel file exported successfully! ";
// Output the following to EXCEL
Try {
// Define the output stream to open the Save dialog box __________________ begin
Response. reset (); // clear the output stream
Response. setHeader ("Content-disposition", "attachment; filename =" + new String (xlsName. getBytes ("GB2312"), "ISO8859-1 "));
// Set the output file header
Response. setContentType ("application/octet-stream"); // defines the output type.
OutputStream OS = response. getOutputStream (); // gets the output stream
// Define the output stream to open the Save dialog box ___________________ end
/************* Create a workbook *************/
WritableWorkbook workbook = Workbook. createWorkbook (OS );
/************** Set the cell font ***************/
WritableFont NormalFont = new WritableFont (WritableFont. ARIAL, 10 );
WritableFont BoldFont = new WritableFont (WritableFont. ARIAL, 10, WritableFont. BOLD );
/************** Set the following three cell styles and use them flexibly *************/
// Used to center the title
WritableCellFormat wcf_center = new WritableCellFormat (BoldFont );
Wcf_center.setBorder (Border. ALL, BorderLineStyle. THIN); // line
Wcf_center.setVerticalAlignment (VerticalAlignment. CENTRE); // vertical text alignment
Wcf_center.setAlignment (Alignment. CENTRE); // horizontal Alignment of Text
Wcf_center.setWrap (false); // whether the text is wrapped
// The left of the text
WritableCellFormat wcf_left = new WritableCellFormat (NormalFont );
Wcf_left.setBorder (Border. NONE, BorderLineStyle. THIN); // line
Wcf_left.setVerticalAlignment (VerticalAlignment. CENTRE); // vertical text alignment
Wcf_left.setAlignment (Alignment. LEFT); // horizontal Alignment of Text
Wcf_left.setWrap (false); // whether the text is wrapped
WritableSheet [] sheets = null;
If (list! = Null & list. size ()> 0 ){
*************/
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 (vertical by default), print paper ******************/
Jxl. SheetSettings sheetset = sheet. getSettings ();
Sheetset. setProtected (false );
Sheets [I] = sheet;
}
/***************** The title starting with EXCEL is as follows, **********************/
// Sheet. mergeCells (0, 0, colWidth, 0 );
// Sheet. addCell (new Label (0, 0, "XX Report", wcf_center ));
/**************** The title of the first row and column in EXCEL is **************** ******/
For (int j = 0; j <scount; j ++ ){
For (int I = 0; I Sheets [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 (vertical by default), print paper ******************/
Jxl. SheetSettings sheetset = sheet. getSettings ();
Sheetset. setProtected (false );
Sheets [0] = sheet;
For (int I = 0; I Sheets [0]. addCell (new Label (I, 0, header. get (I), wcf_center ));
}
}
***************** *****/
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 = "unpaid ";
} Else if (orderstatu. equals ("1 ")){
Orderstatus = "paid ";
} Else if (orderstatu. equals ("-1 ")){
Orderstatus = "canceled ";
} Else if (orderstatu. equals ("-2 ")){
Orderstatus = "invalid ";
}
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 ));
}
}
}
/*********** Write the content in the above cache to the EXCEL file *********/
Workbook. write ();
**************/
Workbook. close ();
} Catch (Exception e ){
Result = "system prompt: Excel File Export failed, cause:" + e. toString ();
// System. out. println (result );
E. printStackTrace ();
} Finally {
If (list! = Null) list. clear ();
}
Return result;
}

2. Call 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 table .xls", "exportAllOrder", response );
Return null;

3. On the jsp page, click Export

<A href = "access the backend export path (Access 2)"> export </a>

4. jxl-2.6.jar of the jar package used

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