Public Class Form1
    Private Enum CED
        PromotionType = 0            '活动类型 
        Index = 1                    '序号
        CodeC = 2                    '款号合
        Image = 3                    '图片
        SalePrice = 4                '零售价
        Season = 5                   '季度
        Group = 6                    '波段
        Item = 7                     '品类
        ItemPlanRank = 8             '商品计划等级
        ZSPK = 9                     'ZSPK
        PromotionType2 = 10          '活动类型
        DetailDataStartCol = 11 '明细数据开始列 20190131这种复杂度的画面，代码量一千八百多行，分分钟加行加列的扩展，谁来挑战？
    End Enum
    Private Enum CEG
        SaleRank = 0                    '销售排名
        IntervalSaleQty = 1             '期间销售量
        WeekSaleQty = 2                 '周销量
        TotalSaleQty = 3                '累计销量
        SingleShopMaxSaleQty = 4        '单店最大销量
        SaleQtyPercent = 5              '销量占比
        SaleAmountPercent = 6           '销售额占比
        SaleShopCount = 7               '销售店铺数
        ShopAvgSaleQty = 8              '店均销售
        ShopDX = 9                      '店铺动销率
        KC = 10                         '库存
        TotalKCZZ = 11                  '总库存周转
        KCShopCount = 12                '库存店铺数
        ShopDeepth = 13                 '店存深度
        PHL = 14                        '铺货率
        IntervalDiscount = 15           '期间折扣率
        IntervalSaleAmount = 16         '期间销售额
        IntervalTagAmount = 17          '期间吊牌额
        LastWeekSaleQty = 18            '上周销量
        LastWeekSaleRank = 19           '上周销量排名
        WeekSaleQtyHB = 20              '周销量环比
        WeekRankRise = 21               '本周上升名次
    End Enum
    Private FNum0 As Integer = 1
    Private FNum1 As Integer = 2
    Private FNum2 As Integer = 3
    Private AllShopCount As Integer = 0

    Private SCDic As New Dictionary(Of String, Dictionary(Of String, String))
    Dim SeasonDic As New Dictionary(Of String, String)
    Dim ColKeyDic As New Dictionary(Of String, Dictionary(Of Integer, Integer()))
    '列头
    Dim CL As New List(Of String)
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

        SCDic = GetColumnHeadTitle()
        SeasonDic = GetSeasonDic()
        ColKeyDic = GetColKeyDic()
        cl.AddRange({"销售排名", "期间销售量", "周销量", "累计销量", "单店最大销量", "销量占比", "销售额占比", "销售店铺数", "店均销售", "店铺动销率", "库存", "总库存周转", "库存店铺数", "店存深度", "铺货率", "期间折扣率", "期间销售额", "期间吊牌额", "上周销量", "上周销量排名", "周销量环比", "本周上升名次"})
        SetTitleHeadTextT(sh, "m_shop_type")

        spr.SuspendLayout()
        sh.RowCount = 200
        For i As Integer = 1 To sh.RowCount - 1
            For j As Integer = 0 To sh.ColumnCount - 1
                sh.Cells(i, j).Value = "test i" & i.ToString() & ":j" & j.ToString()
            Next
        Next
        sh.Cells(0, CED.PromotionType).Value = "所有款合计"
        '单元格合并后，移动列会抛异常
        sh.Cells(0, CED.PromotionType, 0, CED.PromotionType2).ColumnSpan = 11
        spr.ResumeLayout()
    End Sub
    ''' <summary>
    ''' 获取列头标题 类型  [NZB - 南中北]  [AREA1 - 七大区1]  [AREA2 - 七大区2]  [SHOPRANK - 店铺等级]  
    ''' </summary>
    ''' <returns></returns>
    Private Function GetColumnHeadTitle() As Dictionary(Of String, Dictionary(Of String, String))
        Dim res As New Dictionary(Of String, Dictionary(Of String, String))

        Dim value As New Dictionary(Of String, String)
        value.Add(-999, "总")
        value.Add("01", "南")
        value.Add("02", "中")
        value.Add("03", "北")
        res.Add("m_shop_nzb", value)

        value = New Dictionary(Of String, String)
        value.Add(-999, "总")
        value.Add("01", "七大区1")
        value.Add("02", "七大区2")
        value.Add("03", "七大区3")
        value.Add("04", "七大区4")
        value.Add("05", "七大区5")
        value.Add("06", "七大区6")
        value.Add("07", "七大区7")
        res.Add("m_shop_area", value)

        value = New Dictionary(Of String, String)
        value.Add(-999, "总")
        value.Add("01", "代理")
        value.Add("02", "联营")
        value.Add("03", "直营")
        value.Add("99", "未设定")

        res.Add("m_shop_type", value)


        Return res
    End Function
    Private Function GetSeasonDic() As Dictionary(Of String, String)
        Dim res As New Dictionary(Of String, String)

        res.Add("1", "春")
        res.Add("2", "夏")
        res.Add("3", "秋")
        res.Add("4", "冬")
        res.Add("5", "四季")
        res.Add("9", "未设置")

        Return res
    End Function
    Function GetColKeyDic() As Dictionary(Of String, Dictionary(Of Integer, Integer()))
        Dim res As New Dictionary(Of String, Dictionary(Of Integer, Integer()))
        For Each Type As String In SCDic.Keys
            Dim node As New Dictionary(Of Integer, Integer())
            For Num As Integer = CEG.SaleRank To CEG.WeekRankRise
                Dim list(2) As Integer
                If Num < CEG.KC Then
                    list(0) = (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    If SCDic(Type).Count > 0 Then
                        list(1) = (CED.DetailDataStartCol + (Num + 1) * SCDic(Type).Count - 1)
                    Else
                        list(1) = (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    End If
                    node.Add(Num, list)
                End If
                If Num = CEG.KC Then
                    list(0) = (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    If SCDic(Type).Count > 0 Then
                        list(1) = FNum1 + (CED.DetailDataStartCol + (Num + 1) * SCDic(Type).Count - 1)
                    Else
                        list(1) = FNum1 + (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    End If
                    node.Add(Num, list)
                End If
                If Num = CEG.TotalKCZZ Then
                    list(0) = FNum1 + (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    If SCDic(Type).Count > 0 Then
                        list(1) = FNum2 + (CED.DetailDataStartCol + (Num + 1) * SCDic(Type).Count - 1)
                    Else
                        list(1) = FNum2 + (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    End If
                    node.Add(Num, list)
                End If
                If Num > CEG.TotalKCZZ Then
                    list(0) = FNum2 + (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    If SCDic(Type).Count > 0 Then
                        list(1) = FNum2 + (CED.DetailDataStartCol + (Num + 1) * SCDic(Type).Count - 1)
                    Else
                        list(1) = FNum2 + (CED.DetailDataStartCol + Num * SCDic(Type).Count)
                    End If
                    node.Add(Num, list)
                End If
            Next
            res.Add(Type, node)
        Next
        Return res
    End Function

    ''' <summary>
    ''' 设置合计列头
    ''' </summary>
    ''' <param name="sheet">Sheet</param>
    ''' <param name="Type">类别</param>
    ''' <remarks></remarks>
    Private Sub SetTitleHeadTextT(ByVal sheet As FarPoint.Win.Spread.SheetView, ByVal Type As String)
        Dim CTCount As Integer = SCDic(Type).Count
        Dim CT As New SortedList
        sheet.ColumnHeader.RowCount = 2
        sheet.ColumnHeader.Columns.Count = FNum2 + CED.DetailDataStartCol + CL.Count * IIf(SCDic(Type).Count = 0, 1, SCDic(Type).Count)
        sheet.RowHeader.Visible = False
        Dim Width1 As Integer = 80
        Dim Width2 As Integer = 120
        Dim Width3 As Integer = 40
        Dim Width4 As Integer = 90
        Dim Width5 As Integer = 45
        Dim count As Integer = 0
        Dim FKNum As Integer = 0
        For Each key As String In CL
            CT.Add(LabelText(key) & "@" & count, FKNum + CED.PromotionType2 + count * CTCount + 1)
            If (key = "库存") Then
                FKNum += 2
            ElseIf (key = "总库存周转") Then
                FKNum += 1
            End If
            count += 1
        Next
        sheet.ColumnCount = FNum2 + CED.DetailDataStartCol + CL.Count * IIf(SCDic(Type).Count = 0, 1, SCDic(Type).Count)

        sheet.ColumnHeader.Cells(0, CED.PromotionType).Text = LabelText("活动类型")
        sheet.ColumnHeader.Cells(0, CED.Index).Text = LabelText("序号")
        sheet.ColumnHeader.Cells(0, CED.CodeC).Text = LabelText("期间销售排名")
        sheet.ColumnHeader.Cells(0, CED.CodeC).ColumnSpan = CED.PromotionType2 - CED.CodeC + 1
        sheet.ColumnHeader.Cells(1, CED.PromotionType).Text = LabelText("活动类型")
        sheet.ColumnHeader.Cells(1, CED.Index).Text = LabelText("总")
        sheet.ColumnHeader.Cells(1, CED.CodeC).Text = LabelText("排名用款号合")
        sheet.ColumnHeader.Cells(1, CED.Image).Text = LabelText("图片")
        sheet.ColumnHeader.Cells(1, CED.SalePrice).Text = LabelText("零售价")
        sheet.ColumnHeader.Cells(1, CED.Season).Text = LabelText("季度")
        sheet.ColumnHeader.Cells(1, CED.Group).Text = LabelText("波段")
        sheet.ColumnHeader.Cells(1, CED.Item).Text = LabelText("品类")
        sheet.ColumnHeader.Cells(1, CED.ItemPlanRank).Text = LabelText("商品计划等级")
        sheet.ColumnHeader.Cells(1, CED.ZSPK).Text = LabelText("ZSPK")
        sheet.ColumnHeader.Cells(1, CED.PromotionType2).Text = LabelText("活动类型")

        For Each key1 As String In CT.Keys
            sheet.ColumnHeader.Cells(0, CT(key1)).Text = LabelText(key1.Split("@")(0))
            If LabelText(key1.Split("@")(0)) = "库存" Then
                sheet.ColumnHeader.Cells(0, CT(key1)).ColumnSpan = CTCount + FNum1
            ElseIf LabelText(key1.Split("@")(0)) = "总库存周转" Then
                sheet.ColumnHeader.Cells(0, CT(key1)).ColumnSpan = CTCount + FNum0
            Else
                sheet.ColumnHeader.Cells(0, CT(key1)).ColumnSpan = CTCount
            End If
            Dim col As Integer = 0
            For Each key2 As String In SCDic(Type).Keys
                If key2 = "-999" And (LabelText(key1.Split("@")(0)) = "库存") Then
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = "总库存"
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = key2
                    sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                    col += 1
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = "总店存"
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = key2
                    sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                ElseIf key2 = "-999" And (LabelText(key1.Split("@")(0)) = "总库存周转") Then
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = "总店存周转"
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = key2
                    sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                Else
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = LabelText(SCDic(Type)(key2))
                    sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = key2
                    sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                End If
                col += 1
            Next
            If (LabelText(key1.Split("@")(0)) = "库存") Then
                sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = LabelText("仓存")
                sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = ""
                sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                col += 1
            End If
            If (LabelText(key1.Split("@")(0)) = "总库存周转") Then
                sheet.ColumnHeader.Cells(1, CT(key1) + col).Text = LabelText("含仓存周转")
                sheet.ColumnHeader.Cells(1, CT(key1) + col).Tag = ""
                sheet.ColumnHeader.Columns(CT(key1) + col).Width = Width1
                col += 1
            End If

        Next
        sheet.ColumnHeader.Columns(CED.Index).Width = Width3
        sheet.ColumnHeader.Columns(CED.Group).Width = Width5
        sheet.ColumnHeader.Columns(CED.ZSPK).Width = Width5
        sheet.ColumnHeader.Columns(CED.CodeC).Width = Width4
        sheet.ColumnHeader.Columns(CED.ItemPlanRank).Width = Width4
    End Sub
    Public Function LabelText(ByVal Text As String) As String
        LabelText = Text
        If Text Is Nothing Then
            Return Text
        End If

        Return Text
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        spr.SuspendLayout()

        Try

            spr.ActiveSheet.MoveColumn(1, 0, True)
        Catch ex As Exception

        End Try

        spr.ResumeLayout()
    End Sub
End Class
