在Excel开发过程中,大部分时候是和Range单元格区域打交道,在VBA开发中,大家都知道的一点是,不能动不动就去遍历所有单元格,那性能是非常糟糕的,很多时候,是需要把整个单元格区域装入数组中再作处理的。
在VSTO开发中,难不成还要用VBA这套老掉牙的东西来做吗?VBA的二维数组在.Net的世界中,真的一无是处,太多比它好用的东西存在,其中笔者最喜欢用的是DataTable这样的结构化的数据结构。
从单元格到DataTable,其实也就几句代码的事情,当数据进入到DataTable后,可以使用许多数据库的技术进行增删改查,特别是查询方面,遍历数据行记录变得何等轻松,因其是结构化的数据,访问某列某行的数据,不用像二维数组那样很不直观地只能用下标去访问。
同时在.Net世界中,有一猛药,谁用谁喜爱的,用LINQ的方法来访问数据,什么排序、筛选、去重,分组等等,会用SQL语句的人,都知道这叫怎样地一个方便。LINQ比SQL还要好用、易用好几倍。
原理同样地先将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;
}
经过简单几步实现了Excel单元格区域加载至DataTable内,然后才是真正的VSTO开发,在.Net的世界中,有了Excel的源数据,再经过许多轻松方便的轮子功能,快速地实现数据的转换,在Excel催化剂中大量使用(因笔者是数据库技术的资深玩家,可能较一般专业程序开发者玩得更溜在这一块)。
最后,数据在程序内,最终需要返回给用户界面,即Excel单元格区域,又是怎样实现最为合理呢,请继续关注下篇讲解。