● '解决直接对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 ●
'''
''' 将数字列转换为字母 ''' ''' 传入列数
'''
返回传入列数的所在列字母 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 ●
'''
'''将字母列转换为数字 ''' ''' 传入字母列
'''
返回传入字母列的所在列数字 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
●
'''
'''以星期天为界，算每个月有几周，从几号到几号。 ''' ''' 当前月
'''
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
Tags:
延伸阅读
最新评论