通用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 >

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( " 未有找到指定名称的单元格！ " );
}
}
}
}

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;
}
}
}
}

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;
}
}
}
}

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;
}
}
}
}

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;
}
}
}
}

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);
}

}
}
}
}

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文件，请重新指定！ " );
}
}
}
}

<? 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 >

<? 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 >