csharp: Export DataTable to Excel using OpenXml 2.5 in asp.net

 /// <summary>
    /// 
    /// </summary>
    public partial class WebForm1 : System.Web.UI.Page
    {


        DataTable getData()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id", typeof(int));
            dt.Columns.Add("name", typeof(string));
            dt.Rows.Add(1, "geovindu");
            dt.Rows.Add(2, "geov");
            dt.Rows.Add(3, "塗斯博");
            dt.Rows.Add(4, "趙雅芝");
            dt.Rows.Add(5, " なわち日本語");
            dt.Rows.Add(6, "처리한다");
            dt.Rows.Add(7, "涂聚文");
            dt.Rows.Add(8, "塗聚文");
            return dt;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }

        }
        /// <summary>
        /// 
        /// </summary>
        private void BindGrid()
        {
            this.GridView1.DataSource = getData();
            GridView1.DataBind();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, EventArgs e)
        {
            
            string rootPath = HttpContext.Current.Server.MapPath("~").ToString();
            string localCopy = "塗聚文" + DateTime.Now.ToString("yyyyMMddHHmmssfff")+ ".xlsx"; //
            string file = new ExcelHelper().ExportToExcel(getData(), "geovindu",localCopy);
            File.Copy(file, rootPath + localCopy);
            Response.Redirect(HttpUtility.UrlEncode(localCopy,System.Text.Encoding.UTF8));
        }
    }

