数据表到xml与不同的模式
本文关键字:模式 xml 数据表 | 更新日期: 2023-09-27 18:16:12
我需要将我的数据表数据转换为这个模式(要在MS Excel中打开)。
Datatable Schema
ReferId Name Salary
2457165 ABC 10000
输出模式示例
<Worksheet ss:Name="Sheet1">
<ss:Table>
<ss:Row>
<ss:Cell ss:StyleID="s27"><Data ss:Type="String">Refer_ID</Data></ss:Cell>
<ss:Cell ss:StyleID="s27"><Data ss:Type="String">Name</Data></ss:Cell>
<ss:Cell ss:StyleID="s27"><Data ss:Type="String">Salary</Data></ss:Cell>
</ss:Row>
<ss:Row>
<ss:Cell><Data ss:Type="String">2457165</Data></ss:Cell>
<ss:Cell><Data ss:Type="String">ABC</Data></ss:Cell>
<ss:Cell><Data ss:Type="String">10000</Data></ss:Cell>
</ss:Row>
</ss:Table>
我所尝试的
我试着用DataTable.WriteXml()
写简单的xml
System.IO.StringWriter writer = new System.IO.StringWriter();
dt.WriteXml(writer, XmlWriteMode.WriteSchema, true);
但模式不同。我尝试了一些循环通过行和列的数据表。但是,任何帮助都将是感激的
更新1
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s27">
<Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
</Style>
<Style ss:ID="s21">
<NumberFormat ss:Format="yyyy'-mm'-dd"/>
</Style>
<Style ss:ID="s22">
<NumberFormat ss:Format="yyyy'-mm'-dd' hh:mm:ss"/>
</Style>
<Style ss:ID="s23">
<NumberFormat ss:Format="hh:mm:ss"/>
</Style>
</Styles>
尝试xml linq:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using System.Data;
namespace ConsoleApplication23
{
class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("Refer_Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Salary", typeof(decimal));
dt.Rows.Add(new object[] { 2457165, "ABC", 10000 });
dt.Rows.Add(new object[] { 2457166, "DEF", 20000 });
dt.Rows.Add(new object[] { 2457167, "GHI", 30000 });
string header =
"<?xml version='"1.0'" encoding='"utf-8'" ?>" +
"<?mso-application progid='"Excel.Sheet'"?>" +
"<Workbook" +
" xmlns:x='"urn:schemas-microsoft-com:office:excel'"" +
" xmlns:ss='"urn:schemas-microsoft-com:office:spreadsheet'"" +
" xmlns='"urn:schemas-microsoft-com:office:spreadsheet'">" +
"</Workbook>";
XDocument doc = XDocument.Parse(header);
XElement workbook = doc.Elements().Where(x => x.Name.LocalName == "Workbook").FirstOrDefault();
XNamespace ssNs = workbook.GetNamespaceOfPrefix("ss");
XNamespace xNs = workbook.GetNamespaceOfPrefix("x");
XNamespace defaultNs = workbook.GetDefaultNamespace();
XElement styles = new XElement(defaultNs + "Styles", new object[] {
new XElement(defaultNs + "Style", new object[] {
new XAttribute(ssNs + "ID", "Default"),
new XAttribute(ssNs + "Name", "Normal"),
new XElement(defaultNs + "Alignment", new XAttribute(ssNs + "Vertical","Bottom")),
new XElement(defaultNs + "Borders"),
new XElement(defaultNs + "Font"),
new XElement(defaultNs + "Interior"),
new XElement(defaultNs + "Numberformat"),
new XElement(defaultNs + "Protection")
}),
new XElement(defaultNs + "Style", new object[] {
new XAttribute(ssNs + "ID", "s27"),
new XElement(defaultNs + "Font", new object[] {
new XAttribute(xNs + "Family", "Swiss"),
new XAttribute(ssNs + "Color", "#0000FF"),
new XAttribute(ssNs + "Bold", 1)
}),
}),
new XElement(defaultNs + "Style", new object[] {
new XAttribute(ssNs + "ID", "s21"),
new XElement(defaultNs + "NumberFormat", new XAttribute(ssNs + "Format", "yyyy''-mm''-dd")),
}),
new XElement(defaultNs + "Style", new object[] {
new XAttribute(ssNs + "ID", "s22"),
new XElement(defaultNs + "NumberFormat", new XAttribute(ssNs + "Format", "yyyy''-mm''-dd'' hh:mm:ss")),
}),
new XElement(defaultNs + "Style", new object[] {
new XAttribute(ssNs + "ID", "s23"),
new XElement(defaultNs + "NumberFormat", new XAttribute(ssNs + "Format", "hh:mm:ss")),
})
});
workbook.Add(styles);
XElement table = new XElement(ssNs + "Table");
workbook.Add(table);
XElement newRow = new XElement(ssNs + "Row");
table.Add(newRow);
foreach (DataColumn col in dt.Columns)
{
newRow.Add(new XElement(ssNs + "Cell", new object[] {
new XAttribute(ssNs + "StyleID", "s27"),
new XElement(ssNs + "Data", new object[] {
new XAttribute(ssNs + "Type", "String"),
col.ColumnName
})
}));
}
foreach (DataRow row in dt.AsEnumerable())
{
newRow = new XElement(ssNs + "Row");
table.Add(newRow);
string[] itemArray = row.ItemArray.Select(x => x.ToString()).ToArray();
foreach (string item in itemArray)
{
newRow.Add(new XElement(ssNs + "Cell", new object[] {
new XElement(ssNs + "Data", new object[] {
new XAttribute(ssNs + "Type", "String"),
item
})
}));
}
}
}
}
}