none
想请教关于excel file convert 成 textfile import excel 方面可以怎么篇写 RRS feed

  • 问题

  • 我get error 在以下这两个import file以及excel 

    Imports Microsoft.Office.Interop.excel
    Imports Microsoft.Office.Interop

      Private mExcelApp As Excel.Application

     
        Private strFileName As String = "C:\patiënt.xlsx"
        Private strTargetName As String = "C:\Output.xlsx"

    不知是否能提供一些excel convert to text file 的program

    感谢

    2020年1月8日 6:56

答案

  • Hi christing, 

    Excel中的表写入text文件的方法是通过将表转换成DataTable, 然后再用StreamWriter 逐行写入文件(上面第一次回答中已经包含)。

    我的DataTable是我在用F11调试的时候查看DataSet得到的, 如果你想根据所选择的DataTableDataGridView中显示并保存,可以参考如下完整的代码:

        Dim ds = New DataSet()
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim path As String = "D:\Test.xlsx"
            Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"
    
            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim sheets As DataTable = GetSchemaTable(con)
                For Each r As DataRow In sheets.Rows
                    Dim data As DataTable = New DataTable()
                    ComboBox1.Items.Add(r("TABLE_NAME").ToString())
                    Dim query As String = "SELECT * FROM [" & r("TABLE_NAME").ToString() & "]"
                    Dim adapter = New OleDbDataAdapter(query, connection)
                    adapter.Fill(data)
                    ds.Tables.Add(data)
                Next
            End Using
    
        End Sub
        Private Function GetSchemaTable(ByVal connectionString As String) As DataTable
            Using connection As OleDbConnection = New OleDbConnection(connectionString)
                connection.Open()
                Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                Return schemaTable
            End Using
        End Function
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            DataGridView1.DataSource = ds.Tables(ComboBox1.SelectedIndex)
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using saveFileDialog As SaveFileDialog = New SaveFileDialog()
                saveFileDialog.InitialDirectory = "D:\TestFile2\"
                saveFileDialog.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
                saveFileDialog.RestoreDirectory = True
    
                If saveFileDialog.ShowDialog() = DialogResult.OK Then
                    Dim filePath As String = saveFileDialog.FileName
    
                    Using sw As StreamWriter = File.CreateText(filePath)
    
                        If ComboBox1.SelectedIndex > -1 Then
    
                            For Each row As DataRow In ds.Tables(ComboBox1.SelectedIndex).Rows
                                sw.WriteLine(String.Join("|", row.ItemArray))
                            Next
                        Else
                            MessageBox.Show("Please select a table!")
                        End If
                    End Using
                End If
            End Using
        End Sub
    

    测试结果:


    希望这可以帮助你解决问题。

    另外之后你再遇到问题时,希望可以将自己遇到的问题比较全面的描述清楚,这样我们可以在短时间内提供完整的解决方案和建议。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 christing 2020年1月15日 2:11
    2020年1月14日 9:10
    版主

