. NET Excel Reading and Writing Tool Spire. Xls for data operation and control (4),.netspire.xls

. NET Excel Reading and Writing Tool Spire. Xls for data operation and control (4),.netspire.xls

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.

Previous Article: ". NET Excel Reading and Writing Tool Spire. xls uses (3) Cell Control "to introduce Spire. this article focuses on the control of data when operating Excel files in C. Cells and data are the core of Excel files. It is difficult to separate the two files for further explanation or learning. Sometimes they are cross-cutting. Therefore, some of the content in this article has already been mentioned in the previous article. For example, the cell data editing and modification will not be repeated here.

1. Excel group display

When there is a lot of data, you sometimes need to display it in groups to hide a lot of details. When necessary, click to view the detailed content, which is equivalent to a detailed list. Of course, this may not be the case. In short, it is the grouping function of an Excel table, just like the following. Pay attention to the changes in the row number. The row grouping of the middle row number is hidden:

The following describes how to use Spire. XLS is also very simple to set group operations in a program. This is the benefit of commercial components. The function is meticulous and one function is used to implement the desired function, the GroupByRows method of the sheet object is used, as shown in the following example:

Static void ExcelTest6 () {Workbook workbook = new Workbook (); // obtain the first sheet and perform the operation. The subscript is Worksheet sheet = workbook starting from 0. worksheets [0]; sheet. groupByRows (2, 9, true); // The Last bool parameter indicates whether to collapse the workbook by default. saveToFile ("Sample.xls", ExcelVersion. version97to2003); System. diagnostics. process. start ("Sample.xls ");}

The result is as follows: Group rows 2-9 and fold by default:

2. Quickly populate the array with cells

In the previous article, we were writing data directly to cells, which was complicated to remember. So I would also want to avoid writing all arrays at once. Isn't it faster and more convenient, less code. View Spire. in the official case of XLS, we found such a feature very powerful and very useful for data import: directly inserting a two-dimensional array at a specified position, batch operation, saves the trouble of cell-by-Cell operations. Take a look at the following code:

Static void ExcelTest7 () {Workbook workbook = new Workbook (); workbook. createEmptySheets (1); Worksheet sheet = workbook. worksheets [0]; int maxRow = 10; int maxCol = 5; // generate the test data array object [,] myarray = new object [maxRow + 1, maxCol + 1]; bool [,] isred = new bool [maxRow + 1, maxCol + 1]; for (int I = 0; I <= maxRow; I ++) {for (int j = 0; j <= maxCol; j ++) {myarray [I, j] = I + j; if (int) myarray [I, j]> 8) isred [I, j] = true ;}// Insert the array to sheet. The following parameters are the start row and column number sheet. insertArray (myarray, 1, 1); workbook. saveToFile ("test.xls"); System. diagnostics. process. start ("test.xls ");}

The effect is shown in. The result is incomplete... You can write data in other formats:

3. search and replace Data

When processing a large amount of data, it is common to search and replace sheet in Excel. Although there are many manual operations, what are program operations? In fact, it is quite easy to search for sheet objects and find the corresponding cells using the FindAllXXX method. Note that XXX represents different data types, as shown in figureFindAllBool, FindAllDateTime, FindAllNumber, FindAllStringAnd so on. Let's look at an example to demonstrate how to directly search and replace and modify the color of the corresponding cell:

CellRange [] ranges = sheet. findAllString ("test", false, false); // The cell foreach (CellRange range in ranges) {range. text = "modified"; range. style. color = Color. yellow ;}

4. Data filter creation and Cancellation

What is a filter? Most people may not pay attention to it. Of course, if Excel is often used, the filter is essential. It is to filter the columns and automatically list the options. Then, the operator selects the options as needed to display the data of the specified conditions, which is equivalent to a dynamic query process. Excel is very fast and convenient, which is the following effect:

The actual code is also very simple. The following code shows the meaning of the method prototype. Select a table and a range to set the style.

Workbook workbook = new Workbook (); workbook. loadFromFile ("test.xlsx"); Worksheet sheet = workbook. worksheets [0]; // create a filter sheet. listObjects. create ("Table", sheet. range [1, 1, sheet. lastRow, sheet. lastColumn]); sheet. listObjects [0]. builtInTableStyle = TableBuiltInStyles. tableStyleLight9; workbook. saveToFile ("Filter.xlsx", ExcelVersion. version2010 );

Of course, sometimes you also need to cancel the filter. You only need the following clearing operation for the filter:

sheet.AutoFilters.Clear();

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