public static void SexUpWorksheets()
{
Tables.Clear();
MetaData.Clear();
int index = 1;
foreach (Excel.Worksheet sheet in Globals.Sexel.Application.Worksheets)
{
try
{
if (sheet.Name.StartsWith(WorksheetName_MetaDataPrefix))
{
MetaData.BuildFrom(sheet);
}
else if (sheet.Name.StartsWith(WorksheetName_TablePrefix))
{
string suffix = sheet.Name.Substring(WorksheetName_TablePrefix.Length).Trim();
if (suffix.Length == 0)
{
throw new Exception("Missing name. Sheet name has format 'Table <name>' where <name> is an alphanumeric string optionally padded with spaces");
}
var table = new TableParser(suffix, sheet);
Tables.Add(table);
}
index++;
}
catch (Exception ex)
{
Sexel.ShowMessage(string.Format("Error in worksheet #{0} '{1}':\n{2}", index, sheet.Name, ex.Message));
}
}
}
public static void AppendRowDef(StringBuilder sb, TableParser table)
{
sb.Append("(struct ");
AppendTableNameToLocalStructName(sb, table);
sb.Append("Row\n");
int typeRowIndex = table.FirstRowIndex - 2;
int nameRowIndex = table.FirstRowIndex - 1;
Excel.Range item;
for (int i = table.FirstColumnIndex; i < table.FinalColumnIndex; ++i)
{
string fieldName = table.GetCellText(i, nameRowIndex, out item);
string fieldType = table.GetCellText(i, typeRowIndex, out item);
sb.Append(" (");
AppendTableColumnTypeToSXYType(sb, fieldType, table);
sb.Append(" ");
AppendTableColumnNameToSXYIdentifier(sb, fieldName, table);
sb.Append(")\n");
}
sb.AppendFormat(")\n");
}
public static void AppendTableNameToLocalStructName(StringBuilder sb, TableParser table)
{
bool isFirst = true;
int currentLength = sb.Length;
foreach (char c in table.Name)
{
if (isFirst)
{
if (!IsCapital(c))
{
throw new Exception(string.Format("Table {0} -> the first character of the table name must be [A-Z]", GetId(table)));
}
sb.Append(c);
isFirst = false;
continue;
}
if (c == ' ')
{
continue;
}
if (!IsAlphaNum(c))
{
throw new Exception(string.Format("Table {0} -> illegal character in table name. All characters must be alphanumeric", GetId(table)));
}
sb.Append(c);
}
if (currentLength == sb.Length)
{
throw new Exception(string.Format("Table {0} -> blank table name. All characters must be alphanumeric", GetId(table)));
}
}
public static void Add(TableParser table)
{
tables.Add(table);
}
public static void AppendTableColumnNameToSXYIdentifier(StringBuilder sb, string fieldName, TableParser table)
{
bool isFirst = true;
int currentLength = sb.Length;
foreach (char c in fieldName)
{
if (isFirst)
{
if (!IsLowerCase(c))
{
throw new Exception(string.Format("Table {0} -> the first character of the table name must be a lower case letter [a-z]", GetId(table)));
}
sb.Append(c);
isFirst = false;
continue;
}
if (!IsAlphaNum(c))
{
throw new Exception(string.Format("Table {0} -> illegal character in table name. All characters must be alphanumeric", GetId(table)));
}
sb.Append(c);
}
if (currentLength == sb.Length)
{
throw new Exception(string.Format("Table {0} -> blank table name. All characters must be alphanumeric", GetId(table)));
}
}
public static void AppendTableColumnTypeToSXYType(StringBuilder sb, string fieldType, TableParser table)
{
string sxyType;
if (!MapXlTypeToSXYType.TryGetValue(fieldType, out sxyType))
{
var errBuilder = new StringBuilder(1024);
errBuilder.AppendFormat("Table {0} -> Unknown type [{1}]. Must be one of ", GetId(table), table.Name);
AppendAllTypesToString(errBuilder);
throw new Exception(errBuilder.ToString());
}
sb.Append(sxyType);
}
private static string GetId(TableParser table)
{
Excel.Range item;
table.GetCellText(3, table.FirstRowIndex, out item);
return(item.AddressLocal[true, true].Replace('$', ' '));
}
