wangguanjie
/
NDBGJK5.0
2
0
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.Plugin.YJJK/ZLBBReport/ExportGYYDALXHZBHTJB.cs

829 lines
39 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.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 ExportGYYDALXHZBHTJB
{
/// <summary>
/// 执行导出
/// </summary>
/// <param name="savePath">保存位置</param>
/// <param name="baseReportPath">获取年初表位置</param>
/// <returns>是否成功</returns>
public bool Export(string savePath, string baseReportPath, IRDBHelper dbHelper)
{
try
{
DataTable dt = null;
if (string.IsNullOrWhiteSpace(savePath))
{
MessageHelper.ShowError("存储位置错误请重试!");
return false;
}
string sourceFile = string.Empty;
DirectoryInfo dir = new DirectoryInfo(baseReportPath);
//FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("*工业用地按类型汇总统计表.xlsx"), SearchOption.AllDirectories);
FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")工业用地按类型汇总统计表.xlsx"), SearchOption.AllDirectories);
if (fileSet != null && fileSet.Length > 0)
{
sourceFile = fileSet[0].FullName;
}
else
{
fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")工业用地按类型汇总统计表.xls"), SearchOption.AllDirectories);
if (fileSet != null && fileSet.Length > 0)
{
sourceFile = fileSet[0].FullName;
}
}
if (!System.IO.File.Exists(sourceFile))
{
//Common.Helper.MessageHelper.ShowError("年初基础表不存在,请重试!");
//Common.Utility.LogAPI.Debug("工业用地按类型汇总统计表 不能被 成功 找到\r\n");
return false;
}
//Common.Utility.LogAPI.Debug("工业用地按类型汇总统计表 能被 成功 找到\r\n");
// 地类字典
Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();
// 读取模板
string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
string tempFile = tempPath + "工业用地按类型汇总变化统计表.xlsx";
string targetFile = savePath;
Workbook workbook = new Workbook(tempFile);
Worksheet sheet = workbook.Worksheets[0];
Workbook sourceWorkbook = new Workbook(sourceFile);
Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
int valueRow = 0;
for (int i = 0; i < cellsNC.MaxRow; i++)
{
if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + 2;
break;
}
if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
break;
}
}
Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":J" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:H5");
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:H4");
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;
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 JCTJB_GQ " +
"WHERE (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
"AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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 (select case when XZQTZLX = '1' THEN BGQDLBM else BGHDLBM END DLBM,case when XZQTZLX = '1' THEN BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ from(select BGQDLBM,BGQCZCSXM,BGHDLBM,BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from JCTJB_GQ where XZQTZLX in ('1', '3'))A WHERE DLBM LIKE '{0}%' AND TBXHDM = '{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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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;
double ncmj = 0;
if (sheet.Cells[4, cell.CellColumn].Value.GetType().FullName == "System.String")
{
ncmj = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value);
}
else
{
ncmj = sheet.Cells[4, cell.CellColumn].DoubleValue;
}
sheet.Cells[7, cell.CellColumn].Value = ncmj + 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:H11");
for (int i = 0; i < sourceRange.ColumnCount; i++)
{
for (int j = 0; j < 6; j++)
{
string s = dataRange[j, i].Value == null ? "0" : dataRange[j, i].Value.ToString();
dataRange[j, i].PutValue(s, true);
Style style = dataRange[j, i].GetStyle();
style.Number = 2;
dataRange[j, i].SetStyle(style);
}
dataRange[6, i].Value = dataRange[3, i].DoubleValue + dataRange[4, i].DoubleValue - dataRange[5, i].DoubleValue;
}
#endregion
// 保存EXCEL
workbook.Save(targetFile);
return true;
}
catch (Exception e)
{
throw e;
}
finally
{
}
}
public BBPCModel Export(string savePath, string baseReportPath, IRDBHelper dbHelper, ref List<BBPCModel> pcModelList)
{
BBPCModel result = null;
try
{
DataTable dt = null;
if (string.IsNullOrWhiteSpace(savePath))
{
MessageHelper.ShowError("存储位置错误请重试!");
return result;
}
string sourceFile = string.Empty;
DirectoryInfo dir = new DirectoryInfo(baseReportPath);
FileInfo[] fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")工业用地按类型汇总统计表.xlsx"), SearchOption.AllDirectories);
if (fileSet != null && fileSet.Length > 0)
{
sourceFile = fileSet[0].FullName;
}
else
{
fileSet = dir.GetFiles(System.IO.Path.GetFileName("(" + (MapsManager.Instance.MapService.GetProjectInfo() as ProjectInfo).CODE + ")工业用地按类型汇总统计表.xls"), SearchOption.AllDirectories);
if (fileSet != null && fileSet.Length > 0)
{
sourceFile = fileSet[0].FullName;
}
}
if (!System.IO.File.Exists(sourceFile))
{
return result;
}
// 地类字典
Dictionary<string, string> dic2to1 = LandTypeDicHelper.GetLevel2ToLevel1LandTypeDic();
Dictionary<string, string> dic1 = LandTypeDicHelper.GetLevel1LandTypeDic();
Dictionary<string, double[]> dicArea = new Dictionary<string, double[]>();
// 读取模板
string tempPath = SysAppPath.GetCurrentAppPath() + @"\TempalateReports\BGTJ\";
string tempFile = tempPath + "工业用地按类型汇总变化统计表.xlsx";
string targetFile = savePath;
Workbook workbook = new Workbook(tempFile);
Worksheet sheet = workbook.Worksheets[0];
Workbook sourceWorkbook = new Workbook(sourceFile);
Worksheet sourceSheet = sourceWorkbook.Worksheets[0];
Cells cellsNC = sourceWorkbook.Worksheets[0].Cells;
int valueRow = 0;
for (int i = 0; i < cellsNC.MaxRow; i++)
{
if (cellsNC[i, 1].IsMerged == false && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + 2;
break;
}
if (cellsNC[i, 1].IsMerged == true && cellsNC[i, 1].Value != null && cellsNC[i, 1].Value.ToString() == "名称")
{
valueRow = i + cellsNC[i, 1].GetMergedRange().RowCount + 1;
break;
}
}
Range sourceRange = sourceSheet.Cells.CreateRange("D" + valueRow + ":J" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:H5");
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:H4");
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;
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 JCTJB_GQ " +
"WHERE XZQTZLX not in ('1','2','3','4') and (BGHDLBM LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
"AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{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 XZQTZLX not in ('1','2','3','4') and (BGQDLBM LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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 LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
// "AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{1}')) and (XZQTZLX='1' or XZQTZLX='3')", cell.Level1Name, cell.Level2Name);
sql = string.Format(@"select SUM(BGMJ) from (select case when XZQTZLX = '1' THEN BGQDLBM else BGHDLBM END DLBM,case when XZQTZLX = '1' THEN BGQTBXHDM else BGHTBXHDM END TBXHDM, BGMJ from(select BGQDLBM,BGQCZCSXM,BGHDLBM,BGHCZCSXM, BGMJ, BGQTBXHDM, BGHTBXHDM, XZQTZLX from JCTJB_GQ where XZQTZLX in ('1', '3'))A WHERE DLBM LIKE '{0}%' AND TBXHDM = '{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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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;
double ncmj = 0;
if (sheet.Cells[4, cell.CellColumn].Value.GetType().FullName == "System.String")
{
ncmj = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value);
}
else
{
ncmj = sheet.Cells[4, cell.CellColumn].DoubleValue;
}
sheet.Cells[7, cell.CellColumn].Value = ncmj + 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:H11");
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 = 14;
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.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 + "表 10-工业用地按类型汇总变化统计表.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 + ":I" + valueRow);
Range dataRange = null;
#region 年初面积操作
// 年初面积操作
dataRange = sheet.Cells.CreateRange("B5:H5");
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:H4");
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.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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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 LIKE '{0}%') AND (BGHTBXHDM = '{1}') " +
"AND ((BGQDLBM NOT LIKE '{0}%') OR (BGQTBXHDM <> '{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 LIKE '{0}%') AND (BGQTBXHDM = '{1}') " +
"AND ((BGHDLBM NOT LIKE '{0}%') OR (BGHTBXHDM <> '{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;
double ncmj = 0;
if (sheet.Cells[4, cell.CellColumn].Value.GetType().FullName == "System.String")
{
ncmj = Convert.ToDouble(sheet.Cells[4, cell.CellColumn].Value);
}
else
{
ncmj = sheet.Cells[4, cell.CellColumn].DoubleValue;
}
sheet.Cells[7, cell.CellColumn].Value = ncmj + valueHR - valueHC;
#region 调整后年初面积负值
if (isTZMJPC && sheet.Cells[7, cell.CellColumn].DoubleValue < 0)
{
if (TZresult == null)
{
TZresult = new BBPCModel();
TZresult.ReportName = 11;
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.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.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:H11");
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);
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.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
{
}
}
}
}