C# loop to Excel row

Question

1.00/5 (1 vote)

See more:

With the code I've provided I am sending dynamic textboxes to an excel template. With these five statements it will create 1 row of information. I need to create another loop for other dynamic textboxes to start 6 rows below the previous data. Can someone please help ??

C#

int StartBundleRow = 11;
for (int BndlRow = 0; BndlRow < bundleRows; BndlRow++) //add bundle rows to spreadsheet
{
    worksheet.Rows[StartBundleRow].Insert();
    worksheet.Cells[StartBundleRow, "D"].value = srcBundlePanel.Controls["txtQtyBundle" + BndlRow].Text;
    worksheet.Cells[StartBundleRow, "E"].value = srcBundlePanel.Controls["txtProductNameBundle" + BndlRow].Text;
    worksheet.Cells[StartBundleRow, "F"].value = srcBundlePanel.Controls["txtListPriceBundle" + BndlRow].Text;
    worksheet.Cells[StartBundleRow, "G"].value = srcBundlePanel.Controls["txtMaxDiscountBundle" + BndlRow].Text;
    worksheet.Cells[StartBundleRow++, "H"].value = srcBundlePanel.Controls["txtProposedPriceBundle" + BndlRow].Text;
}

Posted 17-Nov-15 8:49am

Member 12103627

Updated 17-Nov-15 9:06am

v3

Add a Solution

Comments

ZurdoDev 17-Nov-15 15:08pm

Why are can't you create another loop?

Member 12103627 17-Nov-15 15:13pm

I can create another loop just like this one but the second group of data has to drop down 6 rows from where the first group ends. I don't know the syntax of that. The first group is dynamic so there is never a row that it will definitely end on.

DotNetSteve 17-Nov-15 17:29pm

int StartBundleRow = 17;??? First row was at 11, add 6 and your row should be 17.

int StartBundleRow = 11;
for (int BndlRow = 0; BndlRow < bundleRows; BndlRow++) //add bundle rows to spreadsheet
{
worksheet.Rows[StartBundleRow].Insert();

Member 12103627 17-Nov-15 17:40pm

In theory I understand that 2 + 2 = 4 , 11 + 6 = 17. Dynamic textboxes are typically created with an idea that you need "this many" "this time"...So, to finish my looooong point. If I need 2 rows of textboxes the first time then 17 may work or be close...but if I have 12 rows the second time then I cant really start at 17 now can I ?,,,,because 11 + 12 is what ??....23 :) So anyway, this is why I need to find the last row used and start the second group 6 rows below that one. This is a template I am using so under the first set of rows there is cells with headings, text, etc....I have to skip over those.

DotNetSteve 18-Nov-15 10:19am

Let me think more deeply on this one. I think I understand your real question.
What confuses me in your example is worksheet.Rows[StartBundleRow].Insert(); I think it should be worksheet.Rows[StartBundleRow + BndlRow ].Insert(); this would create a number of bundlerows instead of just one.

2 solutions

Solution 2

C#

int RowOffset;
int StartBundleRow = 11;

for (RowOffset = StartBundleRow; RowOffset < bundleRows + StartBundleRow ; RowOffset++)
{
   worksheet.Rows[RowOffset].Insert();

   worksheet.Cells[RowOffset, "D"].value = srcBundlePanel.Controls["txtQtyBundle" + RowOffSet].Text;
   // and so on
}

RowOffset += 6;

// start your second loop using 'RowOffset

Using a counter variable declared outside the 'for loop, then you can use that when the 'for loop exits.

Posted 17-Nov-15 13:14pm

BillWoodruff

Comments

Patrice T 17-Nov-15 20:01pm

Hi Bill,
Your program look weird.
Did you see that the original program is populating rows in reverse order ?
First row at bottom and last row on top, because of the insert()

BillWoodruff 13-Dec-15 23:21pm

I just followed the way the OP wrote the code. Looks some of the code in the post was removed.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2015-11-17T12:42:00

Solution 1

It looks like your problem is to keep track of what you do.

From the comments, I see that you are stuck with the variable number of rows of first block.

This is simple maths:
You know that first row of first block is StartBundleRow = 11
You know that the number of rows in first block is bundleRows

Deduce that Last row of first block is StartBundleRow + bundleRows - 1
add 6 blank rows StartBundleRow + bundleRows - 1 + 6
Second block start on next row StartBundleRow + bundleRows - 1 + 6 + 1 which gives StartBundleRow + bundleRows + 6
and you are done.

This kind of things is the basic of programming.

Posted 17-Nov-15 12:42pm

Patrice T

Comments

Patrice T 17-Nov-15 18:54pm

To donwvoter: what is wrong in my solution ?

George Jonsson 17-Nov-15 20:13pm

There are some downvoters lurking around, downvoting this and that, but never leaving a comment.
I give you a 5.

Patrice T 17-Nov-15 20:30pm

Thank you.
I don't understand what kind of pleasure they get by downvoting this or that ?

Member 12103627 17-Nov-15 19:36pm

@ ppolymorphe,,,this solution does the trick for me,,thank you !!!