C# 导出datatable数据到excel

第一步：下载两个需要的NUGET包

           1.org.in2bits.MyXls；2.NPOI

第二步：关键类OutExcel。

using System;
using System.Linq;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using NPOI.HSSF.UserModel;
using org.in2bits.MyXls;
using System.IO;
using System.Reflection;

namespace OutExcel
{
    public enum ExportType
    {
        [Description("Microsoft Excel|*.xls")]
        Excel,
        [Description("(*.txt)|*.txt|" + "(*.*)|*.*")]
        Txt
    }
    public class OutExcelXls
    {
        string sExportName = string.Empty;//导出文件名称
        string sSaveFullPath = string.Empty;//导出全路径

        string sFloderPath = string.Empty;//导出文件夹路径


        const int nExcel2003RowMaxLimit = 65536;
        const int nExcel2003ColMaxLimit = 256;
        ExportType currentExportType;
        public OutExcelXls(string _sExportName)
        {
            this.sExportName = _sExportName;
        }
        public OutExcelXls()
        {
        }
        /// <summary>
        /// 初始化SaveFileDialog
        /// </summary>
        /// <param name="_dialogType">SaveFileDialogType【枚举】</param>
        /// <returns>{确认导出：True;否则:false}</returns>
        public bool InitShowDialog(ExportType _dialogType)
        {
            SaveFileDialog dlgSave = new SaveFileDialog();
            dlgSave.FileName = sExportName;
            dlgSave.Filter = EnumOperate.GetDescriptionFromEnumValue(_dialogType);
            if (dlgSave.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                currentExportType = _dialogType;
                sSaveFullPath = dlgSave.FileName;//C:UsersAdministratorDesktop发现LTULFI设备_20130520142107.xls
                sFloderPath = sSaveFullPath.Substring(0, sSaveFullPath.LastIndexOf(@"") + 1);
                return true;
            }
            return false;
        }
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="_dtExport">需要导出的Datatable</param>
        /// <returns>{导出成功：True;否则:false}</returns>
        public bool DoExportWork(DataTable _dtExport)
        {
            bool _dExportStatus = false;
            switch (currentExportType)
            {
                case ExportType.Excel:
                    _dExportStatus = true;
                    ExportToExecel(_dtExport, sExportName);
                    break;
            }
            return _dExportStatus;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="_dtExport">需要导出的Datatable</param>
        /// <returns>{导出成功：True;否则:false}</returns>
        public bool DoExportWork(DataTable _dtExport, string titleName, string mx)
        {

            bool _dExportStatus = false;
            switch (currentExportType)
            {
                case ExportType.Excel:
                    _dExportStatus = true;
                    if (string.IsNullOrEmpty(sExportName))
                    {
                        sExportName = titleName + DateTime.Now.ToShortDateString() + DateTime.Now.ToFileTime() + ".xls";
                    }
                    ExportToExecel(_dtExport, sExportName, titleName, mx);
                    break;
            }
            return _dExportStatus;
        }


        /// <summary>
        /// 导出为Excel，适合要求不高且数据量不大的数据导出
        /// </summary>
        /// <param name="_dtExport">需要导出的Datatable</param>
        /// <param name="_sSheetName">Excel中SheetName名称</param>
        private void ExportToExecel(DataTable _dtExport, string _sSheetName)
        {
            try
            {
                int _nTotalRowCnt = _dtExport.Columns.Count;
                if (_nTotalRowCnt > nExcel2003RowMaxLimit)
                    throw new Exception(string.Format("导出异常，不能导出！原因：数据行已经超过:{0}.", nExcel2003RowMaxLimit));
                if (_dtExport.Columns.Count > nExcel2003ColMaxLimit)
                    throw new Exception(string.Format("导出异常，不能导出！原因：数据列已经超过:{0}.", nExcel2003ColMaxLimit));

                XlsDocument _xls = new XlsDocument();
                _xls.FileName = sExportName;


                int _nCelIndex = 0;
                int _nRowIndex = 0;

                int page = Convert.ToInt32(Math.Ceiling(_dtExport.Rows.Count / 60000.0));

                for (int i = 0; i < page; i++)
                {
                    //Worksheet _sheet = _xls.Workbook.Worksheets.AddNamed(_sSheetName + (i + 1));
                    Worksheet _sheet = _xls.Workbook.Worksheets.AddNamed("Sheet" + (i + 1));
                    ColumnInfo _colinfo = new ColumnInfo(_xls, _sheet);
                    _colinfo.ColumnIndexStart = 0;
                    _colinfo.ColumnIndexEnd = (ushort)_nTotalRowCnt;
                    _sheet.AddColumnInfo(_colinfo);
                    XF _cellXF = _xls.NewXF();

                    /*
                    #region 创建单元格样式
                    //第一种单元格样式
                    XF xf = xls.NewXF();
                    xf.HorizontalAlignment = HorizontalAlignments.Centered;
                    xf.VerticalAlignment = VerticalAlignments.Centered;
                    xf.Pattern = 1;////设定单元格填充风格。如果设定为0，则是纯色填充
                    xf.PatternColor = Colors.White;//设定填充线条的颜色 
                    xf.UseBorder = true;
                    //设置列的上下左右的样式
                    xf.TopLineStyle = 1;
                    xf.TopLineColor = Colors.Black;
                    xf.BottomLineStyle = 1;
                    xf.BottomLineColor = Colors.Black;
                    xf.LeftLineStyle = 1;
                    xf.LeftLineColor = Colors.Black;
                    xf.RightLineStyle = 1;
                    xf.RightLineColor = Colors.Black;
                    xf.Font.Bold = true;
                    xf.Font.FontName = "仿宋";
                    xf.Font.Height = 12 * 20;
                    //自动换行
                    xf.TextWrapRight = true; 
                    #endregion
                    */
                     

                    _cellXF.VerticalAlignment = VerticalAlignments.Centered;
                    _cellXF.HorizontalAlignment = HorizontalAlignments.Centered;

                    _nCelIndex = 0;
                    _nRowIndex = 1;
                    Cells _cells = _sheet.Cells;

                    foreach (DataColumn col in _dtExport.Columns)
                    {
                        _nCelIndex++;
                        _cells.AddValueCellXF(_nRowIndex, _nCelIndex, col.ColumnName, _cellXF);
                    }
                    for (int j = i * 60000; j < _dtExport.Rows.Count; j++)
                    {
                        DataRow row = _dtExport.Rows[j];
                        _nRowIndex++;
                        _nCelIndex = 0;
                        if (_nRowIndex > 60000)
                        {
                            break;
                        }
                        foreach (DataColumn col in _dtExport.Columns)
                        {
                            _nCelIndex++;
                            Cell cell = _cells.AddValueCellXF(_nRowIndex, _nCelIndex, row[col.ColumnName].ToString(), _cellXF);
                        }
                    }
                }

                _xls.Save(sFloderPath);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("导出到Excel失败，原因:{0}", ex.Message.Trim()));
            }
        }


