protected void Page_Load(object sender, EventArgs e)
{
System.Data.DataTable dt = GetExcelDatatable("D:\\_ExportData.xlsx", "mapTable");
InsetData(dt);
}
/// <summary>
/// Excel数据导入Datable
/// </summary>
/// <param name="fileUrl"></param>
/// <param name="table"></param>
/// <returns></returns>
public System.Data.DataTable GetExcelDatatable(string fileUrl, string table)
{
//office2007之前 仅支持.xls
//const string cmdText = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;IMEX=1';";
//支持.xls和.xlsx，即包括office2010等版本的 HDR=Yes代表第一行是标题，不是数据；
const string cmdText = "Provider=Microsoft.Ace.OleDb.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
System.Data.DataTable dt = null;
//建立连接
OleDbConnection conn = new OleDbConnection(string.Format(cmdText, fileUrl));
try
{
//打开连接
if (conn.State == ConnectionState.Broken || conn.State == ConnectionState.Closed)
{
conn.Open();
}
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet名称
string sheetName = schemaTable.Rows[0]["TABLE_NAME"].ToString().Trim();
//查询sheet中的数据
string strSql = "select * from [" + sheetName + "]";
OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
DataSet ds = new DataSet();
da.Fill(ds, table);
dt = ds.Tables[0];
return dt;
}
catch (Exception exc)
{
throw exc;
}
finally
{
conn.Close();
conn.Dispose();
}
}
/// <summary>
/// 从System.Data.DataTable导入数据到数据库
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public int InsetData(System.Data.DataTable dt)
{
int i = 0;
int iMISId = 0;
string MemberType = "";
string Status = "";
string Prefix = "";
string FirstName = "";
string MiddleName = "";
string LastName = "";
string Organisation = "";
string Address1 = "";
string Address2 = "";
string Address3 = "";
string Town = "";
string County = "";
string PostCode = "";
string Country = "";
string CountryofOrigin = "";
string Region = "";
string IncomeLevel = "";
string MainEmail = "";
string AlternateEmail = "";
string AddressPurpose = "";
DateTime JoinDate = DateTime.Parse("1900-01-01 00:00:00");
string Gender = "";
DateTime DateofBirth = DateTime.Parse("1900-01-01 00:00:00");
decimal TotalFeeOwed = 0;
decimal TotalPaid = 0;
decimal TotalOutstanding = 0;
string DelegateSource = "";
string CompanyType = "";
int CompanyId = 0;
DateTime DateAdded = DateTime.Parse("1900-01-01 00:00:00"); ;
string Fax = "";
DateTime LastUpdated = DateTime.Parse("1900-01-01 00:00:00"); ;
DateTime MemberTypeChangeDate = DateTime.Parse("1900-01-01 00:00:00"); ;
DateTime PaidThrough = DateTime.Parse("1900-01-01 00:00:00"); ;
string PreviousMemberType = "";
string Website = "";
string WorkPhone = "";
DateTime RenewedThru = DateTime.Parse("1900-01-01 00:00:00"); ;
string ExtraNote = "";
DateTime SuspendedDate = DateTime.Parse("1900-01-01 00:00:00"); ;
decimal CreditAmount = 0;
decimal OpeningBalance = 0;
DateTime PaidThru = DateTime.Parse("1900-01-01 00:00:00"); ;
decimal PaymentAmount = 0;
decimal PreviousBalance = 0;
string ProductCode = "";
string ProductTitle = "";
string CompanyIpAddress = "";
string OldMemberType = "";
int OldMemberNumber = 0;
string RemovalCode = "";
string RemovalReason = "";
string SkypeAddress = "";
bool UseOriginCountry = false;
string CommitteePreference1 = "";
string CommitteePreference2 = "";
string CommitteePreference3 = "";
string CommitteePreference4 = "";
bool OfferTrialMembership = false;
foreach (DataRow dr in dt.Rows)
{
if (!string.IsNullOrEmpty(dr["iMIS Id"].ToString()))
{
iMISId = int.Parse(dr["iMIS Id"].ToString().Trim());
}
MemberType = dr["Member Type"].ToString().Trim().Replace("'", "''");
Status = dr["Status"].ToString().Trim().Replace("'", "''");
Prefix = dr["Prefix"].ToString().Trim().Replace("'", "''");
FirstName = dr["First Name"].ToString().Trim().Replace("'", "''");
MiddleName = dr["Middle Name"].ToString().Trim().Replace("'", "''");
LastName = dr["Last Name"].ToString().Trim().Replace("'", "''");
Organisation = dr["Organisation"].ToString().Trim().Replace("'", "''").Replace("'", "''");
Address1 = dr["Address 1"].ToString().Trim().Replace("'", "''");
Address2 = dr["Address 2"].ToString().Trim().Replace("'", "''");
Address3 = dr["Address 3"].ToString().Trim().Replace("'", "''");
Town = dr["Town"].ToString().Trim().Replace("'", "''");
County = dr["County"].ToString().Trim().Replace("'", "''");
PostCode = dr["Post Code"].ToString().Trim().Replace("'", "''");
Country = dr["Country"].ToString().Trim().Replace("'", "''");
CountryofOrigin = dr["Country of Origin"].ToString().Trim().Replace("'", "''");
Region = dr["Region"].ToString().Trim().Replace("'", "''");
IncomeLevel = dr["Income Level"].ToString().Trim().Replace("'", "''");
MainEmail = dr["Main Email"].ToString().Trim().Replace("'", "''");
AlternateEmail = dr["Alternate Email"].ToString().Trim().Replace("'", "''");
AddressPurpose = dr["Address Purpose"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Join Date"].ToString()))
{
JoinDate = DateTime.Parse(DateTime.ParseExact(dr["Join Date"].ToString().Trim(), "dd/MM/yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
Gender = dr["Gender"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Date of Birth"].ToString()))
{
DateofBirth = DateTime.Parse(DateTime.ParseExact(dr["Date of Birth"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(dr["Total Fee Owed"].ToString()))
{
TotalFeeOwed = decimal.Parse(dr["Total Fee Owed"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["Total Paid"].ToString()))
{
TotalPaid = decimal.Parse(dr["Total Paid"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["Total Outstanding"].ToString()))
{
TotalOutstanding = decimal.Parse(dr["Total Outstanding"].ToString().Trim());
}
DelegateSource = dr["Delegate Source"].ToString().Trim().Replace("'", "''");
CompanyType = dr["Company Type"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Company Id"].ToString()))
{
CompanyId = int.Parse(dr["Company Id"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["Date Added"].ToString()))
{
DateAdded = DateTime.Parse(DateTime.ParseExact(dr["Date Added"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
Fax = dr["Fax"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Member Type Change Date"].ToString()))
{
MemberTypeChangeDate = DateTime.Parse(DateTime.ParseExact(dr["Member Type Change Date"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(dr["Paid Through"].ToString()))
{
PaidThrough = DateTime.Parse(DateTime.ParseExact(dr["Paid Through"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
PreviousMemberType = dr["Previous Member Type"].ToString().Trim().Replace("'", "''");
Website = dr["Website"].ToString().Trim().Replace("'", "''");
WorkPhone = dr["WorkPhone"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["RenewedThru"].ToString()))
{
RenewedThru = DateTime.Parse(DateTime.ParseExact(dr["RenewedThru"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
ExtraNote = dr["ExtraNote"].ToString().Trim().Replace("'", "''").Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Suspended Date"].ToString()))
{
SuspendedDate = DateTime.Parse(DateTime.ParseExact(dr["Suspended Date"].ToString().Trim(), "dd/MM/yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(dr["Last Updated"].ToString()))
{
LastUpdated = DateTime.Parse(DateTime.ParseExact(dr["Last Updated"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(dr["CreditAmount"].ToString()))
{
CreditAmount = decimal.Parse(dr["CreditAmount"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["OpeningBalance"].ToString()))
{
OpeningBalance = decimal.Parse(dr["OpeningBalance"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["PaidThru"].ToString()))
{
PaidThru = DateTime.Parse(DateTime.ParseExact(dr["PaidThru"].ToString().Trim(), "dd/MM/yyyy HH:mm:ss", System.Globalization.CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(dr["PaymentAmount"].ToString()))
{
PaymentAmount = decimal.Parse(dr["PaymentAmount"].ToString().Trim());
}
if (!string.IsNullOrEmpty(dr["PreviousBalance"].ToString()))
{
PreviousBalance = decimal.Parse(dr["PreviousBalance"].ToString().Trim());
}
ProductCode = dr["ProductCode"].ToString().Trim().Replace("'", "''");
ProductTitle = dr["ProductTitle"].ToString().Trim().Replace("'", "''");
CompanyIpAddress = dr["Company Ip Address"].ToString().Trim().Replace("'", "''");
OldMemberType = dr["Old Member Type"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Old Member Number"].ToString()))
{
OldMemberNumber = int.Parse(dr["Old Member Number"].ToString().Trim());
}
RemovalCode = dr["Removal Code"].ToString().Trim().Replace("'", "''");
RemovalReason = dr["Removal Reason"].ToString().Trim().Replace("'", "''");
SkypeAddress = dr["Skype Address"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Use Origin Country"].ToString()))
{
UseOriginCountry = bool.Parse(dr["Use Origin Country"].ToString().Trim());
}
CommitteePreference1 = dr["Committee Preference 1"].ToString().Trim().Replace("'", "''");
CommitteePreference2 = dr["Committee Preference 2"].ToString().Trim().Replace("'", "''");
CommitteePreference3 = dr["Committee Preference 3"].ToString().Trim().Replace("'", "''");
CommitteePreference4 = dr["Committee Preference 4"].ToString().Trim().Replace("'", "''");
if (!string.IsNullOrEmpty(dr["Offer Trial Membership"].ToString()))
{
OfferTrialMembership = bool.Parse(dr["Offer Trial Membership"].ToString().Trim());
}
//sw = string.IsNullOrEmpty(sw) ? "null" : sw;
//kr = string.IsNullOrEmpty(kr) ? "null" : kr;
string strSql = string.Format("INSERT INTO IAHR_Member (Uid,iMISId,MemberType,Status,Prefix,FirstName,MiddleName,LastName,Organisation,Address1,Address2,Address3,Town,County,PostCode,Country"
+ ",CountryofOrigin,Region,IncomeLevel,MainEmail,AlternateEmail,AddressPurpose,JoinDate,Gender,DateofBirth,TotalFeeOwed,TotalPaid,TotalOutstanding,DelegateSource,CompanyType,CompanyId,DateAdded"
+ ",Fax,LastUpdated,MemberTypeChangeDate,PaidThrough,PreviousMemberType,Website,WorkPhone,RenewedThru,ExtraNote,SuspendedDate,CreditAmount,OpeningBalance,PaidThru,PaymentAmount,PreviousBalance"
+ ",ProductCode,ProductTitle,CompanyIpAddress,OfferTrialMembership,OldMemberNumber,OldMemberType,RemovalCode,RemovalReason,SkypeAddress,UseOriginCountry,CommitteePreference1,CommitteePreference2"
+ ",CommitteePreference3,CommitteePreference4) VALUES (0,{0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}','{23}',{24},{25},{26},'{27}','{28}',{29},'{30}','{31}','{32}','{33}','{34}','{35}','{36}','{37}','{38}','{39}','{40}',{41},{42},'{43}',{44},{45},'{46}','{47}','{48}','{49}','{50}','{51}','{52}','{53}','{54}','{55}','{56}','{57}','{58}','{59}')", iMISId, MemberType, Status, Prefix, FirstName, MiddleName, LastName, Organisation, Address1, Address2, Address3, Town, County, PostCode, Country, CountryofOrigin, Region, IncomeLevel, MainEmail, AlternateEmail, AddressPurpose, JoinDate, Gender, DateofBirth, TotalFeeOwed, TotalPaid, TotalOutstanding, DelegateSource, CompanyType, CompanyId, DateAdded, Fax, LastUpdated, MemberTypeChangeDate, PaidThrough, PreviousMemberType, Website, WorkPhone, RenewedThru, ExtraNote, SuspendedDate, CreditAmount, OpeningBalance, PaidThru, PaymentAmount, PreviousBalance, ProductCode, ProductTitle, CompanyIpAddress, OfferTrialMembership, OldMemberNumber, OldMemberType, RemovalCode, RemovalReason, SkypeAddress, UseOriginCountry, CommitteePreference1, CommitteePreference2, CommitteePreference3, CommitteePreference4);
string strConnection = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection sqlConnection = new SqlConnection(strConnection);
try
{
// SqlConnection sqlConnection = new SqlConnection(strConnection);
sqlConnection.Open();
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = strSql;
sqlCmd.Connection = sqlConnection;
SqlDataReader sqlDataReader = sqlCmd.ExecuteReader();
i++;
labNum.InnerText = i.ToString();
sqlDataReader.Close();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConnection.Close();
}
//if (opdb.ExcSQL(strSql))
// i++;
}
return i;
}