public static SkandiabankenBankStatement GetBankStatementFromTransactions(List <SBankenTransaction> sBankenTransactions)
{
var date = new Date();
var firstDayOfTheYear = date.FirstDayOfTheYear;
var bankStatment = new SkandiabankenBankStatement
{
Transactions = sBankenTransactions,
IncomingBalanceDate = firstDayOfTheYear,
IncomingBalanceLabel = string.Format("INNGÅENDE SALDO {0:dd.MM.yyyy}", firstDayOfTheYear),
IncomingBalance = 0,
OutgoingBalanceDate = DateTime.MinValue,
OutgoingBalanceLabel = null,
OutgoingBalance = 0
};
return(bankStatment);
}
public static SkandiabankenBankStatement ReadBankStatement(string skandiabankenTransactionsFilePath)
{
var skandiabankenTransactions = new List <SBankenTransaction>();
var wb = new XLWorkbook(skandiabankenTransactionsFilePath);
var ws = wb.Worksheet("Kontoutskrift");
var startColumn = ws.Column(1);
var firstCellFirstColumn = startColumn.FirstCellUsed();
var lastCellFirstColumn = startColumn.LastCellUsed();
var lastCellLastColumn = lastCellFirstColumn.WorksheetRow().AsRange().LastColumnUsed().LastCellUsed();
// check edge case where first cell and last cell is the same (i.e. the spreadsheet contain no data)
if (firstCellFirstColumn == lastCellFirstColumn)
{
// the spreadsheet contain no data
Console.Out.WriteLine("ERROR! Bank statement contains no data.");
return(null);
}
// Get a range with the transaction data
var transactionRange = ws.Range(firstCellFirstColumn, lastCellLastColumn).RangeUsed();
// Treat the range as a table
var transactionTable = transactionRange.AsTable();
// Get the transactions
foreach (var row in transactionTable.DataRange.Rows())
{
// BOKFØRINGSDATO
// RENTEDATO
// ARKIVREFERANSE
// TYPE
// TEKST
// UT FRA KONTO
// INN PÅ KONTO
var skandiabankenTransaction = new SBankenTransaction();
skandiabankenTransaction.TransactionDate = row.Field(0).GetDateTime();
skandiabankenTransaction.InterestDate = row.Field(1).GetDateTime();
skandiabankenTransaction.ArchiveReference = row.Field(2).GetString();
skandiabankenTransaction.Type = row.Field(3).GetString();
skandiabankenTransaction.Text = row.Field(4).GetString();
skandiabankenTransaction.OutAccount = row.Field(5).GetValue <decimal>();
skandiabankenTransaction.InAccount = row.Field(6).GetValue <decimal>();
// set account change
decimal accountChange = skandiabankenTransaction.InAccount - skandiabankenTransaction.OutAccount;;
skandiabankenTransaction.AccountChange = accountChange;
if (accountChange > 0)
{
skandiabankenTransaction.AccountingType = SBankenTransaction.AccountingTypeEnum.IncomeUnknown;
}
else
{
skandiabankenTransaction.AccountingType = SBankenTransaction.AccountingTypeEnum.CostUnknown;
}
skandiabankenTransactions.Add(skandiabankenTransaction);
}
// find the incoming and outgoing balance
var incomingBalanceCell = ws.Cell(lastCellLastColumn.Address.RowNumber + 2, lastCellLastColumn.Address.ColumnNumber);
var outgoingBalanceCell = ws.Cell(1, lastCellLastColumn.Address.ColumnNumber);
decimal incomingBalance = incomingBalanceCell.GetValue <decimal>();
decimal outgoingBalance = outgoingBalanceCell.GetValue <decimal>();
var incomingBalanceLabelCell = ws.Cell(lastCellLastColumn.Address.RowNumber + 2, lastCellLastColumn.Address.ColumnNumber - 2);
var outgoingBalanceLabelCell = ws.Cell(1, lastCellLastColumn.Address.ColumnNumber - 2);
var incomingBalanceLabel = incomingBalanceLabelCell.GetString();
var outgoingBalanceLabel = outgoingBalanceLabelCell.GetString();
var incomingBalanceDate = ExcelUtils.GetDateFromBankStatementString(incomingBalanceLabel);
var outgoingBalanceDate = ExcelUtils.GetDateFromBankStatementString(outgoingBalanceLabel);
var bankStatment = new SkandiabankenBankStatement
{
Transactions = skandiabankenTransactions,
IncomingBalanceDate = incomingBalanceDate,
IncomingBalanceLabel = incomingBalanceLabel,
IncomingBalance = incomingBalance,
OutgoingBalanceDate = outgoingBalanceDate,
OutgoingBalanceLabel = outgoingBalanceLabel,
OutgoingBalance = outgoingBalance
};
return(bankStatment);
}
