public IActionResult CreateDocument([Bind()] InputModel inputModel) { string lookupDirectory = @"E:\temp\tvlk\files\"; string suffix = " - Processed"; inputModel.ProcessResult = new List <ProcessResult>(); if (inputModel.Directory != null) { if (inputModel.Directory.Length > 0) { lookupDirectory = inputModel.Directory; } } if (inputModel.OutputSuffix != null) { if (inputModel.OutputSuffix.Length > 0) { suffix = inputModel.OutputSuffix; } } string[] files = Directory.GetFiles(lookupDirectory, "*" + inputModel.SearchString + "*"); foreach (string file in files) { if (!file.Contains(suffix)) { ProcessResult processResult = new ProcessResult(); try { //FileStream fileStream = new FileStream(file, FileMode.Open); using (FileStream fileStream = new FileStream(file, FileMode.Open)) { //setting up application ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; application.EnableIncrementalFormula = true; //setting up workbook & shits IWorkbook workbook = application.Workbooks.Open(fileStream); IWorksheet worksheet = workbook.Worksheets[0]; worksheet.EnableSheetCalculations(); worksheet.UsedRangeIncludesFormatting = false; //define initial values int colCount = worksheet.UsedRange.LastColumn; int rowCount = worksheet.UsedRange.LastRow; //int colCount = 39; //int rowCount = 10; AuditColumns colName = new AuditColumns(); AuditColumns cleanColName = new AuditColumns(); AuditColumns colBase = new AuditColumns(); ExcelColumnEnum en = new ExcelColumnEnum(); //get column alphabet by header text for (int i = 1; i <= colCount; i++) { string[] date = colBase.date.Split(','); foreach (string split in date) { if (worksheet[1, i].Value == split) { colName.date = en.TranslateIndex(i); break; } } string[] bid = colBase.bid.Split(','); foreach (string split in bid) { if (worksheet[1, i].Value == split) { colName.bid = en.TranslateIndex(i); break; } } string[] locale = colBase.locale.Split(','); foreach (string split in locale) { if (worksheet[1, i].Value == split) { colName.locale = en.TranslateIndex(i); break; } } string[] contractEntity = colBase.contractEntity.Split(','); foreach (string split in contractEntity) { if (worksheet[1, i].Value == split) { colName.contractEntity = en.TranslateIndex(i); break; } } string[] contractCurrency = colBase.contractCurrency.Split(','); foreach (string split in contractCurrency) { if (worksheet[1, i].Value == split) { colName.contractCurrency = en.TranslateIndex(i); break; } } string[] collectEntity = colBase.collectEntity.Split(','); foreach (string split in collectEntity) { if (worksheet[1, i].Value == split) { colName.collectEntity = en.TranslateIndex(i); break; } } string[] collectCurrency = colBase.collectCurrency.Split(','); foreach (string split in collectCurrency) { if (worksheet[1, i].Value == split) { colName.collectCurrency = en.TranslateIndex(i); break; } } string[] commissionRevenue = colBase.commissionRevenue.Split(','); foreach (string split in commissionRevenue) { if (worksheet[1, i].Value == split) { colName.commissionRevenue = en.TranslateIndex(i); break; } } string[] transactionFee = colBase.transactionFee.Split(','); foreach (string split in transactionFee) { if (worksheet[1, i].Value == split) { colName.transactionFee = en.TranslateIndex(i); break; } } string[] premium = colBase.premium.Split(','); foreach (string split in premium) { if (worksheet[1, i].Value == split) { colName.premium = en.TranslateIndex(i); break; } } string[] discount = colBase.discount.Split(','); foreach (string split in discount) { if (worksheet[1, i].Value == split) { colName.discount = en.TranslateIndex(i); break; } } string[] coupon = colBase.coupon.Split(','); foreach (string split in coupon) { if (worksheet[1, i].Value == split) { colName.coupon = en.TranslateIndex(i); break; } } string[] redeemedPoints = colBase.redeemedPoints.Split(','); foreach (string split in redeemedPoints) { if (worksheet[1, i].Value == split) { colName.redeemedPoints = en.TranslateIndex(i); break; } } string[] uniqueCode = colBase.uniqueCode.Split(','); foreach (string split in uniqueCode) { if (worksheet[1, i].Value == split) { colName.uniqueCode = en.TranslateIndex(i); break; } } string[] installmentFee = colBase.installmentFee.Split(','); foreach (string split in installmentFee) { if (worksheet[1, i].Value == split) { colName.installmentFee = en.TranslateIndex(i); break; } } string[] deliveryFee = colBase.deliveryFee.Split(','); foreach (string split in deliveryFee) { if (worksheet[1, i].Value == split) { colName.deliveryFee = en.TranslateIndex(i); break; } } string[] invoiceAmount = colBase.invoiceAmount.Split(','); foreach (string split in invoiceAmount) { if (worksheet[1, i].Value == split) { colName.invoiceAmount = en.TranslateIndex(i); break; } } string[] refundFee = colBase.refundFee.Split(','); foreach (string split in refundFee) { if (worksheet[1, i].Value == split) { colName.refundFee = en.TranslateIndex(i); break; } } string[] rescheduleFee = colBase.rescheduleFee.Split(','); foreach (string split in rescheduleFee) { if (worksheet[1, i].Value == split) { colName.rescheduleFee = en.TranslateIndex(i); break; } } string[] rebookCost = colBase.rebookCost.Split(','); foreach (string split in rebookCost) { if (worksheet[1, i].Value == split) { colName.rebookCost = en.TranslateIndex(i); break; } } } cleanColName = cleanUpNonExistingColumn(colName); string colNotFound = getColumnNotFoundWarning(cleanColName); //define new columns header worksheet[1, colCount + 1].Text = "Margin Amount"; worksheet[1, colCount + 2].Text = "Margin"; worksheet[1, colCount + 3].Text = "Status"; //calculate first non-header row & copy to the rest of sheet string formula = getAddingFormula(cleanColName, 2, false) + getSubtractFormula(cleanColName, 2, true); worksheet[2, colCount + 1, rowCount, colCount + 1].Formula = "=" + formula.Replace("#+", "").Replace("#", ""); string formula2 = getTaggingResult(2, en.TranslateIndex(colCount + 1)); worksheet[2, colCount + 2, rowCount, colCount + 2].Formula = "=" + formula2; worksheet[2, colCount + 3, rowCount, colCount + 3].Text = "ISSUED"; //conditional formatting /* * //prepare writing to compiled shit * List<string> listContractEntity = new List<string>(); * List<string> listCollectingCurrency = new List<string>(); * * //group contract entity * if (cleanColName.contractEntity != "") * { * var distinct = worksheet[cleanColName.contractEntity + "2:" + cleanColName.contractEntity + rowCount.ToString()].Columns.Distinct(); * int countContractEntity = distinct.Count(); * if (countContractEntity > 0) * { * foreach (var item in distinct) * { * listContractEntity.Add(item.Value); * } * } * } * * //group transaction currency * if (cleanColName.collectCurrency != "") * { * var distinct = worksheet[cleanColName.collectCurrency + "2:" + cleanColName.collectCurrency + rowCount.ToString()].Columns.Distinct(); * int countCollectCurrency = distinct.Count(); * if (countCollectCurrency > 0) * { * foreach (var item in distinct) * { * listCollectingCurrency.Add(item.Value); * } * } * } */ using (FileStream stream = new FileStream(@"" + file.Split('.')[0] + suffix + "." + file.Split('.')[1], FileMode.Create)) { workbook.SaveAs(stream); } workbook.Close(); excelEngine.Dispose(); processResult.Success = true; processResult.Message = file + " success"; if (colNotFound != "") { processResult.Message += " with " + colNotFound + " not found."; } } } catch (Exception ex) { processResult.Success = false; processResult.Message = file + " failed"; } inputModel.ProcessResult.Add(processResult); //write to compiled shit } } ViewData["ProcessResult"] = inputModel.ProcessResult; return(View(inputModel)); }
/// <summary> /// Configures how this specific attribute is parsed from an Excel-File. /// </summary> /// <param name="ColumnIndex">1-Bases index of the column in the Excel-File</param> /// <param name="DefaultValue">Default-Value used if cell values is null or empty; Excel-Value is not replaced if <see cref="DefaultValue"/> is kept null</param> /// <param name="CellParser">Parses a string value to the property-type. This Type implements <see cref="ICellParser"/> with empty constructor.</param> /// <param name="WriteStyleNumberformatFormat">e.g. '€#,##0.00': used for writing back into excel to specify cell format </param> public ExcelColumnConfigurationAttribute(ExcelColumnEnum ColumnIndex, string ColumnHeaderText = null, object DefaultValue = null, Type cellParser = null) : this((int)ColumnIndex, ColumnHeaderText, DefaultValue, cellParser) { }