By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

how to write this out using xmlwriter

P: n/a
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";

thanks.
--
Paul G
Software engineer.
Apr 19 '07 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Paul wrote:
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";
We need to know whether those elements (e.g. Row, Cell) are in a
namespace and which namespace URI that namespace has. And we need to
know which namespace URI the prefix ss is bound to.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 20 '07 #2

P: n/a
Hi thanks for the response. When I write out the row I am using the following,
WriteStartElement("Row");
WriteAttributeString("ss","AutofitHeight","Spreads heetNamespace","0");
followed by other write statements for cells/data and closing tags.
So I am using the SpreadsheetNamespace for both rows and cells.
The SpreadsheetNamespace I have defined as
"urn:schemas-microsoft-com:office:spareadsheet". I am using the "ss" prefix
whenever the SpreadsheetNamespace is used like in the WriteAttributeString
above. I am kind of new to xml but was trying to bind it to
urn:schemas-microsoft-com:office:spreadsheet. Do I need an additional line
of code to do this or is it implied by how I am using it?
Thanks again Paul.
--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";

We need to know whether those elements (e.g. Row, Cell) are in a
namespace and which namespace URI that namespace has. And we need to
know which namespace URI the prefix ss is bound to.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #3

P: n/a
Also I am using a namespace when i write out the string attribute for the
cells and rows. I am using the WriteStartElement as described above to write
out the start tags for rows and cells. Thanks Paul.
--
Paul G
Software engineer.
"Paul" wrote:
Hi thanks for the response. When I write out the row I am using the following,
WriteStartElement("Row");
WriteAttributeString("ss","AutofitHeight","Spreads heetNamespace","0");
followed by other write statements for cells/data and closing tags.
So I am using the SpreadsheetNamespace for both rows and cells.
The SpreadsheetNamespace I have defined as
"urn:schemas-microsoft-com:office:spareadsheet". I am using the "ss" prefix
whenever the SpreadsheetNamespace is used like in the WriteAttributeString
above. I am kind of new to xml but was trying to bind it to
urn:schemas-microsoft-com:office:spreadsheet. Do I need an additional line
of code to do this or is it implied by how I am using it?
Thanks again Paul.
--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";
We need to know whether those elements (e.g. Row, Cell) are in a
namespace and which namespace URI that namespace has. And we need to
know which namespace URI the prefix ss is bound to.

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #4

P: n/a
Also under the Workbook I have the following
xmlns:ss = urn:schemas-microsoft-com:office:spreadsheet

--
Paul G
Software engineer.
"Paul" wrote:
Also I am using a namespace when i write out the string attribute for the
cells and rows. I am using the WriteStartElement as described above to write
out the start tags for rows and cells. Thanks Paul.
--
Paul G
Software engineer.
"Paul" wrote:
Hi thanks for the response. When I write out the row I am using the following,
WriteStartElement("Row");
WriteAttributeString("ss","AutofitHeight","Spreads heetNamespace","0");
followed by other write statements for cells/data and closing tags.
So I am using the SpreadsheetNamespace for both rows and cells.
The SpreadsheetNamespace I have defined as
"urn:schemas-microsoft-com:office:spareadsheet". I am using the "ss" prefix
whenever the SpreadsheetNamespace is used like in the WriteAttributeString
above. I am kind of new to xml but was trying to bind it to
urn:schemas-microsoft-com:office:spreadsheet. Do I need an additional line
of code to do this or is it implied by how I am using it?
Thanks again Paul.
--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";
>
We need to know whether those elements (e.g. Row, Cell) are in a
namespace and which namespace URI that namespace has. And we need to
know which namespace URI the prefix ss is bound to.
>
--
>
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
>
Apr 23 '07 #5

P: n/a
additional information, the spreadsheet that I am trying to copy has the
following for the section of interest.
<Cell SS:StyleID="s87">
<ss:Data SS:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
<Font html:Color="#FF0000">M90</Font>
<Font/Thur<Font>
</ss:Data>
</Cell>

