package com.java.demo; import java.io.File; import java.io.IOException; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.format.Colour; import jxl.format.UnderlineStyle; import jxl.format.VerticalAlignment; 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; /** * 导出Excel实例: * @author Administrator * */ public class ExcelDemo { /** * @param args * @throws IOException * @throws WriteException * @throws RowsExceededException */ public static void main(String[] args) throws IOException, RowsExceededException, WriteException { //创建Excel工作簿; WritableWorkbook workbook = Workbook.createWorkbook(new File("C:/ExcelDemo.xls")); //创建Excel电子薄; WritableSheet sheet = workbook.createSheet("第一个Sheet", 0); //分别给2,3,4列设置不同的宽度; sheet.setColumnView(1, 40); sheet.setColumnView(1, 30); sheet.setColumnView(2, 50); sheet.setColumnView(3, 20); //给sheet电子版中所有的列设置默认的列的宽度; sheet.getSettings().setDefaultColumnWidth(30); //设置字体; WritableFont font1 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.RED); WritableCellFormat cellFormat1 = new WritableCellFormat(font1); //设置背景颜色; cellFormat1.setBackground(Colour.BLUE_GREY); //设置边框; cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT); //设置自动换行; cellFormat1.setWrap(true); //设置文字居中对齐方式; cellFormat1.setAlignment(Alignment.CENTRE); //设置垂直居中; cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE); //创建单元格 Label label1 = new Label(0, 0, "第一行第一个单元格(测试是否自动换行!)",cellFormat1); Label label2 = new Label(1, 0, "第一行第二个单元格",cellFormat1); Label label3 = new Label(2, 0, "第一行第三个单元格",cellFormat1); Label label4 = new Label(3, 0, "第一行第四个单元格",cellFormat1); //添加到行中; sheet.addCell(label1); sheet.addCell(label2); sheet.addCell(label3); sheet.addCell(label4); //给第二行设置背景、字体颜色、对齐方式等等; WritableFont font2 = new WritableFont(WritableFont.ARIAL,14,WritableFont.NO_BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLUE2); WritableCellFormat cellFormat2 = new WritableCellFormat(font2); cellFormat2.setAlignment(Alignment.CENTRE); cellFormat2.setBackground(Colour.PINK); cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN); cellFormat2.setWrap(true); //创建单元格; Label label11= new Label(0, 1, "第二行第一个单元格(测试是否自动换行!)",cellFormat2); Label label22 = new Label(1, 1, "第二行第二个单元格",cellFormat2); Label label33 = new Label(2, 1, "第二行第三个单元格",cellFormat2); Label label44 = new Label(3, 1, "第二行第四个单元格",cellFormat2);
sheet.mergeCells(index+7,10,index+8,10); //添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行 sheet.addCell(label11); sheet.addCell(label22); sheet.addCell(label33); sheet.addCell(label44); //写入Excel表格中; workbook.write(); //关闭流; workbook.close(); } }
1、controller
public String exportStaffAttendce(AttendMonthly domain, Model model, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirect) {try { String fileName = "员工考勤.xls"; response.setContentType("application/x-msdownload"); if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) { fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); } //IE else{ fileName = URLEncoder.encode(fileName, "UTF-8"); } response.setHeader("Content-Disposition", "attachment;filename=\""+fileName+"\""); try { attendMonthlyService.dailyPerQueryExport(domain, response.getOutputStream()); } catch (Exception e) { e.printStackTrace(); } } catch (IOException e) { e.printStackTrace(); } return null; }
2、service
public void dailyPerQueryExport(AttendMonthly domain, OutputStream os) throws RowsExceededException, WriteException, IOException, Exception {//创建工作薄 WritableWorkbook workbook = Workbook.createWorkbook(os); //创建新的一页 WritableSheet sheet = workbook.createSheet("人员日志", 0); //构造表头 sheet.mergeCells(0, 0, size*11-1, 2);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行 //生成一个单元格样式控制对象 WritableCellFormat titleFormate = new WritableCellFormat(); titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中 titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中 titleFormate.setWrap(true); //设置字体; WritableFont font1 = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD,false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK); //生成一个单元格样式控制对象 WritableCellFormat titleFormate2 = new WritableCellFormat(font1); titleFormate2.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中 titleFormate2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中 titleFormate2.setWrap(true); //设置边框; titleFormate.setBorder(Border.ALL, BorderLineStyle.THIN); Label title = new Label(0,0,"员工考勤表"+"\r\n"+"考勤日期:"+begin + "~" + end+" "+"制表时间："+create,titleFormate2); sheet.addCell(title); //给sheet电子版中所有的列设置默认的列的宽度; sheet.getSettings().setDefaultColumnWidth(4); //创建要显示的具体内容 //表头 for(int i=0;i<size;i++){ int index = i*11; //分别给1,2列设置不同的宽度; sheet.setColumnView(index, 3); // 日期 sheet.setColumnView(index+1, 3); // 星期 sheet.setColumnView(index+6, 8); AttendMonthly am = list.get(i); // 第一行 Label deptName = new Label(index,3,"部门",titleFormate); sheet.addCell(deptName); sheet.mergeCells(index,3,index+1,3); Label deptNaTd = new Label(index+2,3,am.getOrgName(),titleFormate); sheet.addCell(deptNaTd); sheet.mergeCells(index+2,3,index+6,3); Label name = new Label(index+7,3,"姓名",titleFormate); sheet.addCell(name); Label nameTd = new Label(index+8,3,am.getStaffName(),titleFormate); sheet.addCell(nameTd); sheet.mergeCells(index+8,3,index+10,3); // 第二行 Label date = new Label(index,4,"日期",titleFormate); sheet.addCell(date); sheet.mergeCells(index,4,index+1,4); Label dateTd = new Label(index+2,4,begin + "~" + end,titleFormate); sheet.addCell(dateTd); sheet.mergeCells(index+2,4,index+6,4); Label num = new Label(index+7,4,"工号",titleFormate); sheet.addCell(num); Label numTd = new Label(index+8,4,am.getStaffNo(),titleFormate); sheet.addCell(numTd); sheet.mergeCells(index+8,4,index+10,4); } for(int i=0;i<dates.length;i++){ String tm = dates[i]; String subTm = tm.substring(8, 10); int row = i + 11; for(int j=0;j<list.size();j++){ AttendMonthly am = list.get(j); int cols = j*11; for(int n=0;n<attendanceList.size();n++){ AttendRecord aR = attendanceList.get(n); if(am.getStaffId() == aR.getStaffId()){ if(!StringUtil.isEmptyString(aR.getAttendDate()) && aR.getAttendDate().equals(tm)){ Label dat = new Label(cols,row,subTm,titleFormate);// 日期 sheet.addCell(dat); Label weekNum = new Label(cols+1,row,aR.getWeek(),titleFormate);// 星期 sheet.addCell(weekNum); Label amIn = new Label(cols+2,row,aR.getInRegisterTime(),titleFormate);// 上午上班 sheet.addCell(amIn); sheet.mergeCells(cols+2,row,cols+3,row); Label amOut = new Label(cols+4,row,aR.getAmOutRegisterTime(),titleFormate);// 上午下班 sheet.addCell(amOut); sheet.mergeCells(cols+4,row,cols+5,row); Label pmIn = new Label(cols+6,row,aR.getPmInRegisterTime(),titleFormate);// 下午上班 sheet.addCell(pmIn); Label pmOut = new Label(cols+7,row,aR.getOutRegisterTime(),titleFormate);// 下午下班 sheet.addCell(pmOut); sheet.mergeCells(cols+7,row,cols+8,row); Label signIn = new Label(cols+9,row,"",titleFormate);// 签到 sheet.addCell(signIn); Label signOut = new Label(cols+10,row,"",titleFormate);// 签退 sheet.addCell(signOut); } } } } } //把创建的内容写入到输出流中，并关闭输出流 workbook.write(); workbook.close(); os.flush(); os.close(); }
2374
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
