我有一个包含我的项目小数据的xml文档,我想将它转换为Excel文件(Microsoft Office Excel 2003及以上版本)
我该如何以编程方式实现这个目标?
您可以使用以下代码来实现:
首先声明必要的引用。
Microsoft.Office.Interop.Excel
using System;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Diagnostics;
using Microsoft.Win32;
Excel.Application excel2; // Create Excel app
Excel.Workbook DataSource; // Create Workbook
Excel.Worksheet DataSheet; // Create Worksheet
excel2 = new Excel.Application(); // Start an Excel app
DataSource = (Excel.Workbook)excel2.Workbooks.Add(1); // Add a Workbook inside
string tempFolder = System.IO.Path.GetTempPath(); // Get folder
string FileName = openFileDialog1.FileName.ToString(); // Get xml file name
接下来,在循环中使用以下代码以确保复制 XML 文件中的所有项
// Open that xml file with excel
DataSource = excel2.Workbooks.Open(FileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// Get items from xml file
DataSheet = DataSource.Worksheets.get_Item(1);
// Create another Excel app as object
Object xl_app;
xl_app = GetObj(1, "Excel.Application");
Excel.Application xlApp = (Excel.Application)xl_app;
// Set previous Excel app (Xml) as ReportPage
Excel.Application ReportPage = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
// Copy items from ReportPage(Xml) to current Excel object
Excel.Workbook Copy_To_Excel = ReportPage.ActiveWorkbook;
Copy_To_Excel.Save("thePath\theFileName");
Copy_To_Excel.Close();
你甚至可以将XML文件读取为字符串,并使用正则表达式来读取标签之间的内容并创建CSV文件,或者使用xpath表达式来读取XML文件数据并导出到CSV文件。
1. 将xml文件填入数据集中,
2. 通过以下方法在asp.net中将数据集转换为Excel
这些都是非常简单的方法。
public static void Convert(System.Data.DataSet ds, System.Web.HttpResponse response)
{
//first let's clean up the response.object
response.Clear();
response.Charset = "";
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[0];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}
我不知道有什么简单的方法可以将基于xml的电子表格转换为xls/xlsx。但你可以研究一下Microsoft Open Xml SDK,它可以让你处理xlsx文件。你可以构建一个xlsx电子表格,然后将数据填入其中。在open-xml SDK的层面上,这就像构建一个xml文件。
对于数组元素,请使用逗号“,”分隔,并重复使用相同的列名
1)XML函数
public static class XMLFunctions
{
public static List<Tuple<string, string>> GetXMlTagsAndValues(string xml)
{
var xmlList = new List<Tuple<string, string>>();
var doc = XDocument.Parse(xml);
foreach (var element in doc.Descendants())
{
// we don't care about the parent tags
if (element.Descendants().Count() > 0)
{
continue;
}
var path = element.AncestorsAndSelf().Select(e => e.Name.LocalName).Reverse();
var xPath = string.Join("/", path);
xmlList.Add(Tuple.Create(xPath, element.Value));
}
return xmlList;
}
public static System.Data.DataTable CreateDataTableFromXmlFile(string xmlFilePath)
{
System.Data.DataTable Dt = new System.Data.DataTable();
string input = File.ReadAllText(xmlFilePath);
var xmlTagsAndValues = GetXMlTagsAndValues(input);
var columnList = new List<string>();
foreach(var xml in xmlTagsAndValues)
{
if(!columnList.Contains(xml.Item1))
{
columnList.Add(xml.Item1);
Dt.Columns.Add(xml.Item1, typeof(string));
}
}
DataRow dtrow = Dt.NewRow();
var columnList2 = new Dictionary<string, string>();
foreach (var xml in xmlTagsAndValues)
{
if (!columnList2.Keys.Contains(xml.Item1))
{
dtrow[xml.Item1] = xml.Item2;
columnList2.Add(xml.Item1, xml.Item2);
}
else
{ // Here we are using the same column but appending the next value
dtrow[xml.Item1] = columnList2[xml.Item1] + "," + xml.Item2;
columnList2[xml.Item1] = columnList2[xml.Item1] + "," + xml.Item2;
}
}
Dt.Rows.Add(dtrow);
return Dt;
}
}
2) 完整的Excel类
using Microsoft.Office.Interop.Excel;
using _Excel = Microsoft.Office.Interop.Excel;
public class Excel
{
string path = "";
Application excel = new _Excel.Application();
Workbook wb;
Worksheet ws;
public Range xlRange;
static bool saveChanges = false;
static int excelRow = 0;
static List<string> columnHeaders = new List<string>();
public Excel(string path, int Sheet = 1)
{
this.path = path;
wb = excel.Workbooks.Open(path);
ws = wb.Worksheets[Sheet];
xlRange = ws.UsedRange;
excelRow = 0;
columnHeaders = new List<string>();
}
public void SaveFile(bool save = true)
{
saveChanges = save;
}
public void Close()
{
wb.Close(saveChanges);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
excel.Quit();
}
public void XMLToExcel(string xmlFilePath)
{
var dt = XMLFunctions.CreateDataTableFromXmlFile(xmlFilePath);
AddDataTableToExcel(dt);
}
public void AddDataTableToExcel(System.Data.DataTable table)
{
// Creating Header Column In Excel
for (int i = 0; i < table.Columns.Count; i++)
{
if (!columnHeaders.Contains(table.Columns[i].ColumnName))
{
ws.Cells[1, columnHeaders.Count() + 1] = table.Columns[i].ColumnName;
columnHeaders.Add(table.Columns[i].ColumnName);
}
}
// Get the rows
for (int k = 0; k < table.Columns.Count; k++)
{
var columnNumber = columnHeaders.FindIndex(x => x.Equals(table.Columns[k].ColumnName));
ws.Cells[excelRow + 2, columnNumber + 1] = table.Rows[0].ItemArray[k].ToString();
}
excelRow++;
SaveFile(true);
}
}
3) 调用它
var excel = new Excel(excelFilename);
foreach (var filePath in files)
{
excel.XMLToExcel(filePath);
}
excel.Close();
对于具有追加递增列名称的数组元素(例如column_2)
从Xml文件重新创建数据表
public static System.Data.DataTable CreateDataTableFromXmlFile(string xmlFilePath)
{
System.Data.DataTable Dt = new System.Data.DataTable();
string input = File.ReadAllText(xmlFilePath);
var xmlTagsAndValues = GetXMlTagsAndValues(input);
var columnList = new List<string>();
foreach (var xml in xmlTagsAndValues)
{
if (!columnList.Contains(xml.Item1))
{
columnList.Add(xml.Item1);
Dt.Columns.Add(xml.Item1, typeof(string));
}
else
{
var columnName = xml.Item1;
do
{
columnName = columnName.Increment();
} while (columnList.Contains(columnName));
columnList.Add(columnName);
Dt.Columns.Add(columnName, typeof(string));
}
}
DataRow dtrow = Dt.NewRow();
var columnList2 = new Dictionary<string, string>();
foreach (var xml in xmlTagsAndValues)
{
if (!columnList2.Keys.Contains(xml.Item1))
{
dtrow[xml.Item1] = xml.Item2;
columnList2.Add(xml.Item1, xml.Item2);
}
else
{
var columnName = xml.Item1;
do
{
columnName = columnName.Increment();
} while (columnList2.Keys.Contains(columnName));
dtrow[columnName] = xml.Item2;
columnList2[columnName] = xml.Item2;
}
}
Dt.Rows.Add(dtrow);
return Dt;
}
字符串扩展
public static class StringExtensions
{
public static string Increment(this string str)
{
if (!str.Contains("_"))
{
str += "_2";
return str;
}
var number = int.Parse(str.Substring(str.LastIndexOf('_') + 1));
var stringBefore = StringFunctions.GetUntilOrEmpty(str, "_");
return $"{stringBefore}_{++number}";
}
}
简单来说,您可以使用文件 › 打开 来打开它。然后,当您选择一个XML文件时,会提示您选择以下导入XML数据的方法之一:
你不能直接在Excel中打开它吗?我以为Excel能识别XML后缀呢?