467 lines
24 KiB
C#
467 lines
24 KiB
C#
using LinqToExcel; // 使用 LinqToExcel 讀 excel
|
||
using Microsoft.Ajax.Utilities;
|
||
using NLog;
|
||
using Qcarbon.ViewModels.DTO;
|
||
using System;
|
||
using System.Collections.Generic;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Text;
|
||
using System.Threading;
|
||
using System.Web;
|
||
using Weee.DAL;
|
||
using Weee.Models;
|
||
using Weee.Models.Paramemter;
|
||
|
||
namespace Weee.Areas.Admin.Supports
|
||
{
|
||
public class SimaproExcelImporter
|
||
{
|
||
protected Logger log;
|
||
private ExcelQueryFactory excelFile;
|
||
private string FName;
|
||
|
||
public SimaproExcelImporter(string fileName)
|
||
{
|
||
log = NLog.LogManager.GetCurrentClassLogger();
|
||
log.Info("SimaproExcelImporter 1");
|
||
excelFile = new ExcelQueryFactory(fileName);
|
||
FName = fileName;//DL-6
|
||
log.Info("SimaproExcelImporter 2");
|
||
}
|
||
|
||
public void DeleteExcelFile(string fileName)
|
||
{
|
||
File.Delete(fileName);
|
||
}
|
||
|
||
public void InsertSimaproData(string version, string description = "", string parasource = "")
|
||
{
|
||
log.Info("InsertSimaproData 1");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Encoding, "編碼");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Value, "kg CO2 eq");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Unit, "Unit");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Remark, "Remark");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Remark, "Remarks");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Database, "Database");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Database, "database");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Project, "Project");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Description, "Tool");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.typeTW, "類型");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.typeEN, "Cat");
|
||
|
||
log.Info("InsertSimaproData 2");
|
||
var simaproVersion = new SimaproVersion();
|
||
simaproVersion.Version = version;
|
||
simaproVersion.Description = description;
|
||
simaproVersion.paraSource = parasource;
|
||
|
||
int i = 0;
|
||
System.Diagnostics.Debug.WriteLine("start " + DateTime.Now);
|
||
using (var db = new WeeeDataContext())
|
||
{
|
||
simaproVersion = db.SimaproVersions.Add(simaproVersion);
|
||
db.SaveChanges();
|
||
|
||
log.Info("InsertSimaproData 3");
|
||
var sheetNames = excelFile.GetWorksheetNames();
|
||
log.Info("InsertSimaproData 4");
|
||
|
||
foreach (var sheetName in sheetNames)
|
||
{
|
||
System.Diagnostics.Debug.WriteLine("start row: " + " " + DateTime.Now);
|
||
|
||
log.Info("InsertSimaproData 5");
|
||
var rows = from c in excelFile.Worksheet(sheetName) select c;
|
||
log.Info("InsertSimaproData 6");
|
||
System.Diagnostics.Debug.WriteLine("end row: " + " " + DateTime.Now);
|
||
if (rows.Count() > 0)
|
||
{
|
||
var simaproParameterCategory = new SimaproParameterCategory();
|
||
|
||
simaproParameterCategory.DisplayNameTW = sheetName;
|
||
simaproParameterCategory.DisplayNameCN = sheetName;
|
||
simaproParameterCategory.DisplayNameEN = sheetName;
|
||
simaproParameterCategory.VersionID = simaproVersion.ID;
|
||
simaproParameterCategory = db.SimaproCategories.Add(simaproParameterCategory);
|
||
db.SaveChanges();
|
||
|
||
i++;
|
||
System.Diagnostics.Debug.WriteLine("start SimaproParameterType: " + i + " " + DateTime.Now);
|
||
|
||
/*
|
||
* simaproParameterTypes
|
||
*/
|
||
Dictionary<string, int> simTypeEN = new Dictionary<string, int>();
|
||
Dictionary<string, int> simTypeTW = new Dictionary<string, int>();
|
||
|
||
log.Info("InsertSimaproData 7");
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameTW, "類型");
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameCN, "类型");
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameEN, "Cat");
|
||
var excelContent = excelFile.Worksheet<SimaproParameterType>(sheetName);
|
||
|
||
log.Info("InsertSimaproData 8");
|
||
Dictionary<String, SimaproParameterType> listSimaproParameterType = new Dictionary<String, SimaproParameterType>();
|
||
foreach (var row in excelContent)
|
||
{
|
||
|
||
string nameEN = "";
|
||
if (row.DisplayNameEN != null)
|
||
nameEN = row.DisplayNameEN;
|
||
string nameCN = "";
|
||
if (row.DisplayNameCN != null)
|
||
nameCN = row.DisplayNameCN;
|
||
string nameDefault = "";
|
||
if (row.DisplayNameTW != null)
|
||
nameDefault = row.DisplayNameTW;
|
||
else
|
||
nameDefault = nameEN;
|
||
|
||
var simaproParameterType = new SimaproParameterType();
|
||
simaproParameterType.DisplayNameTW = nameDefault;
|
||
simaproParameterType.DisplayNameCN = nameCN;
|
||
simaproParameterType.DisplayNameEN = nameEN;
|
||
simaproParameterType.CategoryID = simaproParameterCategory.ID;
|
||
if (!string.IsNullOrWhiteSpace(nameDefault) && !listSimaproParameterType.ContainsKey(nameDefault))
|
||
{
|
||
listSimaproParameterType.Add(nameDefault, simaproParameterType);
|
||
}
|
||
}
|
||
System.Diagnostics.Debug.WriteLine("end SimaproParameterType: " + i + " " + DateTime.Now);
|
||
db.SimaproTypes.AddRange(listSimaproParameterType.Values);
|
||
db.SaveChanges();
|
||
foreach (SimaproParameterType simaproParameterType in listSimaproParameterType.Values)
|
||
{
|
||
if (!string.IsNullOrWhiteSpace(simaproParameterType.DisplayNameEN) && !simTypeEN.ContainsKey(simaproParameterType.DisplayNameEN))
|
||
{
|
||
simTypeEN.Add(simaproParameterType.DisplayNameEN, simaproParameterType.ID);
|
||
}
|
||
if (!string.IsNullOrWhiteSpace(simaproParameterType.DisplayNameTW) && !simTypeTW.ContainsKey(simaproParameterType.DisplayNameTW))
|
||
{
|
||
simTypeTW.Add(simaproParameterType.DisplayNameTW, simaproParameterType.ID);
|
||
}
|
||
|
||
}
|
||
System.Diagnostics.Debug.WriteLine("end add SimaproParameterType: " + i + " " + DateTime.Now);
|
||
|
||
System.Diagnostics.Debug.WriteLine("start simaproParameters: " + i + " " + DateTime.Now);
|
||
/*
|
||
* simaproParameters
|
||
*/
|
||
var emptySimaproType = new SimaproParameterType();
|
||
emptySimaproType.DisplayNameTW = "(全部)";
|
||
emptySimaproType.DisplayNameCN = "(全部)";
|
||
emptySimaproType.DisplayNameEN = "(all)";
|
||
emptySimaproType.CategoryID = simaproParameterCategory.ID;
|
||
|
||
log.Info("InsertSimaproData 9");
|
||
List<SimaproParameter> listSimaproParameterImport = new List<SimaproParameter>();
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameTW, "中文");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameCN, "簡體中文");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameEN, "Simapro name");
|
||
var excelContent2 = excelFile.Worksheet<SimaproParameterImport>(sheetName);
|
||
|
||
log.Info("InsertSimaproData 10");
|
||
foreach (var row in excelContent2)
|
||
{
|
||
var simaproParameter = new SimaproParameter();
|
||
|
||
simaproParameter.DisplayNameTW = row.DisplayNameTW != null ? row.DisplayNameTW : row.DisplayNameEN;
|
||
if (string.IsNullOrWhiteSpace(simaproParameter.DisplayNameTW))
|
||
{
|
||
Thread.Sleep(0);
|
||
continue;
|
||
}
|
||
simaproParameter.DisplayNameCN = row.DisplayNameCN;
|
||
simaproParameter.DisplayNameEN = row.DisplayNameEN;
|
||
simaproParameter.Encoding = row.Encoding;
|
||
simaproParameter.Value = row.Value;
|
||
simaproParameter.Unit = row.Unit;
|
||
simaproParameter.Remark = row.Remark;
|
||
simaproParameter.Database = row.Database;
|
||
simaproParameter.Project = row.Project;
|
||
simaproParameter.Description = row.Description;
|
||
|
||
if (!string.IsNullOrWhiteSpace(row.typeEN) && simTypeEN.ContainsKey(row.typeEN))
|
||
{
|
||
simaproParameter.TypeID = simTypeEN[row.typeEN];
|
||
listSimaproParameterImport.Add(simaproParameter);
|
||
}
|
||
else if (!string.IsNullOrWhiteSpace(row.typeTW) && simTypeTW.ContainsKey(row.typeTW))
|
||
{
|
||
simaproParameter.TypeID = simTypeTW[row.typeTW];
|
||
listSimaproParameterImport.Add(simaproParameter);
|
||
}
|
||
else
|
||
{
|
||
SimaproParameter paraRec;
|
||
string json = CScommon.JsonUtl.fromT(simaproParameter);
|
||
paraRec = CScommon.JsonUtl.toT<SimaproParameter>(json);
|
||
emptySimaproType.Parameters.Add(paraRec);
|
||
}
|
||
if (listSimaproParameterImport.Count >= 1000)
|
||
{
|
||
db.SimaproParameters.AddRange(listSimaproParameterImport);
|
||
db.SaveChanges();
|
||
listSimaproParameterImport.Clear();
|
||
}
|
||
}
|
||
System.Diagnostics.Debug.WriteLine("end simaproParameters: " + i + " " + DateTime.Now);
|
||
if (listSimaproParameterImport.Count > 0)
|
||
{
|
||
db.SimaproParameters.AddRange(listSimaproParameterImport);
|
||
db.SaveChanges();
|
||
}
|
||
System.Diagnostics.Debug.WriteLine("end add simaproParameters: " + i + " " + DateTime.Now);
|
||
|
||
if (emptySimaproType.Parameters.Count > 0)
|
||
{
|
||
db.SimaproTypes.Add(emptySimaproType);
|
||
db.SaveChanges();
|
||
}
|
||
System.Diagnostics.Debug.WriteLine("end saving: " + i + " " + DateTime.Now);
|
||
}
|
||
}
|
||
|
||
System.Diagnostics.Debug.WriteLine("end " + DateTime.Now);
|
||
}
|
||
Thread.Sleep(0);
|
||
log.Info("InsertSimaproData 11 end");
|
||
}
|
||
|
||
public void OLDInsertSimaproData(string version, string description = "", string parasource = "")
|
||
{
|
||
System.Diagnostics.Debug.WriteLine("start " + DateTime.Now);
|
||
var simaproVersion = new SimaproVersion();
|
||
simaproVersion.Version = version;
|
||
simaproVersion.Description = description;
|
||
simaproVersion.paraSource = parasource;
|
||
|
||
int i = 0;
|
||
var simaproParameterCategories = this.MappingExcelColumnToSimaproParameterCategory();
|
||
using (var db = new WeeeDataContext())
|
||
{
|
||
foreach (var simaproParameterCategory in simaproParameterCategories)
|
||
{
|
||
i++;
|
||
var sheetName = simaproParameterCategory.DisplayNameEN;
|
||
var simaproParameterTypes = this.MappingExcelColumnToSimaproParameterType(sheetName);
|
||
var simaproParameters = this.MappingExcelColumnToSimaproParameter(sheetName);
|
||
|
||
var emptySimaproType=new SimaproParameterType();
|
||
emptySimaproType.DisplayNameTW = "(全部)";
|
||
emptySimaproType.DisplayNameCN = "(全部)";
|
||
emptySimaproType.DisplayNameEN = "(all)";
|
||
//simaproParameterTypes = this.SimaproTypeNormalization(simaproParameterTypes);
|
||
Dictionary<string, SimaproParameterType> simTypeEN = new Dictionary<string, SimaproParameterType>();
|
||
Dictionary<string, SimaproParameterType> simTypeTW = new Dictionary<string, SimaproParameterType>();
|
||
int j = 0;
|
||
foreach (var rec in simaproParameterTypes)
|
||
{
|
||
if (!string.IsNullOrWhiteSpace(rec.Value.DisplayNameEN))
|
||
simTypeEN.Add(rec.Value.DisplayNameEN, rec.Value);
|
||
if (!string.IsNullOrWhiteSpace(rec.Value.DisplayNameTW))
|
||
simTypeTW.Add(rec.Value.DisplayNameTW, rec.Value);
|
||
j++;
|
||
}
|
||
j = 0;
|
||
foreach (var simaproParameter in simaproParameters)
|
||
{
|
||
SimaproParameter paraRec;
|
||
string json = CScommon.JsonUtl.fromT(simaproParameter);
|
||
paraRec = CScommon.JsonUtl.toT<SimaproParameter>(json);
|
||
if (!string.IsNullOrWhiteSpace(simaproParameter.typeEN) &&
|
||
simTypeEN.ContainsKey(simaproParameter.typeEN))
|
||
simTypeEN[simaproParameter.typeEN].Parameters.Add(paraRec);
|
||
else if (!string.IsNullOrWhiteSpace(simaproParameter.typeTW) &&
|
||
simTypeTW.ContainsKey(simaproParameter.typeTW))
|
||
simTypeTW[simaproParameter.typeTW].Parameters.Add(paraRec);
|
||
//if (simaproParameter.typeEN == simaproParameterType.DisplayNameEN ||
|
||
// simaproParameter.typeTW == simaproParameterType.DisplayNameTW)
|
||
//{
|
||
// simaproParameterType.Parameters.Add(paraRec);
|
||
//}
|
||
else
|
||
emptySimaproType.Parameters.Add(paraRec);
|
||
j++;
|
||
}
|
||
if (emptySimaproType.Parameters.Count>0)
|
||
simaproParameterTypes.Add(emptySimaproType.DisplayNameTW, emptySimaproType);
|
||
j = 0;
|
||
foreach (var simaproParameterType in simaproParameterTypes)
|
||
{
|
||
simaproParameterCategory.SubTypes.Add(simaproParameterType.Value);
|
||
j++;
|
||
}
|
||
simaproVersion.Categories.Add(simaproParameterCategory);
|
||
}
|
||
db.SimaproVersions.Add(simaproVersion);
|
||
db.SaveChanges();
|
||
}
|
||
Thread.Sleep(0);
|
||
System.Diagnostics.Debug.WriteLine("end " + DateTime.Now);
|
||
}
|
||
|
||
private List<SimaproParameterCategory> MappingExcelColumnToSimaproParameterCategory()
|
||
{
|
||
var simaproParameterCategories = new List<SimaproParameterCategory>();
|
||
var sheetNames = excelFile.GetWorksheetNames();
|
||
|
||
foreach (var sheetName in sheetNames)
|
||
{
|
||
var rows = from c in excelFile.Worksheet(sheetName) select c;
|
||
if (rows.Count() > 0)
|
||
{
|
||
//var cols = excelFile.GetColumnNames(sheetName);
|
||
//if (cols.Count() >= 7 || ! FName.Contains("xlsx"))
|
||
{
|
||
var simaproParameterCategory = new SimaproParameterCategory();
|
||
|
||
simaproParameterCategory.DisplayNameTW = sheetName;
|
||
simaproParameterCategory.DisplayNameCN = sheetName;
|
||
simaproParameterCategory.DisplayNameEN = sheetName;
|
||
|
||
simaproParameterCategories.Add(simaproParameterCategory);
|
||
}
|
||
}
|
||
}
|
||
return simaproParameterCategories;
|
||
}
|
||
|
||
private Dictionary<string, SimaproParameterType> MappingExcelColumnToSimaproParameterType(string sheetName)
|
||
{
|
||
var simaproParameterTypes = new Dictionary<string, SimaproParameterType>();
|
||
|
||
//excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameTW, "中文");
|
||
//excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameCN, "簡體中文");
|
||
//excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameEN, "Simapro name");
|
||
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameTW, "類型");
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameCN, "类型");
|
||
excelFile.AddMapping<SimaproParameterType>(x => x.DisplayNameEN, "Cat");
|
||
|
||
var excelContent = excelFile.Worksheet<SimaproParameterType>(sheetName);
|
||
int counts = excelContent.ToList().Count;
|
||
int i = 0;
|
||
foreach (var row in excelContent)
|
||
{
|
||
i++;
|
||
var simaproParameterType = new SimaproParameterType();
|
||
|
||
string nameEN = "";
|
||
if (row.DisplayNameEN != null)
|
||
nameEN = row.DisplayNameEN;//.Split(',')[0];
|
||
//else
|
||
// continue;
|
||
string nameCN = "";
|
||
if (row.DisplayNameCN != null)
|
||
nameCN = row.DisplayNameCN;//.Split('，')[0];
|
||
|
||
string nameDefault = "";
|
||
if (row.DisplayNameTW != null)
|
||
nameDefault = row.DisplayNameTW;//.Split('，')[0];
|
||
else
|
||
nameDefault = nameEN;
|
||
simaproParameterType.DisplayNameTW = nameDefault;
|
||
//if (string.IsNullOrWhiteSpace(nameDefault))
|
||
//{
|
||
// continue;
|
||
// Thread.Sleep(0);
|
||
//}
|
||
simaproParameterType.DisplayNameCN = nameCN;
|
||
simaproParameterType.DisplayNameEN = nameEN;
|
||
if (!string.IsNullOrWhiteSpace(nameDefault) &&
|
||
!simaproParameterTypes.ContainsKey(nameDefault))
|
||
simaproParameterTypes.Add(nameDefault, simaproParameterType);
|
||
}
|
||
return simaproParameterTypes;
|
||
}
|
||
|
||
private List<SimaproParameterImport> MappingExcelColumnToSimaproParameter(string sheetName)
|
||
{
|
||
var simaproParameters = new List<SimaproParameterImport>();
|
||
try
|
||
{
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameTW, "中文");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameCN, "簡體中文");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.DisplayNameEN, "Simapro name");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Encoding, "編碼");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Value, "kg CO2 eq");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Unit, "Unit");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Remark, "Remark");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Remark, "Remarks");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Database, "Database");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Database, "database");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Project, "Project");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.Description, "Tool");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.typeTW, "類型");
|
||
excelFile.AddMapping<SimaproParameterImport>(x => x.typeEN, "Cat");
|
||
|
||
var excelContent = excelFile.Worksheet<SimaproParameterImport>(sheetName);
|
||
int rows = 0;
|
||
foreach (var row in excelContent)
|
||
{
|
||
var simaproParameter = new SimaproParameterImport();
|
||
|
||
//if (row.DisplayNameEN == null) { continue; }
|
||
|
||
simaproParameter.DisplayNameTW = row.DisplayNameTW != null ?
|
||
row.DisplayNameTW : row.DisplayNameEN;
|
||
if (string.IsNullOrWhiteSpace(simaproParameter.DisplayNameTW))
|
||
{
|
||
Thread.Sleep(0);
|
||
continue;
|
||
}
|
||
rows++;
|
||
simaproParameter.DisplayNameCN = row.DisplayNameCN;
|
||
simaproParameter.DisplayNameEN = row.DisplayNameEN;
|
||
simaproParameter.Encoding = row.Encoding;
|
||
simaproParameter.Value = row.Value;
|
||
simaproParameter.Unit = row.Unit;
|
||
simaproParameter.Remark = row.Remark;
|
||
simaproParameter.Database = row.Database;
|
||
simaproParameter.Project=row.Project;
|
||
simaproParameter.Description = row.Description;
|
||
simaproParameter.typeTW = row.typeTW;
|
||
simaproParameter.typeEN = row.typeEN;
|
||
|
||
simaproParameters.Add(simaproParameter);
|
||
}
|
||
}
|
||
catch(Exception ex)
|
||
{
|
||
throw new Exception($"sheet: {sheetName} failed({ex.Message})");
|
||
}
|
||
return simaproParameters;
|
||
}
|
||
|
||
private List<SimaproParameterType> SimaproTypeNormalization(List<SimaproParameterType> simaproParameterTypes)
|
||
{
|
||
var resultOfSimaproTypes = new List<SimaproParameterType>();
|
||
|
||
foreach (var simaproParameterType in simaproParameterTypes)
|
||
{
|
||
var IsExist = false;
|
||
foreach (var resultOfSimaproType in resultOfSimaproTypes)
|
||
{
|
||
if (!string.IsNullOrWhiteSpace(resultOfSimaproType.DisplayNameEN) &&
|
||
resultOfSimaproType.DisplayNameEN == simaproParameterType.DisplayNameEN)
|
||
{
|
||
IsExist = true;
|
||
break;
|
||
}
|
||
if (!string.IsNullOrWhiteSpace(resultOfSimaproType.DisplayNameTW) &&
|
||
resultOfSimaproType.DisplayNameTW == simaproParameterType.DisplayNameTW)
|
||
{
|
||
IsExist = true;
|
||
break;
|
||
}
|
||
}
|
||
if (!IsExist && !string.IsNullOrWhiteSpace(simaproParameterType.DisplayNameTW))
|
||
resultOfSimaproTypes.Add(simaproParameterType);
|
||
}
|
||
return resultOfSimaproTypes;
|
||
}
|
||
}
|
||
} |