locked
Not sure how to fix code to do what I want RRS feed

  • Question

  • Hi,

    Would you know how to change this code so that it will send data from the datagrid in the windows form to an excel file?

    right now it does the opposite...(Opens excel file to windows form - data grid)

    OleDbConnection cnn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";");  
                OleDbDataAdapter da;  
                DataSet ds;  
     
                da = new OleDbDataAdapter("Select * from [" + SheetName1 + "]", cnn);  
                ds = new DataSet("TestExcel");  
     
                da.Fill(ds, "TestExcel");  
                dataGridView1.DataSource = ds.Tables[0];  
                toolStripStatusLabel1.Text = (SheetName1 + "         ");  
                toolStripStatusLabel2.Text = FileName;  
     

    Thank you for your time,

    zBuster
    Wednesday, August 6, 2008 3:38 PM

Answers

  • zBuster,

    Tim Van Wassenhove has an excellent example on how to do exactly what you're trying to do at the following link:

    http://www.timvw.be/datagridview-to-excel/

    I suggest downloading the code he has posted, and taking a look at it, especially the file labeled "ExcelGenerator".
    David Morton - Consultant - Catapult Systems - Houston
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    Wednesday, August 6, 2008 5:03 PM
    Moderator
  • This was awesome you found this sight....

    I searched about 200 sights and got nothing....

    (you know how to use google well....)

    Thanks

    zBuster


    ANSWER:
    public void SaveAs()  
            {  
                using (SaveFileDialog saveFileDialog = GetExcelSaveFileDialog())  
                {  
                    if (saveFileDialog.ShowDialog(this) == DialogResult.OK)  
                    {  
                        string fileName = saveFileDialog.FileName;  
     
                        Workbook workbook = ExcelGenerator.Generate(this.dataGridView1);  
                        workbook.Save(fileName);  
     
                        //Process.Start(fileName);  
                    }  
                }  
            }  
     
     
    private SaveFileDialog GetExcelSaveFileDialog()  
            {  
                SaveFileDialog saveFileDialog = new SaveFileDialog();  
                saveFileDialog.CheckPathExists = true;  
                saveFileDialog.AddExtension = true;  
                saveFileDialog.ValidateNames = true;  
                saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);  
                saveFileDialog.DefaultExt = ".xls";  
                saveFileDialog.Filter = "Microsoft Excel Workbook (*.xls)|*.xls";  
                return saveFileDialog;  
            } 

    using CarlosAg.ExcelXmlWriter;

    using System.Threading;

    using System.Diagnostics;

    ~AND~

    Adding the files:

    CarlosAg.ExcelXmlWriter.dll
    ExcelGenerator.cs

    ~AND~

    Seth the namespace on the .cs file to your programs namespace or no workie'.

    • Edited by zBuster Wednesday, August 6, 2008 5:39 PM fixed
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    Wednesday, August 6, 2008 5:38 PM

All replies

  • This is from http://www.c-sharpcorner.com/Blogs/BlogDetail.aspx?BlogId=532

      Response.Clear();
            Response.Buffer = true;
            Response.ContentType="application/vnd.ms-excel";
            Response.Charset = "";
            this.EnableViewState = false;
            System.IO.StringWriter  oStringWriter = new      System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            dataGridView1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();
    Anuja MCSD
    Wednesday, August 6, 2008 4:15 PM
  • I placed the code into the program button press and here are the errors:

                        The name 'Response' does not exist in the current context.

                        'System.Windows.forms.dataGridView' does not contain a definition for 'RenderControl' and no extension
                        method 'RenderControl' accepting a first argument type 'System.Windows.forms.dataGridView' could be found
                        (Are you missing a using directive or assembly refrence?)

    What would I do now...?

    Thanks,

    zBuster   :)

    Wednesday, August 6, 2008 4:42 PM
  • zBuster,

    Tim Van Wassenhove has an excellent example on how to do exactly what you're trying to do at the following link:

    http://www.timvw.be/datagridview-to-excel/

    I suggest downloading the code he has posted, and taking a look at it, especially the file labeled "ExcelGenerator".
    David Morton - Consultant - Catapult Systems - Houston
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    Wednesday, August 6, 2008 5:03 PM
    Moderator
  • This was awesome you found this sight....

    I searched about 200 sights and got nothing....

    (you know how to use google well....)

    Thanks

    zBuster


    ANSWER:
    public void SaveAs()  
            {  
                using (SaveFileDialog saveFileDialog = GetExcelSaveFileDialog())  
                {  
                    if (saveFileDialog.ShowDialog(this) == DialogResult.OK)  
                    {  
                        string fileName = saveFileDialog.FileName;  
     
                        Workbook workbook = ExcelGenerator.Generate(this.dataGridView1);  
                        workbook.Save(fileName);  
     
                        //Process.Start(fileName);  
                    }  
                }  
            }  
     
     
    private SaveFileDialog GetExcelSaveFileDialog()  
            {  
                SaveFileDialog saveFileDialog = new SaveFileDialog();  
                saveFileDialog.CheckPathExists = true;  
                saveFileDialog.AddExtension = true;  
                saveFileDialog.ValidateNames = true;  
                saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);  
                saveFileDialog.DefaultExt = ".xls";  
                saveFileDialog.Filter = "Microsoft Excel Workbook (*.xls)|*.xls";  
                return saveFileDialog;  
            } 

    using CarlosAg.ExcelXmlWriter;

    using System.Threading;

    using System.Diagnostics;

    ~AND~

    Adding the files:

    CarlosAg.ExcelXmlWriter.dll
    ExcelGenerator.cs

    ~AND~

    Seth the namespace on the .cs file to your programs namespace or no workie'.

    • Edited by zBuster Wednesday, August 6, 2008 5:39 PM fixed
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    • Marked as answer by zBuster Wednesday, August 6, 2008 5:39 PM
    Wednesday, August 6, 2008 5:38 PM