//https://www.microsoft.com/en-us/download/details.aspx?id=5124  Open XML SDK 2.0 for Microsoft Office
   //https://www.microsoft.com/en-us/download/details.aspx?id=30425 Open XML SDK 2.5 for Microsoft Office
   //https://github.com/OfficeDev/Open-Xml-Sdk
   //http://www.codeproject.com/Tips/366446/Export-GridView-Data-to-Excel-using-OpenXml
 
   //引用： WindowsBase.DLL  C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\WindowsBase.dll
 
   /// <summary>
   ///
   /// </summary>
   internal class ExcelHelper
   {
 
       /// <summary>
       ///
       /// </summary>
       internal class ColumnCaption
       {
           private static string[] Alphabets = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
           private static ColumnCaption instance = null;
           private List<string> cellHeaders = null;
           public static ColumnCaption Instance
           {
               get
               {
                   if (instance == null)
                       return new ColumnCaption();
                   else return ColumnCaption.Instance;
               }
           }
           /// <summary>
           ///
           /// </summary>
           public ColumnCaption()
           {
               this.InitCollection();
           }
           /// <summary>
           ///
           /// </summary>
           private void InitCollection()
           {
               cellHeaders = new List<string>();
 
               foreach (string sItem in Alphabets)
                   cellHeaders.Add(sItem);
 
               foreach (string item in Alphabets)
                   foreach (string sItem in Alphabets)
                       cellHeaders.Add(item + sItem);
           }
 
           /// <summary>
           /// Returns the column caption for the given row & column index.
           /// </summary>
           /// <param name="rowIndex">Index of the row.</param>
           /// <param name="columnIndex">Index of the column.</param>
           /// <returns></returns>
           internal string Get(int rowIndex, int columnIndex)
           {
               return this.cellHeaders.ElementAt(columnIndex) + (rowIndex + 1).ToString();
           }
       }
 
       /// <summary>
       /// 导出
       /// </summary>
       /// <param name="DataTable">DataTable</param>
       /// <param name="sheetname">工作表名</param>
       /// <param name="filename">文件名</param>
       /// <returns></returns>
       internal string ExportToExcel(DataTable table, string sheetname,string filename)
       {
           string excelfile = Path.GetTempPath() + filename;
           using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(excelfile, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
           {
               CreateExcelParts(excelDoc, table,sheetname);
           }
           return excelfile;
       }
       /// <summary>
       ///
       /// </summary>
       /// <param name="spreadsheetDoc"></param>
       /// <param name="data"></param>
       /// <param name="sheetname"></param>
       private void CreateExcelParts(SpreadsheetDocument spreadsheetDoc, DataTable data,string sheetname)
       {
           WorkbookPart workbookPart = spreadsheetDoc.AddWorkbookPart();
           CreateWorkbookPart(workbookPart, sheetname);
 
           int workBookPartCount = 1;
 
           WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rId" + (workBookPartCount++).ToString());
           CreateWorkbookStylesPart(workbookStylesPart);
 
           WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>("rId" + (101).ToString());
           CreateWorksheetPart(workbookPart.WorksheetParts.ElementAt(0), data);
 
           SharedStringTablePart sharedStringTablePart = workbookPart.AddNewPart<SharedStringTablePart>("rId" + (workBookPartCount++).ToString());
           CreateSharedStringTablePart(sharedStringTablePart, data);
 
           workbookPart.Workbook.Save();
       }
 
       /// <summary>
       /// Creates the shared string table part.
       /// </summary>
       /// <param name="sharedStringTablePart">The shared string table part.</param>
       /// <param name="sheetData">The sheet data.</param>
       private void CreateSharedStringTablePart(SharedStringTablePart sharedStringTablePart, DataTable sheetData)
       {
           UInt32Value stringCount = Convert.ToUInt32(sheetData.Rows.Count) + Convert.ToUInt32(sheetData.Columns.Count);
 
           SharedStringTable sharedStringTable = new SharedStringTable()
           {
               Count = stringCount,
               UniqueCount = stringCount
           };
 
           for (int columnIndex = 0; columnIndex < sheetData.Columns.Count; columnIndex++)
           {
               SharedStringItem sharedStringItem = new SharedStringItem();
               Text text = new Text();
               text.Text = sheetData.Columns[columnIndex].ColumnName;
               sharedStringItem.Append(text);
               sharedStringTable.Append(sharedStringItem);
           }
 
           for (int rowIndex = 0; rowIndex < sheetData.Rows.Count; rowIndex++)
           {
               SharedStringItem sharedStringItem = new SharedStringItem();
               Text text = new Text();
               text.Text = sheetData.Rows[rowIndex][0].ToString();
               sharedStringItem.Append(text);
               sharedStringTable.Append(sharedStringItem);
           }
 
           sharedStringTablePart.SharedStringTable = sharedStringTable;
       }
 
       /// <summary>
       /// Creates the worksheet part.
       /// </summary>
       /// <param name="worksheetPart">The worksheet part.</param>
       /// <param name="data">The data.</param>
       private void CreateWorksheetPart(WorksheetPart worksheetPart, DataTable data)
       {
           Worksheet worksheet = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
           worksheet.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
           worksheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
           worksheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
 
 
           SheetViews sheetViews = new SheetViews();
           SheetView sheetView = new SheetView() { WorkbookViewId = (UInt32Value)0U };
           Selection selection = new Selection() { ActiveCell = "A1" };
           sheetView.Append(selection);
           sheetViews.Append(sheetView);
 
           PageMargins pageMargins = new PageMargins()
           {
               Left = 0.7D,
               Right = 0.7D,
               Top = 0.75D,
               Bottom = 0.75D,
               Header = 0.3D,
               Footer = 0.3D
           };
 
           SheetFormatProperties sheetFormatPr = new SheetFormatProperties()
           {
               DefaultRowHeight = 15D,
               DyDescent = 0.25D
           };
 
           SheetData sheetData = new SheetData();
 
           UInt32Value rowIndex = 1U;
 
           Row row1 = new Row()
           {
               RowIndex = rowIndex++,
               Spans = new ListValue<StringValue>() { InnerText = "1:3" },
               DyDescent = 0.25D
           };
 
           for (int columnIndex = 0; columnIndex < data.Columns.Count; columnIndex++)
           {
               Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), columnIndex), DataType = CellValues.String };
               CellValue cellValue = new CellValue();
               cellValue.Text = data.Columns[columnIndex].ColumnName.ToString().FormatCode();
               cell.Append(cellValue);
 
               row1.Append(cell);
           }
           sheetData.Append(row1);
 
           for (int rIndex = 0; rIndex < data.Rows.Count; rIndex++)
           {
               Row row = new Row()
               {
                   RowIndex = rowIndex++,
                   Spans = new ListValue<StringValue>() { InnerText = "1:3" },
                   DyDescent = 0.25D
               };
 
               for (int cIndex = 0; cIndex < data.Columns.Count; cIndex++)
               {
                   if (cIndex == 0)
                   {
                       Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.String };
                       CellValue cellValue = new CellValue();
                       cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                       cell.Append(cellValue);
 
                       row.Append(cell);
                   }
                   else
                   {
                       Cell cell = new Cell() { CellReference = ExcelHelper.ColumnCaption.Instance.Get((Convert.ToInt32((UInt32)rowIndex) - 2), cIndex), DataType = CellValues.String };
                       CellValue cellValue = new CellValue();
                       cellValue.Text = data.Rows[rIndex][cIndex].ToString();
                       cell.Append(cellValue);
 
                       row.Append(cell);
                   }
               }
               sheetData.Append(row);
           }
 
           worksheet.Append(sheetViews);
           worksheet.Append(sheetFormatPr);
           worksheet.Append(sheetData);
           worksheet.Append(pageMargins);
           worksheetPart.Worksheet = worksheet;
       }
 
       /// <summary>
       /// Creates the workbook styles part.
       /// </summary>
       /// <param name="workbookStylesPart">The workbook styles part.</param>
       private void CreateWorkbookStylesPart(WorkbookStylesPart workbookStylesPart)
       {
           Stylesheet stylesheet = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
           stylesheet.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
           stylesheet.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
 
           StylesheetExtensionList stylesheetExtensionList = new StylesheetExtensionList();
           StylesheetExtension stylesheetExtension = new StylesheetExtension() { Uri = "{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}" };
           stylesheetExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main");
           DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles slicerStyles = new DocumentFormat.OpenXml.Office2010.Excel.SlicerStyles() { DefaultSlicerStyle = "SlicerStyleLight1" };
           stylesheetExtension.Append(slicerStyles);
           stylesheetExtensionList.Append(stylesheetExtension);
 
           stylesheet.Append(stylesheetExtensionList);
 
           workbookStylesPart.Stylesheet = stylesheet;
       }
 
       /// <summary>
       /// Creates the workbook part.
       /// </summary>
       /// <param name="workbookPart">The workbook part.</param>
       private void CreateWorkbookPart(WorkbookPart workbookPart,string sheetName)
       {
           Workbook workbook = new Workbook();
           Sheets sheets = new Sheets();
 
           Sheet sheet = new Sheet()
           {
               Name = sheetName,  //工作表名
               SheetId = Convert.ToUInt32(101),
               Id = "rId" + (101).ToString()
           };
           sheets.Append(sheet);
 
           CalculationProperties calculationProperties = new CalculationProperties()
           {
               CalculationId = (UInt32Value)123456U  // some default Int32Value
           };
 
           workbook.Append(sheets);
           workbook.Append(calculationProperties);
 
           workbookPart.Workbook = workbook;
       }
 
   }
   /// <summary>
   ///
   /// </summary>
   public static class Extensions
   {
       public static string FormatCode(this string sourceString)
       {
           if (sourceString.Contains("<"))
               sourceString = sourceString.Replace("<", "<");
 
           if (sourceString.Contains(">"))
               sourceString = sourceString.Replace(">", ">");
 
           return sourceString;
       }
   }