This article[
^] will help you to get started, after which you can study the various extra features available.
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>
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");
}
}
}
}
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)