ADO.net和Excel [英] ADO.net and excel

问题描述

大家好.我正在尝试创建一个绘图程序来为我的物理作业作图.我经常使用Excel进行计算，而不是手工计算10个试验，每个试验需要5或5个计算.我的问题是Excel无法制作好的数据图.它们更多是为了商业，而不是出于科学目的.我想我会编写自己的程序，并使用system.drawing命名空间将图形导出为位图.但是，我无法从Excel获取数据.我不断收到无效的演员表错误.我在这里发布了原始代码. http://social.msdn.microsoft.com/论坛/en-US/vbgeneral/thread/bf40b31b-5af7-401e-8233-fc2180f686fb 我唯一得到的解决方法是使用ADO.net而不是Excel应用程序.我仍然会收到错误消息. TI甚至制作了具有两个标记为数据集1"的列的电子表格.和数据集2".每个数字都有1到10.无论我为什么指定数据类型，我都会收到一条错误消息，说明无效的类型转换.如果我尝试阅读标题，则会得到一个空白值.如果有人知道我在做什么错，请告诉我.这是我的代码

导入System.Data.Oledb

公共类表格1

'变量声明
Dim datacn作为OleDb.OleDbConnection'处理连接到excel
Dim dataworker作为OleDb.OleDbCommand'执行数据工作字符串'data range
Dim sheetname As String'数据表
Dim data(1，40)As String

Private Sub btndata_Click(ByVal sender as System.Object，ByVal e As System.EventArgs)处理btndata.Click
Try
Try
If txtsheet.Text<> "和txtsheet.Text<> 工作表"然后``确保我们有一个工作表
sheetname = txtsheet.Text
Else
MessageBox.Show(``请输入一个工作表以获取来自. br>如果是txtrange.Text<> "还有txtrange.Text<> 数据范围"然后
范围= txtrange.Text
Else
MessageBox.Show(``请输入数据范围''，``错误'')
结束

openbox.ShowDialog()
cnstring =``Provider = Microsoft.Jet.OLEDB.4.0;数据源="& openbox.FileName& < Extended Properties =''``Excel 8.0; HDR = YES;``''
datacn = New OleDb.OleDbConnection(cnstring)
数据工作者= New OleDb.

dataworker.CommandText =选择*从[" &工作表名称"$" &范围和"]"
dataworker.Connection = datacn

Dim datareader作为OleDb.OleDbDataReader'创建数据读取器

datacn.Open()'打开到文件的连接
datareader = dataworker.ExecuteReader

Dim i As Integer = 0'循环变量
Do While datareader.Read i = i + 1
数据(0，i)=数据(0，i] =数据(0.

如果(i> 20)然后退出Do Loop
MessageBox.Show(data(0，0))
例行捕获消息框，例如; Error)
结束尝试

End Sub

现在，它应该只显示找到的第一条数据.我需要将其加载到我自己的数组中，以便可以对其进行图形化.

解决方案

您可以尝试这种方法吗?

<身体> 昏暗的cmd作为System.Data.OleDb.OleDbDataAdapter 将Dimds作为新的System.Data.DataSet() cmd = 新 System.Data.OleDb.OleDbDataAdapter(从[Sheet1

A:AB]'选择**，datacn ) datacn.Open() cmd.Fill(ds) MsgBox(ds.Tables(0).Rows(0).Item(0)) datacn.Close()

Hello all. I am trying to create a graphing program to graph my physics homework for me. I frequently use Excel to do calculation rather tha hand calculate 10 trials that need 5 or five calculation a piece. My problem is that Excel can not make good data graphs. They are more for business than for scientific purposes. I figured I would write my own program and export the graphs as bitmaps using the system.drawing namespace. I can not get the data from Excel, though. I keep getting invalid cast errors. I posted my original code here. http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/bf40b31b-5af7-401e-8233-fc2180f686fb  The only respose I got was to use ADO.net instead of an Excel application. I still get errors. TI even made a spread sheet with two colums labeled "data set 1" and "data set 2". Each has the numbers 1 to 10 in them. No matter why data type I specify, I get an error saying invalid cast. If I try to read the heading, I get a blank value. If anyone knows what I am doing wrong, please let me know. Here is my code

Imports System.Data.Oledb

Public Class Form1

    'variable declarations
    Dim datacn As OleDb.OleDbConnection 'handles connecting to excel
    Dim dataworker As OleDb.OleDbCommand 'preforms the data work
    Dim cnstring As String 'file name
    Dim range As String 'data range
    Dim sheetname As String 'data sheet
    Dim data(1, 40) As String

    Private Sub btndata_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndata.Click
        Try
            If txtsheet.Text <> "" And txtsheet.Text <> "sheet" Then 'make sure we have a sheet
                sheetname = txtsheet.Text
            Else
                MessageBox.Show("Please enter a sheet to get data from", "Error")
            End If
            If txtrange.Text <> "" And txtrange.Text <> "Data Range" Then
                range = txtrange.Text
            Else
                MessageBox.Show("Please enter a data range", "Error")
                Exit Sub
            End If

            openbox.ShowDialog()
            cnstring = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & openbox.FileName & ";Extended Properties=""Excel 8.0;HDR=YES;"""
            datacn = New OleDb.OleDbConnection(cnstring)
            dataworker = New OleDb.OleDbCommand()

            dataworker.CommandText = "Select * From[" & sheetname & "$" & range & "]"
            dataworker.Connection = datacn

            Dim datareader As OleDb.OleDbDataReader 'create the data reader

            datacn.Open() 'open the connection to the file
            datareader = dataworker.ExecuteReader

            Dim i As Integer = 0 'loop variable
            Do While datareader.Read
                i = i + 1
                data(0, i) = datareader.GetString(0)

                If (i > 20) Then Exit Do
            Loop
            MessageBox.Show(data(0, 0))
        Catch ex As Exception
            MessageBox.Show(ex.ToString, "Error")
        End Try

    End Sub

Right now, it should only show the first piece of data it found. I need to load it into my own array so I can graph it.

解决方案

Can you try this way?

Dim cmd As System.Data.OleDb.OleDbDataAdapter

Dim ds As New System.Data.DataSet()

cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1 A:AB]", datacn)

datacn.Open()

cmd.Fill(ds)

MsgBox(ds.Tables(0).Rows(0).Item(0))

datacn.Close()

这篇关于ADO.net和Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！