摘要：有时候我们的数据存放在Excel中（特别是对于用户来说更喜欢使用Excel收集一些常用数据），而系统又需要这些数据来处理其他业务，那么此时我们就需要将这些数据导入到数据库中。但是鉴于Excel的样式多种多样，因此每次导入时都必须书写很多重复的代码。很明显对于一个软件开发者做这些重复劳动是一件很无趣的事情。那么怎样来寻中一种通用的方法呢？今天我们就一块看一下如何来解决这个问题。
主要内容
- Excel操作组件的选择
- 总体设计思路
- 配置文件设计
- 类设计
- 编码实现
- 一点补充
- 简单的测试
- 总结
一、Excel操作组件的选择
在开始今天的主题之前我们先简单的看一个基础的问题，那就是如何进行Excel的读写。关于Excel的读写操作目前主要分为：1.Oledb数据库连接方式2.使用Excel.exe Com组件3.使用第三方控件。具体哪种方式好我们要依据具体情况而定，对于第一种方式则要求excel表格必须是想数据库中的表一样规范，例如如果excel牵扯到合并单元格的情况就很难处理了。对于第二种方式则要求用户必须按照Excel，而且其效率比较低。考虑到我们的需求，所以这里选择第三种方式来操作Excel。操作Excel的第三方控件比较多，常见的如NPOI、myxls、Aspose.Cells等。前两者都是开源的，并且NPOI除了写Excel功能比较强之外对于Excel读取也是十分优秀（myxls读取excel不如NPOI）。Aspose.Cells是一款商业控件，其操作方便性当然也是十分强大的，而且Aspose是一个系列组件，不仅有操作Excel的组件还有关于word、ppt、pdf、flash等操作组件。这里因为项目开发中使用的是Aspose.Cells，因此下面的例子中我们就拿Aspose.Cells来进行Excel操作（大家可以去找破解版或者使用NPOI，当然也可以用myxls等）。
二、总体设计思路
我们去设计通用Excel的目的就是为了避免重复工作，也就是说不必因为Excel的样式、数据等变化而重新从零做起、重复劳动。因此我们就必须抽取一个通用的东西出来，使用时只需要关注相关的业务而不必过度关注相关excel操作和存储。再简单一点就是封装共同点，暴漏个性点。考虑到这种情况，我们可以使用配置文件的方式来解决这个问题。在配置文件中我们配置Excle要导入的表、字段等信息，在进行导入时再依据配置文件将数据导入到数据库中。这样一来，在需要进行Excel导入时只需要为某个或多个excel配置一个xml文件，然后调用相关的类就可以完成整个excel导入工作了。
补充：通用的局限性
在这里说明一下，虽然我们设计的是一个通用的Excel导入程序，但是这里的"通用"只是相对来说的，并不是考虑了所有Excel的情况，因为Excel的设计情况十分的复杂多样，要将所有的情况都考虑进去是一个漫长的过程。我们这里的程序只考虑对于单sheet导入一个或多个表中的情况，并且不考虑包含统计行的情况（可以包含合并行、代码表字段等）。
三、配置文件设计
既然考虑使用xml配置的方式来设计通用Excel导入，因此如何设计好xml也就成了设计的重点。对于单表导入（一个Excel主要导入到一个数据库表中，当然这并不排除牵扯其他代码表的情况）我们的配置文件无论以数据库为基础设计（主要是依据数据库表结构）还是以Excel(主要是依据Excel格式设计)为基础设计都可以，但是如果是多表导入（也就是一个Excel可以导入到几张表中的情况）的话考虑其复杂性还是以数据库为基础更为合适。因此考虑到这种情况，我们整个配置设计会以数据库表结构为基础来设计。最终我们的设计样例如下：
<?xml version="1.0" encoding="utf-8" ?> <Config EndTag="RowBlank" HeaderIndex="" DataIndex=""> <Table Name="" DeleteRepeat="true" ExcludedColumns="" > <Column IsPrimaryKey="" ColumnName="" HeaderText="" Required="true" DataType="number" DataLength="100" DefaultValue="" Comment=""> <CodeTalbe Name="" PrimaryKey="" ReferenceColumn="" Condition=""></CodeTalbe> </Column> </Table> </Config>
在最外层为Config节点，代表整个配置。其属性EndTag(数据读取的结束标志，例如"RowBlank"代表空行结束，在读取Excel时遇到某行没有任何数据的情况则视为结束；也可以为某个列地址，在导入时到了此列就会结束导入操作)；属性HeaderIndex代表excel表头对应的行值（从1开始）；DataIndex表示数据列起始行索引（从1开始）。
接着是Table节点，对应数据库中的表，可以有多个。其Name属性对应要导入的表名称；DeleteRepeat属性表示是否删除重复行（如果为true则会根据主键先删除重复行再执行插入操作）；ExcludedColumns表示排除列，多个列名使用"，"分割（这些字段不会导入）。
Table节点内当然就是Column节点，也就是对应的列，通常有多个（注意对于excel中没有的列，而数据库表需要导入的，也需要配置Column节点，此时HeaderText为空或不配置HeaderText属性）。IsPrimarykey属性表示是否为主键（当Table节点配置DeleteRepeat为ture时必须指定一个Column节点的IsPrimaryKey为true，因为此属性是为了delete条件做准备的[有可能它不是真正的主键]）；ColumnName表示对应的列名；HeaderText表示对应的Excel列头（在依据Excel别名导入时根据此值确定导入的列）；Required指定此列是否为必须导入的列（如果配置为true，excel中此列为空并且没有配置默认值的话则会抛出异常）；DataType为数据类型（例如string、number，用于数据校验）；DefaultValue为默认值（注意其值不一定是指定的字符值，可以是"Max"、"NewID".如果为Max，那么此列必须为数值类型，此时在导入的时候如果需要使用默认值，就会在原来数据库表中此列最大值的基础上加上1导入到数据库中，如果为NewID在导入的时候如果需要使用默认值系统就会自动创建id）；Comment是此列的说明。
在Column节点中还可以配置CodeTable节点，表示代码表。Name属性值主表的表名称；PrimaryKey指主表的主键，也就是字表的外键；ReferenceColumn表示对应代码字段关联名称列，也就是我们导入时所依据的excel对应值（例如CategoryID对应CategoryName，那么ReferenceColumn就是CategoryName，因为往往Excel中可能存放的是类似于CategoryName的东西而不是CategoryID，而导入操作时需要CategoryID）。
四、类设计
我们有了思路之后，接下来就来看一下类的设计吧。
在这些类中Excel类是整个导入的核心，其最初要的方法就是Import()，当然除此之外所有对于Excel的读取和对数数据库的操作以及对配置对象的解析都是由此类负责；Config类是对整个配置的抽象，其对应的方法图中也已经标出，每个Config类对应多个实体类；Entity是对于表的抽象，就是表对应的实体类；Property类是对于列的抽象，每个Entity中包含多个Property；另外DictionaryEntity是数据字典，是对代码表的抽象，每个Property可以对应一个代码表；除此之外ConfigHelper是对于应用程序配置的封装；AsposeCell是对Aspose.Cells的封装，包含常用的Excel读写方法。
五、编码实现
接下来我们就开始实现整个设计吧，相信有了上面的说明和代码中的注释，理解起来应该很简单的，我就不再过多赘余了。
AsposeCell类
using System; using System.Collections.Generic; using System.Text; using Aspose.Cells; namespace Cmj.DataExchange { //Aspose帮助类，对常用Aspose用法进行封装 public class AsposeCell { private Workbook _workbook = null; private Dictionary<string,Worksheet> _worksheets = null; private Worksheet _currentWorksheet = null; public AsposeCell(string fullName) { _workbook = new Workbook(); _workbook.Open(fullName); _worksheets = new Dictionary<string, Worksheet>(); foreach (Worksheet worksheet in _workbook.Worksheets) { _worksheets.Add(worksheet.Name, worksheet); } _currentWorksheet = _workbook.Worksheets[0]; } //设置指定名称的sheet为当前操作sheet public void SetCurrentWorksheet(string worksheetName) { if (_worksheets.ContainsKey(worksheetName)) { _currentWorksheet = _worksheets[worksheetName]; } else { throw new Exception("当前工作薄不存在""+worksheetName+""工作表！"); } } //设置指定索引（从0开始）的sheet为当前操作sheet public void SetCurrentWorksheet(byte worksheetIndex) { if (worksheetIndex <= _worksheets.Count) { _currentWorksheet = GetWorkSheetByIndex(worksheetIndex); } else { throw new Exception("工作表索引范围超过了总工作表数量！"); } } //根据索引得到sheet public Worksheet GetWorkSheetByIndex(byte index) { byte i = 0; Worksheet worksheet = null; foreach(string name in _worksheets.Keys) { if (index == i) { worksheet = _worksheets[name]; } i++; } return worksheet; } //根据sheet名称得到sheet public Worksheet GetWorkSheetByName(string sheetName) { if (_worksheets.ContainsKey(sheetName)) { return _worksheets[sheetName]; } else { return null; } } /// <summary> /// 判断指定行是否有数据 /// </summary> /// <param name="row">从1开始，为Excel行序号</param> /// <returns></returns> public bool RowHasValue(int row)//指定行是否有数据（以连续50列没有数据为标准） { bool r = false; for (int i = 0; i < 50; ++i) { if (_currentWorksheet.Cells[row - 1, i].Value != null && _currentWorksheet.Cells[row - 1, i].Value.ToString() != "") { r = true; break; } } return r; } public bool RowHasValue(string position)//指定行是否有数据（以连续50列没有数据为标准） { bool r = false; for (int i = 0; i < 50; ++i) { if (GetCellValue(position)!="") { r = true; break; } } return r; } //取得指定sheet中指定cell位置的数据 public string GetCellValue(string worksheetName, string cellName) { return _worksheets[worksheetName].Cells[cellName].Value != null ? _worksheets[worksheetName].Cells[cellName].Value.ToString() : ""; } public string GetCellValue(byte worksheetIndex, string cellName) { return GetWorkSheetByIndex(worksheetIndex).Cells[cellName].Value != null ? GetWorkSheetByIndex(worksheetIndex).Cells[cellName].Value.ToString() : ""; } public string GetCellValue(string cellName) { return _currentWorksheet.Cells[cellName].Value != null ? _currentWorksheet.Cells[cellName].Value.ToString() : ""; } /// <summary> /// 根据行列索引得到指定位置的数据 /// </summary> /// <param name="row">从0开始</param> /// <param name="column">从0开始</param> /// <returns></returns> public string GetCellValue(int row, int column) { return _currentWorksheet.Cells[row, column].Value != null ? _currentWorksheet.Cells[row, column].Value.ToString() : ""; } /// <summary> /// 判断某位置的单元格是否为合并单元格 /// </summary> /// <param name="row">从0开始</param> /// <param name="column">从0开始</param> /// <returns></returns> public bool IsMerged(int row,int column) { return _currentWorksheet.Cells[row, column].IsMerged; } //取得合并单元格的数据 public string GetMergedCellValue(int row, int column)//事实上合并单元格只有第一个单元格有值其他的全为空，但是我们知道其实从意义上理解合并单元格除了第一个单元格外其他单元格的值同第一个，因此这里提供这样一个方法 { string r = ""; int t=row-1; if (IsMerged(row, column)) { if (GetCellValue(row, column) != "") { r = GetCellValue(row, column); } else//约定合并单元格只是合并行并不和并列，并且合并行数最多50 { while (t >= 0 && (row-t)<50) { if (GetCellValue(t, column) != "") { r = GetCellValue(t, column); break; } t--; } } } return r; } //取得Range名称集合 public List<string> GetRangeNames() { List<string> names = new List<string>(); foreach (Range r in _workbook.Worksheets.GetNamedRanges()) { names.Add(r.Name); } return names; } //取得某行Range名称集合 public List<string> GetRangeNames(int rowIndex) { List<string> names = new List<string>(); if (_workbook.Worksheets.GetNamedRanges()!=null) { foreach (Range r in _workbook.Worksheets.GetNamedRanges()) { if (r.FirstRow == rowIndex && r.Worksheet == _currentWorksheet) { names.Add(r.Name); } } } return names; } //根据Range（别名）取得行索引 public int GetRowIndexByRangeName(string rangeName)//只返回第一行索引（从0开始） { if (_workbook.Worksheets.GetRangeByName(rangeName) != null) { return _workbook.Worksheets.GetRangeByName(rangeName).FirstRow; } else { throw new Exception("未有找到指定名称的单元格！"); } } //根据Range（别名）取得列索引 public int GetColumnIndexByRangeName(string rangeName)//只返回第一行索引（从0开始） { if (_workbook.Worksheets.GetRangeByName(rangeName) != null) { return _workbook.Worksheets.GetRangeByName(rangeName).FirstColumn; } else { throw new Exception("未有找到指定名称的单元格！"); } } //根据Range(别名)取得行列索引 public int[] GetRowAndColumnIndexByRangeName(string rangeName) { int[] i = new int[2]; if (_workbook.Worksheets.GetRangeByName(rangeName) != null) { i[0] = _workbook.Worksheets.GetRangeByName(rangeName).FirstRow; i[1]=_workbook.Worksheets.GetRangeByName(rangeName).FirstColumn; return i; } else { throw new Exception("未有找到指定名称的单元格！"); } } } }
ConfigHelper类
using System; using System.Collections.Generic; using System.Text; using System.Configuration; namespace Cmj.DataExchange { //配置辅助类，主要用于读取应用程序配置文件 internal class ConfigHelper { private static ConfigHelper configHelper = null; private static object obj = new object(); private string excelPath = "";//Excel文件所在路径或者其根目录（此时会将其下所有excel全部倒入） private bool useTransaction = false; private ConfigHelper() { //读取excel配置路径 if (ConfigurationManager.AppSettings["ExcelPath"] != null) { excelPath = ConfigurationManager.AppSettings["ExcelPath"]; } else { throw new Exception("未发现Excel配置路径（ExcelPath），请检查配置文件！"); } if (ConfigurationManager.AppSettings["useTransaction"] != null) { useTransaction = Convert.ToBoolean(ConfigurationManager.AppSettings["useTransaction"]); } } public static ConfigHelper Instance() { lock (obj) { if (configHelper == null) { configHelper = new ConfigHelper(); } } return configHelper; } public string ExcelPath { get { return excelPath; } set { excelPath = value; } } public bool UseTransaction { get { return useTransaction; } set { useTransaction = value; } } } }
DictionaryEntity类
using System; using System.Collections.Generic; using System.Text; namespace Cmj.DataExchange { //代码表类，是对CodeTable的抽象 internal class DictionaryEntity { private string name = "";//代码表名称，对应数据库中主表名称 private string primaryKey = "";//代码表主键，也就是字表的对应外键 private string referenceColumn = "";//代码字段关联名称列，也就是我们导入时所依据的excel对应值 private string condition = "";//相关条件 public DictionaryEntity() { } public string Name { get { return name; } set { name = value; } } public string PrimaryKey { get { return primaryKey; } set { primaryKey = value; } } public string ReferenceColumn { get { return referenceColumn; } set { referenceColumn = value; } } public string Condition { get { return condition; } set { condition = value; } } } }
Property类
using System;
using System.Collections.Generic;
using System.Text;
namespace Cmj.DataExchange
{
//配置属性类，抽象了配置文件的配置属性，对应于数据库中的字段
internalclass Property
{
privatebool isPrimaryKey=false;//是否为主键
privatestring columnName ="";//数据库列名称
privatestring headerText ="";//对应的excel列头名称
privatebool required =true;//是否为必填字段
privatestring dataType ="string";//数据类型（默认为string类型）