How to create template excel based on table columns order not forms control

[engahmedbarbary]

Problem

I need to modify function GetFormFields to Get Order Of column on table selected .

meaning if i have table name items have four fields itemcode,itemname,itemtech,itemsize

it must create template excel based on tables column order according to four columns above

Current it working according to order of controls on form and this is wrong .

so that if itemcode have tab index 3 it will show as number 3 and i dont need that

i need to order as table created

public static void CreateExcelTemplate(xForm frm)
{
try
{
if (frm.TableName == "") return;

Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook Book = null;
Microsoft.Office.Interop.Excel._Worksheet Sheet = null;
ExcelApp.Visible = false;
Book = ExcelApp.Workbooks.Add("WorkBook");
Sheet = Book.ActiveSheet;

List<string> Columns = new List<string>();
Dictionary<string, string> GlobalFields = SqlFactory.Queries.GetGlobalFieldsForTable(frm.TableName);
foreach (KeyValuePair<string, string> keys in GlobalFields)
{
Columns.Add(keys.Key);
}
foreach (string s in frm.AnotherKeys.Keys)
{
Columns.Add(s);
}
GetFormFields(frm.Controls, ref Columns);

String MaxColumn = ((String)(Convert.ToChar(Columns.Count / 26 + 64).ToString() + Convert.ToChar(Columns.Count % 26 + 64))).Replace('@', ' ').Trim();

Microsoft.Office.Interop.Excel.Range range = Sheet.get_Range("A2", MaxColumn + "2");
Sheet.ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, range, null,
Microsoft.Office.Interop.Excel.XlYesNoGuess.xlYes).Name = "Table1";

for (int i = 0; i < Columns.Count; i++)
{
if (Columns != null && Columns.ToString() != "")
{
string Sql = "SELECT " + (GlobalVariables.SysLang ? "ArabicCaption" : "LatinCaption")
+ " FROM " + GlobalVariables.ReferenceFileName + " WHERE TableName = '"
+ frm.TableName + "' AND FieldName ='" + Columns + "'";
Sheet.Cells[2, i + 1] = DataAccess.ExecuteScalar<string>(Sql);
Sheet.Cells[1, i + 1] = Columns.ToString();
Sheet.Rows[1].Hidden = true;
}
}

ExcelApp.Visible = true;
}
catch (Exception ex)
{
xExceptions.ShowExceptionInfo(ex, "FormManager : CreateExcelTemplate()");
}
}
public static void GetFormFields(Control.ControlCollection Controls, ref List<string> Columns)
{
try
{
var Ctrls = from ctrl in Controls.OfType<Control>() orderby ctrl.TabIndex select ctrl;
foreach (Control control in Ctrls)
{
if (control is xLabel)
{
xLabel lbl = control as xLabel;
if (lbl.FieldName != null && lbl.FieldName != "")
Columns.Add(lbl.FieldName);
}
if (control.Controls.Count > 0) GetFormFields(control.Controls, ref Columns);
}
}
catch (Exception ex)
{
xExceptions.ShowExceptionInfo(ex, "FormManager : GetFormFields()");
}
}

I need to order them as table created actually i need is

public static void GetFormFields(Control.ControlCollection Controls, ref List<string> Columns)
{
try
{
get columns from table this what i need according to order on table
}
catch (Exception ex)
{
xExceptions.ShowExceptionInfo(ex, "FormManager : GetFormFields()");
}
}

Continue reading...