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