ASP.NET将XML导出成EXCEL

首先需要引用

using System.Xml;
using Excel = Microsoft.Office.Interop.Excel;

 

   //定义excel中的起始行和列
    int row = 2;
    int cells = 1;
   
    protected void Button1_Click(object sender, EventArgs e)
    {
        string filepath = HttpContext.Current.Server.MapPath("ss.xls");
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbooks w = xlApp.Workbooks;
        Excel.Workbook workbook = w.Add(Excel.XlWBATemplate.xlWBATWorksheet);
        Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];

        //写入数值 
        //定义xml文件
        XmlDocument document = new XmlDocument();
        //读取xml文件的路径并加载
        string map = Server.MapPath("product.xml");
        document.Load(map);
        //定义xml的根节点作为一个节点
        XmlNode nodes = document.DocumentElement;

        //利用深度遍历往excel里面写xml的值
        DOMDepthFirst(worksheet, nodes);

        #region
        //数中的所有子节点
        //XmlNodeList list = document.ChildNodes;
        //获取document的根节点
        //XmlElement ele = document.DocumentElement;

        //int row = 0;
        //int cells = 0;
        //worksheet.Cells[1 + row, 1 + cells] = document.DocumentElement.Attributes[0].Value;
        //XmlNode nodes = document.DocumentElement;
        //while (nodes.ChildNodes != null || nodes.NextSibling != null)
        //{
        //    if (nodes.ChildNodes != null)
        //    {
        //        worksheet.Cells[2 + row, 2 + cells] = nodes.Attributes[0].Value;
        //        nodes = nodes.ch;
        //    }
        //    else if (nodes.NextSibling != null)
        //    {
        //        worksheet.Cells[2 + row, 2 + cells] = nodes.Attributes[0].Value;
        //        nodes = nodes.NextSibling;
        //    }
        //    else
        //    {
        //        row--;
        //    }
        //    row++;
        //}
        #endregion

        worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。

        workbook.Saved = true;

        workbook.SaveCopyAs(filepath);

        xlApp.Quit();

        GC.Collect();//强行销毁

        HttpContext.Current.Response.Buffer = true;

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.ContentType = "application/ms-excel";

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filepath));

        HttpContext.Current.Response.WriteFile(filepath);

        HttpContext.Current.Response.Flush();

        HttpContext.Current.Response.End();

    }

    private void DOMDepthFirst(Excel.Worksheet worksheet, XmlNode nodes)
    {
        worksheet.Cells[row,cells] = nodes.Attributes[0].Value;
        worksheet.Cells[1, cells] = "第" + cells + "级";
        row++;
        if (nodes.HasChildNodes)
        {
            XmlNode node = nodes.FirstChild;  
            while (node != null)
            {
                cells++;
                DOMDepthFirst(worksheet, node);
                cells--;
                node = node.NextSibling;
            }   
        }
    }