.Netread/Write exceltool spire.xlsuse tutorial (1),.netspire.xls

.Netread/Write exceltool spire.xlsuse tutorial (1),.netspire.xls

On the. NET platform, operating Excel files is a very common requirement. The following common methods are available:

1. How to use the Office Com component:This method is very tiring. Microsoft's things are always so complicated that it may be inconvenient to use. You need to install Excel. for servers, you sometimes need to configure IIS permissions. Tossing people, seeing many people use this in the group, there are various crazy mistakes;

Thank you very much for the reply from the free PowerShell software. It uses powershell + excel 2007 to solve the problem. As a matter of fact, since the Office has provided support for com components, it must have been taken into account in all aspects. It is certainly no problem to use it, but the learning cost is very high, everyone has limited energy and technical capabilities, so they are too reluctant to go over and study again. There are certainly some solutions. The pure NPOI and Spire operations allow more developers to quickly master and improve efficiency. They have their own advantages.

In addition, the restrictions and charges for the free version are not well handled for the time being. Haha, you know.

2. Open-source. NET Component NPOI mode:This method should be used by many people. This open-source component is a little simpler than the first one (2003 supports better, and the new version also supports Xlsx ), but it is still very useful. Recently, When I browsed NPOI's open source website, I suddenly found that Spire was actually a sponsor of NPOI .. Lianyi, mutual help, serving the masses, unlike the domestic virus, penguins, fighting, mutual promotion, and blocking, It's really pitiful. Look at the NPOI Codeplex Website:

The. NET platform Excel component Spire. XLS introduced today is better and more stable than the above two methods. Let's take a look at the basic introduction:

1. Spire. Office for. NET

Spire. office. NET is an enterprise level developed by E-iceblue. NET Office editing software set, including Spire. doc, Spire XLS, Spire. PDF, Spire. barCode and so on .. NET platform developers can use Spire. office.. NET can easily operate applications that contain the document editing function. It can also be exported to MS Word/Excel/RTF/Acces, PDF, XPS, HTML, XML, and other formats, using Spire. microsoft Office does not need to be installed. Although Spire. Office for. NET is a commercial product, each of its basic functional components provides a free version. Visit the official website: http://www.e-iceblue.com /. Although there are some restrictions, you can try something simple. The Spire. XLS introduced in this article is only a component for operating Excel under Spire. Office.

2. Spire. XLS features

Spire. XLS for. NET SupportAll Excel format types (97-2003,200 7, 2010)File. With Spire. XLS for. NET, developers can quickly and conveniently view, generate, read and write, and customize and edit Excel files. Spire. XLS for. NET has the following main functions:

1. Supports Excel file creation, modification and editing, cell splitting and merging, attribute setting, Lock encryption, and so on;
2. You can export data from a database to an Excel worksheet or import data from an Excel worksheet to a database;
3. provides powerful cell grids, formatting numbers, text indentation, positioning and adjustment, cell filling, and protection;
4. Supports writing formulas, tables, images, different types of text, links, comments, and even VBA to Excel files;
5. Converts Excel files to PDF, HTML, CSV, Text, XML, and image formats.

Many versions of Excel files are supported. You can see the figure below. The following is a list of members of the enumeration type ExcelVersion:

Spire. XLS also provides the free version: Release. Because I use a genuine version, the following demonstration does not solve this problem.

3. Write a Hello Excel File

Since Microsoft's Office is too huge, I have abandoned it for a long time. Over the past few years, WPS has been used in China, and the effect is still good. It is mainly small and only a few dozen MB, common functions are not inferior to Office functions. It has always been really convenient and free. Second, the current software is competing for users. The software is so huge that the memory usage is also increasing. In fact, users suffer. A huge Office is not a special requirement. Try not to make it as easy as possible.

I am using Spire. Office 2.1 version, because the Unit has genuine, all do not use free version, system win7-32bit + WPS latest grab fresh version. The tool uses VS2013 and the project type. NET 4.0 (of course, the component itself supports many environments, such as. NET 2.0, 3.5, 4.0, 4.0 ClientProfile, Silverlight3/4/5, and WPF ).

1. Basic preparation

The first step is not to mention creating a project. It is mainly to reference the dll file of the corresponding version and add a reference to the namespace:

2. Write basic code

Hello world is now popular. before using this, you can simply experience Hello Excel. The code is very simple and annotated.

Using System; using Spire. xls; // This is a major namespace XlsTest {class Program {static void Main (string [] args) {HelloExcel (); Console. readLine ();} static void HelloExcel () {// create a workbook, the document structure equivalent to one Excel file // Excel is Workbook-> Worksheet (one book can contain multiple sheets) Workbook workbook = new Workbook (); // obtain the first sheet and perform the operation. The subscript is Worksheet sheet = workbook starting from 0. worksheets [0]; // Of course, you can add a named Worksheet to the workbook. work Sheets. Add ("test sheet"); // write the Text sheet. Range ["A1"]. Text = "Hello, World! "; // Save the Excel file to the specified file. You can also specify the Excel version workbook. SaveToFile (" Sample.xls ", ExcelVersion. Version97to2003 );}}}

3. Check the effect.

The code is very simple. Let's look at the actual results. 2 places, 1 is text, 1 is new Sheet, and the default Excel file is 3 Sheet.

4. Differences from Microsoft Com operations and NPOI operations

Currently, the use of Microsoft com operations and NPOI components are the most common. Of course, many netizens have shared articles about using commercial components. I have already introduced Microsoft's com operations before the beginning of this article. The biggest headache is the dependency on office, which is highly demanding on the environment. Many people give up on this, open-source or commercial operating components independent of the Office environment, such as NPOI and Spire. Since I have never used the com method of Microsoft Office to operate documents, I will not compare it too much here. The key point is to compare the difference between the previous use of NPOI and the current Spire. XLS.

1. NPOI began to support Excel in Office 2.0 after version 2014 (which should have been released in 2007). Many features are relatively weak, and the support for Office 2003 is relatively complete, I only used 2003 in my previous work;

2. Spire. XLS provides much improved support for the Excel version. The above also shows that the support for Office 2010/2013 is very complete, which is also the main reason for using it this time;

3. when I used NPOI 2.0 or earlier, I used NPOI for complex reports. However, I have a deep feeling that NPOI has many namespaces and is hard to remember, for example, HSSF, POIFS, and SS are not intuitive enough, so they often cannot be found, especially during post-maintenance;

4. NPOI does not support generating charts in Excel. It seems that NPOI versions earlier than 2.0 are not supported at all. I have not tried any new version yet. I don't know what the situation is. One reason why I use Spire. XLS is that the chart function should be generated in the automatically generated report, which is very well supported by Spire. XLS;

5. NPOI is very accurate and core in terms of functions. As an open-source component, NPOI is already very good. For some auxiliary operations on Excel files, such as file format conversion and data export and import, Spire. XLS is better. Of course, these extensions can also be completed through NPOI.

6. For other advantages and disadvantages and usage issues, we will have a more in-depth comparison after completing this project. I will share it with you later. You are also welcome to add this article.

5. Resources

NPOI open source Website: http://npoi.codeplex.com/

Spire. Office official free version: http://www.e-iceblue.com/Introduce/free-xls-component.html

Next article: [original]. NET Excel Reading and Writing Tool Spire. Xls (2) Excel file control is coming soon, so stay tuned.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.