vb.net - 将 Excel 中的所有列设置为文本

Question

我正在 VB.NET 上开发一个非常基本的应用程序。

功能:导入一个分隔的TXT文件，并执行“TextToColumn”，Column-AutoFit，将所有列的格式设置为TEXT，最后另存为Excel。

我面临的挑战:我能够为所有内容编写代码，除了所有列都未格式化为文本。因此，包含 16 位数字的列将以科学记数法保存。

代码:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO
Public Class Form1
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim style As Microsoft.Office.Interop.Excel.Style
    Dim strFileName As String
    Dim pathFile As String
    Dim FileNameOnly As String
    Dim saveAsPath As String
    Dim delimiterType As String
    Dim fd As OpenFileDialog = New OpenFileDialog()
    Public Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Button2.Text = "Browse A Delimited File"
        Button1.Text = "Format and Save In Excel"
        Me.Text = "Delimiter To Excel - DEMO v1"
    End Sub

    Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Try
            If RadioButton1.Checked = True Then
                delimiterType = RadioButton1.Text
            Else
                delimiterType = RadioButton2.Text
            End If
            xlWorkBook = xlApp.Workbooks.Open(strFileName)
            xlWorkSheet = xlWorkBook.Sheets(1)
            xlApp.Visible = False

            With xlWorkSheet
                .Columns(1).TextToColumns( _
                Destination:=.Cells(1, 1), _
                DataType:=Excel.XlTextParsingType.xlDelimited, _
                ConsecutiveDelimiter:=False, _
                TAB:=False, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=False, _
                Other:=True, _
                OtherChar:=delimiterType, _
                FieldInfo:= <What_to_Fill_Here_to_Format_Every_Column_as_Text>
                TrailingMinusNumbers:=False)
            End With
        Catch ex As Exception
            MsgBox("Something is Wrong")
        End Try
        pathFile = Path.GetDirectoryName(fd.FileName)
        FileNameOnly = System.IO.Path.GetFileNameWithoutExtension(fd.FileName)
        saveAsPath = pathFile + "\" + FileNameOnly + ".xls"
        xlWorkBook.SaveAs(Filename:=saveAsPath, _
                          FileFormat:=39, _
                          ReadOnlyRecommended:=False _
                          )
        xlWorkBook.Close()
    End Sub

    Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        fd.Title = "Open File Dialog"
        fd.InitialDirectory = "C:\"
        fd.Filter = "All files (*.*)|*.*|All files (*.*)|*.*"
        fd.FilterIndex = 2
        fd.RestoreDirectory = True
        fd.Multiselect = False

        If fd.ShowDialog() = DialogResult.OK Then
            strFileName = fd.FileName
        End If
    End Sub
End Class

我将 FIELDINFO 留空，因为我假设此字段将任何列格式化为 TEXT、GENERAL 等。而且我不知道必须为其分配哪些值才能将所有列格式化为 TEXT。

请帮助我。

Answer 1

在代码中的某个位置，在将值加载到单元格之前，运行以下命令:

Cells.NumberFormat = "@"

这会将工作表中的所有单元格格式设置为文本。