public void ImportToAccess() { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataSet ds = new DataSet(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ofd.FileName + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", "Sheet1"); //Sheet1为excel中工作薄 myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "Sheet1"); DataTable dt = ds.Tables["Sheet1"]; Encoding gb2312 = Encoding.GetEncoding("GB2312"); for (int iRow = 1; iRow < dt.Rows.Count; iRow++) { BaseInfoMaterial bim = BaseInfoMaterial.New; bim.Code = dt.Rows[iRow][0].ToString(); bim.Name = dt.Rows[iRow][1].ToString(); bim.ShortCode = PinYinTransfer.GetInitials(bim.Name, gb2312); bim.Barcode = bim.ShortCode; bim.Category = dt.Rows[iRow][4].ToString(); bim.Specification = dt.Rows[iRow][5].ToString(); bim.ItemNo = dt.Rows[iRow][6].ToString(); bim.Brand = dt.Rows[iRow][7].ToString(); bim.PurchaseUnitName = dt.Rows[iRow][8].ToString(); bim.SaleUnitName = dt.Rows[iRow][9].ToString(); bim.UnitConvValue = decimal.Parse(dt.Rows[iRow][10].ToString()); bim.UnitPrice = decimal.Parse(dt.Rows[iRow][11].ToString()); if (bim.UnitPrice == 0) { bim.ConvPrice = 0; } else { bim.ConvPrice = bim.UnitPrice / bim.UnitConvValue; } bim.AvgPrice = bim.ConvPrice; bim.RetailPrice = decimal.Parse(dt.Rows[iRow][14].ToString()); bim.MemberPrice = decimal.Parse(dt.Rows[iRow][15].ToString()); bim.InvQuantity = decimal.Parse(dt.Rows[iRow][16].ToString()); bim.ConvQuantity = bim.InvQuantity * bim.UnitConvValue; bim.InventoryCost = bim.ConvPrice * bim.ConvQuantity; bim.StockLowAlarm = decimal.Parse(dt.Rows[iRow][19].ToString()); bim.BonusPoint = decimal.Parse(dt.Rows[iRow][20].ToString()); bim.IsAddToInventory = (bool)dt.Rows[iRow][21]; bim.IsIngredient = (bool)dt.Rows[iRow][22]; bim.Status = dt.Rows[iRow][23].ToString(); bim.Remark = dt.Rows[iRow][24].ToString(); bim.OptrType = UserStatics.OptrType; bim.SavedOn = DateTime.Now; bim.Save(); } } }
public void ImportToAccess() { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataSet ds = new DataSet(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ofd.FileName + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", "Sheet1"); //Sheet1为excel中工作薄 myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "Sheet1"); DataTable dt = ds.Tables["Sheet1"]; Encoding gb2312 = Encoding.GetEncoding("GB2312"); for (int iRow = 1; iRow < dt.Rows.Count; iRow++) { BaseInfoMember bifm = BaseInfoMember.New; bifm.JoinedDate = (DateTime)dt.Rows[iRow][0]; bifm.CardNumber = dt.Rows[iRow][1].ToString(); bifm.MemberName = dt.Rows[iRow][2].ToString(); bifm.PinYin = PinYinTransfer.GetInitials(bifm.MemberName, gb2312); bifm.Password = dt.Rows[iRow][4].ToString(); bifm.Birth = (DateTime)dt.Rows[iRow][5]; bifm.Mobile = dt.Rows[iRow][6].ToString(); bifm.Phone = dt.Rows[iRow][7].ToString(); bifm.DiscountRate = decimal.Parse(dt.Rows[iRow][8].ToString()); bifm.RemainingSum = decimal.Parse(dt.Rows[iRow][9].ToString()); bifm.TotalBonusPoint = decimal.Parse(dt.Rows[iRow][10].ToString()); bifm.Frequency = decimal.Parse(dt.Rows[iRow][11].ToString()); bifm.TotalSpending = decimal.Parse(dt.Rows[iRow][12].ToString()); bifm.Description = dt.Rows[iRow][13].ToString(); bifm.SavedOn = DateTime.Now; bifm.OptrType = UserStatics.OptrType; bifm.Save(); } } }
public void ImportToAccess() { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataSet ds = new DataSet(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ofd.FileName + ";" + "Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", "Sheet1"); //Sheet1为excel中工作薄 myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "Sheet1"); DataTable dt = ds.Tables["Sheet1"]; Encoding gb2312 = Encoding.GetEncoding("GB2312"); for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { BaseInfoCard bic = BaseInfoCard.New; bic.JoinedDate = (DateTime)dt.Rows[iRow][0]; bic.CardNumber = dt.Rows[iRow][1].ToString(); bic.ChildName = dt.Rows[iRow][2].ToString(); bic.Mobile = dt.Rows[iRow][4].ToString(); bic.CardType = dt.Rows[iRow][5].ToString(); bic.CountLeft = decimal.Parse(dt.Rows[iRow][6].ToString()); DateTime dtime = (DateTime)dt.Rows[iRow][0]; bic.ExpirationDate = dtime.AddYears(1); bic.Remark = dt.Rows[iRow][8].ToString(); bic.CardStatus = 1; bic.PinYin = PinYinTransfer.GetInitials(bic.ChildName, gb2312); bic.SavedOn = DateTime.Now; bic.OptrType = UserStatics.OptrType; bic.Save(); } } }
protected override void SetImportOptration() { try { OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { DataSet ds = new DataSet(); string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + ofd.FileName + ";" + "Extended Properties='Excel 8.0;HDR=yes;IMEX=1'"; OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); string strExcel = ""; OleDbDataAdapter myCommand = null; strExcel = string.Format("select * from [{0}$]", "Sheet1"); //Sheet1为excel中工作薄 myCommand = new OleDbDataAdapter(strExcel, strConn); myCommand.Fill(ds, "Sheet1"); DataTable dt = ds.Tables["Sheet1"]; Encoding gb2312 = Encoding.GetEncoding("GB2312"); for (int iRow = 0; iRow < dt.Rows.Count; iRow++) { BaseInfoMemberTester bifmt = BaseInfoMemberTester.New; bifmt.Mobile = dt.Rows[iRow][0].ToString(); bifmt.Name = dt.Rows[iRow][1].ToString(); bifmt.Description = dt.Rows[iRow][2].ToString(); bifmt.PinYin = PinYinTransfer.GetInitials(bifmt.Name, gb2312); bifmt.TestDate = (DateTime)dt.Rows[iRow][3]; bifmt.FinishedDate = (DateTime)dt.Rows[iRow][4]; bifmt.Status = "0"; bifmt.OptrType = UserStatics.OptrType; bifmt.Save(); } } MessageBox.Show("导入成功!"); } catch (Exception ex) { MessageBox.Show("导入有问题,日期精确到秒,日期列要设为日期格式,并且数值类型如没有必须用0代替!\n" + ex); } }