C# 使用EPPlus导入导出EXCEL

1 通过nuget或直接下载相关DLL引用；

2、版本高于7要在app.config文件中配置授权项；

    <appSettings>
        <add key="EPPlus:ExcelPackage.LicenseContext" value="NonCommercial" />
    </appSettings>

3、附代码

为方便调用，直接在EpplusExcelHelper类中加入“导入时”扩展名判断，导出时“保存文件名“窗口的

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using epplus_xlsx;
using OfficeOpenXml.Style;
using System.Drawing;
using System.Windows.Forms;

namespace epplus_xlsx
{
    public static class EpplusExcelHelper
    {
        /// <summary>
        /// 从EXCEL 导入数据到DataTable ，只支持.xlsx 不支持.xls
        /// </summary>
        /// <param name="filePath">EXCEL文件路径</param>
        /// <returns></returns>
        public static DataTable ReadExcel(string filePath)
        {
            string sExt = System.IO.Path.GetExtension(filePath);
            sExt = sExt.ToUpper();

            if (sExt == ".XLSX")
            {
                using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
                {
                    var workbook = package.Workbook;
                    var worksheet = workbook.Worksheets.FirstOrDefault();
                    var startRow = worksheet.Dimension.Start.Row;
                    var endRow = worksheet.Dimension.End.Row;
                    var startColumn = worksheet.Dimension.Start.Column;
                    var endColumn = worksheet.Dimension.End.Column;
                    var table = new DataTable();
                    for (int i = startColumn; i <= endColumn; i++)
                    {
                        table.Columns.Add(worksheet.Cells[startRow, i].Value.ToString());
                    }

                    for (int row = startRow + 1; row <= endRow; row++)
                    {
                        var dataRow = table.NewRow();

                        for (int col = startColumn; col <= endColumn; col++)
                        {
                            dataRow[col - 1] = worksheet.Cells[row, col].Value;
                        }

                        table.Rows.Add(dataRow);
                    }

                    return table;
                }
            }
            else
            {
                throw new Exception("文件格式有误,只能使用.xlsx");
            }
        }
    

        /// <summary>
        /// 使用EPPlus导出Excel(xlsx)
        /// </summary>
        /// <param name="sourceTable">数据源</param>
        /// <param name="strFileName">xlsx文件名(不含后缀名)</param>

        public static void ExpExcel(DataTable dt)
        {
            string strFileName = "";
            SaveFileDialog s = new SaveFileDialog();
            s.Title = "保存Excel文件";
            s.Filter = "Excel文件*.xlsx|*.csv";
            s.FilterIndex = 1;
            if (s.ShowDialog() == DialogResult.OK)
                strFileName = s.FileName;
            else
                return;
            //所有的操作语句需要放置在下面的using中
            using (ExcelPackage pck = new ExcelPackage())
            {
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");//添加sheet
                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(dt, true);

                List<int> list = new List<int>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    DataColumn dc = dt.Columns[i];
                    if (dc.DataType.ToString().ToUpper().Contains("DATETIME"))
                    {
                        list.Add(i + 1);
                    }
                }

                //Format the row
                ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
                Color borderColor = Color.FromArgb(155, 155, 155);

                using (ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 1, dt.Columns.Count])
                {
                    rng.Style.Font.Name = "宋体";
                    rng.Style.Font.Size = 10;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid;                      //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(255, 255, 255));

                    rng.Style.Border.Top.Style = borderStyle;
                    rng.Style.Border.Top.Color.SetColor(borderColor);

                    rng.Style.Border.Bottom.Style = borderStyle;
                    rng.Style.Border.Bottom.Color.SetColor(borderColor);

                    rng.Style.Border.Right.Style = borderStyle;
                    rng.Style.Border.Right.Color.SetColor(borderColor);
                }
                using (ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count])
                {
                    rng.Style.Font.Bold = true;//设置单元格字体加粗
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(234, 241, 246));  //Set color to dark blue
                    rng.Style.Font.Color.SetColor(Color.FromArgb(51, 51, 51));
                }
                for (int i = 0; i < list.Count; i++)
                {
                    using (ExcelRange rng = ws.Cells[2, list[i], dt.Rows.Count + 1, list[i]])
                    {
                        rng.Style.Numberformat.Format = "yyyy-MM-dd";
                    }
                }
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    pck.SaveAs(fs);
                }
            }
        }

    }
}