自己写的一个将特定格式的TXT数据文件中的数据写入EXCEL中的列中
Imports Excel
Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Imports System.IO
Imports System.Collections
Imports System.Threading
Public Class frmFileToExcel
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents ProgressBar1 As System.Windows.Forms.ProgressBar
Friend WithEvents txtPath As System.Windows.Forms.TextBox
Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
Friend WithEvents lblprompt As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.ComboBox1 = New System.Windows.Forms.ComboBox
Me.Label1 = New System.Windows.Forms.Label
Me.txtPath = New System.Windows.Forms.TextBox
Me.Button1 = New System.Windows.Forms.Button
Me.Button2 = New System.Windows.Forms.Button
Me.ProgressBar1 = New System.Windows.Forms.ProgressBar
Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
Me.lblprompt = New System.Windows.Forms.Label
Me.SuspendLayout()
'
'ComboBox1
'
Me.ComboBox1.Items.AddRange(New Object() {"Pull&Peel", "BookBend Stress", "Abrasion Stress", "Pen Stress", "Page Turning Stress"})
Me.ComboBox1.Location = New System.Drawing.Point(144, 16)
Me.ComboBox1.Name = "ComboBox1"
Me.ComboBox1.Size = New System.Drawing.Size(360, 21)
Me.ComboBox1.TabIndex = 0
'
'Label1
'
Me.Label1.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Label1.Location = New System.Drawing.Point(16, 16)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(112, 24)
Me.Label1.TabIndex = 1
Me.Label1.Text = "Machine:"
'
'txtPath
'
Me.txtPath.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.txtPath.Location = New System.Drawing.Point(144, 79)
Me.txtPath.Name = "txtPath"
Me.txtPath.Size = New System.Drawing.Size(360, 26)
Me.txtPath.TabIndex = 2
Me.txtPath.Text = ""
'
'Button1
'
Me.Button1.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Button1.Location = New System.Drawing.Point(8, 72)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(120, 40)
Me.Button1.TabIndex = 3
Me.Button1.Text = "Browse..."
'
'Button2
'
Me.Button2.Font = New System.Drawing.Font("Microsoft Sans Serif", 12.0!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
Me.Button2.Location = New System.Drawing.Point(184, 128)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(160, 48)
Me.Button2.TabIndex = 4
Me.Button2.Text = "Translate"
'
'ProgressBar1
'
Me.ProgressBar1.Dock = System.Windows.Forms.DockStyle.Bottom
Me.ProgressBar1.Location = New System.Drawing.Point(0, 183)
Me.ProgressBar1.Name = "ProgressBar1"
Me.ProgressBar1.Size = New System.Drawing.Size(536, 15)
Me.ProgressBar1.TabIndex = 5
Me.ProgressBar1.Visible = False
'
'lblprompt
'
Me.lblprompt.ForeColor = System.Drawing.Color.Red
Me.lblprompt.Location = New System.Drawing.Point(112, 183)
Me.lblprompt.Name = "lblprompt"
Me.lblprompt.Size = New System.Drawing.Size(352, 16)
Me.lblprompt.TabIndex = 6
Me.lblprompt.Text = "reading from txt file..."
Me.lblprompt.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
Me.lblprompt.Visible = False
'
'frmFileToExcel
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(536, 198)
Me.Controls.Add(Me.lblprompt)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.txtPath)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.ComboBox1)
Me.Controls.Add(Me.ProgressBar1)
Me.MaximizeBox = False
Me.MinimizeBox = False
Me.Name = "frmFileToExcel"
Me.Text = "Translator(Only For MCT's Machine)"
Me.ResumeLayout(False)
End Sub
#End Region
Dim strPath As String
Private FileInput As FileStream
Private BinaryInput As BinaryReader
Dim Index As Integer
Dim myExcelArray(0) As Array '需要写入EXCEL的数组的数组
Dim myYArray(65000, 0) As Single '将.TXT文件中的数据读入此数组.
Dim collumns(255) As String '对应于EXCEL中的列
Dim myYDataArray(0, 0) As Single '将myYArray(65000, 0)中的实际数据转入此数组. 以便写入EXCEL中的数据是实际读出来的数据
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Me.OpenFileDialog1.Filter = "txt files (*.txt)|*.txt"
Me.OpenFileDialog1.ShowDialog()
strPath = Me.OpenFileDialog1.FileName
Me.txtPath.Text = strPath
End Sub
Private Function ReadFile() As Boolean
Try
lblprompt.Visible = True
Me.lblprompt.Refresh()
Me.ProgressBar1.Visible = True
Me.ProgressBar1.Maximum = 100
Me.ProgressBar1.Minimum = 0
Me.ProgressBar1.Value = 0
Index = 0
Try
If (FileInput Is Nothing) = False Then
FileInput = Nothing
End If
If (BinaryInput Is Nothing) = False Then
BinaryInput = Nothing
End If
If FileInput Is Nothing Then
FileInput = New FileStream(strPath, FileMode.Open, FileAccess.Read)
End If
If BinaryInput Is Nothing Then
BinaryInput = New BinaryReader(FileInput)
End If
Catch ex As IOException
MessageBox.Show("Open File: " & strPath & " Failed!", "Open File Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
FileInput.Close()
BinaryInput.Close()
lblprompt.Visible = False
Return False
End Try
Dim i As Integer
L: Try
If Me.ComboBox1.SelectedIndex = 0 Or Me.ComboBox1.SelectedIndex = 1 Then
Dim cycPos As Long
cycPos = 65001 * Index
i = 0
Do
FileInput.Seek((i + cycpos) * 50, SeekOrigin.Begin)
BinaryInput.ReadInt32()
myYArray(i, 0) = BinaryInput.ReadSingle()
i += 1
Loop
Else
Dim cycPos As Long
cycPos = 65001 * Index
i = 0
Do
FileInput.Seek((i + cycPos) * 20, SeekOrigin.Begin)
myYArray(i, 0) = BinaryInput.ReadSingle()
i += 1
Loop
End If
Catch ex As Exception
ReDim myYDataArray(i - 1, 0) '取出实际有用数据
Array.Copy(myYArray, 0, myYDataArray, 0, i)
ReDim Preserve myExcelArray(Index)
myExcelArray(Index) = myYDataArray.Clone '将读出来的数据数组存入用来保存写入EXCEL中的数组的数组
Array.Clear(myYArray, 0, myYArray.Length)
Array.Clear(myYDataArray, 0, myYDataArray.Length)
Index += 1
If i > 65000 Then
GoTo L
End If
End Try
Me.ProgressBar1.Value = 30
FileInput.Close()
BinaryInput.Close()
lblprompt.Visible = False
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
lblprompt.Visible = False
End Try
Return True
End Function
Private Sub ToExcel()
'定义相关参数
Dim m_objExcel As Application
Dim m_objBook As Workbook
Dim m_objSheet As _Worksheet
m_objExcel = CreateObject("Excel.Application")
m_objBook = m_objExcel.Workbooks.Add
m_objSheet = m_objBook.Worksheets(1)
Dim m_objRange As Range
Dim i As Integer
Me.ProgressBar1.Value = 50
For i = 0 To myExcelArray.Length - 1
m_objRange = m_objSheet.Range(collumns(i))
m_objRange = m_objRange.Resize(myExcelArray(i).Length, 1)
m_objRange.Value = myExcelArray(i) '写入EXCEL中
Next
Me.ProgressBar1.Value = 90
Try
m_objBook.SaveAs(strPath.Substring(0, strPath.Length - 4) & ".xls")
Catch ex As Exception
End Try
'销毁相关对像. 不然在任务管理器中会出来EXCEL.EXE进程.
m_objRange = Nothing '此点容易忽略.此处不赋值NULL,EXCEL.EXE进程将无法杀死
m_objSheet = Nothing
m_objBook = Nothing
m_objExcel.Quit()
m_objExcel = Nothing
GC.Collect()
GC.WaitForPendingFinalizers()
Me.ProgressBar1.Value = 100
txtPath.Text = ""
strPath = ""
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Index = 0
If strPath = "" Then
MessageBox.Show("Please select a txt file", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Button2.Enabled = False
If ReadFile() = False Then
Button2.Enabled = True
Me.lblprompt.Visible = False
Exit Sub
End If
ToExcel()
Button2.Enabled = True
End Sub
Private Sub frmFileToExcel_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Try
Dim i As Integer
Dim j As Integer
Dim bArray() As String = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
Dim aArray() As String = {"A", "B", "C", "D", "E", "F", "G", "H"}
For i = 0 To 25
collumns(i) = bArray(i) & "1"
Next
For i = 1 To 8
For j = 1 To 26
collumns(25 + j + 26 * (i - 1)) = aArray(i - 1) & bArray(j - 1) & 1
Next
Next
For i = 1 To 22
collumns(233 + i) = "I" & bArray(i - 1) & "1"
Next
Catch ex As Exception
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
End Class
附:写特定TXT格式数据的写入函数:
Private Sub SaveDataFile()
Try
If Dir.Exists(Application.StartupPath & "\DataFile") = False Then
Dir.CreateDirectory(Application.StartupPath & "\DataFile")
End If
If FileOutput Is Nothing Then
FileOutput = New FileStream(Application.StartupPath & "\DataFile\" & ItemStr & ".txt", FileMode.Create, FileAccess.Write)
End If
If BinaryOutput Is Nothing Then
BinaryOutput = New BinaryWriter(FileOutput)
End If
Dim i As Integer
Try
For i = 0 To ylist.Count - 1
FileOutput.Seek(i * 20, SeekOrigin.Begin)
BinaryOutput.Write(Convert.ToSingle(ylist(i)))
Next
Catch ex1 As FormatException
MsgBox(ex1.Message)
End Try
Catch ex2 As IOException
MsgBox(ex2.Message)
Catch ex As Exception
'MsgBox(ex.Message)
Finally
FileOutput.Close()
BinaryOutput.Close()
If (FileOutput Is Nothing) = False Then
FileOutput = Nothing
End If
If (BinaryOutput Is Nothing) = False Then
BinaryOutput = Nothing
End If
End Try
End Sub
此代码为边学边试, 写出来的. 在学的过程中,我一直有个问题想问:
如何能将一个一维数组直接写入EXCEL中的某一列中.为何非要用到二维数组来进行.
不明. 如有知道的朋友,请告之.
谢谢!
