/// <exception cref="CultureNotFoundException"> /// Thrown, if one of the cells of the header rows in <paramref name="excelCells" /> is not recognized as /// a language. /// </exception> private Dictionary<CultureInfo, Dictionary<string, string>> ReadWorksheetTranslations( ExcelInterop.Worksheet worksheet) { //initialize needed variables. var readDict = new Dictionary<CultureInfo, Dictionary<string, string>>(); var numberOfGlossaryEntries = 0; object[,] values = worksheet.UsedRange.get_Value(); var maxRow = values.GetUpperBound(0); var maxColumn = values.GetUpperBound(1); var numKeyParts = ExcelCellToDictionaryUtils.GetNumberOfKeyParts(values); //first row only contains column titles (can be null in cell 1,1), no data. var row = 2; ExcelCellToDictionaryUtils.FillSubDictionaries(readDict, values, maxColumn, numKeyParts); //logging. _logger.Log(LogLevel.Debug, $"Found {numKeyParts} columns for key parts and {maxColumn - numKeyParts} language columns."); _logger.Log(LogLevel.Debug, "Now reading rows from excel sheet."); //reading rows. while (row <= maxRow && values[row, 1] != null) { var isGlossaryEntry = _glossaryTag != null && _glossaryTag.Equals(values[row, 1]); //check if current row has a comment //or is part of glossary (assuming a glossary is being used). if (values[row, 2] == null && !isGlossaryEntry) { _logger.Log(LogLevel.Debug, $"Skipped row #{row}, because it is a comment."); row++; continue; } //get key. var key = ExcelCellToDictionaryUtils.ExcelCellToDictionaryKey( values, row, numKeyParts, isGlossaryEntry, _glossaryTag, ref numberOfGlossaryEntries); //add translations to dictionary. for (var langIndex = numKeyParts + 1; langIndex <= maxColumn; langIndex++) { ExcelCellToDictionaryUtils.TryAddExcelCellToDictionary(readDict, values[1, langIndex], values[row, langIndex], key); } row++; } return readDict; }
private void WriteTranslationsToWorksheet(ExcelInterop.Worksheet worksheet, Dictionary<string, List<TextLocalization>> texts) { //assignments that always work. var glossaryKey = new Regex($"^{_glossaryTag}\\d*$"); var usedRange = worksheet.UsedRange; object[,] values = usedRange.Value; //assignments that fail, if sheet is empty or was just created. int maxColumn; Dictionary<CultureInfo, int> languageColumnLookup; int numberOfKeyParts; if (values == null) { numberOfKeyParts = texts.First().Key .Split(Settings.Default.Seperator_for_partial_Literalkeys).Length; languageColumnLookup = new Dictionary<CultureInfo, int>(); maxColumn = numberOfKeyParts; } else { numberOfKeyParts = ExcelCellToDictionaryUtils.GetNumberOfKeyParts(values); languageColumnLookup = ExcelCellToDictionaryUtils.GetLanguageColumnsLookupTable(values, numberOfKeyParts); maxColumn = values.GetUpperBound(1); } foreach (var translation in texts) { //if current entry is part of glossary, skip writing. if (_glossaryTag != null && glossaryKey.IsMatch(translation.Key)) { continue; } var updatedRow = false; var lastFindForDialogIndex = -1; var keyParts = translation.Key.Split(Settings.Default.Seperator_for_partial_Literalkeys); keyParts = DictionaryToExcelCellUtils.SqueezeArrayIntoShapeIfNeeded(keyParts, numberOfKeyParts); //find first row, matching beginning of key. var currentDialogFind = usedRange.Find(keyParts[0], Type.Missing, ExcelInterop.XlFindLookIn.xlValues, ExcelInterop.XlLookAt.xlPart, ExcelInterop.XlSearchOrder.xlByRows, ExcelInterop.XlSearchDirection.xlNext, false); var firstDialogFind = currentDialogFind; //search for match with key. while (currentDialogFind != null) { //values array, usedRange, maxColumns and languagesColumnLookup may change if //Excel sheet needs to be altered. updatedRow = DictionaryToExcelCellUtils.TryUpdateRow(worksheet, ref usedRange, ref values, ref maxColumn, ref languageColumnLookup, currentDialogFind, translation.Value, keyParts, numberOfKeyParts); lastFindForDialogIndex = currentDialogFind.Row; currentDialogFind = usedRange.FindNext(currentDialogFind); //stop, if row was updated or no entries in sheet matched fully. if (updatedRow || currentDialogFind.Address == firstDialogFind.Address) { break; } } if (updatedRow) { continue; } //if no row was found, a new one needs to be created. _logger.Log(LogLevel.Trace, $"New Entry will be created in Excel sheet for key ({translation.Key})."); //values array, usedRange, maxColumns and languagesColumnLookup may change if Excel sheet //needs to be altered. DictionaryToExcelCellUtils.WriteNewRow(worksheet, ref usedRange, ref values, ref maxColumn, ref languageColumnLookup, lastFindForDialogIndex, translation.Value, keyParts, numberOfKeyParts); } }