• Download excel_color_table - 6.22 KB

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 Exceil file

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

Open an exist 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;

• Download excel_color_table - 6.22 KB

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 boder 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

Codes from Free Data Export Component

8. Save excel and follow-up processes

8.1 Save Excel file

// Shield Alert popped up from sysytem
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 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. (Recommend)

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;