Northwind Report

This example demonstrates how to generate report from database.

When data is to be display in table format with headers and cellborders like a database report .

Clicking generate button will launch the pdf and will show the database report set in code .

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text;
using OfficeComponent.Pdf;
using OfficeComponent.Pdf.Graphics;
using OfficeComponent.Pdf.Tables;

namespace OfficeComponent.Samples
{
    class NorthwindReportExample : ExampleBase
    {
        public NorthwindReportExample(string commonDataPath, string outputDir)
            : base(commonDataPath, outputDir)
        {
        }
        
        public NorthwindReportExample(string commonDataPath, string outputDir, string xmlFile) : base(commonDataPath, outputDir, xmlFile)
        {
        }

        public override string Execute()
        {
            // Create a new instance of PdfDocument class.
            PdfDocument doc = new PdfDocument();
            PdfFont font = new PdfStandardFont(PdfFontFamily.Helvetica, 8);

            // Create Pdf ben for drawing broder
            PdfPen borderPen = new PdfPen(PdfBrushes.DarkBlue);
            borderPen.Width = 0;

            // Create brush
            PdfColor color = new PdfColor(250, 250, 250);
            PdfSolidBrush brush = new PdfSolidBrush(color);

            // Create cell styles
            PdfCellStyle altStyle = new PdfCellStyle();
            altStyle.Font = font;
            altStyle.BackgroundBrush = brush;
            altStyle.BorderPen = borderPen;

            PdfCellStyle defStyle = new PdfCellStyle();
            defStyle.Font = font;
            defStyle.BackgroundBrush = PdfBrushes.White;
            defStyle.BorderPen = borderPen;

            PdfCellStyle headerStyle = new PdfCellStyle(font, PdfBrushes.White, PdfPens.DarkBlue);
            brush = new PdfSolidBrush(System.Drawing.Color.FromArgb(33, 67, 126));
            headerStyle.BackgroundBrush = brush;

            // Create DataTable for source
            PdfPage page = doc.Pages.Add();

            // Adding Header
            AddHeader(doc, "Customers");

            // Use DataTable as source
            PdfSimpleTable table = new PdfSimpleTable();

            // Create dataset with the "Customers" table from Norwind database
            DataSet dataSet = GetNorthwindDataSet();

            // Create datatable
            DataTable dataTable = dataSet.Tables[0];

            // Set Data source
            table.DataSource = dataTable;

            // Set table alternate row style
            table.Style.AlternateStyle = altStyle;

            // Set default style
            table.Style.DefaultStyle = defStyle;

            //Set header row style         
            table.Style.HeaderStyle = headerStyle;

            // Show the header row
            table.Style.ShowHeader = true;

            // Repeate header in all the pages
            table.Style.RepeatHeader = true;

            // Set header data from column caption
            table.Style.HeaderSource = PdfHeaderSource.ColumnCaptions;

            table.Style.BorderPen = borderPen;
            table.Style.CellPadding = 2;
            table.Columns[0].Width = 50;
            table.Columns[5].Width = 50;
            table.Columns[5].StringFormat = new PdfStringFormat(PdfTextAlignment.Right);

            table.Style.HeaderStyle.StringFormat = new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
            table.Style.DefaultStyle.StringFormat = new PdfStringFormat(PdfTextAlignment.Left, PdfVerticalAlignment.Middle);

            // Set layout properties
            PdfLayoutSettings format = new PdfLayoutSettings();
            format.Break = PdfLayoutBreakType.FitElement;
            format.Layout = PdfLayoutType.Paginate;

            // Draw table
            table.Draw(page, new PointF(0, 10), format);


            // Save and close the document.
            string outputPath = Path.Combine(OutputDir, this.GetType().Name + "_" + Guid.NewGuid().ToString() + ".pdf");
            doc.Save(outputPath);
            doc.Close(true);

            return outputPath;
        }

        Random random = new Random(Environment.TickCount);
        
