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