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.
408 lines
23 KiB
408 lines
23 KiB
using ExcelDataReader; |
|
using KGIS.Framework.DBOperator; |
|
using KGIS.Framework.Utils; |
|
using KGIS.Framework.Utils.ExtensionMethod; |
|
using System; |
|
using System.Collections.Generic; |
|
using System.Data; |
|
using System.IO; |
|
using System.Linq; |
|
using System.Text; |
|
using WpfApp1.Model; |
|
|
|
namespace WpfApp1.Helper |
|
{ |
|
public class StatisticalReportClass |
|
{ |
|
public bool ExportTDLYXZBGB(string saveCurrentPath, AnalysisExport analysisExport, string temppath) |
|
{ |
|
IRDBHelper dbHelper = null; |
|
string ncsjExcelPath = string.Empty; |
|
FileInfo fileInfo = new FileInfo(analysisExport.JCSJExcelName); |
|
string fileName = "土地利用现状分类面积汇总表";//analysisExport.JCSJExcelName.Replace("(", "").Replace(")", "").Replace(analysisExport.XZQDM, "").Replace(fileInfo.Extension, ""); |
|
#region 数据赋值 |
|
string tempPath = AppDomain.CurrentDomain.BaseDirectory + "Template\\土地利用现状变更表.xlsx"; |
|
File.Copy(tempPath, saveCurrentPath, true); |
|
DataTable resultdataTable = new DataTable(); |
|
dbHelper = RDBFactory.CreateDbHelper("Data Source=" + $"{temppath}\\BGTJ.sqlite", DatabaseType.SQLite); |
|
DataTable dt = dbHelper.ExecuteDatatable("TDLYXZBGB", @" select Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq,bgh,SUM(BGMJ)BGMJ from ( |
|
select bgq,bgh,SUM(BGMJ)BGMJ from |
|
( |
|
select |
|
case when bgq in('1001', '1002', '1003', '1007', '1008', '1009', '1109' )or substr(bgq, 1, 2)='20' THEN 'JSYD' ELSE bgq END bgq, |
|
case when bgh in('1001', '1002', '1003', '1007', '1008', '1009', '1109')or substr(bgh, 1, 2)='20' THEN 'JSYD' ELSE bgh END bgh , BGMJ from (select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ from (select substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ )A )A |
|
|
|
)A WHERE bgq<>bgh and (bgq='JSYD' OR bgh='JSYD' ) GROUP BY bgq,bgh |
|
union ALL |
|
select bgq,bgh,SUM(BGMJ)BGMJ from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ from (select substr(BGQDLBM, 1, 4) BGQDLBM,substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ )A |
|
)A WHERE bgq<>bgh GROUP BY bgq,bgh |
|
)A GROUP BY bgq,bgh |
|
)A left JOIN TDLYXZBGB B ON a.bgh = B.bgh and a.bgq = B.bgq where Cells_X NOTNULL and Cells_Y NOTNULL ", 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}' ", analysisExport.XZQDM, 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, '2' Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '3' Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '4' Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '5' Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '6' Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '7' Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '8' Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '9' Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '10' Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '11' Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '12' Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '13' Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '14' Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '15' Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '16' Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '17' Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '18' Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '19' Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '20' Cells_Y, round(column3,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '21' Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '22' Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '23' Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '24' Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '25' Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '26' Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '27' Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '28' Cells_Y, round(column43,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '29' Cells_Y, round(column44,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '30' Cells_Y, round(column45,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '31' Cells_Y, round(column46,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '32' Cells_Y, round(column47,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '33' Cells_Y, round(column48,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '34' Cells_Y, round(column49,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '36' Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '37' Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '38' Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '39' Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '40' Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '41' Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '42' Cells_Y, round(column35,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '43' Cells_Y, round(column36,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '44' Cells_Y, round(column37,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '45' Cells_Y, round(column38,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '46' Cells_Y, round(column39,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '47' Cells_Y, round(column40,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '48' Cells_Y, round(column41,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '49' Cells_Y, round(column42,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '50' Cells_Y, round(column50,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '51' Cells_Y, round(column51,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '52' Cells_Y, round(column52,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '53' Cells_Y, round(column53,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '54' Cells_Y, round(column54,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '55' Cells_Y, round(column55,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '56' Cells_Y, round(column56,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '57' Cells_Y, round(column57,4) BGMJ FROM {2} WHERE {1}='{0}' ", analysisExport.XZQDM, ColumnName, fileName), true); |
|
} |
|
else if (ColumnName == "column2") |
|
{ |
|
dt = dbHelper.ExecuteDatatable("SNMMJ", string.Format(@" |
|
select '4' Cells_X, '2' Cells_Y, round(column12,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '3' Cells_Y, round(column13,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '4' Cells_Y, round(column14,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '5' Cells_Y, round(column15,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '6' Cells_Y, round(column16,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '7' Cells_Y, round(column17,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '8' Cells_Y, round(column18,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '9' Cells_Y, round(column19,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '10' Cells_Y, round(column20,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '11' Cells_Y, round(column21,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '12' Cells_Y, round(column22,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '13' Cells_Y, round(column23,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '14' Cells_Y, round(column24,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '15' Cells_Y, round(column25,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '16' Cells_Y, round(column26,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '17' Cells_Y, round(column27,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '18' Cells_Y, round(column28,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '19' Cells_Y, round(column29,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '20' Cells_Y, round(column4,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '21' Cells_Y, round(column5,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '22' Cells_Y, round(column6,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '23' Cells_Y, round(column7,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
SELECT '4' Cells_X, '24' Cells_Y, round(column8,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '25' Cells_Y, round(column9,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '26' Cells_Y, round(column10,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '27' Cells_Y, round(column11,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '28' Cells_Y, round(column44,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '29' Cells_Y, round(column45,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '30' Cells_Y, round(column46,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '31' Cells_Y, round(column47,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '32' Cells_Y, round(column48,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '33' Cells_Y, round(column49,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '34' Cells_Y, round(column50,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '36' Cells_Y, round(column30,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '37' Cells_Y, round(column31,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '38' Cells_Y, round(column32,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '39' Cells_Y, round(column33,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '40' Cells_Y, round(column34,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '41' Cells_Y, round(column35,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '42' Cells_Y, round(column36,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '43' Cells_Y, round(column37,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '44' Cells_Y, round(column38,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '45' Cells_Y, round(column39,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '46' Cells_Y, round(column40,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '47' Cells_Y, round(column41,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '48' Cells_Y, round(column42,4) BGMJ FROM {2} WHERE {1}='{0}' UNION ALL |
|
select '4' Cells_X, '49' Cells_Y, round(column43,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}' ", analysisExport.XZQDM, ColumnName, fileName), true); |
|
} |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调入 |
|
dt = dbHelper.ExecuteDatatable("XZQDR", string.Format(@" select '5' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select case when XZQTZLX='1' then bgq ELSE bgh end dlbm, SUM(BGMJ) BGMJ from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,XZQTZLX |
|
from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ |
|
)A WHERE XZQTZLX in ('1','3') |
|
)A GROUP BY dlbm |
|
)a left join TDLYXZBGB B on a.DLBM= B.bgh where b.Cells_X='12' "), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
#endregion |
|
|
|
#region 行政区调出 |
|
dt = dbHelper.ExecuteDatatable("XZQDC", string.Format(@" select '6' Cells_X,Cells_Y,BGMJ from |
|
( |
|
select bgq dlbm, SUM(BGMJ) BGMJ from |
|
( |
|
select case when BGQCZCSXM='空' then BGQDLBM else BGQCZCSXM end bgq,case when BGHCZCSXM = '空' then BGHDLBM else BGHCZCSXM end bgh,BGMJ,XZQTZLX |
|
from |
|
( |
|
select substr(BGQDLBM, 1, 4) BGQDLBM, substr(BGQCZCSXM, 1, 3) BGQCZCSXM, substr(BGHDLBM, 1, 4) BGHDLBM,substr(BGHCZCSXM, 1, 3) BGHCZCSXM,BGMJ, XZQTZLX from JCTJB_GQ |
|
)A WHERE XZQTZLX in ('2','4') |
|
)A GROUP BY dlbm |
|
)a left join TDLYXZBGB B on a.DLBM= B.bgh where b.Cells_X='12' "), true); |
|
if (dt != null && dt.Rows.Count > 0) |
|
{ |
|
resultdataTable.Merge(dt); |
|
} |
|
CellHelper.SetFileToDisk(resultdataTable, saveCurrentPath, 0); |
|
#endregion |
|
CellHelper.Import(saveCurrentPath); |
|
return true; |
|
} |
|
|
|
#region 导入表格数据 |
|
public void ExcelToDataTable(string temppath, string NCDataDir, string xzqdm) |
|
{ |
|
DataTable dataTable = null; |
|
IRDBHelper rdbHelper = null; |
|
try |
|
{ |
|
string dbPath = temppath + @"\BGTJ.sqlite"; |
|
rdbHelper = RDBFactory.CreateDbHelper("Data Source=" + dbPath, DatabaseType.SQLite); |
|
FileInfo file = new FileInfo(NCDataDir); |
|
var tableName = "土地利用现状分类面积汇总表"; |
|
using (var streamData = File.Open(file.FullName, FileMode.Open, FileAccess.Read)) |
|
{ |
|
using (var readerData = ExcelReaderFactory.CreateReader(streamData)) |
|
{ |
|
var result = readerData.AsDataSet(); |
|
dataTable = result.Tables[0]; |
|
Dictionary<string, string> dics = Get_Colunms(dataTable); |
|
CreateTableSql(rdbHelper, tableName, dics); |
|
if (dataTable != null && dataTable.Rows.Count > 0) |
|
{ |
|
if (file.Name.StartsWith("土地利用现状分类面积汇总表")) |
|
{ |
|
DataTable table = dataTable.Clone(); |
|
var dataRow = dataTable.Select($" column2='{xzqdm}' "); |
|
if (dataRow != null && dataRow.Length > 0) |
|
table.ImportRow(dataRow[0]); |
|
dataTable = table; |
|
} |
|
else |
|
{ |
|
dataTable = DtSelectTop(10, dataTable); |
|
} |
|
Insert_sql(dataTable, dics, rdbHelper, tableName); |
|
} |
|
} |
|
} |
|
|
|
} |
|
catch (Exception ex) |
|
{ |
|
LogAPI.Debug("读取Excel数据错误(成果输出):" + ex.Message); |
|
LogAPI.Debug("读取Excel数据错误(成果输出):" + ex.StackTrace); |
|
} |
|
finally |
|
{ |
|
if (dataTable != null) |
|
{ |
|
dataTable.Clear(); |
|
dataTable = null; |
|
} |
|
} |
|
} |
|
public static DataTable DtSelectTop(int TopItem, DataTable oDT) |
|
{ |
|
if (oDT.Rows.Count < TopItem) return oDT; |
|
|
|
DataTable NewTable = oDT.Clone(); |
|
DataRow[] rows = oDT.Select("1=1"); |
|
for (int i = 0; i < TopItem; i++) |
|
{ |
|
NewTable.ImportRow((DataRow)rows[i]); |
|
} |
|
return NewTable; |
|
} |
|
/// <summary> |
|
/// 获取列 |
|
/// </summary> |
|
/// <param name="dic"></param> |
|
/// <returns></returns> |
|
public static Dictionary<string, string> Get_Colunms(DataTable dic) |
|
{ |
|
Dictionary<string, string> dics = new Dictionary<string, string>(); |
|
foreach (DataColumn item in dic.Columns) |
|
{ |
|
dics.Add(item.ColumnName, "Varchar"); |
|
} |
|
return dics; |
|
} |
|
/// <summary> |
|
/// 创建表 |
|
/// </summary> |
|
/// <param name="rdbHelper"></param> |
|
/// <param name="tableName"></param> |
|
/// <param name="table"></param> |
|
protected void CreateTableSql(IRDBHelper rdbHelper, string tableName, Dictionary<string, string> table) |
|
{ |
|
string result = string.Empty; |
|
try |
|
{ |
|
string temp1 = "CREATE TABLE if not exists " + tableName + "("; |
|
foreach (var item in table) |
|
{ |
|
temp1 = temp1 + item.Key.Trim().ToLower() + " " + item.Value + ","; |
|
} |
|
result = temp1.TrimEnd(',') + ");"; |
|
rdbHelper.ExecuteNonQueryWithException($" DROP TABLE if exists {tableName}"); |
|
rdbHelper.ExecuteNonQueryWithException(result); |
|
} |
|
catch (Exception ex) |
|
{ |
|
throw ex; |
|
} |
|
} |
|
|
|
public void Insert_sql(DataTable dic, Dictionary<string, string> dics, IRDBHelper SqlHelper, string Table) |
|
{ |
|
#region 获取所有字段 |
|
StringBuilder columns = new StringBuilder(); |
|
foreach (var item in dics) |
|
{ |
|
columns.Append("," + item.Key); |
|
} |
|
#endregion |
|
|
|
#region 数据入库 |
|
int CycleIndex = 0; |
|
StringBuilder insertsql = new StringBuilder(); |
|
StringBuilder strsql = new StringBuilder(); |
|
StringBuilder stringBuilder = new StringBuilder(); |
|
var count = dic.Rows.Count; |
|
foreach (DataRow item0 in dic.Rows) |
|
{ |
|
CycleIndex++; |
|
StringBuilder data = new StringBuilder();//insert语句插入字段 |
|
foreach (var item1 in dics) |
|
{ |
|
if (item0[item1.Key].ToString().Trim() == "") |
|
{ |
|
data.Append(",null"); |
|
} |
|
else |
|
{ |
|
data.Append((",'" + item0[item1.Key].ToString().Trim().Replace("'", "''").Replace("\n", "").Replace(" ", "").Replace("\t", "").Replace("\r", "") + "'")); |
|
} |
|
} |
|
insertsql.Append(string.Format("insert into " + Table + "({0})values({1});", columns.ToString().Substring(1), data.ToString().Substring(1))); |
|
#region 提交数据 |
|
if (count < 10000) |
|
{ |
|
if (CycleIndex < count) |
|
{ |
|
stringBuilder.Append(insertsql.ToString()); |
|
insertsql.Clear(); |
|
} |
|
else if (CycleIndex == count) |
|
{ |
|
stringBuilder.Append(insertsql.ToString()); |
|
var num = SqlHelper.ExecuteNonQuery(stringBuilder.ToString(), CommandType.Text); |
|
if (num < 0 && stringBuilder.ToString() != "") |
|
return; |
|
} |
|
} |
|
else |
|
{ |
|
strsql.Append(insertsql.ToString()); |
|
insertsql.Clear(); |
|
} |
|
if (CycleIndex == 10000) |
|
{ |
|
try |
|
{ |
|
var num = SqlHelper.ExecuteNonQuery(strsql.ToString(), CommandType.Text); |
|
if (num < 0 && strsql.ToString() != "") |
|
return; |
|
insertsql.Clear(); |
|
strsql.Clear(); |
|
count = count - CycleIndex; |
|
CycleIndex = 0; |
|
} |
|
catch (Exception exc) |
|
{ |
|
throw new Exception(exc.Message); |
|
} |
|
finally |
|
{ |
|
GC.Collect(); |
|
} |
|
} |
|
#endregion |
|
} |
|
#endregion |
|
} |
|
#endregion |
|
|
|
} |
|
}
|
|
|