package cn.com.countrygarden.bi.main.service.investment.impl;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.sql.rowset.serial.SerialException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.mysql.fabric.xmlrpc.base.Array;

import cn.com.countrygarden.bi.main.core.Response;
import cn.com.countrygarden.bi.main.core.Result;
import cn.com.countrygarden.bi.main.dao.investment.InvestProjectInvestmentMapper;
import cn.com.countrygarden.bi.main.dao.investment.InvestProjectProfitMapper;
import cn.com.countrygarden.bi.main.dao.investment.InvestRegionInvestmentMapper;
import cn.com.countrygarden.bi.main.dao.investment.InvestRegionTotalMapper;
import cn.com.countrygarden.bi.main.dto.investment.InvestProjectExcelVO;
import cn.com.countrygarden.bi.main.dto.investment.InvestProjectInvestmentVo;
import cn.com.countrygarden.bi.main.dto.investment.InvestProjectProfitVo;
import cn.com.countrygarden.bi.main.dto.investment.InvestRegionExcelSummaryVo;
import cn.com.countrygarden.bi.main.dto.investment.InvestRegionExcelVo;
import cn.com.countrygarden.bi.main.exception.ServiceException;
import cn.com.countrygarden.bi.main.model.AccessToken;
import cn.com.countrygarden.bi.main.model.investment.InvestProjectAccount;
import cn.com.countrygarden.bi.main.model.investment.InvestProjectInvestment;
import cn.com.countrygarden.bi.main.model.investment.InvestRegionInvestment;
import cn.com.countrygarden.bi.main.model.investment.InvestRegionTotal;
import cn.com.countrygarden.bi.main.model.personnel.Personnel;
import cn.com.countrygarden.bi.main.model.personnel.PersonnelTag;
import cn.com.countrygarden.bi.main.model.sharppathevaluation.PersonnelSharppathevaluation;
import cn.com.countrygarden.bi.main.model.twerp.hm.TbSysOrgan;
import cn.com.countrygarden.bi.main.service.investment.InvestExcelService;
import cn.com.countrygarden.bi.main.service.investment.InvestProjectAccountService;
import cn.com.countrygarden.bi.main.service.investment.InvestProjectInvestmentService;
import cn.com.countrygarden.bi.main.service.investment.InvestRegionInvestmentService;
import cn.com.countrygarden.bi.main.service.investment.InvestRegionTotalService;
import cn.com.countrygarden.bi.main.service.twerp.hm.TbSysOrganService;
import tk.mybatis.mapper.entity.Condition;
import cn.com.countrygarden.bi.main.util.ExcelCommon;

@Component
@Transactional
public class InvestExcelServiceImpl implements InvestExcelService {
	Logger logger = LoggerFactory.getLogger(getClass());

	@Resource
	private InvestProjectAccountService investProjectAccountService;
	@Resource
	private InvestRegionInvestmentMapper investRegionInvestmentMapper;
	@Resource
	private InvestProjectProfitMapper investProjectProfitMapper;
	@Resource
	private InvestRegionTotalMapper investRegionTotalMapper;

	@Resource
	private InvestProjectInvestmentMapper investProjectInvestmentMapper;
	@Resource
	private TbSysOrganService tbSysOrganService;
	@Resource
	private ExcelCommon excelCommonUtils;
	@Resource
	private InvestProjectInvestmentService investProjectInvestmentService;

	@Resource
	private InvestRegionInvestmentService investRegionInvestmentService;
	@Resource
	private InvestRegionTotalService investRegionTotalService;

	public Result importInvest(HttpServletResponse response, AccessToken accessToken, String regionId)
			throws Exception {

		// excel模板路径
		// String path="d:\\第三版同心共享价值明细表.xlsx";
		String path = this.getClass().getClassLoader().getResource("/").toURI().getPath() + "/excel/同心共享价值明细表.xlsx";
		// String path="d:\\同心共享价值明细表.xlsx";
		File fi = new File(path);
		InputStream in = new FileInputStream(fi);
		// 读取excel模板
		XSSFWorkbook wb = new XSSFWorkbook(in);
		// 读取了模板内所有sheet内容
		XSSFSheet sheet1 = wb.getSheetAt(0);
		XSSFSheet sheet2 = wb.getSheetAt(1);

		TbSysOrgan tbSysOrgan = tbSysOrganService.findByUserCode(accessToken.getUserCode());
		String organCode = null;
		if (tbSysOrgan == null) {

			excelCommonUtils.downloadExcel(wb, "同心共享价值明细表.xlsx", response);
			return null;
		}
		if (!tbSysOrgan.getOrganCode().equals("01")) {
			List<String> organCodes = investRegionTotalMapper
					.findAllInvestRegionTotalIds(new String[] { tbSysOrgan.getOrganCode() });
			organCode = organCodes.get(0);
		}
		String[] regionIds = null;
		if (regionId != null && !regionId.equals("")) {
			List<String> investRegionTotalIds = investRegionTotalMapper
					.findAllInvestRegionTotalIds(regionId.split(","));
			if (investRegionTotalIds.size() < 1) {
				excelCommonUtils.downloadExcel(wb, "同心共享价值明细表.xlsx", response);
				return Response.response("没有此区域的数据");
			}
			regionIds = investRegionTotalIds.toArray(new String[investRegionTotalIds.size()]);
		}

		getInvestProjectSheel1(sheet1, organCode, regionIds);
		getInvestRegionSheel2(sheet2, organCode, regionIds);
		excelCommonUtils.downloadExcel(wb, "同心共享价值明细表.xlsx", response);
		return null;
	}

