/// <summary>
/// 获取表单数据
/// </summary>
/// <param name="sheetName">表单名</param>
/// <param name="excel">文档对象</param>
/// <returns>表单数据</returns>
public static DataTable GetSheetTable(String sheetName, MoqikakaExcel excel)
{
//优先读取缓存数据
if (mAllTables.Tables.Contains(sheetName))
return mAllTables.Tables[sheetName];
//没用的表单直接返回
if (ExcelBLL.IsUselessSheet(sheetName))
return null;
//读取表单
var table = ExcelBLL.TryRead(excel, sheetName);
//加入缓存
if (table != null)
mAllTables.Tables.Add(table);
return table;
}
/// <summary>
/// 加载Excel对象
/// </summary>
/// <param name="path">excel文档</param>
/// <returns>Excel对象</returns>
public static MoqikakaExcel LoadExcel(String path)
{
//优先读取缓存
if (mExcels.ContainsKey(path))
{
MoqikakaExcel cache = mExcels[path];
//文档没有修改过
if (cache.ModifyDate == File.GetLastWriteTime(path))
return mExcels[path];
//清理表表数据缓存
foreach (var sheetName in cache.SheetNameList)
{
if (mAllTables.Tables.Contains(sheetName))
mAllTables.Tables.Remove(sheetName);
}
}
//重新加载
MoqikakaExcel excel = new MoqikakaExcel(path);
//缓存已读Excel文档对象 (并发插入异常?)
lock (lockObj)
mExcels[path] = excel;
return excel;
}
/// <summary>
/// 读取表单数据
/// </summary>
/// <param name="excel">文档对象</param>
/// <param name="sheetName">表单名称</param>
/// <returns>表单数据</returns>
public static DataTable TryRead(MoqikakaExcel excel, String sheetName)
{
return TryRead(excel, excel.GetSheetIndex(sheetName));
}
/// <summary>
/// 读取表单数据
/// </summary>
/// <param name="excel">文档对象</param>
/// <param name="sheetIndex">表单序号</param>
/// <returns>表单数据</returns>
public static DataTable TryRead(MoqikakaExcel excel, Int32 sheetIndex)
{
DataTable table = null;
try
{
//读取表单数据
table = excel.ReadAt(sheetIndex);
}
catch (Exception ex)
{
//记录异常日志信息
StringBuilder sb = new StringBuilder();
sb.AppendLine("【表单读取异常】");
sb.AppendLine("异常文件: " + excel.Path);
sb.AppendLine("异常表单: " + excel.GetSheetName(sheetIndex));
sb.AppendLine("异常信息: " + ex.Message);
sb.AppendLine("StackTrace: " + ex.StackTrace);
Trace.Write(sb.ToString());
}
return table;
}
/// <summary>
/// 导入excel单个表单
/// </summary>
/// <param name="excel">MoqikakaExcel对象</param>
/// <param name="sheetIndex">导入表单序号</param>
/// <param name="dbTables">数据库表名集合</param>
private static void ImportSheet(MoqikakaExcel excel, Int32 sheetIndex, List<String> dbTables)
{
String sheetName = excel.GetSheetName(sheetIndex);
//排除没用的表单/已忽略表单/数据库不存在的表单
if (ExcelBLL.IsUselessSheet(sheetName)
|| IgnoreSheetsBLL.IsIgnoreSheet(sheetName)
|| !dbTables.Contains(sheetName.ToLower()))
return;
//读取表单数据
DataTable table = ExcelBLL.TryRead(excel, sheetIndex);
if (table == null)
{
resultInfo[sheetName] = 0;
return;
}
try
{
List<String> sqlList = ExcelBLL.GetSQL(table);
Int32 rows = ExcelBLL.Insert(sqlList, table.TableName, excel.Path, false);
resultInfo[table.TableName] = rows;
}
catch (Exception ex)
{
resultInfo[sheetName] = 0;
Trace.Write("错误表单: " + sheetName + "\r\n" + "错误信息: " + ex.Message + "\r\n" + ex.StackTrace);
}
}
/// <summary>
/// 导入单个excel文件
/// </summary>
/// <param name="path">excel文档路径</param>
/// <param name="dbTables">数据库表名集合</param>
private static void ImportExcel(String path, List<String> dbTables)
{
MoqikakaExcel excel = new MoqikakaExcel(path);
//遍历导入Excel每个表单
for (int i = 0; i < excel.NumberOfSheets; i++)
{
//导入单个表单
ImportSheet(excel, i, dbTables);
}
}
/// <summary>
/// 根据Excel名,构造表单的节点数组
/// </summary>
/// <param name="excelFilePath">Excel文件路径</param>
/// <returns></returns>
private TreeNode[] GetSheetNodesByExcelFile(MoqikakaExcel excel)
{
//获取Excel所有表单列表
List<String> sheetList = excel.SheetNameList;
//存放表单节点的数组
TreeNode[] nodeArray = new TreeNode[sheetList.Count];
//根据每个Excel的表单名,创建对应的节点
for (Int32 i = 0; i < sheetList.Count; i++)
{
TreeNode node = new TreeNode(sheetList[i])
{
Checked = true,
ContextMenuStrip = cmsSheetNode,
ToolTipText = ConstantText.ClickRightToCopy
};
//对已存在映射关系的节点
if (mMappingedSheetList.Contains(sheetList[i].ToUpper()))
{
node.ForeColor = Color.Red;
node.ToolTipText = ConstantText.MappingExist;
}
if (!AllDbTableNames.Contains(sheetList[i].ToLower()))
{
node.ForeColor = Color.Gray;
node.ToolTipText = ConstantText.TableNotExist;
}
//添加到对应节点
nodeArray[i] = node;
}
return nodeArray;
}
/// <summary>
/// 为每个TabPage附加数据
/// </summary>
/// <param name="excel">Excel对象</param>
private void AppendDataToTabPages(MoqikakaExcel excel)
{
DataTable table = null;
String sheetName;
//循环读取每个Excel表单的数据
for (Int32 i = 0; i < excel.NumberOfSheets; i++)
{
sheetName = excel.GetSheetName(i);
table = GlobalCacheBLL.GetSheetTable(sheetName, excel);
//通知主线程控件,数据已准备好
tabControlSheetInfo.Invoke(new MethodInvoker(() =>
{
if (tabControlSheetInfo.TabPages.Count <= i) return;
tabControlSheetInfo.TabPages[i].Controls.Add(CreatSingleGridview(table));
//展示第一个表单信息
if (i == 0) ShowSheetInfo(sheetName, table);
}));
}
}