How to insert line break within OPENXML spreadsheet cell?

Question

I'm currently using something like this to insert inline string in a cell :

new Cell() 
{ 
    CellReference = "E2", 
    StyleIndex = (UInt32Value)4U, 
    DataType = CellValues.InlineString, 
    InlineString = new InlineString(new Text( "some text")) 
}

But \n doesn't work to insert line break, how can i do this?

---

The response

new Cell(
         new CellValue("string \n string")
        ) 
    { 
        CellReference = "E2", 
        StyleIndex = (UInt32Value)4U, 
        DataType = CellValues.String         
    }

Mcclurg

Answer 1

Try CellValues.String instead of CellValues.InlineString.

Parade

Although technically CellValues.String is only for formulas. #6469283 – Balkan

Answer 2

You need to do two things:

1.) Mark the cell as "Wrapped Text". You can do this in the spreadsheet by hand if you are using an existing spreadsheet as your template. Just right-click on the cell(s) and select "Format Cells..", click on the "Alignment" tab and check the "Wrap Text" checkbox.
OR... You can set the CellFormat programmatically. If you have a CellFormat object called "cf ", you would do something like this:

cf.ApplyAlignment = true;//Set this so that Excel knows to use it.
if (cf.Alignment == null)//If no pre-existing Alignment, then add it.
  cf.Alignment = new Alignment() { WrapText = true };
Alignment a = cf.Alignment;
if (a.WrapText == null || a.WrapText.Value == false)
  a.WrapText = new BooleanValue(true);//Update pre-existing Alignment.

2.) You shouldn't use "\n", instead you should be using the standard carriage-return + line-feed combination: "\r\n"
If you are mixing the two (i.e. "\n" without the preceeding "\r" and "\r\n"), this should fix it before populating the cell value:

sHeaderText = sHeaderText.Replace("\r\n", "\n").Replace("\n", "\r\n");

I, myself, do not use CellValues.String or even CellValues.InlineString.
Instead I build my text cells using CellValues.SharedString (just like Excel does).
For Bool I use "CellValues.Boolean", and for all others (numerics and dates) I do not set the cell's DataType to anything - because that is what Excel does when I look at the markup it creates.

Malevolent

How do you apply the CellFormat object or Alignment object to a given cell? – Squalid

This doesn't work for me. I just get an error on the line with \r\n and excel won't open the sheet with the text saying it is corrupted – Patrolman

Answer 3

My solution was: when creating CellFormat set WrapText = true.

new Alignment
{
    Horizontal = HorizontalAlignmentValues.Left,
    Vertical = VerticalAlignmentValues.Center,
    WrapText = true, //This setting
};

Evictee