/// <summary>
/// 修复生成的Xml文件，把Row和Cell的ss:Index全部加上
/// </summary>
/// <param name="xmlPath"></param>
public static void RepairXml(string xmlPath)
{
//先替换掉无用的内容
int instr; string xmln;
Encoding code = Encoding.GetEncoding("utf-8");
StreamReader sr = new StreamReader(xmlPath, code);
string content = sr.ReadToEnd();
xmln = "<?mso-application progid=\"Excel.Sheet\"?>";
instr = content.IndexOf(xmln);
content = content.Substring(0, instr) + content.Substring(instr + xmln.Length);
//content = content.Replace("<?mso-application progid=\"Excel.Sheet\"?>","");
xmln = "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"";
instr = content.IndexOf(xmln);
content = content.Substring(0, instr) + content.Substring(instr + xmln.Length);
sr.Close();
StreamWriter sw = new StreamWriter(xmlPath, false, code);
sw.Write(content);
sw.Close();
int rowIndex;
int colIndex;
XmlDocument xml = new XmlDocument();
xml.Load(xmlPath);
XmlNamespaceManager nsmgr = createXmlNameSpace(xml);
XmlNodeList NodeWorksheets = xml.DocumentElement.SelectNodes("Worksheet", nsmgr);
#region Sheet 循环
foreach (XmlNode Worksheet in NodeWorksheets)
{
rowIndex = 1;
XmlNodeList Rows = Worksheet.SelectNodes("Table/Row", nsmgr);
#region Row 循环
foreach (XmlNode row in Rows)
{
colIndex = 1;
if (row.Attributes["ss:Index"] == null)
{//如果 ss:Index 不存在，则补上
//Create a new attribute.
string ns = row.GetNamespaceOfPrefix("ss");
XmlNode attr = xml.CreateNode(XmlNodeType.Attribute, "ss:Index", ns);
attr.Value = rowIndex.ToString();
//Add the attribute to the document.
row.Attributes.SetNamedItem(attr);
rowIndex++;
}
else
{
rowIndex = int.Parse(row.Attributes["ss:Index"].Value) + 1;
}
XmlNodeList Cols = row.SelectNodes("Cell", nsmgr);
foreach (XmlNode col in Cols)
{
if (col.Attributes["ss:Index"] == null)
{//如果 ss:Index 不存在，则补上
//Create a new attribute.
string n = col.GetNamespaceOfPrefix("ss");
XmlNode at = xml.CreateNode(XmlNodeType.Attribute, "ss:Index", n);
at.Value = colIndex.ToString();
//Add the attribute to the document.
col.Attributes.SetNamedItem(at);
colIndex++;
}
else
{
colIndex = int.Parse(col.Attributes["ss:Index"].Value) + 1;
}
}
}
#endregion
}
#endregion
xml.Save(xmlPath);
}
public static void RestoreXls(string xmlPath, string xlsPath)
{
int instr; string xmln;
Encoding code = Encoding.GetEncoding("utf-8");
StreamReader sr = new StreamReader(xmlPath, code);
string content = sr.ReadToEnd();
xmln = "<?xml version=\"1.0\"?>";
instr = content.IndexOf(xmln);
content = xmln + "<?mso-application progid=\"Excel.Sheet\"?>" + content.Substring(instr + xmln.Length);
xmln = "<Workbook";
instr = content.IndexOf(xmln);
content = content.Substring(0, instr + xmln.Length) + " xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"" + content.Substring(instr + xmln.Length);
sr.Close();
StreamWriter sw = new StreamWriter(xlsPath, false, code);
sw.Write(content);
sw.Close();
}
/// <summary>
/// 提起Excel中的某些Sheet生成新的Excel，单个保存
/// </summary>
/// <param name="xmlPath">由Excel得到的Xml</param>
/// <param name="sheetName">待提起的SheetName数组</param>
/// <param name="savePath">保存路径</param>
public static void CreateXsl(string xmlPath, string savePath)
{
XmlDocument xml = new XmlDocument();
xml.Load(xmlPath);
XmlDocument xsl;
string content = "<?xml version=\"1.0\"?><Workbook xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
content += xml.DocumentElement.ChildNodes[0].OuterXml;
content += xml.DocumentElement.ChildNodes[1].OuterXml;
content += xml.DocumentElement.ChildNodes[2].OuterXml;
XmlNodeList Worksheets;
Worksheets = xml.DocumentElement.SelectNodes("Worksheet");
foreach (XmlNode worksheet in Worksheets)
{
xsl = new XmlDocument();
xsl.LoadXml(content + worksheet.OuterXml + "</Workbook>");
xsl.Save(savePath + "\\" + worksheet.Attributes["ss:Name"].Value + ".xml");
}
}
/// <summary>
/// 添加子节点
/// </summary>
/// <param name="parentNode"></param>
/// <param name="nodeName"></param>
/// <param name="nodeValue"></param>
/// <returns></returns>
public static XmlNode addChildNode(XmlNode parentNode, string nodeName, string nodeValue)
{
return addChildNode(parentNode, nodeName, nodeValue, string.Empty);
}
/// <summary>
/// 添加子节点
/// </summary>
/// <param name="parentNode"></param>
/// <param name="nodeName"></param>
/// <param name="nodeValue"></param>
/// <param name="nodeNameSpace"></param>
/// <returns></returns>
public static XmlNode addChildNode(XmlNode parentNode, string nodeName, string nodeValue, string nodeNameSpace)
{
XmlNode node = parentNode.OwnerDocument.CreateNode(XmlNodeType.Element, nodeName, nodeNameSpace);
node.InnerText = nodeValue;
parentNode.AppendChild(node);
return node;
}
/// <summary>
/// 添加属性
/// </summary>
/// <param name="parentNode"></param>
/// <param name="attName"></param>
/// <param name="attValue"></param>
/// <returns></returns>
public static XmlAttribute addAttribute(XmlNode parentNode, string attName, string attValue)
{
XmlAttribute attr = parentNode.Attributes["attName"];
if (attr == null)
{
attr = parentNode.OwnerDocument.CreateAttribute(attName);
parentNode.Attributes.Append(attr);
}
attr.Value = attValue;
return attr;
}
/// <summary>
/// 添加属性
/// </summary>
/// <param name="parentNode"></param>
/// <param name="attName"></param>
/// <param name="attValue"></param>
/// <param name="nodeNameSpace"></param>
/// <returns></returns>
public static XmlAttribute addAttribute(XmlNode parentNode, string attName, string attValue, string nodeNameSpace)
{
string n = parentNode.GetNamespaceOfPrefix(nodeNameSpace);
XmlNode attr = parentNode.OwnerDocument.CreateNode(XmlNodeType.Attribute,attName,n);
attr.Value = attValue;
parentNode.Attributes.SetNamedItem(attr);
return (XmlAttribute)attr;
}
***********************
XPath 模糊查询
xml.DocumentElement.SelectSingleNode("//*[contains(@id,'黄') and contains(@id,'杰')]")
xml.DocumentElement.SelectSingleNode("Table/Row/Cell/Data[contains(.,'黄') and contains(.,'杰')]")
<?xml version="1.0" encoding="gb2312"?>
<Log>
<WorkBook id="黄杰"/>
<Table>
<Row>
<Cell><Data>黄杰</Data></Cell>
</Row>
</Table>
</Log>