        /// <summary>
        /// 导出为Excel，适合要求不高且数据量不大的数据导出 
        /// </summary>
        /// <param name="_dtExport">需要导出的Datatable</param>
        /// <param name="_sSheetName">Excel中SheetName名称</param>
        private void ExportToExecel(DataTable _dtExport, string _sSheetName, string titleName, string mx)
        {
            try
            {
                int _nTotalRowCnt = _dtExport.Columns.Count;
                if (_nTotalRowCnt > nExcel2003RowMaxLimit)
                    throw new Exception(string.Format("导出异常，不能导出！原因：数据行已经超过:{0}.", nExcel2003RowMaxLimit));
                if (_dtExport.Columns.Count > nExcel2003ColMaxLimit)
                    throw new Exception(string.Format("导出异常，不能导出！原因：数据列已经超过:{0}.", nExcel2003ColMaxLimit));

                XlsDocument _xls = new XlsDocument();
                _xls.FileName = sExportName;
                Worksheet _sheet = _xls.Workbook.Worksheets.AddNamed(_sSheetName);
                ColumnInfo _colinfo = new ColumnInfo(_xls, _sheet);
                _colinfo.ColumnIndexStart = 0;
                _colinfo.ColumnIndexEnd = (ushort)_nTotalRowCnt;
                _sheet.AddColumnInfo(_colinfo);
                XF _cellXF = _xls.NewXF();
                _cellXF.VerticalAlignment = VerticalAlignments.Centered;
                _cellXF.HorizontalAlignment = HorizontalAlignments.Centered;
                // _cellXF.Format = StandardFormats.Date_2;//转换为数字类型

                int _nCelIndex = 0, _nRowIndex = 3;
                Cells _cells = _sheet.Cells;

                _cells.AddValueCellXF(1, 1, titleName, _cellXF);

                MergeArea titleArea = new MergeArea(1, 1, 1, _dtExport.Columns.Count); // 一个合并单元格实例(合并第1行、第1列 到 第1行、第4列)    
                _sheet.AddMergeArea(titleArea);

                _cells.AddValueCellXF(2, 1, mx, _cellXF);
                MergeArea titleArea2 = new MergeArea(2, 2, 1, _dtExport.Columns.Count);
                _sheet.AddMergeArea(titleArea2);

                foreach (DataColumn col in _dtExport.Columns)
                {
                    _nCelIndex++;
                    _cells.AddValueCellXF(3, _nCelIndex, col.ColumnName, _cellXF);
                }
                foreach (DataRow row in _dtExport.Rows)
                {
                    _nRowIndex++;
                    _nCelIndex = 0;
                    foreach (DataColumn col in _dtExport.Columns)
                    {
                        _nCelIndex++;
                        Cell cell = _cells.AddValueCellXF(_nRowIndex, _nCelIndex, row[col.ColumnName].ToString(), _cellXF);
                    }
                }
                _xls.Save(sFloderPath);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("导出到Excel失败，原因:{0}", ex.Message.Trim()));
            }
        }