--
Paul G
Software engineer.
"Paul" wrote:
Hi I have a .net application and am using the xml writer class to create an
xml file that opens as an excel file. I am trying to write out the following
but am having difficulty.
<Row>
<Cell ss:StyleID="s87">
<ss:Data ss: Type = "String">
<Font html:Color="#FF0000">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
This produces a cell with text with part of the text being red the M90 and
the other part being black.
I also have the following strings set up in my code
string spreadsheetNamespace = "urn:schemas-microsoft-com:office:spreadsheet";
string officeNamespace = "urn:schemas-microsoft-com:office:office";
string excelNamespace = "urn:schemas-microsoft-com:office:excel";
string htmlNamespace = "http://www.w3.org/TR/REC-html40";

thanks.
--
Paul G
Software engineer.
Apr 23 '07 #6

P: n/a
Paul wrote:
Also under the Workbook I have the following
xmlns:ss = urn:schemas-microsoft-com:office:spreadsheet
Here is some sample code that should help, you merely need to use the
WriteStartElement, WriteElementString and WriteAttributeString overloads
that allow you to specify prefix, local name, namespace and value:

const string spreadsheetNamespace =
"urn:schemas-microsoft-com:office:spreadsheet";
const string officeNamespace =
"urn:schemas-microsoft-com:office:office";
const string excelNamespace =
"urn:schemas-microsoft-com:office:excel";
const string htmlNamespace = "http://www.w3.org/TR/REC-html40";

XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create("file.xml",
writerSettings))
{
xmlWriter.WriteStartElement("Row", spreadsheetNamespace);
xmlWriter.WriteStartElement("Cell", spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "StyleID",
spreadsheetNamespace, "s87");
xmlWriter.WriteStartElement("ss", "Data",
spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "Type",
spreadsheetNamespace, "String");
xmlWriter.WriteAttributeString(null, "xmlns", null,
htmlNamespace);
xmlWriter.WriteStartElement("Font", htmlNamespace);
xmlWriter.WriteAttributeString("html", "Color",
htmlNamespace, "#FF0000");
xmlWriter.WriteString("M90");
xmlWriter.WriteEndElement();
xmlWriter.WriteElementString("Font", htmlNamespace,
"/Thu");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}

Result is

<Row xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:StyleID="s87"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
<Font html:Color="#FF0000"
xmlns:html="http://www.w3.org/TR/REC-html40">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
</Row>

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #7

P: n/a
Hi, thanks for the response. I will give it a try.
--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Also under the Workbook I have the following
xmlns:ss = urn:schemas-microsoft-com:office:spreadsheet

Here is some sample code that should help, you merely need to use the
WriteStartElement, WriteElementString and WriteAttributeString overloads
that allow you to specify prefix, local name, namespace and value:

const string spreadsheetNamespace =
"urn:schemas-microsoft-com:office:spreadsheet";
const string officeNamespace =
"urn:schemas-microsoft-com:office:office";
const string excelNamespace =
"urn:schemas-microsoft-com:office:excel";
const string htmlNamespace = "http://www.w3.org/TR/REC-html40";

XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create("file.xml",
writerSettings))
{
xmlWriter.WriteStartElement("Row", spreadsheetNamespace);
xmlWriter.WriteStartElement("Cell", spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "StyleID",
spreadsheetNamespace, "s87");
xmlWriter.WriteStartElement("ss", "Data",
spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "Type",
spreadsheetNamespace, "String");
xmlWriter.WriteAttributeString(null, "xmlns", null,
htmlNamespace);
xmlWriter.WriteStartElement("Font", htmlNamespace);
xmlWriter.WriteAttributeString("html", "Color",
htmlNamespace, "#FF0000");
xmlWriter.WriteString("M90");
xmlWriter.WriteEndElement();
xmlWriter.WriteElementString("Font", htmlNamespace,
"/Thu");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}

Result is

<Row xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:StyleID="s87"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
<Font html:Color="#FF0000"
xmlns:html="http://www.w3.org/TR/REC-html40">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
</Row>

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #8

