C#项目中操作Excel文件的四种方式（access excel file in my application using .Net and c# for development in a system where ms office is not installed）-iaspnetcore.com

How can i access excel file in my application using .Net and c# for development in a system where ms office is not installed?

Is there a C# code to read and display Excel sheets?

Office Add-in三种玩法

vba
VSTO
Web Add-in
三种玩法，是指最早的VBA技术，后来的VSTO技术，以及现在更推荐的Web Add-in的技术。

Excel Programming Interfaces

Excel provides several options for developing applications that interface with it. The Excel programming interfaces were added to earlier versions in the following order:

1）XLM macro language: The first user-accessible language for the extension of Excel and the basis of the C API. Although still supported in Excel 2007, XLM has long been superseded by Microsoft Visual Basic for Applications (VBA).

2）C API and XLLs: DLLs that are integrated with Excel. Provides the most direct and fastest interface for the addition of high-performance worksheet functions, although at the cost of some complexity compared with later technologies.

3）VBA: Visual Basic code objects that are associated with Excel workbook objects. Allows event trapping, customization, and the addition of user-defined functions (UDFs) and commands. VBA is the most commonly used and most easily available of the extensibility options.

4）COM: The interoperability standard for Windows-based applications, through which Excel exposes its events and objects. VBA uses COM to interact with Excel. Excel exports COM type libraries that can help you create C++ COM code resources and applications that can control Excel externally.

5）The Microsoft .NET Framework: The multilanguage managed code environment designed for rapid application development for distributed environments. The primary programming language for .NET is C#, although many languages can be compiled to the .NET Intermediate Language (IL). Excel 2002 and later versions can access code resources contained within .NET assemblies.

Office integration solutions
COM or VSTO add-ins are earlier Office integration solutions that run only in Office on Windows

see:https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2007/bb687829(v=office.12)

以下6种机制来实现持久化，并讨论每种方法的优缺点：

①利用WLL,XLL插件；

②利用Excel与PowerPoint的VBA插件；

③利用Office的com插件；

④.利用Excel的Automation插件；

⑤利用Office中VBA editor插件；

⑥利用VSTO插件。VSTO是2005年正式引入。

7.Web Add-in。从Office 2013开始支持的新的开发模式

detail:https://www.77169.net/html/59233.html

恶意代码分析之Office宏代码分析

Office 外接程序 vs VSTO 外接程序?

VSTO

VSTO relies on the .NET Framework. COM add-ins can also be written with the .NET Framework.

Office Add-ins cannot be created with .NET Core and .NET 5+, the latest versions of .NET.

This is because .NET Core/.NET 5+ cannot work together with .NET Framework in the same process and may lead to add-in load failures. You can continue to use .NET Framework to write VSTO and COM add-ins for Office.

Microsoft will not be updating VSTO or the COM add-in platform to use .NET Core or .NET 5+.

You can take advantage of .NET Core and .NET 5+, including ASP.NET Core, to create the server side of Office Web Add-ins.

detail：https://docs.microsoft.com/en-us/visualstudio/vsto/create-vsto-add-ins-for-office-by-using-visual-studio?view=vs-2019

Office Web Add-ins.

Create Office Web Add-In Project Step by Step Using Visual Studio 2015

https://www.c-sharpcorner.com/article/create-office-web-add-in-project-using-visual-studio/

Excel中有三种加载宏程序：Excel加载宏，自定义的com加载宏和自动化加载宏

Office通过加载项来扩展功能，加载项可以分为两类：一般加载项、COM加载项。

一般加载项指的是加载宏：通常是用VBA写成的，office套件可以用类似的格式专门保存加载宏，例如正常的Excel文件扩展名为.xlsx，保存宏的文件扩展名为.xlsm。
COM加载项：指的是使用其他编程语言写的Office的插件，例如用C#开发的COM加载项。

	Excel加载宏	自定义的com加载宏	自动加载宏
文件扩展名	.xla或xll	.exe或.dll	.exe、.dll或.ocx
开发工具	VC++或VBA	VC++或VB	VC++或VB
在excel中的操作位置	工具/加载宏	工具/Com加载项	工具/加载宏/自动化
开发难易程度	容易	困难	困难

