Transaction GetTransaction(int rowNumber)
{
IExcelSheet sheet = _app.GetActiveWorkbook().GetActiveSheet();
ExcelSheetData data = sheet.GetSheetData(1, rowNumber);
if (data.Rows.Count < 2)
{
return(null);
}
ExcelRowData headerRowData = data.Rows[0];
ExcelRowData valueRowData = data.Rows[1];
string separator = ";";
string header = headerRowData.GetValuesInLine(separator);
string values = valueRowData.GetValuesInLine(separator);
TransactionParser parser = new TransactionParser(header, separator.FirstOrDefault());
Transaction transaction = parser.ParseFromLine(values);
return(transaction);
}
public void InsertPivotTableToSheet(int year, int month)
{
_app.Connect();
IExcelWorkbook workbook = _app.GetActiveWorkbook();
IExcelSheet sheet = workbook.GetActiveSheet();
ExcelSheetData sheetData = sheet.GetSheetData();
Transaction transaction = new Transaction();
int firstCol = Transaction.GetIndex(() => transaction.Owner);
int lastCol = Transaction.GetIndex(() => transaction.Amount);
int numberOfRows = sheetData.Rows.Count;
ExcelRangeInfo dataRange = new ExcelRangeInfo(1, firstCol, numberOfRows, lastCol + 1);
ExcelRangeInfo insertRange = new ExcelRangeInfo(numberOfRows + 2, firstCol);
string[] rows =
{
transaction.GetName(() => transaction.TransactionType),
transaction.GetName(() => transaction.Category),
transaction.GetName(() => transaction.SubCategory)
};
string[] columns =
{
transaction.GetName(() => transaction.Owner)
};
string[] values =
{
transaction.GetName(() => transaction.Amount)
};
PivotTableData data = new PivotTableData
{
Range = dataRange,
InsertCell = insertRange,
Columns = columns.ToList(),
Rows = rows.ToList(),
Values = values.ToList(),
Name = "Test01",
StyleName = "PivotStyleMedium2"
};
sheet.InsertPivotTable(data);
}
public FileData GetTransactionData()
{
List <string> lines = new List <string>();
ExcelSheetData sheetData = _sheet.GetSheetData();
foreach (var row in sheetData.Rows)
{
string line = string.Join(_separator, row.Values);
lines.Add(line);
}
return(new FileData()
{
Lines = lines, Name = _sheet.Name, Separator = _separator
});
}