vsto中,处理excel数据的基本套路(C#)

excelhomesnake

已经断断续续在本板块学习vsto差不多一个月,实际只是熟悉了pia库和openxml库而已,最近有写空余时间,因此这里也做个小总结一下,并分享一下学到的知识.

暂时计划的知识点如下:

1,如何把单元格区域值放到数组中处理,然后输出excel.

2,如何通过封装,达到通用处理二维数组的处理.(例如,各种排序,汇总等操作)

3,其他的一些读写技术.(openxml库的基本应用,xml的读取)



由于不希望其他人在研究程序效果的时候,需要安装程序,因此所有的实例程序,我都是用控制台来写,这样可以直接xcopy,不需要安装过程.
同时,因为没有使用vsto模板,因此实际上只是使用pia库而已,但只要在vsto中,找到正确的对象引用,代码可以完全移植到vsto中.

注意:在其他进程使用pia库去操作excel,与直接使用vsto操作,有一个很大的区别.前者执行一些长时间操作excel的代码的时候,一旦人为地操作一下excel(例如点一下单元格之类的),就很有可能让你的代码执行失败,除非先把屏幕刷新去掉.在vba中也有这样的情况.但在vsto中,可以让你的执行功能,到当前excel线程中排队执行(因为在vsto中可以轻易获取到excel的工作线程).这里的水挺深,之后有时间再研究如何在vsto之外实现.

(待续)

excelhomesnake

1,如何把单元格区域值放到数组中处理,然后输出excel.
附件为数据源和源码.
vba代码就不上了,相信很多人都写到想吐了.下面是C#的代码(只上核心的代码),写多,也是想吐的感觉

不知道如何贴上代码块.还是直接去看附件的源码咯
在解决方案的文件Program.cs里面,因为我是用vs2012,恐怕vs2010不一定可以打开,但cs文件可以在任何版本的vs中打开,如果没有vs也可以用记事本打开.

单元格赋值到数组,与vba大同小异.注意,变量类型要object[,]

1. //与vba一样,直接赋值给一个二维数组变量即可.得到的是行列维度都是1基的数组
   
2. object[,] array = wrk.get_Range("A1").CurrentRegion.Value;

复制代码

创建固定大小,维度最小下标从1开始的二维数组

1. //定义一个同样是二维1基的数组 ,来存放结果
   
2. //使用Array的静态方法CreateInstance来创建,参数1指定数组元素类型,参数2指定每个维度的可容纳元素个数,参数3,指定对应参数2每个维度的最小下标
   
3. object[,] resultArray = (object[,]) Array.CreateInstance(typeof(object), new int[] { array.GetUpperBound(0), array.GetUpperBound(1) }, new int[] { 1, 1 });

复制代码

遍历数组也一样,注意:
1,数组的元素都是object类型,如果要对元素进行处理,要先进行转换.
2,根据excel中不同的单元格值,object类型的元素是经过对应的类型转换(或装箱)而来.从vs调试里面可以看出来,所有的数值的单元格,都是double,公式的错误值都是int32(相当于一个错误类型码),字符串就是字符串.

1. for (int irow = 2; irow <= array.GetUpperBound(0); irow++)
   
2. {
   
3. 
   
4. //级次为4的行取出
   
5. int value = Convert.ToInt32(array[irow, 4]);//由于数组是object类型,因此最好是先做类型转换
   
6. 
   
7. if (value == 4)
   
8. {
   
9. inputRowIndex++;
   
10. 
    
11. for (int icol = 1; icol <= array.GetUpperBound(1); icol++)
    
12. {
    
13. resultArray[inputRowIndex, icol] = array[irow, icol];
    
14. }
    
15. 
    
16. }
    
17. 
    
18. }

复制代码

excelhomesnake

2,如何通过封装,达到通用处理二维数组的处理.(例如,各种排序,汇总等操作)

prod_2过程:已经把二维数组封装到一个类来管理,实现可枚举接口.让这个对象可以按照行来foreach遍历.代表进一步简化
prod_3过程:由于可用foreach遍历,也意味可以用linq查询,轻易实现各种过滤,排序,汇总等操作.

个人觉得,这个是vsto(.net)比vba更优胜的地方.如果使用.net平台,也只是换了界面,换了语言,来写着一样的code,是没有多大意义.毕竟在.net上可以写出更通用,更容易维护的东西,不好好利用,还不如继续用vba好了.

关键代码如下:

1,创建一个ArrayManager类,负责单元格区域和二维数组的互相转换,并保存这个二维数组.
2,创建一个ArrayRow结构体,代表数组数据中的一行.里面只保存行索引,当然要保存ArrayManager的引用.
3,让ArrayManager类实现IEnumerable<ArrayRow>接口.这个接口是.net上所有可枚举遍历的关键所在.这样,就可以让ArrayManager的对象,可以用foreach直接遍历,遍历的每个元素类型就是ArrayRow

接口方法的代码:注意
1,此代码在ArrayManager类中定义
2,里面的this,代表当前正在执行代码中的ArrayManager的一个实例引用(就像vba的me,和vb.net的Me)

1. /// <summary>
   
2. /// 让此对象可foreach遍历
   
3. /// </summary>
   
4. /// <returns></returns>
   
5. public IEnumerator<ArrayRow> GetEnumerator()
   
6. {
   
7. 
   
8. for (int i = 1; i <= this.RowsCount; i++)
   
9. {
   
10. yield return new ArrayRow
    
11. {
    
12. ArrayManager = this,
    
13. RowIndex = i
    
14. };
    
15. }
    
16. 
    
17. }
    
18. 
    
19. System.Collections.IEnumerator System.Collections.IEnumerable.GetEnumerator()
    
20. {
    
21. return GetEnumerator();
    
22. }

复制代码

这样就可以直接foreach一个ArrayManager类的实例

1. var am = new ArrayManager(wrk.get_Range("A1").CurrentRegion);
   
2. 
   
3. 
   
4. //现在可以foreach遍历了
   
5. foreach (ArrayRow row in am)
   
6. {
   
7. 
   
8. //分类名称列中,内容是以"普通"开头的行,获取下来
   
9. if (row.GetValueToString(2).StartsWith("普通"))
   
10. {
    
11. resultList.Add(row);
    
12. }
    
13. 
    
14. 
    
15. }

复制代码

因为实现了可枚举的接口,因此就可以使用linq来查询,下面的代码与你遍历是一样(当然,性能上有一些损失,因为后台实际是调用了几个静态扩展方法和保存了几个委托类型)
但linq查询很多都是延迟执行,意味你可以组合多个查询,把逻辑分块写出,但执行顺序与普通的镶嵌遍历一致.本身linq直接有许多有用的汇总,连接的关键字和方法(很像sql,是一种声明形式的表达)

1. //由于am可foreach遍历,意味可以用linq查询am
   
2. 
   
3. var query = from row in am.Skip(1)//跳过第一行
   
4. where row.GetValueToString(2).StartsWith("普通")
   
5. orderby row.GetValueToNumber(4) descending //第4列降序
   
6. select row;

复制代码

VBA万岁

excelhomesnake 发表于 2015-12-17 15:57
1,如何把单元格区域值放到数组中处理,然后输出excel.
附件为数据源和源码.
vba代码就不上了,相信很多人都 ...

代码块的调出方法，只需要单击附件按钮右边的“<>”符号即可，如下图：

excelhomesnake

VBA万岁 发表于 2015-12-18 09:27
代码块的调出方法，只需要单击附件按钮右边的“”符号即可，如下图：

噢噢，稍后试一下，谢谢

VBA万岁

excelhomesnake 发表于 2015-12-17 16:55
2,如何通过封装,达到通用处理二维数组的处理.(例如,各种排序,汇总等操作)

prod_2过程:已经把二维数组封 ...

好！利用二维数组对工作表进行筛选和排序。
有空测试一下。

另，以下两文来普及一下Path.Combine的用法：
1、Path.Combine 方法
2、用Path.Combine的详解

excelhomesnake

3,openxml库的基本使用
实际上我也只是接触了它几天而已,因此没有很深入的理解.如有错误,望见谅.
openxml库,就是微软公开的一个操作openxml格式文件的库,例如,2007或以上版本的excel,ppt等.因为这些文档都是通过压缩xml和其他一些媒体文件得到的文档.所以,就算不使用这个库,也可以自己通过解压缩文件流,然后读写xml来做到这一切.只是openxml库提供了层次结构对象,可以让你方便操作而已.

注意:
1,使用这个库的程序效果与使用pia库是完全不一样.因为前者是以流的方式去修改文件的内容,意味着可以多线程同时读写多个文档.
2,遇到处理包含很多图形的文档,用此方法的效率非常高(因为用pia库要使用excel程序打开文件),例如处理ppt文档,就非常高效.
3,openxml库保存信息的结构相对来说很难懂(为了高效保存信息,这也是xlsx比xls更小的原因吧).

1,首先,要先下载并安装sdk.安装的时候要记住路径,因为相应的dll就在那里.安装目录下还有一个很重要的tool,可以让你看到一个文档的结构还有相对应的xml和C#代码.

excelhomesnake

2,使用之前说的tool,打开一个工作簿.可以看到这样的结构.

• 左边是以层次结构显示的对象结构
• xml后缀的对象,在openxml中是以xxxPart来命名封装,主要负责按照xml的规则的操作.例如:/xl/workbook.xml   这个对应的是一个WorkbookPart的对象
• 后缀是(xxx),是一个数据封装的对象.就像pia库里面的对象一样.一般一个xxxPart对象,都对应一个xxx对象.
• 从图中可以看出来,WorkbookPart的对象下有各种Part的对象,同时也有Workbook的数据对象.因此可以这样来获取
  1. SpreadsheetDocument sd= SpreadsheetDocument.Open(this.FilePath, true);
     
  2. 
     
  3. var wrb = sd.WorkbookPart.Workbook

  复制代码
  
  
  

下面大概说说信息的发布结构

• Workbook对象下,保存这这个工作簿的主要信息.例如:工作表数量,每个工作表的界面显示名字和id.注意:所有的对象的relationid是很重要的信息.
• WorkbookPart下,可以获取每个sheet的数据等信息.
• shareStrings.xml,保存excel单元格值中,所有的文本信息.他就像一个字典一样,单元格的值只要是文本类型,就会保存在这里,在sheet的数据中,只会保存这个文本存放在shareStrings中的对应索引而已.这样,相同的文本,就只需要保存在一个空间,然后多次引用.
• excel的图片的保存原理与字符串是一样.
• 其他的对象,根据名字也可以大概知道负责什么了,这里就不详述了.
• 使用这个tool有一个很好的地方,他提供了xml前后对比的功能.意味着,当你不知道修改一个地方的功能所属的对象,你可以通过实际操作,把文件保存后,使用对比功能,对比与之前的差异,马上就可以知道修改的功能所属的对象在哪里.相当于vba的录制宏了.
  

VBA万岁

excelhomesnake 发表于 2015-12-18 15:18
3,openxml库的基本使用
实际上我也只是接触了它几天而已,因此没有很深入的理解.如有错误,望见谅.
openxml ...

安装了VSTO2010，还需要下载并安装sdk吗？

excelhomesnake

VBA万岁 发表于 2015-12-18 16:23
安装了VSTO2010，还需要下载并安装sdk吗？

要的,这个sdk是另外的,不是.net 系统自带的.上网找一下连接,到微软官网就有下载的