|
|
|
|
|
using Aspose.Cells;
|
|
|
|
|
|
using KGIS.Framework.DBOperator;
|
|
|
|
|
|
using KGIS.Framework.Maps;
|
|
|
|
|
|
using KGIS.Framework.Utils;
|
|
|
|
|
|
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.Text.RegularExpressions;
|
|
|
|
|
|
|
|
|
|
|
|
namespace Kingo.Plugin.YJJK.ZLBBReport
|
|
|
|
|
|
{
|
|
|
|
|
|
/// <summary>
|
|
|
|
|
|
/// 耕地种植类型面积变化统计表
|
|
|
|
|
|
/// </summary>
|
|
|
|
|
|
public class ExportGDZZLXMJBHTJB
|
|
|
|
|
|
{
|
|
|
|
|
|
/// <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 + ")耕地种植类型*"), 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 + ")耕地种植类型"), 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 + ":AE" + valueRow);
|
|
|
|
|
|
//Range sourceRange = sourceSheet.Cells.CreateRange("C5:AD5");
|
|
|
|
|
|
Range dataRange = null;
|
|
|
|
|
|
|
|
|
|
|
|
#region 年初面积操作
|
|
|
|
|
|
// 年初面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC5");
|
|
|
|
|
|
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:AC4");
|
|
|
|
|
|
|
|
|
|
|
|
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 Level1Name = string.Empty;
|
|
|
|
|
|
string Level2Name = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
string Level1 = string.Empty;
|
|
|
|
|
|
string Level2 = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
for (int i = 0; i < dataRange.ColumnCount; i++)
|
|
|
|
|
|
{
|
|
|
|
|
|
level1Cell = dataRange[0, i];
|
|
|
|
|
|
level2Cell = dataRange[1, i];
|
|
|
|
|
|
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2Name = "";
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
Level1 = rex.Match(Level1Name).Value;
|
|
|
|
|
|
Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;
|
|
|
|
|
|
if (Level1Name.Contains("小计") && string.IsNullOrWhiteSpace(Level2))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2 = "小计";
|
|
|
|
|
|
}
|
|
|
|
|
|
CellInfo cellInfo = new CellInfo()
|
|
|
|
|
|
{
|
|
|
|
|
|
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 == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
// 增加
|
|
|
|
|
|
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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE substr(bgh, 1, 2) = '01' and BGHZZSXDM = '{0}' AND(substr(bgq, 1, 2) <> '01' OR BGQZZSXDM <> '{0}')
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[8, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM,XZQTZLX from JCTJB_GQ
|
|
|
|
|
|
)A WHERE substr(bgq, 1, 2) = '01' AND BGQZZSXDM = '{0}' AND(substr(bgh, 1, 2) <> '01' OR BGHZZSXDM <> '{0}') and XZQTZLX<>'2'
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[9, 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 BGQZZSXDM else BGHZZSXDM END ZZSXDM,BGMJ
|
|
|
|
|
|
from
|
|
|
|
|
|
(
|
|
|
|
|
|
select
|
|
|
|
|
|
case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
|
|
|
|
|
|
case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from JCTJB_GQ where XZQTZLX in ('1','3') )A )A
|
|
|
|
|
|
WHERE DLBM IN('0101', '0102', '0103') AND ZZSXDM = '{0}'
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM IN ('0101','0102','0103') and BGQCZCSXM='空')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGHDLBM NOT IN ('0101','0102','0103')) OR (BGHZZSXDM <> '{0}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[6, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE bgh = '{0}' AND BGHZZSXDM = '{1}' AND(bgq <> '{0}' OR BGQZZSXDM <> '{1}')
|
|
|
|
|
|
)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[8, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM,XZQTZLX from JCTJB_GQ
|
|
|
|
|
|
)A WHERE bgq = '{0}' AND BGQZZSXDM = '{1}' AND(bgh <> '{0}' OR BGHZZSXDM <> '{1}') and XZQTZLX<>'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[9, 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 BGQZZSXDM else BGHZZSXDM END ZZSXDM,BGMJ from (
|
|
|
|
|
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
|
|
|
|
|
|
case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX
|
|
|
|
|
|
from JCTJB_GQ where XZQTZLX in ('1','3')
|
|
|
|
|
|
)A
|
|
|
|
|
|
)A
|
|
|
|
|
|
WHERE DLBM = '{0}' AND ZZSXDM = '{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[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}' and BGQCZCSXM='空')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGHDLBM <> '{0}') OR (BGHZZSXDM <> '{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[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
//调整后年初面积
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, 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.Level1Name == cell.Level1Name && x.Level2Name != "小计");
|
|
|
|
|
|
foreach (var item in tempList)
|
|
|
|
|
|
{
|
|
|
|
|
|
valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
|
|
|
|
|
|
}
|
|
|
|
|
|
sheet.Cells[8, cell.CellColumn].Value = valueAdd;
|
|
|
|
|
|
sheet.Cells[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
sheet.Cells[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 年末面积操作
|
|
|
|
|
|
// 年末面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC11");
|
|
|
|
|
|
// 将年初面积转换为数值类型
|
|
|
|
|
|
|
|
|
|
|
|
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 + ")耕地种植类型*"), 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 + ")耕地种植类型"), 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 + ":AE" + valueRow);
|
|
|
|
|
|
Range dataRange = null;
|
|
|
|
|
|
|
|
|
|
|
|
#region 年初面积操作
|
|
|
|
|
|
// 年初面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC5");
|
|
|
|
|
|
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:AC4");
|
|
|
|
|
|
|
|
|
|
|
|
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 Level1Name = string.Empty;
|
|
|
|
|
|
string Level2Name = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
string Level1 = string.Empty;
|
|
|
|
|
|
string Level2 = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
for (int i = 0; i < dataRange.ColumnCount; i++)
|
|
|
|
|
|
{
|
|
|
|
|
|
level1Cell = dataRange[0, i];
|
|
|
|
|
|
level2Cell = dataRange[1, i];
|
|
|
|
|
|
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2Name = "";
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
Level1 = rex.Match(Level1Name).Value;
|
|
|
|
|
|
Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;
|
|
|
|
|
|
if (Level1Name.Contains("小计") && string.IsNullOrWhiteSpace(Level2))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2 = "小计";
|
|
|
|
|
|
}
|
|
|
|
|
|
CellInfo cellInfo = new CellInfo()
|
|
|
|
|
|
{
|
|
|
|
|
|
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 == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
// 增加
|
|
|
|
|
|
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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE substr(bgh, 1, 2) = '01' and BGHZZSXDM = '{0}' AND(substr(bgq, 1, 2) <> '01' OR BGQZZSXDM <> '{0}')
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[8, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE substr(bgq, 1, 2) = '01' AND BGQZZSXDM = '{0}' AND(substr(bgh, 1, 2) <> '01' OR BGHZZSXDM <> '{0}')
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
|
|
|
|
|
|
// 划入
|
|
|
|
|
|
//sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
// "WHERE (BGHDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
// " AND (BGHZZSXDM = '{0}') " +
|
|
|
|
|
|
// " AND ((BGQDLBM NOT IN ('0101','0102','0103')) OR (BGQZZSXDM <> '{0}')) and (XZQTZLX='1' or XZQTZLX='3')", 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 BGQZZSXDM else BGHZZSXDM END ZZSXDM,BGMJ
|
|
|
|
|
|
from
|
|
|
|
|
|
(
|
|
|
|
|
|
select
|
|
|
|
|
|
case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
|
|
|
|
|
|
case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from JCTJB_GQ where XZQTZLX in ('1','3') )A )A
|
|
|
|
|
|
WHERE DLBM IN('0101', '0102', '0103') AND ZZSXDM = '{0}'
|
|
|
|
|
|
)A ", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGHDLBM NOT IN ('0101','0102','0103')) OR (BGHZZSXDM <> '{0}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[6, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE bgh = '{0}' AND BGHZZSXDM = '{1}' AND(bgq <> '{0}' OR BGQZZSXDM <> '{1}')
|
|
|
|
|
|
)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[8, 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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE bgq = '{0}' AND BGQZZSXDM = '{1}' AND(bgh <> '{0}' OR BGHZZSXDM <> '{1}') )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[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
|
|
|
|
|
|
// 划入
|
|
|
|
|
|
//sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
// "WHERE (BGHDLBM = '{0}')" +
|
|
|
|
|
|
// " AND (BGHZZSXDM = '{1}') " +
|
|
|
|
|
|
// " AND ((BGQDLBM <> '{0}') OR (BGQZZSXDM <> '{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 BGQZZSXDM else BGHZZSXDM END ZZSXDM,BGMJ from (
|
|
|
|
|
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,
|
|
|
|
|
|
case when BGHCZCSXM='空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM,1,4) BGQDLBM,substr(BGQCZCSXM,1,3) BGQCZCSXM,substr(BGHDLBM,1,4) BGHDLBM,substr(BGHCZCSXM,1,3) BGHCZCSXM,BGMJ,BGQZZSXDM,BGHZZSXDM,XZQTZLX
|
|
|
|
|
|
from JCTJB_GQ where XZQTZLX in ('1','3')
|
|
|
|
|
|
)A
|
|
|
|
|
|
)A
|
|
|
|
|
|
WHERE DLBM = '{0}' AND ZZSXDM = '{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[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGHDLBM <> '{0}') OR (BGHZZSXDM <> '{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[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
//调整后年初面积
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, 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.Level1Name == cell.Level1Name && x.Level2Name != "小计");
|
|
|
|
|
|
foreach (var item in tempList)
|
|
|
|
|
|
{
|
|
|
|
|
|
valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
|
|
|
|
|
|
}
|
|
|
|
|
|
sheet.Cells[8, cell.CellColumn].Value = valueAdd;
|
|
|
|
|
|
sheet.Cells[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
sheet.Cells[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 年末面积操作
|
|
|
|
|
|
// 年末面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC11");
|
|
|
|
|
|
// 将年初面积转换为数值类型
|
|
|
|
|
|
|
|
|
|
|
|
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 = 11;
|
|
|
|
|
|
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);
|
|
|
|
|
|
|
|
|
|
|
|
sql = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
CellInfo cell = cellList[i];
|
|
|
|
|
|
if (cell.Level2Name == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
continue;
|
|
|
|
|
|
}
|
|
|
|
|
|
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, BGQZZSXDM, BGHZZSXDM from
|
|
|
|
|
|
(
|
|
|
|
|
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQZZSXDM, BGHZZSXDM from JCTJB_GQ
|
|
|
|
|
|
)A WHERE bgq = '{0}' AND BGQZZSXDM = '{1}' AND(bgh <> '{0}' OR BGHZZSXDM <> '{1}') )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 + "表 6-耕地种植类型面积变化统计表.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 + ":AD" + valueRow);
|
|
|
|
|
|
Range dataRange = null;
|
|
|
|
|
|
|
|
|
|
|
|
#region 年初面积操作
|
|
|
|
|
|
// 年初面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC5");
|
|
|
|
|
|
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:AC4");
|
|
|
|
|
|
|
|
|
|
|
|
Cell level1Cell = null;
|
|
|
|
|
|
Cell level2Cell = null;
|
|
|
|
|
|
|
|
|
|
|
|
Regex rex = new Regex(@"(?<=\()[^()]+(?=\))");
|
|
|
|
|
|
|
|
|
|
|
|
List<CellInfo> cellList = new List<CellInfo>();
|
|
|
|
|
|
List<CellInfo> tempList = new List<CellInfo>();
|
|
|
|
|
|
|
|
|
|
|
|
string Level1Name = string.Empty;
|
|
|
|
|
|
string Level2Name = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
string Level1 = string.Empty;
|
|
|
|
|
|
string Level2 = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
for (int i = 0; i < dataRange.ColumnCount; i++)
|
|
|
|
|
|
{
|
|
|
|
|
|
level1Cell = dataRange[0, i];
|
|
|
|
|
|
level2Cell = dataRange[1, i];
|
|
|
|
|
|
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level1Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level1Name = level1Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
if (!string.IsNullOrWhiteSpace(level2Cell.StringValue))
|
|
|
|
|
|
{
|
|
|
|
|
|
Level2Name = level2Cell.StringValue.Replace("（", "(").Replace("）", ")");
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
Level1 = rex.Match(Level1Name).Value;
|
|
|
|
|
|
Level2 = Level2Name.Contains("小计") ? "小计" : rex.Match(Level2Name).Value;
|
|
|
|
|
|
|
|
|
|
|
|
CellInfo cellInfo = new CellInfo()
|
|
|
|
|
|
{
|
|
|
|
|
|
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 == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
// 增加
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGHDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
" AND (BGHZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGQDLBM NOT IN ('0101','0102','0103')) OR (BGQZZSXDM <> '{0}'))", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueAdd = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[8, cell.CellColumn].Value = valueAdd;
|
|
|
|
|
|
|
|
|
|
|
|
// 减少
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGHDLBM NOT IN ('0101','0102','0103')) OR (BGHZZSXDM <> '{0}'))", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueMin = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
|
|
|
|
|
|
// 划入
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGHDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
" AND (BGHZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGQDLBM NOT IN ('0101','0102','0103')) OR (BGQZZSXDM <> '{0}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHR = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{0}') " +
|
|
|
|
|
|
" AND ((BGHDLBM NOT IN ('0101','0102','0103')) OR (BGHZZSXDM <> '{0}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name);
|
|
|
|
|
|
dt = dbHelper.ExecuteDatatable("tb", sql, true);
|
|
|
|
|
|
valueHC = dt.Rows[0][0].ToString() != "" ? Convert.ToDouble(dt.Rows[0][0].ToString()) : 0;
|
|
|
|
|
|
sheet.Cells[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
// 增加
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGHDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGHZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGQDLBM <> '{0}') OR (BGQZZSXDM <> '{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[8, cell.CellColumn].Value = valueAdd;
|
|
|
|
|
|
|
|
|
|
|
|
// 减少
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGHDLBM <> '{0}') OR (BGHZZSXDM <> '{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[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
|
|
|
|
|
|
// 划入
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGHDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGHZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGQDLBM <> '{0}') OR (BGQZZSXDM <> '{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[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
|
|
|
|
|
|
// 划出
|
|
|
|
|
|
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGHDLBM <> '{0}') OR (BGHZZSXDM <> '{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[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
//调整后年初面积
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
|
|
|
|
|
|
#region 调整后年初面积负值
|
|
|
|
|
|
if (isTZMJPC && sheet.Cells[7, cell.CellColumn].DoubleValue < 0)
|
|
|
|
|
|
{
|
|
|
|
|
|
if (TZresult == null)
|
|
|
|
|
|
{
|
|
|
|
|
|
TZresult = new BBPCModel();
|
|
|
|
|
|
TZresult.ReportName = 7;
|
|
|
|
|
|
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[7, cell.CellColumn].DoubleValue);
|
|
|
|
|
|
sql = string.Empty;
|
|
|
|
|
|
if (cell.Level2Name == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
continue;
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
// 减少
|
|
|
|
|
|
sql = string.Format("SELECT ID FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{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.Level1Name == cell.Level1Name && x.Level2Name != "小计");
|
|
|
|
|
|
foreach (var item in tempList)
|
|
|
|
|
|
{
|
|
|
|
|
|
valueAdd += sheet.Cells[8, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueMin += sheet.Cells[9, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHR += sheet.Cells[5, item.CellColumn].DoubleValue;
|
|
|
|
|
|
valueHC += sheet.Cells[6, item.CellColumn].DoubleValue;
|
|
|
|
|
|
}
|
|
|
|
|
|
sheet.Cells[8, cell.CellColumn].Value = valueAdd;
|
|
|
|
|
|
sheet.Cells[9, cell.CellColumn].Value = valueMin;
|
|
|
|
|
|
sheet.Cells[5, cell.CellColumn].Value = valueHR;
|
|
|
|
|
|
sheet.Cells[6, cell.CellColumn].Value = valueHC;
|
|
|
|
|
|
|
|
|
|
|
|
sheet.Cells[7, cell.CellColumn].Value = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value) + valueHR - valueHC;
|
|
|
|
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
#endregion
|
|
|
|
|
|
|
|
|
|
|
|
#region 年末面积操作
|
|
|
|
|
|
// 年末面积操作
|
|
|
|
|
|
dataRange = sheet.Cells.CreateRange("B5:AC11");
|
|
|
|
|
|
// 将年初面积转换为数值类型
|
|
|
|
|
|
|
|
|
|
|
|
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 = 7;
|
|
|
|
|
|
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);
|
|
|
|
|
|
|
|
|
|
|
|
sql = string.Empty;
|
|
|
|
|
|
|
|
|
|
|
|
CellInfo cell = cellList[i];
|
|
|
|
|
|
if (cell.Level2Name == "小计") continue;
|
|
|
|
|
|
|
|
|
|
|
|
if (cell.Level1Name == "01")
|
|
|
|
|
|
{
|
|
|
|
|
|
continue;
|
|
|
|
|
|
// 减少
|
|
|
|
|
|
//sql = string.Format("SELECT ID FROM JCTJB_GQ " +
|
|
|
|
|
|
// "WHERE (BGQDLBM IN ('0101','0102','0103'))" +
|
|
|
|
|
|
// " AND (BGQZZSXDM = '{0}') " +
|
|
|
|
|
|
// " AND ((BGHDLBM NOT IN ('0101','0102','0103')) OR (BGHZZSXDM <> '{0}'))", cell.Level2Name);
|
|
|
|
|
|
}
|
|
|
|
|
|
else
|
|
|
|
|
|
{
|
|
|
|
|
|
// 减少
|
|
|
|
|
|
sql = string.Format("SELECT ID FROM JCTJB_GQ " +
|
|
|
|
|
|
"WHERE (BGQDLBM = '{0}')" +
|
|
|
|
|
|
" AND (BGQZZSXDM = '{1}') " +
|
|
|
|
|
|
" AND ((BGHDLBM <> '{0}') OR (BGHZZSXDM <> '{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
|
|
|
|
|
|
{
|
|
|
|
|
|
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
|
|
|
|
|
}
|
