Excel row insert with C#

Question

I am creating dynamic textboxes on a form with a button(Form1,Panel1).I am sending the text from these dynamics to Excel (with a button from Form2), each textbox on a new row. If I start with a default spreadsheet with an unlimited amount of rows it will send the text just fine with a "foreach" loop. If I try to do an insert row for each textbox, the rows will insert but only the last textbox will show the data in a cell. IE: with 5 textboxes, the text from #5 will show. Here is the code I am using:

int row = 1;
foreach (TextBox dynamicTxtBx in sourcePanel.Controls.OfType<TextBox>())
{
    worksheet.Rows[1].Insert();
    worksheet.Cells[row++, "A"].value = dynamicTxtBx.Text;
    // ...
}

you are adding the new line always at pos 1 use worksheet.rows[row-1].insert() — lumos0815
– lumos0815, Commented Nov 15, 2015 at 7:33

Kvam · Accepted Answer · 2015-11-15 09:50:22Z

3

Try this:

int row = 1;

foreach (TextBox dynamicTxtBx in sourcePanel.Controls.OfType<TextBox>())
{
    worksheet.Rows[row].Insert();
    worksheet.Cells[row++, "A"].value = dynamicTxtBx.Text;
    // ...
}

When you do an insert at Row[1], all the current rows are pushed down. Eg: row 2 becomes row 3, row 3 becomes row 4 and so on. This is the reason the values are getting overwritten and only the last value you have entered stays.

You can simulate this in the excel sheet to get more clarity on this. Open the excel sheet. Insert at row1. Write any value. Insert again and write on the next row. The value you enetered previously is pushed down by a row and you end up overwriting the previous value.

edited Nov 15, 2015 at 9:50

Kvam

2,2282 gold badges21 silver badges35 bronze badges

answered Nov 15, 2015 at 7:52

Shilpa

1891 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DSB Over a year ago

Thank you for all of the answers,, worksheet.Rows[row].Insert(); this did the trick !

Collectives™ on Stack Overflow

Excel row insert with C#

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related