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); }