using Aspose.Cells;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Maps;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using Kingo.Plugin.BGResultManager.Model;
using Kingo.PluginServiceInterface;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace Kingo.Plugin.BGResultManager.Utility
{
    /// <summary>
    /// 耕地坡度分级面积变化统计表
    /// </summary>
    public class ExportGDPDFJMJBHTJB
    {
        /// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="savePath">保存位置</param>
        /// <param name="baseReportPath">获取年初表位置</param>
        /// <returns>是否成功</returns>
        public bool Export(string savePath, string baseReportPath, IRDBHelper dbHelper)
        {
            try
            {
                DataTable dt = null;
                if (string.IsNullOrWhiteSpace(savePath))
                {
                    MessageHelper.ShowError("存储位置错误请重试!");
                    return false;
                }
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                //FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("*耕地坡度分级面积汇总表.xlsx"), SearchOption.AllDirectories);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    //Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!");
                    //Common.Utility.LogAPI.Debug("耕地坡度分级面积汇总表 不能被 成功 找到\r\n");
                    return false;
                }
                //Common.Utility.LogAPI.Debug("耕地坡度分级面积汇总表 能被 成功 找到\r\n");

                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
                string tempFile = tempPath + "耕地坡度分级面积变化统计表.xlsx";
                string targetFile = savePath;

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":Q" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B6:O6");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:O5");

                Cell level0Cell = null;
                Cell level1Cell = null;
                Cell level2Cell = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level0Name = string.Empty;
                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level0 = string.Empty;
                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level0Cell = dataRange[0, i];
                    level1Cell = dataRange[1, i];
                    level2Cell = dataRange[2, i];

                    if (!string.IsNullOrWhiteSpace(level0Cell.StringValue))
                    {
                        Level0Name = level0Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue;
                    }

                    if (Level0Name == "耕地面积")
                    {
                        Level0 = "耕地面积";
                        Level1 = "耕地面积";
                        Level2 = "耕地面积";
                    }
                    if (Level0Name == "平地")
                    {
                        Level0 = "平地";
                        Level1 = "1";
                        Level2 = "面积";
                    }
                    if (Level0Name == "梯田及坡地面积")
                    {
                        Level0 = "梯田及坡地面积";
                        if (Level1Name == "2°～6°")
                        {
                            Level1 = "2";
                        }
                        if (Level1Name == "6°～15°")
                        {
                            Level1 = "3";
                        }
                        if (Level1Name == "15°～25°")
                        {
                            Level1 = "4";
                        }
                        if (Level1Name == "＞25°")
                        {
                            Level1 = "5";
                        }

                        if (Level2Name == "合计")
                        {
                            Level2 = "合计";
                        }
                        if (Level2Name == "梯田")
                        {
                            Level2 = "TT";
                        }
                        if (Level2Name == "坡地")
                        {
                            Level2 = "PD";
                        }
                    }
                    CellInfo cellInfo = new CellInfo()
                    {
                        Level0Name = Level0,
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "耕地面积" || cell.Level2Name == "合计") continue;

                    if (cell.Level0Name == "平地")
                    {
                        // 增加
                        sql = @"select SUM(BGMJ) from 	(	select BGQDLBM,BGHDLBM,case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from	(	select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from JCTJB_GQ  where XZQTZLX<>'2' and XZQTZLX<>'3')A WHERE (substr(bgh, 1, 2) = '01') and (BGHGDPDJB = 1) and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2)) 	)A ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = @" select SUM(BGMJ) from     (    select BGQDLBM, BGHDLBM,case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from    (    select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ  where XZQTZLX<>'2' and XZQTZLX<>'3')A WHERE(substr(bgq, 1, 2) = '01') and(BGQGDPDJB = 1) and(BGQGDPDJB <> BGHGDPDJB or BGQGDLX <> BGHGDLX OR substr(bgq, 1, 2) <> substr(bgh, 1, 2))    )A  ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        sql = @"select SUM(BGMJ) from 	
	(
	select 
	case when XZQTZLX='1' THEN bgq else bgh END dlbm,case when XZQTZLX='1' THEN BGQGDPDJB else BGQGDPDJB END GDPDJB,
	case when XZQTZLX='1' THEN BGQGDLX else BGHGDLX END GDLX,BGMJ
	from 
	(
	select 
	case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,
	BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from	
	(
	select  
	substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM, 
	BGMJ,BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from JCTJB_GQ where XZQTZLX in ('1','3') 
	)A 
	)A
	WHERE substr(dlbm, 1, 2) = '01' and GDPDJB = '1'
	)A ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND BGHGDPDJB<> '1' and (XZQTZLX='2' or XZQTZLX='4')  and BGQCZCSXM='空'";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    else
                    {
                        // 增加
                        sql = string.Format(@" select SUM(BGMJ) from 
(
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from
(
select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ  where XZQTZLX<>'2' and XZQTZLX<>'3'
)A WHERE substr(bgh, 1, 2) = '01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2))
)A  ", cell.Level1Name, cell.Level2Name);
                        if (cell.Level2Name == "PD")
                            sql = string.Format("select SUM(BGMJ) from (select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from(select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') )A WHERE substr(bgh, 1, 2) = '01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2)) )A ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = string.Format(@" select SUM(BGMJ) from 
(
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from
(
select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ  where XZQTZLX<>'2' and XZQTZLX<>'3' 
)A WHERE substr(bgq, 1, 2) = '01' and BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2))
)A  ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        sql = string.Format(@"select SUM(BGMJ) from 	
	(
	select 
	case when XZQTZLX='1' THEN bgq else bgh END dlbm,
	case when XZQTZLX='1' THEN BGQGDPDJB else BGQGDPDJB END GDPDJB,
	case when XZQTZLX='1' THEN BGQGDLX else BGHGDLX END GDLX,BGMJ
	from 
	(
	select 
	case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
	case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,
	BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from	
	(
	select  
	substr(BGQDLBM,1,4) BGQDLBM,
	substr(BGQCZCSXM,1,3) BGQCZCSXM,
	substr(BGHDLBM,1,4) BGHDLBM,
	substr(BGHCZCSXM,1,3) BGHCZCSXM, 
	BGMJ,BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX 
	from JCTJB_GQ where XZQTZLX in ('1','3') 
	)A 
	)A
	WHERE substr(dlbm,1,2) = '01' and GDPDJB = '{0}' AND GDLX = '{1}'
	)A ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND ( BGHGDPDJB<> '{0}' OR BGHGDLX<> '{1}') and (XZQTZLX='2' or XZQTZLX='4') and BGQCZCSXM='空'", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    //调整后年初面积
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                // 合计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "合计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level0Name == cell.Level0Name && x.Level1Name == cell.Level1Name && x.Level2Name != "合计");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                // 耕地面积统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "耕地面积") continue;
                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name == "合计" || x.Level2Name == "面积");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B6:O12");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                }

                #endregion

                // 保存EXCEL
                workbook.Save(targetFile);

                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }

        public BBPCModel Export(string savePath, string baseReportPath, IRDBHelper dbHelper, ref List<BBPCModel> pcModelList)
        {
            BBPCModel result = null;
            try
            {
                DataTable dt = null;
                if (string.IsNullOrWhiteSpace(savePath))
                {
                    MessageHelper.ShowError("存储位置错误请重试!");
                    return result;
                }
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    return result;
                }
                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
                string tempFile = tempPath + "耕地坡度分级面积变化统计表.xlsx";
                string targetFile = savePath;

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":Q" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B6:O6");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:O5");

                Cell level0Cell = null;
                Cell level1Cell = null;
                Cell level2Cell = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level0Name = string.Empty;
                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level0 = string.Empty;
                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level0Cell = dataRange[0, i];
                    level1Cell = dataRange[1, i];
                    level2Cell = dataRange[2, i];

                    if (!string.IsNullOrWhiteSpace(level0Cell.StringValue))
                    {
                        Level0Name = level0Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue;
                    }

                    if (Level0Name == "耕地面积")
                    {
                        Level0 = "耕地面积";
                        Level1 = "耕地面积";
                        Level2 = "耕地面积";
                    }
                    if (Level0Name == "平地")
                    {
                        Level0 = "平地";
                        Level1 = "1";
                        Level2 = "面积";
                    }
                    if (Level0Name == "梯田及坡地面积")
                    {
                        Level0 = "梯田及坡地面积";
                        if (Level1Name == "2°～6°")
                        {
                            Level1 = "2";
                        }
                        if (Level1Name == "6°～15°")
                        {
                            Level1 = "3";
                        }
                        if (Level1Name == "15°～25°")
                        {
                            Level1 = "4";
                        }
                        if (Level1Name == "＞25°")
                        {
                            Level1 = "5";
                        }

                        if (Level2Name == "合计")
                        {
                            Level2 = "合计";
                        }
                        if (Level2Name == "梯田")
                        {
                            Level2 = "TT";
                        }
                        if (Level2Name == "坡地")
                        {
                            Level2 = "PD";
                        }
                    }
                    CellInfo cellInfo = new CellInfo()
                    {
                        Level0Name = Level0,
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "耕地面积" || cell.Level2Name == "合计") continue;

                    if (cell.Level0Name == "平地")
                    {
                        // 增加
                        sql = @"select SUM(BGMJ) from 
	(
	select BGQDLBM,BGHDLBM,case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from
	(
	select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') 
	)A WHERE (substr(bgh, 1, 2) = '01') and (BGHGDPDJB = 1) and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2)) 
	)A ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = @" select SUM(BGMJ) from 
    (
    select BGQDLBM, BGHDLBM,case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from
    (
    select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') 

    )A WHERE(substr(bgq, 1, 2) = '01') and(BGQGDPDJB = 1) and(BGQGDPDJB <> BGHGDPDJB or BGQGDLX <> BGHGDLX OR substr(bgq, 1, 2) <> substr(bgh, 1, 2))
    )A  ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        //sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '1' AND BGQGDPDJB <> '1' and (XZQTZLX='1' or XZQTZLX='3')";
                        sql = @"select SUM(BGMJ) from 	
	(
	select 
	case when XZQTZLX='1' THEN bgq else bgh END dlbm,case when XZQTZLX='1' THEN BGQGDPDJB else BGQGDPDJB END GDPDJB,
	case when XZQTZLX='1' THEN BGQGDLX else BGHGDLX END GDLX,BGMJ
	from 
	(
	select 
	case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,
	BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from	
	(
	select  
	substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM, 
	BGMJ,BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from JCTJB_GQ where XZQTZLX in ('1','3') 
	)A 
	)A
	WHERE substr(dlbm, 1, 2) = '01' and GDPDJB = '1'
	)A ";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND BGHGDPDJB<> '1' and (XZQTZLX='2' or XZQTZLX='4')";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    else
                    {
                        // 增加
                        sql = string.Format(@" select SUM(BGMJ) from 
(
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from
(
select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') 
)A WHERE substr(bgh, 1, 2) = '01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2))
)A  ", cell.Level1Name, cell.Level2Name);
                        if (cell.Level2Name == "PD")
                            sql = string.Format("select SUM(BGMJ) from (select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from(select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') )A WHERE substr(bgh, 1, 2) = '01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2)) )A ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = string.Format(@" select SUM(BGMJ) from 
(
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from
(
select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') 
)A WHERE substr(bgq, 1, 2) = '01' and BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2))
)A  ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        //sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' AND (BGQGDPDJB <> '{0}' OR BGQGDLX <> '{1}') and (XZQTZLX='1' or XZQTZLX='3')", cell.Level1Name, cell.Level2Name);
                        sql = string.Format(@"select SUM(BGMJ) from 	
	(
	select 
	case when XZQTZLX='1' THEN bgq else bgh END dlbm,
	case when XZQTZLX='1' THEN BGQGDPDJB else BGQGDPDJB END GDPDJB,
	case when XZQTZLX='1' THEN BGQGDLX else BGHGDLX END GDLX,BGMJ
	from 
	(
	select 
	case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
	case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,
	BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX from	
	(
	select  
	substr(BGQDLBM,1,4) BGQDLBM,
	substr(BGQCZCSXM,1,3) BGQCZCSXM,
	substr(BGHDLBM,1,4) BGHDLBM,
	substr(BGHCZCSXM,1,3) BGHCZCSXM, 
	BGMJ,BGQGDPDJB,BGHGDPDJB,BGQGDLX,BGHGDLX,XZQTZLX 
	from JCTJB_GQ where XZQTZLX in ('1','3') 
	)A 
	)A
	WHERE substr(dlbm,1,2) = '01' and GDPDJB = '{0}' AND GDLX = '{1}'
	)A ", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND ( BGHGDPDJB<> '{0}' OR BGHGDLX<> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    //调整后年初面积
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                // 合计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "合计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level0Name == cell.Level0Name && x.Level1Name == cell.Level1Name && x.Level2Name != "合计");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                // 耕地面积统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "耕地面积") continue;
                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name == "合计" || x.Level2Name == "面积");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B6:O12");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                    if (dataRange[6, i].DoubleValue < 0)
                    {
                        if (result == null)
                        {
                            result = new BBPCModel();
                            result.ReportName = 10;
                            result.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = i;
                        coluModel.DiffValue = Math.Abs(dataRange[6, i].DoubleValue);

                        CellInfo cell = cellList[i];
                        if (cell.Level2Name == "耕地面积" || cell.Level2Name == "合计") continue;

                        if (cell.Level0Name == "平地")
                        {
                            // 减少
                            sql = @"select SUM(BGMJ) from 
	(
	select BGQDLBM,BGHDLBM,case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from
	(
	select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB,BGQGDLX,BGHGDLX from JCTJB_GQ 
	)A WHERE (substr(bgh, 1, 2) = '01') and (BGHGDPDJB = 1) and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2)) 
	)A ";
                        }
                        else
                        {
                            // 减少
                            sql = string.Format(@" select SUM(BGMJ) from 
(
select case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQGDPDJB, BGHGDPDJB  from
(
select  substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQGDPDJB, BGHGDPDJB, BGQGDLX, BGHGDLX from JCTJB_GQ
)A WHERE substr(bgq, 1, 2) = '01' and BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' and (BGQGDPDJB<>BGHGDPDJB or BGQGDLX<>BGHGDLX OR substr(bgq, 1, 2)<>substr(bgh, 1, 2))
)A  ", cell.Level1Name, cell.Level2Name);
                        }
                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        result.Columns.Add(coluModel);
                    }
                }

                #endregion

                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }

        public BBPCModel CheckNegative(string baseReportPath, IRDBHelper dbHelper, bool isTZMJPC, ref List<BBPCModel> TZNCbBPCModels)
        {
            try
            {
                BBPCModel result = null;
                BBPCModel TZresult = null;
                DataTable dt = null;
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                //FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("*耕地坡度分级面积汇总表.xlsx"), SearchOption.AllDirectories);
                FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xlsx"), SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    sourceFile = fileSet[0].FullName;
                }
                else
                {
                    fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")耕地坡度分级面积汇总表.xls"), SearchOption.AllDirectories);
                    if (fileSet != null && fileSet.Length > 0)
                    {
                        sourceFile = fileSet[0].FullName;
                    }
                }
                if (!System.IO.File.Exists(sourceFile))
                {
                    //Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!");
                    //Common.Utility.LogAPI.Debug("耕地坡度分级面积汇总表 不能被 成功 找到\r\n");
                    return result;
                }
                //Common.Utility.LogAPI.Debug("耕地坡度分级面积汇总表 能被 成功 找到\r\n");

                // 地类字典
                Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
                Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
                Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();

                // 读取模板
                string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\统一时点更新统计表\原样表\";
                string tempFile = tempPath + "表 5-耕地坡度分级面积变化统计表.xlsx";

                Workbook workbook = new Workbook(tempFile);
                Worksheet sheet = workbook.Worksheets[0];

                Workbook sourceWorkbook = new Workbook(sourceFile);
                Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
                Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
                int valueRow = 0;
                for (int i = 0; i < cellsNC.MaxRow; i++)
                {
                    if (cellsNC[i, 0].IsMerged == false && cellsNC[i, 0].Value != null && cellsNC[i, 0].Value.ToString() == "名称")
                    {
                        valueRow = i + 2;
                        break;
                    }
                    if (cellsNC[i, 0].IsMerged == true && cellsNC[i, 0].Value != null && cellsNC[i, 0].Value.ToString() == "名称")
                    {
                        valueRow = i + cellsNC[i, 0].GetMergedRange().RowCount + 1;
                        break;
                    }
                }
                Range sourceRange = sourceSheet.Cells.CreateRange("C" + valueRow + ":P" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B6:O6");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    if (sourceRange[0, i].Value == null || string.IsNullOrWhiteSpace(sourceRange[0, i].StringValue))
                    {
                        sourceRange[0, i].Value = 0;
                    }
                    dataRange[0, i].Value = sourceRange[0, i].Value;
                }
                #endregion

                #region 增减面积操作
                // 增减面积操作
                string sql = string.Empty;
                dataRange = sheet.Cells.CreateRange("B3:O5");

                Cell level0Cell = null;
                Cell level1Cell = null;
                Cell level2Cell = null;

                List<CellInfo> cellList = new List<CellInfo>();
                List<CellInfo> tempList = new List<CellInfo>();

                string Level0Name = string.Empty;
                string Level1Name = string.Empty;
                string Level2Name = string.Empty;

                string Level0 = string.Empty;
                string Level1 = string.Empty;
                string Level2 = string.Empty;

                for (int i = 0; i < dataRange.ColumnCount; i++)
                {
                    level0Cell = dataRange[0, i];
                    level1Cell = dataRange[1, i];
                    level2Cell = dataRange[2, i];

                    if (!string.IsNullOrWhiteSpace(level0Cell.StringValue))
                    {
                        Level0Name = level0Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue;
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue;
                    }

                    if (Level0Name == "耕地面积")
                    {
                        Level0 = "耕地面积";
                        Level1 = "耕地面积";
                        Level2 = "耕地面积";
                    }
                    if (Level0Name == "平地")
                    {
                        Level0 = "平地";
                        Level1 = "1";
                        Level2 = "面积";
                    }
                    if (Level0Name == "梯田及坡地面积")
                    {
                        Level0 = "梯田及坡地面积";
                        if (Level1Name == "2°-6°")
                        {
                            Level1 = "2";
                        }
                        if (Level1Name == "6°-15°")
                        {
                            Level1 = "3";
                        }
                        if (Level1Name == "15°-25°")
                        {
                            Level1 = "4";
                        }
                        if (Level1Name == "＞25°")
                        {
                            Level1 = "5";
                        }

                        if (Level2Name == "合计")
                        {
                            Level2 = "合计";
                        }
                        if (Level2Name == "梯田")
                        {
                            Level2 = "TT";
                        }
                        if (Level2Name == "坡地")
                        {
                            Level2 = "PD";
                        }
                    }
                    CellInfo cellInfo = new CellInfo()
                    {
                        Level0Name = Level0,
                        Level1Name = Level1,
                        Level2Name = Level2,
                        CellColumn = level2Cell.Column
                    };

                    cellList.Add(cellInfo);
                }

                // 添加基础数据
                double valueAdd = 0;
                double valueMin = 0;
                double valueHR = 0;
                double valueHC = 0;
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name == "耕地面积" || cell.Level2Name == "合计") continue;

                    if (cell.Level0Name == "平地")
                    {
                        // 增加
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '1' AND BGQGDPDJB <> '1'";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND BGHGDPDJB<> '1'";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '1' AND BGQGDPDJB <> '1' and (XZQTZLX='1' or XZQTZLX='3')";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = "SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND BGHGDPDJB<> '1' and (XZQTZLX='2' or XZQTZLX='4')";
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    else
                    {
                        // 增加
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' AND (BGQGDPDJB <> '{0}' OR BGQGDLX <> '{1}')", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[9, cell.CellColumn].Value = valueAdd;

                        // 减少
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND ( BGHGDPDJB<> '{0}' OR BGHGDLX<> '{1}')", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[10, cell.CellColumn].Value = valueMin;

                        // 划入
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE substr(BGHDLBM,1,2)='01' and BGHGDPDJB = '{0}' AND BGHGDLX = '{1}' AND (BGQGDPDJB <> '{0}' OR BGQGDLX <> '{1}') and (XZQTZLX='1' or XZQTZLX='3')", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[6, cell.CellColumn].Value = valueHR;

                        // 划出
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND ( BGHGDPDJB<> '{0}' OR BGHGDLX<> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level1Name, cell.Level2Name);
                        dt = dbHelper.ExecuteDatatable("tb", sql, true);
                        valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
                        sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    }
                    //调整后年初面积
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                    #region 调整后年初面积负值
                    if (isTZMJPC && sheet.Cells[8, cell.CellColumn].DoubleValue < 0)
                    {
                        if (TZresult == null)
                        {
                            TZresult = new BBPCModel();
                            TZresult.ReportName = 6;
                            TZresult.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = cell.CellColumn;
                        coluModel.DiffValue = Math.Abs(sheet.Cells[8, cell.CellColumn].DoubleValue);
                        if (cell.Level0Name == "平地")
                        {
                            // 减少
                            sql = "SELECT ID FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND XZQTZLX in ('2','4')";
                        }
                        else
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND XZQTZLX in ('2','4')", cell.Level1Name, cell.Level2Name);
                        }
                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        TZresult.Columns.Add(coluModel);
                        TZNCbBPCModels.Add(TZresult);
                    }
                    #endregion
                }

                // 合计统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "合计") continue;

                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level0Name == cell.Level0Name && x.Level1Name == cell.Level1Name && x.Level2Name != "合计");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                // 耕地面积统计
                foreach (CellInfo cell in cellList)
                {
                    if (cell.Level2Name != "耕地面积") continue;
                    valueAdd = 0;
                    valueMin = 0;
                    valueHR = 0;
                    valueHC = 0;
                    tempList = cellList.FindAll(x => x.Level2Name == "合计" || x.Level2Name == "面积");
                    foreach (var item in tempList)
                    {
                        valueAdd += sheet.Cells[9, item.CellColumn].DoubleValue;
                        valueMin += sheet.Cells[10, item.CellColumn].DoubleValue;
                        valueHR += sheet.Cells[6, item.CellColumn].DoubleValue;
                        valueHC += sheet.Cells[7, item.CellColumn].DoubleValue;
                    }
                    sheet.Cells[9, cell.CellColumn].Value = valueAdd;
                    sheet.Cells[10, cell.CellColumn].Value = valueMin;
                    sheet.Cells[6, cell.CellColumn].Value = valueHR;
                    sheet.Cells[7, cell.CellColumn].Value = valueHC;
                    sheet.Cells[8, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[5, cell.CellColumn].Value) + valueHR - valueHC;
                }

                #endregion

                #region 年末面积操作
                // 年末面积操作
                dataRange = sheet.Cells.CreateRange("B6:O12");
                for (int i = 0; i < sourceRange.ColumnCount; i++)
                {
                    for (int j = 0; j < 6; j++)
                    {
                        string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
                        dataRange[j, i].PutValue(s, true);
                        Style style = dataRange[j, i].GetStyle();
                        style.Number = 2;
                        dataRange[j, i].SetStyle(style);
                    }
                    dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
                    if (dataRange[6, i].DoubleValue < 0)
                    {
                        if (result == null)
                        {
                            result = new BBPCModel();
                            result.ReportName = 6;
                            result.Columns = new List<PCColumnModel>();
                        }
                        PCColumnModel coluModel = new PCColumnModel();
                        coluModel.Ids = new List<int>();
                        coluModel.IsActive = true;
                        coluModel.ColumnIndex = i;
                        coluModel.DiffValue = Math.Abs(dataRange[6, i].DoubleValue);

                        CellInfo cell = cellList[i];
                        if (cell.Level2Name == "耕地面积" || cell.Level2Name == "合计") continue;

                        if (cell.Level0Name == "平地")
                        {
                            // 减少
                            sql = "SELECT ID FROM JCTJB_GQ WHERE BGQGDPDJB = '1' AND BGHGDPDJB<> '1' and XZQTZLX='0'";
                        }
                        else
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQGDPDJB = '{0}' AND BGQGDLX = '{1}' AND ( BGHGDPDJB<> '{0}' OR BGHGDLX<> '{1}')  and XZQTZLX='0'", cell.Level1Name, cell.Level2Name);
                        }
                        DataTable IDsTable = dbHelper.ExecuteDatatable("ids", sql, true);
                        string ids = string.Empty;
                        foreach (DataRow row in IDsTable.Rows)
                        {
                            coluModel.Ids.Add(Convert.ToInt32(row[0]));
                        }
                        result.Columns.Add(coluModel);
                    }
                }

                #endregion

                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }


    }
}