none
How to wrap text and add a 5 space padding to the contents of a column in Excel. RRS feed

  • Question

  • I have a csv data file that I'm exporting to an Excel worksheet. Column "C" may have up to 2000 characters which I want to wrap within a fixed column width. But I want to leave 5 spaces after a (,) and before the edge of the column. How do I pad 5 spaces and then have the text wrap to a new line? Thanks in advance. I'm using Visual Studio 2010 C# using Microsoft.Office.Interop.excel.
    Monday, February 18, 2013 7:29 PM

Answers

  • I found out how to do it. The solution was similar to to what I had tried before, but the newline now works as expected. Set the column width as a constant, "WrapText = false", count characters up to 56 (I could have used 70), look for the next comma, then add a newline. The for-loop is shown below:

                    for (int k = 0; k < ENGBomLength; k++)
                    {
                        for (int x = 0; x < 6; x++)
                        {
                            string myCol = EngbomData[k, x];
                            if (x == 2)
                            {
                                // Column "C" (i.e."Location") needs to wrap text within the 56.0 width set above for "Normal" style.
                                // One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
                                int z, idx;
                                for (z = 0; z < myCol.Length; z++)
                                {
                                    if ((z != 0) && ((z % 56) == 0))
                                    {
                                        idx = myCol.LastIndexOf(",", z);
                                        if (idx > 0)
                                        {
                                            myCol = myCol.Insert(idx + 1, "\n");
                                        }
                                    }
                                }
                            }
                            engWorkSheet.Cells[row, engColumns[x]] = myCol;
                        }
                        row++;
                    }

    Thanks for the response.

    • Marked as answer by gpduric Tuesday, February 19, 2013 9:44 PM
    Tuesday, February 19, 2013 9:44 PM

All replies

  • Hi Gpduric,

    Thank you for posting in the MSDN Forum.

    What do you mean by "pad 5 spaces and then have the text wrap to a new line" ?

    Do you mean that you want increase indent? Only for the first line or for all the text?

    I look forward to your reply.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, February 19, 2013 12:48 PM
    Moderator
  • Hi Quist,

    I can show you what mean better if I include a screen shot of what I want to do. Oops, my image can't be included in this reply until my account is verified. Not sure why because I'm logged in. Well, for now I'll have to explain it better without the image.

    Yes, I want to increase the indent to the edge of the column. I want to end each line at a comma then a newline. The content of the column is read as a long string with commas separating each entry. Here's an example:

    C17,C18,C19,C20,C21,C22,C23,C24,C41,C42,C43,C44,C45,C46,C47,C48,C50,     |
    C51,C54,C55,C57,C58,C60,C61,C64,C65,C68,C69,C71,C72,C74,C75,C77,C78,     |

    The vertical bar at the end of each line represents the column border. There is an indent to the border. I called it 5 spaces but it could be more or less. I need to have each line end on a comma with some space between the comma and the border.  The example above is from a project I took over which was programmed in VB 6.0. I am using VS 2010 C# and have not had too much trouble (my first try at C# and VS), but I can't get the indent correct. 

    The code I wrote to format the column is shown below:

                    range1 = engWorkSheet.get_Range("C1:C" + lastRowString);
                    range1.Columns.ColumnWidth = 72.0;
                    range1.Columns.WrapText = true;
                    range1.HorizontalAlignment = ExcelIO.XlHAlign.xlHAlignLeft;
                    range1.VerticalAlignment = ExcelIO.XlVAlign.xlVAlignTop;
                    range1.BorderAround(missing, ExcelIO.XlBorderWeight.xlThin,
                        ExcelIO.XlColorIndex.xlColorIndexAutomatic, missing);

    I tried "WrapText = false" and counted the characters up to 70, looked for the next comma, then added 5 spaces with a newline. The result was that the spaces were removed along with the newline.

    Thanks for your reply.
    • Marked as answer by gpduric Tuesday, February 19, 2013 9:38 PM
    • Unmarked as answer by gpduric Tuesday, February 19, 2013 9:38 PM
    Tuesday, February 19, 2013 3:22 PM
  • I found out how to do it. The solution was similar to to what I had tried before, but the newline now works as expected. Set the column width as a constant, "WrapText = false", count characters up to 56 (I could have used 70), look for the next comma, then add a newline. The for-loop is shown below:

                    for (int k = 0; k < ENGBomLength; k++)
                    {
                        for (int x = 0; x < 6; x++)
                        {
                            string myCol = EngbomData[k, x];
                            if (x == 2)
                            {
                                // Column "C" (i.e."Location") needs to wrap text within the 56.0 width set above for "Normal" style.
                                // One unit of column width is equal to the width of one character in the Normal style. For proportional fonts, the width of the character 0 (zero) is used.
                                int z, idx;
                                for (z = 0; z < myCol.Length; z++)
                                {
                                    if ((z != 0) && ((z % 56) == 0))
                                    {
                                        idx = myCol.LastIndexOf(",", z);
                                        if (idx > 0)
                                        {
                                            myCol = myCol.Insert(idx + 1, "\n");
                                        }
                                    }
                                }
                            }
                            engWorkSheet.Cells[row, engColumns[x]] = myCol;
                        }
                        row++;
                    }

    Thanks for the response.

    • Marked as answer by gpduric Tuesday, February 19, 2013 9:44 PM
    Tuesday, February 19, 2013 9:44 PM