正文
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];
}