jsp 制作网页xls下载的问题
怎么实现 从网页导出xsl文件 就是下载从数据库读出的数据 然后生成xsl文件 在下载到客户端
[解决办法]
response.setHeader("Content-disposition", "attachment;filename="test.xls");
response.setContentType("application/msexcel");
OutputStream os = response.getOutputStream();
使用POI生成xls文件，生成时使用到Workbook wb = new HSSFWorkbook();
最后wb.write(os);即可。
附上我在项目中使用的代码：
- Java code
public static int createSheet(HttpServletResponse response,Form1324 form)throws Exception { response.setHeader("Content-disposition", "attachment; filename="+form.getGate_id()+"_"+form.getDate_begin()+"_"+form.getDate_end()+".xls"); response.setContentType("application/msexcel"); OutputStream os = response.getOutputStream(); HSSFWorkbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("T中行"); Row row = null; Cell cell = null; String[] refundLogs = form.getRefund_logs(); String[] str = null; int len = refundLogs.length; int i = 0,j=0,k=0; int pageNo = (len -1)/10 + 1; int rndNum = 0; String cardNo = ""; String txnDt = ""; String txnAmt = ""; String refAmt = ""; String authCd = ""; String bk_date = ""; String sysSeqId = ""; String sysDt = ""; String orgData = ""; int pageFrom = 0; double totalTxnAmt = 0.00; double totalRefAmt = 0.00; double subAmt = 0.00; DecimalFormat format = new DecimalFormat("0.00"); CellStyle cs1 = wb.createCellStyle(); Font font1 = wb.createFont(); font1.setFontName("宋体"); font1.setFontHeightInPoints((short)20); font1.setBoldweight(Font.BOLDWEIGHT_BOLD); cs1.setFont(font1); cs1.setAlignment(CellStyle.ALIGN_CENTER); CellStyle cs2 = wb.createCellStyle(); Font font2 = wb.createFont(); font2.setFontName("宋体"); font2.setFontHeightInPoints((short)10); cs2.setFont(font2); cs2.setAlignment(CellStyle.ALIGN_CENTER); CellStyle cs3 = wb.createCellStyle(); cs3.setFont(font2); cs3.setBorderTop(HSSFCellStyle.BORDER_THIN); cs3.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs3.setBorderLeft(HSSFCellStyle.BORDER_THIN); cs3.setBorderRight(HSSFCellStyle.BORDER_THIN); cs3.setAlignment(CellStyle.ALIGN_CENTER); cs3.setVerticalAlignment(CellStyle.VERTICAL_CENTER); CellStyle cs4 = wb.createCellStyle(); Font font4 = wb.createFont(); font4.setFontHeightInPoints((short)14); font4.setFontName("宋体"); font4.setBoldweight(Font.BOLDWEIGHT_BOLD); cs4.setFont(font4); cs4.setVerticalAlignment(CellStyle.VERTICAL_CENTER); for(j=0;j<pageNo;j++){ totalTxnAmt = 0; totalRefAmt = 0; pageFrom = j*37; if(j==(pageNo-1)){ if(len % 10 == 0 ){ rndNum = 10; }else{ rndNum = len % 10; } }else{ rndNum = 10; } row = sheet.createRow(pageFrom); row.setHeightInPoints((short)40); cell = row.createCell(0); cell.setCellStyle(cs1); cell.setCellValue("中国银行人民币卡退款申请表"); sheet.addMergedRegion(new CellRangeAddress(pageFrom, pageFrom, 0, 6)); row = sheet.createRow(pageFrom+1); row.setHeightInPoints((short)20); cell = row.createCell(0); cell.setCellStyle(cs2); cell.setCellValue("此申请为第_____次发送 上次发送日期：____________."); sheet.addMergedRegion(new CellRangeAddress(pageFrom+1, pageFrom+1, 0, 6)); row = sheet.createRow(pageFrom+2); cell = row.createCell(0); cell.setCellValue("TO：中国银行银行卡部"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+2, pageFrom+2, 0, 2)); cell = row.createCell(3); cell.setCellValue("FROM： 汇付天下有限公司"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+2, pageFrom+2, 3, 6)); row = sheet.createRow(pageFrom+3); cell = row.createCell(0); cell.setCellValue("电话：63219234"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+3, pageFrom+3, 0, 2)); cell = row.createCell(3); cell.setCellValue("*商户编号：104310148160006"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+3, pageFrom+3, 3, 6)); row = sheet.createRow(pageFrom+4); cell = row.createCell(0); cell.setCellValue("传真：63291037/63291049"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+4, pageFrom+4, 0, 2)); cell = row.createCell(3); cell.setCellValue("电话：021-61613999*6209 传真：021-61613099"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+4, pageFrom+4, 3, 6)); row = sheet.createRow(pageFrom+5); row.setHeightInPoints((short)30); cell = row.createCell(0); cell = row.createCell(0); cell.setCellStyle(cs3); cell.setCellValue(""); cell = row.createCell(1); cell.setCellStyle(cs3); cell.setCellValue("*卡号"); cell = row.createCell(2); cell.setCellStyle(cs3); cell.setCellValue("*原交易日期"); cell = row.createCell(3); cell.setCellStyle(cs3); cell.setCellValue("*原交易金额"); cell = row.createCell(4); cell.setCellStyle(cs3); cell.setCellValue("*原交易授权号"); cell = row.createCell(5); cell.setCellStyle(cs3); cell.setCellValue("*退款金额"); cell = row.createCell(6); cell.setCellStyle(cs3); cell.setCellValue("◇原因"); for(i=0;i<rndNum;i++){ str = refundLogs[j*10+i].split("\\|"); cardNo = str[1]; bk_date = str[12]; sysSeqId = str[10]; sysDt = str[11]; if(!"".equals(sysDt)) orgData = sysDt; if(!"".equals(sysSeqId)) orgData = orgData + sysSeqId; if(!"".equals(bk_date)) orgData = orgData + bk_date; txnDt = orgData.substring(0,8); txnAmt = str[2]; refAmt = str[3]; authCd = str[9]; row = sheet.createRow(pageFrom+6+i); row.setHeightInPoints((short)30); cell = row.createCell(0); cell.setCellStyle(cs3); cell.setCellValue(i+1); cell = row.createCell(1); cell.setCellStyle(cs3); cell.setCellValue(cardNo); cell = row.createCell(2); cell.setCellStyle(cs3); cell.setCellValue(txnDt); cell = row.createCell(3); cell.setCellStyle(cs3); cell.setCellValue(txnAmt); cell = row.createCell(4); cell.setCellStyle(cs3); cell.setCellValue(authCd); cell = row.createCell(5); cell.setCellStyle(cs3); cell.setCellValue(refAmt); cell = row.createCell(6); cell.setCellStyle(cs3); cell.setCellValue("持卡人退货"); totalTxnAmt += Double.parseDouble(txnAmt); totalRefAmt += Double.parseDouble(refAmt); } //最后一页补充 if(j==((pageNo-1))){ rndNum = 10-rndNum; for(k=0;k<rndNum;k++){ row = sheet.createRow(pageFrom+6+i+k); row.setHeightInPoints((short)30); row.createCell(0).setCellStyle(cs3); row.createCell(1).setCellStyle(cs3); row.createCell(2).setCellStyle(cs3); row.createCell(3).setCellStyle(cs3); row.createCell(4).setCellStyle(cs3); row.createCell(5).setCellStyle(cs3); row.createCell(6).setCellStyle(cs3); } } row = sheet.createRow(pageFrom+16); row.setHeightInPoints((short)30); cell = row.createCell(0); cell.setCellStyle(cs3); cell.setCellValue("合计"); cell = row.createCell(1); cell.setCellStyle(cs3); cell.setCellValue("退款总金额:"); row.createCell(2).setCellStyle(cs3); row.createCell(3).setCellStyle(cs3); row.createCell(4).setCellStyle(cs3); sheet.addMergedRegion(new CellRangeAddress(pageFrom+16, pageFrom+16, 1, 4)); cell = row.createCell(5); cell.setCellStyle(cs3); cell.setCellValue(format.format(totalRefAmt)); row.createCell(6).setCellStyle(cs3); sheet.addMergedRegion(new CellRangeAddress(pageFrom+16, pageFrom+16, 5, 6)); row = sheet.createRow(pageFrom+17); cell = row.createCell(0); cs2.setWrapText(true); cell.setCellStyle(cs2); cell.setCellValue("◇原因： A.持卡人退货 B.刷卡后付现金 C.银行重复清算 D.收银重复刷卡 E.POS机误操作 F.有查询编号 G.其他"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+17, pageFrom+18, 0, 6)); row = sheet.createRow(pageFrom+19); cell = row.createCell(0); cell.setCellStyle(cs4); cell.setCellValue("其中加*号的项目为必填项"); row = sheet.createRow(pageFrom+20); cell = row.createCell(0); cell.setCellStyle(cs4); cell.setCellValue("公司财务章或公章:"); sheet.addMergedRegion(new CellRangeAddress(pageFrom+20, pageFrom+26, 0, 6)); row = sheet.createRow(pageFrom+27); cell = row.createCell(0); cell.setCellValue("备注：1.上述退款金额将从信用卡交易中抵扣，轧差后净额结算。"); row = sheet.createRow(pageFrom+28); cell = row.createCell(0); cell.setCellValue(" 2.每份退款申请表的退款笔数请勿超过10笔，如有超过请换页，退款总金额请单页统计。"); subAmt += totalRefAmt; } row = sheet.createRow(29); cell = row.createCell(0); cell.setCellStyle(cs4); cell.setCellValue("账号：455959692203"); sheet.addMergedRegion(new CellRangeAddress(29, 29, 0, 6)); row = sheet.createRow(30); cell = row.createCell(0); cell.setCellStyle(cs4); cell.setCellValue("一共"+pageNo+"张退款表，共"+len+"笔"); sheet.addMergedRegion(new CellRangeAddress(30, 30, 0, 2)); row = sheet.createRow(31); cell = row.createCell(0); cell.setCellStyle(cs4); cell.setCellValue("退款总金额："+format.format(subAmt)+"元；扣除手续费后金额："+format.format(subAmt*0.008)+"元"); sheet.addMergedRegion(new CellRangeAddress(31, 31, 0, 6)); sheet.setColumnWidth(0, 4*256); sheet.setColumnWidth(1, 20*256); sheet.setColumnWidth(2, 13*256); sheet.setColumnWidth(3, 13*256); sheet.setColumnWidth(4, 13*256); sheet.setColumnWidth(5, 12*256); sheet.setColumnWidth(6, 12*256); wb.write(os); return 0; }
[解决办法]
给你一个从数据库读数据，然后保存到Excel 的源码
- Java code
package com.zds.emis.employee.servlet;import java.io.IOException;import java.io.OutputStream;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import jxl.Workbook;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;import com.zds.emis.employee.service.IEmployeeManageBs;import com.zds.emis.employee.service.imp.EmployeeManageBs;import com.zds.emis.employee.vo.EmployeeVo;public class ExportEmployeeExcel extends HttpServlet { /** * Constructor of the object. */ public ExportEmployeeExcel() { super(); } /** * Destruction of the servlet. <br> */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { request.setCharacterEncoding("gbk"); EmployeeVo employeeVo=new EmployeeVo(); //获取员工编号 employeeVo.setEmpCode(request.getParameter("empCode")); System.out.println("你要导出的员工编号为:"+request.getParameter("empCode")); //获取员工姓名 employeeVo.setEmpName(request.getParameter("empName")); //初始化Service IEmployeeManageBs service=new EmployeeManageBs(); //掉用Service List employeeList=service.queryByCondition(employeeVo); // 用日期构建输出文件名称 SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd"); String date = sd.format(new Date()); String excelName = "员工信息_" + date + ".xls"; // 解决导出员工数据时汉字乱码问题 excelName = new String(excelName.getBytes(), "iso8859-1"); // 选择地址点击保存或者直接点击查看 response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment; filename=\""+ excelName + "\""); OutputStream os = response.getOutputStream(); //利用OutputStream创建WritableWorkbook对象 WritableWorkbook wwb = Workbook.createWorkbook(os); //设置字体 、颜色 、大小 WritableFont font1=new WritableFont(WritableFont.TIMES,11,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.GREEN); WritableFont font2=new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD); WritableFont font3=new WritableFont(WritableFont.ARIAL,9,WritableFont.BOLD); WritableFont font4=new WritableFont(WritableFont.TIMES,11,WritableFont.BOLD,false, UnderlineStyle.NO_UNDERLINE,Colour.RED); WritableCellFormat format1=new WritableCellFormat(font1); WritableCellFormat format2=new WritableCellFormat(font2); WritableCellFormat format3=new WritableCellFormat(font3); WritableCellFormat format4=new WritableCellFormat(font4); try { //把水平对齐方式指定为居中 format1.setAlignment(jxl.format.Alignment.CENTRE); format2.setAlignment(jxl.format.Alignment.CENTRE); format3.setAlignment(jxl.format.Alignment.CENTRE); format4.setAlignment(jxl.format.Alignment.CENTRE); } catch (WriteException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } // 生成名为"员工信息"的工作表，参数0表示这是第一页 WritableSheet sheet=wwb.createSheet("员工信息",0); //设置单元表格的宽度为：13 sheet.setColumnView(0,13); sheet.setColumnView(1,13); sheet.setColumnView(2,13); sheet.setColumnView(3,13); sheet.setColumnView(4,15); sheet.setColumnView(5,15); sheet.setColumnView(6,15); sheet.setColumnView(7,13); sheet.setColumnView(8,13); sheet.setColumnView(9,13); try { //合并单元表格 sheet.mergeCells(0,0,9,0); //向表格里插入数据 并设置 format属性 //在Label对象的构造之中指名单元格位置是第一列第一行(0,0) Label label1 = new Label(0, 0, "员工信息表",format2); Label label2 = new Label(0, 1, "工号",format4); Label label3 = new Label(1, 1, "姓名",format1); Label label4 = new Label(2, 1, "性别",format1); Label label14 = new Label(3, 1, "年龄",format1); Label label15 = new Label(4, 1, "电话",format1); Label label16 = new Label(5, 1, "手机",format1); Label label17 = new Label(6, 1, "邮件",format1); Label label18 = new Label(7, 1, "学历",format1); Label label19 = new Label(8, 1, "地址",format1); Label label20 = new Label(9, 1, "状态",format1); // 将定义好的单元格添加到工作表中 sheet.addCell(label1); sheet.addCell(label2); sheet.addCell(label3); sheet.addCell(label4); sheet.addCell(label14); sheet.addCell(label15); sheet.addCell(label16); sheet.addCell(label17); sheet.addCell(label18); sheet.addCell(label19); sheet.addCell(label20); EmployeeVo[] empVo=new EmployeeVo[employeeList.size()]; for (int i = 0; i <employeeList.size(); i++){ empVo[i] =(EmployeeVo) employeeList.get(i); Label label5 = new Label(0,i+2,""+empVo[i].getEmpCode(),format4); sheet.addCell(label5); Label label6=new Label(1,i+2,""+empVo[i].getEmpName(),format3); sheet.addCell(label6); Label label7=new Label(2,i+2,"1".equals(empVo[i].getEmpSex())?"男":"女",format3); sheet.addCell(label7); jxl.write.Number label8=new jxl.write.Number(3,i+2,empVo[i].getEmpAge(),format3); sheet.addCell(label8); Label label9=new Label(4,i+2,""+empVo[i].getEmpPhone(),format3); sheet.addCell(label9); Label label10=new Label(5,i+2,""+empVo[i].getEmpMobile(),format3); sheet.addCell(label10); Label label11 = new Label(6,i+2,""+empVo[i].getEmpEmail(),format3); sheet.addCell(label11); Label label12=new Label(7,i+2,""+empVo[i].getEmpDegree(),format3); sheet.addCell(label12); Label label13=new Label(8,i+2,""+empVo[i].getEmpAddress(),format3); sheet.addCell(label13); Label label21=new Label(9,i+2,"1".equals(empVo[i].getEmpState())?"在职":"离职",format3); sheet.addCell(label21); } System.out.println("创建成功"); // 写入数据并关闭文件 wwb.write(); wwb.close(); } catch (RowsExceededException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (WriteException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { doGet(request,response); } /** * The doPut method of the servlet. <br> * * This method is called when a HTTP put request is received. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPut(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { // Put your code here } /** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here }}