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