在C#中使用.net3.0和Openxml在服务器端实现数据格式转换成OpenxmlExcel

日前公司要求实现将data table中的数据转换成Excel文件格式提供给用户下载，几经周折，试过很多中方法，终于找到一个相对比较好的解决方案，虽然不够漂亮，但是基本上实现了这个需求。
1 。安装。Net3.0 ，下载 Openxml.dll
这些都是可以从微软的网站上免费获得。

2。可以做使用Excel2007做一个模板，设置好style，这样可以很方便。如果自己生成新的Excel文件的话，也可以，不过要深入研究OpenXml的格式。
3。具体的转换代码（需要改动
4。在具体的使用中，使用stream的方式提供给用户下载。代码如下：
（1）调用代码
 DataTable dt = .........

            string parentFolderPath = Server.MapPath("Excel");
            if (dt == null) return;
            ORE.BusinessLogic.TransferToExcel transfer = new ORE.BusinessLogic.TransferToExcel();
            byte[] results = transfer.TransferToOpenXMLExcelByStream(parentFolderPath, dt, ViewState["Option"].ToString());
            if (results != null)
            {
                Response.ClearContent();
                Response.ContentType = "application/x-zip-compressed";  //this is very important
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.AddHeader("Content-Disposition", "attachment;filename=" + LabelTitle.Text.Replace(" ", "") + ".xlsx");
                Response.BinaryWrite(results);
                Response.Flush();
                Response.Close();
            }
            else
            {
                string errorMessage = "Transfer process terminated unexpectedly, please contact the ORE Helpdesk";
                Response.Write("<script language='javascript'>alert('" + errorMessage + "');</script>");
            }
(2) transferring code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using Microsoft.Office.DocumentFormat.OpenXml.Packaging;
using System.IO.Packaging;
using System.IO;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Text;

    /// <summary>
    /// Transfer the data to the OpenXML Excel format
    /// Added by zhailei 2007-12-24
    /// needs to add WindowsBase and OpenXml references
    /// </summary>
    struct OutputColumn
    {
        private string _colName;
        private string _outputColName;
        private int _outputColWidth;
        public string ColumnName
        {
            get
            {
                return _colName;
            }
        }
        public string OutputColumnName
        {
            get
            {
                return _outputColName;
            }
        }
        public int OutputColWidth
        {
            get
            {
                return _outputColWidth;
            }
        }
        public OutputColumn(string colName, string outputColName):this(colName,outputColName,10)
        {
           
        }
        public OutputColumn(string colName, string outputColName, int outputColWidth)
        {
            this._colName = colName;
            this._outputColName = outputColName;
            this._outputColWidth = outputColWidth;
        }
    }
    public class TransferToExcel
    {
        public byte[] TransferToOpenXMLExcelByStream(string parentFolderPath, DataTable data,string option)
        {
            //template file
            string filePath = parentFolderPath + @"/template.xlsx";
            FileInfo file = new FileInfo(filePath);
            if (!file.Exists)
            {
                return null;
            }
            //read the template file to a byte[] buffer
            byte[] srcFileBuffer;
            using (FileStream srcFileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                srcFileBuffer = new byte[srcFileStream.Length];
                srcFileStream.Read(srcFileBuffer, 0, (int)srcFileStream.Length);
            }
            byte[] resultFileBuffer;
            using (MemoryStream