1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

<%@ Page Language="VB" AutoEventWireup="true" Codebehind="invoice-form.aspx.vb" Inherits="Aspose.Cells.Demos.VisualBasic.InvoiceForm"
    MasterPageFile="~/tpl/Demo.Master" Title="Invoice - Apose.Cells Demos" %>

<asp:Content ID="Content" ContentPlaceHolderID="MainContent" runat="server">
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>&nbsp;</p>
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>
            <table align="center" border="0" cellpadding="0" cellspacing="0" width="90%">
                <tr>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_lft.jpg" width="19" /></td>
                    <td class="demos-heading-bg" width="100%">
                        <h2 class="demos-heading-bg">
                            &nbsp;<font face="Arial" size="4">Invoice - Aspose.Cells</font></h2>
                    </td>
                    <td valign="top" width="19">
                        <img alt="" height="41" src="/Common/images/heading_rt.jpg" width="19" /></td>
                </tr>
            </table>
    </p>
    <p class="MsoNormal">
        <font face="Arial"><span style="color: black"><font size="2">This online demo illustrates
            how to create a well formatted Customer Invoice report </font></span></font>
        <font size="2"><span style="font-family: Arial">using </span><font face="Arial"><a
            href="http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx">
            Aspose.Cells</a> for .NET</font><span style="font-family: Arial">.</span></font><font
                face="Arial" size="2"> </font>
    </p>
    <p class="MsoNormal">
        <font face="Arial" size="2"><span style="color: black">Aspose.Cells component gives
            you the agility to report your data in a variety of ways. Aspose.Cells component
            is fully functional for creating all types of reports. You may customize the size
            and appearance of everything on a report. You can display the information the way
            you want to see it. </span></font>
    </p>
    <p class="MsoNormal">
        <font face="Arial" size="2"><span style="color: black">The demo generates a printed
            report displaying the customer invoice against the order</span>. A separate sheet
            is generated for each customer Invoice.<span style="color: black"> ADO.NET is used to
                retrieve the data from the Invoices and Orders tables of Northwind database, to
                generate the report. </span>You can either open the resultant excel file into MS Excel
        or save directly to your disk to check the results.</font></p>
    <p class="componentDescriptionTxt">
    	Click <b>Process </b> to see how example 
        Prints each invoice on a separate page creating the Excel
        spreadsheet completely via API. </SPAN></p>
    <p class="componentDescriptionTxt">
        <span style="font-size: 10pt; font-family: Arial"></span>
    <asp:Button ID="btnExecute" runat="server" Text="Process" OnClick="btnExecute_Click" />&nbsp;</p>
