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

 //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
   //https://openxmlsdkjs.codeplex.com/
    //引用： 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;
        }
    }

　　

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

　　

        /// <summary>
        /// windows 10 Microsoft Edge 测试无效。
        ///塗聚文註
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="Response"></param>
        /// <param name="filename"></param>
        public static void Convertexcel(DataTable dt, HttpResponse Response, string filename)
        {

            //win 10 通不过
            Response.Clear();
            Response.Buffer = true;
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            //Response.AppendHeader("content-disposition", "attachment; filename=myfile.xlsx");

            //Response.AddHeader "Content-Disposition", "Attachment;Filename=myfile.csv"

            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xlsx");

            //Response.ContentType = "application/ms-excel";  //application/vnd.ms-excel //2003
            //Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls");//xlsx
            Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
            Response.Charset = "utf-8";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);

            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";//2007
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            //stringWrite.Encoding
            System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
            //htmlWrite.Encoding
            System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
            dg.DataSource = dt;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
            string style = @"<!--mce:2-->";
            Response.Write(style);
            Response.Output.Write(stringWrite.ToString());
            //Response.Write(stringWrite.ToString());
            Response.Flush();
            Response.End();
            //HttpContext.Current.ApplicationInstance.CompleteRequest();

        }

　　http://closedxml.codeplex.com/

http://epplus.codeplex.com/

https://sourceforge.net/projects/closedxmlexcel/

https://github.com/vbjay/ClosedXML

http://*.com/questions/23102010/open-xml-reading-from-excel-file

https://openxmlexporttoexcel.codeplex.com/

https://excelpackage.codeplex.com/

https://github.com/OfficeDev/Open-Xml-Sdk

https://openxmlpackaging.codeplex.com/

http://powertools.codeplex.com/

https://github.com/OfficeDev/Open-Xml-PowerTools

http://html2openxml.codeplex.com/

https://github.com/gSerP1983/OpenXml.Excel.Data

https://npoi.codeplex.com/

https://github.com/tonyqus/npoi