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.
1 Commit
4 Branches
0 Tags
121 MiB
 
 
Tag: Branch: Tree: Dev
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'Dev'
${ noResults }
NDBGJK5.0/Kingo.Plugins/内业变更/Kingo.Plugin.BGResultManager/Utility/ZLBBReport/ExportZZYYDBHTJB.cs

811 lines
41 KiB
Raw Permalink Blame History

using Aspose.Cells;
using KGIS.Framework.DBOperator;
using KGIS.Framework.Maps;
using KGIS.Framework.Utils;
using KGIS.Framework.Utils.Helper;
using Kingo.Plugin.BGResultManager.Model;
using Kingo.PluginServiceInterface;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text.RegularExpressions;
namespace Kingo.Plugin.BGResultManager.Utility
{
/// <summary>
/// 林区范围内种植园用地变化统计表
/// </summary>
public class ExportZZYYDBHTJB
{
/// <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 + ")林区范围内园地汇总统计表.xls"), SearchOption.AllDirectories);
// if (fileSet != null && fileSet.Length > 0)
// {
// sourceFile = fileSet[0].FullName;
// }
//}
if (!System.IO.File.Exists(sourceFile))
{
//Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!");
//Common.Utility.LogAPI.Debug("林区范围内种植园用地汇总统计表 不能被 成功 找到\r\n");
return false;
}
//Common.Utility.LogAPI.Debug("林区范围内种植园用地汇总统计表 能被 成功 找到\r\n");
// 地类字典
Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();
// 读取模板
string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
string tempFile = tempPath + "林区范围内园地变化统计表.xlsx";
string targetFile = savePath;
Workbook workbook = new Workbook(tempFile);
Worksheet sheet = workbook.Worksheets[0];
Workbook sourceWorkbook = new Workbook(sourceFile);
Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
int valueRow = 0;
for (int i = 0; i < cellsNC.MaxRow; i++)
{
if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + 2;
break;
}
if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
break;
}
}
Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":H" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:F5");
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:F4");
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("）", ")");
}
if (string.IsNullOrEmpty(Level2Name))
{
Level2Name = Level1Name;
}
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;
// 增加
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 from ( select BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGHTBXHDM, BGQTBXHDM from JCTJB_GQ where XZQTZLX<>'2' and XZQTZLX<>'3' )A WHERE(bgh LIKE '{0}%') AND(BGHTBXHDM = '{1}') AND((bgq NOT LIKE '{0}%') OR(BGQTBXHDM <> '{1}')))A ", cell.Level2Name, cell.Level1Name);
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 from ( select BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGHTBXHDM, BGQTBXHDM from JCTJB_GQ where XZQTZLX<>'2' and XZQTZLX<>'3' )A WHERE XZQTZLX<>'2' and (bgq LIKE '{0}%') AND(BGQTBXHDM = '{1}') AND((bgh NOT LIKE '{0}%') OR(BGHTBXHDM <> '{1}')))A", cell.Level2Name, cell.Level1Name);
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 BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ
from (select
case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from (select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from JCTJB_GQ where XZQTZLX in ('1', '3'))A)A
WHERE(DLBM LIKE '{0}%') AND(TBXHDM = '{1}')
)A ", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') and BGQCZCSXM<>'203' AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
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.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:F11");
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("*林区范围内种植园用地汇总统计表.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 + ")林区范围内园地汇总统计表.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\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 + ":H" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:F5");
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:F4");
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("）", ")");
}
if (string.IsNullOrEmpty(Level2Name))
{
Level2Name = Level1Name;
}
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;
// 增加
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 from ( select BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGHTBXHDM, BGQTBXHDM from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') )A WHERE(bgh LIKE '{0}%') AND(BGHTBXHDM = '{1}') AND((bgq NOT LIKE '{0}%') OR(BGQTBXHDM <> '{1}')))A ", cell.Level2Name, cell.Level1Name);
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 from ( select BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGHTBXHDM, BGQTBXHDM from JCTJB_GQ where XZQTZLX not in ('1','2','3','4') )A WHERE(bgq LIKE '{0}%') AND(BGQTBXHDM = '{1}') AND((bgh NOT LIKE '{0}%') OR(BGHTBXHDM <> '{1}')))A", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
// "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level2Name, cell.Level1Name);
sql = string.Format(@"select SUM(BGMJ) from
(
select case when XZQTZLX = '1' THEN bgq else bgh END DLBM,case when XZQTZLX = '1' THEN BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ
from (select
case when BGQCZCSXM = '空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from (select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from JCTJB_GQ where XZQTZLX in ('1', '3'))A)A
WHERE(DLBM LIKE '{0}%') AND(TBXHDM = '{1}')
)A ", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
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.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:F11");
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 = 12;
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 == "合计") continue;
// 减少
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 from ( select BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, BGHDLBM, substr(BGHCZCSXM, 1, 3) BGHCZCSXM, BGMJ, XZQTZLX, BGHTBXHDM, BGQTBXHDM from JCTJB_GQ)A WHERE(bgq LIKE '{0}%') AND(BGQTBXHDM = '{1}') AND((bgh NOT LIKE '{0}%') OR(BGHTBXHDM <> '{1}')))A", cell.Level2Name, cell.Level1Name);
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 + "表 8-林区范围内种植园用地变化统计表 .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 + ":G" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:F5");
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:F4");
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("）", ")");
}
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;
// 增加
sql = string.Format("SELECT SUM(BGMJ) FROM JCTJB_GQ " +
"WHERE (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
"AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}'))", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
"AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level2Name, cell.Level1Name);
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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) and (XZQTZLX='2' or XZQTZLX='4')", cell.Level2Name, cell.Level1Name);
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 = 9;
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.Format("SELECT ID FROM JCTJB_GQ " +
"WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND XZQTZLX in ('2','4')", cell.Level2Name, cell.Level1Name);
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.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:F11");
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 = 9;
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 == "合计") continue;
// 减少
sql = string.Format("SELECT ID FROM JCTJB_GQ " +
"WHERE (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{1}')) AND XZQTZLX='0'", cell.Level2Name, cell.Level1Name);
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
{
}
}
}
}