By
JLogan3o13
Not one usually to post non-AutoIt things, but as I have this question on StackExchange I thought I would throw it up here as well for any of our C# folks:
I currently have an AutoIt GUI that calls a powershell script; the intent is to allow low-level technicians to batch create VMs in vSphere. Due to some changes in requirements from the customer, I am re-writing as a wpf app. The app itself is complete and working; this is more of a curiosity question.
I have two methods attached to buttons on the GUI - one to pull all the data out of a listview and export to csv and another to do the reverse; importing from csv to the listview element. I wrote the export first, and went with manipulating the Excel application:
private void Launch(object sender, RoutedEventArgs e)
{
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
oExcel.Visible = true;
Microsoft.Office.Interop.Excel.Workbook oWorkBook = oExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);
Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oExcel.ActiveSheet;
int row = 2; //allow for header row
int column = 1;
oSheet.Cells[1, 1] = "Name";
oSheet.Cells[1, 2] = "CPU";
oSheet.Cells[1, 3] = "RAM";
oSheet.Cells[1, 4] = "IP Address";
oSheet.Cells[1, 5] = "Subnet Mask";
oSheet.Cells[1, 6] = "Port Group";
oSheet.Cells[1, 7] = "Default Gateway";
oSheet.Cells[1, 8] = "DNS";
oSheet.Cells[1, 9] = "Description";
oSheet.Cells[1, 10] = "Template";
oSheet.Cells[1, 11] = "Host";
oSheet.Cells[1, 12] = "Site";
oSheet.Cells[1, 13] = "Folder";
oSheet.Cells[1, 14] = "DataStore";
oSheet.Cells[1, 15] = "Patch Method";
oSheet.Cells[1, 16] = "HDD1Size";
oSheet.Cells[1, 17] = "HDD1Format";
oSheet.Cells[1, 18] = "HDD2Size";
oSheet.Cells[1, 19] = "HDD2Format";
oSheet.Cells[1, 20] = "HDD3Size";
oSheet.Cells[1, 21] = "HDD3Format";
oSheet.Cells[1, 22] = "HDD4Size";
oSheet.Cells[1, 23] = "HDD4Format";
oSheet.Cells[1, 24] = "HDD5Size";
oSheet.Cells[1, 25] = "HDD5Format";
foreach (var oVM in MyItems)
{
oSheet.Cells[row, column] = oVM.Name;
oSheet.Cells[row, (column + 1)] = oVM.CPU;
oSheet.Cells[row, (column + 2)] = oVM.RAM;
oSheet.Cells[row, (column + 3)] = oVM.IP;
oSheet.Cells[row, (column + 4)] = oVM.Subnet;
oSheet.Cells[row, (column + 5)] = oVM.PortGroup;
oSheet.Cells[row, (column + 6)] = oVM.Gateway;
oSheet.Cells[row, (column + 7)] = oVM.DNS;
oSheet.Cells[row, (column + 8)] = oVM.Description;
oSheet.Cells[row, (column + 9)] = oVM.Template;
oSheet.Cells[row, (column + 10)] = oVM.Host;
oSheet.Cells[row, (column + 11)] = oVM.Site;
oSheet.Cells[row, (column + 12)] = oVM.Folder;
oSheet.Cells[row, (column + 13)] = oVM.Datastore;
oSheet.Cells[row, (column + 14)] = oVM.Patch;
oSheet.Cells[row, (column + 15)] = oVM.HDD1Size;
oSheet.Cells[row, (column + 16)] = oVM.HDD1Format;
oSheet.Cells[row, (column + 17)] = oVM.HDD2Size;
oSheet.Cells[row, (column + 18)] = oVM.HDD2Format;
oSheet.Cells[row, (column + 19)] = oVM.HDD3Size;
oSheet.Cells[row, (column + 20)] = oVM.HDD3Format;
oSheet.Cells[row, (column + 21)] = oVM.HDD4Size;
oSheet.Cells[row, (column + 22)] = oVM.HDD4Format;
oSheet.Cells[row, (column + 23)] = oVM.HDD5Size;
oSheet.Cells[row, (column + 24)] = oVM.HDD5Format;
row++;
}
oExcel.Application.ActiveWorkbook.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\example", 6);
}
It works, but it is slow. I have Excel set to visible for testing, and it is a good 6 or 7 seconds on a pretty high end box before the app even pops up. It then takes another 2 seconds to populate 11 rows (this could be in the hundreds of rows at some point).
I then wrote the code for the reverse, and decided to try a StreamReader object. The result, surprisingly, was almost immediate:
OpenFileDialog xls = new OpenFileDialog();
xls.Multiselect = false;
xls.Filter = "CSV files (*.csv)|*.csv";
xls.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory);
xls.ShowDialog();
string ins;
if (xls.FileName != null)
{
FileStream srcFS;
srcFS = new FileStream(xls.FileName, FileMode.Open);
StreamReader srcSR = new StreamReader(srcFS, System.Text.Encoding.Default);
do
{
ins = srcSR.ReadLine();
if (ins != null)
{
string[] parts = ins.Split(',');
MyItems.Add(new MyItem
{
Name = parts[0],
CPU = parts[1],
RAM = parts[2],
IP = parts[3],
Subnet = parts[4],
PortGroup = parts[5],
Gateway = parts[6],
DNS = parts[7],
Description = parts[8],
Template = parts[9],
Host = parts[10],
Site = parts[11],
Folder = parts[12],
Datastore = parts[13],
Patch = parts[14],
HDD1Size = parts[15],
HDD1Format = parts[16],
HDD2Size = parts[17],
HDD2Format = parts[18],
HDD3Size = parts[19],
HDD3Format = parts[20],
HDD4Size = parts[21],
HDD4Format = parts[22],
HDD5Size = parts[23],
HDD5Format = parts[24]
});
}
} while (ins != null);
srcSR.Close();
}
}
So, I thought I would go back and change the export to use the same method:
FileStream srcFS;
srcFS = new FileStream(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\\testingout.csv", FileMode.CreateNew, FileAccess.Write);
StreamWriter srcWrt = new StreamWriter(srcFS, System.Text.Encoding.Default);
StringBuilder header = new StringBuilder();
header.Append("Name").Append(',')
.Append("CPU").Append(',')
.Append("RAM").Append(',')
.Append("IP Address").Append(',')
.Append("Port Group").Append(',')
.Append("Default Gateway").Append(',')
.Append("DNS").Append(',')
.Append("Description").Append(',')
.Append("Template").Append(',')
.Append("Host").Append(',')
.Append("Site").Append(',')
.Append("Folder").Append(',')
.Append("Datastore").Append(',')
.Append("Patch").Append(',')
.Append("HDD1Size").Append(',')
.Append("HDD1Format").Append(',')
.Append("HDD2Size").Append(',')
.Append("HDD2Format").Append(',')
.Append("HDD3Size").Append(',')
.Append("HDD3Format").Append(',')
.Append("HDD4Size").Append(',')
.Append("HDD4Format").Append(',')
.Append("HDD5Size").Append(',')
.Append("HDDFormat").Append(',');
srcWrt.WriteLine(header);
foreach (MyItem item in MyItems)
{
StringBuilder builder = new StringBuilder();
builder.Append(item.Name).Append(',')
.Append(item.CPU).Append(',')
.Append(item.RAM).Append(',')
.Append(item.IP).Append(',')
.Append(item.Subnet).Append(',')
.Append(item.PortGroup).Append(',')
.Append(item.Gateway).Append(',')
.Append(item.DNS).Append(',')
.Append(item.Description).Append(',')
.Append(item.Template).Append(',')
.Append(item.Host).Append(',')
.Append(item.Site).Append(',')
.Append(item.Folder).Append(',')
.Append(item.Datastore).Append(',')
.Append(item.Patch).Append(',')
.Append(item.HDD1Size).Append(',')
.Append(item.HDD1Format).Append(',')
.Append(item.HDD2Size).Append(',')
.Append(item.HDD2Format).Append(',')
.Append(item.HDD3Size).Append(',')
.Append(item.HDD3Format).Append(',')
.Append(item.HDD4Size).Append(',')
.Append(item.HDD4Format).Append(',')
.Append(item.HDD5Size).Append(',')
.Append(item.HDD5Format);
srcWrt.WriteLine(builder);
}
MessageBox.Show("Task Complete");
What surprised me is this method is exponentially slower; on the order of 40 seconds to return the MsgBox. I also noticed that even though the loop is complete and shows the message, is seems the stream is still writing. If I open the file too quickly it shows that it is still in use by "Another User". So by the time the file is available to me it is actually closer to a minute for an 11-line csv.
I'm just curious at the difference in speed read vs write using FileStream. Is it something I borked on implementation (eminently possible) or is this a known issue? If interacting with Excel is the way to go (not ideal) is there something I could do to shorten the initial lag?
By Gowrisankar
By IgImAx
By NHD
By gf100
By JLogan3o13