One line is getting overwritten in writing to an excel template in C#

[philky001]

In the following code I am reading a text file that was created from a report and reading it to an excel template. If you look at Balance Forward this one is getting overwritten by the transactions loop. This is a typical report with header type data and then several lines of description. I put line 17 for the Balance forward which is where it should be placed on the excel but it doesn't display. i beleive it is getting over written by the Transactions loop. any ideas how i can resolve?


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop.Excel;

namespace TemplateMapping
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void btnDataFile_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialogData.ShowDialog();

if (result == DialogResult.OK) // Test result.
{
txtBoxDateFile.Text = openFileDialogData.FileName;
}
}

private void btnStartProcessing_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Sheets sheets = null;
Microsoft.Office.Interop.Excel.Worksheet currentSheet = null;
try
{

excelApp = new Microsoft.Office.Interop.Excel.Application();
workbook = excelApp.Workbooks.Open(txtBoxTemplateFile.Text, 0, false, 5, "", "",
false, XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
currentSheet = workbook.Sheets[1];

using (StreamReader sr = new StreamReader(txtBoxDateFile.Text))
{
String line;
bool newPage = false;
int transactionRowNo = 17;
while ((line = sr.ReadLine()) != null)
{
if (!newPage)
{

if (line.Contains("STATEMENT DATE"))
{
string statementDate = line.Replace("STATEMENT DATE", "").Trim();
currentSheet.Cells[7, 14] = statementDate;
}
else if (line.Contains("BILLING PARTY 1"))
{
string billingParty1 = line.Replace("BILLING PARTY 1", "");
if (!string.IsNullOrWhiteSpace(billingParty1))
{
currentSheet.Cells[9, 1] = billingParty1.Trim();
}
}
else if (line.Contains("BILLING PARTY 2"))
{
string billingParty2 = line.Replace("BILLING PARTY 2", "");
if (!string.IsNullOrWhiteSpace(billingParty2))
{
currentSheet.Cells[10, 1] = billingParty2.Trim();
}
}
else if (line.Contains("BILLING PARTY 3"))
{
string billingParty3 = line.Replace("BILLING PARTY 3", "");
if (!string.IsNullOrWhiteSpace(billingParty3))
{
currentSheet.Cells[11, 1] = billingParty3.Trim();
}
}
else if (line.Contains("BILLING PARTY 4"))
{
string billingParty4 = line.Replace("BILLING PARTY 4", "");
if (!string.IsNullOrWhiteSpace(billingParty4))
{
currentSheet.Cells[12, 1] = billingParty4.Trim();
}
}
else if (line.Contains("BILLING PARTY 5"))
{
string billingParty5 = line.Replace("BILLING PARTY 5", "");
if (!string.IsNullOrWhiteSpace(billingParty5))
{
currentSheet.Cells[13, 1] = billingParty5.Trim();
}
}
else if (line.Contains("RESIDENT NAME"))
{
string residentName = line.Replace("RESIDENT NAME", "").Trim();
currentSheet.Cells[9, 13] = residentName;
currentSheet.Cells[39, 1] = residentName;
}
else if (line.Contains("ACCOUNT NUMBER"))
{
string accountNumber = line.Replace("ACCOUNT NUMBER", "").Trim();
currentSheet.Cells[10, 13] = accountNumber;
currentSheet.Cells[39, 8] = accountNumber;
}
else if (line.Contains("ROOM NUMBER"))
{
string roomNo = line.Replace("ROOM NUMBER", "").Trim();
currentSheet.Cells[11, 13] = roomNo;
}
else if (line.Contains("ADMISSION DATE"))
{
string admissionDate = line.Replace("ADMISSION DATE", "").Trim();
currentSheet.Cells[12, 13] = admissionDate;

}
else if (line.Contains("AMOUNT DUE"))
{
string amountDue = line.Replace("AMOUNT DUE", "").Trim();
currentSheet.Cells[13, 13] = amountDue;
currentSheet.Cells[39, 16] = amountDue;

}
else if (line.Contains("AMOUNT REMITTED"))
{
string amountRemit = line.Replace("AMOUNT REMITTED", "").Trim();
currentSheet.Cells[14, 13] = amountRemit;
}





else if (line.Contains("BALANCE FORWARD"))
{
string balanceForward = line.Replace("OPENING BALANCE ", "").Trim();

currentSheet.Cells[17, 11] = balanceForward;
}

else if (line.Contains("TRANSACTION "))
{
string transaction = line.Replace("TRANSACTION ", "");
if (!string.IsNullOrWhiteSpace(transaction))
{
bool isAmountColumn = false;

int firstSpaceIndex = transaction.IndexOf(" ");
string date = transaction.Substring(0, firstSpaceIndex);

currentSheet.Cells[transactionRowNo, 1] = date;

transaction = transaction.Replace(date, "");
if (transaction.EndsWith(" "))
{
//if true it means last value is amount value
transaction = transaction.Trim();
int lastSpaceIndex = transaction.LastIndexOf(" ");
string amount = transaction.Substring(lastSpaceIndex, (transaction.Length) - lastSpaceIndex);
currentSheet.Cells[transactionRowNo, 13] = amount;
transaction = transaction.Replace(amount, "");

}
else
{
transaction = transaction.Trim();
int lastSpaceIndex = transaction.LastIndexOf(" ");
string balance = transaction.Substring(lastSpaceIndex, (transaction.Length) - lastSpaceIndex);
currentSheet.Cells[transactionRowNo, 16] = balance;
transaction = transaction.Replace(balance, "");
}

transaction = transaction.Trim();

if (!string.IsNullOrWhiteSpace(transaction))
{
currentSheet.Cells[transactionRowNo, 4] = transaction;
}
transactionRowNo += 1;
}
}
else if (line.Contains("TOTALS LINE 1"))
{
string total1 = line.Replace("TOTALS LINE 1", "").Trim();
if (!string.IsNullOrWhiteSpace(total1))
{
int firstSpaceIndex = total1.IndexOf(" ");
string amount = total1.Substring(0, firstSpaceIndex);
currentSheet.Cells[35, 13] = amount;
total1 = total1.Remove(0, firstSpaceIndex);

total1 = total1.Trim();
currentSheet.Cells[35, 16] = total1;

}

}
else if (line.Contains("REMARKS 1 "))
{
string remarks1 = line.Replace("REMARKS 1 ", "").Trim();
currentSheet.Cells[42, 1] = remarks1;
}
else if (line.Contains("REMARKS 2 "))
{
string remarks2 = line.Replace("REMARKS 2 ", "").Trim();
currentSheet.Cells[43, 1] = remarks2;
}
else if (line.Contains("REMARKS 3 "))
{
string remarks3 = line.Replace("REMARKS 3 ", "").Trim();
currentSheet.Cells[44, 1] = remarks3;
newPage = true;
}


}

}
}

workbook.Save();
workbook.Close(null, null, null);
excelApp.Quit();
MessageBox.Show("Processing Completed");
}
catch (Exception ex)
{
// Let the user know what went wrong.
Console.WriteLine("The file could not be read:");
Console.WriteLine(ex.Message);
workbook.Close(null, null, null);
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(currentSheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);



GC.Collect();
}
}

private void btnTemplateFile_Click(object sender, EventArgs e)
{
DialogResult result = openFileDialogTemplate.ShowDialog();

if (result == DialogResult.OK) // Test result.
{
txtBoxTemplateFile.Text = openFileDialogTemplate.FileName;
}
}
}
}