	// 个人excel方法
	public void getInvestProjectSheel1(XSSFSheet sheet, String organCode, String[] regionIds)
			throws FileNotFoundException, IOException {
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		Map<String, Object> mapStr = null;
		List<InvestProjectExcelVO> investExcelList = null;

		// 情况一：investExcelVOList.size()==1;不用做合并行处理
		// 情况二：当前区域项目只有一个，不用做合并处理
		// 情况三：当前投资人为最后一个,做特殊处理，
		// 情况四：当前投资人为最后一个时，需要添加合并行

		List<InvestProjectExcelVO> investExcelVOList = investRegionInvestmentMapper.selectInvestExcelVO(organCode,
				regionIds, null);
		if (investExcelVOList.size() == 0) {
			return;
		}
		int count = 3;
		InvestProjectExcelVO investProjectExcelVO = null;
		int flag = 1;
		int copyRow = 5;
		int iflag = 0;
		int rowCount = 0;
		int mergeRow = 0;
		boolean totalFlag = false;
		String[] regionId;
		// 合计
		InvestProjectProfitVo investProjectProfitVo = null;

		if (investExcelVOList.size() == 1) {

			investProjectProfitVo = investProjectProfitMapper
					.findInvestProjectProfitVo(String.valueOf(investExcelVOList.get(0).getRegionInvestmentId()), null);
			if (investProjectProfitVo == null) {
				investProjectProfitVo = new InvestProjectProfitVo();
			}

			count++;
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			// 常规数据
			this.getinvestExcelVOData(sheet, investExcelVOList.get(0), 0, count, count);
			count++;
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			investProjectProfitVo.setNameFlag("个人小计");
			this.getTotalData(sheet, investExcelVOList.get(0), count, investProjectProfitVo);
			count++;
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			investProjectProfitVo.setNameFlag("区域小计");
			this.getTotalData(sheet, investExcelVOList.get(0), count, investProjectProfitVo);
		} else {
			for (int i = 0; i < investExcelVOList.size(); i++) {

				if (i == 0) {
					// 第一组数据处理
					investExcelList = new ArrayList<InvestProjectExcelVO>();
					investExcelList.add(investExcelVOList.get(i));

				} else {

					if (i != investExcelVOList.size() - 1) {
						// 中间数据处理
						if (investExcelVOList.get(i - 1).getRegionInvestmentId() == investExcelVOList.get(i)
								.getRegionInvestmentId()) {
							investExcelList.add(investExcelVOList.get(i));
						} else {

							mapStr = new LinkedHashMap<String, Object>();
							// 个人小计
							investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(
									String.valueOf(investExcelVOList.get(i - 1).getRegionInvestmentId()), null);
							investProjectProfitVo.setNameFlag("个人小计");
							if (investProjectProfitVo == null) {
								investProjectProfitVo = new InvestProjectProfitVo();
							}
							mapStr.put("investExcelVO", investExcelList);
							mapStr.put("investProjectProfitVo", investProjectProfitVo);
							// 区域个人合计
							if (!investExcelVOList.get(i - 1).getRegionId()
									.equals(investExcelVOList.get(i).getRegionId())) {
								investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(null,
										investExcelVOList.get(i - 1).getRegionId());
								investProjectProfitVo.setNameFlag("区域小计");
								mapStr.put("investRegionInvestmentTotal", investProjectProfitVo);
							}
							mapList.add(mapStr);
							investExcelList = new ArrayList<InvestProjectExcelVO>();
							investExcelList.add(investExcelVOList.get(i));
						}
					} else {
						// 最后一组数据处理
						if (investExcelVOList.get(i - 1).getRegionInvestmentId() == investExcelVOList.get(i)
								.getRegionInvestmentId()) {
							investExcelList.add(investExcelVOList.get(i));

						} else {
							investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(
									String.valueOf(investExcelVOList.get(i - 1).getRegionInvestmentId()), null);
							investProjectProfitVo.setNameFlag("个人小计");
							if (investProjectProfitVo == null) {
								investProjectProfitVo = new InvestProjectProfitVo();
							}
							mapStr = new LinkedHashMap<String, Object>();

							mapStr.put("investExcelVO", investExcelList);
							mapStr.put("investProjectProfitVo", investProjectProfitVo);
							// 区域个人合计
							if (!investExcelVOList.get(i - 1).getRegionId()
									.equals(investExcelVOList.get(i).getRegionId())) {
								investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(null,
										String.valueOf(investExcelVOList.get(i - 1).getRegionId()));
								investProjectProfitVo.setNameFlag("区域小计");
								mapStr.put("investRegionInvestmentTotal", investProjectProfitVo);
							}
							mapList.add(mapStr);
							investExcelList = new ArrayList<InvestProjectExcelVO>();
							investExcelList.add(investExcelVOList.get(i));
						}
						mapStr = new LinkedHashMap<String, Object>();
						// 合计
						investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(
								String.valueOf(investExcelVOList.get(i).getRegionInvestmentId()), null);
						investProjectProfitVo.setNameFlag("个人小计");
						if (investProjectProfitVo == null) {
							investProjectProfitVo = new InvestProjectProfitVo();
						}
						mapStr.put("investExcelVO", investExcelList);
						mapStr.put("investProjectProfitVo", investProjectProfitVo);

						regionId = new String[] { String.valueOf(investExcelVOList.get(i).getRegionId()) };
						investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(null,
								String.valueOf(investExcelVOList.get(i).getRegionId()));
						investProjectProfitVo.setNameFlag("区域小计");
						mapStr.put("investRegionInvestmentTotal", investProjectProfitVo);
						mapList.add(mapStr);
						investExcelList = new ArrayList<InvestProjectExcelVO>();

					}

				}

			}

		}

		for (int i = 0; i < mapList.size(); i++) {
			Map<String, Object> map = mapList.get(i);
			Iterator it = map.keySet().iterator();
			InvestProjectExcelVO investExcel = null;
			while (it.hasNext()) {
				String str = (String) it.next();
				List<InvestProjectExcelVO> investExcelVOList2 = null;
				if (str.equals("investExcelVO")) {
					investExcelVOList2 = (List<InvestProjectExcelVO>) map.get(str);
					mergeRow = investExcelVOList2.size();

					// 当记录数大于1时才需要合并行数
					if (mergeRow > 1) {
						int firstRow = count + 1;

						int lastRow = count + mergeRow;
						rowCount = firstRow;
						for (int nColl = 9; nColl < 11; nColl++) {
							// 参数1：起始行号 参数2：终止行号 参数3： 起始列号参数4：终止列号
							CellRangeAddress cellRangeAddress = new CellRangeAddress(firstRow, lastRow, nColl, nColl);
							// 在sheet里增加合并单元格
							sheet.addMergedRegion(cellRangeAddress);
						}
					} else {
						rowCount = count + 1;
					}
					if (investExcelVOList2 != null && investExcelVOList2.size() > 0) {
						for (InvestProjectExcelVO investExcelVO : investExcelVOList2) {
							investExcel = investExcelVO;
							count++;
							excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
							copyRow++;

							this.getinvestExcelVOData(sheet, investExcelVO, mergeRow, rowCount, count);
						}
					}
				}
				InvestProjectProfitVo investProjectProfitVo2 = null;
				if (str.equals("investProjectProfitVo")) {
					count++;
					excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
					copyRow++;
					investProjectProfitVo2 = (InvestProjectProfitVo) map.get(str);

					this.getTotalData(sheet, investExcel, count, investProjectProfitVo2);
				}

				// 区域个人合计
				if (str.equals("investRegionInvestmentTotal")) {

					count++;
					excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
					copyRow++;
					investProjectProfitVo2 = (InvestProjectProfitVo) map.get(str);
					if (investProjectProfitVo2.getNameFlag().equals("区域小计")) {

						investExcel = investRegionInvestmentMapper.selectInvestExcelVOTotal(organCode,
								new String[] { investExcel.getRegionId() });
					}

					this.getTotalData(sheet, investExcel, count, investProjectProfitVo2);
				}

			}

		}
		// 全部个人汇总
		count++;
		excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
		copyRow++;
		investProjectProfitVo = investProjectProfitMapper.findInvestProjectProfitVo(null, null);
		InvestProjectExcelVO investExcelVO = investRegionInvestmentMapper.selectInvestExcelVOTotal(organCode,
				regionIds);
		investProjectProfitVo.setNameFlag("合计");
		this.getTotalData(sheet, investExcelVO, count, investProjectProfitVo);

	}

