Export Excel File for C#

Introduction

Use C# Export Excel Files has been widely used for developers and programmers. Here, I made a small summary which I wish is useful for some of us.

Add Reference and Namespace

Add Microsoft.Office.Interop.Excel reference, its default path is:

 Collapse  |  Copy Code

C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\
PIA\Office12\Microsoft.Office.Interop.Excel.dll 

In code, add reference:

 Collapse  |  Copy Code

using Microsoft.Office.Interop.Excel;

Brief Introduction of Excel Class

• ApplicationClass - Our Excel application
• Workbook - It’s our normal Excel file, we usually use Workbooks class to operate
• Worksheet - A worksheet is an Excel file
• Worksheet.Cells[row, column] - Cell in some row, column. Here row and column are both start from 1.

Excel Operation

Any Excel operations firstly should use Excel application. Create a new ApplicationClass example and release it at the end.

 Collapse  |  Copy Code

1 ApplicationClass xlsApp = new ApplicationClass(); 	//create an excel application 
						//object example 
2 if (xlsApp == null) 
3 { 
4 //Test and verify this example, if it's null that means the machine run 
  //the codes may not install Excel
5 }

a) Open Existing Excel File

 Collapse  |  Copy Code

1 Workbook workbook = xlsApp.Workbooks.Open(excelFilePath, 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); 
2 Worksheet mySheet = workbook.Sheets[1] as Worksheet; //First worksheet
3 mySheet.Name = "testsheet"; //Rename worksheet

b) CopyWorksheet

 Collapse  |  Copy Code

mySheet.Copy(Type.Missing, workbook.Sheets[1]); //Copy mySheet and rename the copy 
	//as mySheet(2) which is testsheet(2). After that, there is one more Worksheet

Note: The method of Copy has 2 parameters which show the new copy sheet is in the front or back. The example above shows the back.

c) Delete Worksheet

 Collapse  |  Copy Code

1 xlsApp.DisplayAlerts = false; 	//If you want to delete some worksheet, 
				//first set it as false
2 (xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();

d) Select Worksheet

 Collapse  |  Copy Code

(xlsApp.ActiveWorkbook.Sheets[1] 
	as Worksheet).Select(Type.Missing); //Select some Worksheet

e) Save Excel File

 Collapse  |  Copy Code

1 workbook.Saved = true; 
2 workbook.SaveCopyAs(filepath);

f) Release Excel Source

 Collapse  |  Copy Code

1 workbook.Close(true, Type.Missing, Type.Missing); 
2 workbook = null; 
3 xlsApp.Quit(); 
4 xlsApp = null;

g) Usually when we import a DataTable and generate Excel, the codes should be:

 Collapse  |  Copy Code

01 /// <summary> 
02 /// 
03 /// </summary> 
04 /// <param name="dt" /> 
05 protected void ExportExcel(DataTable dt) 
06 { 
07     if (dt == null||dt.Rows.Count==0) return; 
08     Microsoft.Office.Interop.Excel.Application xlApp = 
		new Microsoft.Office.Interop.Excel.Application(); 
09   
10     if (xlApp == null) 
11     { 
12         return; 
13     } 
14     System.Globalization.CultureInfo CurrentCI = 
		System.Threading.Thread.CurrentThread.CurrentCulture; 
15     System.Threading.Thread.CurrentThread.CurrentCulture = 
			new System.Globalization.CultureInfo("en-US"); 
16     Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; 
17     Microsoft.Office.Interop.Excel.Workbook workbook = 
	workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); 
18     Microsoft.Office.Interop.Excel.Worksheet worksheet = 
		(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; 
19     Microsoft.Office.Interop.Excel.Range range; 
20     long totalCount = dt.Rows.Count; 
21     long rowRead = 0; 
22     float percent = 0; 
23     for (int i = 0; i < dt.Columns.Count; i++) 
24     { 
25         worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; 
26         range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; 
27         range.Interior.ColorIndex = 15; 
28         range.Font.Bold = true; 
29     } 
30     for (int r = 0; r < dt.Rows.Count; r++) 
31     { 
32         for (int i = 0; i < dt.Columns.Count; i++) 
33         { 
34             worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); 
35         } 
36         rowRead++; 
37         percent = ((float)(100 * rowRead)) / totalCount; 
38     } 
39     xlApp.Visible = true; 
40 }

h) We can insert image into Excel file and we only need add one row of codes:

 Collapse  |  Copy Code

01 protected void ExportExcel(DataTable dt) 
02 { 
03     if (dt == null || dt.Rows.Count == 0) return; 
04     Microsoft.Office.Interop.Excel.Application xlApp = 
			new Microsoft.Office.Interop.Excel.Application(); 
05   
06     if (xlApp == null) 
07     { 
08         return; 
09     } 
10     System.Globalization.CultureInfo CurrentCI = 
			System.Threading.Thread.CurrentThread.CurrentCulture; 
11     System.Threading.Thread.CurrentThread.CurrentCulture = 
			new System.Globalization.CultureInfo("en-US"); 
12     Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks; 
13     Microsoft.Office.Interop.Excel.Workbook workbook = 
	workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); 
