Beispiel #1
0
        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));
        }
Beispiel #2
0
 /// <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)
 {
 }