private void GenerateExcelSheet(Application excel, System.Data.DataTable table)
{
var worksheet = (Worksheet)excel.Worksheets.Add();
try
{
worksheet.Name = table.TableName;
for (int i = 1; i < table.Columns.Count + 1; ++i)
{
worksheet.Cells[1, i] = table.Columns[i - 1].ColumnName;
}
int sheetRow = RowOffset;
for (int row = RowOffset; row < table.Rows.Count + RowOffset; ++row)
{
bool skip = false;
int languageIndex = LanguageEncodings.Count;
foreach (var language in LanguageEncodings.OrderByDescending(x => x.IsPrimary ? 0 : 1))
{
if (!language.IsPrimary &&
SkipLocalized &&
!string.IsNullOrWhiteSpace(table.Rows[row - RowOffset][language.Encoding].ToString()) &&
(string.IsNullOrWhiteSpace(SkipLocalizedIncludeFilterRegex) ||
!Regex.IsMatch(table.Rows[row - RowOffset][language.Encoding].ToString(), SkipLocalizedIncludeFilterRegex, RegexOptions.Multiline)
)
)
{
skip = true;
break;
}
worksheet.Cells[sheetRow, languageIndex + 1] = table.Rows[row - RowOffset][language.Encoding];
languageIndex -= 1;
}
if (!skip)
{
worksheet.Cells[sheetRow, 1] = (string)table.Rows[row - RowOffset][Key];
sheetRow++;
}
}
if (sheetRow == RowOffset && SkipEmptyResources)
{
//((Worksheet)excel.ActiveWorkbook.Sheets[worksheet.Index]).Delete();
//excel.ActiveWorkbook.Save();
//excel.Worksheets.Delete();
//(excel.Worksheets[worksheet.Index] as Worksheet).Delete();
excel.DisplayAlerts = false;
worksheet.Delete();
excel.DisplayAlerts = true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void GroupCommonResources(DataSet data)
{
const string Common = "Common";
System.Data.DataTable table = data.Tables.Add(Common);
table.Columns.Add(Key);
foreach (var language in LanguageEncodings.OrderBy(x => x.IsPrimary ? 0 : 1))
{
table.Columns.Add(language.Encoding);
}
var primaryLanguage = LanguageEncodings.FirstOrDefault(x => x.IsPrimary);
if (primaryLanguage == null)
{
primaryLanguage = LanguageEncodings.FirstOrDefault();
}
var commonEntries = GetQueryableDataSet(data, primaryLanguage)
.GroupBy(x => x)
.Where(x => x.Count() > 1)
.Select(x => x.Key)
.ToList();
foreach (var entry in commonEntries)
{
var row = table.Rows.Add(entry.Key);
row[primaryLanguage.Encoding] = entry.Value;
}
// We now remove all common entries from the other tables.
foreach (System.Data.DataTable t in data.Tables)
{
if (t.TableName != Common)
{
int count = t.Rows.Count;
for (int i = 0; i < count; ++i)
{
//Console.WriteLine("Group Row: {0}", i);
if (!(t.Rows[i][Key] is System.DBNull) && !(t.Rows[i][primaryLanguage.Encoding] is System.DBNull))
{
if (commonEntries.Exists(x => x.Key.ToString() == t.Rows[i][Key].ToString() &&
x.Value.ToString() == t.Rows[i][primaryLanguage.Encoding].ToString()))
{
t.Rows.RemoveAt(i--);
count -= 1;
}
}
}
}
}
}
private DataSet ParseResourceFiles()
{
//var primaryLanguage = LanguageEncodings.FirstOrDefault(x => x.IsPrimary);
//if (primaryLanguage == null)
// primaryLanguage = LanguageEncodings.FirstOrDefault();
DataSet data = new DataSet();
foreach (var language in LanguageEncodings.OrderBy(x => x.IsPrimary ? 0 : 1))
{
var files = new System.IO.DirectoryInfo(ResourcesDirectory).GetFiles($"*.{language.Encoding}.resx", SearchOption.AllDirectories)
// Ordering them d5escending because as they're added to the excel they're added to the front.
.OrderByDescending(x => x.Name.Replace("_", ""))
.ToList();
foreach (var file in files)
{
string tableName = file.Name
.Replace(file.Extension, "")
.Replace($".{language.Encoding}", "")
.Replace("_", "");
if (tableName.Length > 30)
{
tableName = tableName.Substring(0, 30);
}
if (language.IsPrimary)
{
CreateAndPopulateDataTable(data, language, file, tableName);
}
else
{
if (data.Tables[tableName] == null)
{
CreateAndPopulateDataTable(data, language, file, tableName);
}
else
{
System.Data.DataTable table = data.Tables[tableName];
table.Columns.Add(language.Encoding);
if (language.IncludedResourceValues)
{
PopulateDataTable(language, file, table, true);
}
}
}
}
}
return(data);
}
