public static List <StockItem> Read(string file, string priceName)
{
List <StockItem> stockItems = new List <StockItem>();
string error = "";
try
{
int priceColumn = -1;
int treatmentColumn = -1;
int gradeColumn = -1;
int drynessColumn = -1;
int finishColumn = -1;
int widthColumn = -1;
int thicknessColumn = -1;
int lengthColumn = -1;
int packsColumn = -1;
int cubeColumn = -1;
int SKUColumn = -1;
int categoryColumn = -1;
int typeColumn = -1;
using (var stream = File.Open(file, FileMode.Open, FileAccess.Read))
{
error = "reader";
stockItems = new List <StockItem>();
error = "read";
using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(stream))
{
// Just read the first sheet, as there should be only one
int row = 0;
while (reader.Read())
{
if (row == 0) // We need to find out which column this user's pricing information is in
{
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetString(i);
System.Diagnostics.Debug.WriteLine(name);
if (name.Length > 8 && name.Substring(0, 8).ToLower() == "pricing-")
{
string priceNameSubstring = name.Substring(8).Trim();
if (priceNameSubstring == priceName.Trim())
{
priceColumn = i;
break;
}
}
else if (name.ToLower() == "treatment")
{
treatmentColumn = i;
}
else if (name.ToLower() == "grade")
{
gradeColumn = i;
}
else if (name.ToLower() == "dryness")
{
drynessColumn = i;
}
else if (name.ToLower() == "finish")
{
finishColumn = i;
}
else if (name.ToLower() == "width")
{
widthColumn = i;
}
else if (name.ToLower() == "thickness")
{
thicknessColumn = i;
}
else if (name.ToLower() == "length")
{
lengthColumn = i;
}
else if (name.ToLower() == "packs")
{
packsColumn = i;
}
else if (name.ToLower() == "cube")
{
cubeColumn = i;
}
else if (name.ToLower() == "productcode")
{
SKUColumn = i;
}
else if (name.ToLower() == "category")
{
categoryColumn = i;
}
else if (name.ToLower() == "prodcat")
{
typeColumn = i;
}
}
}
else
{
StockItem stockItem = new StockItem();
for (int column = 0; column < reader.FieldCount; column++)
{
stockItem.Price = "";
if (column == categoryColumn)
{
string[] categoryType = reader.GetString(column).Split('-');
if (categoryType.Length > 1)
{
stockItem.Category = categoryType[categoryColumn].TrimEnd();
stockItem.Type = categoryType[typeColumn].TrimStart();
}
else
{
stockItem.Category = categoryType[categoryColumn].TrimEnd();
stockItem.Type = categoryType[categoryColumn].TrimEnd();
}
}
if (column == gradeColumn)
{
stockItem.Grade = reader.GetString(gradeColumn);
stockItem.Grade = stockItem.Grade.ToUpper();
}
else if (column == treatmentColumn)
{
stockItem.Treatment = reader.GetString(treatmentColumn);
stockItem.Treatment = stockItem.Treatment.ToUpper();
}
else if (column == drynessColumn)
{
stockItem.Dryness = reader.GetString(drynessColumn);
stockItem.Dryness = stockItem.Dryness.ToUpper();
}
else if (column == finishColumn)
{
stockItem.Finish = reader.GetString(finishColumn);
stockItem.Finish = stockItem.Finish.ToUpper();
}
else if (column == widthColumn)
{
stockItem.Width = reader.GetString(widthColumn);
}
else if (column == thicknessColumn)
{
stockItem.Thickness = reader[thicknessColumn].ToString();
}
else if (column == lengthColumn)
{
stockItem.Length = reader.GetString(lengthColumn);
}
else if (column == packsColumn)
{
stockItem.Packs = reader[packsColumn].ToString();
}
else if (column == cubeColumn)
{
stockItem.Cube = reader.GetString(cubeColumn);
}
else if (column == SKUColumn)
{
stockItem.SKU = reader.GetString(SKUColumn);
}
else if (column == priceColumn)
{
stockItem.Price = reader.GetString(priceColumn);
}
stockItems.Add(stockItem);
column++;
}
}
row++;
}
}
}
stockItems.Sort();
}
catch (Exception debugException)
{
System.Diagnostics.StackTrace stack = new System.Diagnostics.StackTrace(debugException, true);
string stringLineNumber = stack.GetFrame(1).GetFileLineNumber().ToString();
string message = "(" + stringLineNumber + ") " + debugException.Message;
Email.TellWebMasterAboutError("StockItem", error + "\n" + message);
System.Diagnostics.Debug.WriteLine(error);
System.Diagnostics.Debug.WriteLine(message);
}
return(stockItems);
}
public int CompareTo(object compared)
{
StockItem a = this;
StockItem b = compared as StockItem;
int compareTo = string.Compare(a.Type, b.Type);
if (compareTo == 0)
{
compareTo = string.Compare(a.Category, b.Category);
if (compareTo == 0)
{
try
{
double aWidth = double.Parse(a.Width);
double bWidth = double.Parse(b.Width);
if (aWidth < bWidth)
{
compareTo = -1;
}
else if (aWidth > bWidth)
{
compareTo = 1;
}
}
catch
{
compareTo = string.Compare(a.Width, b.Width);
}
if (compareTo == 0)
{
try
{
double aThickness = double.Parse(a.Thickness);
double bThickness = double.Parse(b.Thickness);
if (aThickness < bThickness)
{
compareTo = -1;
}
else if (aThickness > bThickness)
{
compareTo = 1;
}
}
catch
{
compareTo = string.Compare(a.Thickness, b.Thickness);
}
if (compareTo == 0)
{
try {
double aLength = double.Parse(a.Length);
double bLength = double.Parse(b.Length);
if (aLength < bLength)
{
compareTo = -1;
}
else if (aLength > bLength)
{
compareTo = 1;
}
}
catch {
compareTo = string.Compare(a.Length, b.Length);
}
if (compareTo == 0)
{
compareTo = string.Compare(a.Grade, b.Grade);
}
}
}
}
}
return(compareTo);
}
public static List <StockItem> Read(string file, string priceName)
{
List <StockItem> stockItems = new List <StockItem>();
// string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + file + ";Extended Properties=\"Excel 8.0;HDR=YES\"";
// Create the connection object
OleDbConnection oledbConn = new OleDbConnection(connString);
try
{
// Open connection
oledbConn.Open();
System.Data.DataTable dbSchema = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dbSchema == null || dbSchema.Rows.Count < 1)
{
throw new Exception("Error: Could not determine the name of the first worksheet.");
}
string firstSheetName = dbSchema.Rows[0]["TABLE_NAME"].ToString();
int priceColumn = 0;
int treatmentColumn = -1;
int gradeColumn = -1;
int drynessColumn = -1;
int finishColumn = -1;
int widthColumn = -1;
int thicknessColumn = -1;
int lengthColumn = -1;
int packsColumn = -1;
int cubeColumn = -1;
int SKUColumn = -1;
int categoryColumn = -1;
int typeColumn = -1;
bool priceFound = false;
// Create OleDbCommand object and select data from worksheet Sheet1
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + firstSheetName + "]", oledbConn);
// Create new OleDbDataAdapter
OleDbDataAdapter oleda = new OleDbDataAdapter();
oleda.SelectCommand = cmd;
OleDbDataReader reader = cmd.ExecuteReader();
int row = 0;
stockItems = new List <StockItem>();
while (reader.Read())
{
if (row == 0) // We need to find out which column this user's pricing information is in
{
for (int i = 0; i < reader.FieldCount; i++)
{
string name = reader.GetName(i);
if (name == "Pricing-" + priceName)
{
priceFound = true;
priceColumn = i;
break;
}
else if (name.ToLower() == "treatment")
{
treatmentColumn = i;
}
else if (name.ToLower() == "grade")
{
gradeColumn = i;
}
else if (name.ToLower() == "dryness")
{
drynessColumn = i;
}
else if (name.ToLower() == "finish")
{
finishColumn = i;
}
else if (name.ToLower() == "width")
{
widthColumn = i;
}
else if (name.ToLower() == "thickness")
{
thicknessColumn = i;
}
else if (name.ToLower() == "length")
{
lengthColumn = i;
}
else if (name.ToLower() == "packs")
{
packsColumn = i;
}
else if (name.ToLower() == "cube")
{
cubeColumn = i;
}
else if (name.ToLower() == "productcode")
{
SKUColumn = i;
}
else if (name.ToLower() == "category")
{
categoryColumn = i;
}
else if (name.ToLower() == "prodcat")
{
typeColumn = i;
}
}
}
StockItem stockItem = new StockItem();
string[] categoryType = reader[categoryColumn].ToString().Split('-');
if (categoryType.Length > 1)
{
stockItem.Category = categoryType[categoryColumn].TrimEnd();
stockItem.Type = categoryType[typeColumn].TrimStart();
}
else
{
stockItem.Category = categoryType[categoryColumn].TrimEnd();
stockItem.Type = categoryType[categoryColumn].TrimEnd();
}
if (gradeColumn != -1)
{
stockItem.Grade = reader[gradeColumn].ToString();
stockItem.Grade = stockItem.Grade.ToUpper();
}
if (treatmentColumn != -1)
{
stockItem.Treatment = reader[treatmentColumn].ToString();
stockItem.Treatment = stockItem.Treatment.ToUpper();
}
if (drynessColumn != -1)
{
stockItem.Dryness = reader[drynessColumn].ToString();
stockItem.Dryness = stockItem.Dryness.ToUpper();
}
if (finishColumn != -1)
{
stockItem.Finish = reader[finishColumn].ToString();
stockItem.Finish = stockItem.Finish.ToUpper();
}
if (widthColumn != -1)
{
stockItem.Width = reader[widthColumn].ToString();
}
if (thicknessColumn != -1)
{
stockItem.Thickness = reader[thicknessColumn].ToString();
}
if (lengthColumn != -1)
{
stockItem.Length = reader[lengthColumn].ToString();
}
if (packsColumn != -1)
{
stockItem.Packs = reader[packsColumn].ToString();
}
if (cubeColumn != -1)
{
stockItem.Cube = reader[cubeColumn].ToString();
}
if (SKUColumn != -1)
{
stockItem.SKU = reader[SKUColumn].ToString();
}
if (priceFound)
{
stockItem.Price = reader[priceColumn].ToString();
}
else
{
stockItem.Price = "";
}
stockItems.Add(stockItem);
row++;
}
stockItems.Sort();
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
finally
{
// Close connection
oledbConn.Close();
}
return(stockItems);
}