views:

74

answers:

2

I am using LINQ to XML to create an Excel XML file. I want to include newlines within the cells. Excel uses the 
 literal to represent a new line. If I try to add this using an XText:

XText newlineElement = new XText( "Foo
Bar" );

I get:

Foo
Bar

Which shows up in Excel as:

Foo
Bar

Is there a way to write &#10 to the XML file without doing a

String.Replace( "
", "
" )

over the resulting file text?

EDIT Here is a code snippet showing how I create a row for the Excel document. I was avoiding it since it is a little messy. :) Let me know if more code would help, or if this just adds more confusion.

In this example, the newlineElement described above is represented by the only XText in the code sample.

const string CELL = "{urn:schemas-microsoft-com:office:spreadsheet}Cell";
const string DATA = "{urn:schemas-microsoft-com:office:spreadsheet}Data";
const string STYLE = "{urn:schemas-microsoft-com:office:spreadsheet}StyleID";
const string TYPE= "{urn:schemas-microsoft-com:office:spreadsheet}Type";   
const string HEIGHT = "{urn:schemas-microsoft-com:office:spreadsheet}Height";
const string ROW = "{urn:schemas-microsoft-com:office:spreadsheet}Row";

...

XElement rowElement = new XElement( Row,
                                    new XElement( CELL,
                                        new XAttribute( STYLE, "s0" ) ),
                                    new XElement( CELL,
                                        new XElement( DATA,
                                            new XText( description.Replace("\n", "
") ), 
                                            new XAttribute( TYPE, "String" ) ),
                                        new XAttribute( STYLE, "sWrap" ) ),
                                    new XElement( CELL,
                                        new XAttribute( STYLE, "s0" ) ),
                                    new XAttribute( HEIGHT, height ) );

This produces:

  <ss:Row ss:Height="45">
    <ss:Cell ss:StyleID="s0" />
    <ss:Cell ss:StyleID="sWrap">
      <ss:Data ss:Type="String">Foo&amp;#10;Bar</ss:Data>
    </ss:Cell>
    <ss:Cell ss:StyleID="s0" />
  </ss:Row>

Thanks for the help thus far!

+1  A: 

insert the actual character into the string rather than the escape code - LINQ will handle the conversion for you.

EDIT:

Ok - this was completely wrong, but helped me to get something which (I think) works. If you want to embed something which would otherwise be escaped by the XML rendering, you need to embed it in a CDATA block. Check out this related question on C# and XML which tripped my memory.

AJ
I don't think it will.
SLaks
I think you can use "\n" to insert inline.
AJ
It won't. I tried it.
SLaks
I stand corrected - just tried myself also, same result... What an interesting irritation!
AJ
This partially worked! The .xml file contains the right characters; however, Excel still displays '' instead of the newline. :/
Have you tried inserting the actual newline character rather than the escape code? If you are entering the escape code in the CDATA block it will escape the escape, if you follow :-)
AJ
+1  A: 

Not sure if this helps, but the following code:

var newlineElement = new XText("Foo&#10;Bar");
Console.WriteLine(newlineElement);
Console.WriteLine(newlineElement.Value);

Produces this output:

Foo$amp;#10;Bar

Foo&#10;Bar

So, in your code that is using the newlineElement object, could you use the .Value property?

Dennis Palmer