如何通过 VB.net 更改Excel工作表中的数字格式

使用 Excel 自动化设置范围的数字格式，如下所示，我正在使用您的值显示。如果我在没有设置数字格式的情况下插入该值，则结果与您得到的结果相同，但使用数字格式，数字格式正确。

在这里，我正在使用可执行文件文件夹中的Excel文件，您将进行调整以指向现有或新创建的Excel文件。

Dim FileName As String = IO.Path.Combine(Application.StartupPath, "Formatting.xlsx")
Dim value As Long = 763040059412
OpenExcelDoFormat(FileName, "", "Sheet1", "G2", 763040059412)

支持代码，我通过process.start打开文件以立即看到结果。请注意，参数 SaveFileName 在这里没有任何意义，因为我调整了我为另一个问题所做的另一个演示。

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module OpenWorkSheets4
    Public Sub OpenExcelDoFormat(
        ByVal OpenFileName As String,
        ByVal SaveFileName As String,
        ByVal SheetName As String,
        ByVal CellAddress As String,
        ByVal NewCellValue As Long)
        If IO.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
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
            If Proceed Then
                xlCells = xlWorkSheet.Range(CellAddress)
                xlCells.Select()
                xlCells.NumberFormat = "0"
                xlCells.Value = NewCellValue.ToString
                Dim xlColumns As Excel.Range = Nothing
                xlColumns = xlCells.EntireColumn
                xlColumns.AutoFit()
                Runtime.InteropServices.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)
            Process.Start(OpenFileName)
        Else
            MessageBox.Show("'" & OpenFileName & "' not located. Try one of the write examples first.")
        End If
    End Sub
    Private Sub ReleaseComObject(ByVal obj As Object)
        Try
            If obj IsNot Nothing Then
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            End If
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub
End Module

转到单元格或范围级别并应用NumerFormat = "#,##0.00"