private String [][] title={{"单位名称","开户行","账期"},{"账号","账户名称","币种"},{"科目代码","科目名称","金额单位"}};
private String [][] content={{"企业账期末余额","银行账期末余额"},{"加：银行已收企业未收","加：企业已收银行未收"},
{"减：银行已付企业未付","减：企业已付银行未付"},{"调整后期末余额","调整后期末余额"}};
private String [] table={"制表人","制表日期","审核人"};
@GetMapping("/exportExcel")
public void exportExcel(@RequestParam("periodItemId")Long periodItemId){
//测试数据内容
ReconciliationPeriodReport report= reportDao.getBy(periodItemId);
Map<String, Object> map=reconciliationPeriodItemDao.getBaseInfo(this.getCurrentUser().getId(),periodItemId);
ReportHelper reportHelper=reprotService.getReportHelper(periodItemId);
//report部分
BigDecimal[][] contentDate={{report.getBankBalance(),report.getEnterpriseBalance()},{report.getEnterpriseNoRecive(),report.getEnterpriseNoRecive()},
{report.getBankNoPay(),report.getEnterpriseNoPay()},{report.getBankModifyBalance(),report.getEnterpriseModifyBalance()}};
//审核人专栏
AuditInfoDto auditInfoDto=auditService.getAuditInfo(periodItemId);
XSSFWorkbook workbook=new XSSFWorkbook();
XSSFSheet sheet=workbook.createSheet("导出余额调节表V1.1");
XSSFRow xssfRow=null;
XSSFCell xssfCell=null;
try{
//设置列宽
sheet.setColumnWidth(0, 3766);
sheet.setColumnWidth(1, 3766);
sheet.setColumnWidth(2, 3766);
sheet.setColumnWidth(3, 3766);
sheet.setColumnWidth(4, 3766);
sheet.setColumnWidth(5, 4100);
sheet.setColumnWidth(6, 3766);
sheet.setColumnWidth(7, 3766);
//指定 4 个参数，起始行，结束行，起始列，结束列。然后这个区域将被合并
CellRangeAddress cellRangeTitle=new CellRangeAddress(0,1,0,7);
CellRangeAddress cellRang1=new CellRangeAddress(2,2,1,2);
CellRangeAddress cellRang2=new CellRangeAddress(3,3,1,2);
CellRangeAddress cellRang3=new CellRangeAddress(4,4,1,2);
CellRangeAddress cellRang4=new CellRangeAddress(2,2,4,5);
CellRangeAddress cellRang5=new CellRangeAddress(3,3,4,5);
CellRangeAddress cellRang6=new CellRangeAddress(4,4,4,5);
//6-11
CellRangeAddress cellRang7=new CellRangeAddress(5,5,0,1);
CellRangeAddress cellRang8=new CellRangeAddress(5,5,2,3);
CellRangeAddress cellRang9=new CellRangeAddress(5,5,4,5);
CellRangeAddress cellRang10=new CellRangeAddress(5,5,6,7);
CellRangeAddress cellRang11=new CellRangeAddress(6,6,0,1);
CellRangeAddress cellRang12=new CellRangeAddress(6,6,2,3);
CellRangeAddress cellRang13=new CellRangeAddress(6,6,4,5);
CellRangeAddress cellRang14=new CellRangeAddress(6,6,6,7);
CellRangeAddress cellRang15=new CellRangeAddress(7,7,0,1);
CellRangeAddress cellRang16=new CellRangeAddress(7,7,2,3);
CellRangeAddress cellRang17=new CellRangeAddress(7,7,4,5);
CellRangeAddress cellRang18=new CellRangeAddress(7,7,6,7);
CellRangeAddress cellRang19=new CellRangeAddress(8,8,0,1);
CellRangeAddress cellRang20=new CellRangeAddress(8,8,2,3);
CellRangeAddress cellRang21=new CellRangeAddress(8,8,4,5);
CellRangeAddress cellRang22=new CellRangeAddress(8,8,6,7);
CellRangeAddress cellRang23=new CellRangeAddress(9,9,0,1);
CellRangeAddress cellRang24=new CellRangeAddress(9,9,2,7);
CellRangeAddress cellRang25=new CellRangeAddress(10,10,1,2);
CellRangeAddress cellRang26=new CellRangeAddress(10,10,4,5);
//合并13行
CellRangeAddress cellRang27=new CellRangeAddress(12,12,0,1);
ExportExcelUtil.setBorderForMergeCell(cellRangeTitle,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang1,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang2,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang3,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang4,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang5,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang6,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang7,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang8,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang9,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang10,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang11,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang12,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang13,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang14,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang15,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang16,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang17,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang18,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang19,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang20,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang21,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang22,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang23,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang24,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang25,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang26,sheet,workbook);
ExportExcelUtil.setBorderForMergeCell(cellRang27,sheet,workbook);
//设置表头,余额调节表
xssfCell=sheet.createRow(0).createCell(0);
xssfCell.setCellValue("余额调节表");
XSSFCellStyle xssfCellStyle=workbook.createCellStyle();
//居中
xssfCellStyle.setAlignment(HorizontalAlignment.CENTER);
//设置字体
XSSFFont font = workbook.createFont();
font.setFontName("宋体");
font.setFontHeightInPoints((short) 16);//设置字体大小
xssfCellStyle.setFont(font);
xssfCell.setCellStyle(xssfCellStyle);
//report部分
String [][] contentTitle={{ExportExcelUtil.isBlank(map.get("orgName")),ExportExcelUtil.isBlank(map.get("openBank")),ExportExcelUtil.isBlank(map.get("periodName"))}
,{ExportExcelUtil.isBlank(map.get("accountNO")),ExportExcelUtil.isBlank(map.get("name")),ExportExcelUtil.isBlank(map.get("currencyNames"))},
{ExportExcelUtil.isBlank(map.get("subjectCode")),ExportExcelUtil.isBlank(map.get("subjectName")),ExportExcelUtil.isBlank(map.get("unitName"))}};
//3-5
for(int i = 0;i < 3;i++){
xssfRow= sheet.createRow(i+2);
xssfRow.setHeight((short) 400);
for(int j = 0;j < 8;j++){
if(j % 3 == 0){
xssfCell=ExportExcelUtil.setBorderColor(j,xssfRow,workbook,true);
xssfCell.setCellValue(title[i][j/3]);
}else{
//不设置颜色
xssfCell=ExportExcelUtil.setBorderColor(j,xssfRow,workbook,false);
xssfCell.setCellValue(contentTitle[i][j/3]);
}
}
}
//6-9,创建单元。设置颜色跟高度
for(int i = 0;i < 4;i++){
xssfRow= sheet.createRow(i+5);
xssfRow.setHeight((short) 400);
for(int j = 0;j < 4;j++){
//只处理1、3固定行
if(j % 2 ==0 ) {
//设置固定值(j+j)(j+j+1)0，2
xssfCell=ExportExcelUtil.setBorderColor(j + j,xssfRow,workbook,true);
xssfCell.setCellValue(content[i][j/2]);
xssfCell = ExportExcelUtil.setBorderColor(j + j + 1, xssfRow, workbook, true);
}else{
if(i % 3 ==0 ){//1,4
xssfCell=ExportExcelUtil.setBorderColorRight(j+j,xssfRow,workbook,true);
if(contentDate[i][j/3] == null){
xssfCell.setCellValue(0);
}else{
xssfCell.setCellValue(String.valueOf(contentDate[i][j/3]));
}
xssfCell=ExportExcelUtil.setBorderColorRight(j+j+1,xssfRow,workbook,true);
}else {//
xssfCell = ExportExcelUtil.setBorderColorRight(j + j, xssfRow, workbook, false);
if(contentDate[i][j/3] == null){
xssfCell.setCellValue(0);
}else{
xssfCell.setCellValue(String.valueOf(contentDate[i][j/3]));
}
xssfCell = ExportExcelUtil.setBorderColorRight(j + j + 1, xssfRow, workbook, false);
}
}
}
}
//设置10
xssfRow= sheet.createRow(9);
xssfRow.setHeight((short) 400);
for(int i = 0;i < 8;i++){
xssfCell=ExportExcelUtil.setBorderColor(i,xssfRow,workbook,true);
if(i == 0){
xssfCell.setCellValue("对账结果");
}
if(i == 2){
xssfCell.setCellValue(report.getResult() == 0?"状态":report.getResult() == 1?
"初始":report.getResult() == 2?"调节相符":"调节不符");
}
}
String [] autoArr=new String[3];
if(auditInfoDto != null){
autoArr[0]=auditInfoDto.getCreateByName();
autoArr[1]=auditInfoDto.getCreateDate().toString();
autoArr[2]=auditInfoDto.getLastAuditByName();
}
//设置11
xssfRow= sheet.createRow(10);
xssfRow.setHeight((short) 400);
for(int j = 0;j < 8;j++){
if(j % 3 == 0){
xssfCell=ExportExcelUtil.setBorderColor(j,xssfRow,workbook,true);
xssfCell.setCellValue(table[j/3]);
}else{
//不设置颜色
xssfCell= ExportExcelUtil.setBorderColor(j,xssfRow,workbook,false);
if(auditInfoDto !=null){
xssfCell.setCellValue(autoArr[j/3]);
}
}
}
//设置13
xssfCell= sheet.createRow(12).createCell(0);
XSSFCellStyle xssfCellStyle1=workbook.createCellStyle();
//设置字体
XSSFFont font1 = workbook.createFont();
font1.setFontName("宋体");
font1.setBold(true);
font1.setFontHeightInPoints((short) 11);//设置字体大小
xssfCellStyle1.setFont(font1);
xssfCell.setCellStyle(xssfCellStyle1);
xssfCell.setCellValue("附：未达账明细清单");
//15开始动态加载表格
ExportExcelUtil.getDateTemp(sheet,workbook, 14, ExportExcelUtil.BankPayUnEnterprises,reportHelper,reportHelper.getBankPayUnEnterprisesAmount());
//获取文件的最后一行
int lastRow=sheet.getLastRowNum();
//15开始动态加载表格
ExportExcelUtil.getDateTemp(sheet,workbook, lastRow+2, ExportExcelUtil.BankReceiveUnEnterprises,reportHelper,reportHelper.getBankReceiveUnEnterprisesAmount());
//获取文件的最后一行
int lastRow2=sheet.getLastRowNum();
//15开始动态加载表格
ExportExcelUtil.getDateTemp(sheet,workbook, lastRow2+2, ExportExcelUtil.EnterprisePayUnBank,reportHelper,reportHelper.getEnterprisePayUnBankAmount());
//获取文件的最后一行
int lastRo3=sheet.getLastRowNum();
//15开始动态加载表格
ExportExcelUtil.getDateTemp(sheet,workbook, lastRo3+2, ExportExcelUtil.EnterpriseReceiveUnBank,reportHelper,reportHelper.getEnterpriseReceiveUnBankAmount());
//输出单元格
response.reset();
response.setHeader("Content-disposition","导出余额调节表V1.1");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream outputStream=response.getOutputStream();
workbook.write(outputStream);
outputStream.close();
}catch (Exception e){
e.printStackTrace();
}
}
public class ExportExcelUtil {
private static final String [] arr={"序号","日期","收入金额","支出金额","用途",
"凭证号/流水号","对方户名","备注"};
public static final String BankPayUnEnterprises="银收企未收";
public static final String BankReceiveUnEnterprises="银付企未付";
public static final String EnterprisePayUnBank="企收银未收";
public static final String EnterpriseReceiveUnBank="企付银未付";
//设置合并单元格的边框
public static void setBorderForMergeCell(CellRangeAddress cellRangeTitle, Sheet sheet,XSSFWorkbook workbook){
RegionUtil.setBorderBottom(1, cellRangeTitle, sheet,workbook);
RegionUtil.setBorderLeft(1, cellRangeTitle, sheet,workbook);
RegionUtil.setBorderRight(1, cellRangeTitle, sheet,workbook);
RegionUtil.setBorderTop(1, cellRangeTitle, sheet,workbook);
sheet.addMergedRegion(cellRangeTitle);
}
public static XSSFCell setBorderColor(int cell, XSSFRow row, XSSFWorkbook workbook,Boolean flag){
//设置颜色
XSSFCellStyle xssfCellStyleColor= workbook.createCellStyle();
xssfCellStyleColor.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
xssfCellStyleColor.setBorderBottom(BorderStyle.THIN); //下边框
xssfCellStyleColor.setBorderLeft(BorderStyle.THIN); //左边框
xssfCellStyleColor.setBorderRight(BorderStyle.THIN); //右边框
xssfCellStyleColor.setBorderTop(BorderStyle.THIN); //上边框
XSSFCell xssfCell=row.createCell(cell);
if(flag){
//填充类型默认第一个
xssfCellStyleColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
xssfCellStyleColor.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
}
xssfCell.setCellStyle(xssfCellStyleColor);
return xssfCell;
}
public static XSSFCell setBorderColorRight(int cell, XSSFRow row, XSSFWorkbook workbook,Boolean flag){
//设置颜色
XSSFCellStyle xssfCellStyleColor= workbook.createCellStyle();
xssfCellStyleColor.setAlignment(HorizontalAlignment.RIGHT);
xssfCellStyleColor.setBorderBottom(BorderStyle.THIN); //下边框
xssfCellStyleColor.setBorderLeft(BorderStyle.THIN); //左边框
xssfCellStyleColor.setBorderRight(BorderStyle.THIN); //右边框
xssfCellStyleColor.setBorderTop(BorderStyle.THIN); //上边框
XSSFCell xssfCell=row.createCell(cell);
if(flag){
//填充类型默认第一个
xssfCellStyleColor.setFillPattern(FillPatternType.SOLID_FOREGROUND);
xssfCellStyleColor.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
}
xssfCell.setCellStyle(xssfCellStyleColor);
return xssfCell;
}
//表格模板
public static void getDateTemp(XSSFSheet sheet, XSSFWorkbook workbook, int rowNum, String name, ReportHelper reportHelper, BigDecimal value){
XSSFRow row = sheet.createRow(rowNum);
XSSFCell xssfCell1=row.createCell(0);
XSSFCell xssfCell2=row.createCell(4);
XSSFCell xssfCell3=row.createCell(5);
//合并
CellRangeAddress cellRangeAddress=new CellRangeAddress(rowNum,rowNum,5,7);
sheet.addMergedRegion(cellRangeAddress);
XSSFCellStyle xssfCellStyle=getStyle(workbook);
xssfCell1.setCellStyle(xssfCellStyle);
xssfCell2.setCellStyle(xssfCellStyle);
xssfCell3.setCellStyle(xssfCellStyle);
xssfCell1.setCellValue(name);
xssfCell2.setCellValue("小计");
XSSFCellStyle xcell=workbook.createCellStyle();
xcell.setAlignment(HorizontalAlignment.RIGHT);
xssfCell3.setCellStyle(xcell);
xssfCell3.setCellValue(value.toString());
XSSFRow row2 = sheet.createRow(rowNum+1);
row2.setHeight((short) 500);
XSSFCell xssfCell=null;
for(int i=0;i<8;i++){
xssfCell=ExportExcelUtil.setBorderColor(i,row2,workbook,true);
xssfCell.setCellValue(arr[i]);
}
List<ReconciliationDetailBank> bankPayUnEnterprises=null;
List<ReconciliationDetailEnterprise> enterpriseReceiveUnBank=null;
if(name.equals(BankPayUnEnterprises)){
bankPayUnEnterprises=reportHelper.getBankPayUnEnterprises();
}
if(name.equals(BankReceiveUnEnterprises)){
bankPayUnEnterprises=reportHelper.getBankReceiveUnEnterprises();
}
if(name.equals(EnterprisePayUnBank)){
enterpriseReceiveUnBank= reportHelper.getEnterprisePayUnBank();
}
if(name.equals(EnterpriseReceiveUnBank)){
enterpriseReceiveUnBank=reportHelper.getEnterpriseReceiveUnBank();
}
/*
* TRADE_TIME_
* DEBIT_AMOUNT_
* CREDIT_AMOUNT_
* PURPOSE_
* VOUCHER_NO_
* SERIAL_NO_
* OPP_ACCOUT_NAME_
* REMARK_
* */
if(bankPayUnEnterprises !=null){
for(int i=0;i<bankPayUnEnterprises.size();i++){
XSSFCellStyle cellStyle=workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
XSSFRow row3 = sheet.createRow(rowNum+2+i);
row3.createCell(0).setCellValue(i+1);
row3.createCell(1).setCellValue(bankPayUnEnterprises.get(i).getCertificateDate()!=null?bankPayUnEnterprises.get(i).getCertificateDate():null);
XSSFCell cell2=row3.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(bankPayUnEnterprises.get(i).getDebitAmount()!=null?bankPayUnEnterprises.get(i).getDebitAmount().toString():"0");
XSSFCell cell3=row3.createCell(3);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(bankPayUnEnterprises.get(i).getCreditAmount()!=null?bankPayUnEnterprises.get(i).getCreditAmount().toString():"0");
row3.createCell(4).setCellValue(bankPayUnEnterprises.get(i).getPurpose()!=null?bankPayUnEnterprises.get(i).getPurpose():"");
XSSFCell cell5=row3.createCell(5);
cell5.setCellStyle(cellStyle);
cell5.setCellValue(bankPayUnEnterprises.get(i).getVoucherNo()!=null?bankPayUnEnterprises.get(i).getVoucherNo():""
+"/"
+bankPayUnEnterprises.get(i).getSerialNo()!=null?bankPayUnEnterprises.get(i).getSerialNo():"");
row3.createCell(6).setCellValue(bankPayUnEnterprises.get(i).getOppAccoutName()!=null?bankPayUnEnterprises.get(i).getOppAccoutName():"");
row3.createCell(7).setCellValue(bankPayUnEnterprises.get(i).getRemark()!=null?bankPayUnEnterprises.get(i).getRemark():"");
}
}if(enterpriseReceiveUnBank != null){
for(int i=0;i<enterpriseReceiveUnBank.size();i++){
XSSFCellStyle cellStyle=workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.RIGHT);
XSSFRow row3 = sheet.createRow(rowNum+2+i);
row3.createCell(0).setCellValue(i+1);
row3.createCell(1).setCellValue(enterpriseReceiveUnBank.get(i).getCertificateDate()!=null?enterpriseReceiveUnBank.get(i).getCertificateDate():null);
XSSFCell cell2=row3.createCell(2);
cell2.setCellStyle(cellStyle);
cell2.setCellValue(enterpriseReceiveUnBank.get(i).getDebitAmount()!=null?enterpriseReceiveUnBank.get(i).getDebitAmount().toString():"0");
XSSFCell cell3=row3.createCell(3);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(enterpriseReceiveUnBank.get(i).getCreditAmount()!=null?enterpriseReceiveUnBank.get(i).getCreditAmount().toString():"0");
row3.createCell(4).setCellValue(enterpriseReceiveUnBank.get(i).getPurpose()!=null?enterpriseReceiveUnBank.get(i).getPurpose():"");
XSSFCell cell5=row3.createCell(5);
cell5.setCellStyle(cellStyle);
cell5 .setCellValue(enterpriseReceiveUnBank.get(i).getVoucherNo()!=null?enterpriseReceiveUnBank.get(i).getVoucherNo():""
+"/"
+enterpriseReceiveUnBank.get(i).getSerialNo()!=null?enterpriseReceiveUnBank.get(i).getSerialNo():"");
row3.createCell(6).setCellValue(enterpriseReceiveUnBank.get(i).getOppAccoutName()!=null?enterpriseReceiveUnBank.get(i).getOppAccoutName():"");
row3.createCell(7).setCellValue(enterpriseReceiveUnBank.get(i).getRemark()!=null?enterpriseReceiveUnBank.get(i).getRemark():"");
}
}
}
public static XSSFCellStyle getStyle(XSSFWorkbook workbook){
XSSFCellStyle xssfCellStyle1=workbook.createCellStyle();
//设置字体
XSSFFont font1 = workbook.createFont();
font1.setFontName("宋体");
font1.setBold(true);
font1.setFontHeightInPoints((short) 11);//设置字体大小
xssfCellStyle1.setFont(font1);
return xssfCellStyle1;
}
public static String isBlank(Object str){
if (str == null) {
return null;
} else {
return str.toString();
}
}
}
