예제 #1
0
        private void SQLiteButton_Click(object sender, EventArgs e)
        {
            SqliteSeeder.SeedDatabase();

            string password = Prompt.ShowDialog("Please enter MySQL password: ", string.Empty);
            var    mysqlDb  = new MySqlDb(password);

            MySqlSeeder.SeedDatabase(new SqlServerDb(), mysqlDb);

            var fileInfo = new FileInfo(ExcelOutputReports);

            ExcelReporter.CreateReports(mysqlDb, new SqliteDb(), fileInfo);

            MessageBox.Show(ReportsFromSQLiteAndMySQLCreatedSuccessfully, string.Empty, MessageBoxButtons.OK);
        }
        public void Verify_Report_Output_Two_Hotels_With_Multiple_Ratings()
        {
            IReporter reporter = new ExcelReporter();

            var input = JsonConvert.DeserializeObject <HotelCollection>(File.ReadAllText("input2.json", Encoding.Default));

            // get dummy input 2
            byte[] output = reporter.CreateReport(input.HotelList);

            // verify package
            using (ExcelPackage p = new ExcelPackage(new MemoryStream(output)))
            {
                var sheet = p.Workbook.Worksheets.FirstOrDefault(s => s.Name.Equals("Hotel 1"));

                Assert.IsNotNull(sheet);

                Assert.AreEqual(sheet.Name, "Hotel 1");
                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 1].Value).ToString(), @"14/03/2016 23:00:00");
                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 2].Value).ToString(), @"15/03/2016 23:00:00");
                Assert.AreEqual(sheet.Cells[2, 3].Value, 116.1);
                Assert.AreEqual(sheet.Cells[2, 4].Value, "EUR");
                Assert.AreEqual(@"Name 1", (String)sheet.Cells[2, 5].Value);
                Assert.AreEqual(2.0, sheet.Cells[2, 6].Value);
                Assert.AreEqual(sheet.Cells[2, 7].Value, false);

                sheet = p.Workbook.Worksheets.FirstOrDefault(s => s.Name.Equals("Hotel 2"));

                Assert.IsNotNull(sheet);

                Assert.AreEqual(sheet.Name, "Hotel 2");

                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 1].Value).ToString(), @"14/03/2016 23:00:00");
                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[2, 2].Value).ToString(), @"15/03/2016 23:00:00");
                Assert.AreEqual(sheet.Cells[2, 3].Value, 78.5);
                Assert.AreEqual(sheet.Cells[2, 4].Value, "EUR");
                Assert.AreEqual(@"Name 2", (String)sheet.Cells[2, 5].Value);
                Assert.AreEqual(2.0, sheet.Cells[2, 6].Value);
                Assert.AreEqual(sheet.Cells[2, 7].Value, true);

                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[3, 1].Value).ToString(), @"14/03/2016 23:00:00");
                Assert.AreEqual(DateTime.FromOADate((double)sheet.Cells[3, 2].Value).ToString(), @"15/03/2016 23:00:00");
                Assert.AreEqual(sheet.Cells[3, 3].Value, 116.1);
                Assert.AreEqual(sheet.Cells[3, 4].Value, "EUR");
                Assert.AreEqual(@"Name 3", (String)sheet.Cells[3, 5].Value);
                Assert.AreEqual(1.0, sheet.Cells[3, 6].Value);
                Assert.AreEqual(sheet.Cells[3, 7].Value, false);
            }
        }
예제 #3
0
        static async Task Main(string[] args)
        {
            var data = Enumerable
                       .Range(0, 1000)
                       .Select(x => new User
            {
                Name     = $"{nameof(User.Name)} {x}",
                Role     = x % 2 == 0 ? Role.Admin : Role.Guest,
                IsLocked = x % 2 != 0
            })
                       .ToArray();

            var reporter = new ExcelReporter <User>();

            var report = reporter.Export(data, new ReportOptions());

            await using var s = File.Create("data.xlsx");
            await s.WriteAsync(report);
        }
예제 #4
0
 private void Export() => ExcelReporter.SaveAsExcell(this);
예제 #5
0
 /// <summary>
 /// Set hyperlinks.
 /// </summary>
 /// <param name="cell">The cell to work on.</param>
 /// <param name="hyperlink">The hyperlinks to be associated in the given cell.</param>
 private void SetHyperlinks(Range cell, ExcelReporter.Hyperlink hyperlink)
 {
     // set the value
     cell.Hyperlinks.Add(cell, hyperlink.Address, Type.Missing, Type.Missing, hyperlink.TextToDisplay);
 }
예제 #6
0
        /// <summary>
        /// Highlight specific text segement .
        /// </summary>
        /// <param name="cell">The cell to work on.</param>
        /// <param name="coloredScript">The colored script.</param>
        private void SetColoredTextScript(Range cell, ExcelReporter.ColoredScript coloredScript)
        {
            // set the value
            cell.Value = coloredScript.Text;

            // set the color
            foreach (ExcelReporter.ColoredTextSegment segment in coloredScript.ColoredTextSegments)
            {
                Characters characters = cell.get_Characters(segment.StartIndex, segment.Length);
                characters.Font.ColorIndex = segment.Color;
            }
        }
