连接附件的：
1 Private Sub btnExportForCustomer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExportForCustomer.Click 2 Dim xApp = New Excel.Application 3 Dim xBook As Excel.Workbook 4 Dim xSheet As New Excel.Worksheet 5 6 xBook = xApp.Workbooks.Add(Application.StartupPath & "\Templets\Customer packing list.xlt") 7 xSheet = xBook.Worksheets(1) 8 xSheet.PageSetup().PrintTitleRows = "$27:$27" 9 10 '---------------------------Logo------------------------ 11 Dim strCompyC As String 12 If g.gLocation = LocationType.BRUNE Then 13 strCompyC = "Brunet" 14 Else 15 If g.gLocation = LocationType.CHINA_SHENZHEN Then 16 strCompyC = "SHS" 17 Else 18 strCompyC = "Bogart" 19 End If 20 End If 21 22 Dim netLogo As OleDb.OleDbDataAdapter = gData.GetCompanyLogo(sqlConn, "", strCompyC) 23 Dim netPrint As New DataSet 24 netLogo.Fill(netPrint, "mos") 25 Try 26 strCompyC = netPrint.Tables("mos").Rows(0).Item("CompanyCode") 27 If strCompyC = "" Then 'export 不選擇logo默認一個，否則取不到數 28 strCompyC = "Brunet" 29 End If 30 Catch ex As Exception 31 End Try 32 33 If getImage(strCompyC, 643.5, 205.92) = True Then 34 xSheet.Range("A1").Select() 35 xSheet.Pictures.Insert("C:\Temp\BILogo.jpg").Select() 36 xApp.Selection.ShapeRange.IncrementLeft(244.25#) 37 'xApp.Selection.ShapeRange.IncrementTop(5.0#) 38 'xApp.Selection.ShapeRange.LockAspectRatio = True 39 End If 40 '---------------------------Logo------------------------ 41 42 '--------------------------header----------------------- 43 Dim sql As String = "SELECT A.CLOT,A.CSTORD,A.SPPROC,case when isnull(A.OLDDEG,'')='' then A.DEG else (A.DEG+'/'+A.OLDDEG) end as DEG,A.CSPRD,A.PRDCOM,A.PRDWID,C.CompanyName,C.Address1,C.Address2,C.Address3,C.Address4,C.TelNo,C.FaxNo,b.bandno FROM dbo.CompanyProfile C, dbo.FG_OrderDetail A INNER JOIN FG_PLHeader B ON A.CLOT=B.CLOT AND A.CSTORD=B.CSTORD AND A.DEG=B.DEG AND A.BICOM=B.BICOM AND A.BANDNO=B.BANDNO where B.PLNO='" & dgd.Columns("plno").Text.Trim & "' AND C.CompanyCode='" & strCompyC & "' " 44 Dim head As DataTable = gData.GetDataTable(sql, sqlConn) 45 46 sql = "SELECT t2.bandno,count(*) as count FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) left join FG_CartonBobbin t1 on c.ctnid=t1.ctnid left join FG_BobbinDetail t2 on t1.bobid=t2.bobid LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "' group by t2.bandno" 47 Dim head_LR As DataTable = gData.GetDataTable(sql, sqlConn) 48 If head_LR.Rows.Count > 1 Then 49 head_LR = gData.GetDataTable("SELECT '' as bandno,1 as count", sqlConn) 50 End If 51 head.Rows(0).Item("bandno") = head_LR.Rows(0).Item("bandno") 52 head.AcceptChanges() 53 54 'Shipment No & Customer PO 55 sql = "select distinct cstpo1 from ORFORDY where cstord ='" + head.Rows(0).Item("CSTORD") + "'" 56 Dim tbPONO As DataTable = gData.GetDataTable(sql, netConn) 57 Dim CustomerPONO As String = "" 58 For Each r As DataRow In tbPONO.Rows 59 If r.Item("cstpo1") <> "" Then 60 CustomerPONO = CustomerPONO + r.Item("cstpo1") + "," 61 End If 62 Next 63 If CustomerPONO <> "" Then 64 CustomerPONO = Mid(CustomerPONO, 1, CustomerPONO.Length - 1) 65 End If 66 xSheet.Range("H8").Resize(1, 1).Value = CustomerPONO 67 68 'print date 69 xSheet.Range("H10").Resize(1, 1).Value = Now.Date 70 71 '------------------------------------Invoice address------------------------- 72 Dim altadr As String = gData.SelectValue("select altadr from ORFORDA where cstord='" + Trim(dgd.Columns("cstord").Value) + "'", netConn, "") 73 Dim invoice As DataTable = gData.GetDataTable("select cast(adrs1 as char(60) ccsid 937) adrs1,cast(adrs2 as char(60) ccsid 937) adrs2,cast(adrs3 as char(60) ccsid 937) adrs3,cast(adrs4 as char(60) ccsid 937) adrs4,cast(adrs5 as char(60) ccsid 937) adrs5,phone1,fax1 from ORFEXS where ekey='" & Trim(dgd.Columns("ekey").Value) & "' and altadr=" + altadr, netConn) 74 If invoice.Rows.Count > 0 Then 75 xSheet.Range("B15").Resize(1, 1).Value = invoice.Rows(0).Item("adrs1") 76 xSheet.Range("B16").Resize(1, 1).Value = invoice.Rows(0).Item("adrs2") + " " + invoice.Rows(0).Item("adrs3") + " " + invoice.Rows(0).Item("adrs4") + " " + invoice.Rows(0).Item("adrs5") + Chr(10) + "Attn:" + Chr(10) + "Tel:" + invoice.Rows(0).Item("phone1") + Chr(10) + "Fax:" + invoice.Rows(0).Item("fax1") 77 End If 78 '------------------------------------Invoice address------------------------- 79 80 '--------------------------------------ship to------------------------------- 81 '即是lot的branch地址。先判斷分部（orfexba）是否有數，否則到orfexe取數。 82 Dim Branch_T As Boolean = gData.SelectValue("select count(bkey) from orfexba where ekey='" & dgd.Columns("ekey").Text.Trim & "'", adoConn, "0") > 0 83 Dim bkey As String = gData.SelectValue("select max(bkey) bkey from ORFORDT where cstord='" & dgd.Columns("cstord").Text.Trim & "' group by cstord", adoConn, "") 84 If Branch_T Then 85 Dim Branch_N As Integer = Me.CustomBranchAlt(dgd.Columns("cstord").Text.Trim, dgd.Columns("ekey").Text.Trim) 86 Dim shipto As DataTable = gData.GetDataTable("select cast(adrs1 as char(60) ccsid 937) adrs1,cast(adrs2 as char(60) ccsid 937) adrs2,cast(adrs3 as char(60) ccsid 937) adrs3,cast(adrs4 as char(60) ccsid 937) adrs4,cast(adrs5 as char(60) ccsid 937) adrs5,phone1,fax1 from ORFEXBA where ekey='" & dgd.Columns("ekey").Text.Trim & "' and bkey='" + bkey + "' AND altadr=" & Branch_N, netConn) 87 If shipto.Rows.Count > 0 Then 88 xSheet.Range("F15").Resize(1, 1).Value = shipto.Rows(0).Item("adrs1") 89 xSheet.Range("F16").Resize(1, 1).Value = shipto.Rows(0).Item("adrs2") + " " + shipto.Rows(0).Item("adrs3") + " " + shipto.Rows(0).Item("adrs4") + " " + shipto.Rows(0).Item("adrs5") + Chr(10) + "Attn:" + Chr(10) + "Tel:" + shipto.Rows(0).Item("phone1") + Chr(10) + "Fax:" + shipto.Rows(0).Item("fax1") 90 End If 91 Else 92 Dim shipto As DataTable = gData.GetDataTable("select cast(adrs1 as char(60) ccsid 937) adrs1,cast(adrs2 as char(60) ccsid 937) adrs2,cast(adrs3 as char(60) ccsid 937) adrs3,cast(adrs4 as char(60) ccsid 937) adrs4,cast(adrs5 as char(60) ccsid 937) adrs5,phone1,fax1 from orfexe where ekey='" + dgd.Columns("ekey").Text.Trim + "'", netConn) 93 If shipto.Rows.Count > 0 Then 94 xSheet.Range("F15").Resize(1, 1).Value = shipto.Rows(0).Item("adrs1") 95 xSheet.Range("F16").Resize(1, 1).Value = shipto.Rows(0).Item("adrs2") + " " + shipto.Rows(0).Item("adrs3") + " " + shipto.Rows(0).Item("adrs4") + " " + shipto.Rows(0).Item("adrs5") + Chr(10) + "Attn:" + Chr(10) + "Tel:" + shipto.Rows(0).Item("phone1") + Chr(10) + "Fax:" + shipto.Rows(0).Item("fax1") 96 End If 97 End If 98 '--------------------------------------ship to------------------------------- 99 100 'BI Call Lot No 101 xSheet.Range("B20").Resize(1, 1).Value = head.Rows(0).Item("CLOT") 102 103 'Article No 104 Dim strAtr = CStr(IIf(IsDBNull(head.Rows(0).Item("DEG")), "", head.Rows(0).Item("DEG"))) 105 If Not IsDBNull(head.Rows(0).Item("bandno")) Then 106 If CStr(head.Rows(0).Item("bandno")).Trim.Length > 0 Then 107 strAtr += "-" + CStr(head.Rows(0).Item("bandno")) 108 End If 109 End If 110 xSheet.Range("B23").Resize(1, 1).Value = strAtr 111 112 'Cust. Article No 113 xSheet.Range("G23").Resize(1, 1).Value = head.Rows(0).Item("CSPRD") 114 115 'Special Process 116 xSheet.Range("B24").Resize(1, 1).Value = head.Rows(0).Item("SPPROC") 117 118 'Description 119 xSheet.Range("B25").Resize(1, 1).Value = head.Rows(0).Item("PRDCOM") 120 121 'Width 122 xSheet.Range("B26").Resize(1, 1).Value = head.Rows(0).Item("PRDWID") 123 '--------------------------header----------------------- 124 125 '--------------------------detail----------------------- 126 sql = "SELECT CTNNO,BATCHNO,ltrim(rtrim(CSCOMD)) AS CSCOMD,CSCOM,GRSWGTKG,NETWGTKG,NETWGTLB,C.grslenm,NETLENM,C.deflenm,D.CTNDIMD FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "' order by CTNNO" 127 Dim detail As DataTable = gData.GetDataTable(sql, sqlConn) 128 Dim i As Integer = 0 129 Dim nRow As DataRow 130 Dim count As Integer = detail.Rows.Count 131 Dim ArrayData(count, 11) As Object 132 If count > 0 Then 133 For Each nRow In detail.Rows 134 xSheet.Rows("29:29").EntireRow.Insert() 135 ArrayData(i, 0) = nRow.Item("CTNNO") 136 ArrayData(i, 1) = nRow.Item("BATCHNO") 137 ArrayData(i, 2) = nRow.Item("CSCOMD") 138 ArrayData(i, 3) = nRow.Item("CSCOM") 139 ArrayData(i, 4) = nRow.Item("GRSWGTKG") 140 ArrayData(i, 5) = nRow.Item("NETWGTKG") 141 ArrayData(i, 6) = nRow.Item("NETWGTLB") 142 ArrayData(i, 7) = nRow.Item("grslenm") 143 ArrayData(i, 8) = nRow.Item("NETLENM") 144 ArrayData(i, 9) = nRow.Item("deflenm") 145 ArrayData(i, 10) = nRow.Item("CTNDIMD") 146 i = i + 1 147 Next 148 xSheet.Range("A28").Resize(count, 11).Value = ArrayData 149 End If 150 Dim sumnum As Integer = 29 + count - 1 151 xSheet.Rows(CStr(sumnum) + ":" + CStr(sumnum)).EntireRow.delete() 152 153 sql = "SELECT count(distinct CTNNO) as count,sum(GRSWGTKG) as GRSWGTKG,sum(NETWGTKG) as NETWGTKG,sum(NETWGTLB) as NETWGTLB,sum(C.grslenm) as grslenm,sum(NETLENM) as NETLENM,sum(C.deflenm) as deflenm FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "'" 154 Dim sums As DataTable = gData.GetDataTable(sql, sqlConn) 155 156 xSheet.Cells(sumnum, 1) = "Total Carton/Rolls:" + CStr(IIf(IsDBNull(sums.Rows(0).Item("count")), "", sums.Rows(0).Item("count"))) 157 xSheet.Range("E" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 158 xSheet.Range("F" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 159 xSheet.Range("G" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 160 xSheet.Range("H" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 161 xSheet.Range("I" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 162 xSheet.Range("J" + CStr(sumnum)).FormulaR1C1 = "=SUM(R[-" + CStr(count) + "]C:R[-1]C)" 163 '--------------------------detail----------------------- 164 165 '---------------------------ShipMark---------------------- 166 Dim tabShipMark As DataTable = gData.GetDataTable("Select * from dbo.FG_PLShipMark where plno=" & dgd.Columns("plno").Text.Trim & " order by remlin", sqlConn) 167 If tabShipMark.Rows.Count > 0 Then 168 Dim ShipMark(tabShipMark.Rows.Count, 1) As Object 169 For j As Integer = 0 To tabShipMark.Rows.Count - 1 170 xSheet.Rows(CStr(34 + count) + ":" + CStr(34 + count)).EntireRow.Insert() 171 ShipMark(j, 0) = tabShipMark.Rows(j).Item("remark") 172 Next 173 Dim num As Integer = 33 + count + tabShipMark.Rows.Count 174 xSheet.Range("B" + CStr(33 + count)).Resize(tabShipMark.Rows.Count, 1).Value = ShipMark 175 xSheet.Rows(CStr(num) + ":" + CStr(num)).EntireRow.delete() 176 End If 177 '---------------------------ShipMark---------------------- 178 179 '---------------------------footer------------------------ 180 Dim footer As Integer = 36 181 If count > 0 Then 182 footer = footer + count - 1 183 End If 184 If tabShipMark.Rows.Count > 0 Then 185 footer = footer + tabShipMark.Rows.Count - 1 186 End If 187 Dim footer1 As String = "" 188 Dim footer2 As String = "" 189 Dim footer3 As String = "" 190 footer1 = head.Rows(0).Item("Address1") 191 xSheet.Range("A" + CStr(footer)).Resize(1, 1).Value = footer1 192 footer += 1 193 194 footer2 = head.Rows(0).Item("Address2") + head.Rows(0).Item("Address3") + head.Rows(0).Item("Address4") 195 xSheet.Range("A" + CStr(footer)).Resize(1, 1).Value = footer2 196 footer += 1 197 198 footer3 = "Tel:" + head.Rows(0).Item("TelNo") + " Fax:" + head.Rows(0).Item("FaxNo") 199 xSheet.Range("A" + CStr(footer)).Resize(1, 1).Value = footer3 200 '---------------------------footer------------------------ 201 202 '頁腳 203 xSheet.PageSetup.CenterFooter = footer1 + Chr(10) + footer2 + Chr(10) + footer3 204 '打印範圍 205 xSheet.PageSetup.PrintArea = "$A$1:$k$" + CStr(footer - 3) 206 207 xApp.DisplayAlerts = False 208 xBook.Worksheets(1).Select() 209 xApp.Visible = True 210 xApp.Caption = Me.Text 211 xApp.ActiveWindow.Caption = "Customer Packing List" 212 End Sub 213 214 Private Function CustomBranchAlt(ByVal Lot_C As String, ByVal Custom_C As String) As Integer 215 Try 216 Dim SQL_C As String = "select max(bkey) bkey from ORFORDT where cstord='" & Lot_C & "' group by cstord" 217 Dim Branch_C As String = gData.SelectValue(SQL_C, adoConn, "") 218 SQL_C = "select altadr from orfexba where ekey='" & Custom_C & "' and bkey='" & Branch_C & "'" 219 Dim Branch_N As Integer = gData.SelectValue(SQL_C, adoConn, "0") 220 Return Branch_N 221 Catch ex As Exception 222 Return 0 223 End Try 224 End Function
直接代码的：
1 Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click 2 If Me.dgd.Splits(0).Rows.Count <= 0 Then 3 Exit Sub 4 End If 5 Dim tbDetail As DataTable = gData.GetDataTable("select * from FG_PLDetail WHERE PLNO='" + dgd.Columns("plno").Text.Trim + "' ", sqlConn) 6 If tbDetail.Rows.Count = 0 Then 7 MessageBox.Show("Please add PL details first!", "Packing List") 8 Exit Sub 9 End If 10 Cursor.Current = Cursors.WaitCursor 11 gSub.setPrompt("Working...") 12 Try 13 Dim xApp As Excel.Application = New Excel.Application 14 Dim xBook As Excel.Workbook 15 Dim xSheet As New Excel.Worksheet 16 xBook = xApp.Workbooks.Add 17 xSheet = xBook.Worksheets(1) 18 xSheet.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter 19 xSheet.Cells.VerticalAlignment = Excel.XlHAlign.xlHAlignCenter 20 21 xSheet.Cells.RowHeight = 21.5 22 xSheet.Cells.Font.Size = 12 23 xSheet.Cells.Font.Name = "Arial" 24 xApp.WindowState = Excel.XlWindowState.xlMaximized 25 '1頁寬 26 With xSheet.PageSetup 27 .PrintTitleRows = "$12:$12" 28 .LeftMargin = xApp.Application.InchesToPoints(0.25) 29 .RightMargin = xApp.Application.InchesToPoints(0.25) 30 .TopMargin = xApp.Application.InchesToPoints(0.25) 31 .BottomMargin = xApp.Application.InchesToPoints(1) 32 .HeaderMargin = xApp.Application.InchesToPoints(0.3) 33 .FooterMargin = xApp.Application.InchesToPoints(0.3) 34 .PrintComments = xlPrintNoComments 35 .Orientation = Excel.XlPageOrientation.xlPortrait 36 .Draft = False 37 .FirstPageNumber = xlAutomatic 38 .Order = xlDownThenOver 39 .BlackAndWhite = False 40 .Zoom = False 41 .FitToPagesWide = 1 42 .FitToPagesTall = 1000 43 .PrintErrors = xlPrintErrorsDisplayed 44 '.PaperSize = 120 45 .PrintGridlines = False 46 .CenterHorizontally = True 47 End With 48 49 50 Dim strCompyC As String 51 If g.gLocation = LocationType.BRUNE Then 52 strCompyC = "Brunet" 53 Else 54 If g.gLocation = LocationType.CHINA_SHENZHEN Then 55 strCompyC = "SHS" 56 Else 57 strCompyC = "Bogart" 58 End If 59 End If 60 61 Dim netLogo As OleDb.OleDbDataAdapter = gData.GetCompanyLogo(sqlConn, "", strCompyC) 62 Dim netPrint As New DataSet 63 netLogo.Fill(netPrint, "mos") 64 Try 65 strCompyC = netPrint.Tables("mos").Rows(0).Item("CompanyCode") 66 If strCompyC = "" Then 'export 不選擇logo默認一個，否則取不到數 67 strCompyC = "Brunet" 68 End If 69 Catch ex As Exception 70 End Try 71 72 '---------------------------Logo------------------------ 73 If getImage(strCompyC, 390, 124.8) = True Then 74 xSheet.Range("C1").Select() 75 xSheet.Pictures.Insert("C:\Temp\BILogo.jpg").Select() 76 xApp.Selection.ShapeRange.IncrementLeft(45.0#) 77 'xApp.Selection.ShapeRange.IncrementTop(5.0#) 78 'xApp.Selection.ShapeRange.LockAspectRatio = True 79 End If 80 '---------------------------Logo------------------------ 81 82 '---------------------------head------------------------ 83 Dim netDs As New DataSet 84 Dim sql As String = "SELECT 'PL'+LTRIM(RTRIM(CAST(B.PLNO AS CHAR(18)))) PLNO,A.CSTORD,A.CLOT,A.CORDNO,A.BICOM,A.BANDNO,A.CSTREF,A.EXTNAM,case when isnull(A.OLDDEG,'')='' then A.DEG else (A.DEG+'/'+A.OLDDEG) end as DEG,A.PRDDSC,A.PRDWID,A.SHORDNO,C.CompanyName,C.Address1,C.Address2,C.Address3,C.Address4,C.TelNo,C.FaxNo,A.SPPROC,A.PRDCOM,B.BANDNO FROM dbo.CompanyProfile C, dbo.FG_OrderDetail A INNER JOIN FG_PLHeader B ON A.CLOT=B.CLOT AND A.CSTORD=B.CSTORD AND A.DEG=B.DEG AND A.BICOM=B.BICOM AND A.BANDNO=B.BANDNO where B.PLNO='" & dgd.Columns("plno").Text.Trim & "' AND C.CompanyCode='" & strCompyC & "' " 85 Dim head As DataTable = gData.GetDataTable(sql, sqlConn) 86 netDs.Tables.Add(head) 87 head.TableName = "Head" 88 89 sql = "SELECT t2.bandno,count(*) as count FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) left join FG_CartonBobbin t1 on c.ctnid=t1.ctnid left join FG_BobbinDetail t2 on t1.bobid=t2.bobid LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "' group by t2.bandno" 90 Dim head_LR As DataTable = gData.GetDataTable(sql, sqlConn) 91 If head_LR.Rows.Count > 1 Then 92 head_LR = gData.GetDataTable("SELECT '' as bandno,1 as count", sqlConn) 93 End If 94 netDs.Tables("Head").Rows(0).Item("bandno") = head_LR.Rows(0).Item("bandno") 95 netDs.AcceptChanges() 96 97 'Packing List 98 xSheet.Range("H2").Resize(1, 1).Value = "Packing List" 99 xSheet.Range("H3").Resize(1, 1).Value = "Print Date:" 100 xSheet.Range("H4").Resize(1, 1).Value = "Packing No:" 101 With xSheet.Range("I3").Resize(1, 2) 102 .Merge() 103 .Value = Now.Date 104 End With 105 With xSheet.Range("I4").Resize(1, 2) 106 .Merge() 107 .Value = netDs.Tables("Head").Rows(0).Item("PLNO") 108 End With 109 xSheet.Range("H2").Resize(3, 3).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 110 'main 111 With xSheet.Range("A7").Resize(1, 5) 112 .Merge() 113 .Value = netDs.Tables("Head").Rows(0).Item("CompanyName") 114 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 115 End With 116 With xSheet.Range("A8").Resize(1, 5) 117 .Merge() 118 Dim strAtr = "Atr. No. :" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("DEG")), "", netDs.Tables("Head").Rows(0).Item("DEG"))) 119 If Not IsDBNull(netDs.Tables("Head").Rows(0).Item("BANDNO")) Then 120 If CStr(netDs.Tables("Head").Rows(0).Item("BANDNO")).Trim.Length > 0 Then 121 strAtr += "-" + CStr(netDs.Tables("Head").Rows(0).Item("BANDNO")) 122 End If 123 End If 124 .Value = strAtr 125 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 126 End With 127 With xSheet.Range("A9").Resize(2, 5) 128 .Merge() 129 .Value = "Special Process:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("SPPROC")), "", netDs.Tables("Head").Rows(0).Item("SPPROC"))) 130 .WrapText = True 131 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 132 End With 133 With xSheet.Range("A11").Resize(1, 5) 134 .Merge() 135 .Value = "Mo:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("SHORDNO")), "", netDs.Tables("Head").Rows(0).Item("SHORDNO"))) 136 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 137 End With 138 With xSheet.Range("F7").Resize(1, 5) 139 .Merge() 140 .Value = "P/O No. :" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("CORDNO")), "", netDs.Tables("Head").Rows(0).Item("CORDNO"))).Trim() + "/" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("CLOT")), "", netDs.Tables("Head").Rows(0).Item("CLOT"))).Trim() 141 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 142 End With 143 With xSheet.Range("F8").Resize(3, 5) 144 .Merge() 145 .Value = "Description:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("PRDCOM")), "", netDs.Tables("Head").Rows(0).Item("PRDCOM"))) 146 .WrapText = True 147 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 148 End With 149 With xSheet.Range("F11").Resize(1, 5) 150 .Merge() 151 .Value = "Width:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("PRDWID")), "", netDs.Tables("Head").Rows(0).Item("PRDWID"))) 152 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 153 End With 154 '-----------------去內邊框----------------------------- 155 'With xSheet.Range("A7").Resize(5, 10) 156 ' '.Interior.ColorIndex = 2 157 ' '.Borders(xlDiagonalDown).LineStyle = xlNone 158 ' '.Borders(xlDiagonalUp).LineStyle = xlNone 159 ' With .Borders(xlEdgeLeft) 160 ' .LineStyle = xlContinuous 161 ' .Weight = xlThin 162 ' .ColorIndex = xlAutomatic 163 ' End With 164 ' With .Borders(xlEdgeTop) 165 ' .LineStyle = xlContinuous 166 ' .Weight = xlThin 167 ' .ColorIndex = xlAutomatic 168 ' End With 169 ' With .Borders(xlEdgeBottom) 170 ' .LineStyle = xlContinuous 171 ' .Weight = xlThin 172 ' .ColorIndex = xlAutomatic 173 ' End With 174 ' With .Borders(xlEdgeRight) 175 ' .LineStyle = xlContinuous 176 ' .Weight = xlThin 177 ' .ColorIndex = xlAutomatic 178 ' End With 179 ' '.Borders(xlInsideVertical).LineStyle = xlNone 180 ' '.Borders(xlInsideHorizontal).LineStyle = xlNone 181 'End With 182 'With xSheet.Range("E7").Resize(5, 1) 183 ' With .Borders(xlEdgeRight) 184 ' .LineStyle = xlContinuous 185 ' .Weight = xlThin 186 ' .ColorIndex = xlAutomatic 187 ' End With 188 'End With 189 '-----------------去內邊框----------------------------- 190 '---------------------------head------------------------ 191 192 '---------------------------detail------------------------ 193 sql = "SELECT CTNNO,BATCHNO,ltrim(rtrim(CSCOMD)),GRSWGTKG,NETWGTKG,NETWGTLB,C.grslenm,NETLENM,C.deflenm,D.CTNDIMD FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "' order by CTNNO" 194 Dim detail As DataTable = gData.GetDataTable(sql, sqlConn) 195 196 sql = "SELECT count(distinct CTNNO) as count,sum(GRSWGTKG) as GRSWGTKG,sum(NETWGTKG) as NETWGTKG,sum(NETWGTLB) as NETWGTLB,sum(C.grslenm) as grslenm,sum(NETLENM) as NETLENM,sum(C.deflenm) as deflenm FROM dbo.FG_PLDetail a LEFT JOIN dbo.FG_PLHeader b ON (a.PLNO=b.PLNO) LEFT JOIN dbo.FG_CartonDetail c on (a.ctnid=c.ctnid) LEFT JOIN dbo.FG_CartonDimension d on (c.ctndim=d.ctndim) LEFT JOIN dbo.FG_OrderDetail o on (b.clot=o.clot and b.cstord=o.cstord and b.deg=o.deg and b.bicom=o.bicom and b.bandno=o.bandno) WHERE a.PLNO='" & dgd.Columns("plno").Text.Trim & "'" 197 Dim sums As DataTable = gData.GetDataTable(sql, sqlConn) 198 199 Dim count = detail.Rows.Count 200 Dim Herder() As String = "CTN No.,Batch No.,Color,G.W.(kgs),N.W.(kgs),N.W.(LBS),Gross(m),N.Length(m),Tare(m),Meas/cm".Split(",") 201 Dim data(count + 1, Herder.Length) As Object 202 For i As Integer = 0 To count 203 If i = 0 Then 'header 204 For k As Integer = 0 To Herder.Length - 1 205 data(0, k) = Herder(k) 206 Next 207 Else 'data 208 For j As Integer = 0 To Herder.Length - 1 209 data(i, j) = detail.Rows(i - 1).Item(j) 210 Next 211 End If 212 Next 213 With xSheet.Range("A12").Resize(count + 2, Herder.Length) 214 .Value = data 215 .Borders.LineStyle = 1 216 End With 217 With xSheet.Range("A12").Resize(1, Herder.Length) 218 .Interior.Color = RGB(192, 192, 192) '底色 219 End With 220 221 'sum 222 Dim x = 13 + count 223 xSheet.Range("A" + CStr(x)).Resize(1, 1).Value = "Ctns:" + CStr(IIf(IsDBNull(sums.Rows(0).Item("count")), "", sums.Rows(0).Item("count"))) 224 With xSheet.Range("B" + CStr(x)).Resize(1, 2) 225 .Merge() 226 .Value = "Total:" 227 .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter 228 End With 229 Dim sum(6) As Object 230 sum(0) = sums.Rows(0).Item("GRSWGTKG") 231 sum(1) = sums.Rows(0).Item("NETWGTKG") 232 sum(2) = sums.Rows(0).Item("NETWGTLB") 233 sum(3) = sums.Rows(0).Item("grslenm") 234 sum(4) = sums.Rows(0).Item("NETLENM") 235 sum(5) = sums.Rows(0).Item("deflenm") 236 xSheet.Range("D" + CStr(x)).Resize(1, 6).Value = sum 237 238 xSheet.Range("A1").Resize(1, 1).RowHeight = 93.75 239 'ColumnWidth 240 xSheet.Range("A1").Resize(1, 1).ColumnWidth = 7.57 241 xSheet.Range("B1").Resize(1, 1).ColumnWidth = 14.5 242 xSheet.Range("C1").Resize(1, 1).ColumnWidth = 23.15 243 xSheet.Range("D1").Resize(1, 1).ColumnWidth = 10 244 xSheet.Range("E1").Resize(1, 1).ColumnWidth = 9.71 245 xSheet.Range("F1").Resize(1, 1).ColumnWidth = 10.5 246 xSheet.Range("G1").Resize(1, 1).ColumnWidth = 9.57 247 xSheet.Range("H1").Resize(1, 1).ColumnWidth = 12.43 248 xSheet.Range("I1").Resize(1, 1).ColumnWidth = 8.5 249 xSheet.Range("J1").Resize(1, 1).ColumnWidth = 15.71 250 '---------------------------detail------------------------ 251 252 '---------------------------ShipMark---------------------- 253 x += 2 254 With xSheet.Range("A" + CStr(x)).Resize(1, 2) 255 .Merge() 256 .Value = "Shipping Mark:" 257 .HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 258 End With 259 Dim tabShipMark As DataTable = gData.GetDataTable("Select * from dbo.FG_PLShipMark where plno=" & dgd.Columns("plno").Text.Trim & " order by remlin", sqlConn) 260 If tabShipMark.Rows.Count > 0 Then 261 Dim ShipMark(tabShipMark.Rows.Count, 1) As Object 262 For i As Integer = 0 To tabShipMark.Rows.Count - 1 263 ShipMark(i, 0) = tabShipMark.Rows(i).Item("remark") 264 Next 265 x += 1 266 xSheet.Range("B" + CStr(x)).Resize(tabShipMark.Rows.Count, 1).Value = ShipMark 267 For i As Integer = x To x + tabShipMark.Rows.Count 268 xSheet.Range("B" + CStr(i)).Resize(1, 9).Merge() 269 xSheet.Range("B" + CStr(i)).Resize(1, 9).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft 270 Next 271 x += tabShipMark.Rows.Count + 2 272 Else 273 x += 5 274 End If 275 276 '---------------------------ShipMark---------------------- 277 278 '---------------------------footer------------------------ 279 x += 1 280 Dim footer1 As String = "" 281 Dim footer2 As String = "" 282 Dim footer3 As String = "" 283 With xSheet.Range("A" + CStr(x)).Resize(1, Herder.Length) 284 .Merge() 285 footer1 = netDs.Tables("Head").Rows(0).Item("Address1") 286 .Value = footer1 287 .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter 288 '.Interior.Color = RGB(192, 192, 192) '底色 289 End With 290 x += 1 291 With xSheet.Range("A" + CStr(x)).Resize(1, Herder.Length) 292 .Merge() 293 footer2 = CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("Address2")), "", netDs.Tables("Head").Rows(0).Item("Address2"))) + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("Address3")), "", netDs.Tables("Head").Rows(0).Item("Address3"))) + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("Address4")), "", netDs.Tables("Head").Rows(0).Item("Address4"))) 294 .Value = footer2 295 .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter 296 '.Interior.Color = RGB(192, 192, 192) '底色 297 End With 298 x += 1 299 With xSheet.Range("A" + CStr(x)).Resize(1, Herder.Length) 300 .Merge() 301 footer3 = "Tel:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("TelNo")), "", netDs.Tables("Head").Rows(0).Item("TelNo"))) + " Fax:" + CStr(IIf(IsDBNull(netDs.Tables("Head").Rows(0).Item("FaxNo")), "", netDs.Tables("Head").Rows(0).Item("FaxNo"))) 302 .Value = footer3 303 .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter 304 '.Interior.Color = RGB(192, 192, 192) '底色 305 End With 306 '---------------------------footer------------------------ 307 308 '頁腳 309 xSheet.PageSetup.CenterFooter = footer1 + Chr(10) + footer2 + Chr(10) + footer3 310 '打印範圍 311 xSheet.PageSetup.PrintArea = "$A$1:$J$" + CStr(x - 3) 312 313 xApp.Range("A1").Select() 314 315 xApp.ActiveWindow.Zoom = 100 316 xApp.Visible = True 317 xApp.Caption = "Bogart ProTex Report" 318 xApp.ActiveWindow.Caption = "Data Export" 319 320 xSheet = Nothing 321 xBook = Nothing 322 xApp = Nothing 323 GC.Collect() 324 Catch ex As Exception 325 MsgBox(ex.ToString(), MsgBoxStyle.Information, "Packing List") 326 End Try 327 gSub.setPrompt("Ready") 328 Me.Cursor = Cursors.Default 329 End Sub
vinson
