C# Split String to Excel

Question

I have been reading up on Split string. I am sending data from C# to Excel and some of this text can be rather long. So without using word wrap or autofit with Excel. I would like the data to break at a certain point and continue to the row below it.Can this be accomplished? ProdDescription is the targeted field here. Here is the code I am using to send the basic data over :

        worksheet.Cells[3, "E"].Value = txtCustomer.Text;
        worksheet.Cells[4, "E"].Value = txtDate.Text;
        worksheet.Cells[5, "E"].Value = cboTerms.Text;
        worksheet.Cells[6, "E"].Value = txtProposalID.Text;
        worksheet.Cells[10, "D"].value = frmProposal.QtyMaintxt.Text;
        worksheet.Cells[10, "E"].value = frmProposal.ProdName.Text;
        worksheet.Cells[11, "E"].value = frmProposal.ProdDescription.Text;**
        worksheet.Cells[10, "F"].value = frmProposal.ListPrice.Text;
        worksheet.Cells[10, "G"].value = frmProposal.MaxDiscount.Text;

AFAIK, there is no such method in the BCL, out of the box. Do you want to just insert line breaks, but leave the text in a single cell in Excel, or you want the wrapped lines to occupy adjacent cells downwards? In either case, it would be quite tricky to predict the right margin at which you need to break the lines, even if you plan to use fixed width font in Excel. — felix-b
– felix-b, Commented Nov 21, 2015 at 22:28

Gottfried Lesigang · Accepted Answer · 2015-11-21 22:45:30Z

Try it like this:

        string s = "This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. ";
        s += "This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. ";
        s += "This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. This is a rather long text. ";

        var words = s.Split(new char[] { ' ' });

        int maxLineCount = 35;
        var sb=new System.Text.StringBuilder();

        string part=words[0];
        int i=1;
        while (true) {
            if (i >= words.Length)
                break;
            if ((part + " " + words[i]).Length < maxLineCount)
                part += " " + words[i];
            else {
                sb.AppendLine(part);
                part = words[i];
            }
            i++;
        }
        var result = sb.ToString();

You could write the generated "lines" into an array or directly into your cells too. I just used Stringbuilder to check the result easily...

Collectives™ on Stack Overflow

C# Split String to Excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related