Invoice

This example demonstrates how to create an invoice and save to a Word document

Invoice is most often needed when there is a need of commercial document to be issued by a seller to a buyer, relating to a sale transaction and indicating the products, quantities, and agreed prices for products or services the seller had provided the buyer.

We have given user a choice to choose any order Id from given list

After clicking generate button will launch a word file with above choosen version, this file contains a standard invoice with data from the dummy database.

#define USECODE

using System;
using System.Collections.Generic;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using System.Text;

using OfficeComponent.Word;
using System.Data;

namespace OfficeComponent.Samples
{
    class InvoiceExample : WordExampleBase
    {
        public int OrderId;


        public InvoiceExample(string commonDataPath, string outputDir)
            : base(commonDataPath, outputDir)
        {

        }

        public InvoiceExample(string commonDataPath, string outputDir, string xmlFile)
            : base(commonDataPath, outputDir, xmlFile)
        {

        }

        public override string Execute()
        {
#if WEB
            OrderId = 1010;
#endif

            // Create a new instance of PdfDocument class.
            WordDocument document = new WordDocument(CommonDataPath + (SaveAsFormat == WordDocumentFormat.Doc ? "\\InvoiceTemplate.doc" : "\\InvoiceTemplate.docx"));

            // Execute Mail Merge with groups.
            document.MailMerge.ExecuteGroup(GetDataTableOrder(OrderId));
            document.MailMerge.ExecuteGroup(GetDataTableOrderTotals(OrderId));

            // Using Merge events to do conditional formatting during runtime.
            document.MailMerge.MergeField += MailMerge_MergeField;

            DataView orderDetails = new DataView(GetDataTableOrderDetails(OrderId));
            orderDetails.Sort = "ExtendedPrice DESC";
            document.MailMerge.ExecuteGroup(orderDetails);
            document.LastSection.AddParagraph().AppendText("\t\t\t\t\t\tTHANK YOU FOR YOUR BUSINESS!").CharacterFormat.Bold = true;

            string fileName = Path.Combine(OutputDir, this.GetType().Name + "_" + Guid.NewGuid().ToString() + GetExtension(SaveAsFormat));
            // Save the document.
            document.Save(fileName, SaveAsFormat);

            // Close the document.
            document.Close();

            return fileName;
        }
        private static void MailMerge_MergeField(object sender, MergeFieldEventArgs args)
        {
            // Conditionally format data during Merge.
            if (args.RowIndex % 2 == 0)
            {
                args.CharacterFormat.TextColor = Color.DarkBlue;
            }

        }

#if USECODE
        /// 
        /// Gets fictitious order by id.
        /// 
        private DataTable GetDataTableOrder(int orderId)
        {
            Order order = GetOrder(orderId);

            DataTable dt = new DataTable("Orders");
            dt.Columns.Add("OrderID", typeof(int));
            dt.Columns.Add("CustomerID", typeof(int));
            dt.Columns.Add("OrderDate", typeof(DateTime));
            dt.Columns.Add("RequiredDate", typeof(DateTime));
            dt.Columns.Add("ShippedDate", typeof(DateTime));

            dt.Columns.Add("ShipName", typeof(string));
            dt.Columns.Add("ShipAddress", typeof(string));
            dt.Columns.Add("Freight", typeof(float));
            dt.Columns.Add("ShipCity", typeof(string));
            dt.Columns.Add("ShipCountry", typeof(string));

            dt.Rows.Add(order.OrderID, 10, DateTime.Now.AddYears(-1), DateTime.Now.AddMonths(-11), DateTime.Now.AddYears(-1),
                    order.ShipName, order.ShipAddress, order.Freight, order.ShipCity, order.ShipCountry
                );

            return dt;
        }

        //Sub table
        private DataTable GetDataTableOrderDetails(int orderId)
        {
            OrderDetails[] details = GetOrderDetails(orderId);

            DataTable dt = new DataTable("Order");
            dt.Columns.Add("ProductID", typeof(string));
            dt.Columns.Add("Quantity", typeof(string));
            dt.Columns.Add("UnitPrice", typeof(float));
            dt.Columns.Add("Discount", typeof(float));
            dt.Columns.Add("Price", typeof(string));
            dt.Columns.Add("ExtendedPrice", typeof(float));
            

            foreach (OrderDetails d in details)
                dt.Rows.Add(d.ProductID.ToString(), d.Quantity, d.UnitPrice, d.Discount, "$" + (d.Quantity * d.UnitPrice).ToString(), 0);

            return dt;
        }

