예제 #1
0
        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);
        }
예제 #2
0
        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);
        }
예제 #3
0
        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);
            }
        }