摘要：有时候我们的数据存放在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
{
//配置属性类，抽象了配置文件的配置属性，对应于数据库中的字段
internal class Property
{
private bool isPrimaryKey=false;//是否为主键
private string columnName = "";//数据库列名称
private string headerText = "";//对应的excel列头名称
private bool required = true;//是否为必填字段
private string dataType = "string";//数据类型（默认为string类型）
private int dataLength = 5000;//数据长度（默认为5000）
private string defaultValue = "";//默认值(对应excel此列的值如果为空则会使用此值来导入)
private string comment = "";//字段说明信息（非必要属性）
private DictionaryEntity codeTalbe = null;//对应的代码表
public Property()
{
}
public bool IsPrimaryKey
{
get
{
return isPrimaryKey;
}
set
{
isPrimaryKey = value;
}
}
public string ColumnName
{
get
{
return columnName;
}
set
{
columnName = value;
}
}
public string HeaderText
{
get
{
return headerText;
}
set
{
headerText = value;
}
}
public bool Required
{
get
{
return required;
}
set
{
required = value;
}
}
public string DataType
{
get
{
return dataType;
}
set
{
dataType = value;
}
}
public int DataLength
{
get
{
return dataLength;
}
set
{
dataLength = value;
}
}
public string DefaultValue//解析过的默认值（也就是说直接就是值，而不是其地址什么的）
{
get
{
return defaultValue;
}
set
{
defaultValue = value;
}
}
public string Comment//说明信息
{
get
{
return comment;
}
set
{
comment = value;
}
}
public DictionaryEntity CodeTable//代码表
{
get
{
return codeTalbe;
}
set
{
codeTalbe = value;
}
}
}
}
Entity类
using System;
using System.Collections.Generic;
using System.Text;
namespace Cmj.DataExchange
{
//实体类，对配置文件中Table的抽象，对应数据库中的表
internal class Entity
{
private string name = "";//表名称
private bool deleteRepeat = false;//是否删除重复（默认为false，如果设为true则在插入时首先根据主键删除重复信息）
private List<Property> propertys = null;//属性集合（每个实体对应多个Property）
private List<string> excludedColumns = null;//排除字段（也就是指明哪些字段不用导入）
public Entity()
{
propertys = new List<Property>();
excludedColumns = new List<string>();
}
public Entity(string name)
{
this.name=name;
excludedColumns = new List<string>();
propertys = new List<Property>();
}
public string Name
{
get
{
return name;
}
set
{
name = value;
}
}
public bool DeleteRepeat
{
get
{
return deleteRepeat;
}
set
{
deleteRepeat = value;
}
}
public List<string> ExcludedColumns
{
get
{
return excludedColumns;
}
set
{
excludedColumns = value;
}
}
public List<Property> Propertys
{
get
{
return propertys;
}
set
{
propertys = value;
}
}
}
}
Config类
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Xml;
using Cmj.MyFile;
namespace Cmj.DataExchange
{
//配置类，是对整个配置的抽象
internal class Config
{
#region excel读取相关成员变量
private MyXML xml = null;
#endregion
#region 配置文件相关成员变量
private string endFlag = "RowBlank";//Excel结束标志
private byte headerIndex = 1;//列头所在行
private byte dataIndex = 2;//数据行起始位置
private List<Entity> entities = null;//配置类对应的实体类（也就是说配置时，每个Config节点中可以有多个Table节点）
#endregion
public Config(string excelConfigFullName)
{
//初始化成员变量
entities = new List<Entity>();
//读取配置文件，类初始化
Init(excelConfigFullName);
}
public string EndFlag
{
get
{
return endFlag;
}
}
public int HeaderIndex
{
get
{
return headerIndex;
}
}
public int DataIndex
{
get
{
return dataIndex;
}
}
public List<Entity> Entities
{
get
{
return entities;
}
}
//初始化配置对象
private void Init(string excelConfigFullName)
{
int t = 1;
bool r = true;
xml = new MyXML(excelConfigFullName);
endFlag = xml.GetSingleNodeAttribute("Config", "EndFlag") != "" ? xml.GetSingleNodeAttribute("Config", "EndFlag") : "RowBlank";//默认判断是否读取结束的标志设为“RowBlank”，也就是空行。
r = int.TryParse(xml.GetSingleNodeAttribute("Config", "HeaderIndex"), out t);
if (r)
{
headerIndex = Convert.ToByte(xml.GetSingleNodeAttribute("Config", "HeaderIndex"));
}
r = int.TryParse(xml.GetSingleNodeAttribute("Config", "DataIndex"), out t);
if (r)
{
dataIndex = Convert.ToByte(xml.GetSingleNodeAttribute("Config", "DataIndex"));
}
if (MyFilleBase.FileBeing(excelConfigFullName))//根据完整路径判断文件是否存在
{
AddEnities(excelConfigFullName);
}
else
{
throw new Exception("相应配置文件未找到，请检查相应文件是否存在！");
}
}
//初始化实体对象
private void AddEnities(string fullName)
{
xml = new MyXML(fullName);
XmlNodeList tables = xml.GetNodes("Config/Table");
Entity entity = null;
Property property = null;
bool t = true;
bool r=true;
int i = 0;
foreach (XmlNode table in tables)
{
entity = new Entity(xml.GetNodeAttribute(table,"Name"));
r = bool.TryParse(xml.GetNodeAttribute(table, "DeleteRepeat"),out t);
if (r)
{
entity.DeleteRepeat = Convert.ToBoolean(xml.GetNodeAttribute(table, "DeleteRepeat"));
}
if (xml.GetFirstChildNode(table) != null)//说明有列配置
{
foreach (XmlNode column in xml.GetChildNodes(table))
{
property = new Property();
r = bool.TryParse(xml.GetNodeAttribute(column, "IsPrimaryKey"), out t);
if (r)
{
property.IsPrimaryKey = Convert.ToBoolean(xml.GetNodeAttribute(column, "IsPrimaryKey"));
}
property.ColumnName = xml.GetNodeAttribute(column, "ColumnName");
if (xml.GetNodeAttribute(column, "HeaderText") != "")
{
property.HeaderText = xml.GetNodeAttribute(column, "HeaderText");
}
r = bool.TryParse(xml.GetNodeAttribute(column, "Required"), out t);
if (r)
{
property.Required = Convert.ToBoolean(xml.GetNodeAttribute(column, "Required"));
}
property.DataType = xml.GetNodeAttribute(column, "DataType") != "" ? xml.GetNodeAttribute(column, "DataType") : "string";
if (xml.GetNodeAttribute(column, "DataLength") != "")
{
r = int.TryParse(xml.GetNodeAttribute(column, "DataLength"),out i);
if (r)
{
property.DataLength = i;
}
}
property.Comment = xml.GetNodeAttribute(column, "Comment");
property.DefaultValue = xml.GetNodeAttribute(column, "DefaultValue") != "" ? xml.GetNodeAttribute(column, "DefaultValue") : "";
if (xml.GetFirstChildNode(column) != null)//说明有代码表
{
DictionaryEntity dictionaryEntity = new DictionaryEntity();
dictionaryEntity.Name = xml.GetNodeAttribute(xml.GetFirstChildNode(column), "Name");
dictionaryEntity.PrimaryKey = xml.GetNodeAttribute(xml.GetFirstChildNode(column), "PrimaryKey");
dictionaryEntity.ReferenceColumn = xml.GetNodeAttribute(xml.GetFirstChildNode(column), "ReferenceColumn");
dictionaryEntity.Condition = xml.GetNodeAttribute(xml.GetFirstChildNode(column), "Condition");
property.CodeTable = dictionaryEntity;
}
entity.Propertys.Add(property);
}
entities.Add(entity);
}
else
{
string excludedColumns = xml.GetNodeAttribute(table, "ExcludedColumns");
if (excludedColumns != "")
{
foreach (string ec in excludedColumns.Split(','))
{
entity.ExcludedColumns.Add(ec);
}
}
entities.Add(entity);
}
}
}
}
}
Excel类
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Cmj.MyFile;
using Cmj.MyData;
namespace Cmj.DataExchange
{
//Excel导入核心类
public class Excel
{
private Dictionary<string, Config> configs =null;//配置文件全路径和对应的配置类键值集合
private string excelPath="";//excel路径（可以为目录）
bool useTransaction=false;//是否使用事务
private AsposeCell asposeCell = null;//Aspose.Cells封装类
private SqlHelper dbHelper = new SqlHelper();//数据库操作封装类
public Excel()
{
excelPath=ConfigHelper.Instance().ExcelPath;
useTransaction = ConfigHelper.Instance().UseTransaction;
InitConfig();
}
public Excel(string excelPath,bool useTransaction)
{
this.excelPath=excelPath;
this.useTransaction = useTransaction;
ConfigHelper.Instance().ExcelPath = excelPath;
ConfigHelper.Instance().UseTransaction = useTransaction;
InitConfig();
}
//导入操作核心方法，负责整个Excel导入
public void Import()
{
if (configs.Count > 0)
{
if (useTransaction)//使用事务的情况
{
dbHelper.TransationHandler(SqlHelper.TransationType.Open);
ExcuteImport();
dbHelper.TransationHandler(SqlHelper.TransationType.Complete);
}
else
{
ExcuteImport();
}
}
else
{
throw new Exception("Config对象个数为0，无法导入！");
}
}
//执行Excel导入
private void ExcuteImport()
{
string sqlDelete = "";//删除操作对应的sql语句
string sqlInsert = "";//插入操作使用的sql语句
int headerIndex = 1;//列头所在行
int dataIndex = 2;//数据起始行
string endFlag = "RowBlank";
foreach (string excelFullName in configs.Keys)//遍历所有config
{
asposeCell = new AsposeCell(excelFullName);
List<Entity> entities = configs[excelFullName].Entities;//注意每个config中不一定只有一个实体，可以配置多个，这样每个excel可以导入到多张表中
headerIndex = configs[excelFullName].HeaderIndex;
dataIndex = configs[excelFullName].DataIndex;
endFlag = configs[excelFullName].EndFlag;
while (!IsEnd(asposeCell, endFlag, dataIndex))
{
foreach (Entity entity in entities)
{
if (entity.Propertys.Count > 0)//说明配置了Column字段
{
if (entity.DeleteRepeat)
{
sqlDelete = "delete from " + entity.Name + " where " + GetSqlConditionString(asposeCell, entity, headerIndex, dataIndex);
dbHelper.ExcuteNonQuery(CommandType.Text, sqlDelete);
}
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldString(asposeCell, entity) + ")" + " Values(" + GetSqlFieldValueString(asposeCell, entity, headerIndex, dataIndex) + ")";
dbHelper.ExcuteNonQuery(CommandType.Text, sqlInsert);
}
else
{
if (asposeCell.GetRangeNames(headerIndex - 1).Count > 0)//说明指定了单元格别名作为列名
{
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldStringAccordingToAlias(asposeCell, headerIndex) + ") values(" + GetSqlFieldValueStringAccordingToAlias(asposeCell, headerIndex, dataIndex) + ")";
}
else if (entity.ExcludedColumns.Count > 0)//说明有排除字段
{
sqlInsert = "insert into " + entity.Name + "(" + GetSqlFieldStringAccordingToExcludedColumns(asposeCell, entity) + ") values(" + GetSqlFieldValueStringWithoutAlias(asposeCell, entity, headerIndex, dataIndex) + ")";
}
else
{
sqlInsert = "insert into " + entity.Name + " values(" + GetSqlFieldValueStringWithoutAlias(asposeCell, entity, headerIndex, dataIndex) + ")";
}
dbHelper.ExcuteNonQuery(CommandType.Text, sqlInsert);
}
}
dataIndex++;
}
}
}
/// <summary>
/// 根据EndFlag标记判断当前数据行是否结束
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="endFlag"></param>
/// <param name="row"></param>
/// <returns></returns>
private bool IsEnd(AsposeCell asposeCell,string endFlag,int row)
{
bool r = false;
switch (endFlag)
{
case "RowBlank":
if (!asposeCell.RowHasValue(row))
{
r = true;
}
break;
default :
if (asposeCell.GetCellValue(endFlag+row.ToString()) == "")
{
r = true;
}
break;
}
return r;
}
/// <summary>
/// 根据HeaderText配置节确定列索引（从0开始）
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <param name="headerText"></param>
/// <returns></returns>
private int GetColumnIndexByHeaderText(AsposeCell asposeCell,int headerIndex,string headerText)
{
int columnIndex=0;
int r=0;
while (asposeCell.GetCellValue(headerIndex-1, columnIndex) != "")
{
if (asposeCell.GetCellValue(headerIndex-1, columnIndex) == headerText)
{
r = columnIndex;
}
columnIndex++;
}
return r;
}
/// <summary>
/// 根据DefaultValue配置节确定默认值
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entityName"></param>
/// <param name="columnName"></param>
/// <param name="defaultValue"></param>
/// <returns></returns>
private string GetDefaultValue(AsposeCell asposeCell,string entityName,string columnName, string defaultValue)
{
string r = "";
switch (defaultValue)
{
case "NewID": r = Guid.NewGuid().ToString(); break;
case "Max": r = dbHelper.ExecuteScalar(CommandType.Text, "select max(" + columnName + ") from " + entityName + "") != null ?((Convert.ToInt32(dbHelper.ExecuteScalar(CommandType.Text,"select max(" + columnName + ") from " + entityName + "").ToString())+1).ToString()) : "1"; break;
default: r = defaultValue; break;
}
return r;
}
/// <summary>
/// 得到查询条件sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlConditionString(AsposeCell asposeCell, Entity entity,int headerIndex,int dataIndex)
{
string sql="";
foreach (Property p in entity.Propertys)
{
if(p.IsPrimaryKey)
{
sql+=p.ColumnName+"=";
if (p.HeaderText!="")
{
sql+="'"+asposeCell.GetCellValue(dataIndex-1,GetColumnIndexByHeaderText(asposeCell,headerIndex,p.HeaderText))+"'";
}
else
{
sql += "'"+GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue)+"'";
}
sql += " and";
}
}
sql = sql.Substring(0, sql.Length - 4);
return sql;
}
/// <summary>
/// 得到查询字段sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <returns></returns>
private string GetSqlFieldString(AsposeCell asposeCell,Entity entity)
{
string sql = "";
foreach (Property p in entity.Propertys)
{
sql += p.ColumnName+",";
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 依据别名得到查询字段sql段
/// </summary>
/// <param name="asposeCell"></param>
/// <returns></returns>
private string GetSqlFieldStringAccordingToAlias(AsposeCell asposeCell,int headerIndex)
{
string sql = "";
foreach (string columnName in asposeCell.GetRangeNames(headerIndex - 1))
{
sql += columnName + ",";
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 依据排除列得到查询字段sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <returns></returns>
private string GetSqlFieldStringAccordingToExcludedColumns(AsposeCell asposeCell, Entity entity)
{
string sql = "";
foreach(DataColumn column in dbHelper.GetDataTable(CommandType.Text,"select top 0 * from "+entity.Name).Columns)
{
if (!entity.ExcludedColumns.Contains(column.ColumnName))
{
sql += column.ColumnName + ",";
}
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到查询值sql语句段
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="entity"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueString(AsposeCell asposeCell, Entity entity,int headerIndex, int dataIndex)
{
string sql = "";
string v = "";
foreach (Property p in entity.Propertys)//遍历实体的所有属性
{
if (p.CodeTable != null)
{
sql += "'" + GetCodeTableValue(asposeCell,p,headerIndex,dataIndex).Replace("'","''") + "',";//注意如果单元格本身的值就有“'”的情况
}
else//说明此属性是一个代码表字段
{
if(asposeCell.IsMerged(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText)))//是否为合并单元格(对于合并单元格取此合并单元格的第一个值)
{
v=asposeCell.GetMergedCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText));
}
else
{
v=asposeCell.GetCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, p.HeaderText));
}
if (v == "")//说明单元格中没有任何值，就要考虑“默认值”和“必须”属性
{
if (GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue) != "")//说明有默认值
{
v = GetDefaultValue(asposeCell, entity.Name, p.ColumnName, p.DefaultValue);
}
else//如果单元格没有值并且无默认值，则检查此属性是否是必须的
{
if (!p.Required)
{
v = "";
}
else
{
throw new Exception("列\""+p.HeaderText+"\""+"不能为空！");
}
}
}
//检查类型
if (p.DataType != "" && p.DataType != "string")
{
if (!ValidateDataType(v, p.DataType))
{
throw new Exception("列\"" + p.HeaderText + "\"中存在非\"" + p.DataType + "\"类型数据！");
}
}
//属性长度检查
if (p.DataLength != 0 && p.DataLength != 5000)
{
if(!ValidateDataLength(v,p.DataLength))
{
throw new Exception("列\"" + p.HeaderText + "\"中存长度超过\"" + p.DataLength.ToString() + "\"的数据！");
}
}
sql += "'"+v.Replace("'", "''")+"',";
}
}
sql = sql.TrimEnd(',');
return sql;
}
//数据类型校验
private bool ValidateDataType(string value,string type)
{
bool r=false;
double t = 0;
switch (type.ToLower())
{
case "number":
r = double.TryParse(value, out t);
break;
case "string": r = true;
break;
default: break; ;
}
return r;
}
//数据长度校验
private bool ValidateDataLength(string value, int length)
{
if (value.Length > length)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 得到查询值sql语句段（未配置列情况下使用且有别名）
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueStringAccordingToAlias(AsposeCell asposeCell, int headerIndex, int dataIndex)//对于没有配置列的情况使用
{
string sql = "";
foreach (string columnName in asposeCell.GetRangeNames(headerIndex - 1))
{
if(asposeCell.IsMerged(dataIndex - 1,asposeCell.GetColumnIndexByRangeName(columnName)))
{
sql+="'"+asposeCell.GetMergedCellValue(dataIndex - 1, asposeCell.GetColumnIndexByRangeName(columnName)).Replace("'","''")+"',";
}
else
{
sql += "'" + asposeCell.GetCellValue(dataIndex - 1, asposeCell.GetColumnIndexByRangeName(columnName)).Replace("'", "''") + "',";
}
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到查询值sql语句段（未配置列情况下使用且无别名,不管有没有排除字段）
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetSqlFieldValueStringWithoutAlias(AsposeCell asposeCell,Entity entity, int headerIndex, int dataIndex)//对于没有配置列的情况使用
{
string sql = "";
int column = 0;
while (asposeCell.GetCellValue(headerIndex-1, column) != "")
{
sql += "'" + (asposeCell.IsMerged(dataIndex - 1, column) ? asposeCell.GetMergedCellValue(dataIndex - 1, column) : asposeCell.GetCellValue(dataIndex - 1, column)).Replace("'","''") + "',";
column++;
}
sql = sql.TrimEnd(',');
return sql;
}
/// <summary>
/// 得到代码表的对应值
/// </summary>
/// <param name="asposeCell"></param>
/// <param name="property"></param>
/// <param name="headerIndex"></param>
/// <param name="dataIndex"></param>
/// <returns></returns>
private string GetCodeTableValue(AsposeCell asposeCell,Property property,int headerIndex,int dataIndex)
{
string value=asposeCell.IsMerged(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText))?asposeCell.GetMergedCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText)):asposeCell.GetCellValue(dataIndex-1, GetColumnIndexByHeaderText(asposeCell, headerIndex, property.HeaderText)).Replace("'","''");
StringBuilder sb = new StringBuilder("select distinct ");
sb.Append(property.CodeTable.PrimaryKey);
sb.Append(" from ");
sb.Append(property.CodeTable.Name);
sb.Append(" where ");
sb.Append(property.CodeTable.ReferenceColumn);
sb.Append("='");
sb.Append(value);
sb.Append("'");
if (property.DataType != "" && property.DataType != "string")
{
if (!ValidateDataType(value, property.DataType))
{
throw new Exception("列\"" + property.HeaderText + "\"中存在非\"" + property.DataType + "\"类型数据！");
}
}
if (property.DataLength != 0 && property.DataLength != 5000)
{
if (!ValidateDataLength(value, property.DataLength))
{
throw new Exception("列\"" + property.HeaderText + "\"中存长度超过\"" + property.DataLength.ToString() + "\"的数据！");
}
}
if (dbHelper.ExecuteScalar(CommandType.Text, sb.ToString()) != null)
{
return dbHelper.ExecuteScalar(CommandType.Text, sb.ToString()).ToString();
}
else
{
throw new Exception("没有对应的代码表值！");
}
}
//初始化，主要将excel文件和配置类对应关系存放到configs对象中，方便以后遍历
private void InitConfig()
{
configs=new Dictionary<string,Config>();
List<string> excelFullNames=new List<string>();
if(MyFilleBase.DirectoryBeing(excelPath))//判断目录是否存在(注意：除了多套excel对应多套模板，还可能有一个模板对应多个excel的情况）
{
FileSearch.InitFileInfoList();
List<string> excelConfigFileFullNames= FileSearch.GetFileInfo(".xml", ConfigHelper.Instance().ExcelPath, true, true);//在目录中查找所有名称中包含".xml"的文件
if (excelConfigFileFullNames.Count == 1)//说明是一个excel对应一个xml配置文件的情况
{
AddConfigsByXmlFullNameHasNothingToExcelname(excelConfigFileFullNames[0]);
}
else if(excelConfigFileFullNames.Count>1)//说明目录中有多个xml文件，对应多个excel
{
foreach (string excelConfigFileFullName in excelConfigFileFullNames)
{
AddConfigsByXmlFullName(excelConfigFileFullName);
}
}
else
{
throw new Exception("所指定目录不包含任何XLM模板，请重新指定！");
}
}
else//说明指定的不是目录而是excel文件路径
{
AddConfigByExcelFullName(excelPath);
}
}
//根据Excel全路径构造路径和配置类对应关系（主要用于指导Excel全路径的情况）
private void AddConfigByExcelFullName(string excelFullName)
{
string excelConfigFileFullName = MyFilleBase.GetDirectoryByFullName(excelFullName) + "\\" + MyFilleBase.GetFileNameWithoutExtension(excelFullName) + ".xml";
Config config=null;
if (MyFilleBase.FileBeing(excelConfigFileFullName))
{
config = new Config(excelConfigFileFullName);//创建配置（Config）对象
configs.Add(excelFullName, config);
}
else
{
throw new Exception("所指定文件没有对应的配置文件，请重新指定！");
}
}
//根据XML文件全路径构造路径和配置类对应关系(得到一个xml文件对应的所有excel然后构造config对象存放到configs中)
private void AddConfigsByXmlFullName(string xmlFullName)
{
string excelDirectory = MyFilleBase.GetDirectoryByFullName(xmlFullName);//根据路径取得对应的目录
Config config=null;
FileSearch.InitFileInfoList();
List<string> excelFullNames = FileSearch.GetFileInfo(MyFilleBase.GetFileNameWithoutExtension(xmlFullName), excelDirectory, true, true);//根据xml的名称搜索包含此名称的文件
if (excelFullNames.Count >= 1)
{
foreach (string excelFullName in excelFullNames)
{
if (excelFullNames.IndexOf(".xls") != -1)//必须是excel文件(排除xml文件)
{
config = new Config(xmlFullName);
configs.Add(excelFullName, config);
}
}
}
else
{
throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
}
}
//根据XML文件全路径构造路径和配置类对应关系（此种情况由于只有一个xml，必然对应一个或多个excel文件，所以只需要查找excel文件即可）
private void AddConfigsByXmlFullNameHasNothingToExcelname(string xmlFullName)
{
Config config = null;
string excelDirectory=MyFilleBase.GetDirectoryByFullName(xmlFullName);
FileSearch.InitFileInfoList();
List<string> excelFullNames = FileSearch.GetFileInfo(".xls", excelDirectory, true, true);
if (excelFullNames.Count >= 1)
{
foreach (string excelFullName in excelFullNames)
{
config = new Config(xmlFullName);
configs.Add(excelFullName, config);
}
}
else
{
throw new Exception("所指定模板不包含对应的Excel文件，请重新指定！");
}
}
}
}
注意：程序中用到了Cmj.dll（自定义的一个类库）中的几个类，具体说明如下（如果需要具体的代码可以发Email给我kenshincui@hotmail.com ，或者留言）：
SqlHelper：数据库操作类，使用时在配置文件的ConnectionStrings中配置name为"DBCon"的连接串，指明ProviderName即可，如果不使用配置可以直接在构造函数中传递这两个参数。
MyXML：对于XML文件操作的封装了，用于XML文件操作。
MyFileBase：对文件操作的封装。
FileSearch：对文件查找的封装，可以按照关键字查找文件。
六、一点补充
为了更加容易理解，我对程序中的一些约定和处理做少许补充。
Excel类中有参构造函数两个参数分别是excel路径（也可以是目录）以及是否启用事务的bool型变量。如果用户使用无参构造函数的话，就必须在应用程序配置文件的appSettings配置节点配置中配置"ExcelPath"和"UseTransaction"两个节点。由于对数据库的操作需要访问应用程序的配置文件，因此需要配置connectionStrings节点。
一般的导入按照上面的配置就可以完成了。为了方便使用此类还提供了其他导入方式。假如觉得列配置较麻烦的，觉得没有必要配置那么多列的话，可以考虑下面几种方式。
ⅰ 配置文件中如果没有Column配置，可以通过配置单元格的别名，来指示每一个列对应的表字段。
ⅱ 如果配置文件中没有Column配置，而又不希望指定单元格别名，但是Excel中的列和表中的列又不是一一对应的（很可能有些列数据库表中有，而Excel中没有），此时如果Excel中列的顺序和表中顺序一致，你就可以通过在配置文件中设置Table的ExcludedColumns来将这些列排除（多个需要排除的列中间用"，"分割）。
ⅲ 如果我的Excel中的列和数据库表中完全一致且顺序一样，那么你出来配置文件中配置Table属性外就不需要任何额外的配置了。
另外，如果需要在导入时检查是否有默写列唯一的情况的情况（例如ProductName我不希望有重复），对于重复的先删除再倒入，此时可以再此列上设置IsPrimaryKey为true，然后在Table的属性中配置DeleteRepeat为true就可以了；如果所有的导入过程中你需要使用事务机制，可以在应用程序配置appSettings 配置节点中指定useTransaction为true（注意先要保证事务服务是启动的）；程序支持合并行的导入。
关于配置文件和Excel文件的关系，是这样约定的，多数情况下ExcelPath的配置为目录（当然可以是一个Excel文件路径，此时就只导入该Excel文件），对于此种情形可能出现下列情况。
ⅰ 目录中只有一个配置文件，而有一个或多个Excel。这种情况下程序认为此配置文件对应所有Excel文件，将会使用此配置将所有Excel导入。
ⅱ 目录中有多个配置文件，并且有多个Excel文件。此时程序户根据配置文件的名称自动匹配Excel文件，匹配的规则就是它对应的Excle文件的名称中要出现配置文件的名称。
七、简单的测试
下面我们简单的测试一下我们的类库，应该说使用起来十分简单。
ⅰ 在使用之前当然要有一个需要导入的Excel
ⅱ 根据Excel编写xml文件
<?xml version="1.0" encoding="utf-8" ?>
<Config EndTag="RowBlank" HeaderIndex="1" DataIndex="2">
<Table Name="Products" >
<Column ColumnName="SupplierID" HeaderText="供货商" DefaultValue="">
<CodeTalbe Name="Suppliers" PrimaryKey="SupplierID" ReferenceColumn="CompanyName"></CodeTalbe>
</Column>
<Column ColumnName="ProductName" HeaderText="商品名称" DefaultValue="" />
<Column ColumnName="CategoryID" HeaderText="商品种类" DefaultValue="">
<CodeTalbe Name="Categories" PrimaryKey="CategoryID" ReferenceColumn="CategoryName" ></CodeTalbe>
</Column>
<Column ColumnName="QuantityPerUnit" HeaderText="规格" DefaultValue="" />
<Column ColumnName="UnitPrice" HeaderText="单价" DefaultValue="" />
<Column ColumnName="UnitsInStock" HeaderText="库存" DefaultValue="" />
<Column ColumnName="Discontinued" HeaderText="是否停产" DefaultValue="" />
</Table>
</Config>
ⅲ 将excel文件和对应xml文件放到F:\Cmj.DataExchange\wfTest\excel
ⅳ 建立一个项目，添加Cmj.DataExchange.dll引用
ⅴ 在配置文件中配置数据库连接以及Excel目录等
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ExcelPath" value="F:\Cmj.DataExchange\wfTest\excel"/> <!--Excel文件所在路径或者其根目录（此时会将其下所有excel全部倒入）-->
<add key="UseTransaction" value="true"/>
</appSettings>
<connectionStrings>
<add name="DbConStr" connectionString="Data Source=.;Database=Northwind;Integrated Security=SSPI" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
ⅵ 接着确保Distributed Transaction Coordinator服务是启动的（因为程序中用的是.Net2.0的事务，当然你也可以使用.Net1.0事务，此时就无需启动此服务了）。
ⅶ 最后添加下面两句代码就可以完成整个导入了。
Excel ex = new Excel();
ex.Import();
下面是导入前后数据库Northwind中Products表中的数据（已经成功导入）
导入之前
导入之后
八、总结
对于一般的Excel导入情况程序基本上都能够解决，但是开发过程中难免遇到更复杂的情况，此时就需要更多的考虑和更复杂多样的设计，这种情况有机会我们再一看探讨。由于代码相对比较长阅读多少有些不便，这里提供源代码和示例下载 ，需要的朋友可以看一下。
|
本作品采用知识共享署名 2.5 中国大陆许可协议进行许可，欢迎转载，演绎或用于商业目的。但转载请注明来自崔江涛（KenshinCui），并包含相关链接。 |