关于excel在asp.net中使用

.Net使用excel表有两种方式，
一种是通过oldb来进行连接，把excel表作为数据库来处理。
另一种则是通过引用excel对象，进行对象编程来，调用excel的函数方法。

总的来说，第一种很方便，但缺乏变化，第二种很灵活，但是，缺乏规律性。
具体使用中，看来还是要看情况来操作。

第一种情况，是不需要asp.net作什么额外动作，
第二种情况，需要使用tblim.exe来将excel的com转换成为托管主件，才能在import namespace。
其实，也可以有vs.net进行转换，更为方便。
一旦引用excel namespace，其后的编程就能根据excel帮助里面的编程指南，调用它的对象，方法，属性等等。

我在想，今后把它写成一个webservice可以随意从数据库中，将数据倒出来。

the following content updated on 2005-11-23
===================================
其实，我们经常适用到的还是第二种模式。
最近我接到一个项目用到的导入Excel数据的问题，我把我的经验和代码共享出来。

这个项目的Excel的导入假定条件是：
1.有几个range是固定值
2.有一个整块的数据内容区(range)
3.可能有多个符合上面1、2要求，但具体格式不一致的Excel模板。
4.几个不同模板可能导入不同数据库表。

当时由于正在看DOTTEXT代码，对其中Provider这种模式非常感兴趣，考虑了一番认为这个Excel导入项目可以借用Provider模式。

对Provider模式的解释

---

无论源对象是什么，通过IProvider接口，我们可以得到一个较为统一的对象，也就是说同IProvider，我们屏蔽对源数据对象的处理细节，而完成得到一个符合我们要求的目的对象。

--这段解释完全是自己的理解和体会，请大家斧正

---

综合考虑后，我觉得我应该有一个对象来描述目前Excel格式内容，比如：描述固定range，描述不固定的数据块的开始地方（range），从第几列到第几列是数据区，同时定义输入限制数据类型。

我命名这个对象为：ExcelMapDB

/**//// <summary>
    /// ExcelMapDB 的摘要说明。
    /// Author:King astar
    /// Date:2005-10-27
    /// Description:用来描述Excel列对应数据列的关系
    /// </summary>
    /// 
    [Serializable]
    public class ExcelMapDB
    {
        private bool _isfixed = false;
        /**//// <summary>
        /// 是否是固定列
        /// </summary>
        /// 
        [XmlAttribute("IsFixed")]
        public bool IsFixed
        {
            get{return _isfixed;}
            set{_isfixed = value;}
        }

        private String _rowshowName="";
        /**//// <summary>
        /// 对应的datarow的显示名称是什么
        /// </summary>
        /// 
        [XmlAttribute("RowShowName")]
        public String RowShowName
        {
            get{return _rowshowName;}
            set{_rowshowName = value;}
        }


        private String _rowName;
        /**//// <summary>
        /// 对应的datarow的列名是什么
        /// </summary>
        /// 
        [XmlAttribute("RowName")]
        public String RowName
        {
            get{return _rowName;}
            set{_rowName = value;}
        }

        private String _excelRangeName;
        /**//// <summary>
        /// 如果是固定列则直接读取rangName；
        /// eg. A2 B3 etc.
        /// </summary>
        /// 
        [XmlAttribute("ExcelRangeName")]
        public String ExcelRangeName
        {
            get{return _excelRangeName;}
            set{_excelRangeName = value;}
        }

        private int _offSetStartRange;
        /**//// <summary>
        /// 如果是相对列，则使用相对开始列的Y轴的距离来得到值
        /// </summary>
        /// 
        [XmlAttribute("OffYSetStartRange")]
        public int OffYSetStartRange
        {
            get{return _offSetStartRange;}
            set{_offSetStartRange = value;}
        }

        
        public ExcelMapDB()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
    }

 序列化对象的XML

 1<?xml version="1.0" encoding="utf-8"?>
 2<ExcelMapDbRelation xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" DataTableProviderType="QAComponents.Codes.Provider.BaseDataTableProvider, QAComponents.Codes" ExcelStartRangeName="A6" ConvertorType="QAComponents.Codes.Convertor.BaseConvertor, QAComponents.Codes">
 3  <RelationItems>
 4    <ExcelMapDB IsFixed="true" RowName="Temp1" ExcelRangeName="D4" OffYSetStartRange="0" />
 5    <ExcelMapDB IsFixed="true" RowName="Temp2" ExcelRangeName="L4" OffYSetStartRange="0" />
 6    <ExcelMapDB IsFixed="true" RowName="Temp3" ExcelRangeName="T4" OffYSetStartRange="0" />
 7    <ExcelMapDB IsFixed="false" RowName="SerialNo" ExcelRangeName="" OffYSetStartRange="0" />
 8    <ExcelMapDB IsFixed="false" RowName="Ith1" ExcelRangeName="" OffYSetStartRange="1" />
 9    <ExcelMapDB IsFixed="false" RowName="Vf1" ExcelRangeName="" OffYSetStartRange="2" />
