using Aspose.Cells;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Maps;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.ExtensionMethod;
using KGIS.Framework.Utils.Helper;
using Kingo.Plugin.YJJK.ModelEntity;
using Kingo.PluginServiceInterface;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using System.Xml;

namespace Kingo.Plugin.YJJK.ZLBBReport
{
    /// <summary>
    /// 城镇村及工矿用地面积变化统计表
    /// </summary>
    public class ExportCZCJGKYDMJBHTJB
    {
        /// <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)
                {
                    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;

                    #region 解决"基础数据包\汇总表格"中此"城镇村及工矿用地面积变化统计表"文件如果命名成"*城镇村及工矿用地面积变化统计表0703.xlsx"后会错误地将"飞入地城镇村及工矿内部土地利用统计表"识别为年初数据的问题;
                    string sRealFileNameTemp = "";
                    List<string> FileInfoSourcePathList = new List<string>();
                    for (int iJ = 0; iJ < fileSet.Count(); iJ++)
                    {
                        sRealFileNameTemp = fileSet[iJ].Name;
                        if (sRealFileNameTemp.Contains("飞入地") == true)
                        {
                            continue;
                        }
                        else
                        {
                            FileInfoSourcePathList.Add(fileSet[iJ].FullName);
                        }
                    }

                    if (FileInfoSourcePathList == null || FileInfoSourcePathList.Count() <= 0)
                    {
                        sourceFile = "";
                    }
                    else
                    {
                        sourceFile = FileInfoSourcePathList[0];
                    }
                    #endregion
                }

                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\统一时点更新统计表\原样表\";
                string tempFile = tempPath + "表 4-城镇村及工矿用地面积变化统计表.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, 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 + ":OZ" + valueRow);
                //Range sourceRange = sourceSheet.Cells.CreateRange("C6:OZ6");
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B6:OY6");
                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:OY12");

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

                Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");
                Match DLBMStr = 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];

                    Level2Name = string.Empty;

                    if (!string.IsNullOrWhiteSpace(level0Cell.StringValue))
                    {
                        Level0Name = level0Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }

