插件NPOI下载:https://www.nuget.org/packages/NPOI/

接口代码

/// <summary>
    /// DataTable导出Excel
    /// </summary>
    public interface IDataTableToExcel
    {
        #region Buffer

        /// <summary>
        /// 获取转换后的缓冲区数据
        /// </summary>
        /// <returns></returns>
        byte[] GetBuffer();
        /// <summary>
        /// 获取转换后的缓冲区数据
        /// </summary>
        /// <param name="title">标题</param>
        /// <returns></returns>
        byte[] GetBuffer(string title);
        /// <summary>
        /// 获取转换后的缓冲区数据
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="sheetName">sheet名字</param>
        /// <returns></returns>
        byte[] GetBuffer(string title, string sheetName);

        #region MemoryStream

        /// <summary>
        /// 获取MemoryStream
        /// </summary>
        /// <returns></returns>
        MemoryStream GetMemoryStream();
        /// <summary>
        /// 获取MemoryStream
        /// </summary>
        /// <param name="title">标题</param>
        /// <returns></returns>
        MemoryStream GetMemoryStream(string title);
        /// <summary>
        /// 获取MemoryStream
        /// </summary>
        /// <param name="title">标题</param>
        /// <param name="sheetName">sheet名字</param>
        /// <returns></returns>
        MemoryStream GetMemoryStream(string title, string sheetName);

        #endregion

        #endregion

        #region SaveFile

        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        void SaveFile(string fileName);
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="title">标题</param>
        void SaveFile(string fileName, string title);
        /// <summary>
        /// 保存Excel文件
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <param name="title">标题</param>
        /// <param name="sheetName">sheet名</param>
        void SaveFile(string fileName, string title, string sheetName);

        #endregion
    }

实现类

public class NPOIExcel : IDataTableToExcel
    {
        private readonly string DEFAULT_SHEETNAME = "sheet1";
        private DataTable _dt = null;
        private NPOIExcel() { }
        public NPOIExcel(DataTable dt)
        {
            this._dt = dt;
        }

        public byte[] GetBuffer()
        {
            return GetBuffer("", DEFAULT_SHEETNAME);
        }

        public byte[] GetBuffer(string title)
        {
            return GetBuffer(title, DEFAULT_SHEETNAME);
        }

        public byte[] GetBuffer(string title, string sheetName)
        {
            byte[] bytes = null;
            try
            {
                using (MemoryStream stream = GetMemoryStream(title, sheetName))
                {
                    bytes = stream.ToArray();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return bytes;
        }

        public void SaveFile(string fileName)
        {
            SaveFile(fileName, "", DEFAULT_SHEETNAME);
        }

        public void SaveFile(string fileName, string title)
        {
            SaveFile(fileName, title, DEFAULT_SHEETNAME);
        }

        public void SaveFile(string fileName, string title, string sheetName)
        {
            try
            {
                using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    fs.Position = 0;
                    byte[] bytes = GetBuffer(title, sheetName);
                    fs.Write(bytes, 0, bytes.Length);
                    fs.Flush();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public MemoryStream GetMemoryStream()
        {
            return GetMemoryStream("", DEFAULT_SHEETNAME);
        }

        public MemoryStream GetMemoryStream(string title)
        {
            return GetMemoryStream(title, DEFAULT_SHEETNAME);
        }

        public MemoryStream GetMemoryStream(string title, string sheetName)
        {

            MemoryStream stream = null;
            try
            {
                IWorkbook workBook = new HSSFWorkbook();

                ISheet sheet = workBook.CreateSheet(sheetName);
                //处理表格标题
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue(title);
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, _dt.Columns.Count - 1));
                row.Height = 500;

                ICellStyle cellStyle = workBook.CreateCellStyle();
                IFont font = workBook.CreateFont();
                font.FontName = "微软雅黑";
                font.FontHeightInPoints = 17;
                cellStyle.SetFont(font);
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                cellStyle.Alignment = HorizontalAlignment.Center;
                row.Cells[0].CellStyle = cellStyle;

                //处理表格列头
                row = sheet.CreateRow(1);
                for (int i = 0; i < _dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(_dt.Columns[i].ColumnName);
                    row.Height = 350;
                    sheet.AutoSizeColumn(i);
                }

                //处理数据内容
                for (int i = 0; i < _dt.Rows.Count; i++)
                {
                    row = sheet.CreateRow(2 + i);
                    row.Height = 250;
                    for (int j = 0; j < _dt.Columns.Count; j++)
                    {
                        row.CreateCell(j).SetCellValue(_dt.Rows[i][j].ToString());
                        sheet.SetColumnWidth(j, 256 * 15);
                    }
                }
                stream = new MemoryStream();
                workBook.Write(stream);
                workBook.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return stream;
        }

    }

使用方法


  1. 普通文件保存


                 DataTable dt = new DataTable();
                dt.Columns.Add("第1列列名字");
                dt.Columns.Add("第2列列名字");
                dt.Columns.Add("第3列列名字");
    
                dt.Rows.Add("11", "12", "13");
                dt.Rows.Add("21", "22", "23");
                dt.Rows.Add("31", "32", "33");
    
                IDataTableToExcel export = new NPOIExcel(dt);
                export.SaveFile(@"C:\Users\Kevin\Desktop\excel.xlsx", "这是一个标题");
    

2.文件下载方式


        public FileContentResult BytesToExcelFile(DataTable dt, string title)
        {
            IDataTableToExcel excel = new NPOIExcel(dt);

            byte[] bytes = excel.GetBuffer(title);

            return File(bytes, "application/x-xls", title + ".xls");
        }


        public ActionResult Dowload(int export = 0)
        {

            DataTable dt =  null;

            if (export == 1)
            {
                string filename = $"徐德意博客{year}年{month}月数据报告";
                return BytesToExcelFile(dt, filename);
            }
            else
            {
                return View(dt);
            }
        }