EPPlus

 public class EPPlus
    {
        public static string ExcelContentType
        {
            get
            {
                return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }
        }

        #region 示例
        //public FileContentResult Export()
        //{
        //    var queryJson = Request.Params["queryJson"] + "";
        //    DataTable dt = new EternalService().GetMenuList(JObject.Parse(queryJson));
        //    goto ddd;
        //    ddd:
        //    byte[] filecontent = ExcelExportHelper.ExportExcel(dt, "", false);
        //    return File(filecontent, ExcelExportHelper.ExcelContentType, "MyMenu.xlsx");
        //}
        #endregion
        /// <summary>
        /// 匯出Excel
        /// </summary>
        /// <param name="dataTable">資料來源</param>
        /// <param name="heading">工作簿Worksheet</param>
        /// <param name="showSrNo">//是否顯示行編號</param>
        /// <param name="columnsToTake">要匯出的列</param>
        /// <returns></returns>
        public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
        {
            //建立Excel
            byte[] result = null;
            using (ExcelPackage package = new ExcelPackage())
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));
                //workSheet.Workbook.Properties.Title = "inventory";//設定excel的標題
                workSheet.Workbook.Properties.Author = "涼生";//作者
                //workSheet.Workbook.Properties.Comments = "this is a test";//備註
                workSheet.Workbook.Properties.Company = "xin";//公司

                int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3;  //開始的行
                //是否顯示行編號
                if (showSrNo)
                {
                    DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
                    dataColumn.SetOrdinal(0);
                    int index = 1;
                    foreach (DataRow item in dataTable.Rows)
                    {
                        item[0] = index;
                        index++;
                    }
                }

                //在Excel檔案中新增內容
                workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
                // 小單元格的內容自動調整寬度
                int columnIndex = 1;
                foreach (DataColumn item in dataTable.Columns)
                {
                    ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row + 1, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                    string maxValue = columnCells.Max(cell => (cell.Value ?? "").ToString());
                    int maxLength = maxValue.Count();
                    if (maxLength < 150) workSheet.Column(columnIndex).AutoFit();//自動調整寬度
                    else workSheet.Column(columnIndex).Style.WrapText = true; //自動換行
                    if (Regex.IsMatch(maxValue, @"^[+-]?\d*[.]?\d*$"))//驗證是否為數字字串
                    {
                        workSheet.Column(columnIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                    }
                    if (item.DataType.Equals(Type.GetType("System.DateTime")))//驗證是否是時間
                    {
                        workSheet.Column(columnIndex).AutoFit();
                        workSheet.Column(columnIndex).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
                    }
                    columnIndex++;
                }
                // 格式標題-粗體，黃色的黑色
                using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
                {
                    r.Style.Font.Color.SetColor(System.Drawing.Color.White);
                    r.Style.Font.Bold = true;
                    r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                    r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
                }

                // 格式化單元格-新增邊框 
                using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
                {
                    r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                    r.Style.Border.Right.Style = ExcelBorderStyle.Thin;

                    r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                    r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                }

                // 刪除忽略的列
                for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
                {
                    if (i == 0 && showSrNo)
                    {
                        continue;
                    }
                    if (columnsToTake.Contains(dataTable.Columns[i].ColumnName))
                    {
                        workSheet.DeleteColumn(i + 1);
                    }
                }

                if (!String.IsNullOrEmpty(heading))
                {
                    workSheet.Cells["A1"].Value = heading;
                    workSheet.Cells["A1"].Style.Font.Size = 20;

                    workSheet.InsertColumn(1, 1);
                    workSheet.InsertRow(1, 1);
                    workSheet.Column(1).Width = 5;
                }

                result = package.GetAsByteArray();

            }
            return result;
        }


        #region 示例

        // public ActionResult UploadExcle(HttpPostedFileBase file)
        //{
        //    if (file == null) return Content("沒有檔案！", "text/plain");
        //    var sourcePath = Path.Combine(Server.MapPath("~/tempFiles/"), Path.GetFileName(file.FileName));
        //    //建立臨時傳輸資料夾
        //    if (!Directory.Exists(Path.GetDirectoryName(sourcePath)))
        //        Directory.CreateDirectory(sourcePath);
        //    file.SaveAs(sourcePath);
        //    DataSet dt = ExcelToTable.ReadExcelToDataSet(sourcePath);
        //    //處理資料
        //    //。。。。。。
        //    //刪除檔案
        //    System.IO.File.Delete(sourcePath);
        //    return Content("");
        //} 
        #endregion
        /// <summary>
        /// 匯入
        /// </summary>
        /// <param name="filePath"></param>
        /// <returns></returns>
        public static DataSet ReadExcelToDataSet(string filePath)
        {
            DataSet ds = new DataSet("ds");
            DataRow dr;
            object objCellValue;
            string cellValue;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
            using (ExcelPackage package = new ExcelPackage())
            {
                package.Load(fs);
                foreach (var sheet in package.Workbook.Worksheets)
                {
                    if (sheet.Dimension == null) continue;
                    var columnCount = sheet.Dimension.End.Column;//獲取worksheet的列數
                    var rowCount = sheet.Dimension.End.Row; //獲取worksheet的行數
                    if (rowCount > 0)
                    {
                        DataTable dt = new DataTable(sheet.Name);
                        for (int j = 0; j < columnCount; j++)//設定DataTable列名  
                        {
                            objCellValue = sheet.Cells[1, j + 1].Value;
                            cellValue = objCellValue == null ? "" : objCellValue.ToString();
                            dt.Columns.Add(cellValue, typeof(string));
                        }
                        for (int i = 2; i <= rowCount; i++)
                        {
                            dr = dt.NewRow();
                            for (int j = 1; j <= columnCount; j++)
                            {
                                objCellValue = sheet.Cells[i, j].Value;
                                cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                dr[j - 1] = cellValue;
                            }
                            dt.Rows.Add(dr);
                        }
                        ds.Tables.Add(dt);
                    }
                }
            }
            return ds;

        }
    }