c#读取excel导入到数据库中
读取excel
/// <summary>
/// 从选择的excel导入
/// </summary>
/// <returns></returns>
public DataSet ImportExcel()
{
//文件路径
string ExcelName = ofd_in.FileName;
string strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelName + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1';";//连接excel文件的字符串
if (ExcelName == null)
{
return null;
}
OleDbConnection odcon = new OleDbConnection(strcon);//建立连接
odcon.Open();//打开连接
System.Data.DataTable sTable = odcon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
//Sheets Name
string tableName = sTable.Rows[0][2].ToString().Trim();
if (tableName == "")
{
return null;
}
else
{
tableName = "[" + tableName + "]";
}
OleDbDataAdapter odda = new OleDbDataAdapter("select * from " + tableName, odcon);
DataSet ds = new DataSet();
try
{
odda.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return ds;
}
为了解决导入超时问题，把逻辑写到webserice中，把dataset传到webservice中，同时把每1000条数据提交一次到数据库中，返回字符串到客户端再导出未excel.
try
{
CouponDetail ltw = new CouponDetail();
string[] StrArr = new string[ds.Tables[0].Rows.Count];
int icnt = ds.Tables[0].Rows.Count;
ltw.CouCpnSeq = strCpnseq;
string tmpsql = "select startdate,enddate from gcmis.crm_coupon where cpnseq='" + strCpnseq + "'";
DataSet dsdate = OraHelper.Adapter(OraHelper.CONN_STRING, CommandType.Text, tmpsql, null);
//根据会员数量分几次批量提交（1000个提交一次）
int itmp = icnt / 1000;
for (int j = 0; j < itmp + 1; j++)
{
using (OracleConnection conn = OraHelper.PrepareConn())
{
using (OracleTransaction trans = conn.BeginTransaction())
{
#region for 循环
//中间变量
int tmpcnt = 0;
if (icnt < 1000 || j == itmp) //(j + 1)*1000
{
tmpcnt = icnt;
}
else
{
tmpcnt = (j + 1) * 1000;
}
//for循环
for (int i = j * 1000; i < tmpcnt; i++)
{
//string tmpsql = "select Crm_CouponDetail_CpnDSeq.nextval from dual";
//DataSet dscpnd = OraHelper.Adapter(OraHelper.CONN_STRING, CommandType.Text, tmpsql, null);
//第一行为标题跳出循环
if (i == 0)
{
continue;
}
tmpsql = "select name from crm_customer cm,mis_member mb ";
tmpsql += " where cm.customersn=mb.customersn ";
tmpsql += " and memberid='" + ds.Tables[0].Rows[i][0].ToString() + "'";
DataSet dsname = OraHelper.Adapter(OraHelper.CONN_STRING, CommandType.Text, tmpsql, null);
if (dsname.Tables[0].Rows.Count > 0 && dsdate.Tables[0].Rows.Count > 0)
{
#region 会员卡号有效，执行插入操作
//判断是否激活，'是'不需要绑定时间，'否'需要绑定时间
if (ds.Tables[0].Rows[i][1].ToString() == "是")
{
#region 是激活
//生成随机优惠券编码
string strCpnCode = string.Empty;
while (true)
{
string dt = DateTime.Today.ToString("yyMMdd");
Random ran = new Random();
string number = ran.Next(10000000, 99999999).ToString();
strCpnCode = dt + number;
//判断数据库中是否有生成的优惠券编码
tmpsql = "select count(*) from crm_coupondetail where cpncode='" + strCpnCode + "'";
DataSet dscode = OraHelper.Adapter(OraHelper.CONN_STRING, CommandType.Text, tmpsql, null);
//不存在跳出循环，存在的话重新生成
if (dscode.Tables[0].Rows[0][0].ToString() == "0")
{
//之前生成的优惠券中含有现生成的优惠券编码
if (tmpCode.IndexOf(strCpnCode) > 0)
{
//找到重复的，重新生成
continue;
}
else
{
tmpCode += strCpnCode + ",";
//未找到重复的，跳出循环，该编码可以使用
break;
}
}
}
ltw.CouCpnSeq = strCpnseq;
ltw.CpnCode = strCpnCode;
ltw.MemberID = ds.Tables[0].Rows[i][0].ToString();
ltw.Member = dsname.Tables[0].Rows[0][0].ToString();
ltw.GeneratedOn = DateTime.Now.ToString();
if (dsdate.Tables[0].Rows[0]["startdate"].ToString() != "")
{
ltw.Startdate = DateTime.Parse(dsdate.Tables[0].Rows[0]["startdate"].ToString());
}
else
{
ltw.Startdate = DateTime.Parse(DateTime.Now.ToShortDateString());
}
if (dsdate.Tables[0].Rows[0]["enddate"].ToString() != "")
{
ltw.Enddate = DateTime.Parse(dsdate.Tables[0].Rows[0]["enddate"].ToString());
}
else
{
ltw.Enddate = DateTime.Parse(DateTime.Now.ToShortDateString());
}
ltw.IFACTIVE = "1";
CouponMgr cm = new CouponMgr(null, trans);
ir = cm.InsertCouponDetail(ltw);
//插入不成功，取不成功的memberid
if (ir <= 0)
{
tmpMemberid += ds.Tables[0].Rows[i][0].ToString() + ",";
}
#endregion
}
else
{
#region 不激活
//生成随机优惠券编码
string strCpnCode = string.Empty;
while (true)
{
string dt = DateTime.Today.ToString("yyyyMMdd");
Random ran = new Random();
string number = ran.Next(10000000, 99999999).ToString();
strCpnCode = dt + number;
//判断数据库中是否有生成的优惠券编码
tmpsql = "select count(*) from crm_coupondetail where cpncode='" + strCpnCode + "'";
DataSet dscode = OraHelper.Adapter(OraHelper.CONN_STRING, CommandType.Text, tmpsql, null);
//不存在跳出循环，存在的话重新生成
if (dscode.Tables[0].Rows[0][0].ToString() == "0")
{
//之前生成的优惠券中含有现生成的优惠券编码
if (tmpCode.IndexOf(strCpnCode) > 0)
{
//找到重复的，重新生成
continue;
}
else
{
tmpCode += strCpnCode + ",";
//未找到重复的，跳出循环，该编码可以使用
break;
}
}
}
ltw.CouCpnSeq = strCpnseq;
ltw.CpnCode = strCpnCode;
ltw.MemberID = ds.Tables[0].Rows[i][0].ToString();
ltw.Member = dsname.Tables[0].Rows[0][0].ToString();
ltw.BandingOn = DateTime.Now.ToString();
ltw.GeneratedOn = DateTime.Now.ToString();
if (dsdate.Tables[0].Rows[0]["startdate"].ToString() != "")
{
ltw.Startdate = DateTime.Parse(dsdate.Tables[0].Rows[0]["startdate"].ToString());
}
else
{
ltw.Startdate = DateTime.Parse(DateTime.Now.ToShortDateString());
}
if (dsdate.Tables[0].Rows[0]["enddate"].ToString() != "")
{
ltw.Enddate = DateTime.Parse(dsdate.Tables[0].Rows[0]["enddate"].ToString());
}
else
{
ltw.Enddate = DateTime.Parse(DateTime.Now.ToShortDateString());
}
ltw.IFACTIVE = "0";
CouponMgr cm = new CouponMgr(null, trans);
ir = cm.InsertCouponDetail(ltw);
//插入不成功，取不成功的memberid
if (ir <= 0)
{
tmpMemberid += ds.Tables[0].Rows[i][0].ToString() + ",";
}
#endregion
}
#endregion
}
else
{
//会员卡号无效记录下错误卡号返回到客户端，生成错误报表
//tmptb.Rows.Add(new object[] { ds.Tables[0].Rows[i-1][0].ToString() });
tmpMemberid += ds.Tables[0].Rows[i][0].ToString() + ",";
}
}
#endregion
trans.Commit();
}
}
}
}catch(Exception ex)
{
ir = -1;
throw ex;
}
//赋值到传出参数
strMemberid = tmpMemberid;
return ir;
