private void button1_Click(object sender, EventArgs e)
{
Utl u = new Utl();
u.dbg("Button1 Clicked, Procedure Started");
FileInfo inputf = new FileInfo(textBox1.Text);
u.dbg("Using File info: " + inputf.FullName);
u.dbg("Executing procedure VonExcel");
XElement xmlRoot = VonExcel(inputf, textBox2, progressBar1, u);
GC.Collect(); //With this, Excel will not lock up!
u.dbg("VonExcel Garbage Collection");
xmlRoot.Save(@".\Result.xml");
u.dbg("xml Saved.");
//Process.Start(@".\Result.xml");
//EXCEL => xml conversion completed
//Next step is go get xml to a structured database
// ...
u.dbg("Creating madb Elements");
XElement madb = new XElement("M-Agent_Database");
XElement madb_server = new XElement("SERVER");
//Need a method String Get_Value_By_Loc(XElement xmlRoot, String Loc){}
//Total fields to fetch data:
//madb_hostname_c1v
//madb_hostname_c1p
//madb_hostname_c1s
//madb_hostname_c2v
//madb_hostname_c2p
//madb_hostname_c2s
//madb_hostname_c3v
//madb_hostname_c3p
//madb_hostname_c3s
Add_Ma_Element maer = //Delegate to add 2 strings to an XElement
(XElement xe, string s1, string s2)
=> xe.Add(new XElement(s1, Get_Value_By_Loc(xmlRoot, s2)));
Add_Ma_Element_cbx maex = //Variation: Find CheckBox
(XElement xe, string s1, string[] s2)
=> xe.Add(new XElement(s1, Get_Value_By_Cbx(xmlRoot, s2)));
XElement madb_hostname_c1v =
new XElement(Get_Value_By_Loc(xmlRoot, "H49"));
maer(madb_hostname_c1v, "IP_Addr", "H50");
maer(madb_hostname_c1v, "Cluster_VIP", "H49");
maer(madb_hostname_c1v, "Cluster_PRI", "H51");
maer(madb_hostname_c1v, "Cluster_SEC", "H64");
XElement madb_hostname_c1p =
new XElement(Get_Value_By_Loc(xmlRoot, "H51"));
maer(madb_hostname_c1p, "IP_Addr", "H52");
maer(madb_hostname_c1p, "Maker", "H53");
maer(madb_hostname_c1p, "Model", "H54");
maer(madb_hostname_c1p, "CPU_Num", "H55");
maer(madb_hostname_c1p, "CPU_Micro", "H56");
//Need a method string Get_Value_By_Cbx(XElement xmlRoot, string() Loc){}
// Loc is the string of *CheckBox* Locations
string[] addr_OS = { "$H$57", "$H$58", "$H$59", "$J$57", "$J$58" };
maex(madb_hostname_c1p, "OS", addr_OS);
maer(madb_hostname_c1p, "Version", "H60");
maer(madb_hostname_c1p, "Bit", "H61");
maer(madb_hostname_c1p, "Virtual_Split", "H62");
maer(madb_hostname_c1p, "Virtual_Index", "H63");
maer(madb_hostname_c1p, "Cluster_VIP", "H49");
maer(madb_hostname_c1p, "Cluster_PRI", "H51");
maer(madb_hostname_c1p, "Cluster_SEC", "H64");
XElement madb_hostname_c1s =
new XElement(Get_Value_By_Loc(xmlRoot, "H64"));
maer(madb_hostname_c1s, "IP_Addr", "H65");
maer(madb_hostname_c1s, "Maker", "H66");
maer(madb_hostname_c1s, "Model", "H67");
maer(madb_hostname_c1s, "CPU_Num", "H68");
maer(madb_hostname_c1s, "CPU_Micro", "H69");
string[] addr_OS2 = { "$H$70", "$H$71", "$H$72", "$J$70", "$J$71" };
maex(madb_hostname_c1s, "OS", addr_OS2);
maer(madb_hostname_c1s, "Version", "H73");
maer(madb_hostname_c1s, "Bit", "H74");
maer(madb_hostname_c1s, "Virtual_Split", "H75");
maer(madb_hostname_c1s, "Virtual_Index", "H76");
maer(madb_hostname_c1s, "Cluster_VIP", "H49");
maer(madb_hostname_c1s, "Cluster_PRI", "H51");
maer(madb_hostname_c1s, "Cluster_SEC", "H64");
textBox2.AppendText(madb_hostname_c1v.ToString() + "\r\n");
textBox2.AppendText(madb_hostname_c1p.ToString() + "\r\n");
textBox2.AppendText(madb_hostname_c1s.ToString() + "\r\n");
u.dbg("MA information get.");
u.dbg_done();
}
public static XElement VonExcel
(FileInfo Input_File, TextBox WriteTo, ProgressBar update_pbar, Utl u)
{
u.dbg("VonExcel: Started");
XElement xmlRoot = new XElement("EXCEL_DATA");
XElement xmlGrid = new XElement("MA_GRID"); //Combine into on xml
u.dbg("VonExcel: Root XElements created.");
update_pbar.Maximum = 1000;
update_pbar.Value = 100;
FileInfo inputf = new FileInfo(Input_File.FullName);
u.dbg("VonExcel: Opening up Excel Application.");
Excel.Application xlApp = new Excel.Application();
Excel.Workbooks xlWorkBooks = xlApp.Workbooks;
Excel.Workbook xlWbk = xlWorkBooks.Open(inputf.FullName);
Excel.Sheets xlWorksheets = xlWbk.Worksheets;
Excel.Worksheet xlWks = xlWbk.ActiveSheet;
Excel.Range xlRange = xlWks.UsedRange;
update_pbar.Value += 10;
u.dbg("VonExcel: Excel Application Loaded Successfully.");
ProgressContext <Excel.Range> IEmRange =
new ProgressContext <Excel.Range>(
from Excel.Range rng in xlRange
where rng.Value != null
select rng);
u.dbg("VonExcel: Making LINQ Query for Non-Empty Used Ranges.");
IEmRange.UpdateProgress += (sender0, e0) =>
{
update_pbar.Value += 1;
//u.dbg(sender0.ToString());
//u.dbg(e0.ToString());
};
u.dbg("VonExcel: UpDate Progress Behavior Delegated.");
u.dbg("VonExcel: Creating Excel Grid XML.");
for (int r = 1; r < 100; r++)
{
XElement xmlRow = new XElement("Row");
xmlRow.Add(new XElement("Name", r));
xmlRow.Add(new XElement("Top", xlWks.Cells[r, 1].Top));
xmlRow.Add(new XElement("Top_plus",
xlWks.Cells[r, 1].Top + xlWks.Cells[r, 1].Height));
xmlRow.Add(new XElement("Value", 0));
xmlGrid.Add(xmlRow);
}
for (int c = 1; c < 24; c++)
{
XElement xmlCol = new XElement("Col");
xmlCol.Add(new XElement("Name", ((char)(64 + c)).ToString()));
xmlCol.Add(new XElement("Left", xlWks.Cells[1, c].Left));
xmlCol.Add(new XElement("Left_plus",
xlWks.Cells[1, c].Left + xlWks.Cells[1, c].Width));
xmlCol.Add(new XElement("Value", 0));
xmlGrid.Add(xmlCol);
}
u.dbg("VonExcel: Grid XML Created.");
u.dbg("VonExcel: Populating Range XML.");
foreach (Excel.Range rng in IEmRange)
{
XElement xmlE = new XElement("EXCEL_ELEMENT");
//WriteTo.AppendText(rng.Address +
//" : " + rng.Address.GetType() +
//" : " + rng.Value.ToString());
xmlE.Add(new XElement("Name",
rng.Address.Replace("$", "")));
xmlE.Add(new XElement("Address", rng.Address));
xmlE.Add(new XElement("Left", rng.Left));
xmlE.Add(new XElement("Top", rng.Top));
xmlE.Add(new XElement("Value", rng.Value.ToString()));
xmlE.Add(new XElement("Option", 0));
xmlRoot.Add(xmlE);
}
u.dbg("VonExcel: Range XML Populated.");
ProgressContext <Excel.Shape> IEmShapes =
new ProgressContext <Excel.Shape>(
from Excel.Shape s in xlWks.Shapes
where s.Name.Contains("Check Box") &&
(s.OLEFormat.Object.Value == 1)
select s);
u.dbg("VonExcel: Making a LINQ Query for Checkbox.");
IEmShapes.UpdateProgress += (sender0, e0) =>
{
update_pbar.Value += 1;
};
u.dbg("VonExcel: Populating Checkbox XML.");
foreach (Excel.Shape s in IEmShapes)
{
XElement xmlE = new XElement("EXCEL_ELEMENT");
//WriteTo.AppendText(
//s.OLEFormat.Object.Value.ToString());
xmlE.Add(new XElement("Name", s.Name));
xmlE.Add(new XElement("Address",
Extract_Address(s.Left, s.Top, s.Name, xmlGrid)));
xmlE.Add(new XElement("Left", s.Left));
xmlE.Add(new XElement("Top", s.Top));
xmlE.Add(new XElement("Value",
s.OLEFormat.Object.Value.ToString()));
xmlRoot.Add(xmlE);
}
u.dbg("VonExcel: Checkbox XML Populated.");
// This code fetches all top for rows, and left for columns
xlWbk.Close();
xlWorkBooks.Close();
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
Marshal.ReleaseComObject(xlWorkBooks);
Marshal.ReleaseComObject(xlWbk);
Marshal.ReleaseComObject(xlWorksheets);
Marshal.ReleaseComObject(xlWks);
Marshal.ReleaseComObject(xlRange);
u.dbg("VonExcel: Closing Workbook Application, Recycling. Returning Value.");
return(xmlRoot);
}
