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:
C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\
PIA\Office12\Microsoft.Office.Interop.Excel.dll
In code, add reference:
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.
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
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
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
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
(xlsApp.ActiveWorkbook.Sheets[1]
as Worksheet).Select(Type.Missing); //Select some Worksheet
e) Save Excel File
1 workbook.Saved = true;
2 workbook.SaveCopyAs(filepath);
f) Release Excel Source
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:
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:
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:
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:
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:
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:
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:
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:
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 }