根据结果集的数据来动态的合并EXCEL单元格

最新推荐文章于 2020-04-16 11:55:54 发布

meloveself

最新推荐文章于 2020-04-16 11:55:54 发布

阅读量879

点赞数

分类专栏： Asp.net 文章标签： excel dataset integer db2 user string

本文链接：https://blog.csdn.net/meloveself/article/details/6277877

版权

Asp.net 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

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()

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()