I could solve with the below coding
Excel1.Range xlRange = oSheet.UsedRange;
string add = xlRange.Cells.Address;
if (xlRange != null)
{
int nRows = xlRange.Rows.Count;
int nCols = xlRange.Columns.Count;
for (int row = 1; row <= nRows; row++)
{
for (int col = 1; col <= nCols; col++)
{
string abc = "";
xlRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[row, col];
if ((xlRange.Text == "") || (xlRange.Text == null))
{ }
else
{
string[] xlr_text = xlRange.Text.ToString().Split(' ');
int count = xlr_text.Length;
int length = xlRange.Text.ToString().Length + 1;
for (int i1 = 0; i1 < length; i1++)
{
if (c == "")
{
c = xlRange.get_Characters(st_count, 1).Text;
//if ((c == null) || (c == " "))
if ((c == null))
{
}
else if (c == " ")
{
c = "Space";
}
else
{
strBold = xlRange.get_Characters(st_count, 1).Font.Bold.ToString();
strUnderlined = xlRange.get_Characters(st_count, 1).Font.Underline.ToString();
stritalic = xlRange.get_Characters(st_count, 1).Font.Italic.ToString();
st_count += 1;
}
}
else
{
isBold = false;
isUnderlined = false;
isitalic = false;
c = xlRange.get_Characters(st_count, 1).Text;
if ((c == null) || (c == ""))
//if ((c == null) || (c == " ") || (c == ""))
{
strBold = "False";
strUnderlined = "False";
stritalic = "False";
}
else if (c == " ")
{
c = "Space";
}
else
{
strBold = "";
strUnderlined = "";
stritalic = "";
strBold = xlRange.get_Characters(st_count, 1).Font.Bold.ToString();
strUnderlined = xlRange.get_Characters(st_count, 1).Font.Underline.ToString();
stritalic = xlRange.get_Characters(st_count, 1).Font.Italic.ToString();
}
}
st_count += 1;
if ((strBold.Equals("true", StringComparison.CurrentCultureIgnoreCase)) || (strBold.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isBold = true;
}
if ((stritalic.Equals("true", StringComparison.CurrentCultureIgnoreCase)) || (stritalic.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isitalic = true;
}
// for some reason underline is not true/false but it is 2/-4142
if ((strUnderlined.Equals("2", StringComparison.CurrentCultureIgnoreCase)) || (strUnderlined.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isUnderlined = true;
}
if (isBold)
output += "<b>";
if (isUnderlined)
output += "<u>";
if (isitalic)
output += "<i>";
output += c;
if (isitalic)
output += "</i>";
if (isUnderlined)
output += "</u>";
if (isBold)
output += "</b>";
}
}
output = output.Replace("</u><u>", "");
output = output.Replace("</i><i>", "");
output = output.Replace("</b><b>", "");
output = output.Replace("</u><u>", "");
output = output.Replace("</i><i>", "");
output = output.Replace("</b><b>", "");
output = output.Replace("Space", " ");
list1.Add(output);
txt_input.Text = output;
st_count = 0;
c = "";
output = "";
}
}
}
}
export_xml(list1);
oXL.Workbooks.Close();
oXL.Quit();
void export_xml(List<string> value )
{
int count = value.Count;
for (int a = 0; a < count; a++)
{
if (value[a] != "")
{
string path = "path";
XmlDocument doc = new XmlDocument();
if (!File.Exists(path + "\\xmldemo.xml"))
{
XmlTextWriter xWriter = new XmlTextWriter(path + "\\xmldemo.xml", Encoding.UTF8);
xWriter.WriteStartDocument();
xWriter.WriteStartElement("EmployeeDetails");
xWriter.WriteEndElement();
xWriter.WriteEndDocument();
xWriter.Close();
doc.Load(path + "\\xmldemo.xml");
XmlNode node = doc.CreateNode(XmlNodeType.Element, "Details", null);
//create the nodes first child
XmlNode c_val = doc.CreateElement("cell"+a );
//set the value
c_val.InnerXml = value [a];
//create the nodes second child
// add childes to father
node.AppendChild(c_val);
// find the node we want to add the new node to
XmlNodeList l = doc.GetElementsByTagName("EmployeeDetails");
// append the new node
l[0].AppendChild(node);
// save the file
doc.Save("xmldemo.xml");
}
else
{
doc.Load(path + "\\xmldemo.xml");
//create main node
XmlNode node = doc.CreateNode(XmlNodeType.Element, "Details", null);
//create the nodes first child
XmlNode c_val = doc.CreateElement("cell" + a);
//set the value
c_val.InnerXml = value[a];
//create the nodes second child
// add childes to father
node.AppendChild(c_val);
// find the node we want to add the new node to
XmlNodeList l = doc.GetElementsByTagName("Details");
// append the new node
l[0].AppendChild(node);
// save the file
doc.Save("xmldemo.xml");
}
}
}
}
I could solve with the below coding
Excel1.Range xlRange = oSheet.UsedRange;
string add = xlRange.Cells.Address;
if (xlRange != null)
{
int nRows = xlRange.Rows.Count;
int nCols = xlRange.Columns.Count;
for (int row = 1; row <= nRows; row++)
{
for (int col = 1; col <= nCols; col++)
{
string abc = "";
xlRange = (Microsoft.Office.Interop.Excel.Range)oSheet.Cells[row, col];
if ((xlRange.Text == "") || (xlRange.Text == null))
{ }
else
{
string[] xlr_text = xlRange.Text.ToString().Split(' ');
int count = xlr_text.Length;
int length = xlRange.Text.ToString().Length + 1;
for (int i1 = 0; i1 < length; i1++)
{
if (c == "")
{
c = xlRange.get_Characters(st_count, 1).Text;
//if ((c == null) || (c == " "))
if ((c == null))
{
}
else if (c == " ")
{
c = "Space";
}
else
{
strBold = xlRange.get_Characters(st_count, 1).Font.Bold.ToString();
strUnderlined = xlRange.get_Characters(st_count, 1).Font.Underline.ToString();
stritalic = xlRange.get_Characters(st_count, 1).Font.Italic.ToString();
st_count += 1;
}
}
else
{
isBold = false;
isUnderlined = false;
isitalic = false;
c = xlRange.get_Characters(st_count, 1).Text;
if ((c == null) || (c == ""))
//if ((c == null) || (c == " ") || (c == ""))
{
strBold = "False";
strUnderlined = "False";
stritalic = "False";
}
else if (c == " ")
{
c = "Space";
}
else
{
strBold = "";
strUnderlined = "";
stritalic = "";
strBold = xlRange.get_Characters(st_count, 1).Font.Bold.ToString();
strUnderlined = xlRange.get_Characters(st_count, 1).Font.Underline.ToString();
stritalic = xlRange.get_Characters(st_count, 1).Font.Italic.ToString();
}
}
st_count += 1;
if ((strBold.Equals("true", StringComparison.CurrentCultureIgnoreCase)) || (strBold.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isBold = true;
}
if ((stritalic.Equals("true", StringComparison.CurrentCultureIgnoreCase)) || (stritalic.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isitalic = true;
}
// for some reason underline is not true/false but it is 2/-4142
if ((strUnderlined.Equals("2", StringComparison.CurrentCultureIgnoreCase)) || (strUnderlined.Equals("", StringComparison.CurrentCultureIgnoreCase)))
{
isUnderlined = true;
}
if (isBold)
output += "<b>";
if (isUnderlined)
output += "<u>";
if (isitalic)
output += "<i>";
output += c;
if (isitalic)
output += "</i>";
if (isUnderlined)
output += "</u>";
if (isBold)
output += "</b>";
}
}
output = output.Replace("</u><u>", "");
output = output.Replace("</i><i>", "");
output = output.Replace("</b><b>", "");
output = output.Replace("</u><u>", "");
output = output.Replace("</i><i>", "");
output = output.Replace("</b><b>", "");
output = output.Replace("Space", " ");
list1.Add(output);
txt_input.Text = output;
st_count = 0;
c = "";
output = "";
}
}
}
}
export_xml(list1);
oXL.Workbooks.Close();
oXL.Quit();
void export_xml(List<string> value )
{
int count = value.Count;
for (int a = 0; a < count; a++)
{
if (value[a] != "")
{
string path = "path";
XmlDocument doc = new XmlDocument();
if (!File.Exists(path + "\\xmldemo.xml"))
{
XmlTextWriter xWriter = new XmlTextWriter(path + "\\xmldemo.xml", Encoding.UTF8);
xWriter.WriteStartDocument();
xWriter.WriteStartElement("EmployeeDetails");
xWriter.WriteEndElement();
xWriter.WriteEndDocument();
xWriter.Close();
doc.Load(path + "\\xmldemo.xml");
XmlNode node = doc.CreateNode(XmlNodeType.Element, "Details", null);
//create the nodes first child
XmlNode c_val = doc.CreateElement("cell"+a );
//set the value
c_val.InnerXml = value [a];
//create the nodes second child
// add childes to father
node.AppendChild(c_val);
// find the node we want to add the new node to
XmlNodeList l = doc.GetElementsByTagName("EmployeeDetails");
// append the new node
l[0].AppendChild(node);
// save the file
doc.Save("xmldemo.xml");
}
else
{
doc.Load(path + "\\xmldemo.xml");
//create main node
XmlNode node = doc.CreateNode(XmlNodeType.Element, "Details", null);
//create the nodes first child
XmlNode c_val = doc.CreateElement("cell" + a);
//set the value
c_val.InnerXml = value[a];
//create the nodes second child
// add childes to father
node.AppendChild(c_val);
// find the node we want to add the new node to
XmlNodeList l = doc.GetElementsByTagName("Details");
// append the new node
l[0].AppendChild(node);
// save the file
doc.Save("xmldemo.xml");
}
}
}
}
Thank you for your response
But I would like to get the cell value like this
<b>hello</b> <i>how</i> <big>are you</big>
This article[
^] will help you to get started, after which you can study the various extra features available.
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)