none
How do I Export specific DataGridView cells to specific cells in Excel? RRS feed

  • Question

  • I have a datagrid populated with data from a Access 2003 DB. I need to fill specific cells in Excel 2003 with data from specific cells in the datagrid. Example:

    Excel 2003 cells A1, A2, A3, A4 need to be filled with data from the DataGrid cells A1,B1,C1,D1

    Thanks for any help!

    PS. Using C#, thanks!
    Wednesday, January 16, 2008 9:51 PM

Answers

  • Found the anwser, so thought I would post it here in case anyone else needed it.

    Code Block

    private void button1_Click(object sender, EventArgs e)
            {
                //Creates new istance of Excel
      Excel.Application excelApp = new Excel.Application();

                //Set path to the excel file to open
      string myPath = @"C:\Test.xls";
               
      //opens excel file          
      excelApp.Workbooks.Open(myPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               
                 //Row and column the Excel cell is in (eg 1,1)
       int rowIndex = 1; int colIndex = 1;

       //This gets the value in cell (0,1) from the datagrid
                 string a1 = dataGridView1.Rows[0].Cells[1].FormattedValue.ToString();

                 //Excel cell of (1,1) should have the value in cell (0,1) from the Datagrid
       excelApp.Cells[rowIndex, colIndex] = a1;

       //Show excel app
                 excelApp.Visible = true;
    }
    Thursday, January 17, 2008 1:20 PM

All replies

  • Found the anwser, so thought I would post it here in case anyone else needed it.

    Code Block

    private void button1_Click(object sender, EventArgs e)
            {
                //Creates new istance of Excel
      Excel.Application excelApp = new Excel.Application();

                //Set path to the excel file to open
      string myPath = @"C:\Test.xls";
               
      //opens excel file          
      excelApp.Workbooks.Open(myPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
               
                 //Row and column the Excel cell is in (eg 1,1)
       int rowIndex = 1; int colIndex = 1;

       //This gets the value in cell (0,1) from the datagrid
                 string a1 = dataGridView1.Rows[0].Cells[1].FormattedValue.ToString();

                 //Excel cell of (1,1) should have the value in cell (0,1) from the Datagrid
       excelApp.Cells[rowIndex, colIndex] = a1;

       //Show excel app
                 excelApp.Visible = true;
    }
    Thursday, January 17, 2008 1:20 PM
  • Hi,

     

    I have datagrid with one hyperlink column into it..

    when i am exporting datagrid to excel i am loosing the formatting as well as the hyperlink frm the column value.

    is there any way i can retain the hyperlink for the Column values..

    Below is my code

     

    if (dtExport != null)

    {

    StringBuilder sb = new StringBuilder();

    StringWriter stringWriter = new StringWriter(sb);

    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

    grd.DataSource = dtExport;

    grd.DataBind();

    grd.RenderControl(htmlTextWriter);

    Context.Response.ContentType = "application/ms-excel";

    Context.Response.AddHeader("Content-Disposition", "attachment; filename=WorkItems.xls");

    Context.Response.Write(sb);

    Context.Response.Flush();

    Context.Response.End();

    }

    This is basic TDF format.

    I dont want to use any other COM object or methods.

    Please advice.

    Thanks In advance.

    Sagar Gandewar

    Wednesday, March 12, 2008 6:14 PM
  •  

    Thanks for providing your solution, and sorry for reviving an old thread, but I need some assistance as I am not able to use the FormattedValue.ToString(); property.

     

    I specified as a reference using System.Windows.Forms;

     

    but I still get the following error message

     

    'System.Web.UI.WebControls.TableCell' does not contain a definition for 'FormattedValue' and no extension method 'FormattedValue' accepting a first argument of type 'System.Web.UI.WebControls.TableCell' could be found (are you missing a using directive or an assembly reference?) 

     

    Thanks!!!


     

     

    Monday, August 25, 2008 11:16 PM