做高校平台时遇到这么一个功能,导入树形结构的资源,以免大批的资源一个一个添加。
我们这里指的资源就是每一个系统,以及系统下的模块、页面和按钮。这些资源是树形结构的。可以先给大家看一个图:
我们可以给用户提供Excel模板,让用户导入必要的资源,然后导入到数据库中。界面如下:
我主要说明一下批量导入实现思路:在XML中配置导入表的信息;获取上传Excel文件,转为DataSet;与XML中的定义的信息对比DataSet中的数据是否错误,然后进行必要主键外键设置;存入到数据库中
一、在XML中配置导入表的信息。这里没有什么好说的。我直接给出我配置的XML文件
<?xml version="1.0" encoding="utf-8" ?>
<Excel name="资源应用">
<Sheet name="资源" table="Resource">
<Column name="所属名称" field="DelCol" isTree="true">
<Format>string</Format>
</Column>
<Column name="名称" field="ResourceName" isNecessary="true" >
<Format>string</Format>
</Column>
<Column name="URL" field="Url" >
<Format>string</Format>
</Column>
<Column name="是否打开" field="POpen" >
<Format>short</Format>
</Column>
<Column name="图标" field="Icon" >
<Format>string</Format>
</Column>
<Column name="样式" field="Css">
<Format>string</Format>
</Column>
<Column name="JS代码" field="JavaScript">
<Format>string</Format>
</Column>
<Column name="类型" field="Type" isNecessary="true">
<Format>string</Format>
</Column>
<Column name="是否使用" field="IsUse">
<Format>short</Format>
</Column>
<Column name="说明" field="Description">
<Format>string</Format>
</Column>
<Column name="是否复选" field="Checked">
<Format>short</Format>
</Column>
<Column name="优先级" field="SeqNo">
<Format>string</Format>
</Column>
</Sheet>
</Excel>
其中Column name指Excel中的表头信息,field指存入数据库中的列名。isNecessary是标志该列是否可以为空,isTree标志该列是否要进行自身关联,形成树形结构。
二、获取上传的文件
1.判断文件是否为空,判断文件是否是xls格式的,判断文件是否超过4M
2.在服务器上设置某一个路径来存放文件
3.将excel中的数据转换为dataSet中(使用到excelHelper NPOI)
a创建文件流打开文件,实例HSSFWORK对象操作excel
b最外层的是sheet的循环
针对每一个sheet:第一行是表头存入DataColumn
剩余数据一一存入表中(二维表)
三、进行数据验证
1.获取配置文件
2.对比是否缺少列,若不缺少直接转换DataSet中的表的字段为英文状态与最终数据库对应
3.判断是否有重复数据
4.如果为外键属性则需要查询出关联的另一张表中的字段名称。
5.生成一列GUID,作为主键列
6..添加字段所属名称,最后要转换为PID这一列
四、存储到数据库中
这里采用批量插入数据库的方法。
实现代码:
Controller层代码:
public ActionResult Import(HttpPostedFileBase files)
{
#region 文件验证以及上传到指定文件夹
HttpPostedFileBase file = Request.Files["files"];
string strFileName;
string strSavePath;
if (file == null || file.ContentLength <= 0)
{
ViewBag.error = "文件不能为空";
return View("ResourceManager");
}
else
{
string strFilename = Path.GetFileName(file.FileName);
int intFilesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = System.IO.Path.GetExtension(strFilename);//获取上传文件的扩展名
string strNoFileName = System.IO.Path.GetFileNameWithoutExtension(strFilename);//获取无扩展名的文件名
int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
strFileName = strNoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
if (!FileType.Contains(fileEx))
{
ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件";
//return View();
}
if (intFilesize >= Maxsize)
{
ViewBag.error = "上传文件超过4M,不能上传";
//return View();
}
string path = AppDomain.CurrentDomain.BaseDirectory + "Content/uploads/";
strSavePath = Path.Combine(path, strFileName);
file.SaveAs(strSavePath);
}
#endregion
#region 实现导入
//调用方法
bool flag = iResourceBack.Import(strSavePath, strPathXML);
if (flag)
{
ViewBag.error = "导入成功";
}
return View("ResourceManager");
#endregion
}
WCF层代码:
public bool Import(string fileName, string path)
{
//调用B层接口
IAuthorityImportFacade iAuthorityImport = BLLAbstractFactory.GetAuthorityImportFacade();
return iAuthorityImport.Import(fileName, path);
}
B层代码:
public class AuthorityImportFacade : IAuthorityImportFacade
{
public bool Import(string fileName,string path)
{
ExcelHelper excelHelper = new ExcelHelper(fileName);
DataSet ds = excelHelper.ExcelToDataSet(true);
ds = ConfigOperator.QueryImportEntityProperty<AuthorityResourceEntity>(ds,path);
return ResourceImport.InsertTable(ds.Tables[0], "TA_Resource", ds.Tables[0].Columns);
}
}
ExcelHelper中的ExcelToDataSet方法的代码如下:
/// <summary>
/// 导入实际应用方法(可应对多个Sheet的情况)
/// </summary>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns></returns>
public DataSet ExcelToDataSet(bool isFirstRowColumn)
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//if (fileName.IndexOf(".xls") > 0) // 2003版本
// workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(fs);
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
}
DataSet dsExcel = new DataSet();
for (int intSheetIndex = 0; intSheetIndex < workbook.NumberOfSheets; intSheetIndex++)
{
ISheet sheet = workbook.GetSheetAt(intSheetIndex);
DataTable data = new DataTable();
int startRow = 0;
try
{
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
//第一行如果是DataTable的列名,则将列名读取到DataTable中
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
data.Columns.Add(column);
}
//DataTable赋值的起始数据行
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
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)
{
if (row.GetCell(j) != null) //同理,没有数据的单元格都默认是null
dataRow[j] = row.GetCell(j).ToString();
}
data.Rows.Add(dataRow);
}
//向DataSet中插入以Sheet为单位的DataTable
dsExcel.Tables.Add(data);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return null;
}
}
return dsExcel;
}
ConfigOperator中的QueryImportEntity方法的代码如下:
public static DataSet QueryImportEntityProperty<T>(DataSet dsExcel,string path) where T : new()
{
//Dictionary<string, string> dicForeignInfo = this.QueryForeignInfoEvent();
//实例化所对应的实体对象
T t = new T();
//得到实体类的名称
String strEntityName = t.GetType().Name;
//加载对应的XML配置文件
XmlDocument doc = new XmlDocument();
//string strPath = AppDomain.CurrentDomain.BaseDirectory + "Common/ExcelImportConfig/" + strEntityName + ".xml";
doc.Load(path); //加载Xml文件
XmlElement rootElem = doc.DocumentElement; //获取根节点
//根据对应的XML配置文件下的Sheet数目进行遍历Excel
for (int intSheetIndex = 0; intSheetIndex < rootElem.ChildNodes.Count; intSheetIndex++)
{
DataTable dtExcel = dsExcel.Tables[intSheetIndex];
DataTable dtError = dtExcel.Clone();
//TODO:根据SheetIndex的值获取对应的子节点(可能会出现与Excel中Sheet不匹配的情况)
XmlNode xnSheet = rootElem.SelectSingleNode("/Excel").ChildNodes[intSheetIndex];
XmlNodeList xnlFields = xnSheet.ChildNodes;
//遍历配置文件中对应的Excel列节点
for (int intExcelFieldIndex = 0; intExcelFieldIndex < xnlFields.Count; intExcelFieldIndex++)
{
XmlElement element = (XmlElement)xnlFields[intExcelFieldIndex];
#region 判断Excel的对应Sheet中是否包含对应配置文件中的列(以XML配置为准)
if (dtExcel.Columns.Contains(element.Attributes["name"].Value))
{
String strFieldName = element.Attributes["field"].Value;
dsExcel.Tables[intSheetIndex].Columns[element.Attributes["name"].Value].ColumnName = strFieldName;
}
else if (element.Attributes["isNecessary"] != null && element.Attributes["isNecessary"].Value == "true")
{
throw new Exception("导入Excel文件第" + (intSheetIndex + 1) + "个Sheet缺少必要列:" + element.Attributes["name"].Value + "!");
}
#endregion
#region 判断Excel中是否有重复的记录(根据XML配置字段)
XmlAttribute xaVerify = element.Attributes["isVerifyRepeat"];
if (xaVerify != null && xaVerify.Value == "true")
{
DataView dvExcel = new DataView(dtExcel);
if (dvExcel.Count != dvExcel.ToTable(true, element.Attributes["field"].Value).Rows.Count)
{
throw new Exception("导入文件中有重复的数据" + element.Attributes["name"].Value + "!");
}
}
#endregion
}
#region 自动生成一列主键
dtExcel.Columns.Add("ResourceId", typeof(System.Data.SqlTypes.SqlGuid));
dtExcel.Columns.Add("PId", typeof(System.Data.SqlTypes.SqlGuid));
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
dtExcel.Rows[i]["ResourceId"] = (SqlGuid)System.Guid.NewGuid();
}
#endregion
#region 判断导入的是否是树形结构的资源
for (int intExcelFieldIndex = 0; intExcelFieldIndex < xnlFields.Count; intExcelFieldIndex++)
{
XmlElement element = (XmlElement)xnlFields[intExcelFieldIndex];
XmlAttribute xaTree = element.Attributes["isTree"];
if (xaTree != null && xaTree.Value == "true")
{
SetTreeId(dtExcel);
}
}
#endregion
#region del col
dtExcel.Columns.Remove("DelCol");
#endregion
}
return dsExcel;
}
ResourceImport中的InsertTable方法的代码如下:
/// <summary>批量导入DataTable
/// 批量导入DataTable
/// </summary>
/// <param name="dt">DataTable数据表</param>
/// <param name="tableName">表名</param>
/// <param name="dtColum">数据列集合</param>
/// <return>Boolean值:true成功,false失败</return>
public static Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)
{
using (TransactionScope scope1 = new TransactionScope(TransactionScopeOption.Required))
{
using (SqlBulkCopy sqlBC = new SqlBulkCopy(
CreateConnection().ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
sqlBC.BatchSize = 1000;
sqlBC.DestinationTableName = tableName;
// Write from the source to the destination.
// This should fail with a duplicate key error.
for (int i = 0; i < dtColum.Count; i++)
{
sqlBC.ColumnMappings.Add(dtColum[i].ColumnName, dtColum[i].ColumnName);
}
try
{
//批量写入
sqlBC.WriteToServer(dt);
scope1.Complete();
return true;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
总结:目前该功能封装得还是不够,单表的没有外键关联的导入可以,对于复杂的多表有外键关联的这种情况还未考虑在内。1.0的权限系统就这样了。2.0的时候希望可以将各种情况都封装在内,直接使用就好了。