14     Microsoft.Office.Interop.Excel.Worksheet worksheet = 
		(Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; 
15     Microsoft.Office.Interop.Excel.Range range; 
16     long totalCount = dt.Rows.Count; 
17     long rowRead = 0; 
18     float percent = 0; 
19     for (int i = 0; i < dt.Columns.Count; i++) 
20     { 
21         worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName; 
22         range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1]; 
23         range.Interior.ColorIndex = 15; 
24     } 
25     for (int r = 0; r < dt.Rows.Count; r++) 
26     { 
27         for (int i = 0; i < dt.Columns.Count; i++) 
28         { 
29             try 
30             { 
31                 worksheet.Cells[r + 2, i + 1] = dt.Rows[r][i].ToString(); 
32             } 
33             catch 
34             { 
35                 worksheet.Cells[r + 2, i + 1] = 
			dt.Rows[r][i].ToString().Replace("=", ""); 
36             } 
37         } 
38         rowRead++; 
39         percent = ((float)(100 * rowRead)) / totalCount; 
40     } 
41       
42     worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", 
		Microsoft.Office.Core.MsoTriState.msoFalse, 
		Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300); 
43     worksheet.Shapes.AddTextEffect
	(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red", 
	15, Microsoft.Office.Core.MsoTriState.msoFalse, 
	Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200); 
44     xlApp.Visible = true; 
45 } 

What we invoke here:

 Collapse  |  Copy Code

01 public void GenerateExcel() 
02 { 
03     DataTable dt = new DataTable(); 
04     dt.Columns.Add("Name", typeof(string)); 
05     dt.Columns.Add("Age", typeof(string)); 
06     DataRow dr = dt.NewRow(); 
07     dr["Name"] = "spring"; 
08     dr["Age"] = "20"; 
09     dt.Rows.Add(dr); 
10     dt.AcceptChanges(); 
11     ExportExcel(dt); 
12 }

The result after we execute the codes:

Insert image in specific position:

 Collapse  |  Copy Code

worksheet.Shapes.AddPicture("C:\\Users\\spring\\Desktop\\1.gif", 
	Microsoft.Office.Core.MsoTriState.msoFalse, 
	Microsoft.Office.Core.MsoTriState.msoCTrue, 100, 200, 200, 300);

Add text box with content in specific position:

 Collapse  |  Copy Code

worksheet.Shapes.AddTextEffect
	(Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect1, "123456", "Red",
	15, Microsoft.Office.Core.MsoTriState.msoFalse, 
	Microsoft.Office.Core.MsoTriState.msoTrue, 150, 200);

We can design a base class like this:

First, create an ExcelBE.cs:

 Collapse  |  Copy Code

01 public class ExcelBE 
02  { 
03      private int _row = 0; 
04      private int _col = 0; 
05      private string _text = string.Empty; 
06      private string _startCell = string.Empty; 
07      private string _endCell = string.Empty; 
08      private string _interiorColor = string.Empty; 
09      private bool _isMerge = false; 
10      private int _size = 0; 
11      private string _fontColor = string.Empty; 
12      private string _format = string.Empty; 
13   
14      public ExcelBE(int row, int col, string text, 
		string startCell, string endCell, string interiorColor, 
		bool isMerge, int size, string fontColor, string format) 
15      { 
16          _row = row; 
17          _col = col; 
18          _text = text; 
19          _startCell = startCell; 
20          _endCell = endCell; 
21          _interiorColor = interiorColor; 
22          _isMerge = isMerge; 
23          _size = size; 
24          _fontColor = fontColor; 
25          _format = format; 
26      } 
27   
28      public ExcelBE() 
29      { } 
30   
31      public int Row 
32      { 
33          get { return _row; } 
34          set { _row = value; } 
35      } 
36   
37      public int Col 
38      { 
39          get { return _col; } 
40          set { _col = value; } 
41      } 
42   
43      public string Text 
44      { 
45          get { return _text; } 
46          set { _text = value; } 
47      } 
48   
49      public string StartCell 
50      { 
51          get { return _startCell; } 
52          set { _startCell = value; } 
53      } 
54   
55      public string EndCell 
56      { 
57          get { return _endCell; } 
58          set { _endCell = value; } 
59      } 
60   
61      public string InteriorColor 
62      { 
63          get { return _interiorColor; } 
64          set { _interiorColor = value; } 
65      } 
66   
67      public bool IsMerge 
68      { 
69          get { return _isMerge; } 
70          set { _isMerge = value; } 
71      } 
72   
73      public int Size 
74      { 
75          get { return _size; } 
76          set { _size = value; } 
77      } 
78   
79      public string FontColor 
80      { 
81          get { return _fontColor; } 
82          set { _fontColor = value; } 
83      } 
84   
85      public string Formart 
86      { 
87          get { return _format; } 
88          set { _format = value; } 
89      } 
90   
91  }

