//控制层
@PostMapping("/exportDeposit")
public void excelDownload(HttpServletResponse response,@ModelAttribute FinanceBudgetData data1) throws IOException {
List<List<String>> excelData = new ArrayList<>();
List<String> head = new ArrayList<>();
head.add("日期");
head.add("部门");
head.add("事项");
head.add("计划收入金额");
head.add("计划支出金额");
head.add("余额");
head.add("备注");
excelData.add(head);
PackageResultData resultData=budgetService.getList(data1);
List<ResultData> baseResultData=resultData.getRows();
for(ResultData resData:baseResultData) {
List<String> data = new ArrayList<>();
data.add(resData.getDate());
data.add(resData.getOrganizationName());
data.add(resData.getItem());
data.add(resData.getIncome());
data.add(resData.getExpend());
data.add(resData.getAmount());
data.add(resData.getRemark());
excelData.add(data);
}
String sheetName = "预算现金流量表";
String fileName = "预算现金流量表.xls";
fileName = URLEncoder.encode(fileName, "UTF-8");
ExcelUtil.exportExcel(response, excelData, sheetName, fileName, 15);
}
//服务层
public class ExcelUtil {
/**
* Excel表格导出
* @param response HttpServletResponse对象
* @param excelData Excel表格的数据,封装为List<List<String>>
* @param sheetName sheet的名字
* @param fileName 导出Excel的文件名
* @param columnWidth Excel表格的宽度,建议为15
* @throws IOException 抛IO异常
*/
public static void exportExcel(HttpServletResponse response,
List<List<String>> excelData,
String sheetName,
String fileName,
int columnWidth) throws IOException {
//声明一个工作簿
HSSFWorkbook workbook = new HSSFWorkbook();
//生成一个表格,设置表格名称
HSSFSheet sheet = workbook.createSheet(sheetName);
//设置表格列宽度
sheet.setDefaultColumnWidth(columnWidth);
//写入List<List<String>>中的数据
int rowIndex = 0;
for(List<String> data : excelData){
//创建一个row行,然后自增1
HSSFRow row = sheet.createRow(rowIndex++);
//遍历添加本行数据
for (int i = 0; i < data.size(); i++) {
//创建一个单元格
HSSFCell cell = row.createCell(i);
//创建一个内容对象
HSSFRichTextString text = new HSSFRichTextString(data.get(i));
//将内容对象的文字内容写入到单元格中
cell.setCellValue(text);
}
}
//准备将Excel的输出流通过response输出到页面下载
//八进制输出流
//response.setContentType("application/octet-stream");
response.setContentType("application/ms-excel;charset=utf-8");
//设置导出Excel的名称
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
//刷新缓冲
response.flushBuffer();
//workbook将Excel写入到response的输出流中,供页面下载该Excel文件
workbook.write(response.getOutputStream());
//关闭workbook
workbook.close();
}
}