Asp.Net中用Aspose 导出Excel报表
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
