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