基础类库积累--ExeclHelper类

前言：

相信大家都玩过NPOI这个第三方组件，我就分享一下我平时使用的工具类，如果有不好的地方，请赐教！

NPOI是什么？

NPOI是一个开源的C#读写Excel、WORD等微软OLE2组件文档的项目。

NPOI怎么安装？

NuGet:

控制台：

命令：

Install-Package NPOI

输入命令之后，回车即安装

NPOI怎么使用？

安装NPOI之后，程序中就已经把NPOI服务集成到我们程序了，我们现在来建立一个帮助类，编写读取Execl和导出Execl。我这里的读取Execl，把每一个Sheet页当做一个DataTable，多个DataTable组成一个DataSet，然后将DataSet返回。

NPOI读取Execl

        /// <summary>

        /// Excel导入成DataTble

        /// </summary>

        /// <param name="file">导入路径(包含文件名与扩展名)</param>

        /// <returns></returns>

        public static DataSet ExcelToTable(string file, ref List<string> list_sheetName)

        {

            DataSet ds = new DataSet();

            IWorkbook workbook;

            string fileExt = Path.GetExtension(file).ToLower();

            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))

            {

                if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

                if (workbook == null) { return null; }

                for (int k = 0; k < workbook.NumberOfSheets; k++)

                {

                    DataTable dt = new DataTable();

                    ISheet sheet = workbook.GetSheetAt(k);

                    list_sheetName.Add(sheet.SheetName);

                    //表头

                    IRow header = sheet.GetRow(sheet.FirstRowNum);

                    //过滤空的Sheet

                    if (header!=null)

                    {

                    List<int> columns = new List<int>();

                    for (int i = 0; i < header.LastCellNum; i++)

                    {

                        object obj = GetValueType(header.GetCell(i));

                        if (obj == null || obj.ToString() == string.Empty)

                        {

                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));

                        }

                        else

                            dt.Columns.Add(new DataColumn(obj.ToString()));

                        columns.Add(i);

                    }

                    dt.Columns.Add(new DataColumn("SheetName"));

                    //数据

                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)

                    {

                        DataRow dr = dt.NewRow();

                        bool hasValue = false;

                        foreach (int j in columns)

                        {

                            if (sheet.GetRow(i) != null)

                            {

                                dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));

                                if (dr[j] != null && dr[j].ToString() != string.Empty)

                                {

                                    hasValue = true;

                                }

                            }

                        }

                        if (hasValue)

                        {

                            dr[columns.Count] = sheet.SheetName;

                            dt.Rows.Add(dr);

                        }

                    }

                    ds.Tables.Add(dt);

                    }

                }

            }

            return ds;

        }

        /// <summary>

        /// 获取单元格类型

        /// </summary>

        /// <param name="cell">目标单元格</param>

        /// <returns></returns>

        private static object GetValueType(ICell cell)

        {

            if (cell == null)

                return null;

            switch (cell.CellType)

            {

                case CellType.Blank:

                    return null;

                case CellType.Boolean:

                    return cell.BooleanCellValue;

                case CellType.Numeric:

                    return cell.NumericCellValue;

                case CellType.String:

                    return cell.StringCellValue;

                case CellType.Error:

                    return cell.ErrorCellValue;

                case CellType.Formula:

                default:

                    return "=" + cell.CellFormula;

            }

        }

思考？

我这里读取之后是一个DataSet集合，但是这种数据集虽然在结构上很清晰，一个DataTable对应一个Sheet,但是处理器数据其他麻烦（比如，我想查询表中Name为"张三"的用户信息，肯定是不好查询的），还是就是如果在每个Sheet数据格式相同的情况下，肯定会有想把它们整合在一起的想法，那该如何整合在一起？

思路：

要是能转换为List数组就好，我们就能使用Linq和Lambda进行数据的快速处理。如何把DataSet转换为List,我们可以观察execl中的数据，然后对应在项目中建立一个Model类，用英文做字段，用DisplayName标识对应的中文（为什么要这样，后面会讲），建立一个List,现在只要把DataSet中的DataTable取出来，然后利用反射的方式，比较DataTable中的列名和Model中对应的DisplayName，如何一样，，则存储到List,这里存在一个DataTable转换为List。

Execl数据：

Mode类：

只要这样一一对应起来，后期委会也好维护，比如新增了一个列，在Model中加一个字段即可，方便扩展，如果是多个Sheet，每个Sheet略有不同，就可以使用c#面向对象的思想，提取它们共同的字段，其他做继承，这里就不多说了。接下来讲讲如何做DataSet转换为List,其实DataSet中就是好多DataTable组成，如果能实现DataTable转换到List，其他就迎刃而解！

