public static System.Xml.Schema.XmlSchemaComplexType GetTypedTableSchema(System.Xml.Schema.XmlSchemaSet xs)
{
System.Xml.Schema.XmlSchemaComplexType type = new System.Xml.Schema.XmlSchemaComplexType();
System.Xml.Schema.XmlSchemaSequence sequence = new System.Xml.Schema.XmlSchemaSequence();
ResxData ds = new ResxData();
xs.Add(ds.GetSchemaSerializable());
System.Xml.Schema.XmlSchemaAny any1 = new System.Xml.Schema.XmlSchemaAny();
any1.Namespace = "http://www.w3.org/2001/XMLSchema";
any1.MinOccurs = new decimal(0);
any1.MaxOccurs = decimal.MaxValue;
any1.ProcessContents = System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any1);
System.Xml.Schema.XmlSchemaAny any2 = new System.Xml.Schema.XmlSchemaAny();
any2.Namespace = "urn:schemas-microsoft-com:xml-diffgram-v1";
any2.MinOccurs = new decimal(1);
any2.ProcessContents = System.Xml.Schema.XmlSchemaContentProcessing.Lax;
sequence.Items.Add(any2);
System.Xml.Schema.XmlSchemaAttribute attribute1 = new System.Xml.Schema.XmlSchemaAttribute();
attribute1.Name = "namespace";
attribute1.FixedValue = ds.Namespace;
type.Attributes.Add(attribute1);
System.Xml.Schema.XmlSchemaAttribute attribute2 = new System.Xml.Schema.XmlSchemaAttribute();
attribute2.Name = "tableTypeName";
attribute2.FixedValue = "ResxLocalizedDataTable";
type.Attributes.Add(attribute2);
type.Particle = sequence;
return(type);
}
private ResxData ResxToDataSet(string path, bool deepSearch, string[] cultureList, string[] excludeList, bool useFolderNamespacePrefix)
{
ResxData rd = new ResxData();
string[] files;
if (deepSearch)
{
files = System.IO.Directory.GetFiles(path, "*.resx", SearchOption.AllDirectories);
}
else
{
files = System.IO.Directory.GetFiles(path, "*.resx", SearchOption.TopDirectoryOnly);
}
foreach (string f in files)
{
if (!ResxIsCultureSpecific(f))
{
ReadResx(f, path, rd, cultureList, excludeList, useFolderNamespacePrefix);
}
}
return(rd);
}
private string[] GetCulturesFromDataSet(ResxData rd)
{
if (rd.ResxLocalized.Rows.Count > 0)
{
ArrayList list = new ArrayList();
foreach (ResxData.ResxLocalizedRow r in rd.ResxLocalized.Rows)
{
if (!string.IsNullOrEmpty(r.Culture) &&
list.IndexOf(r.Culture) < 0)
{
list.Add(r.Culture);
}
}
string[] cultureList = new string[list.Count];
int i = 0;
foreach (string c in list)
{
cultureList[i] = c;
i++;
}
return(cultureList);
}
else
{
return(null);
}
}
public override System.Data.DataSet Clone()
{
ResxData cln = ((ResxData)(base.Clone()));
cln.InitVars();
cln.SchemaSerializationMode = this.SchemaSerializationMode;
return(cln);
}
public void ResxToXml(string path, bool deepSearch, string xslFile, string[] cultures, string[] excludeList, bool useFolderNamespacePrefix, string[] directoryExcludes, bool includeNonTranslatableTxt)
{
if (!System.IO.Directory.Exists(path))
{
return;
}
ResxData rd = ResxToDataSet(path, deepSearch, cultures, excludeList, useFolderNamespacePrefix, directoryExcludes, includeNonTranslatableTxt);
DataSetToXml(rd, xslFile);
}
public void ResxToXls(string path, bool deepSearch, string xslFile, string[] cultures, string[] excludeList, bool useFolderNamespacePrefix)
{
if (!System.IO.Directory.Exists(path))
{
return;
}
ResxData rd = ResxToDataSet(path, deepSearch, cultures, excludeList, useFolderNamespacePrefix);
DataSetToXls(rd, xslFile);
ShowXls(xslFile);
}
public static System.Xml.Schema.XmlSchemaComplexType GetTypedDataSetSchema(System.Xml.Schema.XmlSchemaSet xs)
{
ResxData ds = new ResxData();
System.Xml.Schema.XmlSchemaComplexType type = new System.Xml.Schema.XmlSchemaComplexType();
System.Xml.Schema.XmlSchemaSequence sequence = new System.Xml.Schema.XmlSchemaSequence();
xs.Add(ds.GetSchemaSerializable());
System.Xml.Schema.XmlSchemaAny any = new System.Xml.Schema.XmlSchemaAny();
any.Namespace = ds.Namespace;
sequence.Items.Add(any);
type.Particle = sequence;
return(type);
}
private void DataSetToXml(ResxData rd, string fileName)
{
Console.WriteLine(Messages.CreatingXml);
using (XmlWriter xw = XmlWriter.Create(fileName, new XmlWriterSettings()
{
Encoding = new UTF8Encoding(false), Indent = true
}
))
{
int row = DATA_ROWS_OFFSET;
xw.WriteStartElement("Translations");
xw.WriteStartElement("Worksheet");
xw.WriteAttributeString("Name", "Localize");
DataView dw = rd.Resx.DefaultView;
dw.Sort = "FileSource, Key";
foreach (DataRowView drw in dw)
{
ResxData.ResxRow r = (ResxData.ResxRow)drw.Row;
xw.WriteStartElement("Message");
xw.WriteElementString("ResourceFile", r.FileSource);
xw.WriteElementString("ResourceKey", r.Key);
xw.WriteElementString("Text", r.Value);
xw.WriteElementString("Comment", r.Comment);
xw.WriteStartElement("Translation");
foreach (ResxData.ResxLocalizedRow lr in r.GetResxLocalizedRows())
{
if (string.IsNullOrEmpty(lr.Value))
{
continue;
}
xw.WriteStartElement("Text");
xw.WriteAttributeString("Culture", lr.Culture);
xw.WriteValue(lr.Value);
xw.WriteEndElement();
}
xw.WriteEndElement();
xw.WriteEndElement();
}
xw.WriteEndElement();
xw.WriteEndElement();
}
}
private void ReadResx(string fileName, string projectRoot, ResxData rd, string[] cultureList, string[] excludeList, bool useFolderNamespacePrefix, bool includeNonTranslatableTxt)
{
FileInfo fi = new FileInfo(fileName);
string fileRelativePath = fi.FullName.Remove(0, AddBS(projectRoot).Length);
string fileDestination = useFolderNamespacePrefix ?
GetNamespacePrefix(AddBS(projectRoot), AddBS(fi.DirectoryName)) + fi.Name
: fi.Name;
HashSet <string> keys = new HashSet <string>();
try
{
ITypeResolutionService typeres = null;
using (ResXResourceReader reader = new ResXResourceReader(fileName)
{
BasePath = fi.DirectoryName
})
{
reader.UseResXDataNodes = true;
IDictionaryEnumerator dictResx = reader.GetEnumerator();
while (dictResx.MoveNext())
{
ResXDataNode dnode = (ResXDataNode)dictResx.Value;
object value = dnode.GetValue(typeres);
if (value is string)
{
AddResx2DatasetRow(dnode.Name, (string)value, dnode.Comment, rd, cultureList, excludeList, fileRelativePath, fileDestination, includeNonTranslatableTxt);
keys.Add(string.Format("{0}#{1}", fileRelativePath, dnode.Name));
}
}
}
}
catch (Exception ex)
{
if (TryReadingAsXml(fileName, rd, cultureList, excludeList, fileRelativePath, fileDestination, includeNonTranslatableTxt, keys))
{
OutputHelper.WriteWarning(string.Format(Messages.ErrorReadingFileDetailException, fileName, ex.Message));
Console.WriteLine(" " + Messages.InfoReadingControlText);
}
else
{
OutputHelper.WriteError(string.Format(Messages.ErrorReadingFile, fileName));
Console.WriteLine(" " + string.Format(Messages.InfoExceptionDetail, ex.Message));
}
}
}
private ResxData ResxToDataSet(string path, bool deepSearch, string[] cultureList, string[] excludeList, bool useFolderNamespacePrefix, string[] directoryExcludes, bool includeNonTranslatableTxt)
{
ResxData rd = new ResxData();
List <string> cultures = new List <string>(cultureList);
string[] files;
if (deepSearch)
{
files = System.IO.Directory.GetFiles(path, "*.resx", SearchOption.AllDirectories);
}
else
{
files = System.IO.Directory.GetFiles(path, "*.resx", SearchOption.TopDirectoryOnly);
}
List <string> cultureSpecific = new List <string>();
foreach (string f in files)
{
// Ignore .resx processing if "Resources.nontranslatable" file is defined
if (File.Exists(Path.Combine(Path.GetDirectoryName(f), "Resources.nontranslatable")) ||
File.Exists(f + ".nontranslatable"))
{
continue;
}
string cult;
if (!ResxIsCultureSpecific(f, out cult) && !FileHelper.IsExcluded(path, f, directoryExcludes))
{
Console.WriteLine(string.Format(Messages.ReadingFile, f));
ReadResx(f, path, rd, cultureList, excludeList, useFolderNamespacePrefix, includeNonTranslatableTxt);
}
else if (cultures.Contains(cult))
{
cultureSpecific.Add(f);
}
}
foreach (string f in cultureSpecific)
{
Console.WriteLine(string.Format(Messages.ReadingFile, f));
ReadResxCult(f, path, rd, cultureList, excludeList, useFolderNamespacePrefix);
}
return(rd);
}
private void AddResx2DatasetRow(string key, string value, string comment, ResxData rd, string[] cultureList, string[] excludeList, string fileRelativePath, string fileDestination, bool includeNonTranslatableTxt)
{
if (excludeList.FirstOrDefault(x => key.EndsWith(x)) != null)
{
return;
}
if (!includeNonTranslatableTxt &&
(string.IsNullOrEmpty(value) ||
!string.IsNullOrEmpty(comment) && comment.StartsWith("#NonTranslatable", StringComparison.InvariantCultureIgnoreCase)))
{
return;
}
value = value.Replace("\r", "\\r").Replace("\n", "\\n");
ResxData.ResxRow r = rd.Resx.NewResxRow();
r.FileSource = fileRelativePath;
r.FileDestination = fileDestination;
r.Key = key;
r.Value = value;
r.Comment = comment;
rd.Resx.AddResxRow(r);
foreach (string cult in cultureList)
{
ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow();
lr.Key = r.Key;
// change get from resx now if exists.
lr.Value = String.Empty;
lr.Culture = cult;
lr.ParentId = r.Id;
lr.SetParentRow(r);
rd.ResxLocalized.AddResxLocalizedRow(lr);
}
}
private bool TryReadingAsXml(string resxFileName, ResxData rd, string[] cultureList, string[] excludeList, string fileRelativePath, string fileDestination, bool includeNonTranslatableTxt, HashSet <string> keys)
{
// Use this method as a second change to identify all .Text expressions
try
{
XmlDocument xDoc = new XmlDocument();
xDoc.Load(resxFileName);
foreach (XmlNode strNode in xDoc.SelectNodes("root/data[@name]"))
{
if (strNode.Attributes["name"].Value.EndsWith(".Text") &&
!keys.Contains(string.Format("{0}#{1}", fileRelativePath, strNode.Attributes["name"].Value)))
{
AddResx2DatasetRow(strNode.Attributes["name"].Value, strNode.SelectSingleNode("value").InnerText, null, rd, cultureList, excludeList, fileRelativePath, fileDestination, includeNonTranslatableTxt);
}
}
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// Read ResxData from .resx files
/// </summary>
/// <param name="path">root path of resx files</param>
/// <param name="deepSearch">search subdirs</param>
/// <param name="cultureList">list of cultures to translate</param>
/// <param name="excludeKeyList">list of keys to exclude (regular expressions)</param>
/// <param name="excludeCommentList">list of comments to exclude (regular expressions)</param>
/// <param name="useFolderNamespacePrefix">use folder namespace prefix</param>
/// <returns>a ResxData with all data</returns>
public static ResxData FromResx(string path, bool deepSearch, bool purge, List<CultureInfo> cultureList,
List<string> excludeKeyList, List<string> excludeCommentList)
{
ResxData rd = new ResxData();
rd.exportCultures = cultureList;
rd.excludeKeyList = excludeKeyList;
rd.excludeCommentList = excludeCommentList;
List<string> regexes = AppSettings.Default.ExcludeFilenames.Cast<string>()
.ToList();
var searchoptions = deepSearch ? SearchOption.AllDirectories : SearchOption.TopDirectoryOnly;
var files = Directory.GetFiles(path, "*.resx", searchoptions)
.Where(filename => !regexes.Any(pattern => Regex.IsMatch(filename, pattern)))
.ToList();
foreach (string f in files)
{
var resx = new Resx(f);
if (!resx.IsCultureSpecific)
{
rd.ReadResx(resx, path, purge);
}
}
if (!string.IsNullOrEmpty(rd.ReadResxReport))
{
Console.WriteLine(rd.ReadResxReport);
}
return rd;
}
private void DataSetToXls(ResxData rd, string fileName)
{
Console.WriteLine(Messages.CreatingSpreadsheet);
using (ExcelPackage app = new ExcelPackage(new FileInfo(fileName)))
{
app.Workbook.Worksheets.Add("WorkSheet1");
ExcelWorksheets sheets = app.Workbook.Worksheets;
ExcelWorksheet sheet = sheets[1];
sheet.Name = "Localize";
// <Define Basic Columns>
sheet.Cells[TITLE_ROW, COL1_IDX__RESX_SRC].Value = "Resx source";
sheet.Cells[TITLE_ROW, COL2_IDX__RESX_DEST].Value = "Resx Name";
sheet.Cells[TITLE_ROW, COL3_IDX__KEY].Value = "Key";
sheet.Cells[TITLE_ROW, COL4_IDX__VALUE].Value = "Value";
sheet.Cells[TITLE_ROW, COL5_IDX__COMMENT].Value = "Comment";
// </Define Basic Columns>
string[] cultures = GetCulturesFromDataSet(rd);
if (cultures == null)
{
return;
}
int index = DATA_COLS_OFFSET;
foreach (string cult in cultures)
{
CultureInfo ci = new CultureInfo(cult);
sheet.Cells[TITLE_ROW, index].Value = ci.DisplayName;
sheet.Cells[CULTURE_ROW, index].Value = ci.Name;
index++;
}
DataView dw = rd.Resx.DefaultView;
dw.Sort = "FileSource, Key";
int row = DATA_ROWS_OFFSET;
foreach (DataRowView drw in dw)
{
ResxData.ResxRow r = (ResxData.ResxRow)drw.Row;
if (r.Value.StartsWith("="))
{
r.Value = "!" + r.Value;
}
ResxData.ResxLocalizedRow[] rows = r.GetResxLocalizedRows();
#if EMPTYRES
bool hasAlreadyTranslate = false;
bool emptyResource = false;
#endif
foreach (ResxData.ResxLocalizedRow lr in rows)
{
string culture = lr.Culture;
int col = Array.IndexOf(cultures, culture);
if (lr.Value.StartsWith("="))
{
lr.Value = "!" + lr.Value;
}
if (col >= 0 && r.Value.Length > 0 && lr.Value.Length > 0)
{
#if EMPTYRES
hasAlreadyTranslate = true;
#endif
sheet.Cells[row, col + DATA_COLS_OFFSET].Value = lr.Value;
}
else if (col >= 0 && r.Value.Length == 0)
{
//Nothing to translate
#if EMPTYRES
hasAlreadyTranslate = true;
emptyResource = true;
#endif
}
else if (col >= 0)
{
sheet.Cells[row, col + DATA_COLS_OFFSET].Value = lr.Value;
}
}
#if EMPTYRES
if (!emptyResource)
{
#endif
sheet.Cells[row, COL1_IDX__RESX_SRC].Value = r.FileSource;
sheet.Cells[row, COL2_IDX__RESX_DEST].Value = r.FileDestination;
sheet.Cells[row, COL3_IDX__KEY].Value = r.Key;
sheet.Cells[row, COL4_IDX__VALUE].Value = r.Value;
sheet.Cells[row, COL5_IDX__COMMENT].Value = r.Comment;
row++;
#if EMPTYRES
}
#endif
}
sheet.Cells["A1:Z1"].AutoFitColumns();
// Save the Workbook and quit Excel.
app.Save();
}
}
private ResxData XlsToDataSet(string xlsFile)
{
ResxData rd = new ResxData();
using (ExcelPackage app = new ExcelPackage(new FileInfo(xlsFile)))
{
ExcelWorksheets sheets = app.Workbook.Worksheets;
ExcelWorksheet sheet = sheets[1];
int row = DATA_ROWS_OFFSET;
bool continueLoop = true;
while (continueLoop)
{
string fileSrc = (sheet.Cells[row, COL1_IDX__RESX_SRC] as ExcelRange).Text.ToString();
if (String.IsNullOrEmpty(fileSrc))
{
break;
}
ResxData.ResxRow r = rd.Resx.NewResxRow();
r.FileSource = (sheet.Cells[row, COL1_IDX__RESX_SRC] as ExcelRange).Text.ToString();
r.FileDestination = (sheet.Cells[row, COL2_IDX__RESX_DEST] as ExcelRange).Text.ToString();
r.Key = (sheet.Cells[row, COL3_IDX__KEY] as ExcelRange).Text.ToString();
r.Value = (sheet.Cells[row, COL4_IDX__VALUE] as ExcelRange).Text.ToString();
r.Comment = (sheet.Cells[row, COL5_IDX__COMMENT] as ExcelRange).Text.ToString();
rd.Resx.AddResxRow(r);
bool hasCulture = true;
int col = DATA_COLS_OFFSET;
while (hasCulture)
{
string cult = (sheet.Cells[CULTURE_ROW, col] as ExcelRange).Text.ToString();
if (String.IsNullOrEmpty(cult))
{
break;
}
ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow();
lr.Culture = cult;
lr.Key = (sheet.Cells[row, COL3_IDX__KEY] as ExcelRange).Text.ToString();
lr.Value = (sheet.Cells[row, col] as ExcelRange).Text.ToString();
lr.ParentId = r.Id;
lr.SetParentRow(r);
rd.ResxLocalized.AddResxLocalizedRow(lr);
col++;
}
row++;
}
rd.AcceptChanges();
}
return(rd);
}
private void ReadResx(string fileName, string projectRoot, ResxData rd, string[] cultureList, string[] excludeList, bool useFolderNamespacePrefix)
{
FileInfo fi = new FileInfo(fileName);
string fileRelativePath = fi.FullName.Remove(0, AddBS(projectRoot).Length);
string fileDestination;
if (useFolderNamespacePrefix)
{
fileDestination = GetNamespacePrefix(AddBS(projectRoot), AddBS(fi.DirectoryName)) + fi.Name;
}
else
{
fileDestination = fi.Name;
}
ResXResourceReader reader = new ResXResourceReader(fileName);
reader.BasePath = fi.DirectoryName;
try
{
IDictionaryEnumerator ide = reader.GetEnumerator();
#region read
foreach (DictionaryEntry de in reader)
{
if (de.Value is string)
{
string key = (string)de.Key;
bool exclude = false;
foreach (string e in excludeList)
{
if (key.EndsWith(e))
{
exclude = true;
break;
}
}
if (!exclude)
{
string value = de.Value.ToString();
ResxData.ResxRow r = rd.Resx.NewResxRow();
r.FileSource = fileRelativePath;
r.FileDestination = fileDestination;
r.Key = key;
value = value.Replace("\r", "\\r");
value = value.Replace("\n", "\\n");
r.Value = value;
rd.Resx.AddResxRow(r);
foreach (string cult in cultureList)
{
ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow();
lr.Key = r.Key;
lr.Value = String.Empty;
lr.Culture = cult;
lr.ParentId = r.Id;
lr.SetParentRow(r);
rd.ResxLocalized.AddResxLocalizedRow(lr);
}
}
}
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show("A problem occured reading " + fileName + "\n" + ex.Message, "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
reader.Close();
}
private void ReadResxCult(string fileName, string projectRoot, ResxData rd, string[] cultureList, string[] excludeList, bool useFolderNamespacePrefix)
{
FileInfo fi = new FileInfo(fileName);
CultureInfo cultureInfo = GetResxCultureSpecific(fileName);
string fileRelativePath = fi.FullName.Remove(0, AddBS(projectRoot).Length);
string fileDestination;
if (useFolderNamespacePrefix)
{
fileDestination = GetNamespacePrefix(AddBS(projectRoot), AddBS(fi.DirectoryName)) + fi.Name;
}
else
{
fileDestination = fi.Name;
}
ResXResourceReader reader = new ResXResourceReader(fileName);
reader.BasePath = fi.DirectoryName;
try
{
#region read
foreach (DictionaryEntry de in reader)
{
if (de.Value is string)
{
string key = (string)de.Key;
bool exclude = false;
foreach (string e in excludeList)
{
if (key.EndsWith(e))
{
exclude = true;
break;
}
}
if (!exclude)
{
string strWhere = String.Format("FileSource ='{0}' AND Key='{1}'", fileRelativePath.Replace("." + cultureInfo.Name, ""), de.Key.ToString());
ResxData.ResxRow[] rows = (ResxData.ResxRow[])rd.Resx.Select(strWhere);
if ((rows == null) || (rows.Length == 0))
{
continue;
}
ResxData.ResxRow row = rows[0];
foreach (ResxData.ResxLocalizedRow lr in row.GetResxLocalizedRows())
{
if (lr.Culture == cultureInfo.Name)
{
row.BeginEdit();
string value = de.Value.ToString();
// update row
if (value.Contains("\r") || value.Contains("\n"))
{
value = value.Replace("\r", "\\r").Replace("\n", "\\n");
}
lr.Value = value;
row.EndEdit();
}
}
}
}
}
#endregion
}
catch (Exception ex)
{
Console.WriteLine(string.Format(Messages.ErrorReadingFileDetailException, fileName, ex.Message), "Information");
}
reader.Close();
}
private void UpdateXls(string xlsFile, string projectRoot, bool deepSearch, string[] excludeList, bool useFolderNamespacePrefix)
{
if (!File.Exists(xlsFile))
{
return;
}
string path = new FileInfo(xlsFile).DirectoryName;
string[] files;
if (deepSearch)
{
files = System.IO.Directory.GetFiles(projectRoot, "*.resx", SearchOption.AllDirectories);
}
else
{
files = System.IO.Directory.GetFiles(projectRoot, "*.resx", SearchOption.TopDirectoryOnly);
}
ResxData rd = XlsToDataSet(xlsFile);
foreach (string f in files)
{
FileInfo fi = new FileInfo(f);
string fileRelativePath = fi.FullName.Remove(0, AddBS(projectRoot).Length);
string fileDestination;
if (useFolderNamespacePrefix)
{
fileDestination = GetNamespacePrefix(AddBS(projectRoot), AddBS(fi.DirectoryName)) + fi.Name;
}
else
{
fileDestination = fi.Name;
}
ResXResourceReader reader = new ResXResourceReader(f);
reader.BasePath = fi.DirectoryName;
foreach (DictionaryEntry d in reader)
{
if (d.Value is string)
{
bool exclude = false;
foreach (string e in excludeList)
{
if (d.Key.ToString().EndsWith(e))
{
exclude = true;
break;
}
}
if (!exclude)
{
string strWhere = String.Format("FileSource ='{0}' AND Key='{1}'", fileRelativePath, d.Key.ToString());
ResxData.ResxRow[] rows = (ResxData.ResxRow[])rd.Resx.Select(strWhere);
ResxData.ResxRow row = null;
if ((rows == null) | (rows.Length == 0))
{
// add row
row = rd.Resx.NewResxRow();
row.FileSource = fileRelativePath;
row.FileDestination = fileDestination;
// I update the neutral value
row.Key = d.Key.ToString();
rd.Resx.AddResxRow(row);
}
else
{
row = rows[0];
}
// update row
row.BeginEdit();
string value = d.Value.ToString();
value = value.Replace("\r", "\\r");
value = value.Replace("\n", "\\n");
row.Value = value;
row.EndEdit();
}
}
}
}
//delete unchenged rows
foreach (ResxData.ResxRow r in rd.Resx.Rows)
{
if (r.RowState == DataRowState.Unchanged)
{
r.Delete();
}
}
rd.AcceptChanges();
DataSetToXls(rd, xlsFile);
}
/// <summary>
/// Create a ResxData instance from an xls file
/// </summary>
/// <param name="xlsFile">xls file to read</param>
/// <returns>a newly instantized ResxData</returns>
public static ResxData FromXls(string xlsFile)
{
Excel.Application app = new Excel.Application();
Excel.Workbook wb = app.Workbooks.Open(
Filename : xlsFile,
Format : 5,
Origin : Excel.XlPlatform.xlWindows,
Editable : true,
AddToMru : true);
Excel.Sheets sheets = wb.Worksheets;
ResxData rd = new ResxData();
TranslationSourceRow currentResx = null;
foreach (Excel.Worksheet sheet in sheets)
{
// Skip the explanation sheet
if (sheet.Index == 1 && sheet.Name == ExplanationSheetName)
{
continue;
}
// Get filesource for current sheet
var filesource = (sheet.Cells[ExcelMetadataRow, ExcelFilesourceColumn] as Excel.Range).Text.ToString();
// Create a list of all cultures in the excel sheet
List<string> cultures = new List<string>();
int culturescolumn = ExcelCultureColumn;
while (!String.IsNullOrEmpty((sheet.Cells[2, culturescolumn] as Excel.Range).Text.ToString()))
{
cultures.Add((sheet.Cells[ExcelMetadataRow, culturescolumn] as Excel.Range).Text.ToString());
culturescolumn++;
}
// Iterate over all rows in the Excel sheet
int row = ExcelDataRow;
while (!String.IsNullOrEmpty((sheet.Cells[row, 1] as Excel.Range).Text.ToString()))
{
if (currentResx == null || currentResx.FileSource != filesource)
{
currentResx = rd.TranslationSource.NewTranslationSourceRow();
currentResx.FileSource = filesource;
rd.TranslationSource.AddTranslationSourceRow(currentResx);
}
var resxKey = rd.PrimaryTranslation.NewPrimaryTranslationRow();
resxKey.Key = (sheet.Cells[row, ExcelKeyColumn] as Excel.Range).Text.ToString();
resxKey.Value = (sheet.Cells[row, ExcelValueColumn] as Excel.Range).Text.ToString();
resxKey.ResxRow = currentResx;
rd.PrimaryTranslation.AddPrimaryTranslationRow(resxKey);
// Iterate over all culture columns in the Excel sheet
for (int cultureindex = 0; cultureindex < cultures.Count; cultureindex++)
{
SecondaryTranslationRow lr = rd.SecondaryTranslation.NewSecondaryTranslationRow();
lr.Culture = cultures[cultureindex];
lr.Value = (sheet.Cells[row, ExcelCultureColumn + cultureindex] as Excel.Range).Text.ToString();
lr.PrimaryTranslationRow = resxKey;
rd.SecondaryTranslation.AddSecondaryTranslationRow(lr);
}
row++;
}
}
rd.AcceptChanges();
ExcelQuit(app, wb);
return rd;
}
private ResxData XlsToDataSet(string xlsFile)
{
Excel.Application app = new Excel.Application();
Excel.Workbook wb = app.Workbooks.Open(xlsFile,
0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
true, false, 0, true, false, false);
Excel.Sheets sheets = wb.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1);
ResxData rd = new ResxData();
int row = 3;
bool continueLoop = true;
while (continueLoop)
{
string fileSrc = (sheet.Cells[row, 1] as Excel.Range).Text.ToString();
if (String.IsNullOrEmpty(fileSrc))
{
break;
}
ResxData.ResxRow r = rd.Resx.NewResxRow();
r.FileSource = (sheet.Cells[row, 1] as Excel.Range).Text.ToString();
r.FileDestination = (sheet.Cells[row, 2] as Excel.Range).Text.ToString();
r.Key = (sheet.Cells[row, 3] as Excel.Range).Text.ToString();
r.Value = (sheet.Cells[row, 4] as Excel.Range).Text.ToString();
rd.Resx.AddResxRow(r);
bool hasCulture = true;
int col = 5;
while (hasCulture)
{
string cult = (sheet.Cells[2, col] as Excel.Range).Text.ToString();
if (String.IsNullOrEmpty(cult))
{
break;
}
ResxData.ResxLocalizedRow lr = rd.ResxLocalized.NewResxLocalizedRow();
lr.Culture = cult;
lr.Key = (sheet.Cells[row, 3] as Excel.Range).Text.ToString();
lr.Value = (sheet.Cells[row, col] as Excel.Range).Text.ToString();
lr.ParentId = r.Id;
lr.SetParentRow(r);
rd.ResxLocalized.AddResxLocalizedRow(lr);
col++;
}
row++;
}
rd.AcceptChanges();
wb.Close(false, m_objOpt, m_objOpt);
app.Quit();
return(rd);
}
private void DataSetToXls(ResxData rd, string fileName)
{
Excel.Application app = new Excel.Application();
Excel.Workbook wb = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Sheets sheets = wb.Worksheets;
Excel.Worksheet sheet = (Excel.Worksheet)sheets.get_Item(1);
sheet.Name = "Localize";
sheet.Cells[1, 1] = "Resx source";
sheet.Cells[1, 2] = "Resx Name";
sheet.Cells[1, 3] = "Key";
sheet.Cells[1, 4] = "Value";
string[] cultures = GetCulturesFromDataSet(rd);
int index = 5;
foreach (string cult in cultures)
{
CultureInfo ci = new CultureInfo(cult);
sheet.Cells[1, index] = ci.DisplayName;
sheet.Cells[2, index] = ci.Name;
index++;
}
DataView dw = rd.Resx.DefaultView;
dw.Sort = "FileSource, Key";
int row = 3;
foreach (DataRowView drw in dw)
{
ResxData.ResxRow r = (ResxData.ResxRow)drw.Row;
sheet.Cells[row, 1] = r.FileSource;
sheet.Cells[row, 2] = r.FileDestination;
sheet.Cells[row, 3] = r.Key;
sheet.Cells[row, 4] = r.Value;
ResxData.ResxLocalizedRow[] rows = r.GetResxLocalizedRows();
foreach (ResxData.ResxLocalizedRow lr in rows)
{
string culture = lr.Culture;
int col = Array.IndexOf(cultures, culture);
if (col >= 0)
{
sheet.Cells[row, col + 5] = lr.Value;
}
}
row++;
}
sheet.Cells.get_Range("A1", "Z1").EntireColumn.AutoFit();
// Save the Workbook and quit Excel.
wb.SaveAs(fileName, m_objOpt, m_objOpt,
m_objOpt, m_objOpt, m_objOpt, Excel.XlSaveAsAccessMode.xlNoChange,
m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt);
wb.Close(false, m_objOpt, m_objOpt);
app.Quit();
}
