Table of Contents
使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007
接口类型
语法一览
注册表设置
单一数据类型列的类型解析
Home Database Mysql Tutorial C#读取Excel,Access数据库

C#读取Excel,Access数据库

Jun 07, 2016 pm 03:44 PM
access excel use database read

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007 测试文件:D:/97-2003.xls和D:/2007.xlsx,两个文件的内容是一模一样的。 测试环境:SQL Server 2000 / 2005。 接口类型 语法一览 注册表设置 单一数据类型列的类型解析 混合数据类型列的自然解析 混

使用 OpenRowSet 和 OpenDataSource 访问 Excel 97-2007

测试文件:D:/97-2003.xls和D:/2007.xlsx,两个文件的内容是一模一样的。

测试环境:SQL Server 2000 / 2005。


  • 接口类型
  • 语法一览
  • 注册表设置
  • 单一数据类型列的类型解析
  • 混合数据类型列的自然解析
  • 混合数据类型列的强制解析——IMEX=1
  • 如何解决 NULL 值问题
  • SQL Server 2000 中的列顺序问题
  • 如何访问隐藏的 Sheet
  • 如何访问非常规命名的 Sheet
  • 关于Excel导入的HDR=YES; IMEX=1详解
  • C#读取Access数据库

接口类型

有两种接口可供选择:Microsoft.Jet.OLEDB.4.0(以下简称 Jet 引擎)和Microsoft.ACE.OLEDB.12.0(以下简称 ACE 引擎)。

Jet 引擎大家都很熟悉,可以访问 Office 97-2003,但不能访问 Office 2007。

ACE 引擎是随 Office 2007 一起发布的数据库连接组件,既可以访问 Office 2007,也可以访问 Office 97-2003。

另外:Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件,而 Microsoft.Jet.OLEDB.4.0 是不可以的。

Microsoft.ACE.OLEDB.12.0 安装文件:

http://www.microsoft.com/downloads/details.aspx?displaylang=zh-cn&FamilyID=7554f536-8c28-4598-9b72-ef94e038c891

 


语法一览

使用 Jet 引擎或 ACE 引擎访问,在语法上没有什么的区别。

--> Jet 引擎访问 Excel 97-2003
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$]

--> ACE 引擎访问 Excel 97-2003
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/97-2003.xls')...[Sheet1$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/97-2003.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]

--> ACE 引擎访问 Excel 2007
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', 'select * from [Sheet1$]')
select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx', [Sheet1$])
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/2007.xlsx')...[Sheet1$]
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:/2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
Copy after login

Excel 2007 工作簿文件的扩展名是:xlsx

HDR=Yes/No

可选参数,指定 Excel 表的第一行是否列名,缺省为 Yes,可以在注册表中修改缺省的行为。

IMEX=1

可选参数,将 Excel 表中混合 Intermixed 数据类型的列强制解析为文本。


注册表设置

Microsoft.Jet.OLEDB.4.0

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel

Microsoft.ACE.OLEDB.12.0

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Office/12.0/Access Connectivity Engine/Engines/Excel

FirstRowHasNames

设置 HDR 参数的缺省行为,默认为 Yes。

ImportMixedTypes

设置混合列的强制解析类型,默认为文本 Text。

TypeGuessRows

设置用于解析数据类型的取样行数,默认取样前 8 行。如果设置为 0,将分析所有数据行,但不建议这样做,会影响引擎的性能。

注意:Excel 表数据列是单一列数据类型还是混合列数据类型列,是由取样行决定,而不是整列数据决定。


单一数据类型列的类型解析

Sheet1 的内容如下图所示,涵盖了大部分 Excel 的数据类型,其中 longtext 分别有 256 个 A 和 B。

 

C#读取Excel,Access数据库

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1664
14
PHP Tutorial
1268
29
C# Tutorial
1246
24
How to configure zend for apache How to configure zend for apache Apr 13, 2025 pm 12:57 PM

How to configure Zend in Apache? The steps to configure Zend Framework in an Apache Web Server are as follows: Install Zend Framework and extract it into the Web Server directory. Create a .htaccess file. Create the Zend application directory and add the index.php file. Configure the Zend application (application.ini). Restart the Apache Web server.

Oracle's Role in the Business World Oracle's Role in the Business World Apr 23, 2025 am 12:01 AM

Oracle is not only a database company, but also a leader in cloud computing and ERP systems. 1. Oracle provides comprehensive solutions from database to cloud services and ERP systems. 2. OracleCloud challenges AWS and Azure, providing IaaS, PaaS and SaaS services. 3. Oracle's ERP systems such as E-BusinessSuite and FusionApplications help enterprises optimize operations.

MySQL vs. Other Databases: Comparing the Options MySQL vs. Other Databases: Comparing the Options Apr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

Using Dicr/Yii2-Google to integrate Google API in YII2 Using Dicr/Yii2-Google to integrate Google API in YII2 Apr 18, 2025 am 11:54 AM

VprocesserazrabotkiveB-enclosed, Мнепришлостольностьсясзадачейтерациигооглапидляпапакробоглесхетсigootrive. LEAVALLYSUMBALLANCEFRIABLANCEFAUMDOPTOMATIFICATION, ČtookazaLovnetakProsto, Kakaožidal.Posenesko

MySQL: Structured Data and Relational Databases MySQL: Structured Data and Relational Databases Apr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

What is apache server? What is apache server for? What is apache server? What is apache server for? Apr 13, 2025 am 11:57 AM

Apache server is a powerful web server software that acts as a bridge between browsers and website servers. 1. It handles HTTP requests and returns web page content based on requests; 2. Modular design allows extended functions, such as support for SSL encryption and dynamic web pages; 3. Configuration files (such as virtual host configurations) need to be carefully set to avoid security vulnerabilities, and optimize performance parameters, such as thread count and timeout time, in order to build high-performance and secure web applications.

How to solve nginx current limit How to solve nginx current limit Apr 14, 2025 pm 12:06 PM

The Nginx current limit problem can be solved by: use ngx_http_limit_req_module to limit the number of requests; use ngx_http_limit_conn_module to limit the number of connections; use third-party modules (ngx_http_limit_connections_module, ngx_http_limit_rate_module, ngx_http_access_module) to implement more current limit policies; use cloud services (Cloudflare, Google Cloud Rate Limiting, AWS WAF) to DD

Real-World MySQL: Examples and Use Cases Real-World MySQL: Examples and Use Cases Apr 14, 2025 am 12:15 AM

MySQL's real-world applications include basic database design and complex query optimization. 1) Basic usage: used to store and manage user data, such as inserting, querying, updating and deleting user information. 2) Advanced usage: Handle complex business logic, such as order and inventory management of e-commerce platforms. 3) Performance optimization: Improve performance by rationally using indexes, partition tables and query caches.

See all articles