        /// <summary>
        /// 打开文件
        /// </summary>
        public void OpenFile()
        {
            Cursor _currentCursor = Cursor.Current;
            Cursor.Current = Cursors.WaitCursor;
            Cursor.Current = _currentCursor;
            if (MessageBox.Show("是否打开该个文件吗?", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
            {
                try
                {
                    System.Diagnostics.Process _process = new System.Diagnostics.Process();
                    _process.StartInfo.FileName = sSaveFullPath;
                    _process.StartInfo.Verb = "Open";
                    _process.StartInfo.WindowStyle = System.Diagnostics.ProcessWindowStyle.Normal;
                    _process.Start();
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("未能打开该文件，原因:{0}", ex.Message.Trim()));
                }
            }
        }
        public DataTable ExcelToDataSet(string FilePath, DataTable table)
        {
            //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
            HSSFWorkbook workbook = new HSSFWorkbook();// new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            try
            {
                workbook = new HSSFWorkbook(File.Open(FilePath, FileMode.Open));
            }
            catch
            {
                workbook = new HSSFWorkbook(File.OpenRead(FilePath));
            }
            HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);
            //获取excel的第一个sheet

            //获取Excel的最大行数
            int rowsCount = sheet.PhysicalNumberOfRows;
            //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。
            //为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。
            int colsCount = sheet.GetRow(0).PhysicalNumberOfCells;

            for (int h = 0; h < colsCount; h++)
            {
                table.Columns.Add(sheet.GetRow(0).GetCell(h).ToString(), Type.GetType("System.String"));
            }

            for (int x = 1; x < rowsCount; x++)
            {
                DataRow dr = table.NewRow();
                for (int y = 0; y < colsCount; y++)
                {
                    try
                    {
                        dr[y] = sheet.GetRow(x).GetCell(y).ToString();
                    }
                    catch
                    {
                        dr[y] = "";
                    }
                }
                if (!string.IsNullOrEmpty(dr[0].ToString().Trim()))
                {
                    table.Rows.Add(dr);
                }
            }

            sheet = null;
            workbook = null;
            return table;
        }
    }

    public static class EnumOperate
    {
        public static string GetDescriptionFromEnumValue(Enum value)
        {
            DescriptionAttribute attribute = value.GetType()
                .GetField(value.ToString())
                .GetCustomAttributes(typeof(DescriptionAttribute), false)
                .SingleOrDefault() as DescriptionAttribute;
            return attribute == null ? value.ToString() : attribute.Description;
        }

        public static T GetEnumValueFromDescription<T>(string description)
        {
            var type = typeof(T);
            if (!type.IsEnum)
                throw new ArgumentException();
            FieldInfo[] fields = type.GetFields();
            var field = fields
                            .SelectMany(f => f.GetCustomAttributes(
                                typeof(DescriptionAttribute), false), (
                                    f, a) => new { Field = f, Att = a })
                            .Where(a => ((DescriptionAttribute)a.Att)
                                .Description == description).SingleOrDefault();
            return field == null ? default(T) : (T)field.Field.GetRawConstantValue();
        }

    }
}

第三步：调用outExcelNew方法传入datatable和导出文件名名称

  public static void outExcelNew(DataTable dt, string name)
        {
            try
            {
                string strFileName = name + DateTime.Now.ToString("yyyyMMddHHmmss");
                OutExcelXls doExport = new OutExcelXls(strFileName);
                ExportType _exportType = ExportType.Excel;
                bool isPath = doExport.InitShowDialog(_exportType);

                if (isPath)
                {
                    object[] datas = new object[3];
                    DataTable dts = dt;
                    if (dts != null && dts.Rows.Count > 0)
                    {
                        DataTable _dtExportData = dts;
                        for (int i = 0; i < _dtExportData.Columns.Count; i++)
                        {
                            //_dtExportData.Columns[i].ColumnName = dgvData.Columns[i].HeaderText;
                        }

                        if (_dtExportData != null && _dtExportData.Rows.Count > 0)
                        {
                            doExport.DoExportWork(_dtExportData);

                        }

                        MessageBox.Show("【温馨提示】下载完毕！");
                    }
                    else
                    {
                        MessageBox.Show("【温馨提示】当前无数据！");
                    }
                }
            }
            catch { }
        }