C # operate an Excel worksheet

Add reference: Microsoft Excel 11.0 Object Library;
Add: using Microsoft. Office. InterOP. Excel;
★Open an Excel file ==============================
Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. open (@ "E: \ aaa.xls", type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing );
Excel1.visible = true;
★Create an Excel Object ======================================
Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. Add (xlwbatemplate. xlwbatworksheet or true );
Worksheet1.activate (); // activate the table sheet1
Excel1.visible = true;
★Create an Excel table ======================================
Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. Add (true );
Worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
Worksheet worksheet1 = (worksheet) workbook1.worksheets. Add (type. Missing, workbook1.worksheet [1], 1, type. Missing );
Excel1.visible = true;

★Save Excel ==================================

Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. Add (true );
Worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
Worksheet1 = (worksheet) workbook1.worksheets. Add (type. Missing, workbook1.worksheets [1], 1, type. Missing );
Worksheet1.activate ();
Worksheet1.cells [2, 2] = 3455555;
Excel1.visible = true;
Excel1.displayalerts = false; // The prompt box is not displayed.
Workbook1.close (true, "d: \ 1.xls", null );
// Close

Worksheet1 = NULL;
Workbook1 = NULL;
Excel1.quit ();
System. runtime. interopservices. Marshal. releasecomobject (excel1 );
Excel1 = NULL;
System. gc. Collect ();

★Close Excel====================================

Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. open (@ "E: \ aaa.xls", type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing);
excel1.visible = true;

worksheet1 = NULL;
workbook1 = NULL;
excel1.quit ();
system. runtime. interopservices. marshal. releasecomobject (excel1);
excel1 = NULL;
system. GC. collect ();
★Rename an Excel table name =====================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1" or 1];
worksheet1.name =" Work Schedule ";
excel1.visible = true;
★Set or modify the content of cells in an Excel table ======================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
worksheet1.cells [1, 1] = "name";
worksheet1.cells [1, 2] = "gender";
excel1.visible = true;
★Set the row width and column height of an Excel table ===================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
worksheet1.columns. columnwidth = 20; // global row width
worksheet1.columns. rowheight = 20; // global column height
range range1 = (range) worksheet1.cells [2, 1];
range1.columns. columnwidth = 40; // cell row width
range1.columns. rowheight = 40; // cell column height
excel1.visible = true;
★Set the cell border of an Excel table ===================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
range range1 = (range) worksheet1.cells [2, 2];
Range1.borders. Color = system. Drawing. colortranslator. Toole (color. Red );
Range1.borders. get_item (xlbordersindex. xledgetop). linestyle = xllinestyle. xlcontinuous;
Range1.borders. get_item (xlbordersindex. xledgeright). linestyle = xllinestyle. xlcontinuous;
Range1.borders. get_item (xlbordersindex. xledgebottom). linestyle = xllinestyle. xlcontinuous;
Range1.borders. get_item (xlbordersindex. xledgeleft). linestyle = xllinestyle. xlcontinuous;
// Also available Code Replace the preceding four range1.borderaround (xllinestyle. xlcontinuous, xlborderweight. xlthin, xlcolorindex. xlcolorindexautomatic, null );
Range1.borders. get_item (xlbordersindex. xldiagonaldown). linestyle = xllinestyle. xlcontinuous; // slash
Range1.borders. get_item (xlbordersindex. xldiagonalup). linestyle = xllinestyle. xlcontinuous; // backslash
Range1.borders. get_item (xlbordersindex. xldiagonaldown). Color = system. Drawing. colortranslator. Toole (color. Gold );
Excel1.visible = true;
★Excel table block operations ======================================
Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. Add (true );
Worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
Range range1 = worksheet1.get _ range ("A2", "E8"); // select the action block
Range1.font. Bold = true; // set the blacklist
Range1.font. size = 18; // set the font size
Range1.font. Name = ""; // set the font
Range1.font. Color = system. Drawing. colortranslator. Toole (color. Blue); // set the font color
range1.horizontalalignment = xlhalign. xlhaligncenter; // sets the horizontal alignment
range1.verticalignment = xlvalign. xlvaligncenter; // set the vertical aligncenter
range1.value2 = "123 \ r \ n456";
range1.borders. get_item (xlbordersindex. xledgetop ). linestyle = xllinestyle. xlcontinuous;
range1.borders. get_item (xlbordersindex. xledgeright ). linestyle = xllinestyle. xlcontinuous;
range1.borders. get_item (xlbordersindex. xledgebottom ). linestyle = xllinestyle. xlcontinuous;
range1.borders. get_item (xlbordersindex. xledgeleft ). linestyle = xllinestyle. xlcontinuous;
// you can use the following code to replace range1.borderaround (xllinestyle. xlcontinuous, xlborderweight. xlthin, xlcolorindex. xlcolorindexautomatic, null);
range1.borders. get_item (xlbordersindex. xlinsidehorizontal ). linestyle = xllinestyle. xlcontinuous; // In-block vertical bars
range1.borders. get_item (xlbordersindex. xlinsidevertical ). linestyle = xllinestyle. xlcontinuous; // In-block horizontal line
excel1.visible = true;
★Excel table cell merge =====================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
range range1 = worksheet1.get _ range ("A2", "E8 "); // select the action block
range1.value2 = "123 \ r \ n456";
excel1.application. displayalerts = false; // The merge operation does not prompt a warning
range1.merge (false); // If the parameter is true, each row is merged into a cell.
excel1.application. displayalerts = true;
excel1.visible = true;
★Copy an Excel table =====================================
Microsoft. office. interOP. excel. application excel1 = new Microsoft. office. interOP. excel. application ();
workbook workbook1 = excel1.workbooks. add (true);
worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
worksheet1.cells [1, 1] = "123 ";
Worksheet1.copy (type. Missing, worksheet1 );
Worksheet worksheet2 = (worksheet) worksheet1.next;
// Worksheet2.name = "sheet2 ";
Excel1.visible = true;

 ★Page settings ================================

