vb.net 将 Excel 中的所有列格式化为文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20380135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Format all columns in Excel as Text
提问by Rohit
I am developing a very basic application on VB.NET.
我正在 VB.NET 上开发一个非常基本的应用程序。
Function: To import a delimited TXT file and perform "TextToColumn", Column-AutoFit, format all columns as TEXT and finally save as Excel.
功能：导入一个带分隔符的TXT文件，执行“TextToColumn”，Column-AutoFit，将所有列格式化为TEXT，最后保存为Excel。
Challenge I am facing: I am able to code for everything except all columns are not formatted as TEXT. Due to this, a column containing Numbers of 16 digits are saved in Scientific Notation.
我面临的挑战：除了所有列都没有格式化为文本之外，我能够为所有内容编码。因此，包含 16 位数字的列以科学记数法保存。
Code:
代码：
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
I have left FIELDINFO blank because I assume this field formats any column as TEXT, GENERAL, etc. And I don't know the values that must be assigned to this to format all columns as TEXT.
我将 FIELDINFO 留空，因为我假设此字段将任何列格式化为 TEXT、GENERAL 等。而且我不知道必须分配给它的值才能将所有列格式化为 TEXT。
Please help me.
请帮我。
回答by Gary's Student
Somewhere in your code, prior to loading values into the cells, run this:
在代码中的某处，在将值加载到单元格之前，运行以下命令：
Cells.NumberFormat = "@"
this will format all the cells in the worksheet to Text.
这会将工作表中的所有单元格格式化为文本。
回答by Pravin Virkud
This did work for me.
这对我有用。
.Cells(Row, Col).NumberFormat = "@" ' Change format of cell to store number as text
.Cells(Row, Col).NumberFormat = "@" ' 改变单元格的格式以将数字存储为文本
.Cells(Row, Col).Value =
.Cells(Row, Col).Value =
This stores string of number as a text in cell. But excel gives warning with green mark at the cell's corner.
这将数字字符串存储为单元格中的文本。但是excel在单元格的角落用绿色标记发出警告。
回答by Dan Donoghue
Try this in the code where you posted the question :
在您发布问题的代码中试试这个：
FieldInfo:=Array(1, 2)
回答by user2434772
try that-
试试——
With objSht
rng = objSht.Columns("A:AX")
rng.Style = "Text"
End With
