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