我可以通过OleDb Jet4.0读取Excel文件并将其保存到数据集中吗? [英] Shall I read an excel file via OleDb Jet4.0 and save into dataset?

问题描述

我必须读取一个Excel文件并将其放入数据集中.

我应该通过OleDbDataAdapter读取excel文件内容，然后将其填充到数据集中吗?我尝试过但失败了.它说，当数据适配器执行Fill方法时，应用程序无法识别数据库格式.

代码:

 字符串queryAll ="SELECT * FROM [Sheet1 $]";字符串xlsPath = Directory.GetCurrentDirectory()+"\\ paid.xls";字符串strConn ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + xlsPath;尝试{m_dbDA =新的OleDbDataAdapter(queryAll，strConn);DataSet dsPaidXls = new DataSet();m_dbDA.Fill(dsPaidXls);//这里例外}捕获(System.Exception ex){MessageBox.Show(ex.Message);} 

这是否意味着无法直接读取excel数据并将其放入新数据集中?唯一的方法是逐个单元读取excel数据并插入具有数据表的新DataSet吗?

谢谢.

=======================================
已解决
=======================================

 字符串queryAll ="SELECT * FROM [Sheet1 $]";字符串xlsPath = Directory.GetCurrentDirectory()+"\\ paid.xls";字符串strConn ="Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + xlsPath +;扩展属性='Excel 8.0; IMEX = 1';";尝试{m_dbDA =新的OleDbDataAdapter(queryAll，strConn);DataSet dsPaidXls = new DataSet();m_dbDA.Fill(dsPaidXls，"[Sheet1 $]");dataGridView1.DataSource = dsPaidXls;dataGridView1.DataMember ="[Sheet1 $]";}捕获(System.Exception ex){MessageBox.Show(ex.Message);} 

解决方案

一旦您具有正确的连接字符串并且知道数据类型的问题，OLEDB就会很好地工作.Jet适用于2007年之前的版本，您需要为Excel添加扩展属性.

  String strConn ="Provider = Microsoft.ACE.OLEDB.12.0; Data Source ="+ xlsPath +扩展属性='Excel 12.0 Xml; HDR = YES';"; 

请参阅:连接字符串
如何将ADO与Visual Basic或VBA中的Excel数据一起使用(包含有用的注释)
各种说明

I have to read an excel file and put into a dataset.

Shall I read excel file content via OleDbDataAdapter, and then Fill into a dataset? I tried but faild. It said the application cannot recognize database format when data adapter is doing Fill method.

Code:

String queryAll = "SELECT * FROM [Sheet1$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath;
try
{
    m_dbDA = new OleDbDataAdapter(queryAll, strConn);
    DataSet dsPaidXls = new DataSet();
    m_dbDA.Fill(dsPaidXls);  //exception here
 }
 catch (System.Exception ex)
 {
    MessageBox.Show(ex.Message);
 }

Does it mean there is no way to directly read an excel data and put into a new dataset? And the only one way is to read excel data cell by cell and insert to a new DataSet with datatable?

Thanks in advance.

========================================
Resolved
========================================

String queryAll = "SELECT * FROM [Sheet1$]";
String xlsPath = Directory.GetCurrentDirectory() + "\\paid.xls";
String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath +
                 ";Extended Properties='Excel 8.0;IMEX=1';";
try
{
   m_dbDA = new OleDbDataAdapter(queryAll, strConn);
   DataSet dsPaidXls = new DataSet();
   m_dbDA.Fill(dsPaidXls,"[Sheet1$]");
   dataGridView1.DataSource = dsPaidXls;
   dataGridView1.DataMember = "[Sheet1$]";
 }
 catch (System.Exception ex)
 {
     MessageBox.Show(ex.Message);
 }

解决方案

OLEDB works quite well once you have the correct connection string and are aware of issues with data types. Jet is for versions prior to 2007 and you need to add extended properties for Excel.

String strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" 
  + xlsPath + "Extended Properties='Excel 12.0 Xml;HDR=YES';";

See: Connection Strings
How To Use ADO with Excel Data from Visual Basic or VBA (contains useful notes)
Various notes

这篇关于我可以通过OleDb Jet4.0读取Excel文件并将其保存到数据集中吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！