前后端excel导出

前端 vue 导出excel
<el-button
@click=“selectFailure”
type=“primary”
size=“small”
icon=“el-icon-download”
>导出所有数据

点击上传

//导出所有数据
selectFailure(Datalist) {
var myDate = new Date();
var mytime = myDate.toLocaleString(); // 获取日期与时间
import("@/vendor/Export2Excel").then((excel) => {
const tHeader = [
“订单号”,
“实际退款金额(以分为单位)”,
“交易金额（以元为单位）”,
“退款原因”,
“状态”,
“本次交易流水号”,
“原交易流水号”,
“订单导入时间”,
];
const filterVal = [
“masterOrderSn”,
“txnAmt”,
“tkAmt”,
“refundMemo”,
“isStatus”,
“reqSeqNo”,
“oriReqSeqNo”,
“createDate”,
];
excel.export_json_to_excel2(
tHeader,
Datalist,
filterVal,
mytime + “全部退款记录”
);
});
},

importfxx(obj) {
let _this = this;
let inputDOM = this.$refs.inputer;
let arr = [];
// 通过DOM取文件数据
this.file = event.currentTarget.files[0];
var rABS = false; //是否将文件读取为二进制字符串
var f = this.file;

  var reader = new FileReader();
  FileReader.prototype.readAsBinaryString = function (f) {
    var binary = "";
    var rABS = false; //是否将文件读取为二进制字符串
    var pt = this;
    var wb; //读取完成的数据
    var outdata;
    var reader = new FileReader();
    reader.onload = function (e) {
      var bytes = new Uint8Array(reader.result);
      var length = bytes.byteLength;
      for (var i = 0; i < length; i++) {
        binary += String.fromCharCode(bytes[i]);
      }
      //此处引入，用于解析excel
      var XLSX = require("xlsx");
      if (rABS) {
        wb = XLSX.read(btoa(fixdata(binary)), {
          //手动转化
          type: "base64",
        });
      } else {
        wb = XLSX.read(binary, {
          type: "binary",
        });
      }
      outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]]);
      //outdata就是读取的数据（不包含标题行即表头，表头会作为对象的下标）
      //此处可对数据进行处理

      outdata.map((v) => {
        let obj = {};
        obj.masterOrderSn = v["订单号"];
        obj.tkAmt = v["退款金额"];
        obj.refundMemo = v["退款原因"];
        obj.oriReqSeqNo = v["原交易流水号"];
        arr.push(obj);
      });

      realTimeWithholding(arr).then((response) => {
        if (response.data.soaStateBean.code == 1) {
          if (
            response.data.data !== undefined &&
            response.data.data !== null
          ) {
            _this.fetchData(); // 同步数据
            _this.open1("处理成功，请查看最新数据");
          }
        } else {
          _this.open1(response.data.soaStateBean.message);
        }
      });
      return arr;
    };
    reader.readAsArrayBuffer(f);
  };
  if (rABS) {
    reader.readAsArrayBuffer(f);
  } else {
    reader.readAsBinaryString(f);
  }
},
//上传文件时处理方法
handleChange(file, fileList) {
  this.fileTemp = file.raw;
  if (this.fileTemp) {
    if (
      this.fileTemp.type ==
        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" ||
      this.fileTemp.type == "application/vnd.ms-excel"
    ) {
      this.importfxx(this.fileTemp);
    } else {
      this.$message({
        type: "warning",
        message: "附件格式错误，请删除后重新上传！",
      });
    }
  } else {
    this.$message({
      type: "warning",
      message: "请上传附件！",
    });
  }
},