P: n/a
Hi Martin, I integrated your example into my code and looks like it wrote out
the correct stuff when viewed with notepad but when I open the file in excel
for that cell I get M90 /Thur with empty spaces between the strings.
Also they are both colored black so seems like the Color #FF0000 is not
working. Any suggestions on what might be wrong? Thanks .
--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Also under the Workbook I have the following
xmlns:ss = urn:schemas-microsoft-com:office:spreadsheet

Here is some sample code that should help, you merely need to use the
WriteStartElement, WriteElementString and WriteAttributeString overloads
that allow you to specify prefix, local name, namespace and value:

const string spreadsheetNamespace =
"urn:schemas-microsoft-com:office:spreadsheet";
const string officeNamespace =
"urn:schemas-microsoft-com:office:office";
const string excelNamespace =
"urn:schemas-microsoft-com:office:excel";
const string htmlNamespace = "http://www.w3.org/TR/REC-html40";

XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create("file.xml",
writerSettings))
{
xmlWriter.WriteStartElement("Row", spreadsheetNamespace);
xmlWriter.WriteStartElement("Cell", spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "StyleID",
spreadsheetNamespace, "s87");
xmlWriter.WriteStartElement("ss", "Data",
spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "Type",
spreadsheetNamespace, "String");
xmlWriter.WriteAttributeString(null, "xmlns", null,
htmlNamespace);
xmlWriter.WriteStartElement("Font", htmlNamespace);
xmlWriter.WriteAttributeString("html", "Color",
htmlNamespace, "#FF0000");
xmlWriter.WriteString("M90");
xmlWriter.WriteEndElement();
xmlWriter.WriteElementString("Font", htmlNamespace,
"/Thu");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}

Result is

<Row xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:StyleID="s87"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
<Font html:Color="#FF0000"
xmlns:html="http://www.w3.org/TR/REC-html40">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
</Row>

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #9

P: n/a
Thanks for the help, got it working. I had to write stuff out all on one
line to get rid of the spaces between M90 and /thur.

--
Paul G
Software engineer.
"Martin Honnen" wrote:
Paul wrote:
Also under the Workbook I have the following
xmlns:ss = urn:schemas-microsoft-com:office:spreadsheet

Here is some sample code that should help, you merely need to use the
WriteStartElement, WriteElementString and WriteAttributeString overloads
that allow you to specify prefix, local name, namespace and value:

const string spreadsheetNamespace =
"urn:schemas-microsoft-com:office:spreadsheet";
const string officeNamespace =
"urn:schemas-microsoft-com:office:office";
const string excelNamespace =
"urn:schemas-microsoft-com:office:excel";
const string htmlNamespace = "http://www.w3.org/TR/REC-html40";

XmlWriterSettings writerSettings = new XmlWriterSettings();
writerSettings.Indent = true;
using (XmlWriter xmlWriter = XmlWriter.Create("file.xml",
writerSettings))
{
xmlWriter.WriteStartElement("Row", spreadsheetNamespace);
xmlWriter.WriteStartElement("Cell", spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "StyleID",
spreadsheetNamespace, "s87");
xmlWriter.WriteStartElement("ss", "Data",
spreadsheetNamespace);
xmlWriter.WriteAttributeString("ss", "Type",
spreadsheetNamespace, "String");
xmlWriter.WriteAttributeString(null, "xmlns", null,
htmlNamespace);
xmlWriter.WriteStartElement("Font", htmlNamespace);
xmlWriter.WriteAttributeString("html", "Color",
htmlNamespace, "#FF0000");
xmlWriter.WriteString("M90");
xmlWriter.WriteEndElement();
xmlWriter.WriteElementString("Font", htmlNamespace,
"/Thu");
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
xmlWriter.WriteEndElement();
}

Result is

<Row xmlns="urn:schemas-microsoft-com:office:spreadsheet">
<Cell ss:StyleID="s87"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<ss:Data ss:Type="String" xmlns="http://www.w3.org/TR/REC-html40">
<Font html:Color="#FF0000"
xmlns:html="http://www.w3.org/TR/REC-html40">M90</Font>
<Font>/Thu</Font>
</ss:Data>
</Cell>
</Row>

--

Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Apr 23 '07 #10

This discussion thread is closed

Replies have been disabled for this discussion.