使用格式获取单元格值 [英] getting cell value with format

问题描述

我想获取包括其格式的excel单元格值，然后创建具有相同内容的xml文件

请让我知道如何达到相同的目标


谢谢与问候，
Soumya

Hi,

I would like to get the excel cell value including it''s format and then create an xml file with the same content

Pls let me know how can I achive the same


Thanks & Regards,
Soumya

推荐答案

本文 [

This article[^] will help you to get started, after which you can study the various extra features available.

感谢您的回复

但我想得到像这样的单元格值

<b>你好</b> <如何< i> <big>您是</big>

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>

我可以使用以下编码来解决



Excel1.Range xlRange = oSheet.UsedRange;
字符串添加= xlRange.Cells.Address;

如果(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 ++)
{

字符串abc =";
xlRange =(Microsoft.Office.Interop.Excel.Range)oSheet.Cells [row，col];
if(((xlRange.Text ==")||(xlRange.Text == null))
{}
其他
{
string [] xlr_text = xlRange.Text.ToString().Split('''');
int count = xlr_text.Length;
int长度= xlRange.Text.ToString().Length +1;
for(int i1 = 0; i1< length; i1 ++)
{
如果(c ==")
{
c = xlRange.get_Characters(st_count，1).Text;
//if(((c == null)||(c =="))
如果((c == null))
{
}
否则(c ==")
{
c ="Space";
}


其他
{
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;
}

}
其他
{
isBold = false;
isUnderlined = false;
isitalic = false;

c = xlRange.get_Characters(st_count，1).Text;
如果((c == null)||(c =="))
//if(((c == null)||(c ==")||(c =="))
{
strBold ="False";
strUnderlined ="False";
stritalic ="False";
}
否则(c ==")
{
c ="Space";
}

其他
{
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;


如果(((strBold.Equals("true"，StringComparison.CurrentCultureIgnoreCase)))||((strBold.Equals("，StringComparison.CurrentCultureIgnoreCase)))
{
isBold = true;
}

如果(((stritalic.Equals("true"，StringComparison.CurrentCultureIgnoreCase)))||((stritalic.Equals("，StringComparison.CurrentCultureIgnoreCase)))
{
isitalic = true;
}
//由于某种原因，下划线不是true/false，而是2/-4142
if(((strUnderlined.Equals("2"，StringComparison.CurrentCultureIgnoreCase))||((strUnderlined.Equals("，StringComparison.CurrentCultureIgnoreCase)))
{
isUnderlined = true;
}

如果(isBold)
输出+ =< b>" ;;
如果(是带下划线的)
输出+ =< u>" ;;
如果(以斜体显示)
输出+ =< i>" ;;
输出+ = c;

如果(以斜体显示)
输出+ =</i>" ;;
如果(是带下划线的)
输出+ =</u>" ;;
如果(isBold)
输出+ =</b>" ;;
}
}

输出= output.Replace(</u>< u>"，");
输出= output.Replace(</i>< i>"，");
输出= output.Replace(</b>< b>"，");

输出= output.Replace(</u>< u>"，");
输出= output.Replace(</i>< i>"，");
输出= output.Replace(</b>< b>"，");

输出= output.Replace("Space"，");

list1.Add(输出);
txt_input.Text =输出;

st_count = 0;
c =";
输出=";
}

}
}
}


export_xml(list1);
oXL.Workbooks.Close();
oXL.Quit();







void export_xml(List< string>值)
{
int count = value.Count;

for(int a = 0; a< count; a ++)
{

如果(value [a]！=")
{
字符串路径=路径"；
XmlDocument doc =新的XmlDocument();
如果(！File.Exists(path +"\\ xmldemo.xml"))
{
XmlTextWriter xWriter =新的XmlTextWriter(路径+"\\ xmldemo.xml"，Encoding.UTF8);
xWriter.WriteStartDocument();

xWriter.WriteStartElement("EmployeeDetails");

xWriter.WriteEndElement();
xWriter.WriteEndDocument();
xWriter.Close();

doc.Load(path +"\\ xmldemo.xml");
XmlNode节点= doc.CreateNode(XmlNodeType.Element，"Details"，null);

//创建节点的第一个子节点



XmlNode c_val = doc.CreateElement("cell" + a);
//设置值

c_val.InnerXml =值[a];

//创建节点的第二个子节点

//将子级添加到父亲
node.AppendChild(c_val);

//找到要添加新节点的节点
XmlNodeList l = doc.GetElementsByTagName("EmployeeDetails");
//追加新节点
l [0] .AppendChild(node);

//保存文件
doc.Save("xmldemo.xml");


}
其他
{


doc.Load(path +"\\ xmldemo.xml");

//创建主节点
XmlNode节点= doc.CreateNode(XmlNodeType.Element，"Details"，null);

//创建节点的第一个子节点

XmlNode c_val = doc.CreateElement("cell" + a);
//设置值

c_val.InnerXml =值[a];

//创建节点的第二个子节点

//将子级添加到父亲
node.AppendChild(c_val);

//找到要添加新节点的节点
XmlNodeList l = doc.GetElementsByTagName("Details");
//追加新节点
l [0] .AppendChild(node);
//保存文件
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");
}
}


}



}

这篇关于使用格式获取单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！