10    <ExcelMapDB IsFixed="false" RowName="Pf1" ExcelRangeName="3" OffYSetStartRange="3" />
11    <ExcelMapDB IsFixed="false" RowName="ImPf1" ExcelRangeName="4" OffYSetStartRange="4" />
12    <ExcelMapDB IsFixed="false" RowName="SE1" ExcelRangeName="5" OffYSetStartRange="5" />
13    <ExcelMapDB IsFixed="false" RowName="SMSR1" ExcelRangeName="6" OffYSetStartRange="6" />
14    <ExcelMapDB IsFixed="false" RowName="A1" ExcelRangeName="7" OffYSetStartRange="7" />
15    <ExcelMapDB IsFixed="false" RowName="B1" ExcelRangeName="8" OffYSetStartRange="8" />
16    
17    
18    <ExcelMapDB IsFixed="false" RowName="Ith12" ExcelRangeName="9" OffYSetStartRange="9" />
19    <ExcelMapDB IsFixed="false" RowName="Vf2" ExcelRangeName="10" OffYSetStartRange="10" />
20    <ExcelMapDB IsFixed="false" RowName="Pf2" ExcelRangeName="11" OffYSetStartRange="11" />
21    <ExcelMapDB IsFixed="false" RowName="ImPf2" ExcelRangeName="12" OffYSetStartRange="12" />
22    <ExcelMapDB IsFixed="false" RowName="SE2" ExcelRangeName="13" OffYSetStartRange="13" />
23    <ExcelMapDB IsFixed="false" RowName="SMSR2" ExcelRangeName="14" OffYSetStartRange="14" />
24    <ExcelMapDB IsFixed="false" RowName="A2" ExcelRangeName="15" OffYSetStartRange="15" />
25    <ExcelMapDB IsFixed="false" RowName="B2" ExcelRangeName="16" OffYSetStartRange="16" />
26    
27    <ExcelMapDB IsFixed="false" RowName="Sen" ExcelRangeName="17" OffYSetStartRange="17" />
28    <ExcelMapDB IsFixed="false" RowName="Vbr" ExcelRangeName="18" OffYSetStartRange="18" />
29    <ExcelMapDB IsFixed="false" RowName="Vop" ExcelRangeName="19" OffYSetStartRange="19" />
30    <ExcelMapDB IsFixed="false" RowName="Iop" ExcelRangeName="20" OffYSetStartRange="20" />
31    <ExcelMapDB IsFixed="false" RowName="Io" ExcelRangeName="21" OffYSetStartRange="21" />
32    <ExcelMapDB IsFixed="false" RowName="Gain" ExcelRangeName="22" OffYSetStartRange="22" />
33    <ExcelMapDB IsFixed="false" RowName="FileNo" ExcelRangeName="23" OffYSetStartRange="23" />    
34    
35
36  </RelationItems>
37</ExcelMapDbRelation>

 

接着我定义Excel对应数据表的关系对象
这个对象还要考虑，它用什么转换器来转换

  1/**//// <summary>
  2    /// ExcelMapDB 的摘要说明。
  3    /// Author:King astar
  4    /// Date:2005-10-27
  5    /// Description:用来描述Excel表对应数据表的关系
  6    /// </summary>
  7    public class ExcelMapDbRelation
  8    {
  9        private ExcelMapDB[] _relationItems;
 10        [XmlArray("RelationItems")]
 11        public ExcelMapDB[] RelationItems
 12        {
 13            get{return _relationItems;}
 14            set{_relationItems = value;}
 15        }
 16
 17        private String _dataTableProviderType = "BaseDataTableProvider";
 18        /**//// <summary>
 19        /// 数据提供者类型名称
 20        /// </summary>
 21        [XmlAttribute("DataTableProviderType")]
 22        public String DataTableProviderType
 23        {
 24            get{return _dataTableProviderType;}            
 25            set{_dataTableProviderType = value;}
 26        }
 27
 28        private String _excelStartRangeName;
 29        /**//// <summary>
 30        /// 开始的范围；
 31        /// eg. A2 B3 etc.
 32        /// </summary>
 33        /// 
 34        [XmlAttribute("ExcelStartRangeName")]
 35        public String ExcelStartRangeName
 36        {
 37            get{return _excelStartRangeName;}
 38            set{_excelStartRangeName = value;}
 39        } 
 40
 41        /**//// <summary>
 42        /// 取得数据提供者对象
 43        /// </summary>
 44        [XmlIgnore]
 45        public IDataTableProvider TableProvider
 46        {
 47            get
 48            {
 49                IDataTableProvider dprovider =null;
 50                if(DataTableProviderType!=null)
 51                {
 52                    dprovider = (IDataTableProvider)System.Activator.CreateInstance(Type.GetType(DataTableProviderType));
 53                }
 54                return dprovider;
 55            }
 56    
 57
 58
 59        }
 60
 61        private String _convertorType ;
 62        /**//// <summary>
 63        /// 转换器类型名称
 64        /// </summary>
 65        [XmlAttribute("ConvertorType")]
 66        public String ConvertorType
 67        {
 68            get{return _convertorType;}
 69            set{_convertorType = value;}
 70        }
 71
 72        /**//// <summary>
 73        /// 取得转换器对象
 74        /// </summary>
 75        [XmlIgnore]
 76        public IConvertor DataConvertor
 77        {
 78            get
 79            {
 80                IConvertor convertor =null;
 81                if(ConvertorType!=null)
 82                {
 83                    convertor = (IConvertor)System.Activator.CreateInstance(Type.GetType(ConvertorType));
 84                }
 85                return convertor;
 86            }
 87    
 88
 89
 90        }
 91
 92        
 93
 94        constructor#region constructor
 95        public ExcelMapDbRelation()
 96        {
 97            //
 98            // TODO: 在此处添加构造函数逻辑
 99            //
100        }
101        #endregion
102    }
103    

