-- 作者：tommargq2
-- 发布时间：2022/4/21 10:59:00
-- [求助]EXCEL报表输出报错
Dim val As Integer
If inputvalue(VAL,"请输入导出年份","请输入导出年份:") Then
Dim Book As New XLS.Book(ProjectPath & "Attachments\\输出_产量表.xls")
\'开始导出<输出1-最细颗粒度>
\'MessageBox.Show("1")
Dim Filter As String = "S elect 年份,月份,FACTORY,物料,物料描述,SUM(数量) as 合计数量," & _
"EXTWG As [车系],KOSCH As [车型码],是否试制,ISNULL(品牌,\'O\') AS 品牌1 F ROM " & _
"((((S elect YEAR(过帐日期) As 年份,MONTH(过帐日期) As 月份,工厂 As 工厂代码,物料,物料描述,数量," & _
"IIF(LEFT(物料,2)=\'42\',\'试制\',\'非试制\') As [是否试制] F ROM 产量表_起点1 " & _
"WHERE YEAR(过帐日期) = " & val & ") A " & _
"LEFT JOIN (S ELECT MATNR,KOSCH,EXTWG F ROM 匹配表_18位码车型码物料号) D ON D.[MATNR] = A.[物料]) " & _
"LEFT JOIN (S elect DISTINCT 车系,品牌 F rom 匹配表_车系简码匹配) C ON C.[车系] = D.[EXTWG]) " & _
"LEFT JOIN PECPPROJECTDB.dbo.ACT匹配_工厂对照表 B ON A.工厂代码 = B.工厂代码) " & _
"GROUP BY 年份,月份,FACTORY,物料,物料描述,是否试制,EXTWG,KOSCH,品牌 "
DataTables("表A").Fill(Filter,"Finace",True)
Dim gcs As List(Of String) = DataTables("表A").GetValues("FACTORY")
Dim pps As List(Of String) = DataTables("表A").GetValues("品牌1")
Dim br As Integer = Tables("表A").Rows.Count
Dim cs As new List(of String)
For c As Integer = 0 To Tables("表A").Cols.Count - 1
cs.Add(Tables("表A").Cols(c).Name)
Next
Dim Sheet As XLS.Sheet = Book.Sheets("输出1-最细颗粒度")
For r As Integer = 0 To br - 1
Dim RW As Row = Tables("表A").Rows(r)
For c As Integer = 0 To cs.Count - 1
Sheet(r + 1,c).Value = RW(cs(c))
Next
Next
\'开始导出<输出2-制造 分工厂分车系分月>
\'MessageBox.Show("2")
Filter = "S elect M1.* FROM " & _
"(S elec t 年份,月份,A.工厂代码,FACTORY,SUM(数量) As 合计数量,IsNull(品牌,\'O\') as 品牌1," & _
"EXTWG As [车系] F ROM ((((Select YEAR(过帐日期) As 年份,MONTH(过帐日期) As 月份," & _
"工厂 As 工厂代码,数量,物料 F ROM 产量表_起点1 WHERE YEAR(过帐日期) = " & VAL & ") A " & _
"LEFT JOIN (S elect MATNR,EXTWG F rom 匹配表_18位码车型码物料号) D ON D.[MATNR] = A.[物料]) " & _
"LEFT JOIN (S elect DISTINCT 车系,品牌 Fr om 匹配表_车系简码匹配) C ON C.[车系] = D.[EXTWG]) " & _
"LEFT JOIN PECPPROJECTDB.dbo.ACT匹配_工厂对照表 B ON A.工厂代码 = B.工厂代码) " & _
"GROUP BY 年份,月份,A.工厂代码,FACTORY,EXTWG,品牌) As X1 " & _
"PIVOT (SUM(合计数量) For 月份 In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) As M1 "
DataTables("表A").Fill(Filter,"Finace",True)
\'MessageBox.Show("2.1")
Sheet = Book.Sheets("输出2-制造 分工厂分车系分月")
cs.Clear
For c As Integer = 0 To Tables("表A").Cols.Count - 1
cs.Add(Tables("表A").Cols(c).Name)
Next
For r As Integer = 0 To br - 1
Dim RW As Row = Tables("表A").Rows(r)
For c As Integer = 0 To cs.Count - 1
Sheet(r + 2,c).Value = RW(cs(c))
Next
Sheet(r + 2,cs.Count).Formula = "=SUM(F" & r + 3 & ":Q" & r + 3 & ")"
Next
br = Tables("表A").Rows.Count
MessageBox.Show("2.2")
For r As Integer = 0 To gcs.Count - 1
MessageBox.Show(r & "/" & gcs.Count - 1)
Sheet(br + r + 2, 2).Value = gcs(r)
Sheet(br + r + 2, 3).Value = "合计"
For c As Integer = 5 To 16
Dim F As String = "=SUMIF(C3:C" & br + 2 & ",C" & br + r + 2 & _
",INDIRECT(ADDRESS(3," & c + 1 & ")&"":""&ADDRESS(" & br + 2 & "," & c + 1 & ")))"
\'MessageBox.Show(F)
Sheet(br + r + 2,c).Formula = F
Next
Dim G As String = "=SUM(F" & br + r + 3 & ":Q" & br + r + 3 & ")"
MessageBox.Show(G)
Sheet(br + r + 2,17).Formula = G
\'MessageBox.Show("2.26")
Next
。。。。。。
红色部分报错，
详细错误信息：
调用的目标发生了异常。
未将对象引用设置到对象的实例。
[此贴子已经被作者于2022/4/21 10:59:33编辑过]
|