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 Excel5 }

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 worksheet3 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 false2 (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: