List of Exercises

How to Export DataGridView to Excel file in C#

	
public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e)
    {
      try
      {
        OpenFileDialog openFileDialog = new OpenFileDialog();
        openFileDialog.InitialDirectory = "c:\\";
        openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|Excel 2007 (*.xls)|*.xls";
        openFileDialog.FilterIndex = 1;

        if (openFileDialog.ShowDialog() == DialogResult.OK)
        {
          DataTable dt = Excel.DataGridView_To_Datatable(dataGridView1);
          dt.exportToExcel(openFileDialog.FileName);
          MessageBox.Show("Data is exported!");
        }
      }
      catch (Exception ex) { MessageBox.Show(ex.Message); }
    }

  }

public static class Excel
  {
    public static void exportToExcel(this System.Data.DataTable DataTable, string ExcelFilePath = null)
    {
      try
      {
        int ColumnsCount;
        if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
          throw new Exception("ExportToExcel: Null or empty input table!\n");
        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Excel.Workbooks.Add();
        Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
        object[] Header = new object[ColumnsCount];
        for (int i = 0; i < ColumnsCount; i++)
          Header[i] = DataTable.Columns[i].ColumnName;
        Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
        HeaderRange.Value = Header;
        HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
        HeaderRange.Font.Bold = true;
        int RowsCount = DataTable.Rows.Count;
        object[,] Cells = new object[RowsCount, ColumnsCount];

        for (int j = 0; j < RowsCount; j++)
          for (int i = 0; i < ColumnsCount; i++)
            Cells[j, i] = DataTable.Rows[j][i];

        Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[2, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[RowsCount + 1, ColumnsCount])).Value = Cells;
        if (ExcelFilePath != null && ExcelFilePath != "")
        {
          try
          {
            Worksheet.SaveAs(ExcelFilePath);
            Excel.Quit();
            System.Windows.Forms.MessageBox.Show("Excel file saved!");
          }
          catch (Exception ex)
          {
            throw new Exception("ExportToExcel: Excel file could not be saved! Check filepath.\n"
              + ex.Message);
          }
        }
        else  // no filepath is given
        {
          Excel.Visible = true;
        }
      }
      catch (Exception ex)
      {
        throw new Exception("ExportToExcel: \n" + ex.Message);
      }
    }
    public static DataTable DataGridView_To_Datatable(DataGridView dg)
    {
      DataTable ExportDataTable = new DataTable();
      foreach (DataGridViewColumn col in dg.Columns)
      {
        ExportDataTable.Columns.Add(col.Name);
      }
      foreach (DataGridViewRow row in dg.Rows)
      {
        DataRow dRow = ExportDataTable.NewRow();
        foreach (DataGridViewCell cell in row.Cells)
        {
          dRow[cell.ColumnIndex] = cell.Value;
        }
        ExportDataTable.Rows.Add(dRow);
      }
      return ExportDataTable;
    }
  }
	


Download the project of Visual Studio 2013 in DropBox Download


How to Export DataGridView to Excel file in C# | Winforms | Visual Studio


Video Thumbnail