前端代码
//获取数据
function exportAccountBalance(e) {
location.href = ctx + "/admin/account/select/exportAccountBalance/"+ JSON.stringify(query) ;
}
controller代码
/**
* 导出
*/
@RequestMapping("exportAccountBalance/{json}")
@ResponseBody
public void exportCashierData(@PathVariable String json, HttpSession session, HttpServletResponse response) throws Exception {
String fileName = new String("科目余额表");
List<AccountBalance> list = getAccountBalanceListForTableOrExport(json, session, true);
//时间区间
String time = getTimeByJson(json);
getExportAccountBalance(list, fileName, time, session, response);
}
导出的业务逻辑
/**
* 将科目余额表数据写入到Excel文件中
* @param list
* @param fileName
* @param time
* @param session
* @param response
*/
public void getExportAccountBalance(List<AccountBalance> list, String fileName, String time, HttpSession session, HttpServletResponse response) {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet(fileName);
sheet.setColumnWidth(0, 6000);
sheet.setColumnWidth(1, 8000);
sheet.setColumnWidth(2, 2000);
sheet.setColumnWidth(3, 4000);
sheet.setColumnWidth(4, 4000);
sheet.setColumnWidth(5, 4000);
sheet.setColumnWidth(6, 4000);
sheet.setColumnWidth(7, 4000);
sheet.setColumnWidth(8, 2000);
sheet.setColumnWidth(9, 4000);
//样式：字体 大小
HSSFFont font = (HSSFFont) wb.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 11);
// 表头样式对象： 垂直 水平
CellStyle titleStyle = wb.createCellStyle();
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setFont(font);
// 金额样式
CellStyle moneyStyle = wb.createCellStyle();
moneyStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setFont(font);
for (int i = 2; i < 10; i++){
sheet.setDefaultColumnStyle(i, moneyStyle);
}
// 科目名称样式
CellStyle stringStyle = wb.createCellStyle();
stringStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
stringStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
stringStyle.setFont(font);
sheet.setDefaultColumnStyle(0, stringStyle);
sheet.setDefaultColumnStyle(1, stringStyle);
//表头
Row row0 = sheet.createRow(0);
row0.setHeight((short) 700);
Cell cell00 = row0.createCell(0);
cell00.setCellValue("科目余额表");
cell00.setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0,0,0,9));
//单位信息
Row row1 = sheet.createRow(1);
row1.setHeight((short) 500);
row1.createCell(0).setCellValue("组织名称：");
row1.createCell(1).setCellValue(GetUserUtils.GetUserUnit(session).getUnitName());
sheet.addMergedRegion(new CellRangeAddress(1,1,1,3));
row1.createCell(4).setCellValue(time);
sheet.addMergedRegion(new CellRangeAddress(1,1,4,5));
row1.createCell(6).setCellValue("单位：元");
sheet.addMergedRegion(new CellRangeAddress(1,1,6,9));
//行标签
Row row2 = sheet.createRow(2);
row2.setHeight((short) 500);
row2.createCell(0).setCellValue("科目代码");
row2.createCell(1).setCellValue("科目名称");
row2.createCell(2).setCellValue("方向");
row2.createCell(3).setCellValue("期初余额");
row2.createCell(4).setCellValue("借方发生");
row2.createCell(5).setCellValue("贷方发生");
row2.createCell(6).setCellValue("借方累计");
row2.createCell(7).setCellValue("贷方累计");
row2.createCell(8).setCellValue("方向");
row2.createCell(9).setCellValue("余额");
//数据域
BigDecimal sumDebit = new BigDecimal("0.00");
BigDecimal sumCredit = new BigDecimal("0.00");
BigDecimal debit = new BigDecimal("0.00");
BigDecimal credit = new BigDecimal("0.00");
BigDecimal beginBalance1 = new BigDecimal("0.00");
BigDecimal beginBalance2 = new BigDecimal("0.00");
BigDecimal endBalance1 = new BigDecimal("0.00");
BigDecimal endBalance2 = new BigDecimal("0.00");
String dir1 = new String("借");
String dir2 = new String("贷");
for (int i = 0; i < list.size(); i++){
//添加单条数据
Row row = sheet.createRow(i+3);
row.setHeight((short) 300);
row.createCell(0).setCellValue(list.get(i).getSubjectCode());
row.createCell(1).setCellValue(list.get(i).getSubjectName());
row.createCell(2).setCellValue(list.get(i).getDir1());
if (list.get(i).getBeginBalance() != null){ row.createCell(3).setCellValue(list.get(i).getBeginBalance().toString()); }
if (list.get(i).getDebit() != null) { row.createCell(4).setCellValue(list.get(i).getDebit().toString()); }
if (list.get(i).getCredit() != null) { row.createCell(5).setCellValue(list.get(i).getCredit().toString()); }
if (list.get(i).getDebitBalance() != null) { row.createCell(6).setCellValue(list.get(i).getDebitBalance().toString()); }
if (list.get(i).getCreditBalance() != null) { row.createCell(7).setCellValue(list.get(i).getCreditBalance().toString()); }
row.createCell(8).setCellValue(list.get(i).getDir2());
if (list.get(i).getEndBalance() != null) { row.createCell(9).setCellValue(list.get(i).getEndBalance().toString()); }
//统计合计数据
if (list.get(i).getPid() == 0) {
if (list.get(i).getDebitBalance() != null) {
sumDebit = list.get(i).getDebitBalance().add(sumDebit);
}
if (list.get(i).getCreditBalance() != null) {
sumCredit = list.get(i).getCreditBalance().add(sumCredit);
}
if (list.get(i).getDebit() != null) {
debit = list.get(i).getDebit().add(debit);
}
if (list.get(i).getCredit() != null) {
credit = list.get(i).getCredit().add(credit);
}
if (list.get(i).getSource() == 1) {
if (list.get(i).getBeginBalance() != null) {
beginBalance1 = list.get(i).getBeginBalance().add(beginBalance1);
}
if (list.get(i).getEndBalance() != null) {
endBalance1 = list.get(i).getEndBalance().add(endBalance1);
}
} else {
if (list.get(i).getBeginBalance() != null) {
beginBalance2 = list.get(i).getBeginBalance().add(beginBalance2);
}
if (list.get(i).getEndBalance() != null) {
endBalance2 = list.get(i).getEndBalance().add(endBalance2);
}
}
}
}
//汇总数据：
Row rowDebit = sheet.createRow(list.size() + 3);
Row rowCredit = sheet.createRow(list.size() + 4);
rowDebit.setHeight((short) 300);
rowCredit.setHeight((short) 300);
rowDebit.createCell(0).setCellValue("借方合计");
rowCredit.createCell(0).setCellValue("贷方合计");
sheet.addMergedRegion(new CellRangeAddress(list.size() + 3,list.size() + 3,0,1));
sheet.addMergedRegion(new CellRangeAddress(list.size() + 4,list.size() + 4,0,1));
rowDebit.createCell(2).setCellValue(dir1);
rowDebit.createCell(3).setCellValue(beginBalance1.toString());
rowDebit.createCell(4).setCellValue(debit.toString());
rowDebit.createCell(6).setCellValue(sumDebit.toString());
rowDebit.createCell(8).setCellValue(dir1);
rowDebit.createCell(9).setCellValue(endBalance1.toString());
rowCredit.createCell(2).setCellValue(dir2);
rowCredit.createCell(3).setCellValue(beginBalance2.toString());
rowCredit.createCell(5).setCellValue(credit.toString());
rowCredit.createCell(7).setCellValue(sumCredit.toString());
rowCredit.createCell(8).setCellValue(dir2);
rowCredit.createCell(9).setCellValue(endBalance2.toString());
try (OutputStream fileOut = new FileOutputStream(fileName)) {
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
FileUtil.doExport(fileName, response, wb);
}
此处业务逻辑部分比较复杂，代码没有做优化，不是很美观。
使用的下载文件方法 doExport()
/**
* 导出excel(浏览器下载方式)
*
* @param fileName 导出的文件名
* @param response 响应
* @param workbook 工作表
*/
public static void doExport(String fileName,
HttpServletResponse response,
Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xls");
workbook.write(response.getOutputStream());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
}
}
效果图