DataTable转换List

 public static class DataTableToList

    {

        /// <summary>

        /// DataTable转成List

        /// </summary>

        /// <typeparam name="T"></typeparam>

        /// <param name="dt"></param>

        /// <returns></returns>

        public static List<T> ToDataList<T>(this DataTable dt)

        {

            var list = new List<T>();

            var plist = new List<PropertyInfo>(typeof(T).GetProperties());

            foreach (DataRow item in dt.Rows)

            {

                T s = Activator.CreateInstance<T>();

                for (int i = 0; i < dt.Columns.Count; i++)

                {

                    PropertyInfo info = plist.Find(p => p.GetCustomAttribute<System.ComponentModel.DisplayNameAttribute>().DisplayName == dt.Columns[i].ColumnName);

                    if (info != null)

                    {

                        try

                        {

                            if (!Convert.IsDBNull(item[i]))

                            {

                                object v = null;

                                if (info.PropertyType.ToString().Contains("System.Nullable"))

                                {

                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));

                                }

                                else

                                {

                                    v = Convert.ChangeType(item[i], info.PropertyType);

                                }

                                info.SetValue(s, v, null);

                            }

                        }

                        catch (Exception ex)

                        {

                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);

                        }

                    }

                }

                list.Add(s);

            }

            return list;

        }

        public static List<T> ToDataSetList<T>(this DataSet ds)

        {

            var list = new List<T>();

            for (int i = 0; i < ds.Tables.Count; i++)

            {

                list = list.Concat(ToDataList<T>(ds.Tables[i])).ToList();

            }

            return list;

        }

    }

这里是使用泛型+反射的技术，对DataTable转换为List进行封装，只要你的格式一致（Execl和Model），就可以实现转换。

导出Execl

可以导入Execl，然后转换为List之后，我们可以为所欲为了，但是修改数据以后，我们可以想保存信息到Execl。

思考？

如何导出List到一个新的Execl？

思路：

List这个思路很简单，第一步创建一个IWorkbook（Execl对象），第二部创建Sheet，起个名字，然后把List数据遍历到Sheet中，最后写入到文件中。



        /// <summary>

        ///  List<T>导出Execl

        /// </summary>

        /// <typeparam name="T">模型类</typeparam>

        /// <param name="file">保存文件的路径</param>

        /// <param name="list">需要保存的数据</param>

        public static void ListToExecl<T>(string file, List<T> list)

        {

            IWorkbook workbook;

            string fileExt = Path.GetExtension(file).ToLower();

            if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }

            if (workbook == null) { return; }

            //中文显示的列名

            string DisplayName = string.Empty;

            ISheet sheet = workbook.CreateSheet();

            //表头

            IRow header = sheet.CreateRow(0);

            Type t = typeof(T);

            PropertyInfo[] properties = t.GetProperties();

            int index = 0;

            foreach (PropertyInfo field in properties)

            {

                DynamicGetProperty(list[0], field.Name, ref DisplayName).ToString();

                header.CreateCell(index).SetCellValue(DisplayName);

                index += 1;

            }

            for (int i = 0; i < list.Count; i++)

            {

                index = 0;

                header = sheet.CreateRow(1+i);

                foreach (PropertyInfo field in properties)

                {

                    string name = DynamicGetProperty(list[i], field.Name, ref DisplayName).ToString();

                    header.CreateCell(index).SetCellValue(name);

                    index += 1;

                }

            }

            using (FileStream fs=new FileStream(file,FileMode.Create,FileAccess.ReadWrite))

            {

                workbook.Write(fs);

            }

        }

        /// <summary>

	/// 动态获取对象的属性

	/// </summary>

	/// <param name="obj">传入的对象</param>

	/// <param name="propName">属性名</param>

	/// <returns></returns>

	public static object DynamicGetProperty(object obj, string propName,ref string DisplayName)

         {

            // TODO: 检查属性名合法性

            var propNames = propName.Split('.');

            var val = obj;

            foreach (var prop in propNames)

            {

                var propInfo = val.GetType().GetProperty(prop);

                DisplayName = propInfo.GetCustomAttribute<DisplayNameAttribute>().DisplayName;

                val = propInfo.GetValue(val);

            }

            return val;

        }

总结：

我在这只是抛砖引玉，其实NPOI还有一些其他东西，大家可以自行研究，比如：DataTable导出Exelc,C# NPOI计算Execl里面的公式等等。

肯定有人会有疑问，我为什么要把Execl先转换为DataSet，在去转换为List,为什么不在一开始就去转换为List?
答：第一我们不知道Execl数据的有多少Sheet，如果针对每一个都去写一个规则，繁琐且麻烦。你按照我的这种方式，不管你有多少Sheet，只要我知道你的格式【列名】，我就都可以转换为List,虽然多了一层转换，但是我逻辑清晰，代码复用率高，针对不同的Sheet编写对应的模型就可以，并不需要我每次去编写特定的格式。
有人还是有疑问，你这导出怎么就List直接转换到一个Sheet中，如果我想分开，之前怎么读取，之后就怎么保存，我该如何做？
答：其实这个也挺简单，我没做扩展，你在使用我代码的时候，一定会发现，List中多了一列值【SheetName】，所以你在保存的时候，读SheetName进行分类，然后遍历保存即可。

原文地址：https://www.cnblogs.com/2828sea/p/13493710.html

巴特西

基础类库积累--ExeclHelper类

前言：

NPOI是什么？

NPOI怎么安装？

NuGet:

控制台：

NPOI怎么使用？

NPOI读取Execl

思考？

思路：

DataTable转换List

导出Execl

思考？

思路：

总结：

最新文章

热门文章