public List<SellProductModel> ReadDataError(string path, out string errorName, out string errorMarket, out string errorPrice, out int errorCount) { OleDbConnection oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); errorName = ""; errorMarket = ""; errorPrice = ""; errorCount = 0; try { oledbConn.Open(); OleDbCommand cmd = new OleDbCommand(); //CHua' data tren RAM DataSet ds = new DataSet(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; // cmd.CommandText = "SELECT [Tên Sản Phẩm],[Tên Chợ],[Giá] FROM [Sheet1$]"; cmd.CommandText = "SELECT * FROM [sheet1$B2:D2000]"; OleDbDataAdapter oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds, "SellProduct"); DataTableCollection tables = ds.Tables; errorName = ""; errorMarket = ""; errorPrice = ""; List<SellProductModel> sellProductErrorCollection = new List<SellProductModel>(); for (int i = 0; i < tables.Count; i++) { DataTable table = tables[i]; foreach (DataRow row in table.Rows) { bool error = false; SellProductModel sellProductError = new SellProductModel(); sellProductError.RowNumber = table.Rows.IndexOf(row) + 3; sellProductError.Name = row.ItemArray[0].ToString(); sellProductError.MarketName = row.ItemArray[1].ToString(); var dupProduct = sellProductErrorCollection.Where(p => p.Name.Equals(sellProductError.Name) && p.MarketName.Equals(sellProductError.MarketName)).FirstOrDefault(); //if (dupProduct != null) //{ // int price = 0; // double doublePrice; // Double.TryParse(row.ItemArray[2].ToString(), out doublePrice); // price = (int)Math.Round(doublePrice, 0); // sellProductError.Price = price; // if (sellProductError.Price < 1 || sellProductError.Price > 10000) // { // dupProduct.Price = dupProduct.Price; // } //} //else //{ if (sellProductError.Name.Length < 5 || sellProductError.Name.Length > 100) { InvalidNumberException invalidNumberException = new InvalidNumberException("Tên sản phẩm phải từ 5 đến 100 ký tự"); errorName = invalidNumberException.Message; error = true; errorCount++; } if (sellProductError.MarketName.Length < 5 || sellProductError.MarketName.Length > 100) { InvalidNumberException invalidNumberException = new InvalidNumberException("Tên chợ phải từ 5 đến 100 ký tự"); errorMarket = invalidNumberException.Message; error = true; errorCount++; } try { int price = 0; double doublePrice; Double.TryParse(row.ItemArray[2].ToString(), out doublePrice); price = (int)Math.Round(doublePrice, 0); sellProductError.Price = price; if (sellProductError.Price < 1 || sellProductError.Price > 10000) { InvalidNumberException invalidNumberException = new InvalidNumberException("Giá phải từ 1 đến 10000"); errorPrice = invalidNumberException.Message; error = true; errorCount++; } } catch (ArgumentNullException argumentNullException) { throw argumentNullException; } catch (FormatException formatException) { throw formatException; } catch (OverflowException overflowException) { throw overflowException; } finally { if (error) { sellProductErrorCollection.Add(sellProductError); } } //} } } //Compare items in Excel List<string> results = new List<string>(); for (int i = 0; i < sellProductErrorCollection.Count - 1; i++) { var result = sellProductErrorCollection[i].Name; for (int j = i + 1; j < sellProductErrorCollection.Count; j++) { var percentage = CompareStringHelper.CompareString(sellProductErrorCollection[i].Name, sellProductErrorCollection[j].Name); if (percentage > 0.7 && percentage < 1) { result += ";" + sellProductErrorCollection[j].Name; } } if (result != sellProductErrorCollection[i].Name) { results.Add(result); } } return sellProductErrorCollection; } catch (System.InvalidOperationException exception) { throw exception; } catch (System.Data.OleDb.OleDbException exception2) { throw exception2; } return null; }
public List<SellProductModel> ReadData(string path, out string errorName, out string errorMarket, out string errorPrice, out int errorCount) { OleDbConnection oledbConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"); errorName = ""; errorMarket = ""; errorPrice = ""; errorCount = 0; try { oledbConn.Open(); OleDbCommand cmd = new OleDbCommand(); //CHua' data tren RAM DataSet ds = new DataSet(); cmd.Connection = oledbConn; cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM [sheet1$B2:D2000]"; OleDbDataAdapter oleda = new OleDbDataAdapter(cmd); oleda.Fill(ds, "SellProduct"); DataTableCollection tables = ds.Tables; List<SellProductModel> sellProductCollection = new List<SellProductModel>(); for (int i = 0; i < tables.Count; i++) { DataTable table = tables[i]; foreach (DataRow row in table.Rows) { SellProductModel sellProduct = new SellProductModel(); // SellProductModel sellProductError = new SellProductModel(); sellProduct.RowNumber = Int32.Parse(row.ItemArray[0].ToString()) +2; sellProduct.Name = row.ItemArray[1].ToString(); sellProduct.MarketName = row.ItemArray[2].ToString(); var dupProduct = sellProductCollection.Where(p => p.Name.Equals(sellProduct.Name) && p.MarketName.Equals(sellProduct.MarketName)).FirstOrDefault(); if (dupProduct != null) { var price = 0; // Int32.TryParse(row.ItemArray[3].ToString(), out price); double doublePrice; Double.TryParse(row.ItemArray[2].ToString(), out doublePrice); price = (int)Math.Round(doublePrice, 0); sellProduct.Price = price; if (sellProduct.Price < 1 || sellProduct.Price > 10000) { InvalidNumberException invalidNumberException = new InvalidNumberException("Giá phải từ 1 đến 10000"); errorPrice = invalidNumberException.Message; errorCount++; } } if (sellProduct.Name.Length < 5 || sellProduct.Name.Length > 100) { InvalidNumberException invalidNumberException = new InvalidNumberException("Tên sản phẩm phải từ 5 đến 100 ký tự"); errorName = invalidNumberException.Message; errorCount++; } if (sellProduct.MarketName.Length < 5 || sellProduct.MarketName.Length > 100) { InvalidNumberException invalidNumberException = new InvalidNumberException("Tên chợ phải từ 5 đến 100 ký tự"); errorMarket = invalidNumberException.Message; errorCount++; } try { var price = 0; // Int32.TryParse(row.ItemArray[3].ToString(), out price); double doublePrice; Double.TryParse(row.ItemArray[2].ToString(), out doublePrice); price = (int)Math.Round(doublePrice, 0); sellProduct.Price = price; if (sellProduct.Price < 1 || sellProduct.Price > 10000) { InvalidNumberException invalidNumberException = new InvalidNumberException("Giá phải từ 1 đến 10000"); errorPrice = invalidNumberException.Message; errorCount++; } } catch (ArgumentNullException argumentNullException) { throw argumentNullException; } catch (FormatException formatException) { throw formatException; } catch (OverflowException overflowException) { throw overflowException; } finally { sellProductCollection.Add(sellProduct); } } return sellProductCollection; } } catch (System.InvalidOperationException exception) { throw exception; } catch (System.Data.OleDb.OleDbException exception2) { throw exception2; } return null; }