全部回复

  • Hi, 

    首先确保你64位的电脑安装了64位版本的Microsoft Access Database Engine 2010。(如果是32位,则要安装32位版本)

    将‘Target CPU’(右键项目Properties-> Compile -> Target CPU)选择X64.

    最后参考以下代码来读取相关Excel表中的值到datatable中,随后再将值写入text文件中。

            Dim path As String = "D:\Test.xlsx"
            Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"
    
            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim adapter = New OleDbDataAdapter("select * from [Sheet1$]", connection)
                Dim ds = New DataSet()
                adapter.Fill(ds, "TableSheet1")
                Dim data As DataTable = ds.Tables("TableSheet1")
    
                Using sw As StreamWriter = File.CreateText("D:\test.txt")
    
                    For Each row As DataRow In data.Rows
                        sw.WriteLine(String.Join("|", row.ItemArray))
                    Next
                End Using
            End Using
    

    Sheet1表中的值:

    读到的datatable中的值:

    最后写入text文件中的结果:

    希望对你有帮助.

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2020年1月8日 8:16
    版主
  • @Xingyu Zhao

    感谢你的回复。

    Browse 1 (input excel)

      Dim Chosen_File As String = ""

            If openFileDialog1.ShowDialog() = DialogResult.OK Then
                Chosen_File = openFileDialog1.FileName
            End If

            If Chosen_File = String.Empty Then
                Return
            End If

            InputExcel.Text = Chosen_File

    browse 2 (output excel)

      Dim result As DialogResult = Me.folderBrowserDialog1.ShowDialog()
            Dim foldername As String = ""
            If result = DialogResult.OK Then
                foldername = folderBrowserDialog1.SelectedPath
            End If
            locationFile.Text = foldername

    是否将这个Target CPU change to X64 

    convert  code

    Dim path As String = "D:\Test.xlsx"
            Dim con As String = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"

            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim adapter = New OleDbDataAdapter("select * from [Sheet1$]", connection)
                Dim ds = New DataSet()
                adapter.Fill(ds, "TableSheet1")
                Dim data As DataTable = ds.Tables("TableSheet1")

                Using sw As StreamWriter = File.CreateText("D:\test.txt")

                    For Each row As DataRow In data.Rows
                        sw.WriteLine(String.Join("|", row.ItemArray))
                    Next
                End Using
            End Using

     我想问要是将excel file convert去text file save resources 方面可以怎么篇写

    Dim con As String = "Provider=Microsoft.ACE.OLEDB.4.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"

    get error

    (The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator.)

    我的excelfile有sheet1。when 我select了excel file我的excel program read不到我的excel file有几个sheet

    Best Regards,

    chinting




    2020年1月9日 5:03
  • Hi, 

    如果文件的类型为xlsx(来自Excel 2007和更高版本),则提供程序为Microsoft.ACE.OLEDB.12.0

    如果文件的类型为xls,则提供程序为Microsoft.Jet.OleDb.4.0

    请注意以上两者间的差异。

    另外如果你的Excel中有多张表,以下代码将帮助你找到Excel中的所有表,并将其表名显示在combobox中,同时将多张表的datatable存储在dataset中,你可以根据自己需要将datatable写入text文件。

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim path As String = "D:\Test.xlsx"
           Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"
    
            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim sheets As DataTable = GetSchemaTable(con)
                Dim ds = New DataSet()
    
                For Each r As DataRow In sheets.Rows
                    Dim data As DataTable = New DataTable()
                    ComboBox1.Items.Add(r("TABLE_NAME").ToString())
                    Dim query As String = "SELECT * FROM [" & r("TABLE_NAME").ToString() & "]"
                    Dim adapter = New OleDbDataAdapter(query, connection)
                    adapter.Fill(data)
                    ds.Tables.Add(data)
                Next
            End Using
        End Sub
        Private Function GetSchemaTable(ByVal connectionString As String) As DataTable
            Using connection As OleDbConnection = New OleDbConnection(connectionString)
                connection.Open()
                Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                Return schemaTable
            End Using
        End Function

    Excel中的两张表:

      

    DataSet中存储的两个datatable:

    Combobox中的值:

    希望对你有帮助。

    Best Regards,

    Xingyu Zhao



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年1月9日 8:40
    版主
  • @ Xingyu Zhao

    感谢你的回复我想问 Xingyu Zhao。我想问Xingyu Zhao我的visual studio machine会出现这个error. when 我的databaseprovider=Microsoft.Jet.OLEDB.12.0; 这是因为,我的visual studio machine 没有install什么东西吗?

     

    2020年1月13日 1:10
  • Hi christing,

    在你安装64位版本的Microsoft Access Database Engine 2010以及将‘Target CPU’选择X64的基础上,对于xlsx后缀的Excel文件,你需要的 provider 是 ‘Microsoft.ACE.OLEDB.12.0’ 而不是 'Microsoft.Jet.OLEDB.12.0'。

    否则你会出现以下错误:

    希望能够帮助你解决问题。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    2020年1月13日 1:23
    版主
  • @Xingyu Zhao

    想请教xing yu 我可以如何篇写将excel 的sheet convert 成txt file。由于网上太多资料我无法找到适合的code。不知可否提供我关于excel convert to txt 的code呢!感恩。

    2020年1月13日 6:56
  • @Xingyu Zhao

    感谢你的回复。

    我想问xing yu以下table是怎样create的。因为我用了你的方式篇写后并没有出现以下这个table。

    我采用了datagidview 让下图table出现在我的datagridview 但是出现的效果并不完整。可否请教如何让datagridview如你所做般选什么table 出现什么table呢!谢谢 

     DataGridView1.DataSource = ds.Tables(0) 

    • 已标记为答案 christing 2020年1月15日 2:11
    • 取消答案标记 christing 2020年1月15日 2:11
    2020年1月14日 2:22
  • Hi christing, 

    Excel中的表写入text文件的方法是通过将表转换成DataTable, 然后再用StreamWriter 逐行写入文件(上面第一次回答中已经包含)。

    我的DataTable是我在用F11调试的时候查看DataSet得到的, 如果你想根据所选择的DataTableDataGridView中显示并保存,可以参考如下完整的代码:

        Dim ds = New DataSet()
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim path As String = "D:\Test.xlsx"
            Dim con As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties='Excel 12.0;HDR=NO;'"
    
            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim sheets As DataTable = GetSchemaTable(con)
                For Each r As DataRow In sheets.Rows
                    Dim data As DataTable = New DataTable()
                    ComboBox1.Items.Add(r("TABLE_NAME").ToString())
                    Dim query As String = "SELECT * FROM [" & r("TABLE_NAME").ToString() & "]"
                    Dim adapter = New OleDbDataAdapter(query, connection)
                    adapter.Fill(data)
                    ds.Tables.Add(data)
                Next
            End Using
    
        End Sub
        Private Function GetSchemaTable(ByVal connectionString As String) As DataTable
            Using connection As OleDbConnection = New OleDbConnection(connectionString)
                connection.Open()
                Dim schemaTable As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                Return schemaTable
            End Using
        End Function
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            DataGridView1.DataSource = ds.Tables(ComboBox1.SelectedIndex)
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Using saveFileDialog As SaveFileDialog = New SaveFileDialog()
                saveFileDialog.InitialDirectory = "D:\TestFile2\"
                saveFileDialog.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"
                saveFileDialog.RestoreDirectory = True
    
                If saveFileDialog.ShowDialog() = DialogResult.OK Then
                    Dim filePath As String = saveFileDialog.FileName
    
                    Using sw As StreamWriter = File.CreateText(filePath)
    
                        If ComboBox1.SelectedIndex > -1 Then
    
                            For Each row As DataRow In ds.Tables(ComboBox1.SelectedIndex).Rows
                                sw.WriteLine(String.Join("|", row.ItemArray))
                            Next
                        Else
                            MessageBox.Show("Please select a table!")
                        End If
                    End Using
                End If
            End Using
        End Sub
    

    测试结果:


    希望这可以帮助你解决问题。

    另外之后你再遇到问题时,希望可以将自己遇到的问题比较全面的描述清楚,这样我们可以在短时间内提供完整的解决方案和建议。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • 已标记为答案 christing 2020年1月15日 2:11
    2020年1月14日 9:10
    版主
  • @Xingyu Zhao

    感谢你的回复。我会好好提高以及整理思路的能力。谢谢你的指教...:)

    2020年1月15日 2:01