        private string GetRandomAddress()
        {
            string[] list = new string[]
                                 {
                                     "Avda. de la Constitucion 2222",
                                     "Mataderos 2312",
                                     "120 Hanover Sq.",
                                     "Berguvsvagen 8",
                                     "Forsterstr. 57",
                                     "24, place Klober",
                                     "C/ Araquil, 67",
                                     "12, rue des Bouchers",
                                     "23 Tsawassen Blvd.",
                                     "Fauntleroy Circus",
                                 };

            return list[random.Next(list.Length)];
        }

        private string GetRandomName()
        {
            string[] list = new string[]
                                 {
                                     "Maria Anders",
                                     "Ana Trujillo",
                                     "Antonio Moreno",
                                     "Thomas Hardy",
                                     "Christina Berglund",
                                     "Hanna Moos",
                                     "Frederique Citeaux",
                                     "Martin Sommer",
                                     "Laurence Lebihan",
                                     "Elizabeth Lincoln",
                                     "Victoria Ashworth",
                                     "Francisco Chang",
                                     "Yang Wang",
                                 };

            return list[random.Next(list.Length)];
        }

        private string GetRandomCompanyName()
        {
            string[] list = new string[]
                                 {
                                     "Alfreds Futterkiste",
                                     "Around the Horn",
                                     "Berglunds snabbkop",
                                     "Blauer See Delikatessen",
                                     "Bon app'",
                                     "Bottom-Dollar Markets",
                                     "B's Beverages",
                                     "Consolidated Holdings",
                                     "Ernst Handel",
                                     "Folies gourmandes",
                                     "Galeria del gastronomo",
                                     "Hanari Carnes",
                                     "Island Trading",
                                 };

            return list[random.Next(list.Length)];
        }

        private string GetRandomCity()
        {
            string[] list = new string[]
                                 {
                                     "Cork",
                                     "Cowes",
                                     "Versailles",
                                     "Vancouver",
                                     "Walla Walla",
                                     "Frankfurt a.M.",
                                     "San Francisco",
                                     "Barquisimeto",
                                     "Portland",
                                     "Bergamo",
                                     "Bruxelles",
                                     "Montreal",
                                     "Leipzig",
                                     "London",
                                 };

            return list[random.Next(list.Length)];
        }

        private string GetRandomCountry()
        {
            string[] list = new string[]
                                 {
                                     "Germany",
                                     "Venezuela",
                                     "USA",
                                     "Canada",
                                     "Belgium",
                                     "Italy",
                                     "Argentina",
                                     "UK",
                                     "France",
                                     "Mexico",
                                     "Austria",
                                     "Portugal",
                                     "Brazil",
                                     "Switzerland",
                                 };

            return list[random.Next(list.Length)];
        }

        private string GetRandomPostalCode()
        {
            string[] list = new string[]
                                 {
                                     "PO31 7PJ",
                                     "14776",
                                     "78000",
                                     "V3F 2K1",
                                     "99362",
                                     "94117",
                                     "3508",
                                     "24100",
                                     "B-1180",
                                     "SW7 1RZ",
                                     "99508",
                                     "50739",
                                     "05033",
                                     "02389-673",
                                 };

            return list[random.Next(list.Length)];
        }

        /// 
        /// Get fictitious customer list.
        /// 
        /// 
        private DataSet GetNorthwindDataSet()
        {
            DataSet dataSet = new DataSet();

            DataTable dt = new DataTable();
            dt.Columns.Add("CustomerID", typeof(int));
            dt.Columns.Add("CompanyName", typeof(string));
            dt.Columns.Add("ContactName", typeof(string));
            dt.Columns.Add("Address", typeof(string));
            dt.Columns.Add("City", typeof(string));
            dt.Columns.Add("PostalCode", typeof(string));
            dt.Columns.Add("Country", typeof(string));

            for (int i = 0; i < 50; i++)
            {
                dt.Rows.Add(1000 + i, GetRandomCompanyName(), GetRandomName(), GetRandomAddress(), GetRandomCity(), GetRandomPostalCode(), GetRandomCountry());
            }

            dataSet.Tables.Add(dt);

            return dataSet;
        }

