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);
}
}