private void button1_Click(object sender, EventArgs e) { CTLImportExcel ex = new CTLImportExcel(); OpenFileDialog open = new OpenFileDialog(); open.Filter = "*.xlsx|*.xlsx"; if (open.ShowDialog() == DialogResult.OK) { DataTable dt = ex.getDataFromXLS2007(open.FileName); dataGridView1.DataSource = dt; } if (dataGridView1.DataSource != null) { //DataTable dt =(DataTable) dataGridView1.DataSource; //tspdata //foreach (DataRow r in dt.Rows) //{ //} } }
public void ImPort_Excel() { DataTable dt = new DataTable(); try { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "*.xls|*.xls|*.xlsx|*.xlsx"; CTLImportExcel excel = new CTLImportExcel(); if (dialog.ShowDialog() == DialogResult.OK) { FileInfo file = new FileInfo(dialog.FileName); if (file.Extension.ToString().Trim().ToUpper() == ".XLS") { dt = excel.getDataFromXLS(dialog.FileName); } else if (file.Extension.ToString().Trim().ToUpper() == ".XLSX") { dt = excel.getDataFromXLS2007(dialog.FileName); } int dem = 0; foreach (DataColumn col in dt.Columns) { if (col.ColumnName.ToUpper() == "SKU") { dem++; } } if (dem == 0) { return; } string listma = LayDKMaSKUsql(dt); if (listma.Trim() == "and SKU in )") { return; } if (dt.Rows.Count >= 1) {//code cu TPSDataAccess access = new TPSDataAccess(_PathFileINVMST); // DataTable source = access.GetTableDM(@"Select SKU,UPC,Description,Price,CurrencyCode,Sell_Unit from INVMST // where 1=1 "+listma); // if (source.Rows.Count >= 1) // GridDSMa.DataSource = source; //sua lai DataTable dttemp = new DataTable(); dttemp.Columns.Add("SKU"); dttemp.Columns.Add("UPC"); dttemp.Columns.Add("Description"); dttemp.Columns.Add("Price"); dttemp.Columns.Add("CurrencyCode"); dttemp.Columns.Add("Sell_Unit"); foreach (DataRow r in dt.Rows) { if (r["SKU"].ToString() != string.Empty) { DataTable source = access.GetTableDM(string.Format(@"Select SKU,UPC,Description,Price,CurrencyCode,Sell_Unit from INVMST where 1=1 and sku='{0}'", r["SKU"].ToString())); if (source.Rows.Count > 0) { DataRow nrow = dttemp.NewRow(); nrow["SKU"] = source.Rows[0]["SKU"]; nrow["UPC"] = source.Rows[0]["UPC"]; nrow["Description"] = source.Rows[0]["Description"]; nrow["Price"] = source.Rows[0]["Price"]; nrow["CurrencyCode"] = source.Rows[0]["CurrencyCode"]; nrow["Sell_Unit"] = source.Rows[0]["Sell_Unit"]; dttemp.Rows.Add(nrow); } } } if (dttemp.Rows.Count > 0) { GridDSMa.DataSource = dttemp; } } } } catch (Exception ex) { CTLError.WriteError("FrmImportfile ImportExcel", ex.Message); return; throw new Exception(ex.Message); } }