private void InsertSingleReport(IExcelWorkbook workBook, TransactionReport report, IComparer <Transaction> transactionComparer) { ExcelSheetData sheetData = new ExcelSheetData(); TransactionManager transactionManager = new TransactionManager(); ColorConverter converter = new ColorConverter(); var firstTransaction = report.Transactions.First(); List <string> headers = Transaction.GetTransactionHeaders().ToList(); sheetData.Rows.Add(new ExcelRowData(COL_FIRST, ROW_HEADERS, headers.ToArray())); int col_LAST = COL_FIRST + headers.Count - 1; int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Category)); int col_AMOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.Amount)); int col_ACCOUNT = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => firstTransaction.AccountNumber)); int col_TRANSACTION_NUMBER = COL_FIRST + headers.IndexOf( ReflectionTools.GetName(() => firstTransaction.TransactionNumber)); int col_TRANSACTION_COMMENT = COL_FIRST + headers.IndexOf( ReflectionTools.GetName(() => firstTransaction.Comment)); int iTransactionRow = 1; foreach (var transaction in report.Transactions) { iTransactionRow++; object[] values = ReflectionTools .GetPropertyValues(transaction, headers).ToArray(); sheetData.Rows.Add(new ExcelRowData(COL_FIRST, iTransactionRow, values)); if (transaction.Color.IsNotNull()) { ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST); ExcelFormatData colorFormat = new ExcelFormatData(iRange); colorFormat.Background = (Color)converter.ConvertFromString(transaction.Color); sheetData.Formats.Add(colorFormat); } } int row_LAST_TRANSACTION = ROW_FIRST_TRANSACTION + report.Transactions.Count - 1; int row_SUBTOTAL = row_LAST_TRANSACTION + 1; int row_SummaryTable = row_SUBTOTAL + 2; iTransactionRow = row_SummaryTable; Tree <string> categoryTree = GetCategoryTree(report.Transactions); foreach (var categoryItem in categoryTree.Children) { string category = categoryItem.Value; var subCategories = categoryItem.Children.Select(i => i.Value); foreach (string subCategory in subCategories) { ExcelCellData categoryCell = new ExcelCellData(iTransactionRow, col_CATEGORY); categoryCell.Value = category; ExcelCellData subCategoryCell = new ExcelCellData(iTransactionRow, COLUMN_SUB_CATEGORY); subCategoryCell.Value = subCategory; } } ExcelRangeInfo transactionsRange = new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_CATEGORY, row_LAST_TRANSACTION, col_AMOUNT); ExcelRangeInfo subTotalRange = new ExcelRangeInfo(ROW_FIRST_TRANSACTION, col_AMOUNT, row_LAST_TRANSACTION, col_AMOUNT); ExcelFormula subTotalFormula = ExcelFormulaHelper.GetSubTotalSum(row_SUBTOTAL, col_AMOUNT, subTotalRange); sheetData.Formulas.Add(subTotalFormula); ExcelBorderData subTotalBorderData = new ExcelBorderData(subTotalFormula.Range); subTotalBorderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous); sheetData.Borders.Add(subTotalBorderData); ExcelBorderData borderTable = new ExcelBorderData(COL_FIRST, ROW_HEADERS, col_LAST, row_LAST_TRANSACTION); borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlHairline, ExcelLineStyle.xlContinuous)); borderTable.Borders.Add(new ExcelBorderItem(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlMedium, ExcelLineStyle.xlContinuous)); sheetData.Borders.Add(borderTable); ExcelRangeInfo columnAccountRange = ExcelRangeInfo.CreateColumnRange(col_ACCOUNT); ExcelFormatData columnAccountFormat = new ExcelFormatData(columnAccountRange); columnAccountFormat.NumberFormat = "@"; sheetData.Formats.Add(columnAccountFormat); ExcelRangeInfo columnTransactionNumber = ExcelRangeInfo.CreateColumnRange(col_TRANSACTION_NUMBER); ExcelFormatData columnTransactionFormat = new ExcelFormatData(columnTransactionNumber); columnTransactionFormat.NumberFormat = "0"; sheetData.Formats.Add(columnTransactionFormat); IExcelSheet sheet = workBook.CreateSheet(report.Name); sheet.InsertSheetData(sheetData); int columnCount = headers.Count; sheet.SetColumnsAutoFit(1, columnCount); sheet.SetAutoFilter(ROW_HEADERS, COL_FIRST, ROW_HEADERS, col_LAST); }