WebResourceList = GetAllWebResourceFilesfromSolution(ConfigurationManager.AppSettings["SolutionName"].ToString());
Dictionary<string, string> keyValueText = null;
if (WebResourceList != null)
{
foreach (string file in WebResourceList.Values)
{
keyValueText = ReadWebResourceContent(file);
AddToExcelFile(keyValueText, file);
}
ReadFromExcelandTranslate();
Console.WriteLine("Translation Completed!! Press Any key to exit..!");
Console.ReadKey();
Environment.Exit(0);
}
public static void AddToExcelFile(Dictionary<string, string> resourcedictionary, string filename)
{
try
{
bool sheet_avail = false;
string newfilePath = Environment.CurrentDirectory + @"\Transalation.xlsx";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(newfilePath);
int sheetCount = xlWorkBook.Worksheets.Count;
Excel.Sheets sheets = xlWorkBook.Worksheets;
// Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets;
Excel.Range xlRange;
Excel.Worksheet excelWorkSheet;
int index1 = filename.IndexOf('_') + 1;
int index2 = filename.IndexOf('.');
string languagecode = filename.Substring(index2 + 1, 4);
for (int k = 1; k <= sheetCount; k++)
{
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(k);
string name = worksheet.Name;
if (filename.Contains(name))
{
sheet_avail = true;
excelWorkSheet = (Excel.Worksheet)sheets.Item[k];
xlRange = excelWorkSheet.UsedRange;
int totalColumns = xlRange.Columns.Count;
string[] colnames = new string[totalColumns + 1];
for (int q = 1; q <= totalColumns; q++)
colnames[q] = xlRange.Cells[1, q].Value.ToString();
int i = 2, j = 1;
if (languagecode == "1033")
{
foreach (KeyValuePair<string, string> entry in resourcedictionary)
{
excelWorkSheet.Cells[i, j] = entry.Key;
excelWorkSheet.Cells[i, j + 1] = entry.Value;
i++;
}
}
else
{
bool col_avail = false;
for (int m = 1; m < colnames.Length; m++)
{
if (colnames[m] == languagecode)
{
col_avail = true;
foreach (KeyValuePair<string, string> entry in resourcedictionary)
{
if (excelWorkSheet.Cells[i, j].Value.ToString() == entry.Key)
excelWorkSheet.Cells[i, m] = entry.Value;
i++;
}
break;
}
}
if (col_avail == false)
{
excelWorkSheet.Cells[1, totalColumns + 1] = languagecode;
foreach (KeyValuePair<string, string> entry in resourcedictionary)
{
if (excelWorkSheet.Cells[i, j].Value.ToString() == entry.Key)
excelWorkSheet.Cells[i, totalColumns + 1] = entry.Value;
i++;
}
}
}
break;
}
}
if (sheet_avail == false)
{
var xlNewSheet = (Excel.Worksheet)sheets.Add(Type.Missing, sheets[1], Type.Missing, Type.Missing);
//wSheet.Move(Missing.Value, workbook.Sheets[workbook.Sheets.Count]);
//var xlNewSheet=xlWorkBook.Worksheets.Add();
//xlWorkBook.Sheets.Move(After: xlWorkBook.Sheets.Count);
filename.Substring(index1, index2 - index1);
xlNewSheet.Name = filename.Substring(index1, index2 - index1);
int NewCount = xlWorkBook.Worksheets.Count;
excelWorkSheet = (Excel.Worksheet)sheets.Item[NewCount];
excelWorkSheet.Cells[1, 1] = "Key";
excelWorkSheet.Cells[1, 2] = "1033";
int i = 2, j = 1;
foreach (KeyValuePair<string, string> entry in resourcedictionary)
{
excelWorkSheet.Cells[i, j] = entry.Key;
excelWorkSheet.Cells[i, j + 1] = entry.Value;
i++;
}
}
xlApp.Visible = false;
xlApp.UserControl = false;
xlWorkBook.Save();
xlWorkBook.Close();
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
}
public static void ReadFromExcelandTranslate()
{
try
{
string excelPath = Environment.CurrentDirectory + @"\Transalation.xlsx";
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(excelPath);
int totalSheets = xlWorkBook.Worksheets.Count;
for (int sheet = 1; sheet <= totalSheets; sheet++)
{
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(sheet);
Excel.Range xlRange = xlWorkSheet.UsedRange;
int totalRows = xlRange.Rows.Count;
int totalColumns = xlRange.Columns.Count;
string[] languageTobeConverted = null;
string combinedLanguages = ConfigurationManager.AppSettings["languagesTranslation"];
languageTobeConverted = combinedLanguages.Split(',');
List<int> integerList = new List<int>();
string[] languageLables = null;
languageLables = new string[languageTobeConverted.Count()];
//foreach (var langCode in languageTobeConverted)
for (int u = 0; u < languageTobeConverted.Count(); u++)
{
integerList.Add(int.Parse(languageTobeConverted[u]));
languageLables[u] = languageTobeConverted[u];
}
totalColumns = 2 + integerList.Count;
for (int colCount = 3; colCount <= totalColumns; colCount++)
{
int languageID = 0;
string colNullValue = (xlRange.Cells[1, colCount] as Excel.Range).Text;
if (string.IsNullOrEmpty(colNullValue))
{
for (int d = 0; d < integerList.Count; d++)
{
xlWorkSheet.Cells[1, colCount + d] = integerList[d];
}
languageID = Convert.ToInt32((xlRange.Cells[1, colCount] as Excel.Range).Text);
}
if (!string.IsNullOrEmpty(colNullValue) && languageID == 0)
{
languageID = Convert.ToInt32((xlRange.Cells[1, colCount] as Excel.Range).Text);
}
for (int rowCount = 2; rowCount <= totalRows; rowCount++)
{
originalText = Convert.ToString((xlRange.Cells[rowCount, 2] as Excel.Range).Text);
translatedText = Convert.ToString((xlRange.Cells[rowCount, colCount] as Excel.Range).Text);
if (translatedText == null || translatedText == "")
{
List<TranslationHelper> translatedTextList = Translation.TranslateText(originalText, LanguageCodes.languageID[languageID]);
xlWorkSheet.Cells[rowCount, colCount] = translatedTextList.Where(x => x.to == LanguageCodes.languageID[languageID]).Select(x => x.text).ToList().FirstOrDefault();
}
}
integerList.Remove(languageID);
}
}
xlApp.DisplayAlerts = false;
xlWorkBook.Save();
xlWorkBook.Close();
xlApp.Quit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
}
static void ReadFromExcelToImport()
{
try
{
string excelPath = Environment.CurrentDirectory + @"\Transalation.xlsx";
//XmlDocument doc = new XmlDocument();
//doc.Load(@"..\..\sampleResource.resx");
//string xmlcontents = doc.InnerXml;
//XDocument xmlDocument = XDocument.Parse(xmlcontents);
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(excelPath);
int sheetcount = xlWorkBook.Worksheets.Count;
for (int k = 1; k <= sheetcount; k++)
{
Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(k);
Excel.Range xlRange = xlWorkSheet.UsedRange;
int totalRows = xlRange.Rows.Count;
int totalColumns = xlRange.Columns.Count;
for (int c = 1; c < totalColumns; c++)
{
var xmlDocument = new XDocument();
var declartion = new XDeclaration("1.0", "UTF-8", null);
xmlDocument.Declaration = declartion;
var rootElement = new XElement("root");
xmlDocument.Add(rootElement);
for (int row = 1; row < totalRows; row++)
{
var data = new XElement("data");
data.SetAttributeValue("name", "test");
data.SetAttributeValue(XNamespace.Xml + "space", "preserve");
var value = new XElement("value", "test");
data.Add(value);
rootElement.Add(data);
}
int i = 2, j = 1;
var dataelements = xmlDocument.Descendants("data").ToList();
//for (int n = 1; n < totalRows - 8; n++)
//{
// dataelements.Add(new XElement("data", new XAttribute("name", "test"), new XAttribute(XNamespace.Xml + "space", "preserve"), new XElement("value", "test")));
//}
foreach (var dataelement in dataelements)
{
if (xlRange.Cells[i, j] != null)
dataelement.Attribute("name").Value = xlRange.Cells[i, j].Value.ToString();
if (xlRange.Cells[i, j + c] != null)
dataelement.Element("value").Value = xlRange.Cells[i, j + c].Value.ToString();
i++;
}
//dataelements=xmlDocument.Descendants("data").ToList();
string ws_name = xlWorkSheet.Name;
string c_name = xlRange.Cells[1, j + c].Value.ToString();
string filename = ws_name + "." + c_name;
CreateResourcefile(xmlDocument, filename);
}
}
xlApp.DisplayAlerts = false;
xlWorkBook.Close();
xlApp.Quit();
}
catch (FileNotFoundException ex)
{
Console.WriteLine("File not Found");
Console.Read();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
Console.Read();
}
}
}
}
