using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.Data.OleDb; using System.IO; using Microsoft.Data.ConnectionUI; using System.Configuration; using CBusiness.UserInfo; using Common.Entity; namespace CClient.DataExport { public partial class Frm_DataTranslation : Form { /// <summary> /// 是否测试成功 /// </summary> Configuration config; public Frm_DataTranslation() { InitializeComponent(); } private void button2_Click(object sender, EventArgs e) { openFileDialog1.Filter = "Excel2003(*.xls)|*.xls|Excel2007(*.xlsx)|*.xlsx"; openFileDialog1.AddExtension = true; openFileDialog1.Title = "数据导入"; if (openFileDialog1.ShowDialog() == DialogResult.OK) { ExcelFileName.Text = openFileDialog1.FileName; } } private bool ValidateImput() { bool isValidDated = false; if (openFileDialog1.FileName == string.Empty) { MessageBox.Show("请选择要导入的Excel文件.", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information); isValidDated = false; } else { isValidDated = true; } return isValidDated; } private void button3_Click(object sender, EventArgs e) { if (ValidateImput()) { string ConnectionString = GetConnectionString(); if (this.rdbProduct.Checked) TransferData(ExcelFileName.Text.ToString().Trim(), "产品", ConnectionString); if (this.rdbItems.Checked) TransferItemData(ExcelFileName.Text.ToString().Trim(), "项目", ConnectionString); if (this.rdbCards.Checked) TransferCardsData(ExcelFileName.Text.ToString().Trim(), "套餐", ConnectionString); } } private string GetConnectionString() { return ConfigurationManager.ConnectionStrings["PutInData"].ConnectionString; } /// <summary> /// 获取产品类别ID /// </summary> /// <param name="SortName">产品名称</param> /// <returns></returns> private int GetSortID(string sortName, SortType sortType) { string commText = string.Format("SELECT ProductClassID FROM tb_Sort WHERE SortName='{0}' AND SortType={1}", sortName, Convert.ToInt32(sortType)); int result; if (sortType == SortType.ServiceCard) { result = 80; } else if (sortType == SortType.Item) { result = 79; } else { result = 78; } if (sortName.Trim() != string.Empty) { using (SqlConnection conn = new SqlConnection(GetConnectionString())) { SqlCommand comm = conn.CreateCommand(); comm.CommandText = commText; comm.CommandType = CommandType.Text; conn.Open(); object obj = comm.ExecuteScalar(); if (obj != null && int.TryParse(obj.ToString(), out result) == true) { result = Convert.ToInt32(obj); } } } return result; } private void TransferData(string excelFile, string sheetName, string connectionString) { int n = 0; int RepeatData = 0; int Sum = 0; string ExcelStrConn = string.Empty; int i = this.ExcelFileName.Text.Trim().IndexOf(".") + 1; string StrTemp = this.ExcelFileName.Text.Trim().Substring(i); DataSet ds = new DataSet(); try { if (StrTemp == "xls") ExcelStrConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"; if (StrTemp == "xlsx") ExcelStrConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + excelFile + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"; OleDbConnection ExcelConn = new OleDbConnection(ExcelStrConn); ExcelConn.Open(); string StrExcel = string.Format("select * from [{0}$]", sheetName); OleDbDataAdapter adapter = new OleDbDataAdapter(StrExcel, ExcelConn); adapter.Fill(ds, sheetName); using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); progressBar1.Maximum = ds.Tables[0].Rows.Count; progressBar1.Minimum = 0; progressBar1.Step = 1; SqlCommand comm = null; SqlCommand commType = null; foreach (DataRow dr in ds.Tables[0].Rows) { progressBar1.Value++; string ProductNo = dr["产品编号"].ToString().Trim(); if (ProductNo == "") continue; comm = new SqlCommand("select count(*) from tb_Product where ProductNo='" + ProductNo + "'", conn); if (Convert.ToInt16(comm.ExecuteScalar()) == 0) { comm = new SqlCommand("insert into tb_Product(ProductNo,ProductName,Spec,Unit,RetailPrice,Sort,EnglishName,UnAbate,UnDeduct,UnDeductStorage,State) values(@ProductNo,@ProductName,@Spec,@Unit,@RetailPrice,@Sort,@EnglishName,@UnAbate,@UnDeduct,@UnDeductStorage,@State)", conn); SqlParameter mypar = new SqlParameter(); mypar = new SqlParameter("@ProductNo", dr["产品编号"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@ProductName", dr["产品名称"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Spec", dr["规格"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Unit", dr["单位"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@RetailPrice", dr["零售价格"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Sort", GetSortID(dr["分类"].ToString().Trim(), SortType.Product)); comm.Parameters.Add(mypar); mypar = new SqlParameter("@EnglishName", ""); comm.Parameters.Add(mypar); mypar = new SqlParameter("@UnAbate", false); comm.Parameters.Add(mypar); mypar = new SqlParameter("@UnDeduct", false); comm.Parameters.Add(mypar); mypar = new SqlParameter("@UnDeductStorage", false); comm.Parameters.Add(mypar); mypar = new SqlParameter("@State", "可售"); comm.Parameters.Add(mypar); comm.ExecuteNonQuery(); commType = new SqlCommand("select ProductID from tb_Product where ProductNo='" + ProductNo + "'", conn); string ItemID = commType.ExecuteScalar().ToString(); commType = new SqlCommand("insert into tb_ProductNewFeatures(FeaturesID,SellStartTime,SellLastTime,UseStartTime,UseLastTime,UseRange,ProductType)values(@FeaturesID,@SellStartTime,@SellLastTime,@UseStartTime,@UseLastTime,@UseRange,@ProductType)", conn); SqlParameter pam = new SqlParameter(); pam = new SqlParameter("@FeaturesID", ItemID); commType.Parameters.Add(pam); pam = new SqlParameter("@SellStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@SellLastTime", Convert.ToDateTime("2020-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseLastTime", Convert.ToDateTime("2100-12-31")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseRange", GetDeptInfo()); commType.Parameters.Add(pam); pam = new SqlParameter("@ProductType", n); commType.Parameters.Add(pam); commType.ExecuteNonQuery(); } else { RepeatData++; } } } Sum = ds.Tables[0].Rows.Count - RepeatData; MessageBox.Show("已成功导入 " + Sum + " 条数据,其中 " + RepeatData + " 条数据由于编号重复未导成功!", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "导入数据失败", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private void button4_Click(object sender, EventArgs e) { this.Close(); } private void Frm_DataTranslation_Load(object sender, EventArgs e) { string ConStr = ConfigurationManager.ConnectionStrings["PutInData"].ConnectionString; if (ConStr == "") { DataConnectionDialog dlg = new DataConnectionDialog(); dlg.DataSources.Add(DataSource.SqlDataSource); dlg.SelectedDataSource = DataSource.SqlDataSource; dlg.SelectedDataProvider = DataProvider.SqlDataProvider; if (DataConnectionDialog.Show(dlg, this) == DialogResult.OK) { config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); config.ConnectionStrings.ConnectionStrings["PutInData"].ConnectionString = dlg.ConnectionString; config.Save(); } else { this.Close(); } } else { SqlConnection mySqlConncetion = new SqlConnection(ConStr); try { mySqlConncetion.Open(); } catch { DataConnectionDialog dlg = new DataConnectionDialog(); dlg.DataSources.Add(DataSource.SqlDataSource); dlg.SelectedDataSource = DataSource.SqlDataSource; dlg.SelectedDataProvider = DataProvider.SqlDataProvider; if (DataConnectionDialog.Show(dlg, this) == DialogResult.OK) { config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); config.ConnectionStrings.ConnectionStrings["PutInData"].ConnectionString = dlg.ConnectionString; config.Save(); } else { this.Close(); } } finally { mySqlConncetion.Close(); } } } private string GetExcelConnectionString(string fileName) { string connectionString = string.Empty; if (Path.GetExtension(fileName) == ".xls") { connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"; } else if (Path.GetExtension(fileName) == ".xlsx") { connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + fileName + ";" + "Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"; } return connectionString; } //导入项目 private void TransferItemData(string excelFile, string sheetName, string connectionString) { int RepeatData = 0; int Sum = 0; DataSet ds = new DataSet(); string StrExcel = string.Format("select * from [{0}$]", sheetName); using (OleDbConnection ExcelConn = new OleDbConnection(GetExcelConnectionString(excelFile))) { ExcelConn.Open(); OleDbDataAdapter adapter = new OleDbDataAdapter(StrExcel, ExcelConn); adapter.Fill(ds, sheetName); } try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); progressBar1.Maximum = ds.Tables[0].Rows.Count; progressBar1.Minimum = 0; progressBar1.Step = 1; SqlCommand comm = null; SqlCommand commType = null; foreach (DataRow dr in ds.Tables[0].Rows) { progressBar1.Value++; string ItemNo = dr["编号"].ToString().Trim(); if (ItemNo == "") continue; comm = new SqlCommand("select count(*) from tb_ServiceItem where ItemNo='" + ItemNo + "'", conn); if (Convert.ToInt16(comm.ExecuteScalar()) == 0) { comm = new SqlCommand("insert into tb_ServiceItem(ItemNo,ServiceItemName,Unit,Price,Sort,UnAbate,UnDeduct,State,Remark) values(@ItemNo,@ServiceItemName,@Unit,@Price,@Sort,@UnAbate,@UnDeduct,@State,@Remark)", conn); SqlParameter mypar = new SqlParameter(); mypar = new SqlParameter("@ItemNo", dr["编号"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@ServiceItemName", dr["服务项目"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Unit", dr["单位"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Price", dr["单价"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Sort", GetSortID(dr["分类"].ToString().Trim(), SortType.Item)); comm.Parameters.Add(mypar); mypar = new SqlParameter("@UnAbate", false); comm.Parameters.Add(mypar); mypar = new SqlParameter("@UnDeduct", false); comm.Parameters.Add(mypar); mypar = new SqlParameter("@State", "有效"); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Remark", ""); comm.Parameters.Add(mypar); comm.ExecuteNonQuery(); commType = new SqlCommand("select ServiceItemID from tb_ServiceItem where ItemNo='" + ItemNo + "'", conn); string ItemID = commType.ExecuteScalar().ToString(); commType = new SqlCommand("insert into tb_ProductNewFeatures(FeaturesID,SellStartTime,SellLastTime,UseStartTime,UseLastTime,UseRange,ProductType)values(@FeaturesID,@SellStartTime,@SellLastTime,@UseStartTime,@UseLastTime,@UseRange,@ProductType)", conn); SqlParameter pam = new SqlParameter(); pam = new SqlParameter("@FeaturesID", ItemID); commType.Parameters.Add(pam); pam = new SqlParameter("@SellStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@SellLastTime", Convert.ToDateTime("2020-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseLastTime", Convert.ToDateTime("2100-12-31")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseRange", GetDeptInfo()); commType.Parameters.Add(pam); pam = new SqlParameter("@ProductType", 1); commType.Parameters.Add(pam); commType.ExecuteNonQuery(); } else { RepeatData++; } } } Sum = ds.Tables[0].Rows.Count - RepeatData; MessageBox.Show("已成功导入 " + Sum + " 条数据,其中 " + RepeatData + " 条数据由于编号重复未导成功!", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "出错", MessageBoxButtons.OK, MessageBoxIcon.Error); } } //套餐 private void TransferCardsData(string excelFile, string sheetName, string connectionString) { int SortTypeID = 2; int LimitTimes = 0; int RepeatData = 0; int Sum = 0; DataSet ds = new DataSet(); using (OleDbConnection ExcelConn = new OleDbConnection(GetExcelConnectionString(excelFile))) { ExcelConn.Open(); string StrExcel = string.Format("select * from [{0}$]", sheetName); OleDbDataAdapter adapter = new OleDbDataAdapter(StrExcel, ExcelConn); adapter.Fill(ds, sheetName); } try { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); progressBar1.Maximum = ds.Tables[0].Rows.Count; progressBar1.Minimum = 0; progressBar1.Step = 1; SqlCommand comm = null; SqlCommand commType = null; foreach (DataRow dr in ds.Tables[0].Rows) { progressBar1.Value++; string CardID = dr["套餐编号"].ToString().Trim(); if (CardID == "") continue; comm = new SqlCommand("select count(*) from tb_ServiceCard where ServiceNo='" + CardID + "'", conn); if (Convert.ToInt16(comm.ExecuteScalar()) == 0) { comm = new SqlCommand("insert into tb_ServiceCard(ServiceNo,ServiceName,ServiceType,LimitTime,Sort,Price,CardMoney)values(@ServiceNo,@ServiceName,@ServiceType,@LimitTime,@Sort,@Price,@CardMoney)", conn); SqlParameter mypar = new SqlParameter(); mypar = new SqlParameter("@ServiceNo", dr["套餐编号"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@ServiceName", dr["套餐名称"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Price", dr["金额"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@CardMoney", dr["卡内金额"].ToString().Trim()); comm.Parameters.Add(mypar); mypar = new SqlParameter("@ServiceType", SortTypeID); comm.Parameters.Add(mypar); mypar = new SqlParameter("@Sort", GetSortID(dr["分类"].ToString().Trim(), SortType.ServiceCard)); comm.Parameters.Add(mypar); mypar = new SqlParameter("@LimitTime", LimitTimes); comm.Parameters.Add(mypar); comm.ExecuteNonQuery(); #region 销售设置 commType = new SqlCommand("select ServiceID from tb_ServiceCard where ServiceNo='" + CardID + "'", conn); string ItemID = commType.ExecuteScalar().ToString(); commType = new SqlCommand("insert into tb_ProductNewFeatures(FeaturesID,SellStartTime,SellLastTime,UseStartTime,UseLastTime,UseRange,ProductType)values(@FeaturesID,@SellStartTime,@SellLastTime,@UseStartTime,@UseLastTime,@UseRange,@ProductType)", conn); SqlParameter pam = new SqlParameter(); pam = new SqlParameter("@FeaturesID", ItemID); commType.Parameters.Add(pam); pam = new SqlParameter("@SellStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@SellLastTime", Convert.ToDateTime("2020-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseStartTime", Convert.ToDateTime("2009-01-01")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseLastTime", Convert.ToDateTime("2100-12-31")); commType.Parameters.Add(pam); pam = new SqlParameter("@UseRange", GetDeptInfo()); commType.Parameters.Add(pam); pam = new SqlParameter("@ProductType", 2); commType.Parameters.Add(pam); commType.ExecuteNonQuery(); #endregion } else { RepeatData++; } } } Sum = ds.Tables[0].Rows.Count - RepeatData; MessageBox.Show("已成功导入 " + Sum + " 条数据,其中 " + RepeatData + " 条数据由于编号重复未导成功!", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "出错", MessageBoxButtons.OK, MessageBoxIcon.Error); } } private enum SortType { /// <summary> /// 产品 /// </summary> Product, /// <summary> /// 项目 /// </summary> Item, /// <summary> /// 套餐 /// </summary> ServiceCard } private string GetDeptInfo() { string deptIdStr = ""; int pagecount = 0; DeptInfoHander depthander = new DeptInfoHander(); List<DeptInforEntity> _List = depthander.ReturnObjectList(new DeptInforEntity(), 1, 1000, ref pagecount); for (int i = 0; i < _List.Count; i++) { deptIdStr += _List[i].DeptID.ToString() + ","; } if (deptIdStr.EndsWith(",")) { deptIdStr = deptIdStr.Substring(0, deptIdStr.Length - 1); } return deptIdStr; } } }