呃,为了一个样式,全部代码都要改了。用vba去做
Dim cmd As New SQLCommand '定义一个SQL命令
Dim t As DataTable '定义一个数据表变量
Dim Count As Integer = 0
cmd.CommandText = "Select * From {表三} Where 零部件编号 like '" & Tables("表一").Current("任务单编号") & "-" & "%' "
t = cmd.ExecuteReader() '生成一个临时表
Dim cmd1 As New SQLCommand '定义一个SQL命令
Dim t1 As DataTable '定义一个数据表变量
cmd.CommandText = "Select * From {表二} Where 零部件编号 like '" & Tables("表一").Current("任务单编号") & "-" & "%' "
t1 = cmd.ExecuteReader() '生成一个临时表
Dim App As New MSExcel.Application
Dim Wb As MSExcel.WorkBook = App.WorkBooks.Add
Dim Ws As MSExcel.WorkSheet = Wb.WorkSheets(1)
ws.cells.rowheight = 100
ws.cells.Columnwidth = 20
ws.cells(1, 1).Value ="零部件编号"
ws.cells(1, 2).Value ="零部件名称"
Dim d_start As Date = Tables("表一").Current("计划开始日期")
Dim cs As Integer = 3
Do While d_start <= Tables("表一").Current("计划结束日期")
If d_start.DayOfWeek = 0 Then
ws.cells(1, cs).Value = "周日"
Else
ws.cells(1, cs).Value = d_start & " "
End If
cs += 1
d_start = d_start.AddDays(1)
Loop
Dim strs As List(of String) = t.GetValues("零部件编号")
For i As Integer=0 To strs.count-1
ws.cells(i+2, 1).Value = " " & strs(i) & " "
Dim path As String = ProjectPath & "Attachments/" & t1.Find("零部件编号='" & strs(i) & "'")("图片")
If FileSys.FileExists(path) Then
ws.cells(i+2, 2).Select()
ws.Shapes.AddPicture(path, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue,0, 0, 100, 80)
End If
d_start = Tables("表一").Current("计划开始日期")
cs = 3
Do While d_start <= Tables("表一").Current("计划结束日期")
Dim drlist As List(of DataRow) = t.Select("零部件编号='" & strs(i) & "' and 实际完成日期=#" & d_start & "#")
Dim ss As String=""
Dim idxs As String = "-1,"
If drlist.count>0 Then
For Each dr As DataRow In drlist
ss=ss & "," & dr("加工内容")
idxs &= dr("_Identify") & ","
Next
End If
Dim idx As Integer = ss.length
drlist = t.Select("零部件编号='" & strs(i) & "' and 计划加工日期=#" & d_start & "# and _Identify not in (" & idxs.trim(",") & ")")
If drlist.count>0 Then
For Each dr As DataRow In drlist
ss=ss & "," & dr("加工内容")
Next
End If
ws.cells(i+2, cs).Value = ss.trim(",")
ws.cells(i+2, cs).Characters(0,idx).font.ColorIndex = 3
ws.cells(i+2, cs).Characters(idx).font.ColorIndex = 1
cs += 1
d_start = d_start.AddDays(1)
Loop
Next
App.Visible = True