SETHA SITE INFORMATION

  • Increase font size
  • Default font size
  • Decrease font size

How to Export Data from DataGridView in C# to Microsoft Excel?

E-mail Print PDF
This article shows you C# code on export data from DataGridView to Microsoft Excel. In here, you will see how to set font to excel cell and how to make columns become autofit.
  • First, you need to add Microsoft.Office.Interop.Excel as a reference

  • Write C# code using Microsoft.Offce.Interop.Excel namespace

    Download Source Code Here
     
    Microsoft.Office.Interop.Excel.Application ExcelApp = 
              new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel._Workbook ExcelBook;
    Microsoft.Office.Interop.Excel._Worksheet ExcelSheet;
    
    int i = 0;
    int j = 0;
    
    //create object of excel
    ExcelBook = (Microsoft.Office.Interop.Excel._Workbook)ExcelApp.Workbooks.Add(1);
    ExcelSheet = (Microsoft.Office.Interop.Excel._Worksheet)ExcelBook.ActiveSheet;
    //export header
    for (i = 1; i <= this.dgvData.Columns.Count ; i++)
    {
        ExcelSheet.Cells[1, i] = this.dgvData.Columns[i - 1].HeaderText;
    }
    
    //export data
    for (i = 1; i <= this.dgvData.RowCount; i++)
    {
        for (j = 1; j <= dgvData.Columns.Count ; j++)
        {
            ExcelSheet.Cells[i + 1, j] = dgvData.Rows[i - 1].Cells[j - 1].Value;
        }
    }
    
    ExcelApp.Visible = true;
    
    //set font Khmer OS System to data range
    Microsoft.Office.Interop.Excel.Range myRange = ExcelSheet.get_Range(
                              ExcelSheet.Cells[1, 1],
                              ExcelSheet.Cells[this.dgvData.RowCount + 1, 
                              this.dgvData.Columns.Count]);
    Microsoft.Office.Interop.Excel.Font x = myRange.Font;
    x.Name = "Arial";
    x.Size = 10;
    
    //set bold font to column header
    myRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], 
                             ExcelSheet.Cells[1, this.dgvData.Columns.Count]);
    x = myRange.Font;
    x.Bold = true;
    //autofit all columns
    myRange.EntireColumn.AutoFit();
    
    //
    ExcelSheet = null;
    ExcelBook = null;
    ExcelApp = null;