C# split string array to Excel

Question

I have a previous question similar to this but I am still frozen on the actual solution. I am sending rows of text from dynamic textboxes to Excel, this information comes from a database. Within the rows I am sending "txtProductNameBundle" and on occasion a "product description"("txtProductDesc") row that will have to split and would need to be in place under the "txtProductNameBundle" in Excel, it could be 1 row or up to 6. I have my for loops that successfully send all of the rows (without Product Description) exactly where I need them. Here is the problem, I know how to perform a "split string" with the "txtProductDesc" as this text can be rather long and even send it to Excel but I am clueless on how to add a loop to place it after the "product name". The Excel sheet is a template so rows have to be inserted for as many rows of information that will be sent.

    int StartBundleRow = 11;  // row 11 is where I start to insert the dynamic controls
    string rowIndent = "        ";  // adds spaces to the beginning of the text
    string DescriptionSplit = frmProposal.ProdDesc.Text;

    for (int BndlRow = 0; BndlRow < bundleRows; BndlRow++) 
        {
            worksheet.Rows[StartBundleRow].Insert();
            worksheet.Rows[StartBundleRow].Font.Size = 14; //********Excel formatting*********
            worksheet.Cells[StartBundleRow, "E"].Font.Bold = true;  
            worksheet.Rows[StartBundleRow].Interior.Color = ColorTranslator.ToOle(Color.White);
            worksheet.Columns["A"].Interior.Color = ColorTranslator.ToOle((Color)cc.ConvertFromString("#808080"));
            worksheet.Columns["J:XFD"].Interior.Color = ColorTranslator.ToOle((Color)cc.ConvertFromString("#808080"));
            worksheet.Rows[StartBundleRow].HorizontalAlignment = XlHAlign.xlHAlignLeft;
            worksheet.Cells[StartBundleRow, "C"].Interior.Color = ColorTranslator.ToOle((Color)cc.ConvertFromString("#49176D"));
            worksheet.Cells[StartBundleRow, "D"].value = srcBundlePanel.Controls["txtQtyBundle" + BndlRow].Text;
          //(product name below)
            worksheet.Cells[StartBundleRow, "E"].value = srcBundlePanel.Controls["txtProductNameBundle" + BndlRow].Text;
         //(this is where I need to insert the split string of product description)
            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;
        } 
    ** BELOW IS MY SAMPLE STAND ALONE CODE FOR SPLITTING THE STRING INTO 3 ROWS **
    worksheet.Cells[11, "E"].Value = rowIndent + DescriptionSplit.Substring(0, DescriptionSplit.IndexOf("|")).Trim();
    worksheet.Cells[12, "E"].Value = rowIndent + DescriptionSplit.Substring(DescriptionSplit.IndexOf("|") + 1, 
      DescriptionSplit.IndexOf("|")).Trim();
    worksheet.Cells[13, "E"].Value = rowIndent + DescriptionSplit.Substring(DescriptionSplit.LastIndexOf("|") + 1, 
      DescriptionSplit.Length - DescriptionSplit.LastIndexOf("|") - 1).Trim();

PHeiberg · Accepted Answer · 2015-12-06 13:17:37Z

1

The split of the description string can be made simpler provided that each part is separated by a pipe char.

string[] descriptionParts = DescriptionSplit.Split('|');

To insert the rows you can use a simple for loop:

for(int i = 0; i < descriptionParts.Length; i++) 
{
    worksheet.Cells[StartBundleRow + i, "E"].Value = 
        rowIndent + descriptionParts[i].Trim();
}

You probably also want to replace the last line with below code to adjust the row offset for the next bundle based on number of rows used for the description:

worksheet.Cells[StartBundleRow,"H"].value = 
    srcBundlePanel.Controls["txtProposedPriceBundle" + BndlRow].Text;
StartBundleRow += descriptionParts.Length;

edited Dec 6, 2015 at 13:17

answered Dec 5, 2015 at 21:44

PHeiberg

29.9k6 gold badges61 silver badges82 bronze badges

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

3 Comments

PHeiberg Over a year ago

Sorry, I misinterpreted the fixed indexes on the description rows in your example. The index has to be offset just like for other rows. Editing the answer.

CamlCase Over a year ago

When you say "replace the last line", I'm a little confused there sorry. The for loop works but I'm not sure how to insert that into the other group of "startbundlerow" code so I can repeat it each time. Currently it works outside of that but only loops for a single row. I have been editing and trying to adjust but no success. Do I need to use a "foreach" somehow?

PHeiberg Over a year ago

Can't you just insert the for loop instead of the line starting with "//(this is where"? With replace the last line, I mean that you take my two new lines and put them instead of the last line inside your existing for loop. I just changed how the StartBundleRow is incremented.

Collectives™ on Stack Overflow

C# split string array to Excel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related