示例#1
0
        private void LoadFile(string fileName)
        {
            List <string> fileLines;

            try
            {
                fileLines = File.ReadAllLines(fileName).ToList();
            }
            catch (Exception ex)
            {
                ShowMessage(ex.Message);
                return;
            }
            if (!fileLines.Any() || fileLines.First().Substring(0, 4) != "VOL1")
            {
                return;
            }

            var volumeHeader = fileLines.ElementAt(0);
            var hdr1         = fileLines.ElementAt(1);
            var eof          = fileLines.FindIndex(l => l.Substring(0, 3) == "EOF");
            var data         = fileLines.Where((s, i) => i > 3 && i < eof);
            var utl1         = fileLines.ElementAt(eof + 2);
            var file         = new BacsFile
            {
                PaymentFileId    = int.Parse(volumeHeader.Substring(5, 5)),
                CreationDate     = GetDate(hdr1.Substring(42, 5)),
                Transactions     = data.Where(l => l.Substring(15, 2) == "99").Select(GetTransaction),
                ContraRecords    = data.Where(l => l.Substring(15, 2) == "17").Select(GetTransaction),
                DDIs             = data.Where(l => l.Substring(15, 2) == "0N").Select(GetTransaction),
                DebitValueTotal  = decimal.Parse(utl1.Substring(4, 13)) / 100M,
                CreditValueTotal = decimal.Parse(utl1.Substring(17, 13)) / 100M,
                DebitItemCount   = int.Parse(utl1.Substring(30, 7)),
                CreditItemCount  = int.Parse(utl1.Substring(37, 7))
            };

            var dlg = new SaveFileDialog
            {
                AddExtension    = true,
                DefaultExt      = ".xlsx",
                OverwritePrompt = false,
                FileName        = Path.ChangeExtension(fileName, ".xlsx")
            };
            var result = dlg.ShowDialog();

            if (result.Value)
            {
                file.FileName      = Path.ChangeExtension(dlg.FileName, ".xlsx");
                btnOpen.Visibility = Visibility.Collapsed;
                spinner.Visibility = Visibility.Visible;
                _backgroundWorker.RunWorkerAsync(file);
            }
        }
