工具类
public class ExportExcelUtils {
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: liuct @ 2019-01-29
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List<Object>> result,
OutputStream out) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
// style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
// style.setFillBackgroundColor(HSSFColor.WHITE.index);
// style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
// 生成一个字体
HSSFFont font = workbook.createFont();
// font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<Object> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
for (Object str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
cell.setCellValue(str.toString());
cellIndex++;
}
index++;
}
}
}
}
controller层请求以及请求方式
@ApiOperation(value = "上线预测-各校上线情况总结", notes = "上线预测-各校上线情况总结")
@PostMapping("/jks/oneline/school/summary")
public RequestResult<SchoolSummaryVO> getSchoolSummary(@Valid @RequestBody OnlineForecastDTO onlineForecastDTO) throws ErrorException {
return RequestResult.ok(customOnelineForecastService.getSchoolSummary(onlineForecastDTO));
}
调取方法sevice层
@Transactional
public String getOut(OnlineForecastDTO onlineForecastDTO) throws ErrorException, IOException {
log.info("清华北大预测-导出{}", onlineForecastDTO);
// 上线率(获取数据库中的数据)
List<SchoolRateVO> listOne = getOneLineRate(onlineForecastDTO);
// 上线人数(获取数据库中的数据)
List<SchoolLineVO> listTwo = getOneLineNum(onlineForecastDTO);
// 随便写一个位置(因为之前是直接通过流的形式保存到磁盘中)
// String filepath = "E:/test.xls";
try {
// OutputStream out = new FileOutputStream(filepath);//导出本地
OutputStream out = response.getOutputStream();//输出客户端
List<List<Object>> data1 = new ArrayList<>();
List<List<Object>> data2 = new ArrayList<>();
for (SchoolRateVO schoolRateVO : listOne) {
List<Object> rowData = new ArrayList();
rowData.add(schoolRateVO.getSchool());
rowData.add(schoolRateVO.getOnlineRateFirst());
rowData.add(schoolRateVO.getRateChangeFirst());
rowData.add(schoolRateVO.getOnlineRateSecond());
rowData.add(schoolRateVO.getRateChangeSecond());
rowData.add(schoolRateVO.getOnlineRate());
rowData.add(schoolRateVO.getRateChange());
data1.add(rowData);
}
for (SchoolLineVO schoolLineVO : listTwo) {
List<Object> rowData = new ArrayList();
rowData.add(schoolLineVO.getSchool());
rowData.add(schoolLineVO.getOnlineFirst());
rowData.add(schoolLineVO.getNumChangeFirst());
rowData.add(schoolLineVO.getOnlineSecond());
rowData.add(schoolLineVO.getNumChangeSecond());
rowData.add(schoolLineVO.getOnline());
rowData.add(schoolLineVO.getNumChange());
data2.add(rowData);
}
String[] headers = {"学校名称", "本科一批上线率", "本科一批上线率变化",
"本科二批上线率", "本科二批上线率变化", "本科上线率", "本科上线率变化"};
String[] headers2 = {"学校名称", "本科一批上线人数", "本科一批上线人数变化",
"本科二批上线人数", "本科二批上线人数变化", "本科上线人数", "本科上线人数变化"};
ExportExcelUtils eeu = new ExportExcelUtils();
HSSFWorkbook workbook = new HSSFWorkbook();
eeu.exportExcel(workbook, 0, "上线率", headers, data1, out);
eeu.exportExcel(workbook, 1, "上线人数", headers2, data2, out);
//原理就是将所有的数据一起写入,然后再关闭输入流。(如果想保存到具体的位置就放开这里)
// workbook.write(out);
// 如果不是直接保存到磁盘中,那么就通过流的形式传回给前端(注意前端只能通过form表单请求)
response.setContentType("application/force-download");
response.setCharacterEncoding("UTF-8");
// 设置文件名
response.addHeader("Content-Disposition", "attachment;fileName=" +
URLEncoder.encode("清华北大预测-导出", "UTF-8") + ".xls");
workbook.write(out);
out.close();
} catch (
Exception e) {
e.printStackTrace();
}
return null;
}
结果