导出Excel技术模板

public void excelUser( ){log.info("项目综合管理系统excelUser Action ");// CreateProjApplyBean Bean=new CreateProjApplyBean();List cflist = createProjApplyService.selectUser(createProjApplyBean); User user=projEqptService.select_ncostname(createProjApplyBean.getUserId());WritableWorkbook workbook = null;String[] columnNames = { "序号","项目编号", "项目名称","合同金额","签订时间","个人比例","个人金额"};OutputStream outputStream = null;String sdr="业绩信息统计表";try {HttpServletResponse response = ServletActionContext.getResponse();response.reset();// 清空输出流response.setHeader("Content-disposition", "attachment; filename="+ new String(sdr.getBytes("GBK"), "ISO8859-1") + ".xls");// 设定输出文件头response.setContentType("application/msexcel");// 定义输出类型outputStream = response.getOutputStream();// 建立excel文件workbook = Workbook.createWorkbook(outputStream);// 创建工作簿// 创建第一行第一列的样式WritableSheet writableSheet = workbook.createSheet("申请书", 0);// 创建第一页writableSheet.setRowView(0, 300);// 设置第一行的高度// writableSheet.setRowView(0, 0);//将第一列第一行隐藏// writableSheet.setRowGroup(0, 0, false);//让第一行可以伸展// writableCellFormat.setBackground(jxl.format.Colour.BLUE);// 设置列宽writableSheet.setColumnView(0, 10);writableSheet.setColumnView(1, 25);writableSheet.setColumnView(2, 50);writableSheet.setColumnView(3, 25);writableSheet.setColumnView(4, 25);writableSheet.setColumnView(5, 25);writableSheet.setColumnView(6, 25);// 设置单元格格式WritableFont writableFont = new WritableFont(WritableFont.createFont("宋体"),9, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormat writableCellFormat = new WritableCellFormat(writableFont);WritableFont writableFont2 = new WritableFont(WritableFont.createFont("宋体"),9, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormat writableCellFormat2 = new WritableCellFormat(writableFont2);WritableFont writableFont1 = new WritableFont(WritableFont.ARIAL,9, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormat writableCellFormat1 = new WritableCellFormat(writableFont1);WritableFont writableFont3 = new WritableFont(WritableFont.createFont("宋体"),9, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK);WritableCellFormat writableCellFormat3 = new WritableCellFormat(writableFont3);// 添加边框：writableCellFormat.setBorder(Border.ALL, BorderLineStyle.THIN);writableCellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);writableCellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);writableCellFormat3.setBorder(Border.ALL, BorderLineStyle.THIN);// 添加背景色：//writableCellFormat.setBackground(Colour.GRAY_25);writableCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 垂居中writableCellFormat.setAlignment(jxl.format.Alignment.CENTRE);// 水平居中writableCellFormat1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 垂居中 writableCellFormat1.setAlignment(jxl.format.Alignment.CENTRE);// 水平居中 writableCellFormat3.setAlignment(jxl.format.Alignment.RIGHT);// 自动换行writableCellFormat.setWrap(true);writableCellFormat2.setWrap(true);// 设置列头名String sql="";for (CreateProjApplyBean part: cflist){sql=part.getUserNpart();}writableSheet.addCell(new Label(0, 0,"姓名", writableCellFormat1));writableSheet.addCell(new Label(1, 0,user.getCname(), writableCellFormat1));writableSheet.addCell(new Label(2, 0,"申请时间", writableCellFormat1));SimpleDateFormat simp = new SimpleDateFormat("yyyy年MM月dd日 ");String dateStr = simp.format(new Date());writableSheet.addCell(new Label(3, 0,dateStr, writableCellFormat1));writableSheet.addCell(new Label(5, 0,"部门", writableCellFormat1));writableSheet.addCell(new Label(6, 0, sql, writableCellFormat1));writableSheet.addCell(new Label(0, 1,"", writableCellFormat1));writableSheet.mergeCells(3, 0,4, 0); //合并列writableSheet.mergeCells(0, 1,6,2); //合并列//循环显示标题for (int i = 0; i < columnNames.length; i++) {writableSheet.addCell(new Label(i, 3, columnNames[i],writableCellFormat1));} //Label参数值 第一个是列位，第二个是行位，第三个是 其中的值int i = 4;String str=null;String n;for (CreateProjApplyBean bean : cflist) { str="";n=String.valueOf(i-2);writableSheet.addCell(new Label(0, i,n, writableCellFormat));if (bean.getProjId() != null) {writableSheet.addCell(new Label(1, i,bean.getProjId(), writableCellFormat));} else {writableSheet.addCell(new Label(1, i, "",writableCellFormat));} if (bean.getProjName() != null) {writableSheet.addCell(new Label(2, i,bean.getProjName(), writableCellFormat));} else {writableSheet.addCell(new Label(2, i, "",writableCellFormat));}if (bean.getPackMoneys()!= null) {writableSheet.addCell(new Label(3, i,bean.getPackMoneys(), writableCellFormat));} else {writableSheet.addCell(new Label(3, i, "",writableCellFormat));}if (bean.getSignTimeStr() != null) {writableSheet.addCell(new Label(4, i,bean.getSignTimeStr(), writableCellFormat));} else {writableSheet.addCell(new Label(4, i, "",writableCellFormat));} writableSheet.addCell(new Label(5, i,"", writableCellFormat));writableSheet.addCell(new Label(6, i, str,writableCellFormat));i++;}writableSheet.addCell(new Label(0, i,"合计", writableCellFormat));writableSheet.addCell(new Label(1, i,"", writableCellFormat));writableSheet.addCell(new Label(6, i,"", writableCellFormat));writableSheet.mergeCells(1,i,5, i); //合并列writableSheet.addCell(new Label(0, i+1,"市场部意见：", writableCellFormat2));writableSheet.mergeCells(0,i+1,6, i+2); //合并列writableSheet.addCell(new Label(0, i+3,"商务部意见：", writableCellFormat2));writableSheet.mergeCells(0,i+3,6, i+4); //合并列writableSheet.addCell(new Label(0, i+5,"财务部意见：", writableCellFormat2));writableSheet.mergeCells(0,i+5,6, i+6); //合并列writableSheet.addCell(new Label(0, i+7,"总经理意见：", writableCellFormat2));writableSheet.mergeCells(0,i+7,6, i+8); //合并列writableSheet.addCell(new Label(0, i+9,"董事长意见：", writableCellFormat2));writableSheet.mergeCells(0,i+9,6, i+10); //合并列writableSheet.addCell(new Label(0, i+11,"内蒙古宝亮信息技术有限公司", writableCellFormat3));writableSheet.mergeCells(0,i+11,6, i+12); //合并列// 写入数据并关闭文件workbook.write();} catch (Exception e) {e.printStackTrace();} finally {try {outputStream.flush();workbook.close();outputStream.close();} catch (Exception e) {e.printStackTrace();}}}