public static void Export(InternalFormat data)
{
var App = new Excel.Application();
// Make the object visible.
App.Visible = true;
// App.ScreenUpdating = false;
// Create a new, empty workbook and add it to the collection returned
// by property Workbooks. The new workbook becomes the active workbook.
// Add has an optional parameter for specifying a praticular template.
// Because no argument is sent in this example, Add creates a new workbook.
var Workbooks = App.Workbooks;
Workbooks.Add();
// This example uses a single workSheet.
Excel._Worksheet Worksheet = App.ActiveSheet;
// Caption
Worksheet.Rows[1].Interior.Color = ColorTranslator.ToOle(Color.Orange);
Worksheet.Rows[1].Font.Bold = true;
Worksheet.Rows[1].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Establish column headings in cells A1, B1 and other.
Worksheet.Columns[1].ColumnWidth = 10;
Worksheet.Cells[1, "A"] = "#";
Worksheet.Columns[2].ColumnWidth = 40;
Worksheet.Cells[1, "B"] = "ID";
Worksheet.Columns[3].ColumnWidth = 100;
Worksheet.Cells[1, "C"] = data.NativeCulture;
for (int i = 0, j = 4; i < data.Cultures.Count; i++)
{
if (data.Cultures[i] == data.NativeCulture)
{
continue;
}
Worksheet.Columns[j].ColumnWidth = 100;
Worksheet.Cells[1, j] = data.Cultures[i];
j++;
}
int index = 2;
foreach (var ns in data.Subnamespaces)
{
foreach (var rec in ns.Children)
{
foreach (var key in rec.Keys)
{
Worksheet.Cells[index, "A"].HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Worksheet.Cells[index, "A"] = (index - 1).ToString();
Worksheet.Cells[index, "B"] = InternalNamespace.MakeFullName(ns.Name, key.Key);
Worksheet.Cells[index, "C"].Interior.Color = ColorTranslator.ToOle(Color.FromArgb(255, 229, 212));
Worksheet.Cells[index, "C"] = key.GetTranslationForCulture(data.NativeCulture);
for (int i = 0, j = 4; i < data.Cultures.Count; i++)
{
if (data.Cultures[i] == data.NativeCulture)
{
continue;
}
Worksheet.Cells[index, j].Interior.Color = (j % 2 == 0) ?
ColorTranslator.ToOle(Color.FromArgb(200, 239, 212)) :
ColorTranslator.ToOle(Color.FromArgb(200, 235, 250));
Worksheet.Cells[index, j] = key.GetTranslationForCulture(data.Cultures[i]);
j++;
}
index++;
}
}
}
Worksheet.Cells[index, "A"].Font.Color = ColorTranslator.ToOle(Color.Red);
Worksheet.Cells[index, "A"].Font.Bold = true;
Worksheet.Cells[index, "A"] = serviceData;
index++;
foreach (var ns in data.Subnamespaces)
{
foreach (var rec in ns.Children)
{
foreach (var key in rec.Keys)
{
Worksheet.Rows[index].Font.Color = ColorTranslator.ToOle(Color.LightGray);
Worksheet.Cells[index, "A"] = rec.Source;
Worksheet.Cells[index, "B"] = ns.Name;
Worksheet.Cells[index, "C"] = key.Key;
Worksheet.Cells[index, "D"] = key.Path;
index++;
}
}
}
// App.ScreenUpdating = true;
Marshal.ReleaseComObject(Worksheet); Worksheet = null;
Marshal.ReleaseComObject(Workbooks); Workbooks = null;
Marshal.ReleaseComObject(App); App = null;
}
public static InternalFormat Import(string FileName)
{
InternalFormat data = null;
// open excel document
Excel.Application App = new Excel.Application();
Excel.Workbooks Workbooks = App.Workbooks;
Excel.Workbook Workbook = Workbooks.Open(FileName);
Excel._Worksheet Worksheet = App.ActiveSheet;
Excel.Range Range = Worksheet.UsedRange;
// read document data
var Cells = Range.Value2;
int rowCount = Cells.GetLength(0);
int columnCount = Cells.GetLength(1);
data = new InternalFormat();
// read native and other cultures
data.Cultures = new List <string>();
for (int col = 3; col <= columnCount; col++)
{
if (Cells[1, col] != null)
{
// third column is NativeCulture
if (col == 3)
{
data.NativeCulture = Cells[1, col];
}
data.Cultures.Add(Cells[1, col]);
}
}
int index = 2;
int cultureCount = data.Cultures.Count;
List <InternalKey> keys = new List <InternalKey>(rowCount / 2);
// read all translation keys
for (; Cells[index, 1].ToString() != serviceData; index++)
{
InternalKey key = new InternalKey();
key.Key = InternalNamespace.SplitFullName(Cells[index, 2])[1];
key.Translations = new List <InternalText>(cultureCount);
for (int culture = 0; culture < cultureCount; culture++)
{
InternalText translation = new InternalText();
translation.Culture = data.Cultures[culture];
translation.Text = Cells[index, culture + 3];
if (translation.Text == null)
{
translation.Text = "";
}
else // replace \n to \r\n
{
translation.Text = Regex.Replace(translation.Text, "(?<!\r)\n", "\r\n");
}
key.Translations.Add(translation);
}
keys.Add(key);
}
int indexOfServiceData = index;
data.Subnamespaces = new List <InternalNamespace>(rowCount / 2);
InternalNamespace lastNS = null;
InternalRecord lastRec = null;
index++;
for (; index < rowCount + 1; index++)
{
string source = Cells[index, 1];
string ns = Cells[index, 2];
string key = Cells[index, 3];
string path = Cells[index, 4];
if (lastNS == null || lastNS.Name != ns)
{
lastNS = new InternalNamespace();
lastNS.Name = ns;
lastNS.Children = new List <InternalRecord>();
data.Subnamespaces.Add(lastNS);
lastRec = null;
}
if (lastRec == null || lastRec.Source != source)
{
lastRec = new InternalRecord();
lastRec.Source = source;
lastRec.Keys = new List <InternalKey>();
lastNS.Children.Add(lastRec);
}
InternalKey ikey = keys[index - indexOfServiceData - 1];
if (ikey.Key != key)
{
throw new FormatException("Unexpected key: " + key + "!");
}
ikey.Path = path;
ikey.parent = lastRec;
lastRec.Keys.Add(ikey);
}
// close excel and clear all headres
Marshal.ReleaseComObject(Range); Range = null;
Marshal.ReleaseComObject(Worksheet); Worksheet = null;
Workbook.Close(false, Type.Missing, Type.Missing);
Marshal.ReleaseComObject(Workbook); Workbook = null;
Workbooks.Close();
Marshal.ReleaseComObject(Workbooks); Workbooks = null;
App.Quit();
Marshal.ReleaseComObject(App); App = null;
return(data);
}