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