作了这些基础工作后，我考虑可以来写转换器的工作了，这部分工作是核心工作，在这部分数据会从Excel导入到数据库去。
看看接口

1/**//// <summary>
2    /// IConvertor 的摘要说明。
3    /// </summary>
4    public interface IConvertor
5    {
6
7        void Execute(DataTable dt,ExcelMapDB[] excelMapdbs,String fileName,String excelStartRangeName,int IdentityID);
8    }

只有一个Excute的方法参数分别是Excel对应关系对象，Excel文件路径,excel开始range名称，此Excel表唯一ID

然后，我写了一个BaseConvertor， 主要工作就是打开Excel文件，按照定义的Excel列与数据表列对应关系对象来抓数据，并导入到数据库中去。
请参看代码（注意：打开Excel的方法是Excel11.0版本，office 2003）

  1
  2    /**//// <summary>
  3    /// BaseConvertor 的摘要说明。
  4    /// Author:King astar
  5    /// Date:2005-10-27
  6    /// Description:核心类，将Excel数据导入数据库，是基类，可以继承
  7    /// </summary>
  8    public class BaseConvertor : IConvertor
  9    {
 10
 11        variable#region variable 
 12
 13        protected Excel.ApplicationClass app = null;
 14        protected Excel.Workbooks wbs = null;
 15
 16        protected Excel.Worksheet ws = null;
 17        protected Excel.Workbook wbc =null;
 18        //Excel.Worksheet wsh =null;
 19        protected Excel.Range firstRange = null;
 20        protected Excel.Range fixedRange =null;
 21        protected System.Reflection.Missing _objOpt = System.Reflection.Missing.Value;
 22
 23        protected int _identity;
 24
 25        #endregion
 26
 27
 28        public BaseConvertor()
 29        {
 30            //
 31            // TODO: 在此处添加构造函数逻辑
 32            //
 33        }
 34
 35        IConvertor 成员#region IConvertor 成员
 36
 37        public void Execute(System.Data.DataTable dt, QAComponents.Codes.Configs.ExcelMapDB[] excelMapdbs, String fileName,String excelStartRangeName,int IdentityID)
 38        {
 39            _identity = IdentityID;
 40            
 41            
 42
 43            try
 44            {
 45                Openexcel(fileName);            
 46                System.Data.DataTable myTable = FillTable(dt,excelMapdbs,excelStartRangeName);                
 47            }
 48            catch(System.Exception ex)
 49            {
 50                throw new Exception("导入失败："+ex.Message);
 51            }
 52            finally
 53            {                
 54                Dispose();
 55
 56            }
 57        }
 58
 59        #endregion
 60
 61        public virtual void Openexcel(String fileName)
 62        {
 63            string FileName = @fileName;
 64            FileName = FileName.Replace("/",@"\");
 65            FileName = FileName.Replace(@"\\",@"\");
 66
 67
 68            if(!System.IO.File.Exists(FileName))
 69                throw new Exception("["+FileName+"]不存在！");
 70
 71            try
 72            {
 73                app = new  Excel.ApplicationClass();
 74            }
 75            catch(System.Exception ex)
 76            {
 77                new Exception("无法实例化Excel对象;<br/>details:<hr size=1/>"+ex.Message);
 78            }
 79            app.DisplayAlerts = false;
 80
 81//            //app.UserControl = true;
 82            wbs = app.Workbooks;
 83            try
 84            {
 85                
 86                wbc  = wbs.Open(FileName,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt,_objOpt);            
 87            }
 88            catch(System.Exception ex)
 89            {
 90                throw new Exception("无法正确打开文件;<br/>details:<hr size=1/>"+ex.Message);
 91            }
 92
 93
 94        }
 95        public virtual void Dispose()
 96        {
 97            fixedRange =null;
 98            firstRange = null;
 99            ws = null;
100            wbc = null;
101                        
102            if(wbs!=null)
103            {
104                wbs.Close ();
105                wbs = null;
106            }
107            if(app!=null)
108            {
109                app.Quit();
110                app =null;
111            }
112            
113                
114            
115
116        }
117
118        public System.Data.DataTable  FillTable(System.Data.DataTable myTable,QAComponents.Codes.Configs.ExcelMapDB[] excelMapdbs,string excelStartRangeName)
119        {
120            ws= (Excel.Worksheet)wbc.Worksheets.get_Item(1);
121            firstRange = ws.get_Range(excelStartRangeName.Trim().ToUpper(),this._objOpt);
122            int x =0;
123            x = Convert.ToInt32(excelStartRangeName.Trim().ToUpper().Substring(
1241,1));
125            //开始循环
126            while(firstRange.Value2!=null&&firstRange.Value2.ToString()!="")
127            {
128                
129                System.Data.DataRow dr = myTable.NewRow();
130                
131                for(int i=0;i<excelMapdbs.Length;i++)
132                {
133                    try
134                    {
135                        if(excelMapdbs[i].IsFixed)
136                        {
137                            fixedRange = ws.get_Range(excelMapdbs[i].ExcelRangeName.Trim().ToUpper(),this._objOpt);
138                            dr[excelMapdbs[i].RowName] = ConverData(dr.Table.Columns[excelMapdbs[i].RowName].DataType,fixedRange.Value2);
139                        }
140                        else
141                        {
142                            dr[excelMapdbs[i].RowName]    = ConverData(dr.Table.Columns[excelMapdbs[i].RowName].DataType,
143                                firstRange.get_Offset(_objOpt,excelMapdbs[i].OffYSetStartRange).Value2);
144
145#if DEBUG
146                            object Checkvalue1 = firstRange.get_Offset(_objOpt,excelMapdbs[i].OffYSetStartRange).Value2;
147#endif
148                        }
149                    }
150                    catch(System.Exception ex)
151                    {
152                        throw new Exception(         
153                            String.Format("<font color=red><b>导入数据失败</b></font><br/>附件编号:<b>{0}</b><br/>错误位置:X={1};Y(相对于开始格)={2};对应RowName:{3}<br/>details:<hr size=1/>{4}",
154                            this._identity,
155                            x,
156                            excelMapdbs[i].OffYSetStartRange,
157                            excelMapdbs[i].RowName,
158                            ex.Message));
159
160
161                    
162                    }
163                }
164                
165
166                //myTable.Rows.Add(dr);
167                dr["SummaryID"] = this._identity;
168                ImportDataRow(dr);
169
170
171                x++;
172                firstRange  = firstRange.get_Offset(1,_objOpt);
173#if DEBUG
174                object Checkvalue2 = firstRange.Value2;
175#endif
176                
177
178            }
179            return myTable;
180            
181
182        }
183
184        public virtual void ImportDataRow(DataRow row)
185        {
186            BaseDAL b = new BaseDAL();
187            b.ConfigTable(row.Table.TableName,"ID");
188            b.Add(row);
189        }
190
191        ConverData#region ConverData
192        /**//// <summary>
193        /// 根据定义在Excel列与数据表列对应关系对象中，列的数据类型定义来判定输入类型是否正确
194        /// </summary>
195        /// <param name="dbType"></param>
196        /// <param name="o"></param>
197        /// <returns></returns>
198        public virtual object ConverData(System.Type dbType,object o)
199        {
200            if(o==null)
201                return System.DBNull.Value;
202            if(o.ToString().Trim()==""&&dbType!=typeof(System.String))
203                return System.DBNull.Value;
204
205            if(dbType  == typeof(System.String))
206                return Convert.ToString(o);
207
208            if(dbType== typeof( System.Int32))
209                return Convert.ToInt32(o);
210            
211            if(dbType==typeof(System.Decimal))
212                return Convert.ToDecimal(o);
213
214            return System.DBNull.Value;
215        }
216        #endregion 
217
218
219
220    }

至此Excel导入核心的介绍就完毕了，
抛开代码不说，解决问题的方法还是很简单。

1. 描述Excel的数据对应关系
2. 根据对应关系，进行数据转换。

解决这种问题的根本是要有良好的抽象能力，将实际事务转换为一些可以用代码描述的对象。
呵呵。。。

另外补充的是这种方式也可以用以导出Excel， 由您自由想像了。