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)

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

End Sub

VB.NET 通常用法使用笔记

相关推荐