一路向前走

其中的代码,如果您有更好的改进,请一定提出您的宝贵意见及建议

  :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: :: 管理 ::

自己写的一个将特定格式的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


 

posted on 2008-04-24 09:27  Adair  阅读(979)  评论(0编辑  收藏  举报