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)); }