vb.net 使用 VB 2010 express 将数据从文本文件粘贴到 Excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16064635/
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
Pasting data from text file to excel using VB 2010 express
提问by slyclam
This may sound very easy but I am a newbie in vb programming. I have a text file with 11 rows, 2 columns separated by 39 spaces between them. Now I'm trying to read this file, copy it & paste into an excel sheet. Here is the code that I have so far:
这听起来很简单，但我是 vb 编程的新手。我有一个包含 11 行、2 列的文本文件，它们之间用 39 个空格分隔。现在我正在尝试阅读此文件，将其复制并粘贴到 Excel 表格中。这是我到目前为止的代码：
Imports System.IO
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oExcel As Object
Dim oBook As Object
Dim oRow As Int16 = 0
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Read input .txt file line-by-line, Copy to Clipboard & Paste to Excel
Using rdr As New System.IO.StreamReader("C:\Temp\ONI.txt")
Do While rdr.Peek() >= 0
Dim InputLine As String = rdr.ReadLine
oRow = oRow + 1
System.Windows.Forms.Clipboard.SetDataObject (InputLine)
oBook.Worksheets(1).Range("A" + oRow.ToString).Select()
oBook.Worksheets(1).Paste()
Loop
rdr.Close()
End Using
oExcel.Visible = True
'oExcel.SaveAs("C\Temp\test.xls")
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
End Sub
End Class
This is working to the extent that an excel workbook is opened & the data is getting pasted in A1 to A11 i.e the rows are 11 (which is ok) but the column is only 1 (it should be in columns A & B). I know this is very easy, please guide me.
这是在打开 excel 工作簿并且数据被粘贴到 A1 到 A11 的范围内起作用的，即行是 11（可以）但列只有 1（它应该在 A 列和 B 列中）。我知道这很容易，请指导我。
Also the code stops at the "Save as" line (which I have commented). When run it shows error Public member 'SaveAs' on type 'ApplicationClass' not found.
代码也停在“另存为”行（我已经评论过）。运行时显示错误Public member 'SaveAs' on type 'ApplicationClass' not found.
回答by David Zemens
You should be doing oBook.SaveAs(...), you are not saving the Excel.Application, just the workbook.
你应该做的oBook.SaveAs(...)，你不是保存Excel.Application，只是工作簿。
For importing text, why not just use the Workbooks.OpenTextmethod? With some tweaking this should allow you to open the delimited files in the desired format. This saves yout he hassle of trying to also do text-to-columns after importing it, and the nasty hassle of having to work with the clipboard.
对于导入文本，为什么不直接使用该Workbooks.OpenText方法？通过一些调整，这应该允许您以所需格式打开分隔文件。这使您免于在导入后尝试进行文本到列的麻烦，以及必须使用剪贴板的麻烦。
E.g., this opens a text file in an Excel workbook, treats spaces as the delimiter, consecutive delimiters as one, so it should split your columns correctly.
例如，这会在 Excel 工作簿中打开一个文本文件，将空格视为分隔符，将连续分隔符视为一个，因此它应该正确拆分您的列。
Sub OpenTextFile()
Dim wbText As Workbook
Dim sFName As String ' file name & path'
sFName = Application.GetOpenFilename()
Workbooks.OpenText Filename:=sFName _
, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, Semicolon:=False _
, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Exit Sub
End Sub
Here is the test file I used:
这是我使用的测试文件：
And here is the output, properly in 2 columns:
这是输出，正确地在 2 列中：