Then, create ExcelBase.cs:

 Collapse  |  Copy Code

01 public class ExcelBase 
02 { 
03     private Microsoft.Office.Interop.Excel.Application app = null; 
04     private Microsoft.Office.Interop.Excel.Workbook workbook = null; 
05     private Microsoft.Office.Interop.Excel.Worksheet worksheet = null; 
06     private Microsoft.Office.Interop.Excel.Range workSheet_range = null; 
07   
08     public ExcelBase() 
09     { 
10         createDoc(); 
11     } 
12   
13     public void createDoc() 
14     { 
15         try 
16         { 
17             app = new Microsoft.Office.Interop.Excel.Application(); 
18             app.Visible = true; 
19             workbook = app.Workbooks.Add(1); 
20             worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets[1]; 
21         } 
22         catch (Exception e) 
23         { 
24             Console.Write("Error"); 
25         } 
26         finally 
27         { 
28         } 
29     } 
30   
31     public void InsertData(ExcelBE be) 
32     { 
33         worksheet.Cells[be.Row, be.Col] = be.Text; 
34         workSheet_range = worksheet.get_Range(be.StartCell, be.EndCell); 
35         workSheet_range.MergeCells = be.IsMerge; 
36         workSheet_range.Interior.Color = GetColorValue(be.InteriorColor); 
37         workSheet_range.Borders.Color = System.Drawing.Color.Black.ToArgb(); 
38         workSheet_range.ColumnWidth = be.Size; 
39         workSheet_range.Font.Color = string.IsNullOrEmpty(be.FontColor) ? 
	      System.Drawing.Color.White.ToArgb() : System.Drawing.Color.Black.ToArgb(); 
40         workSheet_range.NumberFormat = be.Formart; 
41     } 
42   
43     private int GetColorValue(string interiorColor) 
44     { 
45         switch (interiorColor) 
46         { 
47             case "YELLOW": 
48                 return System.Drawing.Color.Yellow.ToArgb(); 
49             case "GRAY": 
50                 return System.Drawing.Color.Gray.ToArgb(); 
51             case "GAINSBORO": 
52                 return System.Drawing.Color.Gainsboro.ToArgb(); 
53             case "Turquoise": 
54                 return System.Drawing.Color.Turquoise.ToArgb(); 
55             case "PeachPuff": 
56                 return System.Drawing.Color.PeachPuff.ToArgb(); 
57   
58             default: 
59                 return System.Drawing.Color.White.ToArgb(); 
60         } 
61     } 
62 }

Code that we invoke:

 Collapse  |  Copy Code

01 private void btnRun_Click(object sender, EventArgs e) 
02 { 
03     ExcelBase excel = new ExcelBase(); 
04     //creates the main header 
05     ExcelBE be = null; 
06     be = new ExcelBE (5, 2, "Total of Products", "B5", "D5", 
				"YELLOW", true, 10, "n",null); 
07     excel.InsertData(be); 
08     //creates subheaders 
09     be = new ExcelBE (6, 2, "Sold Product", "B6", "B6", "GRAY", true, 10, "",null); 
10     excel.InsertData(be); 
11     be=new ExcelBE(6, 3, "", "C6", "C6", "GRAY", true, 10, "",null); 
12     excel.InsertData(be); 
13     be=new ExcelBE (6, 4, "Initial Total", "D6", "D6", "GRAY", true, 10, "",null); 
14     excel.InsertData(be); 
15     //add Data to cells 
16     be=new ExcelBE (7, 2, "114287", "B7", "B7",null,false,10,"", "#,##0"); 
17     excel.InsertData(be); 
18     be=new ExcelBE (7, 3, "", "C7", "C7", null,false,10,"",null); 
19     excel.InsertData(be); 
20     be = new ExcelBE(7, 4, "129121", "D7", "D7", null, false, 10, "", "#,##0"); 
21     excel.InsertData(be); 
22     //add percentage row 
23     be = new ExcelBE(8, 2, "", "B8", "B8", null, false, 10, "", ""); 
24     excel.InsertData(be); 
25     be = new ExcelBE(8, 3, "=B7/D7", "C8", "C8", null, false, 10, "", "0.0%"); 
26     excel.InsertData(be); 
27     be = new ExcelBE(8, 4, "", "D8", "D8", null, false, 10, "", ""); 
28     excel.InsertData(be); 
29     //add empty divider 
30     be = new ExcelBE(9, 2, "", "B9", "D9", "GAINSBORO", true, 10, "",null); 
31     excel.InsertData(be);   
32   
33 }

Result effect image: