Excel开发学习笔记：文件选择控件、查找匹配项、单元格格式及数据有效性

 一个自用的基于excel的小工具。

开发环境基于VSTO，具体配置：visual studio 2010，VB .Net，excel 2007，文档级别的定制程序。

 

 

Private OpenFileDialog1 As New OpenFileDialog  
Private Sub test()  
    OpenFileDialog1.Filter = "cfg file|*.srp.cfg"  
    If OpenFileDialog1.ShowDialog() = DialogResult.OK Then  
        MsgBox(OpenFileDialog1.FileName)  
        MsgBox(OpenFileDialog1.SafeFileName)  
    Else  
        Return  
    End If  
End Sub  
  
'在工作表中查找关键字，返回最后一个匹配项的单元格  
Public Function findLastCell(key As String) As Excel.Range  
        Dim findResult As Excel.Range = Cells.Find(key)  
        Dim lastRecord As Excel.Range = Nothing  
        If (findResult IsNot Nothing) Then  
            Dim firstRecordRow = findResult.Row  
            Do  
                lastRecord = findResult  
                findResult = Cells.FindNext(findResult)  
            Loop Until findResult Is Nothing OrElse findResult.Row = firstRecordRow  
        End If  
        Return lastRecord  
End Function  
  
'设置单元格的格式  
Private Sub test()  
    With Globals.Sheet1  
        Dim sysKpiRow As Excel.Range = .Range(.Cells(0, 1), .Cells(0, 10))  
        sysKpiRow.Interior.ColorIndex = 34 '浅青绿，详见下  
        sysKpiRow.Borders.LineStyle = Excel.XlLineStyle.xlContinuous '显示边框  
        sysKpiRow.Cells(1).value = "系统KPI"  
        sysKpiRow.Columns.AutoFit() '根据内容自动扩张列宽  
  
        'range的offset仍然是等大小的range  
        Dim sysKpiVal As Excel.Range = sysKpiRow.Offset(1, 0)  
        sysKpiVal.Borders.LineStyle = Excel.XlLineStyle.xlContinuous  
        '设置单元格，添加数据有效性，下拉框选择yes和no  
        With sysKpiVal.Validation  
            .Add(Type:=Excel.XlDVType.xlValidateList, AlertStyle:=Excel.XlDVAlertStyle.xlValidAlertStop, _  
             Operator:=Excel.XlFormatConditionOperator.xlBetween, Formula1:="yes,no")  
            .IgnoreBlank = True  
            .InCellDropdown = True  
            .ShowInput = True  
            .ShowError = True  
        End With  
        sysKpiVal.Value = "yes" '统一设置默认值为yes  
    End With  
End Sub 

 完整的colorIndex效果见微软网站：

https://msdn.microsoft.com/en-us/library/cc296089%28v=office.12%29.aspx#xlDiscoveringColorIndex_ColorIndexProperty