        private DataTable GetDataTableOrderTotals(int orderId)
        {
            Order order = GetOrder(orderId);

            DataTable dt = new DataTable("OrderTotals");
            dt.Columns.Add("OrderID", typeof(int));
            dt.Columns.Add("Subtotal", typeof(float));
            dt.Columns.Add("Freight", typeof(float));
            dt.Columns.Add("Total", typeof(float));

            dt.Rows.Add(order.OrderID, order.Total, order.Freight, order.Total + order.Freight);

            return dt;
        }

        class OrderDetails
        {
            int _productID;
            public int ProductID
            {
                get { return _productID; }
                set { _productID = value; }
            }

            int _quantity;
            public int Quantity
            {
                get { return _quantity; }
                set { _quantity = value; }
            }

            float _unitPrice;
            public float UnitPrice
            {
                get { return _unitPrice; }
                set { _unitPrice = value; }
            }

            float _discount;
            public float Discount
            {
                get { return _discount; }
                set { _discount = value; }
            }
        }

        class Order
        {
            int _orderID;
            public int OrderID
            {
                get { return _orderID; }
                set { _orderID = value; }
            }

            string _shipName;
            public string ShipName
            {
                get { return _shipName; }
                set { _shipName = value; }
            }

            string _shipAddress;
            public string ShipAddress
            {
                get { return _shipAddress; }
                set { _shipAddress = value; }
            }

            float _freight;
            public float Freight
            {
                get { return _freight; }
                set { _freight = value; }
            }

            string _shipCity;
            public string ShipCity
            {
                get { return _shipCity; }
                set { _shipCity = value; }
            }

            string _shipCountry;
            public string ShipCountry
            {
                get { return _shipCountry; }
                set { _shipCountry = value; }
            }

            DateTime _shippedDate;
            public DateTime ShippedDate
            {
                get { return _shippedDate; }
                set { _shippedDate = value; }
            }

            OrderDetails[] _details;
            public OrderDetails[] Details
            {
                get { return _details; }
                set { _details = value; }
            }

            public float Total
            {
                get
                {
                    float t = 0.0f;
                    foreach (OrderDetails d in Details)
                    {
                        t = t + (d.UnitPrice * d.Quantity - d.Discount);
                    }

                    return t;
                }
            }
        }

        Random random = new Random();

        Order[] _orders;
        Order[] EnsureOrders()
        {
            if (_orders == null)
            {
                const int max = 50;
                _orders = new Order[max];

                // Create fake orders.
                for (int i = 0; i < max; i++)
                {
                    Order order = new Order();

                    order.OrderID = 1000 + i;
                    order.ShipAddress = "Luisenstr. 48" + i;
                    order.ShipCity = "Manster";
                    order.ShipCountry = "Germany";
                    order.ShipName = "Toms Spezialitaten";
                    order.ShippedDate = DateTime.Now.AddYears(-1);
                    order.Freight = 11.6f;

                    order.Details = new OrderDetails[2];

                    OrderDetails details = new OrderDetails();
                    details.Discount = 0;
                    details.ProductID = 20 + i;
                    details.Quantity = random.Next(30) + 1;
                    details.UnitPrice = 5.6f * (i + 1);
                    order.Details[0] = details;

                    details = new OrderDetails();
                    details.Discount = 0;
                    details.ProductID = 20 + i + 1;
                    details.Quantity = random.Next(30) + 1;
                    details.UnitPrice = 7.6f * (i + 1);
                    order.Details[1] = details;

                    _orders[i] = order;
                }
            }

            return _orders;
        }

        OrderDetails[] GetOrderDetails(int orderId)
        {
            foreach (Order o in EnsureOrders())
            {
                if (o.OrderID == orderId)
                    return o.Details;
            }

            throw new Exception("Order not found");
        }

        Order GetOrder(int orderId)
        {
            foreach (Order o in EnsureOrders())
            {
                if (o.OrderID == orderId)
                    return o;
            }

            throw new Exception("Order not found");
        }

