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.

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;
  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);
  14. worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet) worKbooK.ActiveSheet;
  15. worKsheeT.Name = "StudentRepoertCard";
  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;
  22. int rowcount = 2;
  24. foreach(DataRow datarow in ExportToExcel().Rows)
  25. {
  26. rowcount += 1;
  27. for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
  28. {
  30. if (rowcount == 3)
  31. {
  32. worKsheeT.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;
  33. worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
  35. }
  37. worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
  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. }
  48. }
  49. }
  51. }
  53. }
  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;
  61. celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];
  63. worKbooK.SaveAs(textBox1.Text);;
  64. worKbooK.Close();
  65. excel.Quit();
  67. }
  68. catch (Exception ex)
  69. {
  70. MessageBox.Show(ex.Message);
  72. }
  73. finally
  74. {
  75. worKsheeT = null;
  76. celLrangE = null;
  77. worKbooK = null;
  78. }
table

work sheet

Thank you.