/**
* @Description 导出凭证分录的excel文件
* @param glDetail
* @param request
* @param response
* @param redirectAttributes
* @return
* @Date 2017年5月4日 上午10:33:22
* @Author FANFYK
*/
@SuppressWarnings("unused")
@RequestMapping(value = "detailEx", method=RequestMethod.POST)
public String detailExport(GlDetail glDetail, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes){
List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>();
Page<GlDetail> page = new Page<GlDetail>(request, response,"ajaxPage");
List<GlDetail> searchList = new ArrayList<GlDetail>();
try {
searchList = glDetailService.findAjaxList(page, glDetail);
} catch (IllegalAccessException e1) {
e1.printStackTrace();
} catch (InvocationTargetException e1) {
e1.printStackTrace();
}
GlVoucher glVoucher = glVoucherService.get(glDetail.getPkVoucher());
// 设置单元格样式设置统一的单元格大小
HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
Sheet sheet = wb.createSheet("新的工作表"); // --->创建了一个工作簿
sheet.setColumnWidth((short) 3, 30 * 250); // --->设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定
sheet.setColumnWidth((short) 4, 40 * 400); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
sheet.setColumnWidth((short) 5, 30 * 250);
sheet.setColumnWidth((short) 6, 30 * 250);
sheet.setColumnWidth((short) 7, 30 * 250);
sheet.setColumnWidth((short) 8, 30 * 250);
sheet.setDefaultRowHeight((short) 600); // ---->有得时候你想设置统一单元格的高度，就用这个方法
// 样式1，设置内容对齐方式以及边框线的粗细
CellStyle style1 = wb.createCellStyle(); // 样式对象
style1.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直
style1.setAlignment(CellStyle.ALIGN_CENTER); // 水平
style1.setBorderTop(CellStyle.BORDER_THIN); // 上边线
style1.setBorderLeft(CellStyle.BORDER_THIN); // 左边线
style1.setBorderBottom(CellStyle.BORDER_THIN); // 底边线
style1.setBorderRight(CellStyle.BORDER_THIN); // 右边线
// 样式2
CellStyle style2 = wb.createCellStyle(); // 样式对象
style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 垂直
style2.setAlignment(CellStyle.ALIGN_CENTER); // 水平
// 设置标题字体格式
Font font = wb.createFont();
// 设置字体样式
font.setFontHeightInPoints((short) 20); // --->设置字体大小
font.setFontName("Courier New"); // --->设置字体，是什么类型例如：宋体
// 设置单个表格内容的行数（5行）
int table = (int) Math.ceil(searchList.size() / 5)+1;
// 循环绘制表格
for (int j = 0; j < table; j++) {
// 表格第一行(记账凭证行)
Row row1 = sheet.createRow(0+12*j); // --->创建一行
// CellRangeAddress有4个参数：起始行号，终止行号， 起始列号，终止列号
sheet.addMergedRegion(new CellRangeAddress(0+12*j, 0+12*j, (short) 3,(short) 7));
row1.setHeightInPoints(25);
Cell cell1 = row1.createCell((short) 3); // --->创建一个单元格
cell1.setCellStyle(style2); // 设置样式(对齐方式无边框)
cell1.setCellValue(glVoucher.getPkVouchertype().getVouchtypename());
// 表格第二行
Row row2 = sheet.createRow(1+12*j);
sheet.addMergedRegion(new CellRangeAddress(1+12*j, 1+12*j, (short) 3,(short) 7));
Cell cell2_1 = row2.createCell((short) 3);
cell2_1.setCellValue(glVoucher.getPrepareddate());
cell2_1.setCellStyle(style2);
Cell cell2_2 = row2.createCell((short) 8);
cell2_2.setCellValue("来源系统：" + glVoucher.getPkSystemName());
cell2_2.setCellStyle(style2);
// 表格第三行
Row row3 = sheet.createRow(2+12*j);
sheet.addMergedRegion(new CellRangeAddress(2+12*j, 2+12*j, (short) 3,(short) 4));
Cell cell3_1 = row3.createCell((short) 3);
cell3_1.setCellValue(glVoucher.getPkGlorgbook().getGlorgbookname());
cell3_1.setCellStyle(style2);
DecimalFormat df=new DecimalFormat("0000");
Cell cell3_2 = row3.createCell((short) 8);
cell3_2.setCellValue("第" + df.format(glVoucher.getNo()) + "号凭证 -"+df.format(j+1)+"/"+df.format(table));
cell3_2.setCellStyle(style2);
// 表格第四行
sheet.addMergedRegion(new CellRangeAddress(3+12*j, 3+12*j, (short) 0,(short) 2));
Row row4 = sheet.createRow(3+12*j);
row4.setHeightInPoints((short) 30);
Cell cell4_1 = row4.createCell((short) 3);
cell4_1.setCellStyle(style1);
cell4_1.setCellValue("摘要");
Cell cell4_2 = row4.createCell((short) 4);
cell4_2.setCellStyle(style1);
cell4_2.setCellValue("会计科目");
Cell cell4_3 = row4.createCell((short) 5);
cell4_3.setCellStyle(style1);
cell4_3.setCellValue("单价");
Cell cell4_4 = row4.createCell((short) 6);
cell4_4.setCellStyle(style1);
cell4_4.setCellValue("数量");
Cell cell4_5 = row4.createCell((short) 7);
cell4_5.setCellStyle(style1);
cell4_5.setCellValue("借方本币");
Cell cell4_6 = row4.createCell((short) 8);
cell4_6.setCellStyle(style1);
cell4_6.setCellValue("贷方本币");
int endrow = searchList.size();
if(searchList.size()>(5*j+5)){
endrow = 5*j+5;
}
// 钱币金额格式化
NumberFormat numberFormat = new DecimalFormat("#,###.00");
for (int i = 0+5*j; i < endrow; i++) {
// 表格第n行
sheet.addMergedRegion(new CellRangeAddress(4 + i+7*j, 4 + i+7*j,(short) 0, (short) 2));
Row row = sheet.createRow(4 + i+7*j);
row.setHeightInPoints((short) 30);
GlDetail detail = searchList.get(i);
Cell row_1 = row.createCell((short) 3);
row_1.setCellStyle(style1);
row_1.setCellValue(StringUtils.isNotBlank(detail.getExplanation()) ? detail.getExplanation() : "");
Cell row_2 = row.createCell((short) 4);
row_2.setCellStyle(style1);
row_2.setCellValue(!StringUtils.isNull(detail.getPkAccsubj()) && StringUtils.isNotBlank(detail.getPkAccsubj().getDispname()) ?
detail.getPkAccsubj().getDispname() : "" + " " + (StringUtils.isNotBlank(detail.getValueName()) ? detail.getValueName() : ""));
Cell row_3 = row.createCell((short) 5);
row_3.setCellStyle(style1);
if (!StringUtils.isNull(detail.getPrice()) && detail.getPrice() != 0) {
row_3.setCellValue(detail.getPrice());
}
Cell row_4 = row.createCell((short) 6);
row_4.setCellStyle(style1);
if (detail.getDirection().equals("D")) {
if (!StringUtils.isNull(detail.getDebitquantity()) && detail.getDebitquantity() != 0) {
row_4.setCellValue(detail.getDebitquantity());
}
} else {
if (!StringUtils.isNull(detail.getCreditquantity()) && detail.getCreditquantity() != 0) {
row_4.setCellValue(detail.getCreditquantity());
}
}
Cell row_5 = row.createCell((short) 7);
row_5.setCellStyle(style1);
if(!StringUtils.isNull(detail.getLocaldebitamount()) && detail.getLocaldebitamount()!=0){
row_5.setCellValue(numberFormat.format(detail.getLocaldebitamount()));
}
Cell row_6 = row.createCell((short) 8);
row_6.setCellStyle(style1);
if(!StringUtils.isNull(detail.getLocalcreditamount()) && detail.getLocalcreditamount()!=0){
row_6.setCellValue(numberFormat.format(detail.getLocalcreditamount()));
}
}
if(searchList.size()<(5*j+5)){
for (int k = searchList.size(); k < 5*j+5; k++) {
// 表格第n行
sheet.addMergedRegion(new CellRangeAddress(4 + 7*j + k, 4 + 7*j + k,(short) 0, (short) 2));
Row row = sheet.createRow(4 + 7*j + k);
row.setHeightInPoints((short) 30);
Cell row_1 = row.createCell((short) 3);
row_1.setCellStyle(style1);
Cell row_2 = row.createCell((short) 4);
row_2.setCellStyle(style1);
Cell row_3 = row.createCell((short) 5);
row_3.setCellStyle(style1);
Cell row_4 = row.createCell((short) 6);
row_4.setCellStyle(style1);
Cell row_5 = row.createCell((short) 7);
row_5.setCellStyle(style1);
Cell row_6 = row.createCell((short) 8);
row_6.setCellStyle(style1);
}
}
Row row10 = sheet.createRow(9+12*j);
row10.setHeightInPoints((short) 30);
sheet.addMergedRegion(new CellRangeAddress(9+12*j, 9+12*j,(short) 4, (short) 6));
Cell cell10_1 = row10.createCell((short) 3);
cell10_1.setCellStyle(style1);
cell10_1.setCellValue("附单据：0 张");
Cell cell10_3 = row10.createCell((short) 4);
cell10_3.setCellStyle(style1);
Cell cell10_4 = row10.createCell((short) 5);
cell10_4.setCellStyle(style1);
Cell cell10_5 = row10.createCell((short) 6);
cell10_5.setCellStyle(style1);
Cell cell10_6 = row10.createCell((short) 7);
cell10_6.setCellStyle(style1);
Cell cell10_7 = row10.createCell((short) 8);
cell10_7.setCellStyle(style1);
if(j == table-1){
cell10_3.setCellValue("合计："+MoneyUtils.change(glVoucher.getTotaldebit()));
cell10_6.setCellValue(numberFormat.format(glVoucher.getTotaldebit()));
cell10_7.setCellValue(numberFormat.format(glVoucher.getTotalcredit()));
}else{
cell10_3.setCellValue("合计：");
}
Row row11 = sheet.createRow(10+12*j);
row4.setHeightInPoints((short) 25);
Cell cell11_1 = row11.createCell((short) 3);
cell11_1.setCellStyle(style2);
if (glVoucher.getPkManager() != null) {
cell11_1.setCellValue("记账：" + glVoucher.getPkManager().getName());
} else {
cell11_1.setCellValue("记账：");
}
Cell cell11_2 = row11.createCell((short) 4);
cell11_2.setCellStyle(style2);
if (glVoucher.getPkChecked() != null) {
cell11_2.setCellValue("审核：" + glVoucher.getPkChecked().getName());
} else {
cell11_2.setCellValue("审核：");
}
Cell cell11_3 = row11.createCell((short) 5);
cell11_3.setCellStyle(style2);
if (glVoucher.getPkCasher() != null) {
cell11_3.setCellValue("出纳：" + glVoucher.getPkCasher().getName());
} else {
cell11_3.setCellValue("出纳：");
}
Cell cell11_4 = row11.createCell((short) 6);
cell11_4.setCellStyle(style2);
if (glVoucher.getPkPrepared() != null) {
cell11_4.setCellValue("制单：" + glVoucher.getPkPrepared().getName());
} else {
cell11_4.setCellValue("制单：");
}
}
String fileName = "凭证"+DateUtils.getDate("yyyyMMddHHmmss")+".xls";
FileOutputStream fileOut = null;
try {
response.setContentType("application/octet-stream; charset=utf-8");
response.setHeader("Content-Disposition", "attachment; filename="+Encodes.urlEncode(fileName));
wb.write(response.getOutputStream());
response.getOutputStream().flush();
response.getOutputStream().close();
return null;
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fileOut != null) {
try {
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return null;
}