Microsoft. Office. InterOP. Excel. Application excel1 = new Microsoft. Office. InterOP. Excel. Application ();
Workbook workbook1 = excel1.workbooks. Add (true );
Excel1.caption = "My reports ";
Worksheet worksheet1 = (worksheet) workbook1.worksheets ["sheet1"];
Worksheet1.pagesetup. papersize = xlpapersize. xlpapera3; // paper size
Worksheet1.pagesetup. printtitlerows = "$1: $3"; // top header line
Worksheet1.pagesetup. Orientation = xlpageorientation. xllandscape; // The page direction is horizontal.
Worksheet1.pagesetup. topmargin = excel1.centimeterstopoints (2); // The top margin is 2 cm (centimeter to pixel)
Worksheet1.pagesetup. bottommargin = excel1.centimeterstopoints (2); // The bottom margin is 2 cm (centimeter to pixel)
Worksheet1.pagesetup. leftmargin = excel1.centimeterstopoints (1.5); // The left margin is 1.5 cm (centimeter to pixel)
Worksheet1.pagesetup. rightmargin = excel1.centimeterstopoints (1.5); // The right margin is 1.5 cm (centimeter to pixel)
Worksheet1.pagesetup. headermargin = excel1.centimeterstopoints (1.2); // the header margin is 1.2 cm (centimeter to pixel)
Worksheet1.pagesetup. footermargin = excel1.centimeterstopoints (1); // The footer margin is 1 cm (centimeter to pixel)
Worksheet1.pagesetup. centerhorizontally = true; // The page is horizontally centered.
Worksheet1.pagesetup. centervertically = false; // The page is not vertically centered.
Worksheet1.pagesetup. centerfooter = "Page & P, total & N"; // content of the middle footer
Excel1.visible = true;