How to Create Excel File Using C#

Introduction

Microsoft Excel is a very useful application for businesses.

Excels contain data in cells like a database so it is also easy to manage data stored in an Excel file from a database or another type.

Excel creates files in the .xls and .xlsx file formats. Excel files are difficult to manage using C#.

Interop

First add a reference from the right side in Solution Explorer. Right-click on the project then select Add Reference then select Microsoft.Office.Interop.Excel.

add Reference

Add the following namespace to the project.

    “using Microsoft.Office.Interop.Excel”

First create a table using a DataGridView in the C# code.

  1. private void Form1_Load(object sender, EventArgs e)  
  2. {  
  3.     dataGridView1.DataSource = ExportToExcel();  
  4. }  
  5. public System.Data.DataTable ExportToExcel()  
  6. {  
  7.     System.Data.DataTable table = new System.Data.DataTable();  
  8.     table.Columns.Add("ID"typeof(int));  
  9.     table.Columns.Add("Name"typeof(string));  
  10.     table.Columns.Add("Sex"typeof(string));  
  11.     table.Columns.Add("Subject1"typeof(int));  
  12.     table.Columns.Add("Subject2"typeof(int));  
  13.     table.Columns.Add("Subject3"typeof(int));  
  14.     table.Columns.Add("Subject4"typeof(int));  
  15.     table.Columns.Add("Subject5"typeof(int));  
  16.     table.Columns.Add("Subject6"typeof(int));  
  17.     table.Rows.Add(1, "Amar""M", 78, 59, 72, 95, 83, 77);  
  18.     table.Rows.Add(2, "Mohit""M", 76, 65, 85, 87, 72, 90);  
  19.     table.Rows.Add(3, "Garima""F", 77, 73, 83, 64, 86, 63);  
  20.     table.Rows.Add(4, "jyoti""F", 55, 77, 85, 69, 70, 86);  
  21.     table.Rows.Add(5, "Avinash""M", 87, 73, 69, 75, 67, 81);  
  22.     table.Rows.Add(6, "Devesh""M", 92, 87, 78, 73, 75, 72);  
  23.     return table;  
  24. }  
In the preceding code, if you use a DataTable directly from System.Data.DataTable then it gives the error “Error 1 'DataTable' is an ambiguous reference between 'System.Data.DataTable' and 'Microsoft.Office.Interop.Excel.DataTable'”. Therefore, use System.Data.DataTable.

Then create an object of Excel Application, Workbook, Worksheet and Range.
  1. Microsoft.Office.Interop.Excel.Application excel;  
  2. Microsoft.Office.Interop.Excel.Workbook worKbooK;  
  3. Microsoft.Office.Interop.Excel.Worksheet worksheet  
  4. Microsoft.Office.Interop.Excel.Range celLrangE;   
To start Excel from an Application Object:
  1. excel = new Microsoft.Office.Interop.Excel.Application();  
Excel Visibility
  1. excel.Visible = false;  
  2. excel.DisplayAlerts = false;  
Create a new WorkBook as in the following:
  1. worKbooK = excel.Workbooks.Add(Type.Missing);  
Make a WorkSheet and provide it a name as in the following:
  1. worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet; worKsheeT.Name = "StudentRepoertCard";  
Merge the cells from [1,1] to [1,8] depending on requirements as in the following:
  1. worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();   
Insert some text into a cell and change the font size of the merged cell's text and use it for the title of the table as in the following:
  1. Microsoft.Office.Interop.Excel.Application excel;  
  2. Microsoft.Office.Interop.Excel.Workbook worKbooK;  
  3. Microsoft.Office.Interop.Excel.Worksheet worKsheeT;  
  4. Microsoft.Office.Interop.Excel.Range celLrangE;  
  5.   
  6. try   
  7. {  
  8.     excel = new Microsoft.Office.Interop.Excel.Application();  
  9.     excel.Visible = false;  
  10.     excel.DisplayAlerts = false;  
  11.     worKbooK = excel.Workbooks.Add(Type.Missing);  
  12.   
  13.   
  14.     worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet) worKbooK.ActiveSheet;  
  15.     worKsheeT.Name = "StudentRepoertCard";  
  16.   
  17.     worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();  
  18.     worKsheeT.Cells[1, 1] = "Student Report Card";  
  19.     worKsheeT.Cells.Font.Size = 15;  
  20.   
  21.   
  22.     int rowcount = 2;  
  23.   
  24.     foreach(DataRow datarow in ExportToExcel().Rows)  
  25.     {  
  26.         rowcount += 1;  
  27.         for (int i = 1; i <= ExportToExcel().Columns.Count; i++)   
  28.         {  
  29.   
  30.             if (rowcount == 3)   
  31.             {  
  32.                 worKsheeT.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;  
  33.                 worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;  
  34.   
  35.             }  
  36.   
  37.             worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();  
  38.   
  39.             if (rowcount > 3)  
  40.             {  
  41.                 if (i == ExportToExcel().Columns.Count)  
  42.                 {  
  43.                     if (rowcount % 2 == 0)   
  44.                     {  
  45.                         celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];  
  46.                     }  
  47.   
  48.                 }  
  49.             }  
  50.   
  51.         }  
  52.   
  53.     }  
  54.   
  55.     celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];  
  56.     celLrangE.EntireColumn.AutoFit();  
  57.     Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;  
  58.     border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
  59.     border.Weight = 2d;  
  60.   
  61.     celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];  
  62.   
  63.     worKbooK.SaveAs(textBox1.Text);;  
  64.     worKbooK.Close();  
  65.     excel.Quit();  
  66.   
  67. }   
  68. catch (Exception ex)   
  69. {  
  70.     MessageBox.Show(ex.Message);  
  71.   
  72. }  
  73. finally   
  74. {  
  75.     worKsheeT = null;  
  76.     celLrangE = null;  
  77.     worKbooK = null;  
  78. }  
table

work sheet

Thank you.