/** * excel * @param teacherComments * @throws IOException * @throws WriteException */private void writeExcel(List<ClassTeacherComment> teacherComments) throws IOException, WriteException {String title = ScUtil.stringEncode(commentVO.getTitle(), "iso-8859-1", "utf-8");OutputStream os = ScUtil.getOs(getResponse(), title);WritableWorkbook wwb = Workbook.createWorkbook(os);RGBColor.initialize(wwb);WritableSheet sheet = wwb.createSheet("sheet 1", 0);for(int i = 0; i < 3; i++) {sheet.setColumnView(i, 15);}// 设置列的宽度sheet.setColumnView(3, 100);sheet.setRowView(0, 350);
// 设置行的高度
sheet.setRowView(1, 350);sheet.mergeCells(0, 0, 3, 1);Label sheetTitle = new Label(0, 0, title, getHeaderFont(14));sheet.addCell(sheetTitle);WritableCellFormat headerFormmate = getHeaderFont(11);// 格式化背景色headerFormmate.setBackground(RGBColor.BLUE2);sheet.setRowView(2, 500);Label header1 = new Label(0, 2, "用户名", headerFormmate);Label header2 = new Label(1, 2, "学号", headerFormmate);Label header3 = new Label(2, 2, "姓名", headerFormmate);Label header4 = new Label(3, 2, "评语", headerFormmate);sheet.addCell(header1);// 向sheet中添加一个Labelsheet.addCell(header2);sheet.addCell(header3);sheet.addCell(header4);WritableCellFormat dataFormmate = getDataFont(10);dataFormmate.setWrap(true);ClassTeacherComment comment = null;User user = null;for (int i = 0; i < teacherComments.size(); i++) {comment = teacherComments.get(i);user = comment.getStudent();Label col0 = new Label(0, i + 3, user.getAccount(), dataFormmate);Label col1 = new Label(1, i + 3, user.getStudentNumber(), dataFormmate);Label col2 = new Label(2, i + 3, user.getName(), dataFormmate);Label col3 = new Label(3, i + 3, comment.getComment(), dataFormmate);sheet.setRowView(i + 3, 1000);sheet.addCell(col0);sheet.addCell(col1);sheet.addCell(col2);sheet.addCell(col3);}wwb.write();wwb.close();os.close();}/** * 获得excel标题或表头格式 * @param fontSize 字体大小 * @return * @throws WriteException */private WritableCellFormat getHeaderFont(int fontSize) throws WriteException {WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), fontSize, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat titleFormat = new WritableCellFormat(titleFont);titleFormat.setAlignment(Alignment.CENTRE); // 水平居中对齐titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 竖直方向居中对齐titleFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, Colour.GRAY_25);return titleFormat;}/** * 获得excel数据格式 * @param fontSize 字体大小 * @return * @throws WriteException */private WritableCellFormat getDataFont(int fontSize) throws WriteException {WritableFont bodyFont = new WritableFont(WritableFont.createFont("宋体"), fontSize, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE); WritableCellFormat bodyFormat = new WritableCellFormat(bodyFont);bodyFormat.setAlignment(Alignment.LEFT); // 水平居中对齐bodyFormat.setVerticalAlignment(VerticalAlignment.CENTRE); // 竖直方向居中对齐bodyFormat.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, Colour.GRAY_25);return bodyFormat;}