        internal int[] GetOrderIds()
        {
            Order[] orders = EnsureOrders();

            List list = new List();
            foreach (Order o in orders)
                list.Add(o.OrderID);

            return list.ToArray();
        }
#else
        private  DataTable GetDataTableOrder(int TestOrderId)
        {
            AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

            DataTable table = new DataTable();
            string connectionstring = "Data Source = " + CommonDataPath + "\\NorthwindIO.sdf";
            SqlCeConnection conn = new SqlCeConnection(connectionstring);
            conn.Open();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM MyOrders WHERE OrderId = " + TestOrderId, conn);
            adapter.Fill(table);
            adapter.Dispose();
            conn.Close();

            table.TableName = "Orders";
            return table;
        }

        private  DataTable GetDataTableOrderDetails(int TestOrderId)
        {
            AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

            DataTable table = new DataTable();
            string connectionstring = "Data Source = " + CommonDataPath + "\\NorthwindIO.sdf";
            SqlCeConnection conn = new SqlCeConnection(connectionstring);
            conn.Open();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM MyOrderDetails WHERE OrderId = " + TestOrderId + " ORDER BY ProductID", conn);
            adapter.Fill(table);
            adapter.Dispose();
            conn.Close();

            table.TableName = "Order";
            return table;
        }

        private  DataTable GetDataTableOrderTotals(int TestOrderId)
        {

            AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", true);

            DataTable table = new DataTable();
            string connectionstring = "Data Source = " + CommonDataPath + "\\NorthwindIO.sdf";
            SqlCeConnection conn = new SqlCeConnection(connectionstring);
            conn.Open();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM MyOrderTotals WHERE OrderId = " + TestOrderId, conn);
            adapter.Fill(table);
            adapter.Dispose();
            conn.Close();

            table.TableName = "OrderTotals";
            return table;
        }
#endif
    }

}
#Const USECODE = True

Imports System.Drawing.Imaging
Imports System.IO
Imports System.Text

Imports OfficeComponent.Word

Namespace OfficeComponent.Samples
	Friend Class InvoiceExample
		Inherits WordExampleBase
		Public OrderId As Integer


		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
#If WEB Then
			OrderId = 1010
#End If

			' Create a new instance of PdfDocument class.
			Dim document As WordDocument
			If SaveAsFormat = WordDocumentFormat.Doc Then
				document = New WordDocument(CommonDataPath & ("\InvoiceTemplate.doc"))
			Else
				document = New WordDocument(CommonDataPath & ("\InvoiceTemplate.docx"))
			End If

			' Execute Mail Merge with groups.
			document.MailMerge.ExecuteGroup(GetDataTableOrder(OrderId))
			document.MailMerge.ExecuteGroup(GetDataTableOrderTotals(OrderId))

			' Using Merge events to do conditional formatting during runtime.
			AddHandler document.MailMerge.MergeField, AddressOf MailMerge_MergeField

			Dim orderDetails As New DataView(GetDataTableOrderDetails(OrderId))
			orderDetails.Sort = "ExtendedPrice DESC"
			document.MailMerge.ExecuteGroup(orderDetails)
			document.LastSection.AddParagraph().AppendText(vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & "THANK YOU FOR YOUR BUSINESS!").CharacterFormat.Bold = True

			Dim fileName As String = Path.Combine(OutputDir, Me.GetType().Name & "_" & Guid.NewGuid().ToString() & GetExtension(SaveAsFormat))
			' Save the document.
			document.Save(fileName, SaveAsFormat)

			' Close the document.
			document.Close()

			Return fileName
		End Function
		Private Shared Sub MailMerge_MergeField(ByVal sender As Object, ByVal args As MergeFieldEventArgs)
			' Conditionally format data during Merge.
			If args.RowIndex Mod 2 = 0 Then
				args.CharacterFormat.TextColor = Color.DarkBlue
			End If

		End Sub

