如何以编程方式将xml转换为excel文件

I have a xml document holding a small data for my project where I want to convert my xml to an excel file (microsoft office excel 2003 and over)

我有一个xml文档,其中包含我的项目的小数据,我想将我的xml转换为excel文件(microsoft office excel 2003及以上版本)

How can I do this programmatically?

我该如何以编程方式执行此操作?

7 个解决方案

#1

It can be achieved by using Microsoft.Office.Interop.Excel as shown below:

它可以通过使用Microsoft.Office.Interop.Excel来实现,如下所示:

First of all declare these necessary references.

首先声明这些必要的参考。

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;

Than create excel app as shown:

比如图所示创建excel app:

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

After that use below code in a loop to ensure all items in xml file are copied

之后在循环中使用下面的代码来确保复制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;

Now we have an Excel object named as Copy_To_Excel that contains all items in Xml.. We can save and close the Excel object with the name we want..

现在我们有一个名为Copy_To_Excel的Excel对象,它包含Xml中的所有项目。我们可以使用我们想要的名称保存和关闭Excel对象。

Copy_To_Excel.Save("thePath\theFileName");
Copy_To_Excel.Close();

#2

If you have control over the XML generated, just generate an XML Spreadsheet file (the XML file standard for Excel 2002 and 2003).

如果您可以控制生成的XML,只需生成XML Spreadsheet文件(Excel 2002和2003的XML文件标准)。

These open natively in Excel, without having to change the extension. (To open by default in Excel, the file extension XML should be set to open with "XML Editor", which is an Office app that routes the XML file to Excel, Word, PowerPoint, InfoPath, or your external XML editor as needed. This is the default mapping when Office is installed, but it may be out of whack for some users, particularly devs who edit XML files in a text editor.)

这些在Excel中本机打开,无需更改扩展名。 (要在Excel中默认打开,应将文件扩展名XML设置为使用“XML Editor”打开,“XML Editor”是一个Office应用程序,可根据需要将XML文件路由到Excel,Word,PowerPoint,InfoPath或外部XML编辑器。这是安装Office时的默认映射,但对于某些用户来说可能不合适,特别是在文本编辑器中编辑XML文件的开发人员。)

Or, use the NPOI library to generate a native (97/2000 BIFF/XLS) Excel file rather than XML.

或者,使用NPOI库生成本机(97/2000 BIFF / XLS)Excel文件而不是XML。

#3

you can even read the XML file as string and use regular expressions to read the content between the tags and create a CSV file or use xpath expressions to read the XML file data and export to CSV file.

您甚至可以将XML文件作为字符串读取,并使用正则表达式读取标记之间的内容并创建CSV文件或使用xpath表达式读取XML文件数据并导出到CSV文件。

#4

1.Fill the xml file into dataset,
2.convert dataset to excel by below method in asp.net

1.将xml文件填入数据集,然后通过asp.net中的以下方法将数据集转换为excel

These are very simple methods.

这些都是非常简单的方法。

  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();
    }

#5

I know of no easy way to do code-based conversion from xml-based spreadsheet to xls/xlsx. But you might look into Microsoft Open Xml SDK which lets you work with xlsx. You might build xlsx spreadsheet and just feed it with your data. On the level of open-xml SDK it's like building xml file.

我知道没有简单的方法可以从基于xml的电子表格到xls / xlsx进行基于代码的转换。但是您可以查看Microsoft Open Xml SDK,它可以让您使用xlsx。您可以构建xlsx电子表格,并将其与您的数据一起提供。在open-xml SDK的层面上,它就像构建xml文件一样。

#6

-3

How to open an XML file in Excel 2003.

如何在Excel 2003中打开XML文件。

In short, you can simply open it by using File › Open. Then, when you select an XML file, you are prompted to select one of the following methods to import the XML data:

简而言之,您只需使用“文件”>“打开”即可打开它。然后,当您选择XML文件时,系统会提示您选择以下方法之一来导入XML数据:

As an XML list

作为XML列表

As a read-only workbook

作为只读工作簿

Use the XML Source task pane

使用XML Source任务窗格

#7

-3

Can't you simply open it in Excel? I thought Excel recognized the suffix XML?

你不能简单地在Excel中打开它吗?我以为Excel识别后缀XML?

#1