// 下载退款模板
download() {
import("@/vendor/Export2Excel").then((excel) => {
const tHeader = [“订单号”, “退款金额”, “退款原因”, “原交易流水号”];
const filterVal = [
“masterOrderSn”,
“tkAmt”,
“refundMemo”,
“oriReqSeqNo”,
];
const list = [
{
masterOrderSn: “必填”,
tkAmt: “必填，以rmb/元为单位”,
refundMemo: “选填，可以为空”,
oriReqSeqNo: “必填（m2p请求号）”,
},
{
masterOrderSn: “12321312”,
tkAmt: “39.32”,
refundMemo: “退款模板”,
oriReqSeqNo: “11123123”,
},
];
excel.export_json_to_excel2(tHeader, list, filterVal, “退款模板”);
});
},
//导出所有数据
expppp(Datalist) {
var myDate = new Date();
var mytime = myDate.toLocaleString(); // 获取日期与时间
import("@/vendor/Export2Excel").then((excel) => {
const tHeader = [
“订单号”,
“实际退款金额(以分为单位)”,
“交易金额（以元为单位）”,
“退款原因”,
“状态”,
“本次交易流水号”,
“原交易流水号”,
“订单导入时间”,
];
const filterVal = [
“masterOrderSn”,
“txnAmt”,
“tkAmt”,
“refundMemo”,
“isStatus”,
“reqSeqNo”,
“oriReqSeqNo”,
“createDate”,
];
excel.export_json_to_excel2(
tHeader,
Datalist,
filterVal,
mytime + “全部退款记录”
);
});
},

需要 有一个 配置文件
E:\Users\P1706123554\Documents\项目地址\src\vendor\Export2Excel.js