        private static void AddHeader(PdfDocument doc, string title)
        {
            RectangleF rect = new RectangleF(0, 0, doc.Pages[0].GetClientSize().Width, 54);
            PdfPageTemplateElement header = new PdfPageTemplateElement(rect);
            PdfSolidBrush brush = new PdfSolidBrush(Color.DarkBlue);

            PdfFont font = new PdfStandardFont(PdfFontFamily.Helvetica, 16, PdfFontStyle.Bold);
            PdfStringFormat format = new PdfStringFormat();
            format.Alignment = PdfTextAlignment.Center;
            format.LineAlignment = PdfVerticalAlignment.Middle;
            header.Graphics.DrawString(title, font, brush, new RectangleF(0, 0, header.Width, header.Height), format);

            doc.Template.Top = header;
        }
    }
}
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Imports OfficeComponent.Pdf
Imports OfficeComponent.Pdf.Graphics
Imports OfficeComponent.Pdf.Tables

Namespace OfficeComponent.Samples
	Friend Class NorthwindReportExample
		Inherits ExampleBase
		Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String)
			MyBase.New(commonDataPath, outputDir)
		End Sub

		Public Sub New(ByVal commonDataPath As String, ByVal outputDir As String, ByVal xmlFile As String)
			MyBase.New(commonDataPath, outputDir, xmlFile)
		End Sub

		Public Overrides Function Execute() As String
			' Create a new instance of PdfDocument class.
			Dim doc As New PdfDocument()
			Dim font As PdfFont = New PdfStandardFont(PdfFontFamily.Helvetica, 8)

			' Create Pdf ben for drawing broder
			Dim borderPen As New PdfPen(PdfBrushes.DarkBlue)
			borderPen.Width = 0

			' Create brush
			Dim color As New PdfColor(250, 250, 250)
			Dim brush As New PdfSolidBrush(color)

			' Create cell styles
			Dim altStyle As New PdfCellStyle()
			altStyle.Font = font
			altStyle.BackgroundBrush = brush
			altStyle.BorderPen = borderPen

			Dim defStyle As New PdfCellStyle()
			defStyle.Font = font
			defStyle.BackgroundBrush = PdfBrushes.White
			defStyle.BorderPen = borderPen

			Dim headerStyle As New PdfCellStyle(font, PdfBrushes.White, PdfPens.DarkBlue)
			brush = New PdfSolidBrush(System.Drawing.Color.FromArgb(33, 67, 126))
			headerStyle.BackgroundBrush = brush

			' Create DataTable for source
			Dim page As PdfPage = doc.Pages.Add()

			' Adding Header
			AddHeader(doc, "Customers")

			' Use DataTable as source
			Dim table As New PdfSimpleTable()

			' Create dataset with the "Customers" table from Norwind database
			Dim dataSet As DataSet = GetNorthwindDataSet()

			' Create datatable
			Dim dataTable As DataTable = dataSet.Tables(0)

			' Set Data source
			table.DataSource = dataTable

			' Set table alternate row style
			table.Style.AlternateStyle = altStyle

			' Set default style
			table.Style.DefaultStyle = defStyle

			'Set header row style         
			table.Style.HeaderStyle = headerStyle

			' Show the header row
			table.Style.ShowHeader = True

			' Repeate header in all the pages
			table.Style.RepeatHeader = True

			' Set header data from column caption
			table.Style.HeaderSource = PdfHeaderSource.ColumnCaptions

			table.Style.BorderPen = borderPen
			table.Style.CellPadding = 2
			table.Columns(0).Width = 50
			table.Columns(5).Width = 50
			table.Columns(5).StringFormat = New PdfStringFormat(PdfTextAlignment.Right)

			table.Style.HeaderStyle.StringFormat = New PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle)
			table.Style.DefaultStyle.StringFormat = New PdfStringFormat(PdfTextAlignment.Left, PdfVerticalAlignment.Middle)

			' Set layout properties
			Dim format As New PdfLayoutSettings()
			format.Break = PdfLayoutBreakType.FitElement
			format.Layout = PdfLayoutType.Paginate

			' Draw table
			table.Draw(page, New PointF(0, 10), format)


			' Save and close the document.
			Dim outputPath As String = Path.Combine(OutputDir, Me.GetType().Name & "_" & Guid.NewGuid().ToString() & ".pdf")
			doc.Save(outputPath)
			doc.Close(True)

			Return outputPath
		End Function

		Private random As New Random(Environment.TickCount)

		Private Function GetRandomAddress() As String
			Dim list() As String = { "Avda. de la Constitucion 2222", "Mataderos 2312", "120 Hanover Sq.", "Berguvsvagen 8", "Forsterstr. 57", "24, place Klober", "C/ Araquil, 67", "12, rue des Bouchers", "23 Tsawassen Blvd.", "Fauntleroy Circus" }

			Return list(random.Next(list.Length))
		End Function

		Private Function GetRandomName() As String
			Dim list() As String = { "Maria Anders", "Ana Trujillo", "Antonio Moreno", "Thomas Hardy", "Christina Berglund", "Hanna Moos", "Frederique Citeaux", "Martin Sommer", "Laurence Lebihan", "Elizabeth Lincoln", "Victoria Ashworth", "Francisco Chang", "Yang Wang" }

			Return list(random.Next(list.Length))
		End Function

		Private Function GetRandomCompanyName() As String
			Dim list() As String = { "Alfreds Futterkiste", "Around the Horn", "Berglunds snabbkop", "Blauer See Delikatessen", "Bon app'", "Bottom-Dollar Markets", "B's Beverages", "Consolidated Holdings", "Ernst Handel", "Folies gourmandes", "Galeria del gastronomo", "Hanari Carnes", "Island Trading" }

			Return list(random.Next(list.Length))
		End Function

		Private Function GetRandomCity() As String
			Dim list() As String = { "Cork", "Cowes", "Versailles", "Vancouver", "Walla Walla", "Frankfurt a.M.", "San Francisco", "Barquisimeto", "Portland", "Bergamo", "Bruxelles", "Montreal", "Leipzig", "London" }

			Return list(random.Next(list.Length))
		End Function

		Private Function GetRandomCountry() As String
			Dim list() As String = { "Germany", "Venezuela", "USA", "Canada", "Belgium", "Italy", "Argentina", "UK", "France", "Mexico", "Austria", "Portugal", "Brazil", "Switzerland" }

			Return list(random.Next(list.Length))
		End Function

		Private Function GetRandomPostalCode() As String
			Dim list() As String = { "PO31 7PJ", "14776", "78000", "V3F 2K1", "99362", "94117", "3508", "24100", "B-1180", "SW7 1RZ", "99508", "50739", "05033", "02389-673" }

			Return list(random.Next(list.Length))
		End Function

		''' 
		''' Get fictitious customer list.
		''' 
		''' 
		Private Function GetNorthwindDataSet() As DataSet
			Dim dataSet As New DataSet()

			Dim dt As New DataTable()
			dt.Columns.Add("CustomerID", GetType(Integer))
			dt.Columns.Add("CompanyName", GetType(String))
			dt.Columns.Add("ContactName", GetType(String))
			dt.Columns.Add("Address", GetType(String))
			dt.Columns.Add("City", GetType(String))
			dt.Columns.Add("PostalCode", GetType(String))
			dt.Columns.Add("Country", GetType(String))

			For i As Integer = 0 To 49
				dt.Rows.Add(1000 + i, GetRandomCompanyName(), GetRandomName(), GetRandomAddress(), GetRandomCity(), GetRandomPostalCode(), GetRandomCountry())
			Next i

			dataSet.Tables.Add(dt)

			Return dataSet
		End Function

		Private Shared Sub AddHeader(ByVal doc As PdfDocument, ByVal title As String)
			Dim rect As New RectangleF(0, 0, doc.Pages(0).GetClientSize().Width, 54)
			Dim header As New PdfPageTemplateElement(rect)
			Dim brush As New PdfSolidBrush(Color.DarkBlue)

			Dim font As PdfFont = New PdfStandardFont(PdfFontFamily.Helvetica, 16, PdfFontStyle.Bold)
			Dim format As New PdfStringFormat()
			format.Alignment = PdfTextAlignment.Center
			format.LineAlignment = PdfVerticalAlignment.Middle
			header.Graphics.DrawString(title, font, brush, New RectangleF(0, 0, header.Width, header.Height), format)

			doc.Template.Top = header
		End Sub
	End Class
End Namespace