예제 #7
0
        /// <summary>
        /// Create new sheet in currently active workbook.
        /// </summary>
        /// <param name="strSheetName">The sheet name.</param>
        /// <param name="sheetContent">The sheet content.</param>
        public void AddNewSheet(string strSheetName,
            ExcelReporter.SheetContent sheetContent)
        {
            Helper.ThrowIfNull(_workbook);

            if (_currentSheetIndex > 0)
            {
                _worksheet = (_Worksheet)_workbook.Worksheets.Add(Missing.Value,
                    _workbook.ActiveSheet,
                    1,
                    XlSheetType.xlWorksheet);
            }

            _worksheet.Name = strSheetName;
            string finalColumnIndex = string.Empty;
            string finalCellIndex = string.Empty;
            string finalRawIndex = string.Empty;
            string categoryTitle = string.Empty;
            Collection<string> columnTitles = new Collection<string>();
            int inColumn = 1;
            foreach (ExcelReporter.ColumnContent column in sheetContent.Columns)
            {
                _worksheet.Cells[1, inColumn] = column.Title;
                if (string.IsNullOrEmpty(categoryTitle))
                {
                    categoryTitle = column.Title;
                }
                else
                {
                    columnTitles.Add(column.Title);
                }

                int rawEndIndex = column.RecordCount + 1;
                string columnIndex = GetColumnIndex(inColumn);
                string endIndex = columnIndex + rawEndIndex.ToString();

                switch (column.Type)
                {
                    case ExcelReporter.ContentTypeCode.ColoredScript:
                        for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
                        {
                            SetColoredTextScript(SelectCell(_worksheet, rawIndex, inColumn),
                                (ExcelReporter.ColoredScript)column.RecordCollection[rawIndex - 2]);
                        }

                        break;
                    case ExcelReporter.ContentTypeCode.HyperLink:
                        for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
                        {
                            SetHyperlinks(SelectCell(_worksheet, rawIndex, inColumn),
                                (ExcelReporter.Hyperlink)column.RecordCollection[rawIndex - 2]);
                        }

                        break;
                    default:
                        for (int rawIndex = 2; rawIndex <= rawEndIndex; rawIndex++)
                        {
                            _worksheet.Cells[rawIndex, inColumn] = column.RecordCollection[rawIndex - 2];
                        }

                        break;
                }

                finalRawIndex = rawEndIndex.ToString();
                finalColumnIndex = columnIndex;
                finalCellIndex = endIndex;
                if (column.Width == ExcelReporter.Width.AutoFit)
                {
                    _worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).EntireColumn.AutoFit();
                }
                else if (column.Width == ExcelReporter.Width.Narrow)
                {
                    _worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 50;
                }
                else if (column.Width == ExcelReporter.Width.Medium)
                {
                    _worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 100;
                }
                else if (column.Width == ExcelReporter.Width.Wide)
                {
                    _worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).ColumnWidth = 150;
                }

                if (column.WarpText)
                {
                    _worksheet.get_Range(finalColumnIndex + "1", finalCellIndex).EntireColumn.WrapText = true;
                }

                inColumn++;
            }

            string finalTitleIndex = finalColumnIndex + "1";
            _worksheet.get_Range("A1", finalTitleIndex).Font.Bold = true;
            _worksheet.get_Range("A1", finalTitleIndex).Font.ColorIndex = 1;
            _worksheet.get_Range("A1", finalTitleIndex).Font.Size = 12;
            _worksheet.get_Range("A1", finalTitleIndex).VerticalAlignment = XlVAlign.xlVAlignCenter;
            _worksheet.get_Range("A1", finalTitleIndex).HorizontalAlignment = XlHAlign.xlHAlignCenter;
            _worksheet.get_Range("A1", finalTitleIndex).EntireRow.WrapText = true;

            _worksheet.get_Range("A2", finalCellIndex).Font.Size = 11;
            _worksheet.get_Range("A2", finalCellIndex).VerticalAlignment = XlVAlign.xlVAlignCenter;
            _worksheet.get_Range("A2", finalCellIndex).HorizontalAlignment = XlHAlign.xlHAlignLeft;

            switch (sheetContent.RowHeight)
            {
                case ExcelReporter.Height.Short:
                    _worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 18;
                    break;
                case ExcelReporter.Height.Medium:
                    _worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 28;
                    break;
                case ExcelReporter.Height.Tall:
                    _worksheet.get_Range("A2", finalCellIndex).EntireRow.RowHeight = 38;
                    break;
                default:
                    break;
            }

            if (sheetContent.DrawChart)
            {
                DrawChartOnSheet(strSheetName,
                    categoryTitle,
                    "values",
                    columnTitles.Count,
                    finalRawIndex,
                    finalColumnIndex,
                    finalCellIndex,
                    columnTitles);
            }

            _currentSheetIndex++;
        }