ExcelManager基于.Net的Excel读写管理类库（一）

1.        Excel读文件目前仅支持单工作簿&＃xff0c;单工作表读取

2.        使用读写功能时需要指定当前操作Sheet表&＃xff08;默认为Sheet1&＃xff09;&＃xff0c;请使用索引枚举EnumType.SheetIndex方式

3.        写Excel文件&＃xff0c;提供&＃xff08;普通&＃xff0c;ReWrite重写&＃xff0c;Append追加&＃xff09;三种操作方式;普通方式为正常情况下对新文件写入&＃xff0c;重写是对原数据进行覆盖性重写&＃xff0c;追加则是在指定Sheet之后追加数据&＃xff0c;若进行大数据量写入操作&＃xff0c;请用此项。 “重写”和“追加”操作中&＃xff0c;若写入时超过当前Sheet最大容量则自动创建Sheet表继续写入

4.        写入数据量的大小要求单次写入&＃xff1a;60000行以内,255列以内&＃xff0c;Cell值长度1-255字符&＃xff0c;目前写操作仅支持单工作簿&＃xff0c;最大存在32个Sheet&＃xff0c;每Sheet最大存储量为60000行

5.        写入数据时&＃xff0c;若不对Sheet重命名&＃xff0c;请设置SheetName属性为空值&＃xff0c;按照Excel约定SheetName的名称不能超过32字符

6.        本版本暂不提供数据插入功能

六、适用范围

       此操作类库适用与.NET平台下的Windows应用程序及Web应用程序

五、性能测试

       环境:WindowsXP、Pentium(R)4 CPU 2.8GHZ、512MB内存。

Excel单文件追加测试结果&＃xff1a;

数据量           时间

5W               7S

15W              26S

25W              41S

40W              59S

50W              1:24S

60W              1:39S

60W              1:52S

100W            3:27S

六、使用方法

引用 las.foundation.Interfaces,las.foundation.ExcelManager&＃xff0c;las.foundation.SmartExcel

       1.读取Excel文件

ReadFile
 1 DataSet ds &＃61; new DataSet();
 2
 3                            IExcelManager iex &＃61; null;
 4
 5                            try
 6
 7                            { 
 8
 9                                     iex &＃61; ExcelManagerFactory.Instance().CreateExcelManager();
10
11                                     iex.FilePath &＃61;需读取文件名;
12
13                                     if (Xml架构文件 !&＃61; "")
14
15                                     {
16
17                                               iex.XMLFilePath &＃61;Xml架构文件;
18
19                                     }
20
21                                     iex.Open();
22
23                                     // 指定当前读取的工作表&＃xff0c;用索引方式&＃xff08;Sheet1—Sheet32&＃xff09;
24
25                                     iex.ActiveSheet(EnumType.SheetIndex.Sheet1);
26
27                                     ds &＃61; iex.Read();
28
29                            }
30
31                            catch(Exception ex)
32
33                            {
34
35                                     MessageBox.Show(ex.Message.ToString());
36
37                            }
38
39                            finally
40
41                            {
42
43                                     if(iex !&＃61; null)
44
45                                     {
46
47                                               iex.Close();
48
49                                     }
50
51                            }
52
53

       2.读取Cell单元格

ReadCell
 1IExcelManager iex &＃61; null;          
 2
 3                     try
 4
 5                     { 
 6
 7                            iex &＃61; ExcelManagerFactory.Instance().CreateExcelManager();
 8
 9                            iex.FilePath &＃61; 读取的文件名;
10
11if (Xml架构文件 !&＃61; "")
12
13                                     {
14
15                                               iex.XMLFilePath &＃61;Xml架构文件;
16
17                                     }
18
19                            iex.Open();
20
21                                     // 指定当前读取的工作表&＃xff0c;用索引方式&＃xff08;Sheet1—Sheet32&＃xff09;
22
23                                     iex.ActiveSheet(EnumType.SheetIndex.Sheet1);
24
25                            strRet &＃61; iex.ReadCell(单元格名称);
26
27                     }
28
29                     catch(Exception ex)
30
31                     {
32
33                            MessageBox.Show(ex.Message.ToString());
34
35                     }
36
37                     finally
38
39                     {
40
41                            if(iex !&＃61; null)
42
43                            {
44
45                                   iex.Close();   
46
47                            }
48
49                     }
50
51

       3.写入文件

