1.java导出excel报表:
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 header,
List> list,String xlsName,String
type,HttpServletResponse response) {
String result="系统提示:Excel文件导出成功!";
// 以下开始输出到EXCEL
try {
//定义输出流,以便打开保存对话框______________________begin
response.reset();// 清空输出流
response.setHeader("Content-disposition", "attachment;
filename="+ new String(xlsName.getBytes("GB2312"),"ISO8859-1"));
// 设定输出文件头
response.setContentType("application/octet-stream");// 定义输出类型
OutputStream os = response.getOutputStream();// 取得输出流
//定义输出流,以便打开保存对话框_______________________end
/** **********创建工作簿************ */
WritableWorkbook workbook = Workbook.createWorkbook(os);
/** ************设置单元格字体************** */
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL, 10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD);
/** ************以下设置三种单元格样式,灵活备用************ */
// 用于标题居中
WritableCellFormat wcf_center = new WritableCellFormat(BoldFont);
wcf_center.setBorder(Border.ALL, BorderLineStyle.THIN); // 线条
wcf_center.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_center.setAlignment(Alignment.CENTRE); // 文字水平对齐
wcf_center.setWrap(false); // 文字是否换行
// 用于正文居左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.NONE, BorderLineStyle.THIN); // 线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); // 文字垂直对齐
wcf_left.setAlignment(Alignment.LEFT); // 文字水平对齐
wcf_left.setWrap(false); // 文字是否换行
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
WritableSheet sheet = workbook.createSheet("Sheet"+i+1, i);
/** **********设置纵横打印(默认为纵打)、打印纸***************** */
jxl.SheetSettings sheetset = sheet.getSettings();
sheetset.setProtected(false);
sheets[i]=sheet;
}
/** ***************以下是EXCEL开头大标题,暂时省略********************* */
//sheet.mergeCells(0, 0, colWidth, 0);
//sheet.addCell(new Label(0, 0, "XX报表", wcf_center));
/** ***************以下是EXCEL第一行列标题********************* */
for(int j=0;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);
/** **********设置纵横打印(默认为纵打)、打印纸***************** */
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));
}
}
/** ***************以下是EXCEL正文数据********************* */
if(list!=null){
for (int i = 0; i < list.size(); i++)
{
Map 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="未支付";
}else if(orderstatu.equals("1")){
orderstatus="已支付";
}else if(orderstatu.equals("-1")){
orderstatus="已取消";
}else if(orderstatu.equals("-2")){
orderstatus="已失效";
}
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));
}
}
}
/** **********将以上缓存中的内容写到EXCEL文件中******** */
workbook.write();
/** *********关闭文件************* */
workbook.close();
} catch (Exception e) {
result="系统提示:Excel文件导出失败,原因:"+ e.toString();
// System.out.println(result);
e.printStackTrace();
}finally{
if(list!=null)list.clear();
}
return result;
}
2.调用导出方法
List header=new ArrayList();
header.add("用户名");
header.add("订单号");
header.add("订单时间");
header.add("订单金额");
header.add("实际金额");
header.add("订单状态");
header.add("支付方式");
ExportExcel.exportExcel(header,list,"订单表.xls","exportAllOrder",response);
return null;
3.jsp页面,点击导出
导出
4.所使用jar包 jxl-2.6.jar
下载地址:/chiyj/excel%E5%AF%BC%E5%87%BAjar.rar