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