if you have a VBA application that is working perfectly, is documented and controlled

How to call a C# function from VBA and Excel

VSTO doesn't provide anything for developing user-defined functions for Excel. You need to create such projects on your own - it can be an automation add-in or XLL one. Both approaches are well described in the Using C# .NET User Defined Functions (UDF) in Excel article(https://adamtibi.net/07-2012/using-c-sharp-net-user-defined-functions-udf-in-excel).

If your choice is XLL add-ins you may consider using Excel-DNA which simplifies development a lot.

XLA add-in for Excel

You can make a public function in the XLA that returns an instance of the class. Then any workbook that has a reference to the XLA can call that function.

COM Add-ins

A COM Add-in is an in-process COM server (an ActiveX DLL) that must implement the IDTExensibility2 interface.

COM Add-ins must be in-process COM servers that support the IDTExtensibility2 interface;

All COM Add-ins must implement each of the five methods of this interface:

OnConnection, 
OnStartupComplete, 
OnAddinsUpdate, 
OnBeginShutDown, 
OnDisconnection.

Automation Add-ins

Excel supports Automation Add-ins. Automation Add-ins build on COM Add-ins in that functions in Automation Add-ins can be called from formulas in Excel worksheets. Automation Add-ins can be in-process or out-of-process COM servers and implementation of IDTExtensibility2 is optional.

不同版本的Office 的Microsoft.Office.Interop.Excel

EXCEL2000（Microsoft Excel 9.0 Object Library）
EXCEL2002（Microsoft Excel 10.0 Object Library）
EXCEL2003（Microsoft Excel 11.0 Object Library）
EXCEL2007（Microsoft Excel 12.0 Object Library）
EXCEL2010（Microsoft Excel 14.0 Object Library）
EXCEL2013\2016（Microsoft Excel 15.0 Object Library）

Excel 2002
Key:            HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Excel\Options
String:         OPENx
Sample Value:   /A "ServerName.ClassName"

 Excel 2003
Key:            HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options
String:         OPENx
Sample Value:   /A "ServerName.ClassName"
Excel 2007
Key:            HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Options
String:         OPENx
Sample Value:   /A "ServerName.ClassName"

detail：https://support.microsoft.com/en-us/topic/excel-com-add-ins-and-automation-add-ins-91f5ff06-0c9c-b98e-06e9-3657964eec72

Controlling Excel from External Applications

To Work With Excel Workbooks, You Can Do Through Three Different Ways。

way 1：If you know the machine you are running on has Office, you can use Excel Interop.

way 2：If there is no guarantee that Excel will be present on the machine, then use 3rd party product that can read Excel files.

You need 3rd party library which acts as an interface between your program and the Excel.

实际C#项目中经常会涉及到需要对本地Excel文件进行操作，常用的操作Excel文件的方法主要有三个：

1.通过ODBC或者OLE，ADO方式读取       -Microsoft.ACE.OLEDB.12.0
2.通过Office自带的COM组件方式读取      -Microsoft.Office.Interop.Excel
3.通过第三方库方式读取                           -NPOI，EPPLUS etc

You can use OleDb data providers for Excel which comes for free with Windows. But there is one limitation though, you can access only data using this technique. You cannot do formatting through this technique.

You can make use of Excel InterOp Objects, but this requires you to have Excel installed in the development environment. This is a binding if you are going to make a product which is to be distributed.

You can use XML to create Excel objects which will open in MSExcel correctly. This is easier, just you need to work with XML through programming. It also supports XML stylesheets. I will also try to discuss this in another article, for the time being, you may look into ExcelXMLDemo.

https://www.codeproject.com/Articles/37055/Working-with-MS-Excel-xls-xlsx-Using-MDAC-and-Oled

通过ODBC或者OLE、COM对Excel表格的读取操作

1. OleDb

把Excel文件看成一个数据库，通过OleDb的方式进行读取与操作。

优点：实现方式简单，读取速度快；

缺点：读取Excel数据的过程不太灵活，对内存的占用比较高，当数据量变的很大时，容易由于内存空间不足导致内存溢出异常。（不过貌似对于今天电脑的硬件配置来说，内存问题不大）

Microsoft.Jet.OLEDB vs Microsoft.ACE.OLEDB.12.0