                    Level0 = rex.Match(Level0Name).Value;
                    Level1 = Level1Name.Contains("合计") ? "合计" : rex.Match(Level1Name).Value;
                    Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;
                    if (string.IsNullOrWhiteSpace(Level2)) Level2 = "合计";
                    if (Level1 == "09") Level2 = "09";

                    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 == "20")
                    {
                        // 增加
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') and (XZQTZLX='1' or XZQTZLX='3') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,3)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,3) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,3)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,3) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') and (XZQTZLX='1' or XZQTZLX='3') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level0Name);
                        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) + sheet.Cells[6, cell.CellColumn].DoubleValue - sheet.Cells[7, cell.CellColumn].DoubleValue;
                }

                // 小计统计
                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 != "小计" && 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 == "09") && x.Level0Name == cell.Level0Name);
                    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:OY12");
                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
            {

            }
        }

        /// <summary>
        /// 执行导出
        /// </summary>
        /// <param name="savePath">保存位置</param>
        /// <param name="baseReportPath">获取年初表位置</param>
        /// <returns>是否成功</returns>
        public bool Export_SQL(string savePath, string baseReportPath, IRDBHelper dbHelper)
        {
            string ncsjExcelPath = string.Empty;
            string fileName = string.Empty;
            try
            {
                if (string.IsNullOrWhiteSpace(savePath))
                {
                    MessageHelper.ShowError("存储位置错误请重试!");
                    return false;
                }
                string sourceFile = string.Empty;
                DirectoryInfo dir = new DirectoryInfo(baseReportPath);
                #region 数据赋值
                FileInfo[] fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")城镇村及工矿*", SearchOption.AllDirectories);
                if (fileSet != null && fileSet.Length > 0)
                {
                    ncsjExcelPath = fileSet[0].FullName;
                    fileName = fileSet[0].Name.Replace("(", "").Replace(")", "").Replace((MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, "").Replace(fileSet[0].Extension, "");
                }
                //else
                //{
                //    fileSet = dir.GetFiles("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")城镇村及工矿内部土地利用统计表.xls", SearchOption.AllDirectories);
                //    if (fileSet != null && fileSet.Length > 0)
                //    {
                //        ncsjExcelPath = fileSet[0].FullName;
                //    }
                //}
                if (String.IsNullOrWhiteSpace(ncsjExcelPath) == true)
                {
                    return false;
                }
                string tempPath = AppDomain.CurrentDomain.BaseDirectory + "TempalateReports\\BGTJ\\城镇村及工矿用地面积变化统计表.xlsx";
                File.Copy(tempPath, savePath, true);
                DataTable resultdataTable = new DataTable();
                DataTable dt = dbHelper.ExecuteDatatable("CZCJGKYDMJBHTJB", @" select Cells_X,Cells_Y,BGMJ from  
(
select BGHDLBM,sum(BGMJ) BGMJ from  
(
select  substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ from JCTJB_GQ where XZQTZLX<>'2' )A WHERE substr(BGHCZCSXM, 1, 2)='20' and (BGQDLBM<>BGHDLBM or substr(BGQCZCSXM, 1, 2)<>substr(BGHCZCSXM, 1, 2))  GROUP BY BGHDLBM
)A left join CZCJGKYDMJBHTJB B on a.BGHDLBM=b.bgh where B.Cells_X=8 
 union all
 select Cells_X,Cells_Y,BGMJ from  
(
select BGQDLBM,sum(BGMJ) BGMJ from  
(
select  substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ from JCTJB_GQ where XZQTZLX<>'2' )A WHERE substr(BGQCZCSXM, 1, 2)='20' and (BGQDLBM<>BGHDLBM or substr(BGQCZCSXM, 1, 2)<>substr(BGHCZCSXM, 1, 2))  GROUP BY BGQDLBM
)A left join CZCJGKYDMJBHTJB B on a.BGQDLBM=b.bgh where B.Cells_X=9  ", true);
                if (dt != null && dt.Rows.Count > 0)
                {
                    resultdataTable.Merge(dt);
                }
                #endregion

                #region 上年末面积
                DataTable table = dbHelper.ExecuteDatatable("tab", string.Format(@"select  case WHEN column1='{0}' THEN'column1' ELSE 'column2' END ColumnName from  {1} WHERE column1='{0}' or column2='{0}' ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, fileName), true);
                if (table == null || table.Rows.Count == 0)
                {
                    return false;
                }
                string ColumnName = table.Rows[0][0].ToTrim();
                if (ColumnName == "column1")
                {
                    dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" 
select '4' Cells_X, '1'    Cells_Y,  round(column2,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '2'    Cells_Y,  round(column3,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '3'    Cells_Y,  round(column4,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '4'    Cells_Y,  round(column5,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '5'    Cells_Y,  round(column6,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '6'    Cells_Y,  round(column7,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '7'    Cells_Y,  round(column8,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '8'    Cells_Y,  round(column9,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '9'    Cells_Y,  round(column10,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '10'   Cells_Y,  round(column11,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '11'   Cells_Y,  round(column12,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '12'   Cells_Y,  round(column13,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '13'   Cells_Y,  round(column14,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '14'   Cells_Y,  round(column15,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '15'   Cells_Y,  round(column16,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '16'   Cells_Y,  round(column17,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '17'   Cells_Y,  round(column18,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '18'   Cells_Y,  round(column19,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '19'   Cells_Y,  round(column20,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '20'   Cells_Y,  round(column21,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '21'   Cells_Y,  round(column22,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '22'   Cells_Y,  round(column23,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '23'   Cells_Y,  round(column24,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '24'   Cells_Y,  round(column25,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '25'   Cells_Y,  round(column26,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '26'   Cells_Y,  round(column27,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '27'   Cells_Y,  round(column28,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '28'   Cells_Y,  round(column29,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '29'   Cells_Y,  round(column30,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '30'   Cells_Y,  round(column31,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '31'   Cells_Y,  round(column32,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '32'   Cells_Y,  round(column33,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '33'   Cells_Y,  round(column34,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '34'   Cells_Y,  round(column35,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '35'   Cells_Y,  round(column36,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '36'   Cells_Y,  round(column37,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '37'   Cells_Y,  round(column38,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '38'   Cells_Y,  round(column39,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '39'   Cells_Y,  round(column40,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '40'   Cells_Y,  round(column41,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '41'   Cells_Y,  round(column42,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '42'   Cells_Y,  round(column43,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '43'   Cells_Y,  round(column44,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '44'   Cells_Y,  round(column45,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '45'   Cells_Y,  round(column46,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '46'   Cells_Y,  round(column47,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '47'   Cells_Y,  round(column48,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '48'   Cells_Y,  round(column49,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '49'   Cells_Y,  round(column50,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '50'   Cells_Y,  round(column51,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '51'   Cells_Y,  round(column52,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '52'   Cells_Y,  round(column53,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '53'   Cells_Y,  round(column54,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '54'   Cells_Y,  round(column55,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '55'   Cells_Y,  round(column56,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '56'   Cells_Y,  round(column57,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '57'   Cells_Y,  round(column58,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '58'   Cells_Y,  round(column59,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '59'   Cells_Y,  round(column60,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '60'   Cells_Y,  round(column61,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '61'   Cells_Y,  round(column62,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '62'   Cells_Y,  round(column63,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '63'   Cells_Y,  round(column64,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '64'   Cells_Y,  round(column65,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '65'   Cells_Y,  round(column66,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '66'   Cells_Y,  round(column67,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '67'   Cells_Y,  round(column68,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '68'   Cells_Y,  round(column69,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '69'   Cells_Y,  round(column70,4) BGMJ from  {2}  WHERE {1}='{0}'   ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true);
                }
                else if (ColumnName == "column2")
                {
                    dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" 
select '4' Cells_X, '1'    Cells_Y,  round(column3,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '2'    Cells_Y,  round(column4,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '3'    Cells_Y,  round(column5,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '4'    Cells_Y,  round(column6,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '5'    Cells_Y,  round(column7,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '6'    Cells_Y,  round(column8,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '7'    Cells_Y,  round(column9,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '8'    Cells_Y,  round(column10,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '9'    Cells_Y,  round(column11,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '10'   Cells_Y,  round(column12,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '11'   Cells_Y,  round(column13,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '12'   Cells_Y,  round(column14,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '13'   Cells_Y,  round(column15,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '14'   Cells_Y,  round(column16,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '15'   Cells_Y,  round(column17,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '16'   Cells_Y,  round(column18,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '17'   Cells_Y,  round(column19,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '18'   Cells_Y,  round(column20,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '19'   Cells_Y,  round(column21,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '20'   Cells_Y,  round(column22,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '21'   Cells_Y,  round(column23,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '22'   Cells_Y,  round(column24,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '23'   Cells_Y,  round(column25,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '24'   Cells_Y,  round(column26,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '25'   Cells_Y,  round(column27,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '26'   Cells_Y,  round(column28,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '27'   Cells_Y,  round(column29,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '28'   Cells_Y,  round(column30,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '29'   Cells_Y,  round(column31,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '30'   Cells_Y,  round(column32,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '31'   Cells_Y,  round(column33,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '32'   Cells_Y,  round(column34,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '33'   Cells_Y,  round(column35,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '34'   Cells_Y,  round(column36,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '35'   Cells_Y,  round(column37,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '36'   Cells_Y,  round(column38,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '37'   Cells_Y,  round(column39,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '38'   Cells_Y,  round(column40,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '39'   Cells_Y,  round(column41,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '40'   Cells_Y,  round(column42,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '41'   Cells_Y,  round(column43,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '42'   Cells_Y,  round(column44,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '43'   Cells_Y,  round(column45,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '44'   Cells_Y,  round(column46,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '45'   Cells_Y,  round(column47,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '46'   Cells_Y,  round(column48,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '47'   Cells_Y,  round(column49,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '48'   Cells_Y,  round(column50,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '49'   Cells_Y,  round(column51,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '50'   Cells_Y,  round(column52,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '51'   Cells_Y,  round(column53,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '52'   Cells_Y,  round(column54,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '53'   Cells_Y,  round(column55,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '54'   Cells_Y,  round(column56,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '55'   Cells_Y,  round(column57,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '56'   Cells_Y,  round(column58,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '57'   Cells_Y,  round(column59,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '58'   Cells_Y,  round(column60,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '59'   Cells_Y,  round(column61,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '60'   Cells_Y,  round(column62,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '61'   Cells_Y,  round(column63,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '62'   Cells_Y,  round(column64,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '63'   Cells_Y,  round(column65,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '64'   Cells_Y,  round(column66,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '65'   Cells_Y,  round(column67,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '66'   Cells_Y,  round(column68,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '67'   Cells_Y,  round(column69,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '68'   Cells_Y,  round(column70,4) BGMJ from  {2}  WHERE {1}='{0}'   union ALL
select '4' Cells_X, '69'   Cells_Y,  round(column71,4) BGMJ from  {2}  WHERE {1}='{0}'   ", (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE, ColumnName, fileName), true);
                }
                
                if (dt != null && dt.Rows.Count > 0)
                {
                    resultdataTable.Merge(dt);
                }
                #endregion

                #region 行政区调入
                dt = dbHelper.ExecuteDatatable("XZQDR", string.Format(@"  select Cells_X,Cells_Y,BGMJ from 
 (
 select DLBM,sum(BGMJ) BGMJ from  
 (
 select case when XZQTZLX='1' THEN BGQCZCSXM else BGHCZCSXM END CZCSXM,BGMJ,case when XZQTZLX='1' THEN BGQDLBM else BGHDLBM END DLBM from 
 (
 select  substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ,XZQTZLX from JCTJB_GQ where XZQTZLX in ('1','3')
 )A
 )A WHERE substr(CZCSXM, 1, 2)='20' GROUP BY DLBM
 )A left join CZCJGKYDMJBHTJB B on a.DLBM=b.bgh where B.Cells_X=5 "), true);
                if (dt != null && dt.Rows.Count > 0)
                {
                    resultdataTable.Merge(dt);
                }
                #endregion

                #region 行政区调出
                dt = dbHelper.ExecuteDatatable("XZQDC", string.Format(@" select Cells_X,Cells_Y,BGMJ from  (select BGQDLBM,sum(BGMJ) BGMJ from  (select  substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ from JCTJB_GQ where XZQTZLX in ('2','4') )A WHERE substr(BGQCZCSXM, 1, 2)='20' and (BGQDLBM<>BGHDLBM or substr(BGQCZCSXM, 1, 2)<>substr(BGHCZCSXM, 1, 2))  GROUP BY BGQDLBM)A left join CZCJGKYDMJBHTJB B on a.BGQDLBM=b.bgh where B.Cells_X=6 "), true);
                if (dt != null && dt.Rows.Count > 0)
                {
                    resultdataTable.Merge(dt);
                }
                CellHelper.SetFileToDisk(resultdataTable, savePath, 0);
                #endregion
                CellHelper.Import(savePath);
                return true;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {

            }
        }
        public List<Dictionary<string, string>> GetResults_Excel(string sTableName)
        {
            List<Dictionary<string, string>> result = new List<Dictionary<string, string>>();
            try
            {
                XmlDocument doc = new XmlDocument();
                string strPath = SysAppPath.GetCurrentAppPath();
                strPath += @"Configs\Results_Excel.xml";
                doc.Load(strPath);
                XmlNode nodeDataCatalog = doc.SelectSingleNode("TableName");
                if (nodeDataCatalog == null)
                    return result;
                XmlNode cnodeDataCatalog = nodeDataCatalog.SelectSingleNode(sTableName);
                foreach (XmlNode item in cnodeDataCatalog.ChildNodes)
                {
                    Dictionary<string, string> dic = new Dictionary<string, string>();
                    foreach (XmlNode citem in item.ChildNodes)
                    {
                        dic.Add(citem.Attributes["DLBM"].Value, citem.Attributes["Cells"].Value);
                    }
                    result.Add(dic);
                }
                return result;
            }
            catch (Exception ex)
            {
                LogAPI.Debug(ex);
                throw;
            }
        }
        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)
                {
                    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;

                    #region 解决"基础数据包\汇总表格"中此"城镇村及工矿用地面积变化统计表"文件如果命名成"*城镇村及工矿用地面积变化统计表0703.xlsx"后会错误地将"飞入地城镇村及工矿内部土地利用统计表"识别为年初数据的问题;
                    string sRealFileNameTemp = "";
                    List<string> FileInfoSourcePathList = new List<string>();
                    for (int iJ = 0; iJ < fileSet.Count(); iJ++)
                    {
                        sRealFileNameTemp = fileSet[iJ].Name;
                        if (sRealFileNameTemp.Contains("飞入地") == true)
                        {
                            continue;
                        }
                        else
                        {
                            FileInfoSourcePathList.Add(fileSet[iJ].FullName);
                        }
                    }

                    if (FileInfoSourcePathList == null || FileInfoSourcePathList.Count() <= 0)
                    {
                        sourceFile = "";
                    }
                    else
                    {
                        sourceFile = FileInfoSourcePathList[0];
                    }
                    #endregion
                }

                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 + "表 4-城镇村及工矿用地面积变化统计表.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 + ":OZ" + valueRow);
                Range dataRange = null;

                #region 年初面积操作
                // 年初面积操作
                dataRange = sheet.Cells.CreateRange("B6:OY6");
                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:OY12");

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

                Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");

                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];

                    Level2Name = string.Empty;

                    if (!string.IsNullOrWhiteSpace(level0Cell.StringValue))
                    {
                        Level0Name = level0Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
                    {
                        Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }
                    if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
                    {
                        Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
                    }

                    Level0 = rex.Match(Level0Name).Value;
                    Level1 = Level1Name.Contains("合计") ? "合计" : rex.Match(Level1Name).Value;
                    Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;
                    if (string.IsNullOrWhiteSpace(Level2)) Level2 = "合计";
                    if (Level1 == "09") Level2 = "09";

                    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 == "20")
                    {
                        // 增加
                        sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ WHERE BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') and (XZQTZLX='1' or XZQTZLX='3') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,3)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,3) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,3)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,3) <> '{1}') ", cell.Level2Name, cell.Level0Name);
                        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 BGHDLBM LIKE '{0}%' AND SUBSTR(BGHCZCSXM,1,2)='{1}' and (BGQDLBM NOT LIKE '{0}%' OR SUBSTR(BGQCZCSXM,1,2) <> '{1}') and (XZQTZLX='1' or XZQTZLX='3') ", cell.Level2Name, cell.Level0Name);
                        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 BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level0Name);
                        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) + sheet.Cells[6, cell.CellColumn].DoubleValue - sheet.Cells[7, cell.CellColumn].DoubleValue;
                    #region 调整年初面积负值
                    if (isTZMJPC && sheet.Cells[8, cell.CellColumn].DoubleValue < 0)
                    {
                        if (TZresult == null)
                        {
                            TZresult = new BBPCModel();
                            TZresult.ReportName = 3;
                            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 == "20")
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and  XZQTZLX in ('2','4')", cell.Level2Name, cell.Level0Name);
                        }
                        else
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,3)='{1}' and XZQTZLX in ('2','4')", cell.Level2Name, cell.Level0Name);
                        }
                        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 != "小计" && 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 == "09") && x.Level0Name == cell.Level0Name);
                    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:OY12");
                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 = 3;
                            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 == "20")
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,2)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,2) <> '{1}') and XZQTZLX='0'", cell.Level2Name, cell.Level0Name);
                        }
                        else
                        {
                            // 减少
                            sql = string.Format("SELECT ID FROM JCTJB_GQ WHERE BGQDLBM LIKE '{0}%' AND SUBSTR(BGQCZCSXM,1,3)='{1}' and (BGHDLBM NOT LIKE '{0}%' OR SUBSTR(BGHCZCSXM,1,3) <> '{1}') and XZQTZLX='0'", cell.Level2Name, cell.Level0Name);
                        }
                        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
            {

            }
        }
    }


    /// <summary>
    /// 单元格模型
    /// </summary>
    public class CellInfo
    {
        /// <summary>
        /// 三大类名称
        /// </summary>
        public string Level0Name { get; set; }
        /// <summary>
        /// 一级地类名称
        /// </summary>
        public string Level1Name { get; set; }
        /// <summary>
        /// 二级地类名称
        /// </summary>
        public string Level2Name { get; set; }

        /// <summary>
        /// 单元格行列位置
        /// </summary>
        public int CellColumn { get; set; }
    }
}