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