C# 使用Epplus读写xlsx文件，从DataGridView导出Excel文件

使用Epplus 7.2.1版本

1、从文件路径读取数据（epplus的限制，只能是xlsx文件），新增对枚举数据的支持

        /// <summary>
        /// 从文件路径读取数据，只能是xlsx文件
        /// </summary>
        /// <typeparam name="T">转换的对象</typeparam>
        /// <param name="filePath">文件路径</param>
        /// <param name="list">输出对象集合</param>
        /// <param name="sheetNum">表编号</param>
        /// <param name="rowStart">从第几行开始读</param>
        /// <returns></returns>
        public static bool Read<T>(string filePath, out List<T> list, int sheetNum = 0,int rowStart = 2) where T : class, new()
        {
            list = new List<T>() { new T() };
            if (!File.Exists(filePath))
                return false;
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//无需授权
            PropertyInfo[] properties = typeof(T).GetProperties();
            if (properties.Length == 0)
                return false;
            FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
            using (ExcelPackage package = new ExcelPackage(fs))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[sheetNum];
                int rowCount = sheet.Dimension.End.Row;
                for (int i = rowStart; i < rowCount; i++)
                {
                    for (int j = 0; j < properties.Length; j++)
                    {
                        switch (properties[j].PropertyType.Name.ToString())
                        {
                            case "Boolean":
                                properties[j].SetValue(list[i - rowStart], Convert.ToBoolean(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "Int16":
                                properties[j].SetValue(list[i - rowStart], Convert.ToInt16(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "Int32":
                                properties[j].SetValue(list[i - rowStart], Convert.ToInt32(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "Int64":
                                properties[j].SetValue(list[i - rowStart], Convert.ToInt64(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "UInt16":
                                properties[j].SetValue(list[i - rowStart], Convert.ToUInt16(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "UInt32":
                                properties[j].SetValue(list[i - rowStart], Convert.ToUInt32(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "UInt64":
                                properties[j].SetValue(list[i - rowStart], Convert.ToUInt64(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "Float":
                                properties[j].SetValue(list[i - rowStart], Convert.ToSingle(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "Dounble":
                                properties[j].SetValue(list[i - rowStart], Convert.ToDouble(sheet.Cells[i, j + 1].Value.ToString()));
                                break;
                            case "String":
                                properties[j].SetValue(list[i - rowStart], sheet.Cells[i, j + 1].Value.ToString());
                                break;
                            default:
                                if (properties[j].PropertyType.BaseType.Name == "Enum")
                                {
                                    Type enumType = (Type)properties[j].PropertyType;
                                    properties[j].SetValue(list[i - rowStart], Enum.Parse(enumType, sheet.Cells[i, j + 1].Value.ToString()));
                                }
                                else
                                    properties[j].SetValue(list[i - rowStart], 0);
                                break;
                        }

                    }
                    if (i < rowCount-1)
                        list.Add(new T());
                }
            }
            return true;
        }

2、写入数据到xlsx文件中

        public static bool WriteToExcel<T>(string filePath, List<T> list) where T : class
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//无需授权
            if (!File.Exists(filePath))
            {
                File.Create(filePath);
            }

            using (ExcelPackage pck = new ExcelPackage(filePath))
            {
                ExcelWorksheet sheet;
                if (pck.Workbook.Worksheets.Count > 0)
                    sheet = pck.Workbook.Worksheets[0];
                else
                    sheet = pck.Workbook.Worksheets.Add("Sheet1");
                int rowCount = 0, colCount = 0;
                if (sheet.Dimension != null)
                {
                    rowCount = sheet.Dimension.Rows;//记录表格中的行数
                    colCount = sheet.Dimension.Columns;//记录表格中的列数
                }

                if (list.Count == 0)
                    return false;
                PropertyInfo[] properties = typeof(T).GetProperties();
                if (properties.Length == 0)
                    return false;

                for (int i = rowCount; i < rowCount + list.Count; i++)
                {
                    for (int j = 0; j < properties.Length; j++)
                    {
                        sheet.Cells[i + 1, j + 1].Value = properties[j].GetValue(list[i - 
                        rowCount], null);//epplus所有行列数都是以1开始
                    }
                }
                rowCount = sheet.Dimension.Rows;//记录表格中的行数
                colCount = sheet.Dimension.Columns;//记录表格中的列数
                //格式化行
                using (ExcelRange rng = sheet.Cells[1, 1, rowCount + 1, colCount])
                {
                    setRowStyle(rng);
                }
                pck.Save();
            }
            return true;
        }

3、从DataGridView导出Excel(xlsx)

        /// <summary>
        /// 使用EPPlus从DataGridView导出Excel(xlsx)
        /// </summary>
        /// <param name="dgv">数据源</param>
        /// <param name="filePath">xlsx文件名(不含后缀名)</param>
        /// <param name="sheetName">表名</param>
        /// <param name="isNeedHeader">是否需要标题</param>
        public static void ExportFromDgv(DataGridView dgv, string filePath, string sheetName = "Sheet1", bool isNeedHeader = true)
        {
            try
            {
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//无需授权
                if (File.Exists(filePath))
                {
                    if (MessageBox.Show($"路径{filePath}文件已存在，是否替换？", "提示", MessageBoxButtons.OKCancel) == DialogResult.Cancel)
                        return;
                    File.Delete(filePath);
                }
                using (ExcelPackage pck = new ExcelPackage(filePath))
                {
                    //创建数据表
                    ExcelWorksheet sheet = pck.Workbook.Worksheets.Add(sheetName);

                    int rowCount =dgv.AllowUserToAddRows? dgv.RowCount-1: dgv.RowCount;//记录表格中的行数
                    int colCount = dgv.ColumnCount;//记录表格中的列数
                    for (int i = 0; i < rowCount; i++)
                    {
                        for (int j = 0; j < colCount; j++)
                        {
                            if (dgv.Columns[j].Visible && dgv.Rows[0].Cells[j].Value != null)
                            {
                                if (i == 0 && isNeedHeader)//写入标题
                                {
                                    sheet.Cells[i + 1, j + 1].Value = dgv.Columns[j].HeaderText.ToString();
                                }
                                else
                                {
                                    sheet.Cells[i + 1, j + 1].Value = 
                                    dgv.Rows[i-1].Cells[j].Value.ToString();//epplus所有行列数都是以1开始
                                }

                            }
                        }
                    }


                    //格式化行
                    using (ExcelRange rng = sheet.Cells[1, 1, rowCount + 1, colCount])
                    {
                        setRowStyle(rng);
                    }
                    if (isNeedHeader)
                    {
                        //格式化标题行
                        using (ExcelRange rng = sheet.Cells[1, 1, 1, colCount])
                        {
                            setHeaderStyle(rng);
                        }
                    }
                    pck.Save();
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

4、其他的样式设置

        //设置行样式
        private static void setRowStyle(ExcelRange rng)
        {
            ExcelBorderStyle borderStyle = ExcelBorderStyle.Thin;
            Color borderColor = Color.FromArgb(155, 155, 155);
            rng.Style.Font.Name = "宋体";
            rng.Style.Font.Size = 10;
            rng.Style.Fill.PatternType = ExcelFillStyle.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);
        }
      //设置列样式
            private static void setHeaderStyle(ExcelRange rng)
        {
            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));
        }