C# dataGridView Export to excel

Question

Trying to Export to Excel I have a dataGridView from access db but i need to cast to a DataTable for the exportation, Any ideas?

private void CreateExcel(string sPath)
        {

            DataTable dt = (DataTable)dataGridView1.DataSource;  // I get the Error for unable to cast here

   int n = dt.Columns.Count;

   string[] strArr = new string[n];

Answer 1

Trying to Export to Excel I have a dataGridView from access db but i need to cast to a DataTable for the exportation, Any ideas?

private void CreateExcel(string sPath)
        {

            DataTable dt = (DataTable)dataGridView1.DataSource;  // I get the Error for unable to cast here

   int n = dt.Columns.Count;

   string[] strArr = new string[n];

Here is the full code of what I am trying to do

private void CreateExcel(string sPath)
        {

            DataTable dt = (DataTable)dataGridView1.DataSource;  

   int n = dt.Columns.Count;

   string[] strArr = new string[n];

   object objValue = System.Reflection.Missing.Value;

   Excel.Application sXLApp = new Excel.Application();

   Excel.Workbooks sXLBooks = (Excel.Workbooks)sXLApp.Workbooks;

   Excel._Workbook sXLBook = (Excel._Workbook)(sXLBooks.Add(objValue));

   Excel.Sheets sXLSheets = (Excel.Sheets)sXLBook.Worksheets;

   Excel._Worksheet sXLWorksheet = (Excel._Worksheet)(sXLSheets.get_Item(1));



   for(int x=0; x < n; x++)           

   {

      strArr[x] = dt.Columns[x].ColumnName.ToString().Trim();             

   }     

   object objHeaders = (object)strArr;

   Excel.Range sXLRange = sXLWorksheet.get_Range("A1", "IV1");

   sXLRange.set_Value(objValue, objHeaders);

   Excel.Font sXLFont = sXLRange.Font;

         

   // To Assign Empty Column Header is null

   for(int y = n+1; y <= sXLRange.Count ; y++)           

   {

      sXLRange[1,y] = null;        

   }     

   

   sXLFont.Bold = true;    // To Assign Header in Bold



   object[,] objData = new object[dt.Rows.Count, dt.Columns.Count];



   for(int nRow = 0; nRow < dt.Rows.Count; nRow++)

   {

      for(int nCol = 0; nCol< dt.Rows.Count; nCol++)

      {

     objData[nRow, nCol] = dt.Rows[nRow][nCol].ToString().Trim();

      }               

   }

   sXLRange = sXLWorksheet.get_Range("A2", objValue);

   sXLRange = sXLRange.get_Resize(dt.Rows.Count, dt.Columns.Count);

   sXLRange.set_Value(objValue, objData);

   

   //If you need Apply the color into Excel Cell based on Grid Cell    

   for(int c=0; c < dataGridView1.Columns.Count; c++)

   {

      // To get the Excel Cell Name    

      string sCell = GetExcelCell(c + 1);



      for (int r = 0; r < dataGridView1.Rows.Count; r++)

      {

      sXLRange = sXLWorksheet.get_Range(sCell + (r+2), sCell + (r+2));

      sXLRange.Interior.Color = ColorTranslator.ToOle(dataGridView1[c, r].Style.BackColor);

      }            

    }



    sXLApp.Columns.EntireColumn.AutoFit();

    sXLApp.Columns.EntireRow.AutoFit();

    sXLBook.SaveAs(sPath, objValue, objValue, objValue, objValue, objValue,

            Excel.XlSaveAsAccessMode.xlNoChange, objValue, objValue, objValue, objValue, objValue);            

    sXLBook.Close(false, objValue, objValue);

    sXLApp.Quit();

    GC.WaitForPendingFinalizers();

    GC.Collect();                  

    GC.WaitForPendingFinalizers();

    GC.Collect();                               

}
        private string GetExcelCell(int nID)

{

  string sCell = string.Empty;

  if(nID < 27)

  {

    switch (nID)

    {

    case 0:

         sCell = "z";

            break;     

    case 1:

         sCell = "A";

            break;     

    case 2:

         sCell = "B";

            break;     

    case 3:

         sCell = "C";

            break;     


    case 25:

         sCell = "Y";

            break;     

    case 26:

         sCell = "Z";

            break;     

    default:

         sCell= String.Empty;

         break;                        

    }                

    return sCell;

  }  

  else

  {

          int nDiv = nID / 26;

     int nMod = nID % 26;

    if (nMod.Equals(0))

    {

       nDiv = nDiv - 1;

    }         

    sCell = GetExcelCell(nDiv);

    sCell = sCell + GetExcelCell(nMod);    

    return sCell;

    }     

}

Answer 2

Hello mkinney88,

Trying to Export to Excel I have a dataGridView from access db but i need to cast to a DataTable for the exportation, Any ideas?

private void CreateExcel(string sPath)
        {

            DataTable dt = (DataTable)dataGridView1.DataSource;  // I get the Error for unable to cast here

   int n = dt.Columns.Count;

   string[] strArr = new string[n];

for some example , follow thos threads,

http://www.codeproject.com/Articles/32224/Exporting-a-DataGridView-to-Excel-in-NET-2-0-C-cod

http://www.codeproject.com/Articles/4374/Export-DataGrid-to-Excel

Regards.

---

• Carmelo La Monica

• Blog
• WordPress
• Twitter

Answer 3

Hello,

Perhaps the DataSource is a BindingSource or DataSet? You could use code to determine this as shown below then from there cast to the approriate type first i.e. If the DataSource is a BindingSource you would need to cast the DataGridView DataSource first to a BindingSource then to a DataTable or as most BindingSource are form level you would not cast the DataGridView DataSource but instead cast the BindingSource.DataSource to a DataTable.

The following is a sure fire way to determine your source.

Type SourceType = this.DataGridView1.DataSource.GetType() ;
if (SourceType == typeof(DataTable))
{
   DataTable dt = (DataTable)DataGridView1.DataSource ;
   // Continue with your logic
}
else
{
   MessageBox.Show(string.Format(
   "Failed to cast DataGridView DataSource to DataTable because the DataSource type is {0}", 
   SourceType.ToString())) ;
}

---

KSG