如何读入Excel文件并提取某些意味着我无法提取所有内容的数据 [英] How Do I Read Into Excel File And Extract Certain Data Which Means I Do Not Extract Everything

问题描述

我怎么读入excel文件并提取某些数据，这意味着我不提取所有内容



[edit]

我有倍数excel文件报告。我想从这些excel文件中提取数据保存在数据库中，这样我就可以有一个大的报告。

到目前为止我能够浏览文件并将数据保存到数据集中。我想读取数据并提取我只需要的某些数据。循环进入数据提取我需要的数据时陷入困境

how do i read into excel file and extract certain data which means i do not extract everything

[edit]
I have multiples excel file report. I'm suppose to extract data from those excel file save it in a database so that i can have 1 big report.
so far i am able to browse the file and save data into a dataset. I want to read into data and extract certain data that i need only. im stuck when looping into data extracting data that i need

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter

Dim fBrowse As New OpenFileDialog
With fBrowse
    .Filter = "Excel files(*.xlsx)|*.xlsx|All files (*.*)|*.*"
    .FilterIndex = 1
    .Title = "Import data from Excel file"
End With
If fBrowse.ShowDialog() = Windows.Forms.DialogResult.OK Then

    Dim fname As String
    fname = fBrowse.FileName
    MyConnection = New System.Data.OleDb.OleDbConnection("Provider= Microsoft.Jet.OLEDB.4.0; Data Source='" & fname & " '; " & "Extended Properties=Excel 8.0;")

    MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Apr14$]", MyConnection)
    MyCommand.TableMappings.Add("Table", "Billing_report")
    DtSet = New System.Data.DataSet


    MyCommand.Fill(DtSet)

    DataGridView1.DataSource = DtSet.Tables(0)
    MyConnection.Close()
    Dim sr As System.IO.StreamReader = New System.IO.StreamReader(fname)
    Dim line As String
    line = sr.ReadLine().Trim
    Dim n As Integer
    Do While line.StartsWith("CNC")
        sr.ReadLine()
        If line.StartsWith("CNC") Then
            line.Substring(10, 11).Length.ToString().Trim()
            n += 1
            If line.Contains("Circuit no") Then

            End If
        End If

    Loop
End If

[/ edit]

[/edit]

推荐答案

，MyConnection）
MyCommand.TableMappings.Add（ 表， Billing_report）
DtSet = 新 System.Data.DataSet


MyCommand.Fill（DtSet）

DataGridView1.DataSource = DtSet.Tables（< span class =code-digit> 0 ）
MyConnection.Close（）
Dim sr As System.IO.StreamReader = 新 System.IO.Strea mReader（fname）
Dim line As String
line = sr.ReadLine（）。修剪
Dim n As 整数
line.StartsWith（ CNC）
sr.ReadLine（）
如果 line.StartsWith（ CNC ）然后
line.Substring（ 10 ， 11 ）。Length.ToString（）。Trim（）
n + = 1
如果 line.Contains（ 电路号）然后

结束 如果
结束 如果

循环
结束 如果

", MyConnection) MyCommand.TableMappings.Add("Table", "Billing_report") DtSet = New System.Data.DataSet MyCommand.Fill(DtSet) DataGridView1.DataSource = DtSet.Tables(0) MyConnection.Close() Dim sr As System.IO.StreamReader = New System.IO.StreamReader(fname) Dim line As String line = sr.ReadLine().Trim Dim n As Integer Do While line.StartsWith("CNC") sr.ReadLine() If line.StartsWith("CNC") Then line.Substring(10, 11).Length.ToString().Trim() n += 1 If line.Contains("Circuit no") Then End If End If Loop End If

[/ edit]

[/edit]

看看这里：如何：使用一个OleDbConnection从多个工作簿中获取数据？ [ ^ ]。它可能对你有帮助。



如果你只想选择'table'的一部分，你需要使用：

Have a look here: How to: Get Data from Multiple Workbooks using One OleDbConnection?[^]. It might help you.

If you want to select only part of 'table', you need to use:

SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [WorksheetName

A1：G500]

如果您有命名范围，

If you have named ranges,

SELECT <ColName1>, <ColName2>, <ColNameN>
FROM [NamedRange]

能够加入来自另一张表的数据，使用：

To be able to join data from another sheet, use:

SELECT t1.<ColName1>, t1.<ColName2>, t2.<ColName1>, t2.<ColName2>
FROM [Worksheet1

这篇关于如何读入Excel文件并提取某些意味着我无法提取所有内容的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！