function exportExcel() { var cond = <%=!cond.equals("")?"\""+cond+"\"":"''"%>; var actionCode = "cqdw,单 位,clmc,车辆品牌,fdjh,发动机号,cjh,车架号,clpl,排 量,gzsj,购车时间,cqdw,资金来源,clpz,车牌号码,bxcbr,承办人,clzbh,自编号";
var url = "xzdjbbExportExcel.jsp?fieldName="+ actionCode+ "&rhsj=<%=cdrq%>&rhsjq=<%=cdrqq%>&rhsjz=<%=cdrqz%>&fileName=新增车辆报表.xls&cond="+cond; window.location= url; } 这个是调用导出按钮的js代码 var actionCode 是需要显示列的名字 url中式一些参数项目刚用过 我给你代码 不明白的地方问哈 给你关键代码吧导出的jsp页面代码<%@ page language="java" import="com.gt.dataObj.function.jjd.*,com.gt.util.common.*,com.gt.dataObj.function.jjd.clgl.*,java.util.*,java.io.*, jxl.CellView,jxl.Workbook,jxl.format.Alignment,jxl.format.Border,jxl.format.BorderLineStyle,jxl.format.Colour, jxl.write.Label,jxl.write.WritableCellFormat,jxl.write.WritableFont,jxl.write.WritableSheet,jxl.write.WritableWorkbook, jxl.write.WriteException,jxl.write.biff.RowsExceededException" pageEncoding="gb2312"%> <% //设备响应格式 response.reset(); response.setContentType("application/vnd.ms-excel"); String cond= request.getParameter("cond"); String fieldName= request.getParameter("fieldName"); String fileName= request.getParameter("fileName"); String bmmc= request.getParameter("rhsj"); String rhsjq= request.getParameter("rhsjq"); String rhsjz= request.getParameter("rhsjz"); if(bmmc == null || bmmc.equals("")){ if(rhsjq!=null&&!rhsjq.equals("")&&rhsjz!=null&&!rhsjz.equals("")){ bmmc=" 起始日期:"+UTIL.convertStr(rhsjq)+"结束日期:"+UTIL.convertStr(rhsjz); }else{ bmmc = ":全部"; } }else{ bmmc=" 年度"+UTIL.convertStr(bmmc); } List list = new ArrayList(); // 从数据库取数据 CarFunc type = new CarFunc(); CarFuncs clxx = new CarFuncs(); list = clxx.getRecordByCond(cond); String arr[] = fieldName.split(",");// 创建生成的Excel生成路径 // OutputStream os; WritableWorkbook wwk = null; //url提交字符转码 fileName = new String(fileName.getBytes("iso-8859-1"), "gb2312");// 表头起始行 try { response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("gb2312"), "ISO8859_1")); WritableFont wfont = new WritableFont( WritableFont.createFont("黑体"), 20); WritableFont font5 = new WritableFont( WritableFont.createFont("黑体"), 10); WritableFont font6 = new WritableFont( WritableFont.createFont("黑体"), 10); wfont.setColour(Colour.BLACK); wfont.setBoldStyle(WritableFont.BOLD); font5.setColour(Colour.BLACK); font5.setBoldStyle(WritableFont.BOLD); font6.setColour(Colour.BLACK); font6.setBoldStyle(WritableFont.BOLD); WritableCellFormat font = new WritableCellFormat(); WritableCellFormat font2 = new WritableCellFormat(font5); WritableCellFormat font3 = new WritableCellFormat(font6); WritableCellFormat font1 = new WritableCellFormat(); WritableCellFormat head = new WritableCellFormat(wfont); // 设置居中 head.setAlignment(Alignment.CENTRE); // 设置居中 font.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); font.setAlignment(Alignment.CENTRE); font2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); font2.setAlignment(Alignment.CENTRE); // 创建一个EXCEL表格 wwk = Workbook.createWorkbook(response.getOutputStream()); WritableSheet ws = wwk.createSheet(fileName.substring(0, fileName.indexOf(".")), 0); // 合并单元格 ws.mergeCells(0, 0, (arr.length / 2)-1, 0); ws.addCell(new Label(0, 0, fileName.substring(0, fileName.indexOf(".")), head)); ws.mergeCells(0, 1,7,1); ws.addCell(new Label(0, 1, "入户时间:" + bmmc,font3)); ws.addCell(new Label((arr.length / 2)-1, 1, DateUtils.getCurrentDateToString(),font3)); // 表格宽度 CellView cv = new CellView(); CellView cv1 = new CellView(); cv.setSize((arr.length / 2)-1+5); //设置表头从第几列开始 int head_col = 0; for (int i = 1; i < arr.length; i++) { ws.setColumnView(head_col, cv.getSize()); ws.setColumnView(3, 24); String header = new String(arr[i].getBytes("iso-8859-1"), "gb2312"); int col = head_col; ws.addCell(new Label(head_col++, 2, header, font2)); //ws.addCell(new Label(col, 5, "", font)); ws.mergeCells( col , 2 , col , 2); //} i++; } // 设置起始行数 int con_row = 3; for (int i = 0; i < list.size(); i++) { //设置表头从第几列开始 int con_col = 0; // 从数据库里面读取的数据到实体里面,然后在遍历实体信息 CarFunc cl = (CarFunc)list.get(i);
Label label = new Label(con_col++, con_row, cl.cqdw, font); ws.addCell(label); Label label1 = new Label(con_col++, con_row, cl.clmc, font); ws.addCell(label1); Label label3 = new Label(con_col++, con_row, cl.fdjh, font); ws.addCell(label3); Label label_cjh = new Label(con_col++, con_row, cl.cjh, font); ws.addCell(label_cjh); Label labelpl = new Label(con_col++, con_row, cl.clpl, font); ws.addCell(labelpl); Label label2 = new Label(con_col++, con_row, cl.gzsj!=null&&!cl.gzsj.equals("")?cl.gzsj.substring(0,4):"", font); ws.addCell(label2); Label label4 = new Label(con_col++, con_row, cl.zjly, font); ws.addCell(label4); Label label6 = new Label(con_col++, con_row, cl.clpz, font); ws.addCell(label6); Label label13 = new Label(con_col++, con_row, cl.bxcbr, font); ws.addCell(label13); Label label14 = new Label(con_col++, con_row, cl.clzbh, font); ws.addCell(label14); con_row++; } wwk.write(); wwk.close(); response.getOutputStream().flush(); response.getOutputStream().close();
excel poi倒处去oracle可以直接倒
HttpServletResponse response = this.getResponse();
String templateExcel="模板.xls";
String templateFilePath = this.getRequest().getSession() //路径写自已的
.getServletContext().getRealPath("/")
+ "template" + "/" + templateExcel;
System.out.println("====="+templateFilePath);
POIFSFileSystem fs = null;
HSSFWorkbook wb = null;
try {
FileInputStream fi = new FileInputStream(templateFilePath);
fs = new POIFSFileSystem(fi);
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
e.printStackTrace();
return null;
}
HSSFSheet sheet = wb.getSheetAt(0);
HSSFCell cell = null;
double price=0;
SimpleDateFormat dateFormat=new SimpleDateFormat("yyyy-MM-dd");
for(int i=0;i<list.size();i++){ //数据库要导出的数据
TAssets tassets=list.get(i);
sheet.shiftRows(i+3, sheet.getLastRowNum(), 1); //从Excel第几行开始写 Excel下标是从0开始的
HSSFRow row = sheet.createRow(i+3);
HSSFRichTextString h0 = new HSSFRichTextString(tassets.getAssetTag());
cell = row.createCell((short) 0);
cell.setCellValue(h0); HSSFRichTextString h1 = new HSSFRichTextString(tassets.getAssetName());
cell = row.createCell((short) 1);
cell.setCellValue(h1); HSSFRichTextString h2 = new HSSFRichTextString(tassets.getSpec());
cell = row.createCell((short) 2);
cell.setCellValue(h2);
if(tassets.getInstoreDate()!=null){
HSSFRichTextString h3 = new HSSFRichTextString(dateFormat.format(tassets.getInstoreDate()));
cell = row.createCell((short) 3);
cell.setCellValue(h3);
}
HSSFRichTextString h4 = new HSSFRichTextString(tassets.getPrice());
cell = row.createCell((short) 4);
cell.setCellValue(h4);
HSSFRichTextString h5 = new HSSFRichTextString("1");
cell = row.createCell((short) 5);
cell.setCellValue(h5);
}
try {
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment; filename="
+ new String(templateExcel.getBytes("GBK"), "ISO-8859-1"));
response.setCharacterEncoding("GBK");
wb.write(response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
}
String targetfile = "D:/test.xls";
OutputStream os = null;
WritableWorkbook workbook = null;
//创建可写入的Excel工作薄
try{
os = new FileOutputStream(targetfile);
}catch(Exception e){
throw new NullPointerException("file not exist!"+e.getMessage());
}
try{
workbook=Workbook.createWorkbook(os);
}catch(Exception e){
System.out.println("CreateWorkbook failure!"+e.getMessage());
}
//创建表
WritableSheet sheet = workbook.createSheet("sheet1",0);
try{
if(rowset != null && rowset.getRowNum()>0){
int coloumns = rowset.getRow(0).getFieldcount();
for(int i = 0;i<rowset.getRowNum();i++){
Row r = rowset.getRow(i);
for(int j=0;j<coloumns;j++){
jxl.write.Label label = new jxl.write.Label(j,i,r.getFieldvalue(r.getFieldname(j)));
sheet.addCell(label);
}
}
}else{
throw new NullPointerException("rowset is null!");
}
workbook.write();
}catch(Exception e){
throw new Exception("create excel failure!"+e.getMessage());
}finally{
if(workbook != null){
workbook.close();
}
if(os != null){
os.close();
}
}
return targetfile;
}
function exportExcel() {
var cond = <%=!cond.equals("")?"\""+cond+"\"":"''"%>;
var actionCode = "cqdw,单 位,clmc,车辆品牌,fdjh,发动机号,cjh,车架号,clpl,排 量,gzsj,购车时间,cqdw,资金来源,clpz,车牌号码,bxcbr,承办人,clzbh,自编号";
var url = "xzdjbbExportExcel.jsp?fieldName="+ actionCode+ "&rhsj=<%=cdrq%>&rhsjq=<%=cdrqq%>&rhsjz=<%=cdrqz%>&fileName=新增车辆报表.xls&cond="+cond;
window.location= url;
}
这个是调用导出按钮的js代码 var actionCode 是需要显示列的名字 url中式一些参数项目刚用过 我给你代码 不明白的地方问哈 给你关键代码吧导出的jsp页面代码<%@ page language="java"
import="com.gt.dataObj.function.jjd.*,com.gt.util.common.*,com.gt.dataObj.function.jjd.clgl.*,java.util.*,java.io.*,
jxl.CellView,jxl.Workbook,jxl.format.Alignment,jxl.format.Border,jxl.format.BorderLineStyle,jxl.format.Colour,
jxl.write.Label,jxl.write.WritableCellFormat,jxl.write.WritableFont,jxl.write.WritableSheet,jxl.write.WritableWorkbook,
jxl.write.WriteException,jxl.write.biff.RowsExceededException"
pageEncoding="gb2312"%>
<%
//设备响应格式
response.reset();
response.setContentType("application/vnd.ms-excel");
String cond= request.getParameter("cond");
String fieldName= request.getParameter("fieldName");
String fileName= request.getParameter("fileName");
String bmmc= request.getParameter("rhsj");
String rhsjq= request.getParameter("rhsjq");
String rhsjz= request.getParameter("rhsjz");
if(bmmc == null || bmmc.equals("")){
if(rhsjq!=null&&!rhsjq.equals("")&&rhsjz!=null&&!rhsjz.equals("")){
bmmc=" 起始日期:"+UTIL.convertStr(rhsjq)+"结束日期:"+UTIL.convertStr(rhsjz);
}else{
bmmc = ":全部";
}
}else{
bmmc=" 年度"+UTIL.convertStr(bmmc);
}
List list = new ArrayList();
// 从数据库取数据
CarFunc type = new CarFunc();
CarFuncs clxx = new CarFuncs();
list = clxx.getRecordByCond(cond);
String arr[] = fieldName.split(",");// 创建生成的Excel生成路径
// OutputStream os;
WritableWorkbook wwk = null;
//url提交字符转码
fileName = new String(fileName.getBytes("iso-8859-1"),
"gb2312");// 表头起始行
try {
response.setHeader("Content-disposition",
"attachment; filename="
+ new String(fileName.getBytes("gb2312"), "ISO8859_1"));
WritableFont wfont = new WritableFont(
WritableFont.createFont("黑体"), 20);
WritableFont font5 = new WritableFont(
WritableFont.createFont("黑体"), 10);
WritableFont font6 = new WritableFont(
WritableFont.createFont("黑体"), 10);
wfont.setColour(Colour.BLACK);
wfont.setBoldStyle(WritableFont.BOLD);
font5.setColour(Colour.BLACK);
font5.setBoldStyle(WritableFont.BOLD);
font6.setColour(Colour.BLACK);
font6.setBoldStyle(WritableFont.BOLD);
WritableCellFormat font = new WritableCellFormat();
WritableCellFormat font2 = new WritableCellFormat(font5);
WritableCellFormat font3 = new WritableCellFormat(font6);
WritableCellFormat font1 = new WritableCellFormat();
WritableCellFormat head = new WritableCellFormat(wfont);
// 设置居中
head.setAlignment(Alignment.CENTRE);
// 设置居中
font.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
font.setAlignment(Alignment.CENTRE);
font2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
font2.setAlignment(Alignment.CENTRE);
// 创建一个EXCEL表格
wwk = Workbook.createWorkbook(response.getOutputStream()); WritableSheet ws = wwk.createSheet(fileName.substring(0, fileName.indexOf(".")), 0);
// 合并单元格
ws.mergeCells(0, 0, (arr.length / 2)-1, 0);
ws.addCell(new Label(0, 0, fileName.substring(0, fileName.indexOf(".")), head));
ws.mergeCells(0, 1,7,1);
ws.addCell(new Label(0, 1, "入户时间:" + bmmc,font3));
ws.addCell(new Label((arr.length / 2)-1, 1, DateUtils.getCurrentDateToString(),font3));
// 表格宽度
CellView cv = new CellView();
CellView cv1 = new CellView();
cv.setSize((arr.length / 2)-1+5);
//设置表头从第几列开始
int head_col = 0;
for (int i = 1; i < arr.length; i++) {
ws.setColumnView(head_col, cv.getSize());
ws.setColumnView(3, 24);
String header = new String(arr[i].getBytes("iso-8859-1"),
"gb2312");
int col = head_col;
ws.addCell(new Label(head_col++, 2, header, font2));
//ws.addCell(new Label(col, 5, "", font));
ws.mergeCells( col , 2 , col , 2);
//}
i++;
}
// 设置起始行数
int con_row = 3;
for (int i = 0; i < list.size(); i++) {
//设置表头从第几列开始
int con_col = 0;
// 从数据库里面读取的数据到实体里面,然后在遍历实体信息
CarFunc cl = (CarFunc)list.get(i);
Label label = new Label(con_col++, con_row, cl.cqdw, font);
ws.addCell(label);
Label label1 = new Label(con_col++, con_row, cl.clmc, font);
ws.addCell(label1);
Label label3 = new Label(con_col++, con_row, cl.fdjh, font);
ws.addCell(label3);
Label label_cjh = new Label(con_col++, con_row, cl.cjh, font);
ws.addCell(label_cjh);
Label labelpl = new Label(con_col++, con_row, cl.clpl, font);
ws.addCell(labelpl);
Label label2 = new Label(con_col++, con_row, cl.gzsj!=null&&!cl.gzsj.equals("")?cl.gzsj.substring(0,4):"", font);
ws.addCell(label2);
Label label4 = new Label(con_col++, con_row, cl.zjly, font);
ws.addCell(label4);
Label label6 = new Label(con_col++, con_row, cl.clpz, font);
ws.addCell(label6);
Label label13 = new Label(con_col++, con_row, cl.bxcbr, font);
ws.addCell(label13);
Label label14 = new Label(con_col++, con_row, cl.clzbh, font);
ws.addCell(label14);
con_row++;
} wwk.write();
wwk.close(); response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
} catch (RowsExceededException e) {
} catch (WriteException e) {
}
%>