Question

vb中怎么把.txt文件中的数据转换成excel表格啊？

如题
比如.txt文件中是这样的文件格式:

设备名 error1 error2 error3
dev1 没电 无信号 无人操作
dev2 没电 无信号 无人操作
dev3 没电 无信号 无人操作

Answer 1

最简单的方法,用计事本打开这个TXT文件,按照以下方法替换
将" "(2空格)替换为" "(一空格),知道文件不在变化
将" "(1空格)替换为","
另存为aaa.csv
用excel打开就行了

这个最简单

用vb代码如下
'打开文本文件
dim tmpstr as string
dim alltxt as string
open "c:\error.txt" for input as #1
do until eof(1)
read #1,tmpstr
alltxt=alltxt & tmpstr
loop
close #1
'这样整个文本就读近来了
'开始替换
do until instr(alltxt," ")=0'2空格
alltxt=replace(alltxt," "," ")'前2后1空格
loop
alltxt=replace(alltxt," ",",")'1空格
open "c:\error.csv" for output as #1
print #1,alltxt
close #1

生成的CSV不是EXCEL格式,但用任何版本的EXCEL打开都正常,安装EXCEL后默认也是用EXCEL打开CSV格式

注意,如果文本里有数字,那可能会转化为"0011"--"11",这个没办法

如果要生成XLS格式,比较麻烦,主要是拆分文本部分

Answer 2

可以把excel的东西先导入到datatable中，然后再导成txt格式

下面是导入到datatable的例子

Dim openbox As New OpenFileDialog
openbox.ShowDialog()
Dim filename As String = openbox.FileName
Dim myConn As New System.Data.OleDb.OleDbConnection
Dim strComMain As String = " SELECT * FROM [12$]"
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 4.0;HDR=no"""
myConn.Open()
Catch ex As Exception
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 1.0;HDR=no"""
myConn.Open()
Catch ex1 As Exception
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 8.0;HDR=no"""
myConn.Open()
Catch ex2 As Exception
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 3.0;HDR=no"""
myConn.Open()
Catch ex3 As Exception
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 2.1;HDR=no"""
myConn.Open()
Catch ex4 As Exception
Try
myConn.ConnectionString = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" & filename & ";Extended Properties=""Excel 5.0;HDR=no"""
myConn.Open()
Catch ex5 As Exception
MsgBox(ex5.Message)
End Try
End Try
End Try
End Try
End Try
End Try
Dim myCommandMain As New System.Data.OleDb.OleDbDataAdapter(strComMain, myConn)
Dim myData = New DataTable
Try
myCommandMain.Fill(myData)
Catch ex As Exception
MsgBox(ex.Message)
Finally
myConn.Close()
End Try