Excel催化剂开源第14波-VSTO开发之单元格区域转DataTable

Excel催化剂

发布于 2021-08-19 15:00:50

1.6K0

发布于 2021-08-19 15:00:50

文章被收录于专栏：Excel催化剂

在Excel开发过程中，大部分时候是和Range单元格区域打交道，在VBA开发中，大家都知道的一点是，不能动不动就去遍历所有单元格，那性能是非常糟糕的，很多时候，是需要把整个单元格区域装入数组中再作处理的。

在VSTO开发中，难不成还要用VBA这套老掉牙的东西来做吗？VBA的二维数组在.Net的世界中，真的一无是处，太多比它好用的东西存在，其中笔者最喜欢用的是DataTable这样的结构化的数据结构。

从单元格到DataTable，其实也就几句代码的事情，当数据进入到DataTable后，可以使用许多数据库的技术进行增删改查，特别是查询方面，遍历数据行记录变得何等轻松，因其是结构化的数据，访问某列某行的数据，不用像二维数组那样很不直观地只能用下标去访问。

同时在.Net世界中，有一猛药，谁用谁喜爱的，用LINQ的方法来访问数据，什么排序、筛选、去重，分组等等，会用SQL语句的人，都知道这叫怎样地一个方便。LINQ比SQL还要好用、易用好几倍。

言归正传，如何实现Range对象转为DataTable对象

原理同样地先将Range对象转为二维数据，再将二维数组转为DataTable 具体代码如下：

  public static DataTable GetTableFromDataTableRange(Excel.Range dataTableRange,bool isAllStringType=false)
        {

            Excel.Range titleRange = dataTableRange.Rows[1];

            if (titleRange.Cells.Cast<Excel.Range>().Any(s => s.Value2 == null))
            {
                throw new Exception("标题行有空单元格，请检查引用区域首行标题区域是否满足非空！");
            }
            else if (titleRange.Cells.Cast<Excel.Range>().GroupBy(s => s.Value2).Count() != titleRange.Cells.Count)
            {
                throw new Exception("标题行有重复列标题，请修正后再运行！");
            }

            DataTable dataTable = GetTableStructureFromTitleRange(titleRange, isAllStringType);

            try
            {
                Excel.Range dataRangeExcludeTitleRange = Common.ExcelApp.Intersect(dataTableRange.Offset[1, 0], dataTableRange);
                if (dataRangeExcludeTitleRange != null)
                {
                    int[] visibleRowIndexs = RangeUtility.GetVisibleRowIndexs(dataTableRange);
                    return InsertDataToDataTable(dataRangeExcludeTitleRange, dataTable, visibleRowIndexs);
                }
                else
                {
                    throw new Exception("转换的区域仅有标题区域没有数据区域！");
                }

            }
            catch (Exception)
            {
                throw;
            }
        }

先构造一个空数据的DataTable结构，DataTable的数据列类型，有时保留Excel的数据类型方便些，有时全部变为String类型方便些。

        public static DataTable GetTableStructureFromTitleRange(Excel.Range titleRow,bool isAllString=false)
        {
            DataTable dt = new DataTable();
            foreach (Excel.Range cell in titleRow.Cells)
            {
                if (isAllString)
                {
                    dt.Columns.Add(cell.Value2, typeof(string));
                }
                else
                {
                    dt.Columns.Add(cell.Value2, typeof(object));
                }

            }
            return dt;

        }

数据注入到DataTable，也就是两次循环嵌套的事情。

        private static DataTable InsertDataToDataTable(Excel.Range dataRangeExcludeTitleRange, DataTable dataTable, int[] visibleRowIndexs)
        {
            object[,] arr = dataRangeExcludeTitleRange.Value;

            for (int i = 0; i < arr.GetLength(0); i++)
            {
                if (visibleRowIndexs.Contains(i + 1))//数据序号从1开始，数组从0开始，所以需要+1。仅抽取非隐藏行
                {
                    DataRow dr = dataTable.NewRow();
                    for (int j = 0; j < arr.GetLength(1); j++)
                    {
                        dr[j] = arr[i + 1, j + 1];
                    }
                    dataTable.Rows.Add(dr);
                }

            }

            return dataTable;
        }