导出excel多sheet页
原创
问题
最近碰到这样一个需求，就是任课教师需要把自己所教学科的学生的每次作业全部导出来。每一个教师不可能只教一个班，可能带多个班。那么导出的成绩就想是多个班的学生每次作业成绩。这样就用到了excel的sheet页。
导入poi包
·····<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency> ·学生总的学生成绩类
public class TeachingHomeworkcjVO{
/**
* 作业成绩
*/
@ExcelField(name = "学期")
private String xq; //学期
private String stuId; //学生id
@ExcelField(name = "学生名称")
private String stuName; //学生名称
private String clazzId; //班级id
@ExcelField(name = "班级名称")
private String clazzName; //班级名称
@ExcelField(name = "学科")
private String kc; //学科
private String kcId; //学科id
private String teaId; //任课教师id
@ExcelField(name = "任课教师名称")
private String teaName; //任课教师名称
@ExcelField(name = "平均分")
private Float avgScore = 0f; //平均分
@ExcelField(name = "总分")
private Float sumScore = 0f; //总分
private List<TeachingHomeworkcjhwVO> hwcjs; //学科下每次作业成绩
public TeachingHomeworkcjVO() {
}
public TeachingHomeworkcjVO(SysUser user,TeachingHomeworkcjVO vo) {
this.xq = vo.getXq();
this.stuId = user.getId();
this.stuName = user.getName();
this.clazzId = vo.getClazzId();
this.clazzName = vo.getClazzName();
this.kc = vo.getKc();
this.teaId = UserUtils.getCurrentUserId();
this.teaName = UserUtils.getCurrentUserName();
}
public Float getSumScore(List<TeachingHomeworkcjhwVO> hwcjs) {
Float score = 0f;
for (TeachingHomeworkcjhwVO vo : hwcjs) {
score = vo.getScore() + score;
}
return score;
}
// get，set方法
}学生作业类
/**
* 作业成绩
*/
public class TeachingHomeworkcjhwVO {
private String hwName; //作业名称
private Float score; //分数
public TeachingHomeworkcjhwVO() {
}
public TeachingHomeworkcjhwVO(TeachingHomeworkStu hwStu) {
this.hwName = hwStu.getHw();
this.score = hwStu.getScore();
}
//get,set方法
}ExcelUtils
package com.rc.common.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.util.Assert;
import org.springframework.web.multipart.MultipartFile;
import com.google.common.base.Objects;
import com.rc.common.annnotaion.export.ExcelField;
import com.rc.common.constant.SysConstants;
public class ExcelUtils {
/**
* 设置sheet页的列宽
* @return
*/
public static HSSFSheet getColumnWidth(HSSFSheet sheet,double[] cellRules,int length) {
for(int i = 0; i < length; i++) {
sheet.setColumnWidth(i, (int)(cellRules[i] * 2000) );
}
return sheet;
}
/**
* 设置边框
* @param workBook
* @return
*/
public static CellStyle getStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
return style;
}
public static Row getRow(Sheet sheet, int index) {
Row row = sheet.getRow(index);
if (row == null) {
row = sheet.createRow(index);
}
return row;
}
/**
* 设置sheet页的列宽
* @return
*/
public static HSSFSheet getColumnWidth(HSSFSheet sheet,int length) {
for(int i = 0; i < length; i++) {
sheet.setColumnWidth(i, 3000);
}
return sheet;
}
/**
* 设置首行内容格式
* @param row
* @param index
* @param value
* @param workBook
* @return
*/
public static Cell setTotalCellValue(Row row, Integer index, String value, Workbook workBook) {
Cell cell = row.getCell(index);
if (cell == null) {
cell = row.createCell(index);
}
cell.setCellStyle(getTotalStyle(workBook));
cell.setCellValue(value);
return cell;
}
/**
* 设置首行边框样式
* @param workBook
* @return
*/
public static CellStyle getTotalStyle(Workbook workBook) {
CellStyle style = workBook.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setFillForegroundColor((short)38);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setAlignment(CellStyle.ALIGN_CENTER);
return style;
}
public static void export(HttpServletRequest request, HttpServletResponse response, Workbook workbook, String filename) throws IOException {
OutputStream out = response.getOutputStream();
// excel 表文件名
String fileName = filename;
String fileName11 = "";
String userAgent = request.getHeader("USER-AGENT");
if (StringUtils.contains(userAgent, "Firefox") || StringUtils.contains(userAgent, "firefox")) {// 火狐浏览器
fileName11 = new String(fileName.getBytes(), "ISO8859-1");
} else {
fileName11 = URLEncoder.encode(fileName, "UTF-8");// 其他浏览器
}
String headStr = "attachment; filename=\"" + fileName11 + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setCharacterEncoding("UTF-8");
response.setHeader("Content-Disposition", headStr);
workbook.write(out);
out.flush();
// workbook.close();
}
}导出sheet的service
package com.rc.teaching.service;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.rc.common.mongodb.utils.MongoQueryUtils;
import com.rc.common.mongodb.utils.MongoUtils;
import com.rc.common.utils.Ast;
import com.rc.common.utils.ClazzUtils;
import com.rc.common.utils.DateUtils;
import com.rc.common.utils.ExcelUtils;
import com.rc.common.utils.Jh;
import com.rc.common.utils.NumUtils;
import com.rc.common.utils.UserUtils;
import com.rc.sys.entity.SysUser;
import com.rc.teaching.entity.TeachingHomeworkStu;
import com.rc.teaching.entity.TeachingHomeworkcjVO;
import com.rc.teaching.entity.TeachingHomeworkcjhwVO;
@Service
public class TeachingHomeworkcjVOService {
//根据传过来的班级，查询作业成绩，并且把学生成绩给放到TeachingHomeworkcjVO类中，把每次作业放到
//List<TeachingHomeworkcjhwVO>中，中间做了个小的排序，为了表头和数据能对应上
public Map<String, Object> xqHwcj(TeachingHomeworkcjVO vo) {
//根据传过来的班级
Ast.astNotBlank(vo.getClazzId(), "请选择班级");
Ast.astNotBlank(vo.getXq(), "请选择学期");
Ast.astNotBlank(vo.getKcId(), "请选择学科");
String[] clazzs = vo.getClazzId().split(",");
Criteria criteria = MongoQueryUtils.getDelFlagCriteria();
criteria.and("xq").is(vo.getXq());
criteria.and("teaId").is(UserUtils.getCurrentUserId());
criteria.and("kcId").is(vo.getKcId());
List<AggregationOperation> opts = new ArrayList<AggregationOperation>();
opts.add(Aggregation.match(criteria));
List<TeachingHomeworkStu> list = MongoUtils.findAgg(opts, TeachingHomeworkStu.class);
Map<String, Object> data = Maps.newHashMap();
Map<String, List<TeachingHomeworkStu>> datamap = Maps.newHashMap();
for (TeachingHomeworkStu hwstu : list) {
List<TeachingHomeworkStu> hwstus = datamap.get(hwstu.getStuId());
if (Jh.isBlank(hwstus)) {
hwstus = Lists.newArrayList();
}
hwstus.add(hwstu);
datamap.put(hwstu.getStuId(), hwstus);
}
for (String clazzId : clazzs) {
vo.setClazzName(ClazzUtils.getNameById(clazzId));
List<TeachingHomeworkcjVO> vos = Lists.newArrayList();
// 查询班级所有学生
List<SysUser> users = ClazzUtils.findStuByJxb(clazzId);
for (SysUser user : users) {
TeachingHomeworkcjVO temp = new TeachingHomeworkcjVO(user, vo);
if (datamap.containsKey(user.getId())) {
List<TeachingHomeworkcjhwVO> hwcjs = Lists.newArrayList();
for (TeachingHomeworkStu stu : datamap.get(user.getId())) {
TeachingHomeworkcjhwVO cjhw = new TeachingHomeworkcjhwVO(stu);
hwcjs.add(cjhw);
}
temp.setHwcjs(hwcjs);
}
vos.add(temp);
}
if (Jh.isNotBlank(vos)) {
for (TeachingHomeworkcjVO cjvo : vos) {
List<TeachingHomeworkcjhwVO> hwvoList = cjvo.getHwcjs();
if (Jh.isNotBlank(hwvoList)) {
Collections.sort(hwvoList, new Comparator<TeachingHomeworkcjhwVO>() {
@Override
public int compare(TeachingHomeworkcjhwVO o1, TeachingHomeworkcjhwVO o2) {
return o1.getHwName().compareTo(o2.getHwName());
};
});
cjvo.setHwcjs(hwvoList);
cjvo.setSumScore(cjvo.getSumScore(hwvoList));
cjvo.setAvgScore(NumUtils.divide(cjvo.getSumScore(hwvoList), hwvoList.size()).floatValue());
}
}
}
List<String> hwnames = Lists.newArrayList();
if (Jh.isNotBlank(vos.get(0).getHwcjs())) {
for (TeachingHomeworkcjhwVO temp : vos.get(0).getHwcjs()) {
hwnames.add(temp.getHwName());
}
}
data.put(clazzId + "list", vos);
data.put(clazzId + "hwNames", hwnames);
//System.out.println(111);
}
return data;
}
public void exportXqHwcj(HttpServletRequest request, HttpServletResponse response,TeachingHomeworkcjVO vo) {
Map<String, Object> data = xqHwcj(vo);
HSSFWorkbook workbook = new HSSFWorkbook();
String[] clazzs = vo.getClazzId().split(",");
for (String clazzId : clazzs) {
HSSFSheet sheet = workbook.createSheet(ClazzUtils.getNameById(clazzId));
@SuppressWarnings("unchecked")
List<String> hwnames = (List<String>) data.get(clazzId + "hwNames");
List<String> gdHeader = Lists.newArrayList("学期","学生姓名","班级名称","学科","任课教师名称");
if (Jh.isNotBlank(hwnames)) {
gdHeader.addAll(hwnames);
}
gdHeader.add("平均分");
gdHeader.add("总分");
//合并头
String[] arr = {};
ExcelUtils.getColumnWidth(sheet, gdHeader.size());
HSSFRow title = sheet.createRow(0);
title.setHeightInPoints(20);
title.setHeight((short)(21.5*20));
for (int i = 0; i < gdHeader.toArray(arr).length; i++) {
ExcelUtils.setTotalCellValue(title, i, gdHeader.toArray(arr)[i], workbook);
}
Row row = ExcelUtils.getRow(sheet, 1);
CellStyle cellStyle = ExcelUtils.getStyle(workbook);
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
@SuppressWarnings("unchecked")
List<TeachingHomeworkcjVO> cjvos = (List<TeachingHomeworkcjVO>) data.get(clazzId + "list");
// 第二行设置数据
if (Jh.isNotBlank(cjvos)) {
int rownum = 1;
for (int i = 0; i < cjvos.size(); i++) {
row = ExcelUtils.getRow(sheet, rownum);
TeachingHomeworkcjVO cj = cjvos.get(i);
int m = 0;
ExcelUtils.setCellValue(row, m++, cj.getXq());
ExcelUtils.setCellValue(row, m++, cj.getStuName());
ExcelUtils.setCellValue(row, m++, cj.getClazzName());
ExcelUtils.setCellValue(row, m++, cj.getKc());
ExcelUtils.setCellValue(row, m++, cj.getTeaName());
if (Jh.isNotBlank(cj.getHwcjs())) {
for (TeachingHomeworkcjhwVO hw : cj.getHwcjs()) {
ExcelUtils.setCellValue(row, m++, hw.getScore());
}
}
ExcelUtils.setCellValue(row, m++, cj.getAvgScore());
ExcelUtils.setCellValue(row, m++, cj.getSumScore());
rownum++;
}
}
}
try {
ExcelUtils.export(request, response, workbook, "学生作业分数情况表_" + DateUtils.getDateTime() + ".xls");
} catch (Exception e) {
// TODO: handle exception
Ast.astFalse("导出分数情况表_信息出错");
}
}
}解析导出sheet页方法
Map<String, Object> data；根据班级id把需要显示的表头和数据放在data里面，这里面根据班级id区分。导出的时候， HSSFWorkbook workbook = new HSSFWorkbook();使用这个类来导出多sheet页，先把固定的头信息放到List<String> gdHeader；中，然后把变量作业名称也放到gdHeader中，根据循环，每次设置对应行列的数据，然后调用导出方法，这样就能导出多sheet也学科成绩。
导出模板如下
sheet1
sheet2
sheet3
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
目录