示例#2
0
        public string SaveExcelFile(BacsFile file)
        {
            var       app       = new Application();
            Workbook  workBook  = null;
            Worksheet workSheet = null;

            app.Visible = false;
            workBook    = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            try
            {
                workSheet = workBook.Worksheets[1];

                workSheet.Cells[1, 1] = "Account ID";
                workSheet.Cells[1, 2] = "Amount";
                workSheet.Cells[1, 3] = "Account name";
                workSheet.Cells[1, 4] = "Destination sort code";
                workSheet.Cells[1, 5] = "Destination a/c number";
                workSheet.Cells[1, 6] = "Originating sort code";
                workSheet.Cells[1, 7] = "Originating a/c number";
                workSheet.get_Range("A1", "G1").Font.Bold    = true;
                (workSheet.Columns[1] as Range).ColumnWidth  = "16";
                (workSheet.Columns[2] as Range).ColumnWidth  = "10";
                (workSheet.Columns[3] as Range).ColumnWidth  = "21";
                (workSheet.Columns[4] as Range).ColumnWidth  = "19";
                (workSheet.Columns[5] as Range).ColumnWidth  = "21";
                (workSheet.Columns[6] as Range).ColumnWidth  = "19";
                (workSheet.Columns[7] as Range).ColumnWidth  = "21";
                (workSheet.Columns[2] as Range).NumberFormat = "£0.00";

                var row = 2;
                if (file.Transactions.Any())
                {
                    for (var t = 0; t < file.Transactions.Count(); t++)
                    {
                        workSheet.Cells[row + t, 1] = file.Transactions.ElementAt(t).Beneficiary;
                        workSheet.Cells[row + t, 2] = file.Transactions.ElementAt(t).Amount;
                        workSheet.Cells[row + t, 3] = file.Transactions.ElementAt(t).AccountName;
                        workSheet.Cells[row + t, 4] = file.Transactions.ElementAt(t).DestSortCode;
                        workSheet.Cells[row + t, 5] = file.Transactions.ElementAt(t).DestAccountNumber;
                        workSheet.Cells[row + t, 6] = file.Transactions.ElementAt(t).OrigSortCode;
                        workSheet.Cells[row + t, 7] = file.Transactions.ElementAt(t).OrigAccountNumber;
                    }
                    row += 2 + file.Transactions.Count();
                }
                if (file.ContraRecords.Any())
                {
                    for (var c = 0; c < file.ContraRecords.Count(); c++)
                    {
                        workSheet.Cells[row + c, 1] = file.ContraRecords.ElementAt(c).Beneficiary;
                        workSheet.Cells[row + c, 2] = file.ContraRecords.ElementAt(c).Amount;
                        workSheet.Cells[row + c, 3] = file.ContraRecords.ElementAt(c).AccountName;
                        workSheet.Cells[row + c, 4] = file.ContraRecords.ElementAt(c).DestSortCode;
                        workSheet.Cells[row + c, 5] = file.ContraRecords.ElementAt(c).DestAccountNumber;
                        workSheet.Cells[row + c, 6] = file.ContraRecords.ElementAt(c).OrigSortCode;
                        workSheet.Cells[row + c, 7] = file.ContraRecords.ElementAt(c).OrigAccountNumber;
                    }
                    row += 2 + file.ContraRecords.Count();
                }
                if (file.DDIs.Any())
                {
                    for (var d = 0; d < file.DDIs.Count(); d++)
                    {
                        workSheet.Cells[row + d, 1] = file.DDIs.ElementAt(d).Beneficiary;
                        workSheet.Cells[row + d, 2] = file.DDIs.ElementAt(d).Amount;
                        workSheet.Cells[row + d, 3] = file.DDIs.ElementAt(d).AccountName;
                        workSheet.Cells[row + d, 4] = file.DDIs.ElementAt(d).DestSortCode;
                        workSheet.Cells[row + d, 5] = file.DDIs.ElementAt(d).DestAccountNumber;
                        workSheet.Cells[row + d, 6] = file.DDIs.ElementAt(d).OrigSortCode;
                        workSheet.Cells[row + d, 7] = file.DDIs.ElementAt(d).OrigAccountNumber;
                    }
                    row += 2 + file.DDIs.Count();
                }
                if (file.Transactions.Any())
                {
                    workSheet.Cells[row, 1]     = "Credit Value Total";
                    workSheet.Cells[row, 2]     = file.CreditValueTotal;
                    workSheet.Cells[row, 4]     = "Credit Item Count";
                    workSheet.Cells[row, 5]     = file.CreditItemCount;
                    workSheet.Cells[row + 1, 1] = "Debit Value Total";
                    workSheet.Cells[row + 1, 2] = file.DebitValueTotal;
                    workSheet.Cells[row + 1, 4] = "Debit Item Count";
                    workSheet.Cells[row + 1, 5] = file.DebitItemCount;
                    workSheet.get_Range($"A{row}", $"A{row}").Font.Bold         = true;
                    workSheet.get_Range($"D{row}", $"D{row}").Font.Bold         = true;
                    workSheet.get_Range($"A{row + 1}", $"A{row + 2}").Font.Bold = true;
                    workSheet.get_Range($"D{row + 1}", $"D{row + 2}").Font.Bold = true;
                }
                workBook.Worksheets[1].Name = $"PF-{file.PaymentFileId}";
                workBook.SaveAs(file.FileName);
                workBook.Close();

                return("");
            }

            catch (Exception ex)
            {
                return(ex.Message);
            }

            finally
            {
                app.Quit();
                Marshal.ReleaseComObject(workSheet);
                Marshal.ReleaseComObject(workBook);
                Marshal.ReleaseComObject(app);
            }
        }