. NET Excel Reading and Writing Tool Spire. Xls use excelcell control (32.16,.netspire.xls

. NET Excel Reading and Writing Tool Spire. Xls use excelcell control (32.16,.netspire.xls

Previous Article: ". NET Excel Reading and Writing Tool Spire. xls use (2) Excel file control "introduces C # Use Spire. XLS to control the basic functions and related practical code of Excel files. This article focuses on the control of Excel cells when operating Excel files in C.

In the past, when using NPOI, We were most impressed with cell operations, because the main task of using NPOI was to generate a complex statistical report, which not only complicated the header, in addition, the control of row categories is also complicated, not fixed, and should be carried out dynamically. In addition, the borders of different cells are different, so NPOI also took a lot of detours. Cell Control is the core of writing Excel files. So this article introduces the control summary of related cells in the use of Spire. XLS.

1. Excel cell Overview

Excel cells are the smallest unit for table editing and the subject of operations. The following functions are involved in daily operations:

1. Read and Write content to a specified cell: some operations can be performed to read and write content, such as merging the cell type;

2. Control the cell format, such as numbers, strings, text color, and font size. This requirement is common for Custom reports and other files;

3. It is also common to control cell styles, such as border lines (style, color), content alignment, and cell merging;

4. Cell-related statistics, such as some functions and sum statistics functions;

5. For cell protection functions, refer to the previous article. NET Excel Reading and Writing Tool Spire. Xls (2) Excel file control

The following content mainly demonstrates the above four main purposes and comes with some actual code. If you have any questions, leave a message.

2. Read and Write cell content

Read and Write cells in Spire. XLS. The main object cannot be unknown, that isCellRangeIt indicates a range set of cells. You can return the set of cells based on cell names such as A1 and B2, or access the set according to the row number and column number (subscript starts from 1. For example, in the following code, assign values to cell A1 directly and then assign values to cell 2nd in the first row. Compared with NPOI, it is much more convenient to assign values without stopping Create. Check the Code:

Static void ExcelTest5 () {// create a Workbook, which is equivalent to an Excel file workbook Workbook = new Workbook (); // obtain the first sheet for operations, the subscript is Worksheet sheet = workbook starting from 0. worksheets [0]; // write a text sheet to cell A1. range ["A1"]. text = "I'm cell A1"; sheet. range [1, 2]. text = "1st cells in rows 2nd"; // Save the Excel file to a specified file. You can also specify the Excel version workbook. saveToFile ("example .xls", ExcelVersion. version97to2003 );}

View results:

If you want to access the value of the corresponding cell, you can directly use the aboveCellRangeAfter obtaining the cell object, directly access its attribute Value, orText, NumberValueTo obtain the value of the type you want. You do not need to convert the value by yourself. But you need to know the cell format. The following code is used:

 Console.WriteLine(sheet.Range["A1"].Value); Console.WriteLine(sheet.Range["B1"].Text);

3. cell content format and style

Data Type of cell 3.1

In the process of using NPOI, not only do cells need to be frequently created, but also the format of the written data needs to be set. In Spire. XLS, these become simpler. It is very concise and clear to directly give the specified attribute based on the data type. For example, the following code directly writes double-precision, date, and boolean data to cells, and assigns values to the NumberValue, DateTimeValue, and BooleanValue attributes.

// Write the text sheet to cell A1. range ["A1"]. text = "I'm cell A1"; sheet. range [1, 2]. text = "I am the 1st cells in the 2nd rows"; sheet. range ["A3"]. numberValue = 100.23; sheet. range ["A4"]. dateTimeValue = DateTime. now; sheet. range ["A5"]. booleanValue = true;

Isn't it very simple? A few lines of code may have to be done in the past, and it also has a headache when it is too much.

Font format of cell 3.2

In the previous NPOI style control, you must create a new style object and set it in Spire. in XLS, you can directly set the Range attribute, which is very simple and clear. Let's see how we control the font format of cells in the Range and only list several attributes, other attributes and so on:

1 // control the font of cells within a certain range
2 sheet. Range ["A1: B10"]. Style. Font. FontName = ""; // Font name
3 sheet. Range ["A1: B10"]. Style. Font. Size = 20; // Font Size
4 sheet. Range ["A1: B10"]. Style. Font. Underline = FontUnderlineType. DoubleAccounting; // Underline type
See the results:

Is it simpler, and the Range selection is flexible.

3.3 merge Cells

Merging cells is useful when using Excel for reporting. It is also very easy to operate merging cells in C.

// Merge the cells of the A5-B6 into sheet. Range ["A5: B6"]. Merge (); // Merge all Rows into sheet. Rows [7]. Merge ();

Note: To merge a row, instead of merging all cells in a row, it is merged to the last column by default (the column with the largest value ). The effect is as follows:

Sometimes you need to cancel merging cells. In fact, the principle is the same as above.UnMergeMethod. We will not demonstrate it here.

Other functions of cell 3.4

In fact, there are still many cell operations, such as copying and applying the format to other cells, hiding rows or columns, and inserting rows or columns. These operations are actually very simple, you can refer to the tutorials provided on the official website. I will not introduce them in detail because I have used them less. If I can use them myself in the future, I will also refer to these documents: Official tutorials.

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