Jump to content

FileStream vs Excel Application


Recommended Posts

  • Moderators

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?

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites

I know NOTHING about c#.

But I noticed a dramatic decrease in execution time when I rewrote the Excel UDF. The old UDF called an Excel method for each cell to write data. The new UDF populates an array and then writes the whole array by calling an Excel method only once.
In your case you should see a 25:1 ratio.
Please check the Excel UDF to get an idea how it would speed up your code :)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to post
Share on other sites
  • Moderators

Thanks, water, I was just doing that actually. The conversion from list view object to array is giving me fits, but it looks like that may be a bether way to go. Still strange why Filestream is so slow.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites

have you tried this person's answer on stack exchange?

https://stackoverflow.com/questions/13139213/fastest-way-to-read-from-and-write-to-excel-using-c-sharp

and at GitHub, and Excel Data Reader

https://github.com/ExcelDataReader/ExcelDataReader

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Link to post
Share on other sites
  • Moderators

It ended up being a case of blatant stupidity. With the WriteLine inside the loop of course it was going to slow down. By doing it properly, building the entire StringBuilder object and then writing only once, it completes 1000 rows by 25 columns in about 2 seconds.

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites

I’m still going to play with that ExcelReader lol. I need that anyway to verify my installs when I do automated testing because our stupid company keep the control sheet in Excel

Edited by Earthshine

My resources are limited. You must ask the right questions

 

Link to post
Share on other sites
  • 1 year later...
  • 1 month later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By argumentum
      ...trying to get the MonthCal font size, given that when loading it ( GUICtrlCreateMonthCal() ), this Win32 control has the font size auto-adjusted by the OS and working with display scaling, getting the font that is visually congruent, getting this value should make it the perfect font size for my GUI.
      My question is how to get this ( https://docs.microsoft.com/en-us/dotnet/api/system.windows.forms.monthcalendar.size?view=windowsdesktop-6.0 ) working from within AutoIt3.
      This should work in any PC given that the .NET supports this call in every version of .NET
      Thanks. ( I have no clue of C# or .NET )
    • By Skeletor
      Hi All,
      So I was searching through the internet and found a plethora of information to learn C#. 
      However, some are way below par and does not explain very well. 

      So, anyone can suggest a proper website or eBook or video course explaining C#?
       
      I've learnt AutoIt (still I'm learning it) by making small programs, reading the forums, going through the F1 guide (huge help, seriously this helps alot) and reading the Wiki.
      Any direction would be appreciated, just not off the edge of a cliff.. 
    • By Colduction
      Hi AutoIt programmers, excuse me for bothering you with multiple topics.

      In AutoIt we can use Number() function to convert Hex string to number but it's output is different of C# output & and i wanna make it's output like AutoIt code.

      For e.g I use this in AutoIt:
      Local $dBinary = Binary("Hello") ; Create binary data from a string. Local $dExtract = Number(BinaryMid($dBinary, 1, 5)) ConsoleWrite($dExtract & @CRLF) And i use this for C#:
      using System; using System.Text; //NameSpace Is Use of Project Name namespace TEST { class Program { public static void Main(string[] args) { //declaring a variable and assigning hex value string dd = ToHex("Hello", Encoding.ASCII); decimal d = Int64.Parse(dd, System.Globalization.NumberStyles.HexNumber); Console.WriteLine("Result: " + d); //hit ENTER to exit Console.ReadLine(); } public static string ToHex(string sInput, Encoding oEncoding, bool b0x_Prefix = false) { byte[] a_binaryOutput = oEncoding.GetBytes(sInput); string sOutput = BitConverter.ToString(a_binaryOutput).Replace("-", ""); if (b0x_Prefix == false) { return sOutput; } else { return "0x" + sOutput; } } } }
      I say once again that excuse me for creating new topic, in fact i'm making a library for GAuthOTP from a topic in AutoIt.
    • By ScrapeYourself
      I didn't like the search time of Simple Native Image Search, and being on windows 10 64bit, I couldn't get ImageSearchDll.dll to work properly.
      So I started researching image search routines and found this excellent post and set of replies find-a-bitmap-within-another-bitmap.

      I really liked the pattern that the Simple Native Image Search used, the clipboard usage and the method of searching. Although I think it could be improved by using some short circuit techniques to return sooner, like consecutive matched > 65% return | matched total > 85% return) , and I wanted the function to manage the click on the found image as well.
      So I just did a bit more research and a few trips to MSDN and stackoverflow, these two snippets allow me to replicate KyleJustKnows code, and click.  Another feature is that it also saves the image it captures to disk, so that if the image is not found you can check what was captured, and alternatively cut out a new search image to use.
      private void PrintScreen() { keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0); keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0); } public Bitmap CaptureScreenPrtSc() { PrintScreen(); if (Clipboard.ContainsImage()) { using (Image img = Clipboard.GetImage()) { img.Save("ClipBoard.PNG", ImageFormat.Png); return new Bitmap(img); } } return default; } [DllImport("user32.dll")] [return: MarshalAs(UnmanagedType.Bool)] private static extern bool GetCursorPos(out MousePoint lpMousePoint); [DllImport("user32.dll")] private static extern void mouse_event(int dwFlags, int dx, int dy, int dwData, int dwExtraInfo); private MousePoint GetCursorPosition() { var gotPoint = GetCursorPos(out MousePoint currentMousePoint); if (!gotPoint) { currentMousePoint = new MousePoint(0, 0); } return currentMousePoint; } private void MouseEvent(MouseEvents value) { MousePoint position = GetCursorPosition(); mouse_event ((int)value, position.X, position.Y, 0, 0) ; }
      So after managing to compile the dll with COM support, with much reading of this forum and many posts from paulpmeier, ptrex, LarsJ, and others about loading .net, I managed to get this all working.

      Here is the BotIt Core:
      ; BotIt Core Global $sPath = "BotIt.dll" Global $RegAsmPath = "C:\Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe" Func BotIt_StartUp() RegisterBotIt() OnAutoItExitRegister("UnregisterBotIt") EndFunc ;==>BotIt_StartUp Func RegisterBotIt() RunWait($RegAsmPath & " /register /codebase /tlb " & $sPath, @ScriptDir, @SW_HIDE) EndFunc ;==>RegisterBotIt Func UnregisterBotIt() FileDelete("Step.txt") RunWait($RegAsmPath & " /unregister " & $sPath, @ScriptDir, @SW_HIDE) EndFunc ;==>UnregisterBotIt Func ActivateAndSearch($sTitle, $sImgPath, $bClick = True) WinActivate($sTitle) Sleep(1000) $oBotIt = ObjCreate("BotIt.DetectImageAndClick") ConsoleWrite($sImgPath & @CRLF & $bClick & @CRLF) $bRet = $oBotIt.FindAndClick($sImgPath, $bClick) Return $bRet EndFunc ;==>ActivateAndSearch  
      Usage:
      Do Sleep(500) Until ActivateAndSearch("Window Title", "PathToFile")

      I hope you enjoy!
      Regards,
      ScrapeYourself
       
       
      BotIt.cs
    • By DiegoCorradini
      Hi all,
      I have a problem to handle the controls of an application.
      Using AutoIT Windows Tool I can get only the Window (see Summary of the picture).
      Any tips to get the controls without knowing the name?
      (PS Using TestStack.White everything works, however I want the HIDE application feature of AutoIT). 
       
      Many thanks

×
×
  • Create New...