</asp:Content>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Partial Class InvoiceForm
    Inherits System.Web.UI.Page
    Protected Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim workbook As Workbook
        Dim path As String = MapPath(".")
        path = path.Substring(0, path.LastIndexOf("\"))
        Dim param As String = "Invoice"
        'Create Invoice
        Dim Invoice As Invoice = New Invoice(path)
        workbook = Invoice.CreateInvoice()
        workbook.Save(param + ".xls", SaveType.OpenInExcel, FileFormatType.Excel97To2003, Me.Response)

        ' End response to avoid unneeded HTML after XLS
        Response.End()
    End Sub

End Class

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308

Imports Aspose.Cells.Drawing

Public Class Invoice
    Inherits DbBase
    Public Sub New(ByVal path As String)
        MyBase.New(path)
    End Sub

    Public Function CreateInvoice() As Workbook

        DBInit()

        Dim workbook As Workbook = New Workbook()

        Me.oleDbDataAdapter1.SelectCommand.CommandText = "SELECT  DISTINCTROW OrderID FROM Orders ORDER BY OrderID DESC"
        Me.oleDbDataAdapter1.Fill(Me.dataTable1)

        Dim dtInvoice() As DataTable = New DataTable(Me.dataTable1.Rows.Count - 1) {}

        Dim i As Integer
        'For i = 0 To dtInvoice.Length - 1
        'We generate invoices for the first 100 orders for demo only. If you want to
        'generate all invoices,uncomment the line above and comment the line below.
        For i = 0 To 99
            dtInvoice(i) = Me.ReadInvoice(Me.dataTable1.Rows(i)(0).ToString())
        Next

        Dim sheets As WorksheetCollection = workbook.Worksheets
        Dim sheet As Worksheet = sheets(0)
        sheet.Name = "Invoice"

        Dim cells As Cells = sheet.Cells
        Dim startRow As Integer = 0

        SetInvoiceStyles(workbook)



        'For i = 0 To dtInvoice.Length - 1
        'We generate invoices for the first 100 orders for demo only. If you want to
        'generate all invoices,uncomment the line above and comment the line below.
        For i = 0 To 99
            Dim imagePath As String = path + "\Image"
            sheet.Pictures.Add(startRow, 0, startRow + 2, 1, imagePath + "\logo.jpg")
            Dim picIndex As Integer = sheet.Pictures.Add(startRow, 1, startRow + 2, 2, imagePath + "\namelogo.jpg")
            Dim pic As Picture = sheet.Pictures(picIndex)
            pic.UpperDeltaY = 100

            CreateInvoiceHeader(cells, workbook, dtInvoice(i), startRow)
            startRow += 11
            CreateOrder(cells, workbook, dtInvoice(i), startRow, Me.dataTable1.Rows(i)(0).ToString())
            startRow += 4
            CreateOrderDetail(cells, workbook, dtInvoice(i), startRow)

            startRow += dtInvoice(i).Rows.Count + 1
            sheet.HPageBreaks.Add(startRow - 1, 0)
        Next
        Return workbook
    End Function

    Private Function ReadInvoice(ByVal orderID As String) As DataTable

        Dim invoiceQuery As String = "SELECT DISTINCTROW Invoices.* FROM Invoices WHERE Invoices.OrderID=" + orderID
        Me.oleDbDataAdapter2.SelectCommand.CommandText = invoiceQuery
        Dim dtInvoice As DataTable = New DataTable()
        Me.oleDbDataAdapter2.Fill(dtInvoice)
        Return dtInvoice

    End Function

    Private Sub SetInvoiceStyles(ByVal workbook As Workbook)

        'Add LightBlue and DarkBlue colors to color palette
        workbook.ChangePalette(Color.LightBlue, 54)
        workbook.ChangePalette(Color.DarkBlue, 55)

        Dim style As Style
        Dim styleIndex As Integer = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 12
        style.Font.IsBold = True
        style.Font.Color = Color.White
        style.ForegroundColor = Color.LightBlue
        style.Pattern = BackgroundType.Solid
        style.HorizontalAlignment = TextAlignmentType.Center
        style.Name = "Font12Center"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 12
        style.Font.IsBold = True
        style.Font.Color = Color.White
        style.ForegroundColor = Color.LightBlue
        style.Pattern = BackgroundType.Solid
        style.HorizontalAlignment = TextAlignmentType.Left
        style.Name = "Font12Left"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 12
        style.Font.IsBold = True
        style.Font.Color = Color.White
        style.ForegroundColor = Color.LightBlue
        style.Pattern = BackgroundType.Solid
        style.HorizontalAlignment = TextAlignmentType.Right
        style.Name = "Font12Right"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Number = 7
        style.Name = "Number7"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Number = 9
        style.Name = "Number9"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.HorizontalAlignment = TextAlignmentType.Center
        style.Name = "Center"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 16
        style.Font.IsBold = True
        style.Font.Color = Color.DarkBlue
        style.Pattern = BackgroundType.Solid
        style.Name = "Darkblue"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.Size = 12
        style.Font.IsBold = True
        style.Font.Color = Color.DarkBlue
        style.Name = "Darkblue12"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Font.IsItalic = True
        style.Font.Color = Color.DarkBlue
        style.Name = "DarkblueItalic"

        styleIndex = workbook.Styles.Add()
        style = workbook.Styles(styleIndex)
        style.Borders(BorderType.BottomBorder).Color = Color.Black
        style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium
        style.Name = "BlackMedium"

    End Sub

    Private Sub CreateOrderDetail(ByVal Cells As Cells, ByVal workbook As Workbook, ByVal dtInvoice As DataTable, ByVal startRow As Integer)

        Dim style1 As Style, style2 As Style, style3 As Style
        style1 = workbook.Styles("Number7")
        style2 = workbook.Styles("Number9")
        style3 = workbook.Styles("Center")

        Dim i As Integer
        For i = 0 To dtInvoice.Rows.Count - 1
            Cells(startRow + i, 0).PutValue(CType(dtInvoice.Rows(i)("ProductID"), Integer))
            Cells(startRow + i, 0).Style = style3
            Cells(startRow + i, 1).PutValue(CType(dtInvoice.Rows(i)("ProductName"), String))
            Cells(startRow + i, 3).PutValue(CType(dtInvoice.Rows(i)("Quantity"), Short))
            Cells(startRow + i, 4).PutValue(CType(CType(dtInvoice.Rows(i)("UnitPrice"), Decimal), Double))
            Cells(startRow + i, 4).Style = style1
            Cells(startRow + i, 5).PutValue(CType(dtInvoice.Rows(i)("Discount"), Single))
            Cells(startRow + i, 5).Style = style2
            Cells(startRow + i, 6).PutValue(CType(CType(dtInvoice.Rows(i)("ExtendedPrice"), Decimal), Double))
            Cells(startRow + i, 6).Style = style1
        Next

    End Sub

    Private Sub CreateOrder(ByVal Cells As Cells, ByVal workbook As Workbook, ByVal dtInvoice As DataTable, _
        ByVal startRow As Integer, ByVal orderID As String)

        Cells.SetRowHeight(startRow, 14)
        Cells.SetRowHeight(startRow + 3, 14)

        Cells.SetColumnWidth(1, 16)
        Cells.SetColumnWidth(2, 16)
        Cells.SetColumnWidth(3, 16)
        Cells.SetColumnWidth(4, 16)
        Cells.SetColumnWidth(5, 16)
        Cells.SetColumnWidth(6, 18)

        Dim style As Style = workbook.Styles("Font12Center")

        Dim i As Byte
        For i = 0 To 6
            Cells(startRow, i).Style = style
            Cells(startRow + 3, i).Style = style
        Next

        style = workbook.Styles("Center")
        For i = 0 To 6
            Cells(startRow + 1, i).Style = style
        Next

        Cells(startRow, 0).PutValue("Order ID:")
        Cells(startRow + 1, 0).PutValue(Integer.Parse(orderID))
        Cells(startRow, 1).PutValue("Customer ID:")
        Cells(startRow + 1, 1).PutValue(CType(dtInvoice.Rows(0)("CustomerID"), String))
        Cells(startRow, 2).PutValue("Salesperson:")
        Cells(startRow + 1, 2).PutValue(CType(dtInvoice.Rows(0)("Salesperson"), String))
        Cells(startRow, 3).PutValue("Order Date:")
        Cells(startRow + 1, 3).PutValue(CType(dtInvoice.Rows(0)("OrderDate"), DateTime).ToString("D"))
        Cells(startRow, 4).PutValue("Required Date:")
        Cells(startRow + 1, 4).PutValue(CType(dtInvoice.Rows(0)("RequiredDate"), DateTime).ToString("D"))
        Cells(startRow, 5).PutValue("Shipped Date:")
        If Not dtInvoice.Rows(0)("ShippedDate") Is DBNull.Value Then
            Cells(startRow + 1, 5).PutValue(CType(dtInvoice.Rows(0)("ShippedDate"), DateTime).ToString("D"))
        End If
        Cells(startRow, 6).PutValue("Ship Via:")
        Cells(startRow + 1, 6).PutValue(CType(dtInvoice.Rows(0)("Shippers.CompanyName"), String))

        Cells(startRow + 3, 0).PutValue("Product ID:")
        Cells(startRow + 3, 1).PutValue("Product")
        Cells(startRow + 3, 2).PutValue(" Name:")
        Cells(startRow + 3, 3).PutValue("Quantity:")
        Cells(startRow + 3, 4).PutValue("Unit Price:")
        Cells(startRow + 3, 5).PutValue("Discount:")
        Cells(startRow + 3, 6).PutValue("Extended Price:")

        style = workbook.Styles("Font12Right")
        Cells(startRow + 3, 1).Style = style

        style = workbook.Styles("Font12Left")
        Cells(startRow + 3, 2).Style = style
    End Sub

    Private Sub CreateInvoiceHeader(ByVal Cells As Cells, ByVal workbook As Workbook, ByVal dtInvoice As DataTable, ByVal startRow As Integer)

        Cells.SetRowHeight(startRow, 24)

        Cells.SetColumnWidth(0, 12)

        Cells(startRow, 5).PutValue("INVOICE")
        Dim style As Style = workbook.Styles("Darkblue")
        Cells(startRow, 5).Style = style

        style = workbook.Styles("BlackMedium")
        Dim i As Integer
        For i = 0 To Byte.MaxValue - 1
            Cells(startRow + 2, CType(i, Byte)).Style = style
        Next

        Cells(startRow + 3, 0).PutValue("One Portals Way, Twin Points WA 98156")
        Cells(startRow + 4, 0).PutValue("Phone:1-206-555-1417 Fax:1-206")
        style = workbook.Styles("DarkblueItalic")
        Cells(startRow + 3, 0).Style = style
        Cells(startRow + 4, 0).Style = style

        Dim currentDate As DateTime = DateTime.Today
        Dim strTime As String = currentDate.ToString("D")

        Cells(startRow + 3, 5).PutValue("Date:")
        Cells(startRow + 3, 6).PutValue(strTime)


        Cells(startRow + 6, 0).PutValue("Ship To:")
        style = workbook.Styles("Darkblue12")

        Cells(startRow + 6, 0).Style = style
        Cells.SetRowHeight(startRow + 6, 16)

        Cells(startRow + 6, 4).PutValue("Bill To:")
        Cells(startRow + 6, 4).Style = style

        Cells(startRow + 3, 5).Style = style

        If Not dtInvoice.Rows(0)(0) Is DBNull.Value Then
            Cells(startRow + 6, 1).PutValue(CType(dtInvoice.Rows(0)(0), String))
            Cells(startRow + 6, 5).PutValue(CType(dtInvoice.Rows(0)(0), String))
        End If
        If Not dtInvoice.Rows(0)(1) Is DBNull.Value Then
            Cells(startRow + 7, 1).PutValue(CType(dtInvoice.Rows(0)(1), String))
            Cells(startRow + 7, 5).PutValue(CType(dtInvoice.Rows(0)(1), String))
        End If

        Dim strDest As String = ""
        If Not dtInvoice.Rows(0)(2) Is DBNull.Value Then
            strDest += dtInvoice.Rows(0)(2)
        End If

        If Not dtInvoice.Rows(0)(3) Is DBNull.Value Then
            strDest += " " + dtInvoice.Rows(0)(3)
        End If

        If Not dtInvoice.Rows(0)(4) Is DBNull.Value Then
            strDest += " " + dtInvoice.Rows(0)(4)
        End If

        strDest.TrimStart(" "c)


        If strDest <> "" Then
            Cells(startRow + 8, 1).PutValue(strDest)
            Cells(startRow + 8, 5).PutValue(strDest)
        End If
        Cells(startRow + 9, 1).PutValue(CType(dtInvoice.Rows(0)(5), String))
        Cells(startRow + 9, 5).PutValue(CType(dtInvoice.Rows(0)(5), String))

    End Sub

End Class