在VB中获取Excel单元格样式 [英] Get Excel cell style in VB

问题描述

如何使用VB.NET获取Excel电子表格的各种单元格属性

How can I get the various cell properties of an excel spreadsheet using VB.NET

我的工作表包含各种单元格，这些单元格具有不同的字体类型+颜色不同的填充等

I have sheet with various cells that have different font type + color different fill etc,

当我尝试使用"XL_Sheet.Cells.Item(row，column).style"获取该信息时，我似乎总是为所有单元格返回相同的值.  我可以获取单元格值的内容，而不是属性.

When I try to get that information using "XL_Sheet.Cells.Item(row, column).style" I seems always return the same values for all cells.   I can get the content of the cell value but not the properties.

谢谢

推荐答案

你好，

我已经有一段时间没有这样做了，但是下面显示的内容应该可以帮助您.我将单个单元格的颜色设置为红色，然后要求输入它的颜色.事情是颜色将返回为3，这是红色的翻译，具体取决于 颜色.简而言之，它展示了如何获取单个目标细胞的细胞颜色.

I have not done this for a while but what is shown below should get you going. I set the color of a single cell to red then in return ask for it's color. The thing is the color will come back as 3 which is a translation for red as per the link within the color.  So in short it shows how to get the cell color of a single targeted cell.

我使用的是早期绑定，而不是后期绑定的支持者，因此，如果您使用的是后期绑定(因为我所做的所有工作，客户始终拥有与我相同的Excel版本)，则需要进行调整.

I'm using early binding, not a fan of late binding so if you are using late binding (because all work I've done the customer always had the same version of Excel as I did) you will need to adapt.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Imports System.IO

Module OpenWorkSheetsSamples
    Private Sub ReleaseComObject(ByVal excelObject As Object)
        Try
            If excelObject IsNot Nothing Then
                Marshal.ReleaseComObject(excelObject)
                excelObject = Nothing
            End If
        Catch ex As Exception
            excelObject = Nothing
        End Try
    End Sub
    Public Sub OpenExcelDoFormat(
        ByVal OpenFileName As String,
        ByVal SheetName As String,
        ByVal CellAddress As String,
        ByVal NewCellValue As Decimal)


        If File.Exists(OpenFileName) Then

            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False

            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(OpenFileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then

                xlCells = xlWorkSheet.Range(CellAddress)
                xlCells.Select()

                'xlCells.AutoFilter 
                xlCells.NumberFormat = "

#，###.##" xlCells.Value = NewCellValue.ToString Dim字体为Excel.Font = xlCells.Font font.Color =颜色.红色 font.Bold = True 'http://dmcritchie.mvps.org/excel/colors.htm 将颜色变暗为Color = ColorTranslator.FromOle(CInt(font.ColorIndex)) 将Color1变暗为Color = Color.FromArgb(3) Console.WriteLine(在此中断，上方为考试变量") 元帅.FinalReleaseComObject(字体) 字体= Nothing Dim xlColumns As Excel.Range = Nothing xlColumns = xlCells.EntireColumn xlColumns.AutoFit() 元帅.FinalReleaseComObject(xlColumns) xlColumns =没什么 别的 MessageBox.Show(SheetName&未找到.") 万一 xlWorkSheet.SaveAs(OpenFileName) xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() ReleaseComObject(xlCells) ReleaseComObject(xlWorkSheets) ReleaseComObject(xlWorkSheet) ReleaseComObject(xlWorkBook) ReleaseComObject(xlWorkBooks) ReleaseComObject(xlApp) 别的 MessageBox.Show('"& OpenFileName&未找到.请首先尝试其中一个写示例.") 万一 结束子 终端模块

#,###.##" xlCells.Value = NewCellValue.ToString Dim font As Excel.Font = xlCells.Font font.Color = Color.Red font.Bold = True 'http://dmcritchie.mvps.org/excel/colors.htm Dim theColor As Color = ColorTranslator.FromOle(CInt(font.ColorIndex)) Dim theColor1 As Color = Color.FromArgb(3) Console.WriteLine("Break here, examime variable above") Marshal.FinalReleaseComObject(font) font = Nothing Dim xlColumns As Excel.Range = Nothing xlColumns = xlCells.EntireColumn xlColumns.AutoFit() Marshal.FinalReleaseComObject(xlColumns) xlColumns = Nothing Else MessageBox.Show(SheetName & " not found.") End If xlWorkSheet.SaveAs(OpenFileName) xlWorkBook.Close() xlApp.UserControl = True xlApp.Quit() ReleaseComObject(xlCells) ReleaseComObject(xlWorkSheets) ReleaseComObject(xlWorkSheet) ReleaseComObject(xlWorkBook) ReleaseComObject(xlWorkBooks) ReleaseComObject(xlApp) Else MessageBox.Show("'" & OpenFileName & "' not located. Try one of the write examples first.") End If End Sub End Module

这篇关于在VB中获取Excel单元格样式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！