#If USECODE Then
		''' 
		''' Gets fictitious order by id.
		''' 
		Private Function GetDataTableOrder(ByVal orderId As Integer) As DataTable
			Dim order As Order = GetOrder(orderId)

			Dim dt As New DataTable("Orders")
			dt.Columns.Add("OrderID", GetType(Integer))
			dt.Columns.Add("CustomerID", GetType(Integer))
			dt.Columns.Add("OrderDate", GetType(Date))
			dt.Columns.Add("RequiredDate", GetType(Date))
			dt.Columns.Add("ShippedDate", GetType(Date))

			dt.Columns.Add("ShipName", GetType(String))
			dt.Columns.Add("ShipAddress", GetType(String))
			dt.Columns.Add("Freight", GetType(Single))
			dt.Columns.Add("ShipCity", GetType(String))
			dt.Columns.Add("ShipCountry", GetType(String))

			dt.Rows.Add(order.OrderID, 10, Date.Now.AddYears(-1), Date.Now.AddMonths(-11), Date.Now.AddYears(-1), order.ShipName, order.ShipAddress, order.Freight, order.ShipCity, order.ShipCountry)

			Return dt
		End Function

		'Sub table
		Private Function GetDataTableOrderDetails(ByVal orderId As Integer) As DataTable
			Dim details() As OrderDetails = GetOrderDetails(orderId)

			Dim dt As New DataTable("Order")
			dt.Columns.Add("ProductID", GetType(String))
			dt.Columns.Add("Quantity", GetType(String))
			dt.Columns.Add("UnitPrice", GetType(Single))
			dt.Columns.Add("Discount", GetType(Single))
			dt.Columns.Add("Price", GetType(String))
			dt.Columns.Add("ExtendedPrice", GetType(Single))


			For Each d As OrderDetails In details
				dt.Rows.Add(d.ProductID.ToString(), d.Quantity, d.UnitPrice, d.Discount, "$" & (d.Quantity * d.UnitPrice).ToString(), 0)
			Next d

			Return dt
		End Function

		Private Function GetDataTableOrderTotals(ByVal orderId As Integer) As DataTable
			Dim order As Order = GetOrder(orderId)

			Dim dt As New DataTable("OrderTotals")
			dt.Columns.Add("OrderID", GetType(Integer))
			dt.Columns.Add("Subtotal", GetType(Single))
			dt.Columns.Add("Freight", GetType(Single))
			dt.Columns.Add("Total", GetType(Single))

			dt.Rows.Add(order.OrderID, order.Total, order.Freight, order.Total + order.Freight)

			Return dt
		End Function

		Private Class OrderDetails
			Private _productID As Integer
			Public Property ProductID() As Integer
				Get
					Return _productID
				End Get
				Set(ByVal value As Integer)
					_productID = value
				End Set
			End Property

			Private _quantity As Integer
			Public Property Quantity() As Integer
				Get
					Return _quantity
				End Get
				Set(ByVal value As Integer)
					_quantity = value
				End Set
			End Property

			Private _unitPrice As Single
			Public Property UnitPrice() As Single
				Get
					Return _unitPrice
				End Get
				Set(ByVal value As Single)
					_unitPrice = value
				End Set
			End Property

			Private _discount As Single
			Public Property Discount() As Single
				Get
					Return _discount
				End Get
				Set(ByVal value As Single)
					_discount = value
				End Set
			End Property
		End Class

		Private Class Order
			Private _orderID As Integer
			Public Property OrderID() As Integer
				Get
					Return _orderID
				End Get
				Set(ByVal value As Integer)
					_orderID = value
				End Set
			End Property

			Private _shipName As String
			Public Property ShipName() As String
				Get
					Return _shipName
				End Get
				Set(ByVal value As String)
					_shipName = value
				End Set
			End Property

			Private _shipAddress As String
			Public Property ShipAddress() As String
				Get
					Return _shipAddress
				End Get
				Set(ByVal value As String)
					_shipAddress = value
				End Set
			End Property

			Private _freight As Single
			Public Property Freight() As Single
				Get
					Return _freight
				End Get
				Set(ByVal value As Single)
					_freight = value
				End Set
			End Property

			Private _shipCity As String
			Public Property ShipCity() As String
				Get
					Return _shipCity
				End Get
				Set(ByVal value As String)
					_shipCity = value
				End Set
			End Property

			Private _shipCountry As String
			Public Property ShipCountry() As String
				Get
					Return _shipCountry
				End Get
				Set(ByVal value As String)
					_shipCountry = value
				End Set
			End Property

			Private _shippedDate As Date
			Public Property ShippedDate() As Date
				Get
					Return _shippedDate
				End Get
				Set(ByVal value As Date)
					_shippedDate = value
				End Set
			End Property

			Private _details() As OrderDetails
			Public Property Details() As OrderDetails()
				Get
					Return _details
				End Get
				Set(ByVal value As OrderDetails())
					_details = value
				End Set
			End Property

			Public ReadOnly Property Total() As Single
				Get
					Dim t As Single = 0.0F
					For Each d As OrderDetails In Details
						t = t + (d.UnitPrice * d.Quantity - d.Discount)
					Next d

					Return t
				End Get
			End Property
		End Class

		Private random As New Random()

		Private _orders() As Order
		Private Function EnsureOrders() As Order()
			If _orders Is Nothing Then
				Const max As Integer = 50
				_orders = New Order(max - 1){}

				' Create fake orders.
				For i As Integer = 0 To max - 1
					Dim order As New Order()

					order.OrderID = 1000 + i
					order.ShipAddress = "Luisenstr. 48" & i
					order.ShipCity = "Manster"
					order.ShipCountry = "Germany"
					order.ShipName = "Toms Spezialitaten"
					order.ShippedDate = Date.Now.AddYears(-1)
					order.Freight = 11.6F

					order.Details = New OrderDetails(1){}

					Dim details As New OrderDetails()
					details.Discount = 0
					details.ProductID = 20 + i
					details.Quantity = random.Next(30) + 1
					details.UnitPrice = 5.6F * (i + 1)
					order.Details(0) = details

					details = New OrderDetails()
					details.Discount = 0
					details.ProductID = 20 + i + 1
					details.Quantity = random.Next(30) + 1
					details.UnitPrice = 7.6F * (i + 1)
					order.Details(1) = details

					_orders(i) = order
				Next i
			End If

			Return _orders
		End Function

		Private Function GetOrderDetails(ByVal orderId As Integer) As OrderDetails()
			For Each o As Order In EnsureOrders()
				If o.OrderID = orderId Then
					Return o.Details
				End If
			Next o

			Throw New Exception("Order not found")
		End Function

		Private Function GetOrder(ByVal orderId As Integer) As Order
			For Each o As Order In EnsureOrders()
				If o.OrderID = orderId Then
					Return o
				End If
			Next o

			Throw New Exception("Order not found")
		End Function

		Friend Function GetOrderIds() As Integer()
			Dim orders() As Order = EnsureOrders()

			Dim list As New List(Of Integer)()
			For Each o As Order In orders
				list.Add(o.OrderID)
			Next o

			Return list.ToArray()
		End Function
#Else
		Private Function GetDataTableOrder(ByVal TestOrderId As Integer) As DataTable
			AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)

			Dim table As New DataTable()
			Dim connectionstring As String = "Data Source = " & CommonDataPath & "\NorthwindIO.sdf"
			Dim conn As New SqlCeConnection(connectionstring)
			conn.Open()
			Dim adapter As New SqlCeDataAdapter("SELECT * FROM MyOrders WHERE OrderId = " & TestOrderId, conn)
			adapter.Fill(table)
			adapter.Dispose()
			conn.Close()

			table.TableName = "Orders"
			Return table
		End Function

		Private Function GetDataTableOrderDetails(ByVal TestOrderId As Integer) As DataTable
			AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)

			Dim table As New DataTable()
			Dim connectionstring As String = "Data Source = " & CommonDataPath & "\NorthwindIO.sdf"
			Dim conn As New SqlCeConnection(connectionstring)
			conn.Open()
			Dim adapter As New SqlCeDataAdapter("SELECT * FROM MyOrderDetails WHERE OrderId = " & TestOrderId & " ORDER BY ProductID", conn)
			adapter.Fill(table)
			adapter.Dispose()
			conn.Close()

			table.TableName = "Order"
			Return table
		End Function

		Private Function GetDataTableOrderTotals(ByVal TestOrderId As Integer) As DataTable

			AppDomain.CurrentDomain.SetData("SQLServerCompactEditionUnderWebHosting", True)

			Dim table As New DataTable()
			Dim connectionstring As String = "Data Source = " & CommonDataPath & "\NorthwindIO.sdf"
			Dim conn As New SqlCeConnection(connectionstring)
			conn.Open()
			Dim adapter As New SqlCeDataAdapter("SELECT * FROM MyOrderTotals WHERE OrderId = " & TestOrderId, conn)
			adapter.Fill(table)
			adapter.Dispose()
			conn.Close()

			table.TableName = "OrderTotals"
			Return table
		End Function
#End If
	End Class

End Namespace