C#通过类模板通用导入Excel通用方法

1.新建TestClass类示例，导入类TestClass类的模板数据，其他类可通用该导入方法

    public class TestClass
    {
        public string code {  get; set; }
        public string name { get; set; }
        public string description { get; set; }
    }

 

2.新建Excel导入模板，模板第2行为类中的字段名称，一一对应

 

3.封装工具类ExcelHelper提供静态方法GetEntityFromExcel<T>导入时调用

　　获取单元格的值

　　

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell, ISheet sheet)
        {
            string ret = "";

            if (cell != null)
            {
                switch (cell.CellType)
                {
                    case CellType.Blank:
                        break;
                    case CellType.Numeric:
                        if (DateUtil.IsCellDateFormatted(cell))
                        {
                            ret = cell.DateCellValue.GetValueOrDefault().ToString("yyyyMMdd");
                        }
                        else
                        {
                            ret = cell.NumericCellValue.ToString();
                        }
                        break;
                    case CellType.String:
                        ret = cell.StringCellValue.Trim();
                        break;
                    case CellType.Formula:
                        try
                        {
                            IFormulaEvaluator evaluator = WorkbookFactory.CreateFormulaEvaluator(sheet.Workbook);
                            var formulaValue = evaluator.EvaluateInCell(cell);
                            if (formulaValue.CellType == CellType.Numeric)
                                ret = formulaValue.NumericCellValue.ToString().Trim();
                            else if (formulaValue.CellType == CellType.String)
                                ret = formulaValue.StringCellValue.Trim();
                        }
                        catch (Exception ex)
                        {
                            ret = cell.ToString().Trim();
                        }
                        break;
                    default:
                        ret = cell.ToString().Trim();
                        break;
                }
            }

            return ret;
        }

　　获取一行的值

　　

        /// <summary>
        /// 获取一行的值
        /// </summary>
        /// <param name="row"></param>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static string GetDetailFromRow<T>(IRow row, ISheet sheet, ref T detail)
        {
            string ret = string.Empty;

            try
            {
                //第2行设置对应字段
                var tempRow = sheet.GetRow(1);

                for (int i = 0; i < tempRow.LastCellNum; i++)
                {
                    var name = tempRow.GetCell(i).ToString();

                    var property = detail.GetType().GetProperty(name);
                    var properType = property.PropertyType;
                    var underlyingType = Nullable.GetUnderlyingType(properType) ?? properType;

                    var value = GetCellValue(row.GetCell(i), sheet).Trim();

                    if (!string.IsNullOrWhiteSpace(value))
                    {
                        if (underlyingType.Name == "DateTime" || underlyingType.Name == "Date")
                            property.SetValue(detail, Convert.ChangeType(value, underlyingType), null);
                        else
                            property.SetValue(detail, Convert.ChangeType(value, underlyingType), null);
                    }
                }
            }
            catch (Exception ex)
            {
                ret = ex.Message;
            }

            return ret;
        }

　　从模板读取EXCEL

　　

    /// <summary>
    /// 从模板读取EXCEL
    /// </summary>
    /// <param name="stream"></param>
    /// <param name="ret"></param>
    /// <returns></returns>
    public static string GetEntityFromExcel<T>(string filePath,ref List<T> list)
    {
        string ret = "";
       
        // 处理文件，例如读取或显示
        FileStream stream = new FileStream(filePath, FileMode.Open);
        IWorkbook workbook = WorkbookFactory.Create(stream);

        try
        {
            ISheet sheet = workbook.GetSheetAt(0);

            if (sheet != null)
            {
                IRow row = null;

                for (int i = 2; i <= sheet.LastRowNum; i++)
                {
                    row = sheet.GetRow(i);

                    if (row == null) continue;

                    T detail = Activator.CreateInstance<T>();
                    ret = GetDetailFromRow(row, sheet,ref detail);

                    list.Add(detail);
                }
            }
            else
            {
                ret = "Excel无内容";
            }

        }
        catch (Exception ex)
        {
            ret = ex.Message;
        }

        return ret;
    }

 

4.调用静态类方法实现导入功能

            OpenFileDialog openFileDialog = new OpenFileDialog();
            //openFileDialog.InitialDirectory = "c:\\"; // 设置初始目录
            openFileDialog.Filter = "txt files (*.xls)|*.xlsx|All files (*.*)|*.*"; // 设置文件过滤器
            openFileDialog.FilterIndex = 2;
            openFileDialog.RestoreDirectory = true;

            if (openFileDialog.ShowDialog() == DialogResult.OK) // 显示对话框
            {
                // 获取选中的文件路径
                string filePath = openFileDialog.FileName;

                var list = new List<TestClass>();
                var msg = ExcelHelper.GetEntityFromExcel(filePath, ref list);

                if (!string.IsNullOrWhiteSpace(msg))
                {
                    MessageBox.Show("导入失败："+ msg);
                }
                else
                {
                    dataGridView1.DataSource = list;
                }
            }