none
How do I insert contents into an Excel worksheet? RRS feed

  • Question

  • I found that the follow retrieves the contents of A1 through D6 cells

                System.Array MyValues = (System.Array)gxlsxSheet.get_Range("A1", "D6").Cells.Value;

    How do I insert contents into cells?




    bhs67

    • Moved by Kristin Xie Friday, March 27, 2015 9:19 AM move to appropriate forum
    Thursday, March 26, 2015 9:21 PM

Answers

  • This works:

        oledbCmd.CommandText = "SELECT * FROM [" + stSheetName + "$]";
        DataTable dtDataTable = new DataTable();
        using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCmd)) {
            oledbAdapter.Fill(dtDataTable);

        }
        string stRowNumber = (dtDataTable.Rows.Count + 1).ToString();
        string stRowID = "A" + stRowNumber + ":B" + stRowNumber;
        oledbCmd.CommandText = "UPDATE [" + stSheetName + "$" + stRowID + "] SET F1=3, F2='z'";
        oledbCmd.ExecuteNonQuery();

    The dtDataTable fills with the Worksheet contents ... the number of rows plus one indicates the next row to add using UPDATE.


    bhs67


    • Edited by bhs67 Thursday, April 2, 2015 11:18 AM
    • Marked as answer by L.HlModerator Tuesday, April 7, 2015 5:19 AM
    Thursday, April 2, 2015 11:17 AM

All replies

  • There are a lot ways, here recommend u a third party .NET API :

    download and add dll to your project,then add the following code:

               //Initialize a new Workboook object
                Workbook workbook = new Workbook();
                //Get the first worksheet
                Worksheet sheet = workbook.Worksheets[0];
                //Write string values in a cell
                sheet.Range["A3"].Text = "This Excel Document is Created by Spire.XLS for .NET";
                //Save workbook to disk
                workbook.SaveToFile("Sample.xls");
                try
                {
                       System.Diagnostics.Process.Start(workbook.FileName);
                }
                catch { }


    • Edited by jujubeee Friday, March 27, 2015 1:57 AM change code
    Friday, March 27, 2015 1:56 AM
  • Take a look at the How to automate Microsoft Excel from Microsoft Visual C#.NET article which provides a sample code and describes all the required steps for automating Excel.
    Friday, March 27, 2015 10:05 AM
  • Take a look at the How to automate Microsoft Excel from Microsoft Visual C#.NET article which provides a sample code and describes all the required steps for automating Excel.
    This does work ... however it works only if I allow the Excel spreadsheet to display on the screen.  When I remove "gxlsxApp.Visible = true" it does not work.

    bhs67

    Friday, March 27, 2015 12:40 PM
  • why don't you use oledb to inset values in an cell ?
    • Edited by Lanlan0901 Sunday, March 29, 2015 5:50 PM
    Sunday, March 29, 2015 5:42 PM
  • I am using oledb.  I'd appreciate an example.  Thanks! 

    bhs67


    • Edited by bhs67 Monday, March 30, 2015 12:02 PM
    Monday, March 30, 2015 12:01 PM
  • Hi

    >>How do I insert contents into cells?

     The following code provide a way to insert values into Excel Cell which you can refer to

    1.  Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();

                    app.Visible = false;

                    Workbook wb = app.Workbooks.Add(true);

                    Worksheet ws = (Worksheet)app.Worksheets[1];

     

                    for (int i = 1; i < 6; i++)

                    {

                        for (int j = 1; j < 8; j++)

                        {

                            ws.Cells[i, j] = i.ToString() + " + " + j.ToString() + "= ? ";

     

                        }

                    }

                    app.DisplayAlerts = false;

                    app.AlertBeforeOverwriting = false;

                    wb.SaveCopyAs(@"d:\test1.xlsx"); // already exists in your disk

                    app.Quit();

                                app = null;

    >>When I remove "gxlsxApp.Visible = true" it does not work.

    could you please share your code to provide the context, so more information could be helpful for solve this issue? is there any error meeages ?

    >> I am using oledb.  I'd appreciate an example

    This library could provide a sample code to use OLEdb, which you can refer to

    https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection(v=vs.110).aspx

    By the way, this is the forum for Excel develop (VBA, Automation), for your question is more about ADO.NET, I will suggest you posting this thread in TechNet Forum for Excel.Thanks for your understanding.

    Best Regards

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, April 2, 2015 6:45 AM
    Moderator
  • This works:

        oledbCmd.CommandText = "SELECT * FROM [" + stSheetName + "$]";
        DataTable dtDataTable = new DataTable();
        using (OleDbDataAdapter oledbAdapter = new OleDbDataAdapter(oledbCmd)) {
            oledbAdapter.Fill(dtDataTable);

        }
        string stRowNumber = (dtDataTable.Rows.Count + 1).ToString();
        string stRowID = "A" + stRowNumber + ":B" + stRowNumber;
        oledbCmd.CommandText = "UPDATE [" + stSheetName + "$" + stRowID + "] SET F1=3, F2='z'";
        oledbCmd.ExecuteNonQuery();

    The dtDataTable fills with the Worksheet contents ... the number of rows plus one indicates the next row to add using UPDATE.


    bhs67


    • Edited by bhs67 Thursday, April 2, 2015 11:18 AM
    • Marked as answer by L.HlModerator Tuesday, April 7, 2015 5:19 AM
    Thursday, April 2, 2015 11:17 AM