```javascript
/* eslint-disable */
require('script-loader!file-saver');
import XLSX from 'xlsx'

function generateArray(table) {
  var out = [];
  var rows = table.querySelectorAll('tr');
  var ranges = [];
  for (var R = 0; R < rows.length; ++R) {
    var outRow = [];
    var row = rows[R];
    var columns = row.querySelectorAll('td');
    for (var C = 0; C < columns.length; ++C) {
      var cell = columns[C];
      var colspan = cell.getAttribute('colspan');
      var rowspan = cell.getAttribute('rowspan');
      var cellValue = cell.innerText;
      if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;

      //Skip ranges
      ranges.forEach(function (range) {
        if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
          for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
        }
      });

      //Handle Row Span
      if (rowspan || colspan) {
        rowspan = rowspan || 1;
        colspan = colspan || 1;
        ranges.push({
          s: {
            r: R,
            c: outRow.length
          },
          e: {
            r: R + rowspan - 1,
            c: outRow.length + colspan - 1
          }
        });
      };

      //Handle Value
      outRow.push(cellValue !== "" ? cellValue : null);

      //Handle Colspan
      if (colspan)
        for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
    }
    out.push(outRow);
  }
  return [out, ranges];
};

function datenum(v, date1904) {
  if (date1904) v += 1462;
  var epoch = Date.parse(v);
  return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}

function sheet_from_array_of_arrays(data, opts) {
  var ws = {};
  var range = {
    s: {
      c: 10000000,
      r: 10000000
    },
    e: {
      c: 0,
      r: 0
    }
  };
  for (var R = 0; R != data.length; ++R) {
    for (var C = 0; C != data[R].length; ++C) {
      if (range.s.r > R) range.s.r = R;
      if (range.s.c > C) range.s.c = C;
      if (range.e.r < R) range.e.r = R;
      if (range.e.c < C) range.e.c = C;
      var cell = {
        v: data[R][C]
      };
      if (cell.v == null) continue;
      var cell_ref = XLSX.utils.encode_cell({
        c: C,
        r: R
      });

      if (typeof cell.v === 'number') cell.t = 'n';
      else if (typeof cell.v === 'boolean') cell.t = 'b';
      else if (cell.v instanceof Date) {
        cell.t = 'n';
        cell.z = XLSX.SSF._table[14];
        cell.v = datenum(cell.v);
      } else cell.t = 's';

      ws[cell_ref] = cell;
    }
  }
  if (range.s.c < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
  return ws;
}

function Workbook() {
  if (!(this instanceof Workbook)) return new Workbook();
  this.SheetNames = [];
  this.Sheets = {};
}

function s2ab(s) {
  var buf = new ArrayBuffer(s.length);
  var view = new Uint8Array(buf);
  for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF;
  return buf;
}

export function export_table_to_excel(id) {
  var theTable = document.getElementById(id);
  var oo = generateArray(theTable);
  var ranges = oo[1];

  /* original data */
  var data = oo[0];
  var ws_name = "SheetJS";

  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  /* add ranges to worksheet */
  // ws['!cols'] = ['apple', 'banan'];
  ws['!merges'] = ranges;

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: 'xlsx',
    bookSST: false,
    type: 'binary'
  });

  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), "test.xlsx")
}

export function export_json_to_excel({
  header,
  data,
  filename,
  autoWidth = true,
  bookType=  'xlsx'
} = {}) {
  /* original data */
  filename = filename || 'excel-list'
  data = [...data]
  data.unshift(header);
  var ws_name = "SheetJS";
  var wb = new Workbook(),
    ws = sheet_from_array_of_arrays(data);

  if (autoWidth) {
    /*设置worksheet每列的最大宽度*/
    const colWidth = data.map(row => row.map(val => {
      /*先判断是否为null/undefined*/
      if (val == null) {
        return {
          'wch': 10
        };
      }
      /*再判断是否为中文*/
      else if (val.toString().charCodeAt(0) > 255) {
        return {
          'wch': val.toString().length * 2
        };
      } else {
        return {
          'wch': val.toString().length
        };
      }
    }))
    /*以第一行为初始值*/
    let result = colWidth[0];
    for (let i = 1; i < colWidth.length; i++) {
      for (let j = 0; j < colWidth[i].length; j++) {
        if (result[j]['wch'] < colWidth[i][j]['wch']) {
          result[j]['wch'] = colWidth[i][j]['wch'];
        }
      }
    }
    ws['!cols'] = result;
  }

  /* add worksheet to workbook */
  wb.SheetNames.push(ws_name);
  wb.Sheets[ws_name] = ws;

  var wbout = XLSX.write(wb, {
    bookType: bookType,
    bookSST: false,
    type: 'binary'
  });
  saveAs(new Blob([s2ab(wbout)], {
    type: "application/octet-stream"
  }), `${filename}.${bookType}`);
}

function formatJson(jsonSource, jsonFillter) {
  return jsonSource.map(v => jsonFillter.map(j => {
      return v[j]
  }))
}

export function export_json_to_excel2(th, jsonSource, jsonFillter, name) {
  const data = formatJson(jsonSource, jsonFillter)
  export_json_to_excel({header: th, data, filename: name})
}

前端vue 上传excel 导出excel完成

后端java 导出excel
public static HSSFWorkbook getFinRepCashConsumeNewExcel(List<FinRepCashConsumeNew> list, String sheetName,
			boolean isSecond, Map<String, YmsMemberTrends> memberMap) {
		HSSFWorkbook wb;

		// 当不是特殊情况的时候
		if (!isSecond) {
			List<String> titleSet = new ArrayList<>();
			titleSet.add("日 期");
			titleSet.add("病历号");
			titleSet.add("姓 名");
			titleSet.add("大类");
			titleSet.add("明细项目");
			titleSet.add("规格");
			titleSet.add("数量");
			titleSet.add("疗程");
			titleSet.add("门市价");
			titleSet.add("原价");
			titleSet.add("折扣");
			titleSet.add("消费金额(元)");
			titleSet.add("身 份");
			titleSet.add("所属省");
			titleSet.add("店编号");
			titleSet.add("店长姓名");
			titleSet.add("级别");
			titleSet.add("咨询师");
			titleSet.add("支付情况");
			titleSet.add("备注");
			titleSet.add("实际收款");
			titleSet.add("状态");
			titleSet.add("业绩");
			titleSet.add("订单主键");
			titleSet.add("是否加盟店推荐");
			titleSet.add("订单类型");
			titleSet.add("备注");
			titleSet.add("SAP会员编号");
			titleSet.add("设计专家");
			titleSet.add("员工亲友");
			titleSet.add("诊所");
			wb = getWorkbook(null, sheetName, titleSet);
			HSSFSheet sheet = wb.getSheet(sheetName);
			HSSFRow row;
			String amount="" ;//储存门市价用于计算
			int i = 0;
			OBJCustom a = new OBJCustom();
			for (FinRepCashConsumeNew bean : list) {
				row = sheet.createRow((int) i + 1);
				row.createCell((short) 0)
						.setCellValue(new SimpleDateFormat("yyyy/MM/dd").format (bean.getDatetimeCheckout()));
				row.createCell((short) 1).setCellValue(bean.getOutpatientRecNo());
				if (bean.getCustom() != null) { // 防止 java.lang.NullPointerException: null
					a = bean.getCustom();
					row.createCell((short) 2).setCellValue(a.getName()); // 报错 null
					String channelName = a.getChannelName();
					row.createCell((short) 12).setCellValue(getChannelName(channelName));
					row.createCell((short) 13).setCellValue(a.getProvince());
					row.createCell((short) 14).setCellValue(a.getMemberCode());
					if ("加盟店".equals(a.getChannelName()) || "加盟店推荐".equals(a.getChannelName())) {
						row.createCell((short) 15).setCellValue(a.getMemberName());
					}
					String memberCode = a.getMemberCode();
					YmsMemberTrends ymsMemberTrends = memberMap.get(memberCode);
					if (ymsMemberTrends != null) {
						row.createCell((short) 16).setCellValue(ymsMemberTrends.memberLevelECN());
					}
					if ("加盟店推荐".equals(a.getChannelName())) {
						row.createCell((short) 24).setCellValue("是");
					}

					if ("员工亲友".equals(a.getChannelName())) {
						row.createCell((short) 29).setCellValue(a.getMemberName());
					}

					row.createCell((short) 27).setCellValue(1500000000 + a.getId() + "");

				} // 判断结束

				row.createCell((short) 3).setCellValue(bean.getBigType());
				row.createCell((short) 4).setCellValue(bean.getProductName());
				if (bean.getProductNumber() != null) {
					if("pay".equals(bean.getStatus())) { // 状态非pay的则为退款订单 不做计算
						row.createCell((short) 6).setCellValue(bean.getProductNumber() + "");
					}else {
						row.createCell((short) 6).setCellValue("-" + bean.getProductNumber());
					}
				
				}
				row.createCell((short) 5).setCellValue(bean.getProductSpec());
				if (bean.getCourseCount() != null) {
					row.createCell((short) 7).setCellValue(bean.getCourseCount() + "");
				}
				
				BigDecimal zk;
				if (bean.getAmount() != null) { // 门市价
				 
					row.createCell((short) 9).setCellValue(bean.getAmount().multiply(new BigDecimal (bean.getProductNumber())).doubleValue());// 原价
					row.createCell((short) 8).setCellValue(bean.getAmount().doubleValue());// 门市价
				   
				  }
				
				if (bean.getAmount() != null && bean.getAmount().compareTo(new BigDecimal(0)) > 0
						&& bean.getNetAmountL() != null && bean.getNetAmountL().compareTo(new  BigDecimal(0)) > 0
						&& bean.getProductNumber() != null && bean.getProductNumber() != 0) {
					// 折扣= 消费金额 % （原价 * 数量) 保留两位小数
					zk = bean.getNetAmountL().divide(
							bean.getAmount().multiply(new BigDecimal(bean.getProductNumber ())), 3,
							BigDecimal.ROUND_HALF_UP);
					row.createCell((short) 10).setCellValue(zk.multiply(new BigDecimal(100)).doubleValue() +  "%"); // 折扣*100 + "%"
														 													
				}

				if (bean.getPayableAmount() == null) { // 消费金额
					row.createCell((short) 11).setCellValue("");
				} else if("pay".equals(bean.getStatus())) { // 状态非pay的则为退款订单 不做计算
					row.createCell((short) 11).setCellValue(((BigDecimal) bean.getPayableAmount()).doubleValue ());
				}else {
					row.createCell((short) 11).setCellValue(((BigDecimal) bean.getPayableAmount().multiply(new  BigDecimal(-1))).doubleValue());
				}

				row.createCell((short) 17).setCellValue(bean.getSalesName());
				row.createCell((short) 18).setCellValue(bean.getChargeTypeName());
				row.createCell((short) 19).setCellValue(bean.getRemark());
				if (bean.getRealAmount() != null) {
					if ("pay".equals(bean.getStatus())) {
						row.createCell((short) 20).setCellValue(((BigDecimal) bean.getRealAmount ()).doubleValue());
						row.createCell((short) 21).setCellValue("成功");
					} else {
						row.createCell((short) 20).setCellValue(
								((BigDecimal) bean.getRealAmount().multiply(new  BigDecimal(-1))).doubleValue());
						row.createCell((short) 21).setCellValue("退款");
					}
				}
				List<FinRepCashConsumeNew> listnew = new ArrayList<>();
				listnew.add(bean);
				row.createCell((short) 22).setCellValue(
						((BigDecimal) FinancialReportsController.getSumPayableAmount(listnew).get ("achieveAmount"))
								.doubleValue());

				row.createCell((short) 23).setCellValue(bean.getChargeBillNoteId());

				String orderType = bean.getOrderType();
				row.createCell((short) 25).setCellValue(getType(orderType));

				row.createCell((short) 26).setCellValue(bean.getNote());

				row.createCell((short) 28).setCellValue(bean.getBeauticianName());

				row.createCell((short) 30).setCellValue(getclinicName(bean.getClinicNo()));
				i++;
			}
			Map<String, List<Integer>> map = heBing(list);
			for (Map.Entry<String, List<Integer>> entry : map.entrySet()) {
				List<Integer> iList = entry.getValue();
				if (iList.size() > 1) {
					int firstRow = iList.get(0);
					int secondRow = iList.get(iList.size() - 1);
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 0, 0));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 1, 1));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 2, 2));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 11, 11));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 12, 12));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 13, 13));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 14, 14));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 15, 15));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 16, 16));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 17, 17));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 18, 18));
					for (int j = firstRow + 1; j < secondRow + 1; j++) {
						row = sheet.getRow(j);
						row.getCell(20).setCellValue(0);
					}
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 19, 19));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 20, 20));
					for (int j = firstRow + 1; j < secondRow + 1; j++) {
						row = sheet.getRow(j);
						row.getCell(22).setCellValue(0);
					}
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 21, 21));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 22, 22));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 23, 23));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 24, 24));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 25, 25));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 26, 26));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 27, 27));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 28, 28));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 29, 29));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 30, 30));
				}
			}
			Map<String, BigDecimal> map2 = FinancialReportsController.getSumPayableAmount(list);
			BigDecimal sumPayableAmount = map2.get("realAmount");
			BigDecimal achieveAmount = map2.get("achieveAmount");
			BigDecimal sumProductNumber = map2.get("ProductNumber");//总数量
			BigDecimal sumAmount = map2.get("numNetAmountL");//总消费金额
			BigDecimal sumOriginalPrice = map2.get("sumOriginalPrice");//总原价
			BigDecimal sumMarketAmount = map2.get("sumMarketAmount");//总市场价
			
			row = sheet.createRow((int) i + 1);
			row.createCell((short) 5).setCellValue("合计");
			row.createCell((short) 6).setCellValue(((BigDecimal) sumProductNumber).doubleValue());
			row.createCell((short) 9).setCellValue(((BigDecimal) sumOriginalPrice).doubleValue());
			row.createCell((short) 8).setCellValue(((BigDecimal) sumMarketAmount).doubleValue());
			row.createCell((short) 11).setCellValue(((BigDecimal) sumAmount).doubleValue());
			row.createCell((short) 20).setCellValue(((BigDecimal) sumPayableAmount).doubleValue());
			row.createCell((short) 22).setCellValue(((BigDecimal) achieveAmount).doubleValue());
		} else {
			List<String> titleSet = new ArrayList<>();
			titleSet.add("日 期");
			titleSet.add("姓 名");
			titleSet.add("大类");
			titleSet.add("明细项目");
			titleSet.add("消费金额");
			titleSet.add("项目原价");
			titleSet.add("折扣");
			titleSet.add("所属省");
			titleSet.add("咨询师");
			titleSet.add("支付情况");
			titleSet.add("实际收款");
			titleSet.add("状态");
			titleSet.add("订单主键");
			titleSet.add("备注");
			wb = getWorkbook(null, sheetName, titleSet);
			HSSFSheet sheet = wb.getSheet(sheetName);
			HSSFRow row;
			int i = 0;
			for (FinRepCashConsumeNew bean : list) {
				row = sheet.createRow((int) i + 1);
				row.createCell((short) 0)
						.setCellValue(new SimpleDateFormat("yyyy/MM/dd").format (bean.getDatetimeCheckout()));
				row.createCell((short) 1).setCellValue(bean.getCustom().getName());
				row.createCell((short) 2).setCellValue(bean.getBigType());
				row.createCell((short) 3).setCellValue(bean.getProductName());
				if (bean.getPayableAmount() == null) {
					row.createCell((short) 4).setCellValue("");
				} else {
					row.createCell((short) 4).setCellValue(((BigDecimal) bean.getPayableAmount()).doubleValue ());
				}

				if (bean.getAmount() != null) {
					row.createCell((short) 5).setCellValue(((BigDecimal) bean.getAmount()).doubleValue());
				} else {
					row.createCell((short) 5).setCellValue("");
				}
				BigDecimal discount = bean.getDiscount();
				// 0<discount<1
				if (discount != null && discount.compareTo(new BigDecimal(0)) == 1
						&& discount.compareTo(new BigDecimal(1)) == -1) {
					BigDecimal setScale = discount.setScale(4, BigDecimal.ROUND_HALF_UP);
					NumberFormat percent = NumberFormat.getPercentInstance();
					percent.setMaximumFractionDigits(2);
					row.createCell((short) 6).setCellValue(percent.format(setScale.doubleValue()));

				} else if (discount != null && discount.compareTo(new BigDecimal(1)) == 0 && bean.getRemark() !=  null
						&& bean.getRemark().contains("买一")) {

					row.createCell((short) 6).setCellValue("25%");

				} else {
					row.createCell((short) 6).setCellValue("无折扣");
				}
				row.createCell((short) 7).setCellValue(bean.getCustom().getProvince());
				row.createCell((short) 8).setCellValue(bean.getSalesName());
				row.createCell((short) 9).setCellValue(bean.getChargeTypeName());
				if (bean.getRealAmount() != null) {
					if ("pay".equals(bean.getStatus())) {
						row.createCell((short) 10).setCellValue(((BigDecimal) bean.getRealAmount ()).doubleValue());
						row.createCell((short) 11).setCellValue("成功");
					} else {
						row.createCell((short) 10).setCellValue(
								((BigDecimal) bean.getRealAmount().multiply(new  BigDecimal(-1))).doubleValue());
						row.createCell((short) 11).setCellValue("退款");
					}
				}
				row.createCell((short) 12).setCellValue(bean.getChargeBillNoteId());
				row.createCell((short) 13).setCellValue(bean.getNote());
				i++;
			}
			Map<String, List<Integer>> map = heBing(list);
			for (Map.Entry<String, List<Integer>> entry : map.entrySet()) {
				List<Integer> iList = entry.getValue();
				if (iList.size() > 1) {
					int firstRow = iList.get(0);
					int secondRow = iList.get(iList.size() - 1);
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 0, 0));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 1, 1));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 7, 7));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 8, 8));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 9, 9));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 10, 10));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 11, 11));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 12, 12));
					sheet.addMergedRegion(new CellRangeAddress(firstRow, secondRow, 13, 13));
				}
			}
			BigDecimal sumPayableAmount = FinancialReportsController.getSumPayableAmount(list).get("realAmount");
			row = sheet.createRow((int) i + 3);
			row.createCell((short) 3).setCellValue("合计");
			row.createCell((short) 10).setCellValue(((BigDecimal) sumPayableAmount).doubleValue());

		}
		return wb;
	}