WriteFile
 1 IExcelManager iex &＃61; null;          
 2
 3                     string strPa &＃61; "";
 4
 5                     strPa &＃61; this.txtFileName.Text;                                                          
 6
 7                     try
 8
 9                     {
10
11                            iex &＃61; ExcelManagerFactory.Instance().CreateExcelManager();
12
13                            iex.FilePath &＃61; strPa;
14
15                            iex.DataSource &＃61; 数据源;
16
17                            iex.WriteType &＃61; EnumType.WriteType.None;
18
19                            iex.SheetName &＃61; 设置Sheet名称
20
21                            iex.OpenCreate();
22
23                                     // 指定当前读取的工作表&＃xff0c;用索引方式&＃xff08;Sheet1—Sheet32&＃xff09;
24
25                                     iex.ActiveSheet(EnumType.SheetIndex.Sheet1);
26
27                            iex.Write();
28
29                            // 重写数据
30
31//iex.ReWrite(重写开始行);
32
33                     }
34
35                     catch(Exception ex)
36
37                     {
38
39                            MessageBox.Show(ex.Message.ToString());
40
41                     }
42
43                     finally
44
45                     {
46
47                            if(iex !&＃61; null)
48
49                            {
50
51                                   iex.Close();
52
53                            }
54
55                     }
56
57

       4.写入数据至某单元格

WriteCell
 1IExcelManager iex &＃61; null;          
 2
 3                     string strPa &＃61; 写入的文件名;
 4
 5                     try
 6
 7                     {
 8
 9                            iex &＃61; ExcelManagerFactory.Instance().CreateExcelManager();
10
11                            iex.FilePath &＃61; strPa;
12
13                            iex.BackColor &＃61;背景颜色;
14
15                            iex.ForeColor &＃61;字体颜色
16
17                            iex.Font &＃61; 字体样式;
18
19                           iex.OpenCreate();                     
20
21                                               // 指定当前读取的工作表&＃xff0c;用索引方式&＃xff08;Sheet1—Sheet32&＃xff09;
22
23                                               iex.ActiveSheet(EnumType.SheetIndex.Sheet1);      
24
25    iex.WriteCell(行&＃xff0c;列&＃xff0c;写入值);
26
27                            }
28
29                     catch(Exception ex)
30
31                     {
32
33                            MessageBox.Show(ex.Message.ToString());
34
35                     }
36
37                     finally
38
39                     {
40
41                            if(iex !&＃61; null)
42
43                            {
44
45                                   iex.Close();   
46
47                            }
48
49                     }
50
51                                                                      
52

1.        .NET操作Excel COM对象

2.        Microsoft MSDN

3.        http://support.microsoft.com/default.aspx?scid&＃61;kb;zh-cn;306023

4.        http://support.microsoft.com/kb/283667/zh-cn

5.        文件创建部分参考博客园灵感之源vb.net SmartExcel及C#SmartExcel两位朋友的方法&＃xff0c;感谢两位朋友所付出的劳动&＃xff0c;谢谢&＃xff01;
            (个人意见&＃xff1a;鉴于此方法使用组件的问题,不建议用此方法)  如有兴趣的朋友可以查看

七、版权

ExcelManager的技术&＃xff0c;思想都来自于网络&＃xff0c;鉴于人人为我&＃xff0c;我为人人&＃xff0c;基于网络&＃xff0c;服务于网络的原则&＃xff0c;大家可以自由借鉴其中好的部分。当然由于各种原因&＃xff0c;此版本仅到1.1版本&＃xff0c;其中内部还有很多地方存在不足,需要完善,有时间我会继续完善&＃xff0c;有机会参考java中的javaExcelAPI的进行更好的实现。

如果你有更好的想法&＃xff0c;请不吝赐教&＃xff0c;其它相关服务可以与我联系(MSN:linfuguo&＃64;hotmail.com)。

八、设计图
1.User Case

2.基本类图

3.写入文件序列图

4.读取文件序列图