private void UpdateSingleReport(IExcelWorkbook workBook, TransactionReport report)
{
ExcelSheetData sheetData = new ExcelSheetData();
List <string> headers = Transaction.GetTransactionHeaders().ToList();
ColorConverter converter = new ColorConverter();
int col_LAST = COL_FIRST + headers.Count - 1;
Transaction template = new Transaction();
int col_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.Category));
int col_SUB_CATEGORY = COL_FIRST + headers.IndexOf(ReflectionTools.GetName(() => template.SubCategory));
int iTransactionRow = 2;
foreach (var transaction in report.Transactions)
{
ExcelCellData categoryData = new ExcelCellData(iTransactionRow, col_CATEGORY);
categoryData.Value = transaction.Category;
sheetData.Cells.Add(categoryData);
ExcelCellData subCategoryData = new ExcelCellData(iTransactionRow, col_SUB_CATEGORY);
subCategoryData.Value = transaction.SubCategory;
sheetData.Cells.Add(subCategoryData);
Color color = Color.White;
if (transaction.Color.IsNotNull())
{
color = (Color)converter.ConvertFromString(transaction.Color);
}
ExcelRangeInfo iRange = new ExcelRangeInfo(iTransactionRow, COL_FIRST, iTransactionRow, col_LAST);
ExcelFormatData colorFormat = new ExcelFormatData(iRange);
colorFormat.Background = color;
sheetData.Formats.Add(colorFormat);
iTransactionRow++;
}
IExcelSheet sheet = workBook.GetSheet(report.Name);
sheet.InsertSheetData(sheetData);
}
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);
}
public void GenerateSheets()
{
int YEAR = 2017;
object[] headers =
{
@"Project/Activity", "Feature", "Employee", "Comment", "Business Line (BL)",
"Time(date)", "Location", "Work Units"
};
string PATH = @"c:\Maciek\Timesheets\2017";
int COL_FIRST = 1;
int COL_LAST = 8;
int ROW_FIRST = 1;
int ROW_DATA_FIRST = 2;
ExcelApp excel = new ExcelApp();
excel.CreateNewInstance();
for (int m = 12; m >= 1; m--)
{
IExcelWorkbook workbook = excel.CreateAndActivateNewWorkbook();
string workBookName = $"Protokol_MSzczudlo_{YEAR}_{m:D2}.xlsx";
string workBookPath = Path.Combine(PATH, workBookName);
int daysInMonth = DateTime.DaysInMonth(YEAR, m);
int ROW_LAST = daysInMonth + 1;
int ROW_SUM = ROW_LAST + 1;
ExcelSheetData sheetData = new ExcelSheetData();
ExcelRowData headerRowData = new ExcelRowData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowData.Values = headers.ToList();
sheetData.Rows.Add(headerRowData);
ExcelFormatData headerRowFormat = new ExcelFormatData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
headerRowFormat.Background = Color.LightGray;
headerRowFormat.IsFontBold = true;
headerRowFormat.HorizontalAlignment = ExcelHorizontalAlignmentType.Center;
sheetData.Formats.Add(headerRowFormat);
for (int d = 1; d <= daysInMonth; d++)
{
object[] values = new object[8];
int row = d + 1;
DateTime iDay = new DateTime(YEAR, m, d);
values[5] = iDay.ToShortDateString();
bool isFreeDay = iDay.DayOfWeek == DayOfWeek.Saturday || iDay.DayOfWeek == DayOfWeek.Sunday;
if (isFreeDay)
{
ExcelFormatData iFormatData = new ExcelFormatData(COL_FIRST, row, COL_LAST, row);
iFormatData.Background = Color.Salmon;
sheetData.Formats.Add(iFormatData);
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
else
{
values[0] = "IS Treasury";
values[2] = "Maciej Szczudło";
values[7] = 8;
ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
iRow.Values = values.ToList();
sheetData.Rows.Add(iRow);
}
ExcelBorderData borderData = new ExcelBorderData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_LAST);
borderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
borderData.Borders.AddBorder(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderData);
ExcelFormatData fontFormatData = new ExcelFormatData(borderData.Range);
fontFormatData.FontSize = 10;
sheetData.Formats.Add(fontFormatData);
}
string monthName = DateTimeTools.GetMonthName(m);
string sheetName = $"{monthName} {YEAR}";
IExcelSheet newSheet = workbook.CreateSheet(sheetName);
ExcelRangeInfo sumRange = new ExcelRangeInfo(ROW_DATA_FIRST, COL_LAST, ROW_LAST, COL_LAST);
ExcelFormula formula = ExcelFormulaHelper.GetSum(ROW_SUM, COL_LAST, sumRange);
sheetData.Formulas.Add(formula);
ExcelBorderData borderSumData = new ExcelBorderData(formula.Range);
borderSumData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
sheetData.Borders.Add(borderSumData);
newSheet.InsertSheetData(sheetData);
newSheet.SetColumnsAutoFit(COL_FIRST, COL_LAST);
newSheet.SetCellName(ROW_SUM, COL_LAST, "godziny");
excel.SetDisplayGridlines(false);
workbook.Save(workBookPath);
}
}