c# 读取导入的excel文件,循环批量处理数据
dt = FM_HR_ShiftMaintenanceManager.GetCsvToDataTable(strConn, excelName,"XJSQMonthlyImportExcelData"); int iCount = dt.Rows.Count; StringBuilder sb = new StringBuilder(); if (dt != null) { int loop = Convert.ToInt32(Math.Floor((iCount / 100) * 1.0)); DlSoft.Liveflow.Common.Log.LogHelper.Logger.Write("loop :"+loop); if (loop < 1) { for (int j = 0; j < iCount; j++) { if (!string.IsNullOrEmpty(dt.Rows[j][1].ToString())) { sb.Append(dt.Rows[j][0].ToString() + "@@" + dt.Rows[j][1].ToString() + "$$"); } } ssql = string.Format(@"exec usp_UpdateXiuJiaSQMonthlyStatus '{0}'", sb.ToString()); int t = dbt.ExecuteNonQuery(ssql); } else { for (int k = 0; k < loop; k++) { for (int l = 0; l < 100; l++) { if (!string.IsNullOrEmpty(dt.Rows[l + k * 100][1].ToString())) { sb.Append(dt.Rows[l + k * 100][0].ToString() + "@@" + dt.Rows[l + k * 100][1].ToString() + "$$"); } } if (!string.IsNullOrEmpty(sb.ToString())) { ssql = string.Format(@"exec usp_UpdateXiuJiaSQMonthlyStatus '{0}'", sb.ToString()); int tt = dbt.ExecuteNonQuery(ssql); sb.Remove(0, sb.Length); } } for (int m = 0; m < iCount - loop * 100; m++) { if (!string.IsNullOrEmpty(dt.Rows[m + loop * 100][1].ToString())) { sb.Append(dt.Rows[m + loop * 100][0].ToString() + "@@" + dt.Rows[m + loop * 100][1].ToString() + "$$"); } } if (!string.IsNullOrEmpty(sb.ToString())) { ssql = string.Format(@"exec usp_UpdateXiuJiaSQMonthlyStatus '{0}'", sb.ToString()); int ttt = dbt.ExecuteNonQuery(ssql); } } }
获取excel数据
/// <summary> /// 获取excel数据导入.xlsxs /// </summary> /// <param name="strConn">连接名称</param> /// <param name="path">exec地址</param> /// <param name="excelName">excel名字</param> /// <param name="FillName">exce表自定义名字</param> /// <returns></returns> public static DataTable GetCsvToDataTable(string strConn, string excelName, string FillName) { OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); DataSet ds = new DataSet(); OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("select * from [" + excelName + "$]"), conn); //这里的表名参数,就是 CSV的完整文件名 odda.Fill(ds, FillName); conn.Close(); return ds.Tables[0]; }
作者:RichardCui
出处:https://www.cnblogs.com/yachao1120/
本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。该文章也同时发布在我的独立博客中-RichardCuiBlog。