public static IEnumerable <Entity> AsList <Entity>(this ExcelQueryable <Row> query) where Entity : new()
{
try
{
var result = new List <Entity>();
foreach (var item in query)
{
var newItem = new Entity();
foreach (var p in typeof(Entity).GetProperties())
{
var valueInData = item[p.Name].Value;
var destiny = newItem.GetType();
destiny.GetProperty(p.Name).SetValue(newItem, valueInData);
}
result.Add(newItem);
}
return(result);
}
catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.Message); }
return(default(List <Entity>));
}
public IEnumerable <Branch> ReadDistinctBrnaches(string fileName)
{
var book = new LinqToExcel.ExcelQueryFactory(fileName);
ExcelQueryable <Row> a = book.Worksheet(_worksheetName);
return(Enumerable.TakeWhile(a, row => !string.IsNullOrEmpty(row["Branża"].Cast <string>())).Select(row => new Branch()
{
Name = row["Branża"].Cast <string>()
}).GroupBy(brand => brand.Name).Select(brands => brands.First()));
}
public void where_not_null()
{
ExcelQueryable <CompanyNullable> items = ExcelQueryFactory.Worksheet <CompanyNullable>("NullCells",
_excelFileName + "x", null);
var companies = from c in items.AsEnumerable()
where c.EmployeeCount != null
select c;
Assert.AreEqual(1, companies.ToList().Count);
}
public ActionResult ReceivedPLExcel(int templateTypeId)
{
HttpPostedFileBase file = Request.Files.Count > 0 ? Request.Files[0] : null;
if (file != null)
{
try
{
string filename = null;
using (WorkFlowApiClient client = new WorkFlowApiClient())
{
byte[] fileBytes = new byte[file.InputStream.Length];
int byteCount = file.InputStream.Read(fileBytes, 0, (int)file.InputStream.Length);
string fileContent = Convert.ToBase64String(fileBytes);
string ret = client.UploadFile(file.FileName, fileContent);
JObject obj = JObject.Parse(ret);
string newName = obj["fileName"].ToString();
string subDir = newName.Substring(0, newName.IndexOf(@"\"));
string fName = newName.Substring(newName.IndexOf(@"\") + 1);
string dir = Server.MapPath("~/temp/app/" + subDir);
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
filename = Path.Combine(dir, fName);
file.SaveAs(filename);
ViewBag.newName = newName;
ViewBag.filename = file.FileName;
ViewBag.fileSize = file.ContentLength;
}
if (file.FileName.EndsWith(".xlsx") || file.FileName.EndsWith(".xls"))
{
using (ExcelQueryFactory excel = new ExcelQueryFactory(filename))
{
ExcelQueryable <Row> sheet = excel.Worksheet(0);
var rows = sheet.Select(p => p).ToArray();
Dictionary <string, object> fileData = new Dictionary <string, object>();
if (templateTypeId == 1)
{
var sumProfit = 0.0;
#region get file data from excel
foreach (var row in rows)
{
var key = row[0].Value.ToString();
var keyForConstruction = row[3].Value.ToString();
if (key.Equals("Gross Profit") ||
key.Equals("Net Sales") ||
key.Equals("Occupancy charges") ||
key.Equals("Salary") ||
key.Equals("Depreciation") ||
key.Equals("Royalty") ||
key.Equals("Others") ||
key.Equals("Total operating expenses") ||
key.Equals("Operating Profit"))
{
var formattedKey = key.Replace(" ", "");
if (!fileData.ContainsKey(formattedKey))
{
var year1 = row[3].Value.ToString().Replace(",", "");
var year2 = row[6].Value.ToString().Replace(",", "");
var year3 = row[10].Value.ToString().Replace(",", "");
fileData[formattedKey] = new Dictionary <string, string>()
{
{ "Year1", year1 },
{ "Year2", year2 },
{ "Year3", year3 }
};
if (key.Equals("Operating Profit"))
{
sumProfit = double.Parse(year1) + double.Parse(year2) + double.Parse(year3);
}
}
#region Gross Profit
if (key.Equals("Gross Profit"))
{
formattedKey = "GrossMargin";
if (!fileData.ContainsKey(formattedKey))
{
var year1 = row[4].Value.ToString()
.Replace(",", "")
.Replace("%", "");
var year2 = row[7].Value.ToString()
.Replace(",", "")
.Replace("%", "");
var year3 = row[11].Value.ToString()
.Replace(",", "")
.Replace("%", "");
fileData[formattedKey] = new Dictionary <string, string>()
{
{ "Year1", year1 },
{ "Year2", year2 },
{ "Year3", year3 }
};
}
}
#endregion
}
else if (key.Equals("Store Size (sq.m)"))
{
fileData["StoreSize"] = row[1].Value.ToString().Replace(",", "");
}
else if (key.Equals("# of Staffs:"))
{
fileData["ofStaffs"] = row[1].Value.ToString().Replace(",", "");
}
if (keyForConstruction.Equals("Walls, Ceiling & Floor") ||
keyForConstruction.Equals("Furniture") ||
keyForConstruction.Equals("Labor Cost") ||
keyForConstruction.Equals("IT Equipment") ||
keyForConstruction.Equals("Utilities & Others") ||
keyForConstruction.Equals("Total Costs") ||
keyForConstruction.Equals("Moving, Assembly, Removal"))
{
var formattedKey = keyForConstruction
.Replace(" ", "")
.Replace("&", "")
.Replace(",", "");
if (!fileData.ContainsKey(formattedKey))
{
var value = row[4].Value.ToString().Replace(",", "");
fileData[formattedKey] = value;
if (keyForConstruction.Equals("Total Costs") && value.Length > 0)
{
fileData["NetGain"] = sumProfit - double.Parse(value);
}
}
}
if (row[6].Value.ToString().Equals("Commission as % of Net Sales:"))
{
var year1 = row[7].Value.ToString()
.Replace(",", "")
.Replace("%", "");
var year2 = row[8].Value.ToString()
.Replace(",", "")
.Replace("%", "");
var year3 = row[9].Value.ToString()
.Replace(",", "")
.Replace("%", "");
fileData["CommissionPercent"] = new Dictionary <string, string>()
{
{ "Year1", year1 },
{ "Year2", year2 },
{ "Year3", year3 }
};
}
}
#endregion
}
else if (templateTypeId == 7)
{
#region get file data from excel
foreach (var row in rows)
{
var key = row[0].Value.ToString();
var keyForConstruction = row[3].Value.ToString();
if (key.Equals("Sales") ||
key.Equals("Gross Profit") ||
key.Equals("Occupancy Charges") ||
key.Equals("Salary") ||
key.Equals("Depreciation") ||
key.Equals("Royalty") ||
key.Equals("Others") ||
key.Equals("Total Operating Expenses") ||
key.Equals("Operating Profit"))
{
var formattedKey = key.Replace(" ", "");
if (!fileData.ContainsKey(formattedKey))
{
var lastYear = row[1].Value.ToString().Replace(",", "");
var year1 = row[3].Value.ToString().Replace(",", "");
fileData[formattedKey] = new Dictionary <string, string>()
{
{ "col1", lastYear },
{ "col2", year1 }
};
}
}
}
#endregion
}
ViewBag.fileData = JsonConvert.SerializeObject(fileData);
}
}
//ViewBag.newName = newName;
//ViewBag.filename = file.FileName;
//ViewBag.fileSize = file.ContentLength;
return(PartialView("~/Views/Application/_UploadedFile.cshtml"));
}
catch (Exception e)
{
Singleton <ILogWritter> .Instance.WriteExceptionLog("Read excel for event", e, null);
return(Content(StringResource.UPLOAD_ATTACHMENT_FAILED + ", " + e.Message));
}
}
return(Content(StringResource.FILE_IS_MISSING));
}