1、Jet 引擎，可以访问 Office 97-2003，但不能访问 Office 2007。

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

3、Microsoft.ACE.OLEDB.12.0 可以访问正在打开的 Excel 文件，而 Microsoft.Jet.OLEDB.4.0 是不可以的。

4. Microsoft.ACE.OLEDB.12.0 由AccessDatabaseEngine_x64.exe提供

5.If you use the Office 2007 version of the Northwind database, you must use this line of code with the following line of code::
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _       
   sNWind & ";"

This example use the Microsoft.Jet.OleDb.4.0 provider to open and read the Excel file.

TheMicrosoft.Jet.OleDb.4.0 Provider supports the following database types for Microsoft Excel workbooks:

Excel 3.0

Excel 4.0

Excel 5.0

Excel 8.0

NOTE: Use the Excel 5.0 source database type for Microsoft Excel 5.0 and 7.0 (95) workbooks and use the Excel 8.0 source database type for Microsoft Excel 8.0 (97) and 9.0 (2000) workbooks.

However, if the file is of type xlsx (from Excel 2007 and later), then you need to download the Microsoft Access Database Engine components and install it on the target machine.

The provider is called Microsoft.ACE.OLEDB.12.0;. Pay attention to the fact that there are two versions of this component, one for 32bit and one for 64bit. Choose the appropriate one for the bitness of your application and what Office version is installed (if any). There are a lot of quirks to have that driver correctly working for your application. See this question for example.

//采用OleDB读取EXCEL文件
//把EXCEL文件当做一个数据源来进行数据的读取操作，实例如下

public DataSet ExcelToDS(string Path) 
{ 
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; 
OleDbConnection conn = new OleDbConnection(strConn); 
conn.Open();   
string strExcel = "";    
OleDbDataAdapter myCommand = null; 
DataSet ds = null; 
strExcel="select * from [sheet1$]"; 
myCommand = new OleDbDataAdapter(strExcel, strConn); 
ds = new DataSet(); 
myCommand.Fill(ds,"table1");    
return ds; 
}

源码：https://www.w3cschool.cn/csharp/csharp-4la92p9w.html

what is the difference between ADO OLE DB and ODBC data sources?

OLEDB和ODBC的区别

ADO 调用 OLEDB，OLEDB 调用ODBC

ODBC(开放数据库互连)是ADO的前身:ODBC是Microsoft引进的一种早期数据库接口技术。它实际上是ADO的前身。早期的数据库连接是非常困难的. 每个数据库的格式都不一样,开发者得对他们所开发的每种数据库的底层API有深刻的了解. 因此,能处理各种各样数据库的通用的API就应运而生了. 也就是现在的ODBC(Open Database Connectivity), ODBC是人们在创建通用API的早期产物. 有许多种数据库遵从了这种标准,被称为ODBC兼容的数据库.

The application uses ADO to talk to the OLE DB Provider for ODBC, which then loads the appropriate ODBC driver. The driver passes the SQL statement to the DBMS, which retrieves the data.

OLEDB(对象链接和嵌入数据库)位于ODBC层与应用程序之间. 在你的ASP页面里,ADO是位于OLEDB之上的"应用程序". 你的ADO调用先被送到OLEDB,然后再交由ODBC处理. 你可以直接连接到OLEDB层,如果你这么做了,你将看到服务器端游标(recordset的缺省的游标,也是最常用的游标)性能的提升.

例如：
连接EXCEL语句的OleDB
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +"Data Source="+ Path +";"+"Extended Properties=Excel 8.0;"; 
连接Oracle语句OleDB：
    cnn.Open "Provider=msdaora;DataSource=dl580;User Id=username;Password=userpasswd;"

what is the difference between ADO OLE DB and ODBC data sources? （language chinese）

C#用Microsoft.ACE.OLEDB.12.0读取excel表格，组装json,最终存入mongodb

源码：https://blog.csdn.net/ios99999/article/details/77970610

C#读取Excel代码(通过OLEDB方式读取)

https://www.cnblogs.com/aizai846/p/10980626.html

2. 使用Office自带的库（调用Excel的COM组件）

