VB.NET 通常用法使用笔记

● '解决直接对DataTable排序无效的问题
    '例如：要对dtOrderData进行排序，直接用.DefaultView.Sort的话排序是无效的。
     '无效用法   
      dtOrderData.DefaultView.Sort = "ID_ORD Asc"
      '有效用法
     Dim dvSort As New DataView 
      dvSort = dtOrderData.DefaultView
      dvSort.Sort = "ID_ORD Asc"
      dtOrderData = dvSort.ToTable
●  '在DataTable中筛选数据()
    Dim strFilter As String
    Dim strSearchVal As String = "查询值"
      strFilter = "字段名 = '" + strSearchVal  + "'"
    '克隆 dtInfo (DataTable) 的结构
    Dim dtFilterTable As DataTable = dtInfo.Clone
    '将[字段名]为'查询值'的数据都筛选出来
    For Each foundRows As DataRow In dtInfo.Select(strFilter)
        dtFilterTable.ImportRow(foundRows)
    Next
●  '在DataTable中取唯一值;相当于SQL文的DISTINCT效果
    Dim dtFilterDistinct As New DataTable
    dtFilterDistinct = dtInfo.DefaultView.ToTable(True, New String() {"ID", "NAME"})
●  '合并单元格,居中
    xlSheet.Cells.Range("B1:C1").Merge()
    xlSheet.Cells.Range("B1").Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
●  '补粗线框
    xlSheet.Range("A1:E1").BorderAround(, Excel.XlBorderWeight.xlMedium)
●  '画细线框(详细)
    With xlApp.ActiveSheet.Range("A1:E1").Borders(Excel.XlBordersIndex.xlEdgeTop)
        .LineStyle = Excel.XlLineStyle.xlContinuous
        .Weight = Excel.XlBorderWeight.xlThin  'xlMedium
        .ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic 'xlAutomatic
    End With
●  '增加一行
    xlSheet.Rows("10:10").Insert(Shift:=Excel.XlDirection.xlDown)
●  '删除有数据的最后一行
    xlSheet.Rows(xlSheet.UsedRange.Rows.Count).Delete()
●  '加快写Excel数据速度:自动重新计算工作表(写数据前关)
    xlSheet.EnableCalculation = False
    '加快写Excel数据速度:自动重新计算工作表(保存数据前开)
      xlSheet.EnableCalculation = True

●  ''' <summary>
    ''' 将数字列转换为字母
    ''' </summary>
    ''' <param name="inCol">传入列数</param>
    ''' <returns>返回传入列数的所在列字母</returns>
    Function GetNoToAbcCol(ByVal intCol As Integer) As String
        intCol –= 1
        Dim chars As New List(Of String)
        Do
            If (chars.Count > 0) Then
                intCol –= 1
            End If
            chars.Insert(0, Chr(intCol Mod 26 + Asc("A")))
            intCol = intCol \ 26
        Loop While intCol > 0
        Return String.Join(String.Empty, chars.ToArray)
    End Function

●  ''' <summary>
    '''将字母列转换为数字
    ''' </summary>
    ''' <param name="strRangeCol">传入字母列</param>
    ''' <returns>返回传入字母列的所在列数字</returns>
    Function GetAbcToNoCol(ByVal strRangeCol As String) As Integer
        Dim returnVal As Integer = –1
        strRangeCol = UCase(strRangeCol)
        If strRangeCol.Length = 1 Then
            returnVal = Asc(strRangeCol) Mod 64
            Return returnVal
        ElseIf strRangeCol.Length = 2 Then
            returnVal = (Asc(Left(strRangeCol, 1)) Mod 64) * 26 _
                       + Asc(Right(strRangeCol, 1)) Mod 64
            Return returnVal
        Else
        End If
    End Function 

●  ''' <summary>
    '''以星期天为界，算每个月有几周，从几号到几号。
    ''' </summary>
    ''' <param name="dateCurrentYMD">当前月</param>
    ''' <returns></returns>

    Private Sub subWriteWeekTitle(ByVal dateCurrentYMD As Date)

        Dim arrMonth As New ArrayList

        Dim strStart As String = String.Empty

        Dim strEnd As String = String.Empty

        Dim y As Integer

        Dim m As Integer

        Dim iDays As Integer

        Dim FirstDayOfMonth As Date

        Dim FirstDayOfWeek As Date

        Dim iWeeks As Integer = 1

 

        y = CInt(Format(dateCurrentYMD, "yyyy"))

        m = CInt(Format(dateCurrentYMD, "MM"))

        iDays = Date.DaysInMonth(y, m)

        FirstDayOfMonth = Date.Parse(CStr(y) + "-" + CStr(m) + "-1")

        FirstDayOfWeek = FirstDayOfMonth

  '本月的场合

        While (FirstDayOfWeek.Month = m)

            Dim LastDayOfWeek As Date

            strStart = "第" + CStr(iWeeks) + "周:" + getFormatM(FirstDayOfWeek) + "-"

 

            If (FirstDayOfWeek.Day + 7 > iDays) Then

                LastDayOfWeek = FirstDayOfWeek.AddDays(iDays - FirstDayOfWeek.Day)

                'Debug.Print(LastDayOfWeek.ToString("MM/dd"))

                strEnd = LastDayOfWeek.ToString("dd")

                arrMonth.Add(strStart + strEnd)

                Exit While

            Else

                LastDayOfWeek = FirstDayOfWeek

            End If

            '循环一周天

            For iPlus As Integer = 1 To 7

                '以星期天为界

                If LastDayOfWeek.DayOfWeek = DayOfWeek.Sunday And LastDayOfWeek.Month = m Then

                    'Debug.Print(LastDayOfWeek.ToString("MM/dd"))

                    strEnd = LastDayOfWeek.ToString("dd")

                    Exit For

                ElseIf LastDayOfWeek.Month = m + 1 Then

                    LastDayOfWeek = LastDayOfWeek.AddDays(-1)

                    'Debug.Print(LastDayOfWeek.ToString("MM/dd"))

                    strEnd = LastDayOfWeek.ToString("dd")

                    Exit For

                End If

                LastDayOfWeek = LastDayOfWeek.AddDays(1)

            Next

            iWeeks += 1

            FirstDayOfWeek = LastDayOfWeek.AddDays(1)

 

            arrMonth.Add(strStart + strEnd)

        End While

 

        For iWeek As Integer = 0 To arrMonth.Count - 1

            Debug.Print(arrMonth.Item(iWeek))

        Next

End Sub

转载于:https://www.cnblogs.com/blucy/archive/2011/09/14/2176637.html