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