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 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; } /// /// 获取列 /// /// /// public static Dictionary Get_Colunms(DataTable dic) { Dictionary dics = new Dictionary(); foreach (DataColumn item in dic.Columns) { dics.Add(item.ColumnName, "Varchar"); } return dics; } /// /// 创建表 /// /// /// /// protected void CreateTableSql(IRDBHelper rdbHelper, string tableName, Dictionary 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 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 } }