using System.Xml;
using System.Web;
轉換一般的XML
public XmlDocument transDataTableToXMLDocument(DataTable dt)
{
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.CreateXmlDeclaration("1.0", "utf-8", "yes");
XmlNode rootNode = xmlDoc.CreateElement("Data");
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
XmlNode RowNode = xmlDoc.CreateElement("Row");
foreach (DataColumn dc in dt.Columns)
{
XmlNode ColumnNode = xmlDoc.CreateElement(dc.ColumnName);
ColumnNode.InnerText = dr[dc.ColumnName].ToString();
RowNode.AppendChild(ColumnNode);
}
rootNode.AppendChild(RowNode);
}
}
xmlDoc.AppendChild(rootNode);
return xmlDoc;
}
轉換 Excel 格式的 XML
public XmlDocument transDataTableToXMLDocument_ExcelFormat(List<DataTable> dts, List<string> tabNames)
{
if(!(dts != null && tabNames != null && dts.Count == tabNames.Count)){
throw new Exception("Number of datatable and tabname are not the same.");
}
XmlDocument xmlDoc = new XmlDocument();
string EXCEL_NS = "urn:schemas-microsoft-com:office:spreadsheet";
xmlDoc.CreateXmlDeclaration("1.0", "utf-8", "yes");
xmlDoc.CreateProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");
// create a Workbook and setting excel format
XmlNode WorkbookNode = xmlDoc.CreateElement("Workbook", EXCEL_NS);
XmlAttribute WorkbookNode_xmlns_ss = xmlDoc.CreateAttribute("xmlns", "ss", "http://www.w3.org/2000/xmlns/");
WorkbookNode_xmlns_ss.Value = EXCEL_NS;
WorkbookNode.Attributes.Append(WorkbookNode_xmlns_ss);
// create Sytles
XmlNode StylesNode = xmlDoc.CreateElement("Styles", EXCEL_NS);
// create HeaderStyle
XmlNode HeaderStyleNode = xmlDoc.CreateElement("Style", EXCEL_NS);
XmlAttribute HeaderStyleNode_ss_ID = xmlDoc.CreateAttribute("ss", "ID", EXCEL_NS);
HeaderStyleNode_ss_ID.Value = "HeaderStyle";
HeaderStyleNode.Attributes.Append(HeaderStyleNode_ss_ID);
XmlNode AlignmentNode = xmlDoc.CreateElement("Alignment", EXCEL_NS);
XmlAttribute AlignmentNode_ss_Horizontal = xmlDoc.CreateAttribute("ss", "Horizontal", EXCEL_NS);
XmlAttribute AlignmentNode_ss_Vertical = xmlDoc.CreateAttribute("ss", "Vertical", EXCEL_NS);
AlignmentNode_ss_Horizontal.Value = "Center";
AlignmentNode_ss_Vertical.Value = "Center";
AlignmentNode.Attributes.Append(AlignmentNode_ss_Horizontal);
AlignmentNode.Attributes.Append(AlignmentNode_ss_Vertical);
HeaderStyleNode.AppendChild(AlignmentNode);
XmlNode FontNode = xmlDoc.CreateElement("Font", EXCEL_NS);
XmlAttribute FontNode_ss_Bold = xmlDoc.CreateAttribute("ss", "Bold", EXCEL_NS);
FontNode_ss_Bold.Value = "1";
FontNode.Attributes.Append(FontNode_ss_Bold);
HeaderStyleNode.AppendChild(FontNode);
StylesNode.AppendChild(HeaderStyleNode);
// create TextStyle
XmlNode TextStyleNode = xmlDoc.CreateElement("Style", EXCEL_NS);
XmlAttribute TextStyleNode_ss_ID = xmlDoc.CreateAttribute("ss", "ID", EXCEL_NS);
TextStyleNode_ss_ID.Value = "TextStyle";
TextStyleNode.Attributes.Append(TextStyleNode_ss_ID);
XmlNode TextAlignmentNode = xmlDoc.CreateElement("Alignment", EXCEL_NS);
XmlAttribute TextAlignmentNode_ss_WrapText = xmlDoc.CreateAttribute("ss", "WrapText", EXCEL_NS);
TextAlignmentNode_ss_WrapText.Value = "1";
TextAlignmentNode.Attributes.Append(TextAlignmentNode_ss_WrapText);
TextStyleNode.AppendChild(TextAlignmentNode);
StylesNode.AppendChild(TextStyleNode);
WorkbookNode.AppendChild(StylesNode);
if (dts != null && dts.Count > 0)
{
for (int i = 0; i < dts.Count; i++)
{
DataTable dt = dts[i];
// create Worksheet
XmlNode WorksheetNode = xmlDoc.CreateElement("Worksheet", EXCEL_NS);
XmlAttribute WorksheetNode_ss_Name = xmlDoc.CreateAttribute("ss", "Name", EXCEL_NS);
WorksheetNode_ss_Name.Value = tabNames[i];
WorksheetNode.Attributes.Append(WorksheetNode_ss_Name);
XmlNode TableNode = xmlDoc.CreateElement("Table", EXCEL_NS);
// create column style
if (dt != null && dt.Columns != null && dt.Columns.Count > 0)
{
foreach (DataColumn dc in dt.Columns)
{
XmlNode ColumnNode = xmlDoc.CreateElement("Column", EXCEL_NS);
XmlAttribute ColumnNode_ss_Width = xmlDoc.CreateAttribute("ss", "Width", EXCEL_NS);
string columnWidth = "100";
if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
{
if (dc.DataType == System.Type.GetType("System.Decimal"))
{
IEnumerable<decimal> tmp = dt.AsEnumerable().Where(p => p.Field<decimal?>(dc.ColumnName).HasValue)
.Select(p => p.Field<decimal?>(dc.ColumnName).Value);
if (tmp != null && tmp.Count() > 0)
{
columnWidth = (tmp.Select(p => Convert.ToString(p).Length).Max() * 8).ToString();
}
}
else if (dc.DataType == System.Type.GetType("System.String"))
{
IEnumerable<string> tmp = dt.AsEnumerable().Where(p => p.Field<string>(dc.ColumnName) != null)
.Select(p => p.Field<string>(dc.ColumnName));
if (tmp != null && tmp.Count() > 0)
{
columnWidth = (tmp.Select(p => p.Length).Max() * 8).ToString();
}
}
else if (dc.DataType == System.Type.GetType("System.DateTime"))
{
columnWidth = "160";
}
}
if (((dc.ColumnName.Length) * 8) > Convert.ToInt16(columnWidth))
{
columnWidth = (dc.ColumnName.Length * 8).ToString();
}
if (Convert.ToInt16(columnWidth) > 200)
{
columnWidth = "200";
}
ColumnNode_ss_Width.Value = columnWidth;
ColumnNode.Attributes.Append(ColumnNode_ss_Width);
TableNode.AppendChild(ColumnNode);
}
}
// create row header
XmlNode RowHeaderNode = xmlDoc.CreateElement("Row", EXCEL_NS);
XmlAttribute RowHeaderNode_ss_StyleID = xmlDoc.CreateAttribute("ss", "StyleID", EXCEL_NS);
RowHeaderNode_ss_StyleID.Value = "HeaderStyle";
RowHeaderNode.Attributes.Append(RowHeaderNode_ss_StyleID);
if (dt != null && dt.Columns != null && dt.Columns.Count > 0)
{
foreach (DataColumn dc in dt.Columns)
{
XmlNode CellNode = xmlDoc.CreateElement("Cell", EXCEL_NS);
XmlNode DataNode = xmlDoc.CreateElement("Data", EXCEL_NS);
XmlAttribute DataNode_ss_Type = xmlDoc.CreateAttribute("ss", "Type", EXCEL_NS);
DataNode_ss_Type.Value = "String";
DataNode.Attributes.Append(DataNode_ss_Type);
DataNode.InnerText = dc.ColumnName;
CellNode.AppendChild(DataNode);
RowHeaderNode.AppendChild(CellNode);
}
}
TableNode.AppendChild(RowHeaderNode);
// create row data
if (dt != null && dt.Rows != null && dt.Rows.Count > 0 && dt.Columns != null && dt.Columns.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
XmlNode RowNode = xmlDoc.CreateElement("Row", EXCEL_NS);
XmlAttribute RowNode_ss_StyleID = xmlDoc.CreateAttribute("ss", "StyleID", EXCEL_NS);
RowNode_ss_StyleID.Value = "TextStyle";
RowNode.Attributes.Append(RowNode_ss_StyleID);
foreach (DataColumn dc in dt.Columns)
{
XmlNode CellNode = xmlDoc.CreateElement("Cell", EXCEL_NS);
XmlNode DataNode = xmlDoc.CreateElement("Data", EXCEL_NS);
XmlAttribute DataNode_ss_Type = xmlDoc.CreateAttribute("ss", "Type", EXCEL_NS);
DataNode_ss_Type.Value = "String";
DataNode.Attributes.Append(DataNode_ss_Type);
DataNode.InnerText = dr[dc.ColumnName].ToString();
CellNode.AppendChild(DataNode);
RowNode.AppendChild(CellNode);
}
TableNode.AppendChild(RowNode);
}
}
WorksheetNode.AppendChild(TableNode);
WorkbookNode.AppendChild(WorksheetNode);
}
}
xmlDoc.AppendChild(WorkbookNode);
return xmlDoc;
}
下載XML
public void downloadDataTableToXMLFile(List<DataTable> dts, List<string> tabNames, string fileName)
{
CommonService cs = new CommonService();
XmlDocument doc = cs.transDataTableToXMLDocument_ExcelFormat(dts, tabNames);
MemoryStream ms = new MemoryStream();
using (XmlWriter writer = XmlWriter.Create(ms))
{
doc.WriteTo(writer); // Write to memorystream
}
byte[] data = ms.ToArray();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.Expires = 0;
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.AddHeader("Content-Disposition",
"attachment;filename=" + HttpUtility.UrlEncode(fileName) + ";size=" + data.Length.ToString());
HttpContext.Current.Response.ContentType = "Application/octet-stream";
HttpContext.Current.Response.BinaryWrite(data);
HttpContext.Current.Response.End();
ms.Flush(); // Probably not needed
ms.Close();
}
