1
Public
Sub
ExportAll(
ByVal
sender
As
Object
,
ByVal
e
As
System.Web.UI.ImageClickEventArgs)
9
10 Me .BindData()
11 Dim _asposeExcel As Aspose.Excel.Excel
12
13 _asposeExcel = New Aspose.Excel.Excel
14 ' _asposeExcel.Open(Web.HttpContext.Current.Server.MapPath("~/Documents/Templates/OrderFollowUpReportTemplate.xls"))
15
16 ' set styles
17 Me .setStyles(_asposeExcel)
18
19 Me .buildSheet(_asposeExcel)
20
21 ' Export the Excel file
22 _asposeExcel.Save( " MyPOListBySeason " & Me .m_ItemSeason.DataSetSeason.Seasons.FindBySeasonID( CType ( Me .dropSeason.SelectedItem.Value, Integer )).Name & " .xls " , Aspose.Excel.SaveType.OpenInExcel, Aspose.Excel.FileFormatType.Default, Me .Page.Response)
23
24
25 End Sub
26 '设置表格样式
27 Private Sub setStyles( ByVal _asposeExcel As Aspose.Excel.Excel)
28
29 ' Add Gray and DarkGray colors to color palette
30 _asposeExcel.ChangePalette(Drawing.Color.Gray, 54 )
31 _asposeExcel.ChangePalette(Drawing.Color.LightGray, 55 )
32
33 Dim _style As Aspose.Excel.Style
34 Dim _styleIndex As Integer
35
36 _styleIndex = _asposeExcel.Styles.Add()
37 _style = _asposeExcel.Styles(_styleIndex)
38 _style.Font.Size = 14
39 _style.Font.IsBold = True
40 _style.Font.Name = " Times New Roman "
41 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
42 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
43 Me .SetStyleBorder(_style) ' SetStyleBorder
44 _style.Name = " styleTopic1 "
45
46 _styleIndex = _asposeExcel.Styles.Add
47 _style = _asposeExcel.Styles(_styleIndex)
48 _style.Font.Size = 7
49 _style.Font.IsBold = True
50 _style.Font.Name = " Verdana "
51 _style.Font.Color = Drawing.Color.White
52 _style.IsTextWrapped = True
53 _style.ShrinkToFit = True
54 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
55 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
56 _style.ForegroundColor = Drawing.Color.Black
57 Me .SetStyleBorder(_style) ' SetStyleBorder
58 _style.Name = " styleHeader1 "
59
60 _styleIndex = _asposeExcel.Styles.Add
61 _style = _asposeExcel.Styles(_styleIndex)
62 _style.Font.Size = 7
63 _style.Font.IsBold = True
64 _style.Font.Name = " Verdana "
65 _style.Font.Color = Drawing.Color.White
66 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
67 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
68 _style.ForegroundColor = Drawing.Color.Black
69 Me .SetStyleBorder(_style) ' SetStyleBorder
70 _style.Name = " styleHeaderLeft1 "
71
72
73 _styleIndex = _asposeExcel.Styles.Add
74 _style = _asposeExcel.Styles(_styleIndex)
75 _style.Font.Size = 8
76 _style.Font.Name = " Arial "
77 _style.ForegroundColor = Drawing.Color.LightGray
78 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
79 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
80 Me .SetStyleBorder(_style) ' SetStyleBorder
81 _style.Name = " styleHeader2 "
82
83 _styleIndex = _asposeExcel.Styles.Add
84 _style = _asposeExcel.Styles(_styleIndex)
85 _style.Font.Size = 8
86 _style.Font.Name = " Arial "
87 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
88 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
89 Me .SetStyleBorder(_style) ' SetStyleBorder
90 _style.Name = " styleValue1 "
91
92 _styleIndex = _asposeExcel.Styles.Add
93 _style = _asposeExcel.Styles(_styleIndex)
94 _style.Font.Size = 8
95 _style.Font.IsBold = True
96 _style.Font.Name = " Arial "
97 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
98 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
99 Me .SetStyleBorder(_style) ' SetStyleBorder
100 _style.Name = " styleValueBoldCenter1 "
101
102 _styleIndex = _asposeExcel.Styles.Add
103 _style = _asposeExcel.Styles(_styleIndex)
104 _style.Font.Size = 8
105 _style.Font.IsBold = True
106 _style.Font.Name = " Arial "
107 _style.ForegroundColor = Drawing.Color.Gray
108 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
109 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
110 Me .SetStyleBorder(_style) ' SetStyleBorder
111 _style.Name = " styleHeader3 "
112
113 _styleIndex = _asposeExcel.Styles.Add
114 _style = _asposeExcel.Styles(_styleIndex)
115 _style.Font.Size = 8
116 _style.Font.IsBold = True
117 _style.Font.Name = " Arial "
118 _style.ForegroundColor = Drawing.Color.LightGray
119 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
120 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
121 Me .SetStyleBorder(_style) ' SetStyleBorder
122 _style.Name = " styleValueCustomer "
123
124
125 _styleIndex = _asposeExcel.Styles.Add
126 _style = _asposeExcel.Styles(_styleIndex)
127 _style.Font.Size = 7
128 _style.Font.Name = " Arial "
129 _style.IsTextWrapped = True
130 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
131 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
132 ' _style.Borders.SetStyle(Aspose.Excel.CellBorderType.Thin)
133 Me .SetStyleBorder(_style) ' SetStyleBorder
134 _style.Name = " styleValueLeftWrap1 "
135
136 _styleIndex = _asposeExcel.Styles.Add
137 _style = _asposeExcel.Styles(_styleIndex)
138 _style.Font.Size = 9
139 _style.Font.Name = " Arial "
140 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
141 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
142 Me .SetStyleBorder(_style) ' SetStyleBorder
143 _style.Name = " styleRemarks1 "
144
145 End Sub
146 '创建表格的行与列,并填充所有行
147 Private Sub buildSheet( ByVal _asposeExcel As Aspose.Excel.Excel)
148 Dim _CustomerID As Integer = 0
149 Dim _SupplierID As Integer = 0
150 Dim _SeasonOrderRowByCustomer As SeasonOrderDataSet.VW_SeasonOrderRow
151 Dim _SeasonOrderRowBySupplier As SeasonOrderDataSet.VW_SeasonOrderRow
152 Dim _SeasonOrderRows As SeasonOrderDataSet.VW_SeasonOrderRow()
153 Dim _SeasonOrderRow As SeasonOrderDataSet.VW_SeasonOrderRow
154 Dim _tempCell As ExcelCellCoordinate
155 Dim _rowIndex As Integer
156 Dim _Cells1 As Aspose.Excel.Cells
157 ' Dim _SeasonRow As SeasonDataset.SeasonsRow
158 ' _SeasonRow = Me.m_ItemSeason.DataSetSeason.Seasons.FindBySeasonID(Me.dropSeason.SelectedItem.Value)
159
160 _Cells1 = _asposeExcel.Worksheets( 0 ).Cells
161
162
163 ' fill season name
164 ' _Cells1(0, 1).PutValue(_SeasonRow.Name)
165 ' _Cells1(0, 1).Style.Font.IsItalic = True
166
167 ' Date
168 ' _Cells1(0, 9).PutValue("Date: " & Now.Date.ToString("dd-MMM-yy"))
169
170 ' locked the tables start Coordinate
171 Me .startCellOfPOListTable = New ExcelCellCoordinate( 0 , 0 )
172
173 ' *********************************** My authorized PO list table *************************************************
174 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
175 ' First row
176 ' POID
177 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " POID " )
178 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeaderLeft1 " )
179 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 3 )
180 _tempCell.ColumnIndex += 1
181 ' Lot
182 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Lot " )
183 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
184 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 4 )
185 _tempCell.ColumnIndex += 1
186 ' style
187 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Style " )
188 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
189 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
190 _tempCell.ColumnIndex += 1
191 ' Qty
192 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Qty " )
193 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
194 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
195
196 _tempCell.ColumnIndex += 1
197 ' Supplier shipment date
198 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Sup.Ship " )
199 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
200 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
201 _tempCell.ColumnIndex += 1
202 ' Customer shipment date
203 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Cus.Ship " )
204 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
205 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
206 _tempCell.ColumnIndex += 1
207 ' ETD
208 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " ETD " )
209 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
210 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
211 _tempCell.ColumnIndex += 1
212 ' QtyShipped
213 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " QtyShipped " )
214 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
215 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 9.5 )
216 _tempCell.ColumnIndex += 1
217 ' Customer LC No.
218 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " CusLCNo " )
219 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
220 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 11 )
221 _tempCell.ColumnIndex += 1
222 ' Supplier LC No.
223 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " SupLCNo " )
224 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
225 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 7 )
226 _tempCell.ColumnIndex += 1
227 ' Invoice No.
228 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Inv.No " )
229 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
230 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
231 _tempCell.ColumnIndex += 1
232 ' Status
233 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Status " )
234 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
235 _tempCell.ColumnIndex += 1
236
237 ' Value rows
238 Me .startCellOfPOListTable.RowIndex += 1
239 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
240 ' Customer
241 For Each _CustomerID In Me .m_ArrayCustomersID
242 If _CustomerID <> 0 Then
243 _tempCell.RowIndex = Me .startCellOfPOListTable.RowIndex
244 _SeasonOrderRowByCustomer = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString)( 0 )
245 ' Customer Rows
246 _Cells1.Merge(_tempCell.RowIndex, _tempCell.ColumnIndex, 1 , 12 )
247 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(_SeasonOrderRowByCustomer.CustomerName)
248 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader3 " )
249 Me .startCellOfPOListTable.RowIndex += 1
250
251 ' Supplier
252 For Each _SupplierID In Me .getSupplierByCustomerID(_CustomerID)
253 Me .startCellOfPOListTable.ColumnIndex = 0
254 _tempCell.RowIndex = Me .startCellOfPOListTable.RowIndex
255 _SeasonOrderRowBySupplier = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " SupplierID= " & _SupplierID.ToString)( 0 )
256 ' Supplier Rows
257 _Cells1.Merge(_tempCell.RowIndex, _tempCell.ColumnIndex, 1 , 12 )
258 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(_SeasonOrderRowBySupplier.SupplierName)
259 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader2 " )
260 Me .startCellOfPOListTable.RowIndex += 1
261 ' Order lot Rows
262 ' if user is in role "Manager" or is head office user ,show all
263 If Garzone.User.isUserInRoles( " Manager " , HttpContext.Current.User) Or Garzone.User.isHeadOfficeUser( CType (Context.User.Identity.Name, Integer )) Then
264 If OrderReportsUser = True Then
265 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and " & sqlstr, " OrderID ASC,CustomerShipmentDate ASC " )
266 Else
267 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and StatusID in (5,8,9,10) " , " OrderID ASC,CustomerShipmentDate ASC " )
268 End If
269
270 Else
271 ' if user is logistic user,show local office PO
272 Dim _RowUser As UserDataset.UsersRow
273 _RowUser = Me .m_ItemUser.DatasetUser.Users.FindByUserID( CType (Context.User.Identity.Name, Integer ))
274 If _RowUser.Department = User.Departments.Logistic Then
275 If OrderReportsUser = True Then
276 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and OfficeID= " & _RowUser.OfficeID.ToString & " and " & sqlstr, " OrderID ASC,CustomerShipmentDate ASC " )
277 Else
278 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and OfficeID= " & _RowUser.OfficeID.ToString & " and StatusID in (5,8,9,10) " , " OrderID ASC,CustomerShipmentDate ASC " )
279 End If
280 Else
281 ' if user is Merchandiser,show the PO according to the buyer coordinator or UserInCharge
282 If Customer.hasAccessToBuyer(_SeasonOrderRowBySupplier.BuyerID, HttpContext.Current) = True Or _SeasonOrderRowBySupplier.UserIDIncharge = CType (Context.User.Identity.Name, Integer ) Then
283 If OrderReportsUser = True Then
284 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and " & sqlstr & " and (UserIDInCharge= " & CType (Context.User.Identity.Name, Integer ) & " or BuyerId= " & _SeasonOrderRowBySupplier.BuyerID & " ) " , " OrderID ASC,CustomerShipmentDate ASC " )
285 Else
286 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and StatusID in (5,8,9,10) " & " and (UserIDInCharge= " & CType (Context.User.Identity.Name, Integer ) & " or BuyerId= " & _SeasonOrderRowBySupplier.BuyerID & " ) " , " OrderID ASC,CustomerShipmentDate ASC " )
287 End If
288 End If
289 End If
290 End If
291 For Each _SeasonOrderRow In _SeasonOrderRows
292 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
293 With _SeasonOrderRow
294 ' POID
295 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.OrderID.ToString)
296 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
297 _tempCell.ColumnIndex += 1
298 ' Lot
299 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.LotName)
300 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
301 _tempCell.ColumnIndex += 1
302 ' style
303 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.StyleNumber)
304 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
305 _tempCell.ColumnIndex += 1
306 ' Qty
307 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( FormatNumber (.QuantityOrdered, 0 ))
308 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
309 _tempCell.ColumnIndex += 1
310 ' Supplier shipment date
311 If .IsSupplierShipmentDateNull = False Then
312 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.SupplierShipmentDate.ToString( " dd-MMM-yy " ))
313 Else
314 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
315 End If
316 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
317 _tempCell.ColumnIndex += 1
318 ' Customer shipment date
319 If .IsCustomerShipmentDateNull = False Then
320 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.CustomerShipmentDate.ToString( " dd-MMM-yy " ))
321 Else
322 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
323 End If
324 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
325 _tempCell.ColumnIndex += 1
326 ' ETD
327 If .IsETADateNull = False Then
328 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.ETADate.ToString( " dd-MMM-yy " ))
329 Else
330 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
331 End If
332 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
333 _tempCell.ColumnIndex += 1
334 ' QtyShipped
335 If .IsQuantityShippedNull = False Then
336 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( FormatNumber (.QuantityShipped, 0 ))
337 Else
338 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
339 End If
340 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
341 _tempCell.ColumnIndex += 1
342 ' Customer LC No.
343 If .IsCustomerLCNull = False Then
344 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.CustomerLC)
345 Else
346 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
347 End If
348 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
349 _tempCell.ColumnIndex += 1
350 ' Supplier LC No.
351 If .IsSupplierLCNull = False Then
352 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.SupplierLC)
353 Else
354 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
355 End If
356 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
357 _tempCell.ColumnIndex += 1
358 ' Inv No.
359 If .IsInvoiceNumberNull = False Then
360 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.InvoiceNumber)
361 Else
362 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
363 End If
364 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
365 _tempCell.ColumnIndex += 1
366 ' PO status
367 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.Status)
368 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
369 _tempCell.ColumnIndex += 1
370 End With
371
372 ' Next excel row
373 Me .startCellOfPOListTable.RowIndex += 1
374 _tempCell.ColumnIndex = 0
375 Next
376
377 Next
378
379 End If
380 Next
381 ' auto fit the rows
382 For _rowIndex = 2 To _tempCell.RowIndex
383 _asposeExcel.Worksheets( 0 ).AutoFitRow(_rowIndex)
384 Next
385
386 End Sub
387 '/声明Excel表格属性
388 #Region " Inner Class "
389 Private Class ExcelCellCoordinate
390 Private m_rowIndex As Integer
391 Private m_columnIndex As Byte
392
393 Public Property RowIndex() As Integer
394 Get
395 Return m_rowIndex
396 End Get
397 Set ( ByVal Value As Integer )
398 m_rowIndex = Value
399 End Set
400 End Property
401
402 Public Property ColumnIndex() As Byte
403 Get
404 Return m_columnIndex
405 End Get
406 Set ( ByVal Value As Byte )
407 m_columnIndex = Value
408 End Set
409 End Property
410
411 Public Sub New ( ByVal rowIndex As Integer , ByVal columnIndex As Byte )
412 Me .m_rowIndex = rowIndex
413 Me .m_columnIndex = columnIndex
414 End Sub
415 End Class
416 #End Region
417
418'/设置单元格边框
419 Private Sub SetStyleBorder( ByVal _style As Aspose.Excel.Style)
420 _style.Borders(Aspose.Excel.BorderType.LeftBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
421 _style.Borders(Aspose.Excel.BorderType.RightBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
422 _style.Borders(Aspose.Excel.BorderType.TopBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
423 _style.Borders(Aspose.Excel.BorderType.BottomBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
424 End Sub
9
10 Me .BindData()
11 Dim _asposeExcel As Aspose.Excel.Excel
12
13 _asposeExcel = New Aspose.Excel.Excel
14 ' _asposeExcel.Open(Web.HttpContext.Current.Server.MapPath("~/Documents/Templates/OrderFollowUpReportTemplate.xls"))
15
16 ' set styles
17 Me .setStyles(_asposeExcel)
18
19 Me .buildSheet(_asposeExcel)
20
21 ' Export the Excel file
22 _asposeExcel.Save( " MyPOListBySeason " & Me .m_ItemSeason.DataSetSeason.Seasons.FindBySeasonID( CType ( Me .dropSeason.SelectedItem.Value, Integer )).Name & " .xls " , Aspose.Excel.SaveType.OpenInExcel, Aspose.Excel.FileFormatType.Default, Me .Page.Response)
23
24
25 End Sub
26 '设置表格样式
27 Private Sub setStyles( ByVal _asposeExcel As Aspose.Excel.Excel)
28
29 ' Add Gray and DarkGray colors to color palette
30 _asposeExcel.ChangePalette(Drawing.Color.Gray, 54 )
31 _asposeExcel.ChangePalette(Drawing.Color.LightGray, 55 )
32
33 Dim _style As Aspose.Excel.Style
34 Dim _styleIndex As Integer
35
36 _styleIndex = _asposeExcel.Styles.Add()
37 _style = _asposeExcel.Styles(_styleIndex)
38 _style.Font.Size = 14
39 _style.Font.IsBold = True
40 _style.Font.Name = " Times New Roman "
41 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
42 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
43 Me .SetStyleBorder(_style) ' SetStyleBorder
44 _style.Name = " styleTopic1 "
45
46 _styleIndex = _asposeExcel.Styles.Add
47 _style = _asposeExcel.Styles(_styleIndex)
48 _style.Font.Size = 7
49 _style.Font.IsBold = True
50 _style.Font.Name = " Verdana "
51 _style.Font.Color = Drawing.Color.White
52 _style.IsTextWrapped = True
53 _style.ShrinkToFit = True
54 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
55 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
56 _style.ForegroundColor = Drawing.Color.Black
57 Me .SetStyleBorder(_style) ' SetStyleBorder
58 _style.Name = " styleHeader1 "
59
60 _styleIndex = _asposeExcel.Styles.Add
61 _style = _asposeExcel.Styles(_styleIndex)
62 _style.Font.Size = 7
63 _style.Font.IsBold = True
64 _style.Font.Name = " Verdana "
65 _style.Font.Color = Drawing.Color.White
66 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
67 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
68 _style.ForegroundColor = Drawing.Color.Black
69 Me .SetStyleBorder(_style) ' SetStyleBorder
70 _style.Name = " styleHeaderLeft1 "
71
72
73 _styleIndex = _asposeExcel.Styles.Add
74 _style = _asposeExcel.Styles(_styleIndex)
75 _style.Font.Size = 8
76 _style.Font.Name = " Arial "
77 _style.ForegroundColor = Drawing.Color.LightGray
78 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
79 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
80 Me .SetStyleBorder(_style) ' SetStyleBorder
81 _style.Name = " styleHeader2 "
82
83 _styleIndex = _asposeExcel.Styles.Add
84 _style = _asposeExcel.Styles(_styleIndex)
85 _style.Font.Size = 8
86 _style.Font.Name = " Arial "
87 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
88 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
89 Me .SetStyleBorder(_style) ' SetStyleBorder
90 _style.Name = " styleValue1 "
91
92 _styleIndex = _asposeExcel.Styles.Add
93 _style = _asposeExcel.Styles(_styleIndex)
94 _style.Font.Size = 8
95 _style.Font.IsBold = True
96 _style.Font.Name = " Arial "
97 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Center
98 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
99 Me .SetStyleBorder(_style) ' SetStyleBorder
100 _style.Name = " styleValueBoldCenter1 "
101
102 _styleIndex = _asposeExcel.Styles.Add
103 _style = _asposeExcel.Styles(_styleIndex)
104 _style.Font.Size = 8
105 _style.Font.IsBold = True
106 _style.Font.Name = " Arial "
107 _style.ForegroundColor = Drawing.Color.Gray
108 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
109 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
110 Me .SetStyleBorder(_style) ' SetStyleBorder
111 _style.Name = " styleHeader3 "
112
113 _styleIndex = _asposeExcel.Styles.Add
114 _style = _asposeExcel.Styles(_styleIndex)
115 _style.Font.Size = 8
116 _style.Font.IsBold = True
117 _style.Font.Name = " Arial "
118 _style.ForegroundColor = Drawing.Color.LightGray
119 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
120 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
121 Me .SetStyleBorder(_style) ' SetStyleBorder
122 _style.Name = " styleValueCustomer "
123
124
125 _styleIndex = _asposeExcel.Styles.Add
126 _style = _asposeExcel.Styles(_styleIndex)
127 _style.Font.Size = 7
128 _style.Font.Name = " Arial "
129 _style.IsTextWrapped = True
130 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
131 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
132 ' _style.Borders.SetStyle(Aspose.Excel.CellBorderType.Thin)
133 Me .SetStyleBorder(_style) ' SetStyleBorder
134 _style.Name = " styleValueLeftWrap1 "
135
136 _styleIndex = _asposeExcel.Styles.Add
137 _style = _asposeExcel.Styles(_styleIndex)
138 _style.Font.Size = 9
139 _style.Font.Name = " Arial "
140 _style.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left
141 _style.VerticalAlignment = Aspose.Excel.TextAlignmentType.Center
142 Me .SetStyleBorder(_style) ' SetStyleBorder
143 _style.Name = " styleRemarks1 "
144
145 End Sub
146 '创建表格的行与列,并填充所有行
147 Private Sub buildSheet( ByVal _asposeExcel As Aspose.Excel.Excel)
148 Dim _CustomerID As Integer = 0
149 Dim _SupplierID As Integer = 0
150 Dim _SeasonOrderRowByCustomer As SeasonOrderDataSet.VW_SeasonOrderRow
151 Dim _SeasonOrderRowBySupplier As SeasonOrderDataSet.VW_SeasonOrderRow
152 Dim _SeasonOrderRows As SeasonOrderDataSet.VW_SeasonOrderRow()
153 Dim _SeasonOrderRow As SeasonOrderDataSet.VW_SeasonOrderRow
154 Dim _tempCell As ExcelCellCoordinate
155 Dim _rowIndex As Integer
156 Dim _Cells1 As Aspose.Excel.Cells
157 ' Dim _SeasonRow As SeasonDataset.SeasonsRow
158 ' _SeasonRow = Me.m_ItemSeason.DataSetSeason.Seasons.FindBySeasonID(Me.dropSeason.SelectedItem.Value)
159
160 _Cells1 = _asposeExcel.Worksheets( 0 ).Cells
161
162
163 ' fill season name
164 ' _Cells1(0, 1).PutValue(_SeasonRow.Name)
165 ' _Cells1(0, 1).Style.Font.IsItalic = True
166
167 ' Date
168 ' _Cells1(0, 9).PutValue("Date: " & Now.Date.ToString("dd-MMM-yy"))
169
170 ' locked the tables start Coordinate
171 Me .startCellOfPOListTable = New ExcelCellCoordinate( 0 , 0 )
172
173 ' *********************************** My authorized PO list table *************************************************
174 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
175 ' First row
176 ' POID
177 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " POID " )
178 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeaderLeft1 " )
179 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 3 )
180 _tempCell.ColumnIndex += 1
181 ' Lot
182 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Lot " )
183 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
184 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 4 )
185 _tempCell.ColumnIndex += 1
186 ' style
187 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Style " )
188 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
189 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
190 _tempCell.ColumnIndex += 1
191 ' Qty
192 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Qty " )
193 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
194 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
195
196 _tempCell.ColumnIndex += 1
197 ' Supplier shipment date
198 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Sup.Ship " )
199 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
200 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
201 _tempCell.ColumnIndex += 1
202 ' Customer shipment date
203 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Cus.Ship " )
204 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
205 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
206 _tempCell.ColumnIndex += 1
207 ' ETD
208 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " ETD " )
209 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
210 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 6 )
211 _tempCell.ColumnIndex += 1
212 ' QtyShipped
213 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " QtyShipped " )
214 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
215 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 9.5 )
216 _tempCell.ColumnIndex += 1
217 ' Customer LC No.
218 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " CusLCNo " )
219 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
220 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 11 )
221 _tempCell.ColumnIndex += 1
222 ' Supplier LC No.
223 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " SupLCNo " )
224 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
225 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 7 )
226 _tempCell.ColumnIndex += 1
227 ' Invoice No.
228 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Inv.No " )
229 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
230 _Cells1.SetColumnWidth(_tempCell.ColumnIndex, 5 )
231 _tempCell.ColumnIndex += 1
232 ' Status
233 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " Status " )
234 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader1 " )
235 _tempCell.ColumnIndex += 1
236
237 ' Value rows
238 Me .startCellOfPOListTable.RowIndex += 1
239 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
240 ' Customer
241 For Each _CustomerID In Me .m_ArrayCustomersID
242 If _CustomerID <> 0 Then
243 _tempCell.RowIndex = Me .startCellOfPOListTable.RowIndex
244 _SeasonOrderRowByCustomer = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString)( 0 )
245 ' Customer Rows
246 _Cells1.Merge(_tempCell.RowIndex, _tempCell.ColumnIndex, 1 , 12 )
247 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(_SeasonOrderRowByCustomer.CustomerName)
248 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader3 " )
249 Me .startCellOfPOListTable.RowIndex += 1
250
251 ' Supplier
252 For Each _SupplierID In Me .getSupplierByCustomerID(_CustomerID)
253 Me .startCellOfPOListTable.ColumnIndex = 0
254 _tempCell.RowIndex = Me .startCellOfPOListTable.RowIndex
255 _SeasonOrderRowBySupplier = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " SupplierID= " & _SupplierID.ToString)( 0 )
256 ' Supplier Rows
257 _Cells1.Merge(_tempCell.RowIndex, _tempCell.ColumnIndex, 1 , 12 )
258 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(_SeasonOrderRowBySupplier.SupplierName)
259 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleHeader2 " )
260 Me .startCellOfPOListTable.RowIndex += 1
261 ' Order lot Rows
262 ' if user is in role "Manager" or is head office user ,show all
263 If Garzone.User.isUserInRoles( " Manager " , HttpContext.Current.User) Or Garzone.User.isHeadOfficeUser( CType (Context.User.Identity.Name, Integer )) Then
264 If OrderReportsUser = True Then
265 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and " & sqlstr, " OrderID ASC,CustomerShipmentDate ASC " )
266 Else
267 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and StatusID in (5,8,9,10) " , " OrderID ASC,CustomerShipmentDate ASC " )
268 End If
269
270 Else
271 ' if user is logistic user,show local office PO
272 Dim _RowUser As UserDataset.UsersRow
273 _RowUser = Me .m_ItemUser.DatasetUser.Users.FindByUserID( CType (Context.User.Identity.Name, Integer ))
274 If _RowUser.Department = User.Departments.Logistic Then
275 If OrderReportsUser = True Then
276 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and OfficeID= " & _RowUser.OfficeID.ToString & " and " & sqlstr, " OrderID ASC,CustomerShipmentDate ASC " )
277 Else
278 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and OfficeID= " & _RowUser.OfficeID.ToString & " and StatusID in (5,8,9,10) " , " OrderID ASC,CustomerShipmentDate ASC " )
279 End If
280 Else
281 ' if user is Merchandiser,show the PO according to the buyer coordinator or UserInCharge
282 If Customer.hasAccessToBuyer(_SeasonOrderRowBySupplier.BuyerID, HttpContext.Current) = True Or _SeasonOrderRowBySupplier.UserIDIncharge = CType (Context.User.Identity.Name, Integer ) Then
283 If OrderReportsUser = True Then
284 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and " & sqlstr & " and (UserIDInCharge= " & CType (Context.User.Identity.Name, Integer ) & " or BuyerId= " & _SeasonOrderRowBySupplier.BuyerID & " ) " , " OrderID ASC,CustomerShipmentDate ASC " )
285 Else
286 _SeasonOrderRows = Me .m_Item.SeasonOrderDataSet.VW_SeasonOrder.Select( " CustomerID= " & _CustomerID.ToString & " and SupplierID= " & _SupplierID.ToString & " and StatusID in (5,8,9,10) " & " and (UserIDInCharge= " & CType (Context.User.Identity.Name, Integer ) & " or BuyerId= " & _SeasonOrderRowBySupplier.BuyerID & " ) " , " OrderID ASC,CustomerShipmentDate ASC " )
287 End If
288 End If
289 End If
290 End If
291 For Each _SeasonOrderRow In _SeasonOrderRows
292 _tempCell = New ExcelCellCoordinate( Me .startCellOfPOListTable.RowIndex, Me .startCellOfPOListTable.ColumnIndex)
293 With _SeasonOrderRow
294 ' POID
295 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.OrderID.ToString)
296 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
297 _tempCell.ColumnIndex += 1
298 ' Lot
299 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.LotName)
300 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
301 _tempCell.ColumnIndex += 1
302 ' style
303 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.StyleNumber)
304 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
305 _tempCell.ColumnIndex += 1
306 ' Qty
307 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( FormatNumber (.QuantityOrdered, 0 ))
308 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
309 _tempCell.ColumnIndex += 1
310 ' Supplier shipment date
311 If .IsSupplierShipmentDateNull = False Then
312 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.SupplierShipmentDate.ToString( " dd-MMM-yy " ))
313 Else
314 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
315 End If
316 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
317 _tempCell.ColumnIndex += 1
318 ' Customer shipment date
319 If .IsCustomerShipmentDateNull = False Then
320 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.CustomerShipmentDate.ToString( " dd-MMM-yy " ))
321 Else
322 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
323 End If
324 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
325 _tempCell.ColumnIndex += 1
326 ' ETD
327 If .IsETADateNull = False Then
328 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.ETADate.ToString( " dd-MMM-yy " ))
329 Else
330 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
331 End If
332 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
333 _tempCell.ColumnIndex += 1
334 ' QtyShipped
335 If .IsQuantityShippedNull = False Then
336 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( FormatNumber (.QuantityShipped, 0 ))
337 Else
338 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
339 End If
340 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
341 _tempCell.ColumnIndex += 1
342 ' Customer LC No.
343 If .IsCustomerLCNull = False Then
344 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.CustomerLC)
345 Else
346 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
347 End If
348 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
349 _tempCell.ColumnIndex += 1
350 ' Supplier LC No.
351 If .IsSupplierLCNull = False Then
352 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.SupplierLC)
353 Else
354 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
355 End If
356 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
357 _tempCell.ColumnIndex += 1
358 ' Inv No.
359 If .IsInvoiceNumberNull = False Then
360 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.InvoiceNumber)
361 Else
362 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue( " - " )
363 End If
364 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
365 _tempCell.ColumnIndex += 1
366 ' PO status
367 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).PutValue(.Status)
368 _Cells1(_tempCell.RowIndex, _tempCell.ColumnIndex).Style = _asposeExcel.Styles( " styleValueLeftWrap1 " )
369 _tempCell.ColumnIndex += 1
370 End With
371
372 ' Next excel row
373 Me .startCellOfPOListTable.RowIndex += 1
374 _tempCell.ColumnIndex = 0
375 Next
376
377 Next
378
379 End If
380 Next
381 ' auto fit the rows
382 For _rowIndex = 2 To _tempCell.RowIndex
383 _asposeExcel.Worksheets( 0 ).AutoFitRow(_rowIndex)
384 Next
385
386 End Sub
387 '/声明Excel表格属性
388 #Region " Inner Class "
389 Private Class ExcelCellCoordinate
390 Private m_rowIndex As Integer
391 Private m_columnIndex As Byte
392
393 Public Property RowIndex() As Integer
394 Get
395 Return m_rowIndex
396 End Get
397 Set ( ByVal Value As Integer )
398 m_rowIndex = Value
399 End Set
400 End Property
401
402 Public Property ColumnIndex() As Byte
403 Get
404 Return m_columnIndex
405 End Get
406 Set ( ByVal Value As Byte )
407 m_columnIndex = Value
408 End Set
409 End Property
410
411 Public Sub New ( ByVal rowIndex As Integer , ByVal columnIndex As Byte )
412 Me .m_rowIndex = rowIndex
413 Me .m_columnIndex = columnIndex
414 End Sub
415 End Class
416 #End Region
417
418'/设置单元格边框
419 Private Sub SetStyleBorder( ByVal _style As Aspose.Excel.Style)
420 _style.Borders(Aspose.Excel.BorderType.LeftBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
421 _style.Borders(Aspose.Excel.BorderType.RightBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
422 _style.Borders(Aspose.Excel.BorderType.TopBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
423 _style.Borders(Aspose.Excel.BorderType.BottomBorder).LineStyle = Aspose.Excel.CellBorderType.Thin
424 End Sub