专栏首页有脑子的搬砖工导出excel多sheet页
原创

导出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

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 日志切面的配置使用

    在User类上加上@Component(value=“user”)//注入beanfactory中

    微醺
  • springclound快速入门

    微醺
  • ssm的环境搭建

    微醺
  • Hadoop阅读笔记(一)——强大的MapReduce

    前言:来园子已经有8个月了,当初入园凭着满腔热血和一脑门子冲动,给自己起了个响亮的旗号“大数据 小世界”,顿时有了种世界都是我的,世界都在我手中的赶脚。可是.....

    JackieZheng
  • Eclipse连接Hadoop集群和WordCount实战

    本文将主要介绍Eclipse连接Hadoop集群和WordCount实践项目两大内容。

    挖掘大数据
  • Spring Boot 1.X和2.X优雅重启实战

    项目在重新发布的过程中,如果有的请求时间比较长,还没执行完成,此时重启的话就会导致请求中断,影响业务功能,优雅重启可以保证在停止的时候,不接收外部的新的请求,等...

    纯洁的微笑
  • Spring Boot 优雅重启知多少

    项目在重新发布的过程中,如果有的请求处理时间比较长,还没执行完成,此时重启的话就会导致请求中断,影响业务功能,优雅重启可以保证在停止的时候,不接收外部的新的请求...

    黄泽杰
  • Spring Boot 1.X和2.X优雅重启实战

    项目在重新发布的过程中,如果有的请求时间比较长,还没执行完成,此时重启的话就会导致请求中断,影响业务功能,优雅重启可以保证在停止的时候,不接收外部的新的请求,等...

    用户5224393
  • Hadoop(九)Hadoop IO之Compression和Codecs

      前面一篇介绍了Java怎么去查看数据块的相关信息和怎么去查看文件系统。我们只要知道怎么去查看就行了!接下来我分享的是Hadoop的I/O操作。

    大道七哥
  • 用 Python 做数学建模

    数学建模中,大多数人都在用MATLAB,但MATLAB不是一门正统的计算机编程语言,而且速度慢还收费,最不能忍受的就是MATLAB编辑器不支持代码自动补全。py...

    KEVINGUO_CN

扫码关注云+社区

领取腾讯云代金券