private void CreateTableSheet() { var sheet = _workbook.Worksheets.Add("Dane w tabeli"); //dodać nagłówek sheet.Cells[1, 1].Value = "ID"; sheet.Cells[1, 2].Value = "Imię"; sheet.Cells[1, 3].Value = "Drugie imię"; sheet.Cells[1, 4].Value = "Nazwisko"; sheet.Cells[1, 5].Value = "e-mail"; sheet.Cells[1, 6].Value = "Hasło"; //przygotować dane //Ok now format the values; using (var range = sheet.Cells[1, 1, 1, 6]) { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); range.Style.Font.Color.SetColor(Color.White); } using (var context = new wzlEntities()) { var r = 2; var items = context.Customer .Where(customer => customer.FirstName.StartsWith(SearchName)); //var items = context.Customer; foreach (var item in items) // gdy chcemy wszystkie to używamy context.Customer) { sheet.Cells[r, 1].Value = item.CustomerID.ToString(); sheet.Cells[r, 2].Value = item.FirstName; sheet.Cells[r, 3].Value = item.MiddleName; sheet.Cells[r, 4].Value = item.LastName; sheet.Cells[r, 5].Value = item.EmailAddress; sheet.Cells[r, 6].Value = item.PasswordHash; sheet.Cells[r, 1, r, 6].Style.Fill.PatternType = ExcelFillStyle.LightVertical; sheet.Cells[r, 1, r, 6].Style.Fill.BackgroundColor .SetColor(r % 2 == 0 ? Color.LightBlue : Color.LightSalmon); r++; } } sheet.Cells.AutoFitColumns(); // dopasowanie szerokości kolumn do tekstu //iterować dane do arkusza }
private void CreateChart() { var sheet = _workbook.Worksheets.Add("Wykres"); using (var context = new wzlEntities()) { var chartData = context.Customer.GroupBy(customer => customer.LastName) .Select(group => new { group.Key, Counter = group.Count() }) .GroupBy(entry => entry.Counter) .Select(value => new { value.Key, Counter = (double)value.Count() }); sheet.Cells[1, 1].Value = "Klucz"; sheet.Cells[1, 2].Value = "Wartość"; var k = 2; foreach (var item in chartData) { sheet.Cells[k, 1].Value = item.Key; sheet.Cells[k, 2].Value = item.Counter; k++; } var chart = (sheet.Drawings.AddChart("PieChart", eChartType.Pie3D) as ExcelPieChart); chart.Title.Text = "Total"; chart.SetPosition(0, 0, 5, 5); chart.SetSize(600, 300); var valueAddress = new ExcelAddress(2, 2, k - 1, 2); // zakres dla warości var legendAddress = new ExcelAddress(2, 1, k - 1, 1); // zakres danych dla legendy chart.Series.Add(valueAddress.Address, legendAddress.Address); chart.DataLabel.ShowCategory = true; chart.DataLabel.ShowPercent = true; chart.Legend.Border.LineStyle = eLineStyle.Solid; chart.Legend.Border.Fill.Style = eFillStyle.SolidFill; chart.Legend.Border.Fill.Color = Color.DarkBlue; sheet.Cells[k, 2].Formula = $"SUM({valueAddress.Address})"; } sheet.Calculate(); }
private void CreateChart() { // Utowrzenie sekcji i nagłówka var section = _document.AddSection(); var paragraph = section.AddParagraph("Grupowanie na wykresie"); paragraph.Style = "SectionTitle"; // Dodanie bookmarku pozwala klikać w spisie treści (interaktywny PDF) paragraph.AddBookmark("Wykres"); using (var context = new wzlEntities()) { var chartData = context.Customer.GroupBy(customer => customer.LastName) .Select(group => new { group.Key, Counter = group.Count() }) .GroupBy(entry => entry.Counter) .Select(value => new { value.Key, Counter = (double)value.Count() }); foreach (var item in chartData) { Debug.WriteLine($"key={item.Key} and value={item.Counter}"); } var chart = section.AddChart(); chart.Left = 0; chart.Width = Unit.FromCentimeter(16); chart.Height = Unit.FromCentimeter(12); var series = chart.SeriesCollection.AddSeries(); series.ChartType = ChartType.Column2D; //wybór typu wykresu series.Add(chartData.Select(item => item.Counter).ToArray()); var xAxis = chart.XValues.AddXSeries(); xAxis.Add(chartData.Select(item => item.Key.ToString()).ToArray()); series.HasDataLabel = true; chart.XAxis.MajorTickMark = TickMarkType.Outside; chart.XAxis.Title.Caption = "Częstość występowania nazwiska"; chart.YAxis.MajorTickMark = TickMarkType.Outside; chart.YAxis.HasMajorGridlines = true; chart.PlotArea.LineFormat.Color = Colors.DarkGray; //var chart1 = section.AddChart(); var chart1 = new Chart { Left = 0, Width = Unit.FromCentimeter(16), Height = Unit.FromCentimeter(12) }; var series1 = chart1.SeriesCollection.AddSeries(); series1.ChartType = ChartType.Line; //wybór typu wykresu series1.Add(chartData.Select(item => item.Counter).ToArray()); var xAxis1 = chart1.XValues.AddXSeries(); xAxis1.Add(chartData.Select(item => item.Key.ToString()).ToArray()); series.HasDataLabel = true; chart1.XAxis.MajorTickMark = TickMarkType.Outside; chart1.XAxis.Title.Caption = "Częstość występowania nazwiska"; chart1.YAxis.MajorTickMark = TickMarkType.Outside; chart1.YAxis.HasMajorGridlines = true; chart1.PlotArea.LineFormat.Color = Colors.DarkGray; section.Add(chart1); } }
private void CreateDataTable() { // Utowrzenie sekcji i nagłówka var section = _document.AddSection(); var paragraph = section.AddParagraph("Tabela danych"); paragraph.Style = "Title"; // Dodanie bookmarku pozwala klikać w spisie treści (interaktywny PDF) paragraph.AddBookmark("Tabela"); // Dodanie tabeli var table = section.AddTable(); table.Style = "Table"; table.Borders.Color = GetColor(System.Drawing.Color.Black); table.Borders.Width = 0.25; table.Borders.Left.Width = 0.5; table.Borders.Right.Width = 0.5; table.Rows.LeftIndent = 0; // Before you can add a row, you must define the columns var column = table.AddColumn("2.5cm"); column.Format.Alignment = ParagraphAlignment.Left; column = table.AddColumn("2.5cm"); column.Format.Alignment = ParagraphAlignment.Left; column = table.AddColumn("2.5cm"); column.Format.Alignment = ParagraphAlignment.Left; column = table.AddColumn("2.5cm"); column.Format.Alignment = ParagraphAlignment.Left; column = table.AddColumn("2.5cm"); column.Format.Alignment = ParagraphAlignment.Left; column = table.AddColumn("4.5cm"); column.Format.Alignment = ParagraphAlignment.Left; // Create the header of the table var row = table.AddRow(); row.HeadingFormat = true; row.Format.Alignment = ParagraphAlignment.Center; row.Format.Font.Bold = true; row.Shading.Color = GetColor(System.Drawing.Color.LightBlue); // Nagłówki string[] headers = { "ID", "Imię", "Drugie imię", "Nazwisko", "e-mail", "Hasło" }; // Dodanie nagłówków for (int i = 0; i < headers.Length; i++) { row.Cells[i].AddParagraph(headers[i]); row.Cells[i].Format.Font.Bold = true; row.Cells[i].Format.Alignment = ParagraphAlignment.Center; row.Cells[i].VerticalAlignment = VerticalAlignment.Center; row.Cells[i].MergeDown = 1; } row = table.AddRow(); row.Shading.Color = GetColor(System.Drawing.Color.LightBlue); table.SetEdge(0, 0, 6, 2, Edge.Box, BorderStyle.Single, 0.75, GetColor(System.Drawing.Color.Black)); // Dodanie wierszy z danymi- jeden wiersz w tabeli w bazie danych to jeden wiersz w tabeli w raporcie using (var context = new wzlEntities()) { var k = 0; var items = context.Customer .Where(customer => customer.FirstName.StartsWith(SearchName)); foreach (var item in items) // gdy chcemy wszystkie to używamy context.Customer) { row = table.AddRow(); row.Cells[0].AddParagraph(item.CustomerID.ToString()); row.Cells[1].AddParagraph(item.FirstName); row.Cells[2].AddParagraph(item.MiddleName ?? ""); row.Cells[3].AddParagraph(item.LastName); row.Cells[4].AddParagraph(item.EmailAddress ?? ""); row.Cells[5].AddParagraph(AdjustIfTooWideToFitIn(row.Cells[5], item.PasswordHash));//funkcja zawija tekst aby dopasować do komórki // Wiersze parzyste i nieparzyste podkreślane różnymi kolorami row.Shading.Color = GetColor(k % 2 == 0 ? System.Drawing.Color.LightGray : System.Drawing.Color.NavajoWhite); k++; } } }