wangguanjie
/
NDBGJK5.0
4
1
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
年度变更建库软件5.0版本
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
2 Commits
4 Branches
0 Tags
121 MiB
 
 
Tag: Branch: Tree: wgj
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'wgj'
${ noResults }
NDBGJK5.0/Kingo.Plugins/内业变更/Kingo.Plugin.BGResultManager/Utility/ZLBBReport/ExportCZCJGKYDMJBHTJB.cs

1035 lines
59 KiB
Raw Permalink Blame History

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.BGResultManager.Model;
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.BGResultManager.Utility
{
/// <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' and XZQTZLX<>3 )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' and XZQTZLX<>3 )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; }
}
}