Try
Dim dataSet As New DataSet()
Dim strCmd As String = ""
Dim str_item As String
str_item = " SUM(S.P0_V1) AS P0_V1," & _
" CAST(ROUND(SUM(DOUBLE(COALESCE(S.P0_V1_F/100,0))),2) AS DECIMAL(10,2)) AS P0_V1_F," & _
" SUM(S.P0_V2) AS P0_V2," & _
" CAST(ROUND(SUM(DOUBLE(COALESCE(S.P0_V2_F/100,0))),2) AS DECIMAL(10,2)) AS P0_V2_F," & _
" SUM(S.P0_V3) AS P0_V3," & _
" CAST(ROUND(SUM(DOUBLE(COALESCE(S.P0_V3_F/100,0))),2) AS DECIMAL(10,2)) AS P0_V3_F," & _
" SUM(S.P0_V4) AS P0_V4," & _
" CAST(ROUND(SUM(DOUBLE(COALESCE(S.P0_V4_F/100,0))),2) AS DECIMAL(10,2)) AS P0_V4_F," & _
" SUM(S.P0_V5) AS P0_V5," & _
" CAST(ROUND(SUM(DOUBLE(COALESCE(S.P0_V5_F/100,0))),2) AS DECIMAL(10,2)) AS P0_V5_F," & _
" SUM(S.P6_V1) AS P6_V1,SUM(S.P6_V2) AS P6_V2,SUM(S.P6_V3) AS P6_V3," & _
" SUM(S.P6_V4) AS P6_V4,SUM(S.P6_V5) AS P6_V5"
If card_net_name = "全部发行方(含测试)" Then
str_item = " SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE COALESCE(P0_V1,0) END) AS P0_V1," & _
" CAST(SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE ROUND(COALESCE(P0_V1_F/100,0),2) END) AS DECIMAL(10,2)) AS P0_V1_F," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE COALESCE(P0_V2,0) END) AS P0_V2," & _
" CAST(SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE ROUND(COALESCE(P0_V2_F/100,0),2) END) AS DECIMAL(10,2)) AS P0_V2_F," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE COALESCE(P0_V3,0) END) AS P0_V3," & _
" CAST(SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE ROUND(COALESCE(P0_V3_F/100,0),2) END) AS DECIMAL(10,2)) AS P0_V3_F," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE COALESCE(P0_V4,0) END) AS P0_V4," & _
" CAST(SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE ROUND(COALESCE(P0_V4_F/100,0),2) END) AS DECIMAL(10,2)) AS P0_V4_F," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE COALESCE(P0_V5,0) END) AS P0_V5," & _
" CAST(SUM(CASE CARD_NET_ID WHEN '1108' THEN 0 ELSE ROUND(COALESCE(P0_V5_F/100,0),2) END) AS DECIMAL(10,2)) AS P0_V5_F," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN COALESCE(P0_V1,0) ELSE 0 END + COALESCE(P6_V1,0)) AS P6_V1," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN COALESCE(P0_V2,0) ELSE 0 END + COALESCE(P6_V2,0)) AS P6_V2," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN COALESCE(P0_V3,0) ELSE 0 END + COALESCE(P6_V3,0)) AS P6_V3," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN COALESCE(P0_V4,0) ELSE 0 END + COALESCE(P6_V4,0)) AS P6_V4," & _
" SUM(CASE CARD_NET_ID WHEN '1108' THEN COALESCE(P0_V5,0) ELSE 0 END + COALESCE(P6_V5,0)) AS P6_V5"
End If
Dim cmdString As String
'收费员使用A70_USERS中的User_ID与Operator_ID关联，广场用A36_ORGAN与PLAZA_ID关联
cmdString = " SELECT U.USER_NUM,U.USER_NAME,A.ORGAN_NAME AS ORGAN_NAME," & _
" CASE S.PASSED_TYPE WHEN 1 THEN 'MTC' WHEN 0 THEN 'ETC' ELSE '其他' END AS PASSED_TYPE," & str_item & _
" FROM (" & _
" SELECT " + CreateSQL.sql + " FROM ECDBA.RPT_VEHICLE_PER_SHIFT " & _
" WHERE NEWSHIFT_DATE= '" & shiftDate & "'" & _
" AND NEWSHIFT_ID=" & shiftID & _
" AND PROCESS_RESULT IN (0,2)" & _
" AND ENTRY_EXIT=1 " & _
" AND CARD_NET_ID IN (" & card_net_id & ")" & _
" ) AS S" & _
" LEFT JOIN DB2INST1.A70_USERS AS U ON U.USER_ID=S.OPERATOR_ID" & _
" LEFT JOIN DB2INST1.A36_ORGAN AS A ON A.ORGAN_ID=S.PLAZAID AND A.ORGAN_DUTY=2" & _
" LEFT JOIN DB2INST1.A36_ORGAN AS B ON A.VERIFYSUPER_ID=B.ORGAN_ID" & _
" WHERE B.VERIFYSUPER_ID=" & Me.ddlPartCenter.SelectedValue
cmdString = cmdString & " GROUP BY U.USER_NAME,U.USER_NUM,A.ORGAN_NAME,S.PASSED_TYPE " & _
" ORDER BY U.USER_NAME,U.USER_NUM,A.ORGAN_NAME,S.PASSED_TYPE "
dataSet = db2con.GetDataSet(cmdString)
Me.NoPay(excelHelper, shiftDate, shiftID, sheetIndex, card_net_id, db2con, 10)
Me.Untreated(excelHelper, shiftDate, shiftID, sheetIndex, card_net_id, db2con, 11)
'扩充单位格
If dataSet.Tables(0).Rows.Count > 2 Then
For index As Integer = 3 To dataSet.Tables(0).Rows.Count
excelHelper.WorkSheet.Rows(7).copy()
excelHelper.WorkSheet.Rows(8).Insert()
Next
ElseIf dataSet.Tables(0).Rows.Count = 1 Then
excelHelper.WorkSheet.Rows(8).Delete()
End If
'修改目前的赋值逻辑
'创建一个新的数据行当作临时变量，用来保存当前行的数据信息
Dim dr As System.Data.DataRow = dataSet.Tables(0).NewRow()
'创建一个新的数据行当作临时变量，用来保存下一行的数据信息
Dim drNext As System.Data.DataRow = dataSet.Tables(0).NewRow()
'定义此变量用来保存员工工号和员工姓名合并后的单元个的行号
Dim rowIndex As Integer = 0
'定义此变量用来保存员工收费广场的单元格的行号
Dim organRowIndex As Integer = 0
'初始化首行的值
dr = dataSet.Tables(0).Rows(0)
For i As Integer = 0 To dataSet.Tables(0).Rows.Count - 1 Step 1
'取得第二行的数据信息
drNext = dataSet.Tables(0).Rows(i)
'如果当前行和下一行数据信息中的员工工号相同的情况，进行单元格合并
If (dr("USER_NUM").ToString().Trim() = drNext("USER_NUM").ToString().Trim()) Then
'员工工号合并
excelHelper.WorkSheet.Range(Cell1:="A" & 7 + rowIndex, Cell2:="A" & 7 + i).Merge()
'员工姓名合并
excelHelper.WorkSheet.Range(Cell1:="B" & 7 + rowIndex, Cell2:="B" & 7 + i).Merge()
'判断广场是否相同,如果相同时的处理方式
If (dr("ORGAN_NAME").ToString().Trim() = drNext("ORGAN_NAME").ToString().Trim()) Then
excelHelper.WorkSheet.Range(Cell1:="C" & 7 + organRowIndex, Cell2:="C" & 7 + i).Merge()
Else
organRowIndex = organRowIndex + i
End If
excelHelper.WorkSheet.Cells(7 + organRowIndex, 3) = drNext("ORGAN_NAME")
Else
'如果不同时进行的操作如下更改rowIndex的数值
rowIndex = rowIndex + i
excelHelper.WorkSheet.Cells(7 + rowIndex, 3) = drNext("ORGAN_NAME")
End If
'直接进行EXCEL单元格赋值操作
excelHelper.WorkSheet.Cells(7 + rowIndex, 1) = drNext("USER_NUM")
excelHelper.WorkSheet.Cells(7 + rowIndex, 2) = drNext("USER_NAME")
excelHelper.WorkSheet.Cells(7 + i, 4) = drNext("PASSED_TYPE")
excelHelper.WorkSheet.Cells(7 + i, 5) = Convert.ToInt64(drNext("P0_V1"))
excelHelper.WorkSheet.Cells(7 + i, 6) = Convert.ToDouble(drNext("P0_V1_F"))
excelHelper.WorkSheet.Cells(7 + i, 7) = Convert.ToInt64(drNext("P0_V2"))
excelHelper.WorkSheet.Cells(7 + i, 8) = Convert.ToDouble(drNext("P0_V2_F"))
excelHelper.WorkSheet.Cells(7 + i, 9) = Convert.ToInt64(drNext("P0_V3"))
excelHelper.WorkSheet.Cells(7 + i, 10) = Convert.ToDouble(drNext("P0_V3_F"))
excelHelper.WorkSheet.Cells(7 + i, 11) = Convert.ToInt64(drNext("P0_V4"))
excelHelper.WorkSheet.Cells(7 + i, 12) = Convert.ToDouble(drNext("P0_V4_F"))
excelHelper.WorkSheet.Cells(7 + i, 13) = Convert.ToInt64(drNext("P0_V5"))
excelHelper.WorkSheet.Cells(7 + i, 14) = Convert.ToDouble(drNext("P0_V5_F"))
excelHelper.WorkSheet.Cells(7 + i, 17) = Convert.ToInt64(drNext("P6_V1"))
excelHelper.WorkSheet.Cells(7 + i, 18) = Convert.ToInt64(drNext("P6_V2"))
excelHelper.WorkSheet.Cells(7 + i, 19) = Convert.ToInt64(drNext("P6_V3"))
excelHelper.WorkSheet.Cells(7 + i, 20) = Convert.ToInt64(drNext("P6_V4"))
excelHelper.WorkSheet.Cells(7 + i, 21) = Convert.ToInt64(drNext("P6_V5"))
dr = drNext
Next
此处比较关键的地方是：分组完成之后一定要指定排序规则才可以。