Imports System.Data.SqlClient Imports System.Windows.Forms.VisualStyles.VisualStyleElement Public Class Frm_Retan Dim cmd As New SqlCommand Dim da As New SqlDataAdapter Dim dt As New DataTable Dim sql As String Dim conn As New SqlConnection Dim PTCache As Microsoft.Office.Interop.Excel.PivotCache Dim PT As Microsoft.Office.Interop.Excel.PivotTable Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click Dim sql1 As String Dim sql2 As String Dim sql3 As String Dim sql4 As String sql1 = "SELECT CARD as 卡号, card2 as 卡号1, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep FROM RT_RETAN_RECORD_D WHERE " sql2 = "(date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "')" sql3 = ") AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ORDER BY p.CARD) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" '--------------全部下鼓 sql = sql1 + sql2 + sql3 + " order by date " cmd.CommandText = sql dt = New DataTable da.SelectCommand = cmd da.Fill(dt) dgv2.DataSource = dt Label3.Text = dt.Compute("sum(下鼓重)", "") ' Label6.Text = dt.Compute("sum(出库重)", "") 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 & ") as p" 'cmd.CommandText = sql 'Label3.Text = cmd.ExecuteScalar.ToString Dim dt1 As New DataTable Dim dt2 As New DataTable Dim dt3 As New DataTable Dim dt4 As New DataTable Dim dt5 As New DataTable Dim dt6 As New DataTable '------蓝皮下鼓 Dim dv As New DataView(dt) dv.RowFilter = ("出库重 <>0 and 类别1='打底'") dt1 = dv.ToTable() DataGridView3.DataSource = dt1 TextBox1.Text = dt1.Compute("sum(出库重)", "") '------蓝皮下鼓(无出库) dv.RowFilter = ("出库重 =0 and 类别<>'其他' and 类别1='打底' and 皮源 not like '*干*' and 皮源 not like '*brf*' ") dt2 = dv.ToTable() DataGridView5.DataSource = dt2 TextBox2.Text = dt2.Compute("sum(下鼓重)", "") '------套染 dv.RowFilter = ("(类别1='套染' or 皮源 like '*C级*') and (皮源 not like '*brf*')") dt3 = dv.ToTable() DataGridView1.DataSource = dt3 TextBox3.Text = dt3.Compute("sum(下鼓重)", "") '------回鼓 dv.RowFilter = ("类别1='回鼓' and 皮源 not like '*干*'") dt4 = dv.ToTable() DataGridView4.DataSource = dt4 TextBox5.Text = dt4.Compute("sum(出库重)", "") '------改色 dv.RowFilter = ("(皮源 like '*干*' or 皮源 like '*brf*') and (皮源 not like '*C级*') ") dt5 = dv.ToTable() DataGridView2.DataSource = dt5 TextBox4.Text = dt5.Compute("sum(下鼓重)", "") '------改色 dv.RowFilter = ("类别='其他'") dt6 = dv.ToTable() DataGridView6.DataSource = dt6 TextBox6.Text = dt6.Compute("sum(下鼓重)", "") TextBox7.Text = Val(TextBox1.Text) + Val(TextBox2.Text) + Val(TextBox3.Text) + Val(TextBox4.Text) + Val(TextBox5.Text) + Val(TextBox6.Text) DataGridView1.Columns("卡号1").Visible = False DataGridView1.Columns(0).Width = 150 DataGridView1.Columns(1).Width = 70 DataGridView1.Columns(2).Width = 66 DataGridView1.Columns(3).Width = 73 DataGridView1.Columns("技术员").Width = 80 DataGridView1.Columns("代码").Width = 65 DataGridView1.Columns("单价").Width = 65 DataGridView1.Columns("厚度").Width = 65 DataGridView1.Columns("日期").Width = 90 DataGridView1.Columns("选级").Width = 65 DataGridView2.Columns("卡号1").Visible = False DataGridView2.Columns(0).Width = 150 DataGridView2.Columns(1).Width = 70 DataGridView2.Columns(2).Width = 66 DataGridView2.Columns(3).Width = 73 DataGridView2.Columns("技术员").Width = 80 DataGridView2.Columns("代码").Width = 65 DataGridView2.Columns("单价").Width = 65 DataGridView2.Columns("厚度").Width = 65 DataGridView2.Columns("日期").Width = 90 DataGridView2.Columns("选级").Width = 65 DataGridView3.Columns("卡号1").Visible = False DataGridView3.Columns(0).Width = 150 DataGridView3.Columns(1).Width = 70 DataGridView3.Columns(2).Width = 66 DataGridView3.Columns(3).Width = 73 DataGridView3.Columns("技术员").Width = 80 DataGridView3.Columns("代码").Width = 65 DataGridView3.Columns("单价").Width = 65 DataGridView3.Columns("厚度").Width = 65 DataGridView3.Columns("日期").Width = 90 DataGridView3.Columns("选级").Width = 65 DataGridView4.Columns("卡号1").Visible = False DataGridView4.Columns(0).Width = 150 DataGridView4.Columns(1).Width = 70 DataGridView4.Columns(2).Width = 66 DataGridView4.Columns(3).Width = 73 DataGridView4.Columns("技术员").Width = 80 DataGridView4.Columns("代码").Width = 65 DataGridView4.Columns("单价").Width = 65 DataGridView4.Columns("厚度").Width = 65 DataGridView4.Columns("日期").Width = 90 DataGridView4.Columns("选级").Width = 65 DataGridView5.Columns("卡号1").Visible = False DataGridView5.Columns(0).Width = 150 DataGridView5.Columns(1).Width = 70 DataGridView5.Columns(2).Width = 66 DataGridView5.Columns(3).Width = 73 DataGridView5.Columns("技术员").Width = 80 DataGridView5.Columns("代码").Width = 65 DataGridView5.Columns("单价").Width = 65 DataGridView5.Columns("厚度").Width = 65 DataGridView5.Columns("日期").Width = 90 DataGridView5.Columns("选级").Width = 65 DataGridView6.Columns("卡号1").Visible = False DataGridView6.Columns(0).Width = 150 DataGridView6.Columns(1).Width = 70 DataGridView6.Columns(2).Width = 66 DataGridView6.Columns(3).Width = 73 DataGridView6.Columns("技术员").Width = 80 DataGridView6.Columns("代码").Width = 65 DataGridView6.Columns("单价").Width = 65 DataGridView6.Columns("厚度").Width = 65 DataGridView6.Columns("日期").Width = 90 DataGridView6.Columns("选级").Width = 65 'sql = sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is not null and type=N'打底' order by date" 'cmd.CommandText = sql 'dt = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt) 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is not null and type=N'打底' " & ") as p" 'cmd.CommandText = sql 'Label6.Text = cmd.ExecuteScalar.ToString 'TextBox1.Text = cmd.ExecuteScalar.ToString '------蓝皮下鼓(无) 'sql = sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is null and 类别<>N'其他' and type=N'打底' order by date" 'cmd.CommandText = sql 'dt = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt) 'DataGridView5.DataSource = dt 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having AVG(挑级仓出库重) is null and 类别<>N'其他' and type=N'打底' " & ") as p" 'cmd.CommandText = sql 'Label16.Text = cmd.ExecuteScalar.ToString 'TextBox2.Text = cmd.ExecuteScalar.ToString ''------套染 'sql = sql1 + sql2 + sql3 + " having type=N'套染' order by date" 'cmd.CommandText = sql 'dt = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt) 'DataGridView1.DataSource = dt 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having type=N'套染' " & ") as p" 'cmd.CommandText = sql 'Label16.Text = cmd.ExecuteScalar.ToString 'TextBox3.Text = cmd.ExecuteScalar.ToString ''------回鼓 'sql = sql1 + sql2 + sql3 + " having type=N'回鼓' order by date" 'cmd.CommandText = sql 'dt = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt) 'DataGridView4.DataSource = dt 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having type=N'回鼓' " & ") as p" 'cmd.CommandText = sql 'Label16.Text = cmd.ExecuteScalar.ToString 'TextBox5.Text = cmd.ExecuteScalar.ToString ''------合鼓 'sql = sql1 + sql2 + sql3 + " having 类别=N'其他' order by date" 'cmd.CommandText = sql 'dt = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt) 'DataGridView6.DataSource = dt 'sql = "select cast(sum(下鼓重) as numeric(10,1)) from (" + sql1 + sql2 + sql3 + " having 类别=N'其他' " & ") as p" 'cmd.CommandText = sql 'TextBox6.Text = cmd.ExecuteScalar.ToString End Sub Private Sub Frm_Retan_Load(sender As Object, e As EventArgs) Handles MyBase.Load 'connstring = "Data Source=127.0.0.1;Persist Security Info=True;User ID=sa;Password=Lafayette11;Initial Catalog=maindb_hy2014" conn.ConnectionString = connstring conn.Open() dgv2.RowHeadersWidth = 15 dgv2.ColumnHeadersHeight = 20 dgv2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText 'DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText 'DataGridView2.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText DataGridView3.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText ' DataGridView4.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText ' DataGridView5.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText ' DataGridView6.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText ' DataGridView7.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText ' DataGridView8.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText cmd.Connection = conn sql = "SELECT TOP (1) date FROM RT_RETAN_RECORD ORDER BY date DESC" cmd.CommandText = sql Label11.Text = cmd.ExecuteScalar End Sub Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox8.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\下鼓染色入库.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CPRK0" Dim yy As Integer = 1 Dim x As Integer = 0 'sql = "" ''----下鼓 'Dim dt_out As DataTable 'cmd.CommandText = sql 'dt_out = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt_out) Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'郑笔成" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" 'If x = 1 Or x = 2 Then ' xlSheet.Cells(x + 2, 9) = "'607-R" ' xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Else xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" 'End If xlSheet.Cells(x + 2, 11) = "'103" xlSheet.Cells(x + 2, 12) = "'曾添梅" xlSheet.Cells(x + 2, 13) = "'103" xlSheet.Cells(x + 2, 14) = "'曾添梅" xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT top(100) percent CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') and p.source not like '%美国%' THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层'WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号 FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 6 Select Case xx Case 1 sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' and source not like N'%干%' " my_type = "--蓝皮下鼓--" sql1 = "SELECT zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240630.weight FROM (" + sql1 + ") AS zz LEFT OUTER JOIN (select date,card,weight,commentd from rt_TM240630 AS rt_TM240630_1 where date='2024/8/31') as rt_tm240630 ON zz.卡号 = rt_tm240630.card WHERE (rt_tm240630.weight IS NULL)" Case 2 sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%C级%') and (type<>N'回鼓') order by date" my_type = "--改色--" Case 3 sql1 = sql + " having (type=N'套染' or source like N'%C级%') and (source not like N'%brf%') order by date" my_type = "--套染--" Case 4 sql1 = sql + " having type='回鼓' order by date" my_type = "--回鼓--" Case 5 sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " my_type = "--蓝皮下鼓无对应--" Case 6 sql1 = "SELECT zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240630.weight FROM (" + sql1 + ") AS zz LEFT OUTER JOIN (select date,card,weight,commentd from rt_TM240630 AS rt_TM240630_1 where date='2024/9/30') as rt_tm240630 ON zz.卡号 = rt_tm240630.card WHERE (rt_tm240630.commentD LIKE N'%鼓中%')" my_type = "--蓝皮下鼓(鼓中)--" End Select dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd da.Fill(dt2) For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb ' xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--改色--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 End If If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓--" Then xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓无对应--" Then xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If End If xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期") Select Case xx Case 1 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" xlSheet.Cells(x + 2, 9) = "'607-R" xlSheet.Cells(x + 2, 10) = "'挑选等级-R" Case 2 xlSheet2.Cells(yy + 1 + y, 25) = "'313" xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '皮胚改色 xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" 'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" 'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" Case 3 xlSheet2.Cells(yy + 1 + y, 25) = "'313" xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '套染 xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" Case 4 xlSheet2.Cells(yy + 1 + y, 25) = "'313" xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" '回鼓 xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" Case 5 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" xlSheet.Cells(x + 2, 9) = "'607-R" xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Case 6 ' xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08" ' xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和" End Select xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户") xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级") xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 Next xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("O2:O" & x + 1).FillDown() 'xlSheet2.Range("P2:P" & x + 1).FillDown() xlSheet2.Range("G:M").EntireColumn.Hidden = True xlSheet2.Range("T:X").EntireColumn.Hidden = True xlSheet2.Range("AB:AK").EntireColumn.Hidden = True PTCache = xlBook.PivotCaches.Add _ (SourceType:=Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase, SourceData:=xlSheet2.Range("a1:au" & xlSheet2.UsedRange.Rows.Count).CurrentRegion.Address) PT = PTCache.CreatePivotTable _ (TableDestination:=xlSheet2.Cells(2, 51), TableName:="PivotTable1") With PT '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField .PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField End With xlSheet2.PivotTables("PivotTable1").TableStyle2 = "PivotStyleMedium9" '---------------- PT = PTCache.CreatePivotTable(TableDestination:=xlSheet2.Cells(2, 54), TableName:="PivotTable2") With PT '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField .PivotFields("备注").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField End With xlSheet2.PivotTables("PivotTable2").TableStyle2 = "PivotStyleMedium9" '---------------- PT = PTCache.CreatePivotTable(TableDestination:=xlSheet2.Cells(2, 57), TableName:="PivotTable3") With PT '.PivotFields("批号").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField .PivotFields("收货仓库_FName").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlRowField .PivotFields("实收数量").Orientation = Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField End With xlSheet2.PivotTables("PivotTable3").TableStyle2 = "PivotStyleMedium9" MsgBox("") End Sub Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox9.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlApp1 As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlBook1 As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlApp1 = CreateObject("Excel.Application") xlApp1.Visible = True xlApp1.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\水场出库.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "SCLL0" Dim yy As Integer = 1 Dim x As Integer = 0 Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) Dim final_no As Integer For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" xlSheet.Cells(x + 2, 9) = "'622-R" xlSheet.Cells(x + 2, 10) = "'煎板、真空、挤水-R" xlSheet.Cells(x + 2, 12) = "'103" xlSheet.Cells(x + 2, 13) = "'曾添梅" xlSheet.Cells(x + 2, 14) = "'103" xlSheet.Cells(x + 2, 15) = "'曾添梅" ' xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') and p.source not like '%美国%' THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号' FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = "SELECT y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, y.出库重, y.化工成本, rt_TM240731.card FROM (SELECT s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号 FROM RT_RETAN_RECORD_D WHERE (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS zz ON s.CARD = zz.card_buff GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd, s.备注, s.皮源1, s.代码, zz.date_buff HAVING (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN rt_TM240731 ON y.卡号 = rt_TM240731.card WHERE (rt_TM240731.card IS NULL)" my_type = "--蓝皮下鼓(已磨皮)--" Case 2 sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%A级%') and (source not like N'%B级%') and (source not like N'%C级%') and (type<>N'回鼓') and color not like N'黑色%' order by date" my_type = "--改色--" Case 3 sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%') order by date" my_type = "--套染--" Case 4 sql1 = sql + " having type='回鼓' order by date" my_type = "--回鼓--" Case 5 sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " my_type = "--蓝皮下鼓无对应--" 'Case 6 ' sql1 = sql + " having 类别=N'其他' order by date" ' my_type = "--合鼓--" End Select dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd da.Fill(dt2) For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb 'xlSheet2.Cells(yy + 1 + y, 5) = "112." & dt2.Rows(y).Item("皮源代码") 'xlSheet2.Cells(yy + 1 + y, 6) = dt2.Rows(y).Item("皮源1") ' xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--改色--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--蓝皮下鼓无对应--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") End If xlSheet2.Cells(yy + 1 + y, 28) = "'--" & dt2.Rows(y).Item("日期") & " 磨皮--" xlSheet2.Cells(yy + 1 + y, 55) = dt2.Rows(y).Item("鼓号") & my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期") Select Case xx Case 1 xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/蓝皮下鼓" Case 2 xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.04" xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚改色" 'xlSheet.Cells(x + 2, 9) = "'621-R" 'xlSheet.Cells(x + 2, 10) = "'染色-R" 'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" 'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" Case 3 xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.02" xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚套染" 'xlSheet.Cells(x + 2, 9) = "'621-R" 'xlSheet.Cells(x + 2, 10) = "'染色-R" Case 4 xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.03" xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/皮胚回鼓" 'xlSheet.Cells(x + 2, 9) = "'621-R" 'xlSheet.Cells(x + 2, 10) = "'染色-R" Case 5 xlSheet2.Cells(yy + 1 + y, 35) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 36) = "'染色仓/蓝皮下鼓" 'xlSheet.Cells(x + 2, 9) = "'607-R" 'xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Case 6 ' xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08" ' xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和" End Select 'If dt_out.Rows(x).Item("type") = "打底" And IsDBNull(dt_out.Rows(x).Item("card_shaving")) And dt_out.Rows(x).Item("类别") <> "其他" Then 'ElseIf dt_out.Rows(x).Item("type") = "打底" And Not (IsDBNull(dt_out.Rows(x).Item("card_shaving"))) Then 'ElseIf dt_out.Rows(x).Item("type") = "套染" Then 'ElseIf dt_out.Rows(x).Item("type") = "回鼓" Or dt_out.Rows(x).Item("type") = "套色" Then 'ElseIf dt_out.Rows(x).Item("类别") = "其他" Then 'End If xlSheet2.Cells(yy + 1 + y, 53) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 52) = "'" & dt2.Rows(y).Item("客户") ' xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 54) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 56) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("选级") ' xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 60) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 final_no = x Next sql = "SELECT TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, rt_TM240630.source1, rt_TM240630.code, z.date_buff,CASE WHEN rt_tm240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_tm240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN rt_tm240630.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (rt_tm240630.source LIKE N'%巴西%' OR rt_tm240630.source LIKE N'%bx%' or rt_tm240630.source LIKE N'%南美%') THEN N'巴西 二层' WHEN rt_tm240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_tm240630.source LIKE N'%德国%' THEN N'德国 二层' WHEN rt_tm240630.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN rt_tm240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN rt_tm240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN rt_tm240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_tm240630.source LIKE N'%APP%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%日本%' THEN N'日本 二层' WHEN rt_tm240630.source LIKE N'%美国%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_tm240630.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN rt_tm240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_tm240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN rt_tm240630.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_tm240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN rt_tm240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN rt_tm240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源 FROM rt_TM240630 LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS z ON rt_TM240630.card = z.card_buff WHERE (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff < '2024/09/01') AND (z.date_buff > '2024/7/31') AND rt_TM240630.date = '2024/07/31'" '----240630期初 dt = New DataTable cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt) Dim final_no1 As Integer final_no1 = final_no For dd As Integer = 0 To dt.Rows.Count - 1 Dim serial1 As Integer = 1 xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang" xlSheet.Cells(dd + final_no1 + 3, 6) = "'1" xlSheet.Cells(dd + final_no1 + 3, 7) = "'2" xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701" xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R" xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R" xlSheet.Cells(dd + final_no1 + 3, 12) = "'103" xlSheet.Cells(dd + final_no1 + 3, 13) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 14) = "'103" xlSheet.Cells(dd + final_no1 + 3, 15) = "'曾添梅" xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1 '行号 xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb If dt.Rows(dd).Item("type") = "反绒" Then xlSheet2.Cells(yy + 1 + dd, 6) = "313.FM" xlSheet2.Cells(yy + 1 + dd, 7) = "二层皮" xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源") '批号 Else xlSheet2.Cells(yy + 1 + dd, 6) = "313.PU" xlSheet2.Cells(yy + 1 + dd, 7) = "贴膜皮" xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源") '批号 End If xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight") xlSheet2.Cells(yy + 1 + dd, 28) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--" xlSheet2.Cells(yy + 1 + dd, 32) = dt.Rows(dd).Item("date_buff") xlSheet2.Cells(yy + 1 + dd, 35) = "'213A-R.01" xlSheet2.Cells(yy + 1 + dd, 36) = "染色仓/蓝皮下鼓" xlSheet2.Cells(yy + 1 + dd, 53) = dt.Rows(dd).Item("type") xlSheet2.Cells(yy + 1 + dd, 52) = "'" & dt.Rows(dd).Item("cust") xlSheet2.Cells(yy + 1 + dd, 62) = dt.Rows(dd).Item("tech") xlSheet2.Cells(yy + 1 + dd, 54) = dt.Rows(dd).Item("source") xlSheet2.Cells(yy + 1 + dd, 56) = dt.Rows(dd).Item("color") xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("thick") xlSheet2.Cells(yy + 1 + dd, 58) = dt.Rows(dd).Item("card") final_no += 1 bb = bb + 1 Next my_total_no = my_total_no + dt.Rows.Count 'For dd As Integer = 0 To dt.Rows.Count - 1 xlSheet2.Range("A2:A" & my_total_no).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & my_total_no).FillDown() xlSheet2.Range("I2:I" & my_total_no).FillDown() xlSheet2.Range("J2:J" & my_total_no).FillDown() xlSheet2.Range("K2:V" & my_total_no).FillDown() ' xlSheet.Range("A2:A" & final_no).FillDown() xlSheet.Range("R2:V" & final_no).FillDown() 'xlBook1 = xlApp.Workbooks.Open("D:\K3\k3表格模板\313涂饰在制品入库.xls",, True) 'xlBook1.Activate() 'For i = 1 To xlApp.Worksheets.Count ' If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then ' xlSheet = xlApp.Workbooks(1).Worksheets(i) ' xlSheet.Activate() ' Exit For ' End If 'Next MsgBox("") End Sub Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox12.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313回鼓套染改色--入库.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CPRK0" Dim yy As Integer = 1 Dim x As Integer = 0 'sql = "" ''----下鼓 'Dim dt_out As DataTable 'cmd.CommandText = sql 'dt_out = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt_out) Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" xlSheet.Cells(x + 2, 11) = "'103" xlSheet.Cells(x + 2, 12) = "'曾添梅" xlSheet.Cells(x + 2, 13) = "'103" xlSheet.Cells(x + 2, 14) = "'曾添梅" xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号 FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = "" 'sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' order by date" 'my_type = "--蓝皮下鼓--" Case 2 sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%' ) order by date" my_type = "--改色--" Case 3 sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%') order by date" my_type = "--套染--" Case 4 sql1 = sql + " having type='回鼓' order by date" my_type = "--回鼓--" Case 5 sql1 = "" 'sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " 'my_type = "--蓝皮下鼓无对应--" 'Case 6 ' sql1 = sql + " having 类别=N'其他' order by date" ' my_type = "--合鼓--" End Select cmd.Connection = conn dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd If sql1 <> "" Then da.Fill(dt2) End If For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb ' xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--改色--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 End If If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓--" Then 'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") 'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") 'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1") '批号 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "112.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "112.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If ElseIf my_type = "--蓝皮下鼓无对应--" Then 'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") 'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") 'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1") '批号 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "112.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "112.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If End If xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期") xlSheet2.Cells(yy + 1 + y, 25) = "'313" xlSheet2.Cells(yy + 1 + y, 26) = "涂饰在制品" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户") xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级") xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 Next xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("O2:O" & x + 1).FillDown() 'xlSheet2.Range("P2:P" & x + 1).FillDown() MsgBox("") End Sub Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox10.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlApp1 As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlBook1 As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313回套改--出库.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "SCLL0" Dim yy As Integer = 1 Dim x As Integer = 0 Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) Dim final_no As Integer For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" xlSheet.Cells(x + 2, 12) = "'103" xlSheet.Cells(x + 2, 13) = "'曾添梅" xlSheet.Cells(x + 2, 14) = "'103" xlSheet.Cells(x + 2, 15) = "'曾添梅" ' xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号 FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = "" Case 2 sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%' ) order by date" my_type = "--改色--" Case 3 sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%') order by date" my_type = "--套染--" Case 4 sql1 = sql + " having type='回鼓' order by date" my_type = "--回鼓--" Case 5 sql1 = "" End Select cmd.Connection = conn dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd If sql1 <> "" Then da.Fill(dt2) End If For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb 'xlSheet2.Cells(yy + 1 + y, 5) = "112." & dt2.Rows(y).Item("皮源代码") 'xlSheet2.Cells(yy + 1 + y, 6) = dt2.Rows(y).Item("皮源1") ' xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--改色--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 6) = "313.FM" xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 6) = "313.PU" xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 23) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 6) = "112.FM" ' xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 6) = "112.PU" ' xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" 'End If 'xlSheet2.Cells(yy + 1 + y, 23) = "112 " & dt2.Rows(y).Item("皮源1") '批号 'xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") '' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--蓝皮下鼓无对应--" Then 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 6) = "112.FM" ' xlSheet2.Cells(yy + 1 + y, 7) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 6) = "112.PU" ' xlSheet2.Cells(yy + 1 + y, 7) = "贴膜皮" 'End If 'xlSheet2.Cells(yy + 1 + y, 23) = "112 " & dt2.Rows(y).Item("皮源1") '批号 'xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") '' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") End If xlSheet2.Cells(yy + 1 + y, 28) = "'--" & dt2.Rows(y).Item("日期") & my_type xlSheet2.Cells(yy + 1 + y, 55) = dt2.Rows(y).Item("鼓号") & my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期") xlSheet2.Cells(yy + 1 + y, 35) = "'313" xlSheet2.Cells(yy + 1 + y, 36) = "涂饰在制品" xlSheet2.Cells(yy + 1 + y, 53) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 52) = "'" & dt2.Rows(y).Item("客户") ' xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 54) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 56) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("选级") ' xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 60) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 final_no = x Next sql = "SELECT rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment,CASE WHEN rt_tm240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_tm240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN rt_tm240630.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (rt_tm240630.source LIKE N'%巴西%' OR rt_tm240630.source LIKE N'%bx%' or rt_tm240630.source LIKE N'%南美%') THEN N'巴西 二层' WHEN rt_tm240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_tm240630.source LIKE N'%德国%' THEN N'德国 二层' WHEN rt_tm240630.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN rt_tm240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN rt_tm240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN rt_tm240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_tm240630.source LIKE N'%APP%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%日本%' THEN N'日本 二层' WHEN rt_tm240630.source LIKE N'%美国%' THEN N'美国 二层' WHEN rt_tm240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_tm240630.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN rt_tm240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_tm240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN rt_tm240630.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_tm240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN rt_tm240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN rt_tm240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源, rt_TM240630.source1, rt_TM240630.code, z.date_buff FROM rt_TM240630 LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS z ON rt_TM240630.card = z.card_buff WHERE (rt_TM240630.commentE LIKE N'%水场%' and rt_TM240630.date='2024/7/31') AND (z.date_buff < '2024/9/1') AND (z.date_buff > '2024/7/31')" '----240630期初 dt = New DataTable cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt) Dim final_no1 As Integer final_no1 = final_no For dd As Integer = 0 To dt.Rows.Count - 1 Dim serial1 As Integer = 1 xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang" xlSheet.Cells(dd + final_no1 + 3, 6) = "'1" xlSheet.Cells(dd + final_no1 + 3, 7) = "'2" xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701" xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R" xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R" xlSheet.Cells(dd + final_no1 + 3, 12) = "'103" xlSheet.Cells(dd + final_no1 + 3, 13) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 14) = "'103" xlSheet.Cells(dd + final_no1 + 3, 15) = "'曾添梅" xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1 '行号 xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb If dt.Rows(dd).Item("type") = "反绒" Then xlSheet2.Cells(yy + 1 + dd, 6) = "313.FM" xlSheet2.Cells(yy + 1 + dd, 7) = "二层皮" xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源") '批号 Else xlSheet2.Cells(yy + 1 + dd, 6) = "313.PU" xlSheet2.Cells(yy + 1 + dd, 7) = "贴膜皮" xlSheet2.Cells(yy + 1 + dd, 23) = "313 " & dt.Rows(dd).Item("皮源") '批号 End If xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight") xlSheet2.Cells(yy + 1 + dd, 28) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--" ' xlSheet2.Cells(yy + 1 + dd, 47) = dt.Rows(dd).Item("date_buff") xlSheet2.Cells(yy + 1 + dd, 35) = "'313" xlSheet2.Cells(yy + 1 + dd, 36) = "'涂饰在制品" xlSheet2.Cells(yy + 1 + dd, 53) = dt.Rows(dd).Item("type") xlSheet2.Cells(yy + 1 + dd, 52) = "'" & dt.Rows(dd).Item("cust") xlSheet2.Cells(yy + 1 + dd, 61) = dt.Rows(dd).Item("tech") xlSheet2.Cells(yy + 1 + dd, 54) = dt.Rows(dd).Item("source") xlSheet2.Cells(yy + 1 + dd, 56) = dt.Rows(dd).Item("color") xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("thick") xlSheet2.Cells(yy + 1 + dd, 58) = dt.Rows(dd).Item("card") final_no += 1 bb = bb + 1 Next my_total_no = my_total_no + dt.Rows.Count 'For dd As Integer = 0 To dt.Rows.Count - 1 xlSheet2.Range("A2:A" & my_total_no).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & my_total_no).FillDown() xlSheet2.Range("I2:I" & my_total_no).FillDown() xlSheet2.Range("J2:J" & my_total_no).FillDown() xlSheet2.Range("K2:V" & my_total_no).FillDown() ' xlSheet.Range("A2:A" & final_no).FillDown() xlSheet.Range("R2:V" & final_no).FillDown() MsgBox("") End Sub Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox11.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313入库(蓝皮下鼓及水场在制).xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CPRK0" Dim yy As Integer = 1 Dim x As Integer = 0 'sql = "" ''----下鼓 'Dim dt_out As DataTable 'cmd.CommandText = sql 'dt_out = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt_out) Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) Dim final_no As Integer For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" 'If x = 1 Or x = 2 Then ' xlSheet.Cells(x + 2, 9) = "'607-R" ' xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Else ' xlSheet.Cells(x + 2, 9) = "'621-R" ' xlSheet.Cells(x + 2, 10) = "'染色-R" 'End If xlSheet.Cells(x + 2, 11) = "'103" xlSheet.Cells(x + 2, 12) = "'曾添梅" xlSheet.Cells(x + 2, 13) = "'103" xlSheet.Cells(x + 2, 14) = "'曾添梅" xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本, cast(SUM(chem) / AVG(投鼓重) / 6 as numeric(10,2)) AS 每尺化工 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号' FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = "SELECT y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, y.出库重, y.化工成本, y.每尺化工, rt_TM240630.card FROM (SELECT s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本, CAST(SUM(s.chem) / AVG(s.投鼓重) / 6 AS numeric(10, 2)) AS 每尺化工 FROM (SELECT TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号 FROM RT_RETAN_RECORD_D WHERE (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS zz ON s.CARD = zz.card_buff GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd, s.备注, s.皮源1, s.代码, zz.date_buff HAVING (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN (SELECT date, no, card, type, word, cust, color, weight_ini, tech, source, thick, word1, weight, commentB, commentC, commentD, commentE, serial, comment, source1, code FROM rt_TM240630 AS rt_TM240630_1 WHERE (date = '2024/07/31')) AS rt_tm240630 ON y.卡号 = rt_TM240630.card WHERE (rt_TM240630.card IS NULL)" my_type = "--蓝皮下鼓--" Case 2 sql1 = "" 'sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%C级%') and (type<>N'回鼓') order by date" 'my_type = "--改色--" Case 3 sql1 = "" 'sql1 = sql + " having (type=N'套染' or source like N'%C级%') and (source not like N'%brf%') order by date" 'my_type = "--套染--" Case 4 sql1 = "" 'sql1 = sql + " having type='回鼓' order by date" 'my_type = "--回鼓--" Case 5 sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " my_type = "--蓝皮下鼓无对应--" 'Case 6 ' sql1 = sql + " having 类别=N'其他' order by date" ' my_type = "--合鼓--" End Select dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd If sql1 <> "" Then da.Fill(dt2) End If For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb ' xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then 'If dt2.Rows(y).Item("出库重") = 0 Then ' xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") 'Else ' xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") 'End If 'xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If ElseIf my_type = "--改色--" Then 'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") '' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") 'xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If ElseIf my_type = "--套染--" Then 'If dt2.Rows(y).Item("出库重") = 0 Then ' xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") ' xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 'Else ' xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") ' xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 'End If 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If ElseIf my_type = "--蓝皮下鼓--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓无对应--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If End If xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type & dt2.Rows(y).Item("日期") & " 磨皮" '备注 ' xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期") xlSheet2.Cells(yy + 1 + y, 25) = "'313" xlSheet2.Cells(yy + 1 + y, 26) = "'涂饰在制品" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户") xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级") xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 final_no = x Next sql = "SELECT TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, CASE WHEN rt_TM240630.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN rt_TM240630.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN rt_TM240630.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (rt_TM240630.source LIKE N'%巴西%' OR rt_TM240630.source LIKE N'%bx%' OR rt_TM240630.source LIKE N'%南美%') THEN N'巴西 二层' WHEN rt_TM240630.source LIKE N'%智利%' THEN N'智利 二层' WHEN rt_TM240630.source LIKE N'%德国%' THEN N'德国 二层' WHEN rt_TM240630.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN rt_TM240630.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN rt_TM240630.source LIKE N'%法国%' THEN N'法国 二层' WHEN rt_TM240630.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN rt_TM240630.source LIKE N'%APP%' THEN N'美国 二层' WHEN rt_TM240630.source LIKE N'%日本%' THEN N'日本 二层' WHEN rt_TM240630.source LIKE N'%美国%' THEN N'美国 二层' WHEN rt_TM240630.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN rt_TM240630.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN rt_TM240630.source LIKE N'%英国%' THEN N'英国 二层' WHEN rt_TM240630.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN rt_TM240630.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN rt_TM240630.source LIKE N'%南非%' THEN N'南非 二层' WHEN rt_TM240630.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN rt_TM240630.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, rt_TM240630.source1, rt_TM240630.code, z.date_buff FROM rt_TM240630 LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS z ON rt_TM240630.card = z.card_buff WHERE (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff > '2024/7/31') and (z.date_buff < '2024/9/1') and rt_TM240630.date='2024/7/31'" '----240630期初 dt = New DataTable cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt) Dim final_no1 As Integer final_no1 = final_no For dd As Integer = 0 To dt.Rows.Count - 1 Dim serial1 As Integer = 1 xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang" xlSheet.Cells(dd + final_no1 + 3, 6) = "'1" xlSheet.Cells(dd + final_no1 + 3, 7) = "'2" xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701" xlSheet.Cells(dd + final_no1 + 3, 9) = "'621-R" xlSheet.Cells(dd + final_no1 + 3, 10) = "'染色-R" xlSheet.Cells(dd + final_no1 + 3, 11) = "'103" xlSheet.Cells(dd + final_no1 + 3, 12) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 13) = "'103" xlSheet.Cells(dd + final_no1 + 3, 14) = "'曾添梅" xlSheet2.Cells(yy + 1 + dd, 2) = "'" & serial1 '行号 xlSheet2.Cells(yy + 1 + dd, 3) = "'" & my_no & bb If dt.Rows(dd).Item("type") = "反绒" Then xlSheet2.Cells(yy + 1 + dd, 5) = "313.FM" xlSheet2.Cells(yy + 1 + dd, 6) = "二层皮" xlSheet2.Cells(yy + 1 + dd, 17) = "313 " & dt.Rows(dd).Item("皮源1") '批号 Else xlSheet2.Cells(yy + 1 + dd, 5) = "313.PU" xlSheet2.Cells(yy + 1 + dd, 6) = "贴膜皮" xlSheet2.Cells(yy + 1 + dd, 17) = "313 " & dt.Rows(dd).Item("皮源1") '批号 End If xlSheet2.Cells(yy + 1 + dd, 14) = dt.Rows(dd).Item("weight") xlSheet2.Cells(yy + 1 + dd, 18) = "'-240731盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--" xlSheet2.Cells(yy + 1 + dd, 47) = dt.Rows(dd).Item("date_buff") xlSheet2.Cells(yy + 1 + dd, 25) = "'313" xlSheet2.Cells(yy + 1 + dd, 26) = "涂饰在制品" xlSheet2.Cells(yy + 1 + dd, 38) = dt.Rows(dd).Item("type") xlSheet2.Cells(yy + 1 + dd, 39) = "'" & dt.Rows(dd).Item("cust") xlSheet2.Cells(yy + 1 + dd, 40) = dt.Rows(dd).Item("tech") xlSheet2.Cells(yy + 1 + dd, 41) = dt.Rows(dd).Item("source") xlSheet2.Cells(yy + 1 + dd, 42) = dt.Rows(dd).Item("color") xlSheet2.Cells(yy + 1 + dd, 43) = dt.Rows(dd).Item("thick") xlSheet2.Cells(yy + 1 + dd, 44) = dt.Rows(dd).Item("card") final_no += 1 bb = bb + 1 Next my_total_no = my_total_no + dt.Rows.Count 'For dd As Integer = 0 To dt.Rows.Count - 1 xlSheet2.Range("A2:A" & my_total_no).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & my_total_no).FillDown() xlSheet2.Range("J2:J" & my_total_no).FillDown() xlSheet2.Range("K2:L" & my_total_no).FillDown() ' xlSheet.Range("A2:A" & final_no).FillDown() xlSheet.Range("o2:o" & final_no).FillDown() xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("O2:O" & x + 1).FillDown() 'xlSheet2.Range("P2:P" & x + 1).FillDown() MsgBox("") End Sub Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox9.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlApp1 As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlBook1 As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet_1 As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2_2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlApp1 = CreateObject("Excel.Application") xlApp1.Visible = True xlApp1.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\水场调拨至313.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CHG" Dim yy As Integer = 1 Dim x As Integer = 0 Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) Dim final_no As Integer For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'41" ' xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 7) = "'215378" ' xlSheet.Cells(x + 2, 9) = "'622-R" ' xlSheet.Cells(x + 2, 10) = "'煎板、真空、挤水-R" xlSheet.Cells(x + 2, 8) = "'103" xlSheet.Cells(x + 2, 9) = "'曾添梅" xlSheet.Cells(x + 2, 10) = "'103" xlSheet.Cells(x + 2, 11) = "'曾添梅" xlSheet.Cells(x + 2, 12) = "'1" xlSheet.Cells(x + 2, 18) = "'01" xlSheet.Cells(x + 2, 19) = "'成本调拨" xlSheet.Cells(x + 2, 20) = "'433" ' xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as '鼓号' FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = "SELECT s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期,zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本, CAST(SUM(s.chem) / AVG(s.投鼓重) / 6 AS numeric(10, 2)) AS 每尺化工 FROM (SELECT TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号 FROM RT_RETAN_RECORD_D WHERE (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN (SELECT card AS card_buff, min(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS zz ON s.CARD = zz.card_buff GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd, s.备注, s.皮源1, s.代码, zz.date_buff HAVING (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (zz.date_buff IS not NULL) and (source not like N'%干%')" sql1 = "SELECT y.卡号, y.鼓号, y.类别, y.类别1, y.生产重, y.下鼓重, y.单价, y.code, y.选级, y.出库卡, y.客户, y.颜色, y.皮源, y.日期, y.date_buff, y.厚度, y.技术员, y.皮源1, y.代码, y.出库重, y.化工成本, rt_TM240630.card FROM (SELECT s.CARD AS 卡号, s.鼓号, s.类别, s.type AS 类别1, CASE WHEN AVG(下鼓重) IS NULL THEN AVG(挑级仓出库重) ELSE AVG(下鼓重) END AS 生产重, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, zz.date_buff, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE AVG(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN '中鼓' ELSE '大鼓' END AS 鼓号 FROM RT_RETAN_RECORD_D WHERE (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) as p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS zz ON s.CARD = zz.card_buff GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd, s.备注, s.皮源1, s.代码, zz.date_buff HAVING (zz.date_buff IS NOT NULL) AND (AVG(s.挑级仓出库重) IS NOT NULL) AND (s.type = N'打底') AND (s.source NOT LIKE N'%干%')) AS y LEFT OUTER JOIN (SELECT date, no, card, type, word, cust, color, weight_ini, tech, source, thick, word1, weight, commentB, commentC, commentD, commentE, serial, comment, source1, code FROM rt_TM240630 AS rt_TM240630_1 WHERE (date = '2024/07/31')) AS rt_tm240630 ON y.卡号 = rt_TM240630.card WHERE (rt_TM240630.card IS NULL)" my_type = "--蓝皮下鼓(已磨皮)--" Case 2 sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%' ) order by date" my_type = "--改色--" Case 3 sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%') order by date" my_type = "--套染--" Case 4 sql1 = sql + " having type='回鼓' order by date" my_type = "--回鼓--" Case 5 sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " my_type = "--蓝皮下鼓无对应--" 'Case 6 ' sql1 = sql + " having 类别=N'其他' order by date" ' my_type = "--合鼓--" End Select dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd da.Fill(dt2) For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 3) = "'0" '行号 xlSheet2.Cells(yy + 1 + y, 5) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 6) = "'" & my_no & bb xlSheet2.Cells(yy + 1 + y, 7) = "'41" If my_type = "--回鼓--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 21) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--改色--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 21) = "'313 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 8) = "'313.FM" xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 8) = "'313.PU" xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 21) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") Else xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") End If ElseIf my_type = "--蓝皮下鼓(已磨皮)--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 8) = "'112.FM" xlSheet2.Cells(yy + 1 + y, 9) = "'二层皮" Else xlSheet2.Cells(yy + 1 + y, 8) = "'112.PU" xlSheet2.Cells(yy + 1 + y, 9) = "'贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 21) = "'112 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 23) = dt2.Rows(y).Item("出库重") xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("出库重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") ElseIf my_type = "--蓝皮下鼓无对应--" Then If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 8) = "'112.FM" xlSheet2.Cells(yy + 1 + y, 9) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 8) = "'112.PU" xlSheet2.Cells(yy + 1 + y, 9) = "贴膜皮" End If xlSheet2.Cells(yy + 1 + y, 21) = "'112 " & dt2.Rows(y).Item("皮源1") '批号 xlSheet2.Cells(yy + 1 + y, 24) = dt2.Rows(y).Item("下鼓重") ' xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") End If xlSheet2.Cells(yy + 1 + y, 30) = "'--" & dt2.Rows(y).Item("日期") & " 磨皮--" xlSheet2.Cells(yy + 1 + y, 58) = dt2.Rows(y).Item("鼓号") & my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 32) = dt2.Rows(y).Item("日期") Select Case xx Case 1 xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/蓝皮下鼓" Case 2 xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.04" xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚改色" Case 3 xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.02" xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚套染" Case 4 xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.03" xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/皮胚回鼓" Case 5 xlSheet2.Cells(yy + 1 + y, 36) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 37) = "'染色仓/蓝皮下鼓" End Select xlSheet2.Cells(yy + 1 + y, 40) = "'313" xlSheet2.Cells(yy + 1 + y, 41) = "'涂饰在制品" xlSheet2.Cells(yy + 1 + y, 57) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 56) = "'" & dt2.Rows(y).Item("客户") xlSheet2.Cells(yy + 1 + y, 66) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 67) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 61) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 62) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 59) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 63) = dt2.Rows(y).Item("选级") ' xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 68) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 final_no = x Next sql = "SELECT TOP (200) rt_TM240630.date, rt_TM240630.no, rt_TM240630.card, rt_TM240630.type, rt_TM240630.word, rt_TM240630.cust, rt_TM240630.color, rt_TM240630.weight_ini, rt_TM240630.tech, rt_TM240630.source, rt_TM240630.thick, rt_TM240630.word1, CAST(rt_TM240630.weight / 2 AS numeric(10, 1)) AS weight, rt_TM240630.commentB, rt_TM240630.commentC, rt_TM240630.commentD, rt_TM240630.commentE, rt_TM240630.serial, rt_TM240630.comment, rt_TM240630.source1, rt_TM240630.code, z.date_buff FROM rt_TM240630 LEFT OUTER JOIN (SELECT card AS card_buff, MIN(date) AS date_buff FROM Rt_buff_daily GROUP BY card) AS z ON rt_TM240630.card = z.card_buff WHERE (rt_TM240630.commentE LIKE N'%水场%') AND (z.date_buff < '2024/8/1')" '----240630期初 dt = New DataTable cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt) Dim final_no1 As Integer final_no1 = final_no For dd As Integer = 0 To dt.Rows.Count - 1 Dim serial1 As Integer = 1 xlSheet.Cells(dd + final_no1 + 3, 1) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 2) = String.Format("{0:d}", dt.Rows(dd).Item("date_buff")) xlSheet.Cells(dd + final_no1 + 3, 3) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 4) = "'" & my_no & bb xlSheet.Cells(dd + final_no1 + 3, 5) = "'zhang" xlSheet.Cells(dd + final_no1 + 3, 6) = "'41" xlSheet.Cells(dd + final_no1 + 3, 7) = "'215378" ' xlSheet.Cells(dd + final_no1 + 3, 8) = "'165701" 'xlSheet.Cells(dd + final_no1 + 3, 9) = "'622-R" 'xlSheet.Cells(dd + final_no1 + 3, 10) = "'煎板、真空、挤水-R" xlSheet.Cells(dd + final_no1 + 3, 8) = "'103" xlSheet.Cells(dd + final_no1 + 3, 9) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 10) = "'103" xlSheet.Cells(dd + final_no1 + 3, 11) = "'曾添梅" xlSheet.Cells(dd + final_no1 + 3, 12) = "'1" xlSheet.Cells(dd + final_no1 + 3, 18) = "'01" xlSheet.Cells(dd + final_no1 + 3, 19) = "'成本调拨" xlSheet.Cells(dd + final_no1 + 3, 20) = "'433" xlSheet2.Cells(yy + 1 + dd, 3) = "'0" '行号 xlSheet2.Cells(yy + 1 + dd, 5) = "'" & serial1 '行号 xlSheet2.Cells(yy + 1 + dd, 6) = "'" & my_no & bb xlSheet2.Cells(yy + 1 + dd, 7) = "'41" If dt.Rows(dd).Item("type") = "反绒" Then xlSheet2.Cells(yy + 1 + dd, 8) = "313.FM" xlSheet2.Cells(yy + 1 + dd, 9) = "二层皮" xlSheet2.Cells(yy + 1 + dd, 21) = "313 " & dt.Rows(dd).Item("code") '批号 Else xlSheet2.Cells(yy + 1 + dd, 8) = "313.PU" xlSheet2.Cells(yy + 1 + dd, 9) = "贴膜皮" xlSheet2.Cells(yy + 1 + dd, 21) = "313 " & dt.Rows(dd).Item("code") '批号 End If xlSheet2.Cells(yy + 1 + dd, 23) = "'kg" xlSheet2.Cells(yy + 1 + dd, 24) = dt.Rows(dd).Item("weight") xlSheet2.Cells(yy + 1 + dd, 30) = "'-240630盘点-" & dt.Rows(dd).Item("date_buff") & " 磨皮--" ' xlSheet2.Cells(yy + 1 + dd, 32) = dt.Rows(dd).Item("date_buff") xlSheet2.Cells(yy + 1 + dd, 36) = "'213A-R.01" xlSheet2.Cells(yy + 1 + dd, 37) = "染色仓/蓝皮下鼓" xlSheet2.Cells(yy + 1 + dd, 40) = "'313" xlSheet2.Cells(yy + 1 + dd, 41) = "'涂饰在制品" xlSheet2.Cells(yy + 1 + dd, 56) = "'" & dt.Rows(dd).Item("cust") xlSheet2.Cells(yy + 1 + dd, 57) = dt.Rows(dd).Item("type") xlSheet2.Cells(yy + 1 + dd, 66) = dt.Rows(dd).Item("tech") xlSheet2.Cells(yy + 1 + dd, 67) = dt.Rows(dd).Item("source") xlSheet2.Cells(yy + 1 + dd, 61) = dt.Rows(dd).Item("color") xlSheet2.Cells(yy + 1 + dd, 62) = dt.Rows(dd).Item("thick") xlSheet2.Cells(yy + 1 + dd, 59) = dt.Rows(dd).Item("card") final_no += 1 bb = bb + 1 Next my_total_no = my_total_no + dt.Rows.Count 'For dd As Integer = 0 To dt.Rows.Count - 1 xlSheet2.Range("A2:A" & my_total_no).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & my_total_no).FillDown() xlSheet2.Range("I2:I" & my_total_no).FillDown() xlSheet2.Range("J2:J" & my_total_no).FillDown() xlSheet2.Range("K2:V" & my_total_no).FillDown() ' xlSheet.Range("A2:A" & final_no).FillDown() xlSheet.Range("R2:V" & final_no).FillDown() 'xlBook1 = xlApp.Workbooks.Open("D:\K3\k3表格模板\313涂饰在制品入库.xls",, True) 'xlBook1.Activate() 'For i = 1 To xlApp.Worksheets.Count ' If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then ' xlSheet = xlApp.Workbooks(1).Worksheets(i) ' xlSheet.Activate() ' Exit For ' End If 'Next MsgBox("") End Sub Private Sub Button8_Click(sender As Object, e As EventArgs) Handles Button8.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox8.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\下鼓染色入库.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CPRK0" Dim yy As Integer = 1 Dim x As Integer = 0 'sql = "" ''----下鼓 'Dim dt_out As DataTable 'cmd.CommandText = sql 'dt_out = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt_out) Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM RT_RETAN_RECORD_D WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'郑笔成" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" 'If x = 1 Or x = 2 Then ' xlSheet.Cells(x + 2, 9) = "'607-R" ' xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Else ' xlSheet.Cells(x + 2, 9) = "'621-R" ' xlSheet.Cells(x + 2, 10) = "'染色-R" 'End If xlSheet.Cells(x + 2, 11) = "'103" xlSheet.Cells(x + 2, 12) = "'曾添梅" xlSheet.Cells(x + 2, 13) = "'103" xlSheet.Cells(x + 2, 14) = "'曾添梅" xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT r.卡号, r.卡号1, r.鼓号, r.类别, r.类别1, r.下鼓重, r.单价, r.code, r.选级, r.出库卡, r.客户, r.颜色, r.皮源, r.日期, r.厚度, r.技术员, r.皮源1, r.代码, r.出库重, r.化工成本, r.weight, CASE WHEN weight IS NULL then N'本期' else N'前期' END AS 期别, RT_313_bal_price.bal_p, CASE WHEN weight IS NULL THEN 单价 ELSE bal_p END AS 单价1 FROM (SELECT TOP (100) PERCENT s.CARD AS 卡号, s.card2 AS 卡号1, s.鼓号, s.类别, s.type AS 类别1, CAST(AVG(s.下鼓重) AS numeric(10, 1)) AS 下鼓重, CAST(AVG(s.price) AS numeric(10, 2)) AS 单价, s.code, s.选级, s.card_shaving AS 出库卡, s.CUST AS 客户, s.COLOR AS 颜色, s.source AS 皮源, s.DATE AS 日期, s.厚度, s.dd AS 技术员, s.皮源1, s.代码, CASE WHEN AVG(挑级仓出库重) IS NULL THEN 0 ELSE avg(挑级仓出库重) END AS 出库重, SUM(s.chem) AS 化工成本, rt_TM240731.weight FROM (SELECT TOP (100) PERCENT p.CARD, p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'313 无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'313 阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'313 澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') AND (NOT (p.source LIKE N'%巴西美国%')) THEN N'313 巴西 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%智利%' THEN N'313 智利 二层' WHEN p.source LIKE N'%德国%' THEN N'313 德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'313 西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'313 欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'313 法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'313 爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'313 美国 二层' WHEN p.source LIKE N'%日本%' THEN N'313 日本 二层' WHEN p.source LIKE N'%美国%' THEN N'313 美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'313 新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'313 小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'313 英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'313 墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'313 乌拉圭 二层' WHEN p.source LIKE N'%南非%' THEN N'313 南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'313 孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'313 泰国 二层' ELSE N'313 杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%意大利%' THEN N'IT' WHEN p.source LIKE N'%韩国%' THEN N'KO' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' WHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, CASE WHEN header IS NULL AND source LIKE N'%干%' THEN WEIGHT * 1.7 - w2 - w3 - w4 ELSE WEIGHT - w2 - w3 - w4 END AS WEIGHT, P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep, CASE WHEN header IS NULL THEN N'中鼓' ELSE N'大鼓' END AS 鼓号 FROM RT_RETAN_RECORD_D WHERE (date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')) AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1, p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT) AS s LEFT OUTER JOIN rt_TM240731 ON s.CARD = rt_TM240731.card GROUP BY s.CARD, s.card2, s.类别, s.type, s.下鼓重, s.鼓号, s.code, s.选级, s.card1, s.card_shaving, s.CUST, s.COLOR, s.source, s.编码, s.皮源, s.DATE, s.厚度, s.dd, s.备注, s.皮源1, s.代码, rt_TM240731.weight) AS r LEFT OUTER JOIN RT_313_bal_price ON r.类别 = RT_313_bal_price.name AND r.皮源1 = RT_313_bal_price.lot GROUP BY r.卡号, r.卡号1, r.鼓号, r.类别, r.类别1, r.下鼓重, r.单价, r.code, r.选级, r.出库卡, r.客户, r.颜色, r.皮源, r.日期, r.厚度, r.技术员, r.皮源1, r.代码, r.出库重, r.化工成本, r.weight, RT_313_bal_price.bal_p" Dim sql1 As String Dim my_type As String Dim serial As Integer = 1 For xx As Integer = 1 To 5 Select Case xx Case 1 sql1 = sql + " having (出库重<>0) and 类别1 =N'打底' and 皮源 not like N'%干%' " my_type = "--蓝皮下鼓--" 'sql1 = "SELECT zz.卡号, zz.卡号1, zz.鼓号, zz.类别, zz.类别1, zz.下鼓重, zz.单价, zz.code, zz.选级, zz.出库卡, zz.客户, zz.颜色, zz.皮源, zz.日期, zz.厚度, zz.技术员, zz.皮源1, zz.代码, zz.出库重, zz.化工成本, rt_TM240731.weight FROM (" + sql1 + ") AS zz LEFT OUTER JOIN rt_TM240731 ON zz.卡号 = rt_TM240731.card WHERE (rt_TM240731.weight IS NULL)" Case 2 sql1 = sql + " having (皮源 like N'%干%' or 皮源 like N'%蓝皮完%' ) and (皮源 not like N'%a级%') and (皮源 not like N'%b级%') and (皮源 not like N'%C级%') and (类别1<>N'回鼓') " my_type = "--改色--" Case 3 sql1 = sql + " having (类别1=N'套染' or 皮源 like N'%A级%' or 皮源 like N'%B级%' or 皮源 like N'%C级%') and (皮源 not like N'%brf%') " my_type = "--套染--" Case 4 sql1 = sql + " having 类别1='回鼓' " my_type = "--回鼓--" Case 5 sql1 = sql + " having (出库重 =0) and 类别<>'其他' and 类别1=N'打底' and 皮源 not like N'%干%' and 皮源 not like N'%brf%' " my_type = "--蓝皮下鼓无对应--" 'Case 6 ' sql1 = sql + " having 类别=N'其他' order by date" ' my_type = "--合鼓--" End Select dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd da.Fill(dt2) For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("单价") If my_type = "--回鼓--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1") Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价1") End If xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--改色--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--套染--" Then If dt2.Rows(y).Item("出库重") = 0 Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价1") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 Else xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价1") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 End If If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("出库重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("出库重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓无对应--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("类别") = "反毛" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If End If xlSheet2.Cells(yy + 1 + y, 18) = dt2.Rows(y).Item("鼓号") & my_type & dt2.Rows(y).Item("期别") '备注 xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期") Select Case xx Case 1 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" xlSheet.Cells(x + 2, 9) = "'607-R" xlSheet.Cells(x + 2, 10) = "'挑选等级-R" Case 2 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.04" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚改色" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" 'xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" 'xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" Case 3 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.02" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚套染" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" Case 4 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.03" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/皮胚回鼓" xlSheet.Cells(x + 2, 9) = "'621-R" xlSheet.Cells(x + 2, 10) = "'染色-R" Case 5 xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" xlSheet.Cells(x + 2, 9) = "'607-R" xlSheet.Cells(x + 2, 10) = "'挑选等级-R" 'Case 6 ' xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.08" ' xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/合鼓中和" End Select 'If dt_out.Rows(x).Item("type") = "打底" And IsDBNull(dt_out.Rows(x).Item("card_shaving")) And dt_out.Rows(x).Item("类别") <> "其他" Then 'ElseIf dt_out.Rows(x).Item("type") = "打底" And Not (IsDBNull(dt_out.Rows(x).Item("card_shaving"))) Then 'ElseIf dt_out.Rows(x).Item("type") = "套染" Then 'ElseIf dt_out.Rows(x).Item("type") = "回鼓" Or dt_out.Rows(x).Item("type") = "套色" Then 'ElseIf dt_out.Rows(x).Item("类别") = "其他" Then 'End If xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("客户") xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源") xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("颜色") xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("厚度") xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("卡号") xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级") xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("出库卡") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 Next bb = bb + 1 Next xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("O2:O" & x + 1).FillDown() 'xlSheet2.Range("P2:P" & x + 1).FillDown() MsgBox("") End Sub Private Sub Button9_Click(sender As Object, e As EventArgs) Handles Button9.Click Dim aa As String Dim bb As String Dim my_card As String = "" Dim my_date As Date aa = TextBox13.Text bb = aa If aa = "" Then MsgBox("请输入起始单号") Exit Sub End If Dim xlApp As Microsoft.Office.Interop.Excel.Application Dim xlBook As Microsoft.Office.Interop.Excel.Workbook Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet Dim xlSheet2 As Microsoft.Office.Interop.Excel.Worksheet Dim file_name As String = "" Dim file_name1 As String = "" Dim my_weight As Double = 0 Dim a As Integer = 0 Dim my_count As Integer = 0 Dim my_x As Integer = 0 xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.DisplayAlerts = False xlBook = xlApp.Workbooks.Open("D:\K3\k3表格模板\313入库-削匀皮.xls",, True) xlBook.Activate() Dim dt2 As New DataTable For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page1" Then xlSheet = xlApp.Workbooks(1).Worksheets(i) xlSheet.Activate() Exit For End If Next Dim my_no As String = "CPRK0" Dim yy As Integer = 1 Dim x As Integer = 0 'sql = "" ''----下鼓 'Dim dt_out As DataTable 'cmd.CommandText = sql 'dt_out = New DataTable 'da.SelectCommand = cmd 'da.Fill(dt_out) Dim my_total_no As Integer = 0 Dim dt_retan As New DataTable sql = "SELECT date FROM rt_shaving_out WHERE date BETWEEN '" & String.Format("{0:d}", dtp_begin.Value) & "' and '" & String.Format("{0:d}", dtp_end.Value) & "' group by date" cmd.CommandText = sql da.SelectCommand = cmd da.Fill(dt_retan) For x = 0 To dt_retan.Rows.Count - 1 '-----------loop xlSheet.Cells(x + 2, 1) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 2) = String.Format("{0:d}", dt_retan.Rows(x).Item("date")) xlSheet.Cells(x + 2, 3) = "'曾添梅" xlSheet.Cells(x + 2, 4) = "'" & my_no & bb xlSheet.Cells(x + 2, 5) = "'zhang" xlSheet.Cells(x + 2, 6) = "'1" xlSheet.Cells(x + 2, 7) = "'2" xlSheet.Cells(x + 2, 8) = "'165701" xlSheet.Cells(x + 2, 9) = "'607-R" xlSheet.Cells(x + 2, 10) = "'挑选等级-R" xlSheet.Cells(x + 2, 11) = "'103" xlSheet.Cells(x + 2, 12) = "'曾添梅" xlSheet.Cells(x + 2, 13) = "'103" xlSheet.Cells(x + 2, 14) = "'曾添梅" xlSheet.Cells(x + 2, 15) = "'85" For i = 1 To xlApp.Worksheets.Count If xlApp.Workbooks(1).Worksheets(i).name = "Page2" Then xlSheet2 = xlApp.Workbooks(1).Worksheets(i) xlSheet2.Activate() Exit For End If Next sql = "SELECT CARD as 卡号, card2 as 卡号1,鼓号, 类别, type as 类别1, cast(AVG(下鼓重) as numeric(10,1)) AS 下鼓重, cast(AVG(price) as numeric(10,2)) AS 单价, code, 选级, card_shaving as 出库卡, CUST as 客户, COLOR as 颜色, source as 皮源, DATE as 日期, 厚度, dd as 技术员, 皮源1, 代码, case when AVG(挑级仓出库重) is null then 0 else AVG(挑级仓出库重) end AS 出库重, SUM(chem) AS 化工成本 FROM (SELECT TOP (100) PERCENT p.CARD,p.鼓号, CASE WHEN substring(p.CARD, 3, 1) = 'F' THEN substring(p.CARD, 4, 10) + 'F' ELSE substring(p.CARD, 4, 10) + 'P' END AS card2, CASE WHEN substring(p.card, 3, 1) = 'P' THEN N'贴膜' WHEN substring(p.card, 3, 1) = 'F' THEN '反毛' ELSE '其他' END AS 类别, p.type, AVG(p.WEIGHT) / 2 AS 下鼓重, COUNT(*) AS Expr2, CASE WHEN rt_shaving_out.price IS NULL THEN 30 ELSE rt_shaving_out.price END AS price, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.WEIGHT) / 2 ELSE rt_shaving_out.weight END AS weight, CASE WHEN rt_shaving_out.code IS NULL THEN '212F.XX.2.1' ELSE rt_shaving_out.code END AS code, CASE WHEN rt_shaving_out.name IS NULL THEN N'D级' ELSE rt_shaving_out.name END AS 选级, rt_shaving_out.card1, rt_shaving_out.card AS card_shaving, p.CUST, p.COLOR, p.source, CASE WHEN rt_wetblue_name.code IS NULL THEN '212F.XX' ELSE rt_wetblue_name.code END AS 编码, CASE WHEN rt_wetblue_name.name IS NULL THEN N'杂皮 二层' ELSE rt_wetblue_name.name END AS 皮源, p.DATE, CASE WHEN substring(rt_shaving_out.code, 13, 1) = '1' THEN '1.02' WHEN substring(rt_shaving_out.code, 13, 1) = '2' THEN '1.24' WHEN substring(rt_shaving_out.code, 13, 1) = '3' THEN '1.46' WHEN substring(rt_shaving_out.code, 13, 1) = '4' THEN '1.68' WHEN substring(rt_shaving_out.code, 13, 1) IS NULL THEN '1.24' END AS 厚度, p.dd, CASE WHEN rt_shaving_out.card1 IS NULL AND p.type = N'套染' THEN N'套染' WHEN rt_shaving_out.price IS NULL AND p.type = N'打底' THEN N'回鼓' ELSE N'打底' END AS 备注, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN p.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN p.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' or p.source LIKE N'%南美%') THEN N'巴西 二层' WHEN p.source LIKE N'%智利%' THEN N'智利 二层' WHEN p.source LIKE N'%德国%' THEN N'德国 二层' WHEN p.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN p.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN p.source LIKE N'%法国%' THEN N'法国 二层' WHEN p.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN p.source LIKE N'%APP%' THEN N'美国 二层' WHEN p.source LIKE N'%日本%' THEN N'日本 二层' WHEN p.source LIKE N'%美国%' THEN N'美国 二层' WHEN p.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN p.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN p.source LIKE N'%英国%' THEN N'英国 二层' WHEN p.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN p.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN p.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN p.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN p.source LIKE N'%南非%' THEN N'南非 二层' WHEN p.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN p.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, CASE WHEN p.source LIKE N'%无铬鞣%' THEN N'CT' WHEN p.source LIKE N'%阿根廷%' THEN N'AR' WHEN p.source LIKE N'%澳%' THEN N'AU' WHEN (p.source LIKE N'%巴西%' OR p.source LIKE N'%bx%' OR p.source LIKE N'%南美%') THEN N'BR' WHEN p.source LIKE N'%智利%' THEN N'CL' WHEN p.source LIKE N'%德国%' THEN N'DE' wHEN p.source LIKE N'%西班牙%' THEN N'ES' WHEN p.source LIKE N'%欧洲%' THEN N'EU' WHEN p.source LIKE N'%法国%' THEN N'FR' WHEN p.source LIKE N'%爱尔兰%' THEN N'IE' WHEN p.source LIKE N'%日本%' THEN N'JP' WHEN p.source LIKE N'%越南APP%' THEN N'US' WHEN p.source LIKE N'%美国%' THEN N'US' WHEN p.source LIKE N'%新西兰%' THEN N'NZ' WHEN p.source LIKE N'%小皮%' THEN N'SS' WHEN p.source LIKE N'%英国%' THEN N'UK' WHEN p.source LIKE N'%墨西哥%' THEN N'MX' WHEN p.source LIKE N'%乌拉圭%' THEN N'UY' WHEN p.source LIKE N'%南非%' THEN N'ZA' WHEN p.source LIKE N'%孟加拉%' THEN N'BD' WHEN p.source LIKE N'%泰国%' THEN N'TH' ELSE N'XX' END AS 代码, rt_shaving_out.weight AS 挑级仓出库重, CASE WHEN rt_shaving_out.weight IS NULL THEN AVG(p.weight) / 2 ELSE rt_shaving_out.weight END AS 投鼓重, RT_SYS_CHEM_OUT.CODE AS Expr1, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1 AS chemprice, RT_SYS_CHEM_OUT.WEIGHT AS chemweight, CASE WHEN rt_chem_price_li.price1 IS NULL THEN 0 ELSE rt_chem_price_li.price1 * rt_sys_chem_out.weight END AS chem FROM RT_SYS_CHEM_OUT INNER JOIN rt_chem_price_li ON RT_SYS_CHEM_OUT.CODE = rt_chem_price_li.code RIGHT OUTER JOIN (SELECT NO, AREA, MONTH, HEADER, HEADER2, DATE, CARD, CARD1, CUST, COLOR, W1, P1, A1, W2, P2, A2, W3, P3, A3, W4, P4, A4, source, TOTAL, class, type, DATE1, case when header is null and source like N'%干%' then WEIGHT*1.7-w2-w3-w4 else WEIGHT-w2-w3-w4 end as WEIGHT , P, COMa, COMb, COMc, dd, ee, ff, gg, hh, Dep,case when header is null then '中鼓' else '大鼓' end as 鼓号 FROM RT_RETAN_RECORD_D WHERE date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "') AS p ON RT_SYS_CHEM_OUT.CARD = p.CARD LEFT OUTER JOIN rt_wetblue_name RIGHT OUTER JOIN rt_shaving_out ON LEFT(rt_wetblue_name.code, 7) = LEFT(rt_shaving_out.code, 7) ON p.CARD = rt_shaving_out.card1 GROUP BY p.type, p.CARD, p.CARD1,p.鼓号, rt_shaving_out.price, rt_shaving_out.weight, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.card1, rt_shaving_out.card, p.CUST, p.COLOR, p.source, rt_wetblue_name.code, rt_wetblue_name.name, p.DATE, p.dd, RT_SYS_CHEM_OUT.CODE, RT_SYS_CHEM_OUT.NAME, rt_chem_price_li.price1, RT_SYS_CHEM_OUT.WEIGHT ) AS s GROUP BY CARD, card2, 类别, type, 下鼓重, 鼓号, code, 选级, card1, card_shaving, CUST, COLOR, source, 编码, 皮源, DATE, 厚度, dd, 备注, 皮源1, 代码" Dim sql1 As String sql1 = "SELECT rt_shaving_out.date, rt_shaving_out.code, rt_shaving_out.name, rt_shaving_out.lot, rt_shaving_out.weight, rt_shaving_out.price, rt_shaving_out.amount, rt_shaving_out.p, rt_shaving_out.card, rt_shaving_out.card1, rt_shaving_out.id, RT_RETAN_RECORD1.source, CASE WHEN RT_RETAN_RECORD1.source LIKE N'%无铬鞣%' THEN N'无铬鞣 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%阿根廷%' THEN N'阿根廷 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%澳%' THEN N'澳大利亚 二层' WHEN (RT_RETAN_RECORD1.source LIKE N'%巴西%' OR RT_RETAN_RECORD1.source LIKE N'%bx%' OR RT_RETAN_RECORD1.source LIKE N'%南美%') THEN N'巴西 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%智利%' THEN N'智利 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%德国%' THEN N'德国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%西班牙%' THEN N'西班牙 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%欧洲%' THEN N'欧洲 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%法国%' THEN N'法国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%爱尔兰%' THEN N'爱尔兰 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%APP%' THEN N'美国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%日本%' THEN N'日本 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%美国%' THEN N'美国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%新西兰%' THEN N'新西兰 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%小皮%' THEN N'小皮 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%英国%' THEN N'英国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%墨西哥%' THEN N'墨西哥 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%乌拉圭%' THEN N'乌拉圭 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%意大利%' THEN N'意大利 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%韩国%' THEN N'韩国 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%南非%' THEN N'南非 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%孟加拉%' THEN N'孟加拉 二层' WHEN RT_RETAN_RECORD1.source LIKE N'%泰国%' THEN N'泰国 二层' ELSE N'杂皮 二层' END AS 皮源1, rt_shaving_out.cust, rt_shaving_out.color, rt_shaving_out.thick, RT_RETAN_RECORD1.spec FROM rt_shaving_out LEFT OUTER JOIN (SELECT MIN(date) AS date, source, card,spec FROM RT_RETAN_RECORD GROUP BY card, source,spec) AS RT_RETAN_RECORD1 ON rt_shaving_out.card1 = RT_RETAN_RECORD1.card WHERE (rt_shaving_out.date = '" & String.Format("{0:d}", dt_retan.Rows(x).Item("date")) & "')" Dim my_type As String my_type = "--蓝皮下鼓--" Dim serial As Integer = 1 'For xx As Integer = 1 To 5 ' Select Case xx ' Case 1 ' sql1 = "" ' 'sql1 = sql + " having (avg(挑级仓出库重)) is not null and type =N'打底' order by date" ' 'my_type = "--蓝皮下鼓--" ' Case 2 ' sql1 = sql + " having (source like N'%干%' or source like N'%蓝皮完%' ) and (source not like N'%a级%') and (source not like N'%b级%') and (source not like N'%C级%') and (type<>N'回鼓') and NOT(color like N'黑色%' ) order by date" ' my_type = "--改色--" ' Case 3 ' sql1 = sql + " having (type=N'套染' or source like N'%A级%' or source like N'%B级%' or source like N'%C级%') and (source not like N'%brf%') order by date" ' my_type = "--套染--" ' Case 4 ' sql1 = sql + " having type='回鼓' order by date" ' my_type = "--回鼓--" ' Case 5 ' sql1 = "" ' 'sql1 = sql + " having (avg(挑级仓出库重) is null) and 类别<>'其他' and type=N'打底' and source not like N'%干%' and source not like N'%brf%' order by date " ' 'my_type = "--蓝皮下鼓无对应--" ' 'Case 6 ' ' sql1 = sql + " having 类别=N'其他' order by date" ' ' my_type = "--合鼓--" ' End Select cmd.Connection = conn dt2 = New DataTable cmd.CommandText = sql1 da.SelectCommand = cmd If sql1 <> "" Then da.Fill(dt2) End If For y = 0 To dt2.Rows.Count - 1 xlSheet2.Cells(yy + 1 + y, 2) = "'" & serial '行号 xlSheet2.Cells(yy + 1 + y, 3) = "'" & my_no & bb xlSheet2.Cells(yy + 1 + y, 15) = dt2.Rows(y).Item("price") If my_type = "--蓝皮下鼓--" Then xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("weight") xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("amount") xlSheet2.Cells(yy + 1 + y, 17) = "313 " & dt2.Rows(y).Item("皮源1") '批号 If dt2.Rows(y).Item("spec") = "反绒" Then xlSheet2.Cells(yy + 1 + y, 5) = "313.FM" xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" Else xlSheet2.Cells(yy + 1 + y, 5) = "313.PU" xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" End If ElseIf my_type = "--蓝皮下鼓无对应--" Then 'xlSheet2.Cells(yy + 1 + y, 14) = dt2.Rows(y).Item("下鼓重") 'xlSheet2.Cells(yy + 1 + y, 16) = dt2.Rows(y).Item("下鼓重") * dt2.Rows(y).Item("单价") 'xlSheet2.Cells(yy + 1 + y, 17) = "112 " & dt2.Rows(y).Item("皮源1") '批号 'If dt2.Rows(y).Item("类别") = "反毛" Then ' xlSheet2.Cells(yy + 1 + y, 5) = "112.FM" ' xlSheet2.Cells(yy + 1 + y, 6) = "二层皮" 'Else ' xlSheet2.Cells(yy + 1 + y, 5) = "112.PU" ' xlSheet2.Cells(yy + 1 + y, 6) = "贴膜皮" 'End If End If xlSheet2.Cells(yy + 1 + y, 18) = my_type '备注 ' xlSheet2.Cells(yy + 1 + y, 22) = dt2.Rows(y).Item("日期") xlSheet2.Cells(yy + 1 + y, 25) = "'213A-R.01" xlSheet2.Cells(yy + 1 + y, 26) = "染色仓/蓝皮下鼓" ' xlSheet2.Cells(yy + 1 + y, 38) = dt2.Rows(y).Item("类别") xlSheet2.Cells(yy + 1 + y, 39) = "'" & dt2.Rows(y).Item("cust") 'xlSheet2.Cells(yy + 1 + y, 40) = dt2.Rows(y).Item("技术员") xlSheet2.Cells(yy + 1 + y, 41) = dt2.Rows(y).Item("皮源1") xlSheet2.Cells(yy + 1 + y, 42) = dt2.Rows(y).Item("color") xlSheet2.Cells(yy + 1 + y, 43) = dt2.Rows(y).Item("thick") xlSheet2.Cells(yy + 1 + y, 44) = dt2.Rows(y).Item("card1") ' xlSheet2.Cells(yy + 1 + y, 45) = dt2.Rows(y).Item("选级") 'xlSheet2.Cells(yy + 1 + y, 46) = dt2.Rows(y).Item("化工成本") xlSheet2.Cells(yy + 1 + y, 47) = dt2.Rows(y).Item("card") my_total_no = yy + 1 + y serial = serial + 1 Next yy = yy + dt2.Rows.Count aa = aa + 1 my_x = my_x + 1 bb = bb + 1 Next xlSheet2.Range("A2:A" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("B2:B" & x + 1).FillDown() xlSheet2.Range("D2:D" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("I2:I" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("J2:J" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("L2:L" & xlSheet2.UsedRange.Rows.Count).FillDown() xlSheet2.Range("M2:M" & xlSheet2.UsedRange.Rows.Count).FillDown() 'xlSheet2.Range("O2:O" & x + 1).FillDown() 'xlSheet2.Range("P2:P" & x + 1).FillDown() MsgBox("") End Sub End Class