Jxl交叉表合计公式的使用 使用公式的方法: public boolean atRecordStudentStatistic(File file, List<StatisticVo> values) { boolean flag = true; try { Workbook in = Workbook.getWorkbook(new File( "./excel/atRecordStudentStatistic.xls")); WritableWorkbook workbook = Workbook.createWorkbook(file, in); WritableSheet sheet = workbook.getSheet(0); Label label = null; Number number = null; Formula formula = null; // CellView cellView = new CellView(); // cellView.setAutosize(true); // sheet.setColumnView(2, cellView); // sheet.setColumnView(5, cellView); WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD); WritableCellFormat format = new WritableCellFormat(font); format.setAlignment(Alignment.CENTRE); format.setVerticalAlignment(VerticalAlignment.CENTRE); format.setBorder(Border.ALL, BorderLineStyle.THIN); // WritableCellFormat siteFormat = new WritableCellFormat(font); // siteFormat.setAlignment(Alignment.CENTRE); // siteFormat.setVerticalAlignment(VerticalAlignment.CENTRE); int size = values.size(); int startLine = 4; int j = 3; String siteId = null; String siteName = null; String formu = ""; int length = 0; int maxSiteLength = 0; int maxSpecialityLength = 0; for (int i = 0; i < size; i++) { StatisticVo statistic = values.get(i); if (null != siteId && !siteId.equalsIgnoreCase(statistic.getSiteId())) { length = this.getStringLength(siteName + " 汇总"); if (maxSiteLength < length) { maxSiteLength = length; sheet.setColumnView(2, maxSiteLength); } label = new Label(2, j, siteName + " 汇总", format); sheet.addCell(label); formu = "SUM(I" + startLine + ":I" + j + ")"; formula = new Formula(8, j, formu, format); sheet.addCell(formula); formu = "SUM(J" + startLine + ":J" + j + ")"; formula = new Formula(9, j, formu, format); sheet.addCell(formula); formu = "SUM(K" + startLine + ":K" + j + ")"; formula = new Formula(10, j, formu, format); sheet.addCell(formula); formu = "SUM(L" + startLine + ":L" + j + ")"; formula = new Formula(11, j, formu, format); sheet.addCell(formula); j++; startLine = j + 1; } siteId = statistic.getSiteId(); siteName = statistic.getSiteName(); label = new Label(0, j, String.valueOf(i + 1), format); sheet.addCell(label); label = new Label(1, j, statistic.getTypeName(), format); sheet.addCell(label); length = this.getStringLength(statistic.getSiteName()); if (maxSiteLength < length) { maxSiteLength = length; sheet.setColumnView(2, maxSiteLength); } label = new Label(2, j, statistic.getSiteName(), format); sheet.addCell(label); label = new Label(3, j, statistic.getGrade(), format); sheet.addCell(label); label = new Label(4, j, statistic.getSpecialityId(), format); sheet.addCell(label); length = this.getStringLength(statistic.getSpecialityName()); if (maxSpecialityLength < length) { maxSpecialityLength = length; sheet.setColumnView(5, maxSpecialityLength); } label = new Label(5, j, statistic.getSpecialityName(), format); sheet.addCell(label); label = new Label(6, j, statistic.getLevelName(), format); sheet.addCell(label); label = new Label(7, j, String.valueOf(statistic .getLevelLength()), format); sheet.addCell(label); number = new Number(8, j, (double) statistic.getStudentNumber(), format); sheet.addCell(number); number = new Number(9, j, (double) statistic.getIncreaseStu(), format); sheet.addCell(number); number = new Number(10, j, (double) statistic.getDecreaseStu(), format); sheet.addCell(number); number = new Number(11, j, (double) statistic .getUngraduateNumber(), format); sheet.addCell(number); label = new Label(12, j, statistic.getGraduateTime(), format); sheet.addCell(label); j++; if (i == size - 1) { length = this.getStringLength(siteName + " 汇总"); if (maxSiteLength < length) { maxSiteLength = length; sheet.setColumnView(2, maxSiteLength); } label = new Label(2, j, siteName + " 汇总", format); sheet.addCell(label); formu = "SUM(I" + startLine + ":I" + j + ")"; formula = new Formula(8, j, formu, format); sheet.addCell(formula); formu = "SUM(J" + startLine + ":J" + j + ")"; formula = new Formula(9, j, formu, format); sheet.addCell(formula); formu = "SUM(K" + startLine + ":K" + j + ")"; formula = new Formula(10, j, formu, format); sheet.addCell(formula); formu = "SUM(L" + startLine + ":L" + j + ")"; formula = new Formula(11, j, formu, format); sheet.addCell(formula); // j++; } } workbook.write(); in.close(); workbook.close(); } catch (IOException e) { e.printStackTrace(); } catch (BiffException e) { e.printStackTrace(); } catch (WriteException e) { e.printStackTrace(); } return flag; } StatisticVo类如下: package com.view.vo; public class StatisticVo { private String typeId; private String typeName; private String siteId; private String siteName; private String grade; private String graduateTime; private String specialityId; private String specialityName; private String levelId; private String levelName; private float levelLength; private int studentNumber; private int ungraduateNumber; private int graduateNumber; private int increaseStu; private int decreaseStu; private String courseId; private String courseName; private String examTime; // private String examFlag; private int shouldExamStu; private int notExamStu; private int examStu; public StatisticVo() { super(); // TODO Auto-generated constructor stub } public StatisticVo(String typeId, String typeName, String siteId, String siteName, String grade, String graduateTime, String specialityId, String specialityName, String levelId, String levelName, int studentNumber, int ungraduateNumber, int graduateNumber, String courseId, String courseName, String examTime, int shouldExamStu, int notExamStu, int examStu) { super(); // TODO Auto-generated constructor stub this.typeId = typeId; this.typeName = typeName; this.siteId = siteId; this.siteName = siteName; this.grade = grade; this.graduateTime = graduateTime; this.specialityId = specialityId; this.specialityName = specialityName; this.levelId = levelId; this.levelName = levelName; this.studentNumber = studentNumber; this.ungraduateNumber = ungraduateNumber; this.graduateNumber = graduateNumber; this.courseId = courseId; this.courseName = courseName; this.examTime = examTime; // this.examFlag = examFlag; this.shouldExamStu = shouldExamStu; this.notExamStu = notExamStu; this.examStu = examStu; } public StatisticVo(String typeId, String typeName, String siteId, String siteName, String grade, String graduateTime, String specialityId, String specialityName, String levelId, String levelName, float levelLength, int studentNumber, int ungraduateNumber, int increaseStu, int decreaseStu) { super(); this.typeId = typeId; this.typeName = typeName; this.siteId = siteId; this.siteName = siteName; this.grade = grade; this.graduateTime = graduateTime; this.specialityId = specialityId; this.specialityName = specialityName; this.levelId = levelId; this.levelName = levelName; this.levelLength = levelLength; this.studentNumber = studentNumber; this.ungraduateNumber = ungraduateNumber; this.increaseStu = increaseStu; this.decreaseStu = decreaseStu; } public StatisticVo(String typeId, String typeName, String siteId, String siteName, String grade, String graduateTime, String specialityId, String specialityName, String levelId, String levelName, int studentNumber, int ungraduateNumber, int graduateNumber, int increaseStu, int decreaseStu, String courseId, String courseName, String examTime, int shouldExamStu, int notExamStu, int examStu) { super(); // TODO Auto-generated constructor stub this.typeId = typeId; this.typeName = typeName; this.siteId = siteId; this.siteName = siteName; this.grade = grade; this.graduateTime = graduateTime; this.specialityId = specialityId; this.specialityName = specialityName; this.levelId = levelId; this.levelName = levelName; this.studentNumber = studentNumber; this.ungraduateNumber = ungraduateNumber; this.graduateNumber = graduateNumber; this.increaseStu = increaseStu; this.decreaseStu = decreaseStu; this.courseId = courseId; this.courseName = courseName; this.examTime = examTime; this.shouldExamStu = shouldExamStu; this.notExamStu = notExamStu; this.examStu = examStu; } public int getDecreaseStu() { return decreaseStu; } public void setDecreaseStu(int decreaseStu) { this.decreaseStu = decreaseStu; } public int getIncreaseStu() { return increaseStu; } public void setIncreaseStu(int increaseStu) { this.increaseStu = increaseStu; } public String getCourseId() { return courseId; } public void setCourseId(String courseId) { this.courseId = courseId; } public String getCourseName() { return courseName; } public void setCourseName(String courseName) { this.courseName = courseName; } // public String getExamFlag() { // return examFlag; // } // // public void setExamFlag(String examFlag) { // this.examFlag = examFlag; // } public int getExamStu() { return examStu; } public void setExamStu(int examStu) { this.examStu = examStu; } public String getExamTime() { return examTime; } public void setExamTime(String examTime) { this.examTime = examTime; } public String getGrade() { return grade; } public void setGrade(String grade) { this.grade = grade; } public int getGraduateNumber() { return graduateNumber; } public void setGraduateNumber(int graduateNumber) { this.graduateNumber = graduateNumber; } public String getGraduateTime() { return graduateTime; } public void setGraduateTime(String graduateTime) { this.graduateTime = graduateTime; } public String getLevelId() { return levelId; } public void setLevelId(String levelId) { this.levelId = levelId; } public String getLevelName() { return levelName; } public void setLevelName(String levelName) { this.levelName = levelName; } public float getLevelLength() { return levelLength; } public void setLevelLength(float levelLength) { this.levelLength = levelLength; } public int getNotExamStu() { return notExamStu; } public void setNotExamStu(int notExamStu) { this.notExamStu = notExamStu; } public int getShouldExamStu() { return shouldExamStu; } public void setShouldExamStu(int shouldExamStu) { this.shouldExamStu = shouldExamStu; } public String getSiteId() { return siteId; } public void setSiteId(String siteId) { this.siteId = siteId; } public String getSiteName() { return siteName; } public void setSiteName(String siteName) { this.siteName = siteName; } public String getSpecialityId() { return specialityId; } public void setSpecialityId(String specialityId) { this.specialityId = specialityId; } public String getSpecialityName() { return specialityName; } public void setSpecialityName(String specialityName) { this.specialityName = specialityName; } public int getStudentNumber() { return studentNumber; } public void setStudentNumber(int studentNumber) { this.studentNumber = studentNumber; } public String getTypeId() { return typeId; } public void setTypeId(String typeId) { this.typeId = typeId; } public String getTypeName() { return typeName; } public void setTypeName(String typeName) { this.typeName = typeName; } public int getUngraduateNumber() { return ungraduateNumber; } public void setUngraduateNumber(int ungraduateNumber) { this.ungraduateNumber = ungraduateNumber; } }