最近根据项目需求需要将Excel里面的表格自动到SQL Server数据库中,整个需求如下:
(1)Excel表格有多个Sheet,每个Sheet的名称在SQL Server中有一张表名称与之对应
(2)Excel里面的表格字段和表中的字段一一对应,并且顺序一样
(3)对于Excel里面的部分数据需要处理然后上传
(4)由于外键等约束,可以指定Sheet上传顺序,但是整个过程要基本上实现自动传输处理
整个项目初步分析很简单,我们只需要将其Excel表格的数据取出来上传到数据库中即可,但是在实际编程过程中还是出了不少问题。整个小工具实现当初有三种思路:
a.绑定实体数据模型,然后根据每个表创建一个类,在工厂模式中根据Sheet名来动态创建相应表处理类的对象进行相关数据处理。(这个idea被pass了,原因是表格Sheet数量比较多,需要创建的类太多,重复代码太多)
b.通过SQL进行插入操作,该方法基本上实现了相关的功能,我们只需要判断一些特殊类型(例如string 在插入时我们应该加入‘’,否则会报错,整个SQL语句插入感觉就是网络串口通信,将语句发送然后SQLServer进行解析),同时对于数据表自增不要插入即可。
c.绑定实体数据模型,根据反射来调用相关的数据表结构进行数据传输。该方法核心是如何根据Sheet名的string来动态创建类对象并调用其方法。
例如:我们的项目的namespace为Example, 其中一个Sheet名为 sheetName = “Data1”,并且Excel对应的Data1里面的表数据已经读取到DataTable table中;其中row=table.Rows[i]表示第i行数据。
SheetName=“Example” + sheetName;//获取相应的实体数据表完整类型描述
var Entity = Activator.CreateInstance(Type.GetType(SheetName)); //创建相关的类对象
PropertyInfo[] props = Type.GetType(SheetNam).GetProperties();
int k = 0; //主要标记当前已经是第几个字段
foreach(PropertyInfo item in props) //为了不读取为virtual的字段,我的想法是通过标记来判断
{
bool flag = false;
foreach(var accessor in item.GetAccessor())
{
if(!accessor.IsVirtual)
flag = true;
}
if(flag)
{
item.SetValue(Entity,Convert.ChangeType(row.ItemArray[k], item.PropertyType, null));
k++;
}
}
通过上述代码我们就可以将table里面的一行数据保存到Entity中,但是如何上传到数据库呢?对于上传函数我们已经封装好了,整个函数调用格式为:
class Repository{
public void AddEntity<T>(T value) where T:class
{
_context.Set<T>().Add(value);
}
}
此时问题出现了,这个参数T如何传递,一般的如果我们创建了类对象,我们可以直接写如下语句:
repository.AddEntity<Data1>.Add(Entity);
但是我们此时无法获取Data1类型,而且我们需要不停根据SheetName来创建不同的T对象,因此必须想个办法动态调用该方法:
MethodInfo methodinfo = Type.GetType(repository).GetMethod("AddEntity").MakeGenericMethod(Type.GetType(tableName)); //MakeGennericMethod用类型数组的元素替代当前的泛型方法定义的类型
method.Invoke(repository, object[]{Entity}); //这样可以完成调用AddEntity()方法
上述思想主要是采用C#.net的反射机制来创建.Net的泛型类型实例,这种反射机制比创建工厂模式灵活,我们可以根据已有的信息来反射创建对象。
最后总结一下C#读取Excel的两种方法(实际上不止两种方法,但是我个人只用了以下两种方法):
(1)采用OleDB读取EXCEL文件: (读取Excel2007文件格式.xlsx, 下面Path代表文件路径)
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;";
strConnection += @"Data Source=" + Path;
strConnection += ";Extended Properties=\"Excel 12.0 Xml;HDR=NO;IMEX=3\";";
OleDbConnection conn = new OleDbConnection(strConnection);
conn.Open();
table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
(2)NPOI方式读取Excel
IWorkbook workbook = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
string s;
if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}