	/**
	 * 
	 * @param sheet
	 *            :处理的sheel
	 * @param investExcelVO
	 * @param mergeRow
	 *            ：需要合并的总共行数
	 * @param rowCount
	 *            ：可能做合并的行数从第rowCount行开始写数据
	 * @param count
	 *            ：当前进行的行数
	 */
	public void getinvestExcelVOData(XSSFSheet sheet, InvestProjectExcelVO investExcelVO, int mergeRow, int rowCount,
			int count) {

		DateFormat dFormat = new SimpleDateFormat("yyyy/MM/dd"); // HH表示24小时制；
		XSSFCell cell = null;
		cell = sheet.getRow(count).getCell(0);// 人员名称
		cell.setCellValue(investExcelVO.getUserName());
		cell = sheet.getRow(count).getCell(1);// 区域名称
		cell.setCellValue(investExcelVO.getRegionName());
		cell = sheet.getRow(count).getCell(2);// 项目名称
		cell.setCellValue(investExcelVO.getProjectName());
		cell = sheet.getRow(count).getCell(3);// 资金池（房产发放）
		cell.setCellValue(investExcelVO.getPropertyDistributionCapital().doubleValue());
		cell = sheet.getRow(count).getCell(4);// 单独资金账户（房产发放）
		cell.setCellValue(investExcelVO.getPropertyDistributionCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(5);// 资金池（个人已发放）
		cell.setCellValue(investExcelVO.getIssuedCapital().doubleValue());
		cell = sheet.getRow(count).getCell(6);// 单独资金账户（个人已发放）
		cell.setCellValue(investExcelVO.getIssuedCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(7);// 资金池（个人未发放）
		cell.setCellValue(investExcelVO.getUnissuedCapital().doubleValue());
		cell = sheet.getRow(count).getCell(8);// 单独资金账户（个人未发放）
		cell.setCellValue(investExcelVO.getUnissuedCapitalAccount().doubleValue());

		// 合并行
		cell = sheet.getRow((rowCount)).getCell(9);// 跟投（资金池）
		cell.setCellValue(investExcelVO.getFollowCapital().doubleValue());
		cell = sheet.getRow((rowCount)).getCell(10);// 跟投时间（资金池）
		cell.setCellValue(investExcelVO.getFollowTime() == null ? null : dFormat.format(investExcelVO.getFollowTime()));
		cell = sheet.getRow((count)).getCell(11);// 跟投（独资金账户）
		cell.setCellValue(investExcelVO.getRegionFollowCapitalAccount().doubleValue());
		cell = sheet.getRow((count)).getCell(12);// 跟投时间（独资金账户）
		cell.setCellValue(investExcelVO.getFollowCapitalAccountTime() == null ? null
				: dFormat.format(investExcelVO.getFollowCapitalAccountTime()));
		cell = sheet.getRow(count).getCell(13);// 资金池（区域跟投）
		cell.setCellValue(investExcelVO.getRegionFollowCapital().doubleValue());
		cell = sheet.getRow(count).getCell(14);// 单独资金账户（区域跟投）
		cell.setCellValue(investExcelVO.getRegionFollowCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(15);// 资金池（阶段性现金返还）
		cell.setCellValue(investExcelVO.getCashReturnCapital().doubleValue());
		cell = sheet.getRow(count).getCell(16);// 单独资金账户（阶段性现金返还）
		cell.setCellValue(investExcelVO.getCashReturnCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(17);// 资金池（本金返还）
		cell.setCellValue(investExcelVO.getPrincipalReturnCapital().doubleValue());
		cell = sheet.getRow(count).getCell(18);// 单独资金账户（本金返还）
		cell.setCellValue(investExcelVO.getPrincipalReturnCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(19);// 资金池（利润分配）
		cell.setCellValue(investExcelVO.getProfitDistributionCapital().doubleValue());
		cell = sheet.getRow(count).getCell(20);// 单独资金账户（利润分配）
		cell.setCellValue(investExcelVO.getProfitDistributionCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(21);// 资金池（本金未收回）
		cell.setCellValue(investExcelVO.getPrincipalNotrecoveredCapital().doubleValue());
		cell = sheet.getRow(count).getCell(22);// 单独资金账户（本金未收回）
		cell.setCellValue(investExcelVO.getPrincipalNotrecoveredCapitalAccount().doubleValue());

	}

	public void getTotalData(XSSFSheet sheet, InvestProjectExcelVO investExcelVO, int count,
			InvestProjectProfitVo investProjectProfitVo) {
		// 合计处理
		XSSFCell cell = null;

		cell = sheet.getRow(count).getCell(0);// 合计
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(1);// 合计
		cell.setCellValue(investExcelVO.getRegionName() == null ? "" : investExcelVO.getRegionName());
		cell = sheet.getRow(count).getCell(2);// 合计
		cell.setCellValue(investProjectProfitVo.getNameFlag());
		// cell.setCellValue(investExcelVO.getUserName() + "合计");
		cell = sheet.getRow(count).getCell(3);// 个人-资金池（房产发放）合计
		cell.setCellValue(investProjectProfitVo.getPropertyDistributionCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(4);// 单独资金账户（房产发放）总计
		cell.setCellValue(investProjectProfitVo.getPropertyDistributionCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(5);// 个人-已发放个人利润（累计数）-资金池总计
		cell.setCellValue(investProjectProfitVo.getIssuedCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(6);// 个人-已发放个人利润（累计数）--单独资金账户总计
		cell.setCellValue(investProjectProfitVo.getIssuedCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(7);// 资金池（个人未发放）总计
		cell.setCellValue(investProjectProfitVo.getUnissuedCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(8);// 单独资金账户（个人未发放）总计
		cell.setCellValue(investProjectProfitVo.getUnissuedCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(9);// 跟投（资金池） 总计
		cell.setCellValue(investExcelVO.getFollowCapital().doubleValue());
		cell = sheet.getRow(count).getCell(10);//
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(11);// 跟投（单独资金池） 总计
		cell.setCellValue(investExcelVO.getFollowCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(12);// 跟投（单独资金池） 总计
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(13);// 个人-资金池（区域跟投)总计
		cell.setCellValue(investProjectProfitVo.getRegionFollowCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(14);// 个人-资金池（单独资金账户)总计
		cell.setCellValue(investProjectProfitVo.getRegionFollowCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(15);// 个人-阶段性现金返还-资金池总计
		cell.setCellValue(investProjectProfitVo.getCashReturnCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(16);// 个人-阶段性现金返还-资金池总计
		cell.setCellValue(investProjectProfitVo.getCashReturnCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(17);// 个人-资金池（本金返还）总计
		cell.setCellValue(investProjectProfitVo.getPrincipalReturnCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(18);// 个人-单独资金账户（本金返还）总计
		cell.setCellValue(investProjectProfitVo.getPrincipalReturnCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(19);// 个人-利润分配-资金池总计
		cell.setCellValue(investProjectProfitVo.getProfitDistributionCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(20);// 个人-利润分配-单独资金账户总计
		cell.setCellValue(investProjectProfitVo.getProfitDistributionCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(count).getCell(21);// 个人-资金池（本金未收回）总计
		cell.setCellValue(investProjectProfitVo.getPrincipalNotrecoveredCapitalTotal().doubleValue());
		cell = sheet.getRow(count).getCell(22);// 个人-单独资金账户（本金未收回）合计
		cell.setCellValue(investProjectProfitVo.getPrincipalNotrecoveredCapitalAccountTotal().doubleValue());
		// 资金池（理财收益）
		// 单独资金账户（理财收益）
		cell = sheet.getRow(count).getCell(23);// 个人-资金池（理财收益）
		cell.setCellValue(investExcelVO.getFinancialIncomeCapital().doubleValue());
		cell = sheet.getRow(count).getCell(24);// 个人-单独资金账户（理财收益）
		cell.setCellValue(investExcelVO.getFinancialIncomeCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(25);// 个人-资金池（募集资金余额）
		cell.setCellValue(investExcelVO.getRaisingBalanceCapital().doubleValue());
		cell = sheet.getRow(count).getCell(26);// 个人-资金池（募集资金余额）
		cell.setCellValue(investExcelVO.getRaisingBalanceCapitalAccount().doubleValue());
		cell = sheet.getRow(count).getCell(27);// 个人-资金池（可用资金余额）
		cell.setCellValue(investExcelVO.getAvailableBalanceCapital().doubleValue());
		cell = sheet.getRow(count).getCell(28);// 个人-单独资金账户（可用资金余额）
		cell.setCellValue(investExcelVO.getAvailableBalanceCapitalAccount().doubleValue());
	}

	public void getInvestRegionSheel2(XSSFSheet sheet, String organCode, String[] regionIds)
			throws FileNotFoundException, IOException {
		// 存放重新处理的值
		List<Map<String, Object>> mapList = new ArrayList<Map<String, Object>>();
		// 存在各项目或小计
		Map<String, Object> mapStr = null;
		// 存放项目与小计的临时值
		List<InvestRegionExcelVo> investRegionExcelList = null;
		// 查询所有的项目
		List<InvestRegionExcelVo> investRegionExcelVoList = investRegionTotalMapper.findInvestRegionExcelVo(organCode,
				regionIds);
		if (investRegionExcelVoList.size() == 0) {
			return;
		}
		int count = 2;
		InvestRegionExcelVo investRegionExcelVo = null;
		int copyRow = 5;
		int rowCount = 0;
		int mergeRow = 0;
		boolean end = false;// 是否是最后一行
		// 项目小计
		InvestProjectInvestmentVo investProjectInvestmentVo = null;
		InvestRegionExcelSummaryVo investRegionExcelSummaryVo = null;

		if (investRegionExcelVoList.size() == 1) {
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			count++;
			// excel常规数据填充
			this.getInvestRegionExcelVOData(sheet, investRegionExcelVoList.get(0), 0, count, count);
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			count++;
			// excel项目小计数据填充
			investProjectInvestmentVo = investProjectInvestmentMapper.queryInvestProjectInvestmentVoExcel(
					investRegionExcelVoList.get(0).getProjectName(), investRegionExcelVoList.get(0).getRegionId());
			if (investProjectInvestmentVo == null) {
				investProjectInvestmentVo = new InvestProjectInvestmentVo();
			}
			this.getInvestRegionExcelVOTotalData(sheet, investRegionExcelVoList.get(0), count,
					investProjectInvestmentVo);
			excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
			copyRow++;
			count++;
			// excel合计数据填充
			investRegionExcelSummaryVo = investRegionTotalMapper.findInvestRegionExcelSummaryVo(organCode, null,
					investRegionExcelVoList.get(0).getRegionId(), null);
			investRegionExcelSummaryVo
					.setRegionAmountRaisedSummary(investRegionExcelVoList.get(0).getRegionAmountRaised());
			investRegionExcelSummaryVo
					.setSingleAmountRaisedSummary(investRegionExcelVoList.get(0).getSingleAmountRaised());
			investRegionExcelSummaryVo.setSummaryFlag("区域小计");
			if (investRegionExcelSummaryVo == null) {
				investRegionExcelSummaryVo = new InvestRegionExcelSummaryVo();

			}
			this.getInvestRegionExcelSummaryVo(sheet, count, investRegionExcelSummaryVo, end);
		} else {
			for (int i = 0; i < investRegionExcelVoList.size(); i++) {

				if (i == 0) {
					// 第一组数据处理
					investRegionExcelList = new ArrayList<InvestRegionExcelVo>();
					investRegionExcelList.add(investRegionExcelVoList.get(i));

				} else {

					if (i != investRegionExcelVoList.size() - 1) {
						// 中间数据处理
						if (investRegionExcelVoList.get(i - 1).getRegionId()
								.equals(investRegionExcelVoList.get(i).getRegionId())
								&& investRegionExcelVoList.get(i - 1).getProjectName()
										.equals(investRegionExcelVoList.get(i).getProjectName())) {
							investRegionExcelList.add(investRegionExcelVoList.get(i));
						} else {

							mapStr = new LinkedHashMap<String, Object>();
							// 小计
							investProjectInvestmentVo = investProjectInvestmentMapper
									.queryInvestProjectInvestmentVoExcel(
											investRegionExcelVoList.get(i - 1).getProjectName(),
											investRegionExcelVoList.get(i - 1).getRegionId());
							if (investProjectInvestmentVo == null) {
								investProjectInvestmentVo = new InvestProjectInvestmentVo();
							}

							mapStr.put("InvestRegionExcelVo", investRegionExcelList);
							mapStr.put("InvestProjectInvestmentVo", investProjectInvestmentVo);
							if (!investRegionExcelVoList.get(i - 1).getRegionId()
									.equals(investRegionExcelVoList.get(i).getRegionId())) {
								investRegionExcelSummaryVo = investRegionTotalMapper.findInvestRegionExcelSummaryVo(
										null, null, investRegionExcelVoList.get(i - 1).getRegionId(), null);
								investRegionExcelSummaryVo.setRegionAmountRaisedSummary(
										investRegionExcelVoList.get(i - 1).getRegionAmountRaised());
								investRegionExcelSummaryVo.setSingleAmountRaisedSummary(
										investRegionExcelVoList.get(i - 1).getSingleAmountRaised());
								investRegionExcelSummaryVo.setSummaryFlag("区域小计");
								if (investRegionExcelSummaryVo == null) {
									investRegionExcelSummaryVo = new InvestRegionExcelSummaryVo();
								}
								mapStr.put("InvestRegionExcelSummaryVo", investRegionExcelSummaryVo);

							}
							mapList.add(mapStr);
							investRegionExcelList = new ArrayList<InvestRegionExcelVo>();
							investRegionExcelList.add(investRegionExcelVoList.get(i));
						}
					} else {
						// 最后一组数据处理
						if (investRegionExcelVoList.get(i - 1).getRegionId()
								.equals(investRegionExcelVoList.get(i).getRegionId())
								&& investRegionExcelVoList.get(i - 1).getProjectName()
										.equals(investRegionExcelVoList.get(i).getProjectName())) {
							investRegionExcelList.add(investRegionExcelVoList.get(i));

						} else {
							investProjectInvestmentVo = investProjectInvestmentMapper
									.queryInvestProjectInvestmentVoExcel(
											investRegionExcelVoList.get(i - 1).getProjectName(),
											investRegionExcelVoList.get(i - 1).getRegionId());
							if (investProjectInvestmentVo == null) {
								investProjectInvestmentVo = new InvestProjectInvestmentVo();
							}
							mapStr = new LinkedHashMap<String, Object>();

							mapStr.put("InvestRegionExcelVo", investRegionExcelList);
							mapStr.put("InvestProjectInvestmentVo", investProjectInvestmentVo);
							if (!investRegionExcelVoList.get(i - 1).getRegionId()
									.equals(investRegionExcelVoList.get(i).getRegionId())) {
								investRegionExcelSummaryVo = investRegionTotalMapper.findInvestRegionExcelSummaryVo(
										null, null, investRegionExcelVoList.get(i - 1).getRegionId(), null);
								investRegionExcelSummaryVo.setRegionAmountRaisedSummary(
										investRegionExcelVoList.get(i - 1).getRegionAmountRaised());
								investRegionExcelSummaryVo.setSingleAmountRaisedSummary(
										investRegionExcelVoList.get(i - 1).getSingleAmountRaised());
								investRegionExcelSummaryVo.setSummaryFlag("区域小计");
								if (investRegionExcelSummaryVo == null) {
									investRegionExcelSummaryVo = new InvestRegionExcelSummaryVo();
								}
								mapStr.put("InvestRegionExcelSummaryVo", investRegionExcelSummaryVo);

							}
							mapList.add(mapStr);
							investRegionExcelList = new ArrayList<InvestRegionExcelVo>();
							investRegionExcelList.add(investRegionExcelVoList.get(i));
						}
						mapStr = new LinkedHashMap<String, Object>();
						// 小计
						investProjectInvestmentVo = investProjectInvestmentMapper.queryInvestProjectInvestmentVoExcel(
								investRegionExcelVoList.get(i).getProjectName(),
								investRegionExcelVoList.get(i).getRegionId());
						if (investProjectInvestmentVo == null) {
							investProjectInvestmentVo = new InvestProjectInvestmentVo();
						}
						// 合计
						investRegionExcelSummaryVo = investRegionTotalMapper.findInvestRegionExcelSummaryVo(null, null,
								investRegionExcelVoList.get(i).getRegionId(), null);
						investRegionExcelSummaryVo
								.setRegionAmountRaisedSummary(investRegionExcelVoList.get(i).getRegionAmountRaised());
						investRegionExcelSummaryVo
								.setSingleAmountRaisedSummary(investRegionExcelVoList.get(i).getSingleAmountRaised());
						investRegionExcelSummaryVo.setSummaryFlag("区域小计");
						if (investRegionExcelSummaryVo == null) {
							investRegionExcelSummaryVo = new InvestRegionExcelSummaryVo();
						}
						mapStr = new LinkedHashMap<String, Object>();
						mapStr.put("InvestRegionExcelVo", investRegionExcelList);
						mapStr.put("InvestProjectInvestmentVo", investProjectInvestmentVo);
						mapStr.put("InvestRegionExcelSummaryVo", investRegionExcelSummaryVo);
						mapList.add(mapStr);
						investRegionExcelList = new ArrayList<InvestRegionExcelVo>();

					}

				}

			}

		}

		String regionId = null;

		for (int i = 0; i < mapList.size(); i++) {
			boolean rowFlag = false;
			Map<String, Object> map = mapList.get(i);
			Iterator it = map.keySet().iterator();
			InvestRegionExcelVo investRegionExcelVo1 = null;
			while (it.hasNext()) {
				String str = (String) it.next();
				// 存放值给合计使用

				if (str.equals("InvestRegionExcelVo")) {

					List<InvestRegionExcelVo> investRegionExcelVoList2 = (List<InvestRegionExcelVo>) map.get(str);
					if (regionId == null) {
						regionId = investRegionExcelVoList2.get(0).getRegionId();
						rowFlag = true;
					} else {
						if (!regionId.equals(investRegionExcelVoList2.get(0).getRegionId())) {
							rowFlag = true;
							regionId = investRegionExcelVoList2.get(0).getRegionId();
						}
					}

					if (rowFlag) {

						Condition condition = new Condition(InvestProjectInvestment.class);
						condition.createCriteria().andEqualTo("regionId",
								investRegionExcelVoList2.get(0).getRegionId());
						mergeRow = investProjectInvestmentMapper.selectCountByCondition(condition)
								+ investProjectInvestmentMapper
										.selectCountProject(investRegionExcelVoList2.get(0).getRegionId());

						// 当记录数大于1时才需要合并行数
						if (mergeRow > 1) {
							CellRangeAddress cellRangeAddress = null;
							int firstRow = count + 1;

							int lastRow = count + mergeRow;
							rowCount = firstRow;
							// 合并第一列的区域名称
							// 参数1：起始行号 参数2：终止行号 参数3： 起始列号参数4：终止列号
							cellRangeAddress = new CellRangeAddress(firstRow, lastRow + 1, 0, 0);
							// 在sheet里增加合并单元格

							sheet.addMergedRegion(cellRangeAddress);

							// 合并第12-14列的区域金额及其他
							for (int nColl = 13; nColl < 15; nColl++) {
								// 参数1：起始行号 参数2：终止行号 参数3： 起始列号参数4：终止列号
								cellRangeAddress = new CellRangeAddress(firstRow, lastRow, nColl, nColl);
								// 在sheet里增加合并单元格

								sheet.addMergedRegion(cellRangeAddress);
								// }

							}

							mergeRow = 0;
						} else {
							rowCount = count + 1;

						}

					}

					for (InvestRegionExcelVo investRegionExcel : investRegionExcelVoList2) {
						investRegionExcelVo1 = investRegionExcel;
						count++;
						excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
						copyRow++;

						this.getInvestRegionExcelVOData(sheet, investRegionExcel, mergeRow, rowCount, count);

					}

				}
				if (str.equals("InvestProjectInvestmentVo")) {
					count++;
					excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
					copyRow++;
					InvestProjectInvestmentVo investProjectInvestment = (InvestProjectInvestmentVo) map.get(str);
					this.getInvestRegionExcelVOTotalData(sheet, investRegionExcelVo1, count, investProjectInvestment);
				}
				if (str.equals("InvestRegionExcelSummaryVo")) {
					count++;
					excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
					copyRow++;
					investRegionExcelSummaryVo = (InvestRegionExcelSummaryVo) map.get(str);
					this.getInvestRegionExcelSummaryVo(sheet, count, investRegionExcelSummaryVo, end);
				}
			}

		}
		// 全部区域合计
		count++;
		excelCommonUtils.copyRows(sheet, 4, 4, copyRow);
		copyRow++;
		end = true;
		InvestRegionTotal investRegionTotal = investRegionTotalMapper.findAllInvestRegionToTotal(organCode, regionIds);
		if (investRegionTotal == null) {
			investRegionTotal = new InvestRegionTotal();
		}
		investRegionExcelSummaryVo = investRegionTotalMapper.findInvestRegionExcelSummaryVo(organCode, null, null,
				regionIds);
		investRegionExcelSummaryVo.setRegionAmountRaisedSummary(investRegionTotal.getRegionAmountRaised());
		investRegionExcelSummaryVo.setSingleAmountRaisedSummary(investRegionTotal.getSingleAmountRaised());
		investRegionExcelSummaryVo.setSummaryFlag("合计");
		this.getInvestRegionExcelSummaryVo(sheet, count, investRegionExcelSummaryVo, end);
	}

	// 区域excel的常规数据
	/**
	 * 
	 * @param sheet
	 *            :处理的sheel
	 * @param investExcelVO
	 * @param mergeRow
	 *            ：需要合并的总共行数：当>1时才需要合并
	 * @param rowCount
	 *            ：可能做合并的行数从第rowCount行开始写数据
	 * @param count
	 *            ：当前进行的行数
	 */
	public void getInvestRegionExcelVOData(XSSFSheet sheet, InvestRegionExcelVo investRegionExcelVo, int mergeRow,
			int rowCount, int count) {
		DateFormat dFormat = new SimpleDateFormat("yyyy/MM/dd"); // HH表示24小时制；
		XSSFCell cell = null;
		int num = 1;
		cell = sheet.getRow(rowCount).getCell(0);// 区域名称
		cell.setCellValue(investRegionExcelVo.getRegionName());
		cell = sheet.getRow(count).getCell(num++);// 项目名称
		cell.setCellValue(investRegionExcelVo.getProjectName());

		cell = sheet.getRow(count).getCell(num++);// 资金池（项目收益）
		cell.setCellValue(investRegionExcelVo.getIncomeCapital().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 单独资金账户（项目收益）
		cell.setCellValue(investRegionExcelVo.getIncomeCapitalAccount().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 资金池（已发放项目收益）
		cell.setCellValue(investRegionExcelVo.getIssuedIncomeCapital().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 资金池时间（已发放项目收益）
		cell.setCellValue(investRegionExcelVo.getIssuedIncomeCapitalTime() == null ? null
				: dFormat.format(investRegionExcelVo.getIssuedIncomeCapitalTime()));
		cell = sheet.getRow(count).getCell(num++);// 单独资金账户（已发放项目收益）
		cell.setCellValue(investRegionExcelVo.getIssuedIncomeCapitalAccount().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）
		cell.setCellValue(investRegionExcelVo.getIssuedIncomeCapitalAccountTime() == null ? null
				: dFormat.format(investRegionExcelVo.getIssuedIncomeCapitalAccountTime()));

		cell = sheet.getRow(count).getCell(num++);// 项目总跟投金额（计算：项目跟投情况）
		cell.setCellValue(investRegionExcelVo.getProjectFollowTotalCapital().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 资金池跟投金额（项目跟投情况）
		cell.setCellValue(investRegionExcelVo.getProjectFollowCapital().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 资金池跟投金额時間（项目跟投情况）
		cell.setCellValue(investRegionExcelVo.getProjectFollowCapitalTime() == null ? null
				: dFormat.format(investRegionExcelVo.getProjectFollowCapitalTime()));
		cell = sheet.getRow(count).getCell(num++);// 独资金跟投金额（项目跟投情况）
		cell.setCellValue(investRegionExcelVo.getProjectFollowSingleCapital().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 时间（项目跟投情况）
		cell.setCellValue(investRegionExcelVo.getProjectFollowSingleCapitalTime() == null ? null
				: dFormat.format(investRegionExcelVo.getProjectFollowSingleCapitalTime()));
		// 中间有两行为合并行

		cell = sheet.getRow(rowCount).getCell(num++);// 区域总募集金额）
		cell.setCellValue(investRegionExcelVo.getRegionAmountRaised().doubleValue());

		cell = sheet.getRow(rowCount).getCell(num++);// 独资金总募资金额
		cell.setCellValue(investRegionExcelVo.getSingleAmountRaised().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 阶段性返还金额（阶段性返还）
		cell.setCellValue(investRegionExcelVo.getCashReturnMoney().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 资金池（阶段性返还）
		cell.setCellValue(investRegionExcelVo.getCashReturnCapital().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（阶段性返还）
		cell.setCellValue(investRegionExcelVo.getCashReturnCapitalAccount().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 时间（阶段性返还）
		cell.setCellValue(investRegionExcelVo.getCashReturnTime() == null ? null
				: dFormat.format(investRegionExcelVo.getCashReturnTime()));

		cell = sheet.getRow((count)).getCell(num++);// 本金返还金额（本金返还）
		cell.setCellValue(investRegionExcelVo.getPrincipalReturnMoney().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 资金池（本金返还）
		cell.setCellValue(investRegionExcelVo.getPrincipalReturnCapital().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（本金返还）
		cell.setCellValue(investRegionExcelVo.getPrincipalReturnCapitalAccount().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（本金返还）
		cell.setCellValue(investRegionExcelVo.getPrincipalReturnTime() == null ? null
				: dFormat.format(investRegionExcelVo.getPrincipalReturnTime()));

		cell = sheet.getRow((count)).getCell(num++);// 资金池（利润分配）
		cell.setCellValue(investRegionExcelVo.getProfitDistributionCapital().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（利润分配）
		cell.setCellValue(investRegionExcelVo.getProfitDistributionCapitalTime() == null ? null
				: dFormat.format(investRegionExcelVo.getProfitDistributionCapitalTime()));

		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（利润分配）
		cell.setCellValue(investRegionExcelVo.getProfitDistributionCapitalAccount().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 时间（利润分配）
		cell.setCellValue(investRegionExcelVo.getProfitDistributionCapitalAccountTime() == null ? null
				: dFormat.format(investRegionExcelVo.getProfitDistributionCapitalAccountTime()));

		cell = sheet.getRow((count)).getCell(num++);// 资金池（本金未收回）
		cell.setCellValue(investRegionExcelVo.getPrincipalNotrecoveredCapital().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（本金未收回）
		cell.setCellValue(investRegionExcelVo.getPrincipalNotrecoveredCapitalAccount().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 资金池（理财收益）
		cell.setCellValue(investRegionExcelVo.getFinancialIncomeCapital() == null ? 0
				: investRegionExcelVo.getFinancialIncomeCapital().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（理财收益）
		cell.setCellValue(investRegionExcelVo.getFinancialIncomeCapitalTime() == null ? null
				: dFormat.format(investRegionExcelVo.getFinancialIncomeCapitalTime()));

		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（理财收益）
		cell.setCellValue(investRegionExcelVo.getFinancialIncomeCapitalAccount().doubleValue());

		cell.setCellValue(investRegionExcelVo.getFinancialIncomeCapitalAccount() == null ? 0
				: investRegionExcelVo.getFinancialIncomeCapitalAccount().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（理财收益）
		cell.setCellValue(investRegionExcelVo.getFinancialIncomeCapitalAccountTime() == null ? null
				: dFormat.format(investRegionExcelVo.getFinancialIncomeCapitalAccountTime()));

	}

	// 区域excel的小计
	public void getInvestRegionExcelVOTotalData(XSSFSheet sheet, InvestRegionExcelVo investRegionExcelVo,
			int totalCount, InvestProjectInvestmentVo investProjectInvestmentVo) {
		DateFormat dFormat = new SimpleDateFormat("yyyy/MM/dd"); // HH表示24小时制；
		XSSFCell cell = null;
		int num = 1;
		cell = sheet.getRow(totalCount).getCell(num++);// 项目小计
		cell.setCellValue((investRegionExcelVo.getProjectName() + "小计"));
		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（项目收益）小计

		cell.setCellValue(investProjectInvestmentVo.getIncomeCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（项目收益）小计
		cell.setCellValue(investProjectInvestmentVo.getIncomeCapitalAccountTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（已发放项目收益）小计
		cell.setCellValue(investProjectInvestmentVo.getIssuedIncomeCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 空格
		cell.setCellValue("");
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户(已发放项目收益)小计
		cell.setCellValue(investProjectInvestmentVo.getIssuedIncomeCapitalAccountTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 空格
		cell.setCellValue("");

		cell = sheet.getRow(totalCount).getCell(num++);// 项目总跟投金额（项目跟投情况）小计
		cell.setCellValue(investProjectInvestmentVo.getProjectFollowTotalCapitalTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 资金池跟投金额（项目跟投情况）小计
		cell.setCellValue(investProjectInvestmentVo.getProjectFollowCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 时间空格
		cell.setCellValue("");

		cell = sheet.getRow(totalCount).getCell(num++);// 独资金跟投金额（项目跟投情况）小计
		cell.setCellValue(investProjectInvestmentVo.getProjectFollowSingleCapitalTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 时间空格
		cell.setCellValue("");

		cell = sheet.getRow(totalCount).getCell(num++);// 区域总募集金额小计
		cell.setCellValue(investRegionExcelVo.getRegionAmountRaised().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 独资金总募资金额小计
		cell.setCellValue(investRegionExcelVo.getSingleAmountRaised().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 阶段性返还金额（阶段性返还）小计
		cell.setCellValue(investProjectInvestmentVo.getCashReturnMoneyTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（阶段性返还）小计
		cell.setCellValue(investProjectInvestmentVo.getCashReturnCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（阶段性返还）小计
		cell.setCellValue(investProjectInvestmentVo.getCashReturnCapitalAccountTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 空格
		cell.setCellValue("");

		cell = sheet.getRow(totalCount).getCell(num++);// 本金返还金额（本金返还）小计
		cell.setCellValue(investProjectInvestmentVo.getPrincipalReturnMoneyTotal().doubleValue());

		cell = sheet.getRow(totalCount).getCell(num++);// 本金返还-资金池小计
		cell.setCellValue(investProjectInvestmentVo.getPrincipalReturnCapitalTotal().doubleValue() * 1.000000000);
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（本金返还）
		cell.setCellValue(
				investProjectInvestmentVo.getPrincipalReturnCapitalAccountTotal().doubleValue() * 1.000000000);

		cell = sheet.getRow(totalCount).getCell(num++);// 空格
		cell.setCellValue("");

		cell = sheet.getRow(totalCount).getCell(num++);// -资金池（利润分配）小计
		cell.setCellValue(investProjectInvestmentVo.getProfitDistributionCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// -时间空格
		cell.setCellValue("");
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（利润分配）小计
		cell.setCellValue(investProjectInvestmentVo.getProfitDistributionCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// -时间空格
		cell.setCellValue("");
		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（本金未收回）小计
		cell.setCellValue(investProjectInvestmentVo.getPrincipalNotrecoveredCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（本金未收回）小计
		cell.setCellValue(investProjectInvestmentVo.getPrincipalNotrecoveredCapitalAccount().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（本金未收回）小计
		cell.setCellValue(investProjectInvestmentVo.getFinancialIncomeCapitalTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 资金池（本金未收回）小计
		cell.setCellValue("");
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（本金未收回）小计
		cell.setCellValue(investProjectInvestmentVo.getFinancialIncomeCapitalAccountTotal().doubleValue());
		cell = sheet.getRow(totalCount).getCell(num++);// 单独资金账户（本金未收回）小计
		cell.setCellValue("");

	}

	/**
	 * 区域excel的合计
	 * 
	 * @param sheet
	 * @param count
	 */
	public void getInvestRegionExcelSummaryVo(XSSFSheet sheet, int count,
			InvestRegionExcelSummaryVo investRegionExcelSummaryVo, boolean end) {
		int num = 1;
		XSSFCell cell = null;
		cell = sheet.getRow(count).getCell(num++);// 0.
		cell.setCellValue(investRegionExcelSummaryVo.getSummaryFlag());

		cell = sheet.getRow(count).getCell(num++);// 资金池（项目收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getIncomeCapitalSummary().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 单独资金账户（项目收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getIncomeCapitalAccountSummary().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 资金池（已发放项目收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getIssuedIncomeCapitalSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）,空格
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(num++);// 单独资金账户（已发放项目收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getIssuedIncomeCapitalAccountSummary().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）,空格
		cell.setCellValue("");

		cell = sheet.getRow(count).getCell(num++);// 项目总跟投金额（计算：项目跟投情况）合计
		cell.setCellValue(investRegionExcelSummaryVo.getProjectFollowTotalCapitalSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 资金池跟投金额（项目跟投情况）合计
		cell.setCellValue(investRegionExcelSummaryVo.getProjectFollowCapitalSummary().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）,空格
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(num++);// 独资金跟投金额（项目跟投情况）合计
		cell.setCellValue(investRegionExcelSummaryVo.getProjectFollowSingleCapitalSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 时间（项目跟投情况）空格
		cell.setCellValue("");
		cell = sheet.getRow(count).getCell(num++);// 区域总募集金额 合计
		cell.setCellValue(investRegionExcelSummaryVo.getRegionAmountRaisedSummary().doubleValue());

		cell = sheet.getRow(count).getCell(num++);// 独资金总募资金额 合计single_amount_raised
		cell.setCellValue(investRegionExcelSummaryVo.getSingleAmountRaisedSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 阶段性返还金额（阶段性返还）合计
		cell.setCellValue(investRegionExcelSummaryVo.getCashReturnMoneySummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 资金池（阶段性返还）合计
		cell.setCellValue(investRegionExcelSummaryVo.getCashReturnCapitalSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（阶段性返还）
		cell.setCellValue(investRegionExcelSummaryVo.getCashReturnCapitalAccountSummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 时间（阶段性返还）
		cell.setCellValue("");

		cell = sheet.getRow((count)).getCell(num++);// 本金返还金额（本金返还）合计
		cell.setCellValue(investRegionExcelSummaryVo.getPrincipalReturnMoneySummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 资金池（本金返还）合计
		cell.setCellValue(investRegionExcelSummaryVo.getPrincipalReturnCapitalSummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（本金返还）合计
		cell.setCellValue(investRegionExcelSummaryVo.getPrincipalReturnCapitalAccountSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（本金返还）
		cell.setCellValue("");

		cell = sheet.getRow((count)).getCell(num++);// 资金池（利润分配）合计
		cell.setCellValue(investRegionExcelSummaryVo.getProfitDistributionCapitalSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）,空格
		cell.setCellValue("");
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（利润分配）合计
		cell.setCellValue(investRegionExcelSummaryVo.getProfitDistributionCapitalAccountSummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 时间（利润分配）
		cell.setCellValue("");

		cell = sheet.getRow((count)).getCell(num++);// 资金池（本金未收回）合计
		cell.setCellValue(investRegionExcelSummaryVo.getPrincipalNotrecoveredCapitalSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（本金未收回）合计
		cell.setCellValue(investRegionExcelSummaryVo.getPrincipalNotrecoveredCapitalAccountSummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 资金池（理财收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getFinancialIncomeCapitalSummary().doubleValue());
		cell = sheet.getRow(count).getCell(num++);// 时间（已发放项目收益）,空格
		cell.setCellValue("");
		cell = sheet.getRow((count)).getCell(num++);// 单独资金账户（理财收益）合计
		cell.setCellValue(investRegionExcelSummaryVo.getFinancialIncomeCapitalAccountSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 时间（理财收益）
		cell.setCellValue("");

		cell = sheet.getRow((count)).getCell(num++);// 区域-资金池（募集资金余额）合计
		cell.setCellValue(investRegionExcelSummaryVo.getRegionaRaisingBalanceCapitalSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 区域-单独资金账户（募集资金余额）合计
		cell.setCellValue(investRegionExcelSummaryVo.getRegionaRaisingBalanceCapitalAccountSummary().doubleValue());

		cell = sheet.getRow((count)).getCell(num++);// 区域-资金池（可用资金余额）合计
		cell.setCellValue(investRegionExcelSummaryVo.getRegionaAailableBalanceCapitalSummary().doubleValue());
		cell = sheet.getRow((count)).getCell(num++);// 区域-单独资金账户（可用资金余额）合计
		cell.setCellValue(investRegionExcelSummaryVo.getRegionaAvailableBalanceCapitalAccountSummary().doubleValue());

	}

	public Result exportExcel(String fileName, MultipartFile multipartFile, AccessToken accessToken) {
		Result result = new Result();
		Workbook xwb = null;
		BufferedInputStream bf = null;
		Sheet sheet = null;

		try {
			bf = new BufferedInputStream(multipartFile.getInputStream());
			if (fileName.endsWith("xls")) { // Excel&nbsp;2003
				xwb = new HSSFWorkbook(bf);
			} else if (fileName.endsWith("xlsx")) { // Excel 2007/2010
				xwb = new XSSFWorkbook(bf);
			}
			sheet = xwb.getSheetAt(1);// 获取Sheet1开始
			readSheet1(sheet, accessToken);
			sheet = xwb.getSheetAt(0);// 获取Sheet0
			readSheet0(sheet, accessToken);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		result.setData("导入成功");
		return result;

	}

	public void readSheet1(Sheet sheet, AccessToken accessToken) {
		Condition condtion = null;
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd");

		List<InvestProjectInvestment> investProjectInvestmentList = new ArrayList<InvestProjectInvestment>();
		InvestProjectInvestment investProjectInvestment = null;
		List<InvestRegionTotal> investRegionTotalList = null;
		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			System.out.println(sheet.getLastRowNum());
			Row row = sheet.getRow(i);
			if (row == null) {

				continue;
			}
			if (!excelCommonUtils.isRowNotEmpty(row)) {

				continue;
			}
			// 怎么判断是否为结束的字段.(是否为合并单元格;

			if (row.getCell(0) == null) {
				throw new ServiceException("第" + (i + 1) + "行，所属区域为空,导入失败。");
			} else if (row.getCell(1) == null) {
				throw new ServiceException("第" + (i + 1) + "行，区域投资名称为空,导入失败。");
			} else if (row.getCell(2) == null) {
				throw new ServiceException("第" + (i + 1) + "行，项目为空,导入失败。");
			} else {

				int num = 1;
				try {
					investProjectInvestment = new InvestProjectInvestment();
					// 1：根据区域+投资名称找到相应的区域id
					XSSFCell cell = null;
					cell = (XSSFCell) row.getCell(0);// 0.
					cell.setCellType(CellType.STRING);
					String regionName = cell.getStringCellValue();

					cell = (XSSFCell) row.getCell(num++);// 0.
					cell.setCellType(CellType.STRING);
					// 1.查询所在区域的id
					String investmentName = cell.getStringCellValue();
					condtion = new Condition(InvestRegionTotal.class);
					condtion.createCriteria().andEqualTo("regionName", regionName);
					condtion.createCriteria().andEqualTo("investmentName", investmentName);
					investRegionTotalList = investRegionTotalMapper.selectByCondition(condtion);
					if (investRegionTotalList.size() > 0) {
						investProjectInvestment.setRegionId(String.valueOf(investRegionTotalList.get(0).getId()));// 区域id
						investProjectInvestment.setRegionName(regionName);
					} else {
						throw new ServiceException("项目sheet页第" + (i + 1) + "行(区域名称+投资名称)系统数据不存在，请系统添加完后再导入");
					}

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setProjectName(cell.getStringCellValue());// 项目名称

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setIncomeCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 资金池（项目收益）
					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setIncomeCapitalAccount(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 单独资金账户（项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setIssuedIncomeCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 资金池（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment
							.setIssuedIncomeCapitalTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 资金池时间（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setIssuedIncomeCapitalAccount(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 单独资金账户（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment.setIssuedIncomeCapitalAccountTime(
							excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 资金池时间（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setProjectFollowCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 资金池跟投金额（项目跟投情况）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment
							.setProjectFollowCapitalTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 资金池时间（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setProjectFollowSingleCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 独资金跟投金额（项目跟投情况）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment.setProjectFollowSingleCapitalTime(
							excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 资金池时间（已发放项目收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setCashReturnMoney(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 阶段性返还金额（阶段性返还）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment
							.setCashReturnTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（阶段性返还）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setPrincipalReturnMoney(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 本金返还金额（本金返还）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment
							.setPrincipalReturnTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（本金返还）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setProfitDistributionCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 资金池（利润分配）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment.setProfitDistributionCapitalTime(
							excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（利润分配

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setProfitDistributionCapitalAccount(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 单独资金账户（利润分配）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment.setProfitDistributionCapitalAccountTime(
							excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（利润分配）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setFinancialIncomeCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 资金池（理财收益）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment
							.setFinancialIncomeCapitalTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（理财收益）

					cell = (XSSFCell) row.getCell(num++);// 2
					cell.setCellType(CellType.STRING);
					investProjectInvestment.setFinancialIncomeCapitalAccount(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));// 单独资金账户（理财收益）

					cell = (XSSFCell) row.getCell(num++);// 2

					investProjectInvestment.setFinancialIncomeCapitalAccountTime(
							excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));// 时间（理财收益）
					investProjectInvestment.setCreateBy(accessToken.getUserCode());
					investProjectInvestment.setCreateTime(new Date());
					investProjectInvestmentService.add(investProjectInvestment, accessToken);
				} catch (ParseException e) {
					throw new ServiceException(
							"项目sheet页第" + (i + 1) + "行" + (num + 1) + "列时间格式错误,请使用yyyy/MM/dd或yyyy-MM-dd格式，如:2018/11/11或2018-11-11");
				}
				

			}

		}

	}

	public void readSheet0(Sheet sheet, AccessToken accessToken) {
		Condition condtion = null;
		InvestRegionInvestment investRegionInvestment = null;
		SimpleDateFormat dateformat = new SimpleDateFormat("yyyy/MM/dd");
		List<InvestRegionInvestment> investRegionInvestmentList = new ArrayList<InvestRegionInvestment>();
		List<InvestProjectAccount> investProjectAccountList = null;
		List<InvestRegionTotal> investRegionTotalList = null;
		List<String> bips = new ArrayList<String>();
		for (int i = 1; i <= sheet.getLastRowNum(); i++) {
			System.out.println(sheet.getLastRowNum());
			Row row = sheet.getRow(i);
			if (row == null) {

				continue;
			}
			if (!excelCommonUtils.isRowNotEmpty(row)) {

				continue;
			}
			// 怎么判断是否为结束的字段.(是否为合并单元格;

			if (row.getCell(0) == null) {
				throw new ServiceException("第" + (i + 1) + "行，区域不能为空,导入失败。");
			} else if (row.getCell(1) == null) {
				throw new ServiceException("第" + (i + 1) + "行，区域投资名称为空,导入失败。");
			} else if (row.getCell(2) == null) {
				throw new ServiceException("第" + (i + 1) + "行，姓名为空,导入失败。");
			} else if (row.getCell(3) == null) {
				throw new ServiceException("第" + (i + 1) + "行，BIP账号为空,导入失败。");
			} else {

				int num = 0;
				try {
					investRegionInvestment = new InvestRegionInvestment();
					// 1：根据区域+投资名称找到相应的区域id
					XSSFCell cell = null;
					cell = (XSSFCell) row.getCell(num++);// 0.
					cell.setCellType(CellType.STRING);
					String regionName = cell.getStringCellValue();

					cell = (XSSFCell) row.getCell(num++);// 0.
					cell.setCellType(CellType.STRING);
					// 1.查询所在区域的id
					String investmentName = cell.getStringCellValue();
					condtion = new Condition(InvestRegionTotal.class);
					condtion.createCriteria().andEqualTo("regionName", regionName);
					condtion.createCriteria().andEqualTo("investmentName", investmentName);
					investRegionTotalList = investRegionTotalMapper.selectByCondition(condtion);

					// 2。读取个人投资信息添加到list中存储
					cell = (XSSFCell) row.getCell(num++);// 0.
					cell.setCellType(CellType.STRING);
					investRegionInvestment.setUserName(cell.getStringCellValue());

					cell = (XSSFCell) row.getCell(num++);// 0.
					cell.setCellType(CellType.STRING);

					investRegionInvestment.setBipUserAccount(cell.getStringCellValue());
					// 判断excel中bip是否重复
					bips.add(investRegionInvestment.getBipUserAccount());
					Set<String> set = new HashSet<String>(bips);
					if (set.size() != bips.size()) {
						throw new ServiceException("个人sheet页第" + (i + 1) + "行bip账号:"
								+ investRegionInvestment.getBipUserAccount() + "在EXCEL数据中重复，请更改后再导入，导入失败");
					}
					// 判断系统中bip是否重复
					Result result = investRegionInvestmentService.validateBIP(investRegionInvestment);

					if (result.getData().equals("bipExist")) {
						throw new ServiceException("个人sheet页第" + (i + 1) + "行bip账号:"
								+ investRegionInvestment.getBipUserAccount() + "在系统已经存在不能重复，导入失败");
					}
					if (investRegionTotalList.size() > 0) {
						investRegionInvestment.setRegionId(String.valueOf(investRegionTotalList.get(0).getId()));
						investRegionInvestment.setRegionName(regionName);
					} else {
						throw new ServiceException("个人sheet页第" + (i + 1) + "行（区域名称+投资名称）系统数据不存在，请系统添加完后再导入");
					}

					cell = (XSSFCell) row.getCell(num++);// 跟投（资金池）
					cell.setCellType(CellType.STRING);
					investRegionInvestment.setFollowCapital(
							cell.getStringCellValue().isEmpty() ? null : new BigDecimal(cell.getStringCellValue()));

					cell = (XSSFCell) row.getCell(num++);// 跟投时间（资金池）
					investRegionInvestment.setCreateBy(accessToken.getUserCode());
					investRegionInvestment.setCreateTime(new Date());
					investRegionInvestment.setFollowTime(excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty() ? null
							: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));

					investRegionInvestmentMapper.insertSelective(investRegionInvestment);

					// 3.读取个人单投项目跟投添加到list中存储

					investProjectAccountList = new ArrayList<InvestProjectAccount>();
					InvestProjectAccount investProjectAccount = null;
					String followCapitalAccount = null;
					String projectName = null;
					Boolean followCapitalAccountTimeFlag = false;

					// 第一组单投处理
					for (int j = 0; j < 5; j++) {
						cell = (XSSFCell) row.getCell(num++);// 单独资金账户跟投本金
						cell.setCellType(CellType.STRING);
						followCapitalAccount = cell.getStringCellValue();
						cell = (XSSFCell) row.getCell(num++);// //处理获得项目id
						cell.setCellType(CellType.STRING);
						projectName = cell.getStringCellValue();

						cell = (XSSFCell) row.getCell(num++);
						followCapitalAccountTimeFlag = excelCommonUtils.getDate(cell, "yyyy/MM/dd").isEmpty();
						// 三列都不为空时添加
						if (!followCapitalAccount.isEmpty() && !projectName.isEmpty()
								&& !followCapitalAccountTimeFlag) {
							investProjectAccount = new InvestProjectAccount();
							investProjectAccount.setInvestRegionInvestmentId(investRegionInvestment.getId());// 关联个人区域投资ID

							investProjectAccount.setFollowCapitalAccount(
									followCapitalAccount.isEmpty() ? null : new BigDecimal(followCapitalAccount));

							Map<String, Object> map = new HashMap<String, Object>();

							map.put("regionId", investRegionTotalList.get(0).getId());
							map.put("projectName", projectName);
							List<InvestProjectInvestment> investProjectInvestmentList = investProjectInvestmentMapper
									.findInvestProjectInvestment(map);
							if (investProjectInvestmentList.size() == 1) {
								investProjectAccount
										.setInvestProjectInvestmentId(investProjectInvestmentList.get(0).getId());
							} else {
								throw new ServiceException("个人sheet页,第" +i+"行，"+ projectName + "项目不存在，导入失败");
							}

							// 单独资金账户跟投时间

							investProjectAccount.setFollowCapitalAccountTime(followCapitalAccountTimeFlag ? null
									: dateformat.parse(excelCommonUtils.getDate(cell, "yyyy/MM/dd")));
							investProjectAccount.setCreateBy(accessToken.getUserCode());
							investProjectAccount.setCreateTime(new Date());
							investProjectAccountList.add(investProjectAccount);
						} else if (followCapitalAccount.isEmpty() && projectName.isEmpty()
								&& followCapitalAccountTimeFlag) {
							// 三列都为空时不处理
						} else {
							//// 三列其中有一个为空时导入失败
							throw new ServiceException("个人sheet页第" + (i + 1) + "行,第" + (num - 2) + "列," + (num - 1)
									+ "列," + "第" + num + "列请同时为空或同时填写,导入失败");
						}
					}
					investProjectAccountService.addinvestProjectAccountList(investRegionInvestment,
							investProjectAccountList);
				} catch (ParseException e) {
					throw new ServiceException(
							"个人sheet页第" + (i + 1) + "行" + (num + 1) + "列时间格式错误,请使用yyyy/MM/dd或yyyy-MM-dd格式，如:2018/11/11或2018-11-11");
				}
				
			}
		}
		// 个人项目收益与区域重新计算
		investRegionTotalService.edit(investRegionTotalList.get(0), accessToken);
	}

}
