0. Import namespace:
using ; using ; using ; using ;
1. How to open an existing excel document or create a new excel document
Application app = new Application(); Workbooks wbks = ; _Workbook _wbk = (xxx);
If you open an existing excel, replace "xxx" with the file path of the excel;
Note: If you create a new Excel document, replace "xxx" with true; however, the newly created Excel document here has only one sheet by default.
2. Get, delete and add sheet
Sheets shs = _wbk.Sheets;
2.1 Obtained:
//i is the index of the sheet to be obtained_Worksheet _wsh = (_Worksheet)shs.get_Item(i)
2.2 Delete:
//Delete the necessary settings for sheet = false; _wsh.Delete();
2.3 Added:
//a(before), b(after): determine the addition position; c: number; d: type(a，b，c，d);
2.4 Rename sheet
_wsh.Name = "xxx";
3. Delete rows and columns
3.1 Delete the line:
((Range)_wsh.Rows[3, ]).Delete();
3.2 Delete columns:
_wsh.get_Range( _wsh.Cells[1, 2], _wsh.Cells[_wsh., 2]).Delete( );
4. Add rows and columns
4.1 Add line:
((Range)_wsh.Rows[11, ]) .Insert(, );
4.2 Add columns:
_wsh.get_Range( _wsh.Cells[1, 1], _wsh.Cells[_wsh., 1]) .Insert(, );
5. Cell operations
5.1 Acquisition of cell
//Get cell object_wsh.Cells[row, cell]
5.2 Setting the formula
//Enter the formula in the corresponding cell_wsh.Cells[row, cell] = "=Sum(A1/B1)";
5.3 Merge cells
((Range)_wsh.Rows[1, ]).Merge();
5.4 Set row height and column width
((Range)_wsh.Rows[3, ]).RowHeight = 5; ((Range)_wsh.Rows[3, ]).ColumnWidth = 5;
5.5 Set cell color There are 56 colors in total. Please refer to the [Color Comparison Table] in the appendix for details.
((Range)_wsh.Rows[1, ]). = 3;
5.6 Set the font size
((Range)_wsh.Cells[1, "B"]). = 8;
5.7 Whether to set bold
((Range)_wsh.Rows[1, ]). = false;
5.8 Cell/area, horizontal and vertical centering
((Range)_wsh.Rows[1, ]). = false;
5.9 Setting area borders
((Range)_wsh.Cells[3, 3]). = 3;
5.10 Set the upper, lower, left and right lines of the border
//Left_wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[].Weight = ;// //right_wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[].Weight = ;// //superior_wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[].Weight = ;//Down //Down_wsh.get_Range( _wsh.Cells[2, 1], _wsh.Cells[2, 2]) .Borders[].Weight = ;
6. Copying of the specified area
_Worksheet _wsh = (_Worksheet)shs.get_Item(1);//Copy the content of the selected area Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.]); (); (); //Select the starting position of pastingRange test = ((Range)_wsh.Cells[11, 1]); (); //Shield Alert, and paste by default = false; (, );
Note: and, in Excel operations, are regarded as the default values of certain parameters, and their role is often used to form the parameters.
Saving and subsequent processing of files
7.1 File Saving
//Block the Alert that jumped out of the system = false; //Save to the specified directorySaveAs(filePath, , , , , , , , , , , );
Note: This place can only be saved using this method, otherwise, in addition to saving the file under the specified path, a corresponding copy will be generated in my document.
7.2 Follow-up processing: Exit and release
//_wbk.Close(null, null, null); //(); (); //Release the excess excel process(app); app = null;
Note: During the application closure process, we usually have two solutions:
#Exit the app directly
#First close workbook, then close workbooks, and finally exit the app
Given these two methods, it may be essentially the same (this point needs to be proved), but according to our software development principle: declare wherever you need, and where you end, where you release the recycling.
Since we are directly quitting the app, we don’t know when the workbook and workbooks are closed. It is better to manually close it at the end. This can achieve rapid and direct recycling of resources;
Therefore, it is recommended to close the workbook first, then close the workbooks, and finally exit the app.
8. Regarding cell setting and obtaining the required data in the field
8.1 If the cell has been set to the drop-down box
//The "1, 2, 3" here sets the value of the drop-down box((Range)_wsh.Cells[2, 1]) .(, , , "1,2,3", );
8.2 If the cell has not been set to the drop-down box
((Range)_wsh.Cells[2, 1]) .(, , ,"1,2,3", );
8.3 Get the value of the drop-down box field
string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;
Note: If the value of the drop-down box is set through validity in the excel template, strValue will get the formula in excel, and it needs to be converted. After obtaining strValue, you can get the value you need based on its index;
9. Hide rows and hidden columns
9.1 Hide the line
_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]). = true;
9.2 Hide columns
_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh., 1]) . = true;
This is the end of this article about the summary of C# operation methods related to Excel. For more related C# operation content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!