private void FillFromExcelDocument(string fileName, XElement xDeclarationsHolder)
{
var etbUnit = new CurrencyUnit("ETB");
Instance.Units.Add(etbUnit);
var quarter1Of2018 = new DurationPeriod(new DateTime(2018, 1, 1), new DateTime(2018, 3, 31));
var startOfQuarter1Of2018 = new InstantPeriod(new DateTime(2017, 12, 31));
var endOfQuarter1Of2018 = new InstantPeriod(new DateTime(2018, 3, 31));
Instance.Periods.Add(quarter1Of2018);
Instance.Periods.Add(startOfQuarter1Of2018);
Instance.Periods.Add(endOfQuarter1Of2018);
var wsi = new Entity
{
Id = "wsi",
IdentifierScheme = new Uri("http://www.sec.gov/CIK"),
Identifier = "WSI",
Name = "Walia Steel Industry PLC"
};
Instance.Entities.Add(wsi);
var package = new ExcelPackage(new FileInfo(fileName));
var worksheet = package.Workbook.Worksheets[1];
ExcelRangeBase conceptCell = worksheet.Cells["B2"];
ExcelRangeBase valueCell = worksheet.Cells["C2"];
ExcelRangeBase startDateCell = worksheet.Cells["D2"];
ExcelRangeBase dateOrEndDateCell = worksheet.Cells["E2"];
ExcelRangeBase explicitMembersCell = worksheet.Cells["F2"];
while (conceptCell.Value != null && valueCell.Value != null)
{
var concept = conceptCell.GetValue <string>();
var value = valueCell.Value;
var period = startDateCell.Value == null || string.IsNullOrEmpty(startDateCell.Value.ToString()) ?
new InstantPeriod(dateOrEndDateCell.GetValue <DateTime>()) :
(Period) new DurationPeriod(startDateCell.GetValue <DateTime>(), dateOrEndDateCell.GetValue <DateTime>());
var conceptName = ToXName(concept, xDeclarationsHolder);
var fact = new Fact(conceptName, wsi, period)
{
Unit = etbUnit,
Value = value.ToString(),
BalanceMethod = BalanceMethod.Algebraic
};
AddExplicitMembers(fact, explicitMembersCell.Value, xDeclarationsHolder);
Instance.Facts.Add(fact);
conceptCell = conceptCell.Offset(1, 0);
valueCell = valueCell.Offset(1, 0);
startDateCell = startDateCell.Offset(1, 0);
dateOrEndDateCell = dateOrEndDateCell.Offset(1, 0);
explicitMembersCell = explicitMembersCell.Offset(1, 0);
}
}
public T Get()
{
try
{
T source;
if (function != null)
{
var func = function.Compile();
source = func(excelRange.Value);
}
else
{
source = excelRange.GetValue <T>();
}
return(source);
}
catch (Exception)
{
var message = $"{excelRange.Address} cannot convert value.";
if (errorMessage != null)
{
var func = errorMessage.Compile();
message = func(excelRange);
}
throw new ConverterException(message);
}
}
private void WorksheetToDataTable(ExcelWorksheet ws)
{
int totalCols = ws.Dimension.End.Column;
int totalRows = ws.Dimension.End.Row;
int startRow = 2;
ExcelRange wsRow;
ExcelRange wsRowNames;
wsRowNames = ws.Cells[1, 1, 1, totalCols];
for (int rowNum = startRow; rowNum <= totalRows; rowNum++)
{
wsRow = ws.Cells[rowNum, 1, rowNum, totalCols];
List <KeyValuePair <String, String> > oneTask = new List <KeyValuePair <String, String> >();
for (int i = 0; i < wsRow.Columns; i++)
{
String fieldName = String.Empty;
String fieldValue = String.Empty;
ExcelRangeBase fieldNameExcel = wsRowNames.ElementAtOrDefault(i);
ExcelRangeBase fielValueExcel = wsRow.ElementAtOrDefault(i);
if (fieldNameExcel != null)
{
fieldName = fieldNameExcel.GetValue <String>();
}
if (fielValueExcel != null)
{
fieldValue = fielValueExcel.GetValue <String>();
}
oneTask.Add(new KeyValuePair <String, String>(fieldName, fieldValue));
}
Write_list(oneTask);
}
CheckTasksForParents();
}
public static T TryGetValue <T>(this ExcelRangeBase excelRangeBase, string catalog, ILogger <ExcelReaderService> logger)
{
try
{
return(excelRangeBase.GetValue <T>());
}
catch (Exception)
{
logger.LogError($"Impossible de caster la case {excelRangeBase.Address} de {catalog} à {typeof(T)}");
return(default(T));
}
}
/// <summary>
/// Sets the property value for the <paramref name="dataInstance"/>.
/// This method also checks the validation actions, before and after casting the cell value,
/// if one of them aborts the execution, this method will return false and it will not set the
/// value for this property.
/// </summary>
/// <param name="dataInstance"></param>
/// <param name="cell"></param>
/// <returns></returns>
protected bool SetPropertyValue(TModel dataInstance, ExcelRangeBase cell)
{
// This instance should be created only if there is at least one callback function defined.
var context = (this.validateValue != null || this.validateCastedValue != null) ?
new PropertyExtractionContext(new CellAddress(cell))
:
null;
if (this.validateValue != null)
{
this.validateValue(context, cell.Value);
if (context.Aborted)
{
return(false);
}
}
TValue value;
if (cellValueConverter == null)
{
value = cell.GetValue <TValue>();
}
else
{
value = this.cellValueConverter(cell.Value);
}
if (this.validateCastedValue != null)
{
this.validateCastedValue(context, value);
#pragma warning disable S2259 // Since "validateCastedValue" is not null, there is no way for "context" to be ull.
if (context.Aborted)
{
return(false);
}
#pragma warning restore S2259
}
setPropertyValueAction(dataInstance, value);
return(true);
}
public ListInfoProvider()
{
using (var stream = Load.ScotlandsMountains.ListInfo)
using (var package = new ExcelPackage())
{
package.Load(stream);
ExcelRangeBase cell = package.Workbook.Worksheets.Single().Cells["A2"];
while (cell.Value != null)
{
_listInfo.Add(new ListInfo
{
Code = cell.GetValue <string>(),
Name = cell.Offset(0, 1).GetValue <string>(),
Order = cell.Offset(0, 2).GetValue <int>(),
Description = cell.Offset(0, 3).GetValue <string>(),
Enabled = cell.Offset(0, 4).GetValue <bool>()
});
cell = cell.Offset(1, 0);
}
}
}
public static string GetCellString(ExcelRangeBase cell)
{
var value = cell.GetValue <string>();
return(string.IsNullOrEmpty(value) ? string.Empty : value);
}
private Instance LoadInstanceFromExcelDocument(string fileName)
{
XNamespace bSharpNamespace = "http://banan-it.com/taxonomy/2018-07-05/bsharp";
XNamespace ifrsNamespace = "http://xbrl.ifrs.org/taxonomy/2017-03-09/ifrs-full";
var xDeclarationsHolder = new XElement("dummy",
new XAttribute(XNamespace.Xmlns + "ifrs-full", ifrsNamespace),
new XAttribute(XNamespace.Xmlns + "banan", bSharpNamespace));
var instance = new Instance();
var etbUnit = new CurrencyUnit("ETB");
instance.Units.Add(etbUnit);
var quarter1Of2018 = new DurationPeriod(new DateTime(2018, 1, 1), new DateTime(2018, 3, 31));
var startOfQuarter1Of2018 = new InstantPeriod(new DateTime(2017, 12, 31));
var endOfQuarter1Of2018 = new InstantPeriod(new DateTime(2018, 3, 31));
instance.Periods.Add(quarter1Of2018);
instance.Periods.Add(startOfQuarter1Of2018);
instance.Periods.Add(endOfQuarter1Of2018);
var wsi = new Entity
{
Id = "wsi",
IdentifierScheme = new Uri("http://www.sec.gov/CIK"),
Identifier = "WSI",
Name = "Walia Steel Industry PLC"
};
instance.Entities.Add(wsi);
var package = new ExcelPackage(new FileInfo(fileName));
var worksheet = package.Workbook.Worksheets[1];
ExcelRangeBase conceptCell = worksheet.Cells["B2"];
ExcelRangeBase valueCell = worksheet.Cells["C2"];
ExcelRangeBase startDateCell = worksheet.Cells["D2"];
ExcelRangeBase dateOrEndDateCell = worksheet.Cells["E2"];
ExcelRangeBase explicitMembersCell = worksheet.Cells["F2"];
while (conceptCell.Value != null && valueCell.Value != null)
{
var concept = conceptCell.GetValue <string>();
var value = valueCell.Value;
var period = startDateCell.Value == null || string.IsNullOrEmpty(startDateCell.Value.ToString()) ?
new InstantPeriod(dateOrEndDateCell.GetValue <DateTime>()) :
(Period) new DurationPeriod(startDateCell.GetValue <DateTime>(), dateOrEndDateCell.GetValue <DateTime>());
var conceptName = ToXName(concept, xDeclarationsHolder);
var fact = new Fact(conceptName, wsi, period)
{
Unit = etbUnit,
Value = value.ToString(),
BalanceMethod = BalanceMethod.Algebraic
};
AddExplicitMembers(fact, explicitMembersCell.Value, xDeclarationsHolder);
instance.Facts.Add(fact);
conceptCell = conceptCell.Offset(1, 0);
valueCell = valueCell.Offset(1, 0);
startDateCell = startDateCell.Offset(1, 0);
dateOrEndDateCell = dateOrEndDateCell.Offset(1, 0);
explicitMembersCell = explicitMembersCell.Offset(1, 0);
}
return(instance);
}
public static bool ExcelToLuaTableEPPlus(string filePath, string savePath)
{
FileInfo fileInfo = new FileInfo(filePath);
if (!fileInfo.Exists)
{
Debug.LogError(string.Format(FIND_NOT_PATH, filePath));
return(false);
}
FileStream file = null;
try
{
file = File.OpenRead(filePath);
}
catch (Exception e)
{
Debug.LogError("文件可能被占用,无法写入: " + Path.GetFileName(filePath));
}
if (file == null)
{
return(false);
}
ExcelPackage excelPackage = new ExcelPackage(fileInfo);
ExcelWorkbook workbook = excelPackage.Workbook;
Action clearFunc = () =>
{
workbook.Dispose();
excelPackage.Dispose();
file.Dispose();
};
if (workbook == null)
{
clearFunc.Invoke();
return(false);
}
if (workbook.Worksheets.Count <= 0)
{
Debug.LogError(string.Format(FIND_NOT_SHEET, filePath));
return(false);
}
for (int index = 1; index < workbook.Worksheets.Count; index++)
{
string strTable = string.Empty;
ExcelWorksheet sheet = workbook.Worksheets[index];
int maxColumnNum = sheet.Dimension.End.Column; //最大列
int minColumnNum = sheet.Dimension.Start.Column; //最小列
int maxRowNum = sheet.Dimension.End.Row; //最小行
int minRowNum = sheet.Dimension.Start.Row; //最大行
//tableName
string key_name = string.Empty;
try
{
ExcelRow row = sheet.Row(KEY_ROW + 1);
ExcelRangeBase cell = sheet.Cells[KEY_ROW + 1, 1];
key_name = cell.GetValue <string>();//cell.StringCellValue;
}
catch (Exception e)
{
continue;
}
if (key_name == null)
{
continue;
}
strTable = string.Format("LuaConfig_{0} = {{\n", key_name);
//type
Dictionary <int, string> dicTypes = new Dictionary <int, string>();
for (int i = 3; i < maxColumnNum; i++)
{
ExcelRangeBase cell = sheet.Cells[TYPE_ROW + 1, i];
if (cell == null)
{
continue;
}
string value = cell.GetValue <string>();
if (value == null)
{
break;
}
dicTypes[i] = value;
}
//key
Dictionary <int, string> dicKeys = new Dictionary <int, string>();
for (int i = 3; i < maxColumnNum; i++)
{
ExcelRangeBase cell = sheet.Cells[KEY_ROW + 1, i];
if (cell == null)
{
continue;
}
string value = cell.GetValue <string>();
if (value == null)
{
break;
}
dicKeys[i] = value;
}
//value
for (int i = 5; i < maxRowNum; i++)
{
string strOneRow = string.Empty;
for (int j = 3; j < maxColumnNum; j++)
{
string value = string.Empty;
string key;
if (!dicKeys.TryGetValue(j, out key))
{
continue;
}
string type;
if (!dicTypes.TryGetValue(j, out type))
{
continue;
}
string[] types = type.Split('|');
type = types[0];
string conditions = string.Empty;
if (types.Length > 1)
{
conditions = types[1];
}
if (key_name.Equals("art") && key.Equals("nav"))
{
string s = string.Empty;
}
Func <string, string> func;
if (!Type2Func.TryGetValue(type, out func))
{
func = Type2Func["str"];
}
ExcelRangeBase cell = sheet.Cells[i, j];
if (cell == null)
{
continue;
}
value = cell.GetValue <string>();
if (value == null)
{
if (key == "id")
{
break;
}
value = func.Invoke(string.Empty);
}
else
{
value = func.Invoke(value);
}
value = value.Replace("\r\n", "");
//处理条件
if (!string.IsNullOrEmpty(conditions))
{
if (conditions.Equals("half"))
{
value = value.Replace("\r\n", "");
value = value.Replace("\n", "");
}
}
//
if (j != 3)
{
strOneRow = strOneRow + ",";
}
if (key == "id")
{
strOneRow = string.Format("\t[{0}] = {{", value);
}
strOneRow = strOneRow + string.Format("{0}={1}", key, value);
}
if (!strOneRow.Equals(string.Empty))
{
strOneRow = strOneRow + "},\n";
strTable = strTable + strOneRow;
}
}
strTable = strTable + "}";
//write
FileInfo luaConfig = new FileInfo(savePath + "/LuaConfig_" + key_name + ".lua");
//Debug.Log(luaConfig.FullName);
if (luaConfig.Exists == false)
{
luaConfig.Create().Dispose();
}
try
{
File.WriteAllText(luaConfig.FullName, strTable, Encoding.UTF8);
}
catch (Exception e)
{
Debug.LogError(string.Format("文件可能被占用,无法写入！<color=yellow>{0}</color>,{1}", key_name, luaConfig.FullName));
Debug.Log(e.ToString());
clearFunc.Invoke();
return(false);
}
//Debug.Log(string.Format("finish: <color=yellow>{0}</color>,{1},{2}", key_name, sheet.SheetName, filePath));
//EditorUtility.DisplayProgressBar("ExcelToLuaTable", filePath, index + 1 / workbook.Worksheets.Count);
}
clearFunc.Invoke();
return(true);
}
