In the past, VBA was used for Secondary Development of Excel. It is a simplified version of VB. After opening Excel, We can click the tool -- macro -- Visual Basic Editor to perform secondary development. What we are talking about now is not the secondary development of VBA, but the secondary development under. net.
Our ultimate goal is to use. NET provides development tools to quickly achieve secondary development of Excel, including the most basic cell and band data access, merger, font color, border and grid lines, etc., from simple to deep, from basic implementation to combination, the report design, nesting, and template printing are implemented. The powerful functions of Excel are used to analyze any complicated formats, reports, and statistical graphs.
Starting from this article, we will gradually explore the method of Secondary Development of Excel. net, and gradually introduce the secondary development of Excel and implement the following:
1. How to convert various versions of Excel (com-Type Library) to generate a. net public Language Runtime Library program set such as InterOP. Excel. dll
2. How to reference an Excel library or generated assembly
3. Create an Excel instance
4. Open the Excel worksheet and template
5. add, delete, modify, insert, locate, and rename a worksheet
6. Obtain the specified range cells and band of the activity worksheet.
7. Read and Write cell content
8. Write content to the band
9. Merge Cells
10. font and color
11. Borders and gridlines
12. Save and save
13. Print and preview
14. Close the Excel process completely
15. Comprehensive application, Global Reporting
This article describes how to add an Excel reference to a project. In the subsequent articles, it is assumed that the reading has a certain understanding of the vs 2003 Development Platform. Therefore, omitting some text, such as creating a new project or something, is unnecessary.
My purpose: simple, fast, easy to use, and practical. Therefore, in the future articles, there will be a majority of programs, with as few descriptions as possible and more comments. But this article, haha, is only described.
Right-click the reference node of the project, and click Add reference in the pop-up menu...
In the displayed dialog box, click com to find the Microsoft Excel 11.0 Object Library.
Click Select, and then click Select to "reference excel2003 ".
We can see that the Excel. EXE file is referenced. This is excel2003. Excel2000 and excel2002 are excel XP
For com items, see "Excel version table ".
After excel2003 is referenced, an Excel or other node will be added to the reference node. "You can delete unnecessary references after excel2003 is referenced ".
Enter the bin/DEBUG directory of the project and you can see that InterOP is added accordingly. excel. DLL, InterOP. microsoft. office. core. DLL, InterOP. vbide. DLL files.. Net converts the type definition in the com Type Library.. net public Language Runtime Library Equivalent Definition of the files generated.
We only need to reference InterOP. excel. DLL. The following two files support files, if a syntax error occurs during program debugging without adding references to these two files, the system prompts that the dependency InterOP cannot be found. office, InterOP. vbide, this is generally an illusion, unless you and the program actually use them, if there is no syntax error, the compiler will not prompt :).
In the reference, Microsoft. Office. Core, stdole, and vbide are selected and deleted. You can also delete the Excel reference and then directly reference InterOP. Excel. dll.
Another method is to directly Add a reference to InterOP. Excel. dll.
Another method for generating InterOP. Excel. dll:
The above is the visual Automatic Generation of the Excel corresponding assembly, you can also use the following method to generate.
Open the Visual Studio. net2003 command prompt and run
Tlbimp excel. EXE InterOP. Excel. dll
If it is excel2000, then
Tlbimp excel9.olb InterOP. Excel. dll
Of course, the first parameter excel. EXE (excel9.olb) must specify the directory (Office2000 installation directory). If it is not specified, copy it to the Vs. net installation path.
Such as C:/Visual Studio. NET/SDK/V1.1/bin;
Similarly, the second parameter is InterOP. excel. DLL is the corresponding dynamic library generated. If no directory is specified, it is generated in vs by default. net installation path bin, you can use your favorite name
Replace InterOP. Excel. dll. After it is generated, you can add references to it in the Vs. Net Development Environment Project.
Tlbimp.exe is the type library import program, which converts the type definition in the com Type Library to the equivalent definition in the Common Language Runtime Library set. For details about its usage, see msdn.
The library referenced by the first method and the second method and the default file directory are as follows:
Excel 2000 is
Microsoft Excel 9.0 Object Library
C:/program files/Microsoft Office/office/excel9.olb
Excel 2002 (XP) is
Microsoft Excel 10.0 Object Library
C:/program files/Microsoft Office/office10/Excel. exe
Excel 2003 is
Microsoft Excel 11.0 Object Library
C:/program files/Microsoft Office/office11/Excel. exe
---------------------------------------- Excel version table ----------------------------------------
|File/version||InterOP. Excel. dll||InterOP. Office. dll||InterOP. vbide. dll||Add reference/COM components|
|2000||V18.104.22.168||V22.214.171.124||V126.96.36.199||Microsoft Excel 9.0 Object Library (excel9.olb)|
|2002 (XP)||V188.8.131.52||V184.108.40.206||V220.127.116.11||Microsoft Excel 10.0 Object Library (Excel. EXE file)|
|2003||V18.104.22.168||V22.214.171.124||V126.96.36.199||Microsoft Excel 11.0 Object Library (Excel. EXE file)|
For information on source code download, visit: