Summarize C# Control Excel Skills

• Download source - 6.22 KB

Introduction

This article will demonstrate some basic skills of controlling Excel by using C#, including Create, Open, Save Excel files, Add/Delete Sheet, Add/Delete/Hide rows and columns, format cells and Export data to Excel. I hope this resource is useful for some of you.

0. Import Namespace

using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;

1. How to Open an Excel File, or Create a New Excel File

Application app = new Application();
Workbooks wbks = app.Workbooks;
_Workbook _wbk = wbks.Add(xxx);

Open an existing Excel file, please replace “xxx” with the excel file path.

Create a new Excel file, please replace “xxx” with “true”.

Note: There is only one worksheet in the Excel file.

2. Get, Delete and Add Sheet

Sheets shs = _wbk.Sheets;

2.1 Get

//i is index of the sheet which you want get:
_Worksheet _wsh = (_Worksheet)shs.get_Item(i)

2.2 Delete

//must do when delete
app.DisplayAlerts = false;
_wsh.Delete();

2.3 Add

//a(before),b(after):Besure position;c:amount;d:type
app.Worksheets.Add(a,b,c,d);

2.4 Rename Sheet

_wsh.Name = "xxx";

3. Delete Rows and Columns

3.1 Delete Rows

((Range)_wsh.Rows[3, Missing.Value]).Delete(XlDeleteShiftDirection.xlShiftUp);

3.2 Delete Columns

_wsh.get_Range(
_wsh.Cells[1, 2],
_wsh.Cells[_wsh.Rows.Count, 2]).Delete(XlDeleteShiftDirection.xlShiftToLeft
);

4. Add Rows and Columns

4.1 Add Rows

((Range)_wsh.Rows[11, Missing.Value])
.Insert(Missing.Value, XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

4.2 Add Columns

_wsh.get_Range(
_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.Insert(Missing.Value, XlInsertShiftDirection.xlShiftToRight);

5. Format Cells

5.1 Get Cell

//Get cell object
_wsh.Cells[row, cell]

5.2 Set Formula

//input formula in the cell
_wsh.Cells[row, cell] = "=Sum(A1/B1)";

5.3 Merge Cells

((Range)_wsh.Rows[1, Missing.Value]).Merge(Missing.Value);

5.4 Set Row Height and Column Width

((Range)_wsh.Rows[3, Missing.Value]).RowHeight = 5;
((Range)_wsh.Rows[3, Missing.Value]).ColumnWidth = 5;

5.5 Set Cell Color (56 Choices) Color Table Attached

((Range)_wsh.Rows[1, Missing.Value]).Interior.ColorIndex = 3;

5.6 Set Font Size

((Range)_wsh.Cells[1, "B"]).Font.Size = 8;

5.7 Set Font Bold

((Range)_wsh.Rows[1, Missing.Value]).Font.Bold = false;

5.8 Set Cell or Field Horizontal and Center

((Range)_wsh.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;

5.9 Set Field Borders

((Range)_wsh.Cells[3, 3]).Borders.LineStyle = 3;

5.10 Set Border Lines (Upside, Downside, Left Side and Right Side)

//Left side border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;//
	 
//Right side border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//
	 
//Upside border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;//?
	 
//Downside border line
_wsh.get_Range(
_wsh.Cells[2, 1], _wsh.Cells[2, 2])
.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;

6. Copy Selected Field

_Worksheet _wsh = (_Worksheet)shs.get_Item(1);// Copy selected field content
	 
Range range = _wsh.get_Range(_wsh.Cells[7, 1], _wsh.Cells[10, _wsh.Columns.Count]);
	
range.Select();
range.Copy(Type.Missing);
	 
//Select paste starting position
Range test = ((Range)_wsh.Cells[11, 1]);
test.Select();
	 
// Shield Alert, default confirm paste
app.DisplayAlerts = false;
test.Parse(Missing.Value, Missing.Value);

Note: Type Missing and Missing Value are considered as some parameters' default value. Most of time, they are form complemented parameters.

7. Export Data from Database to Excel

Click here to know How to export Database to Excel.

Code from Free Data Export Component.

Friendly Reminder:This Free Data Export component has some limits. Before use it, conside those limits into your project.

8. Save Excel and Follow-up Processes

8.1 Save Excel File

// Shield Alert popped up from system
app.AlertBeforeOverwriting = false;
	 
// Save to selected file path
SaveAs(filePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, 
Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, 
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Note: It’s the only solution for saving the Excel file. Otherwise, there will generate a corresponding copy in “My File”.

8.2 Follow-up Processes: Exit and Release

//_wbk.Close(null, null, null);
//wbks.Close();
app.Quit();
	 
// Release unnecessary excel processes
System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
app = null;

Note: During the process of closing application, we usually have 2 solutions:

• Directly exit app 
• Close workbook, close workbooks, exit app. (Recommended)

9. Set Cell Field and Get the Necessary Data from Field

9.1 If cell has been set as drop-down box

// Here the “1, 2, 3” means the drop-down box value
 ((Range)_wsh.Cells[2, 1])
.Validation.Modify(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, 
	Type.Missing, "1,2,3", Type.Missing);

9.2 If cell has not been set as drop-down box

((Range)_wsh.Cells[2, 1])
.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, 
	Type.Missing,"1,2,3", Type.Missing);

9.3 Get drop-down box field value

string strValue = ((Range)_wsh.Cells[2, 1]).Validation.Formula1;

Note: In the excel template, if set drop-down box value through effectiveness, strValue will get the formula of excel, which need be converted. After get strValue, you can get the numerical value according to index.

10. Hide Rows and Hide Columns

10.1 Hide Rows

_wsh.get_Range(_wsh.Cells[19, 1], _wsh.Cells[22, 1]).EntireRow.Hidden = true;

10.2 Hide Columns

_wsh.get_Range(_wsh.Cells[1, 1], _wsh.Cells[_wsh.Rows.Count, 1])
.EntireColumn.Hidden = true;

Conclusion

Any kind of feedback is welcome and it would be appreciated if you can add some more here.