使用条件：只支持Office 2013 及以前的版本和只支持.Net Framework 框架，不支持 .Net core 框架

1)First, you have to have Office installed on the machine

2)In Reference Manager, use Browse... (at the bottom) to find the DLL file:
C:Windows\assembly\GAC_MSIL\Microsoft.Office.Interop.Excel\15.0.0.0...\
Microsoft.Office.Interop.Excel.dll
Add

这种方式是通过Com组件 Microsoft.Office.Interop.Excel.dll实现Excel文件的操作。

一个.NET组件事实上是一个.NET下的DLL，它包含的不仅是运行程序本身，更重要的是包含这个DLL的描述信息（Meta Data,即元数据），而一个COM组件是用其类库（TLB）储存其描述信息。这些COM组件都是非受管代码，要在Visual C＃中使用这些非受管代码的COM组件，就必须把他们转换成受管代码的.NET组件。所以在用Visual C＃调用Excel表格之前，必须完成从COM组件的非受管代码到受管代码的类库的转换。

微软的Excel对象模型包括了128个不同的对象，从矩形，文本框等简单的对象到透视表，图表等复杂的对象．下面我们简单介绍一下其中最重要，也是用得最多的四个对象。
　　(1) Application对象。Application对象处于Excel对象层次结构的顶层，表示Excel自身的运行环境。
　　(2) Workbook对象。Workbook对象直接地处于Application对象的下层，表示一个Excel工作薄文件。
　　(3) Worksheet对象。Worksheet对象包含于Workbook对象，表示一个Excel工作表。
　　(4) Range对象。Range对象包含于Worksheet对象，表示Excel工作表中的一个或多个单元格。
　C#中的受管代码和非受管代码

优点：读取Excel数据非常灵活，可以实现Excel具有的各种数据处理功能；

缺点：对数据的访问时基于单元格方式实现的，所以读写数据较慢，特别是当数据量较大时，访问效率问题更为突出。另一点是要求本机安装了Microsoft Office组件。

see：https://www.cnblogs.com/JacobHuang/p/5663926.html

源码：https://www.w3cschool.cn/csharp/csharp-4la92p9w.html

https://www.cnblogs.com/kenjiang/p/10790652.html

3.第三方库(third-party tools/libraries)

Rather than using Office Interop, use a library which creates an Excel file directly. That way, it doesn't matter which version of Office your users have installed - your code will still work if they don't have Office installed at all.

3. 1 NPOI

这种方式是通过NPOI库实现Excel文件操作，可以在没有安装微软Office的情况下使用。

优点：读取Excel数据速度较快，操作方式灵活；

3.2 使用ClosedXml

3.3 使用free spire.xls

Free Spire.XLS for .NET to read and write Excel files and manipulate formulas as well. This library does not require MS Excel to be installed on either developer's or client's machines.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Excel-Formula.html

3.4 EPPLUS

https://www.cnblogs.com/kenjiang/p/10790652.html

EPPlus - Create advanced Excel spreadsheets using .NET 6

https://github.com/JanKallman/EPPlus

注意一些简单的问题

1.excel文件只能存储65535行数据，如果你的数据大于65535行，那么就需要将excel分割存放了。

2.关于乱码，这主要是字符设置问题。

3.5 DocumentFormat.OpenXml

If you are unwilling to pay for a 3rd party product, you could use the OpenXML SDK that comes free from Microsoft.

You can open modern Excel files (e.g. Files created with Office 2007 and later with the .xlsx or .xlsm extensions). It has a steep learning curve, though and not something I would wish on anyone.

Stop and consider if those Excel sheets can be exported to .CSV files. Reading in .CSV files is infinitely easier.

Read and Create/Write Excel file in C# .NET 5.x using DocumentFormat.OpenXml

3.6 Aspose （pay）

A lot of groups in my company have had a lot of success with Aspose.

useful links

Microsoft Office Visio Addin based on .NET 5 (core) example。

example implementation of and add-in based on .NET 5 (for Visio, with setup on WIX) Should compile, and work.

Adds a single clickable button to the ribbon:

https://github.com/nbelyh/VisioComAddinNet5

Office download

1.office 2010 下载及安装教程

https://pan.baidu.com/s/1jGwY6pw

Table of Contents