Jump to content
JLogan3o13

FileStream vs Excel Application

Recommended Posts

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?

Share this post


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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


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

 

Share this post


Link to post
Share on other sites

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.

Share this post


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

 

Share this post


Link to post
Share on other sites

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

  • Similar Content

    • 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

    • By IgImAx
      Hi
       
      UPDATED Notes: ===============================================================================
      UPDATED: My main and second questions answered. Here the answers:
      To add/access WinMenuSelectItem you need to [Thanks to Fernando_Marinho]: Add AutoItX.Dotnet in Manage NuGet Packages Right Click in your Project -> Add -> Reference... -> COM ( Type Libraries )than, check the option AutoItX3 1.0 Type Library  using AutoItX3Lib; AutoItX3 au3 = new AutoItX3(); au3.WinMenuSelectItem("", ...) My full source code in C# exists in 11 posts in below. How to access those overloaded methods in AutoitX3 that are not accessible via above method!? Or how to fix AutoitX3 DLL Registration need in target computers without Autoit pre-installed on them!? Please check my post at 14 posts below!
      =============================================================================== Original Post:
       
       
      I was writing a small app in Autoit to close µTorrent app. It was working. Then I try to import AutoItX into C#, but unfortunately this method
      WinMenuSelectItem Couldn't find by IntelliSense and If I typed completely it still give me this message:
      Please check the image. I Google it and I found this QA at stackoverflow: Autoit error within C# application I saw they use this line:
      au = new AutoItX3Lib.AutoItX3Class(); I figure it how to add 'AutoItX3Lib' to project (by adding 'AutoItX3.dll' to reference) but again! When I use this line:
      var au = new AutoItX3Class(); I got this error message: Interop type 'AutoItX3Class' cannot be embedded. Use the applicable interface instead.
      My system info:
      Visual Studio 2017 Enterprise - v15.5.4
      X64 Windows 10 Enterprise 1607
      Thanks in advanced
      IgImAx

    • By Luigi
      Greetings, someone can give a exemple, how send a error from a C#'s dll to AutoIt?
      I use this line, to send an error... but, I want get a error code In AutoIt with macro @error, it's possible?
       
      throw new ArgumentException("arquivo map não existe", "value" ); In this way, work, I know ther are error, but, @errror always is zero.
      I don't want this, I want a number as error code.
      Can you help me?
       
      Best regards
    • By breakbadsp
      AutoIT AU3info doeas not detect all gui objects uniquely for .NET GUIs developed in C#.
      this is not working now i am using COM windows approach for this, But its very difficult.
      Please let me know if anyone has done it before.
    • By 5ervant
      What's the best way to receive file from a desktop app?
      app.exe will execute a cmd with "au3file.exe /path/of/the/file.xml" and the au3file.exe will get and delete that. Or else? THE MOST IMPORTANT PART OF THE QUESTION
      And best way to transfer file to a desktop app?
      au3file.exe do a $_POST request and the app.exe MUST HAVE a local HTTP server that can receive $_POST, but it looks heavy 'cause the app must have a server such XAMPP. au3file.exe execute a cmd with "app.exe /path/of/the/file.xml" and the app.exe will now get that file and delete. Or else?  
×
×
  • Create New...