WPF [SOLVED]: C# exporting from listview to csv – Excel application vs. FileStream

WPF [SOLVED]: C# exporting from listview to csv – Excel application vs. FileStream

Home Forums Frameworks WPF WPF [SOLVED]: C# exporting from listview to csv – Excel application vs. FileStream

Tagged: , , ,

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #246865

    Cloudy Point
    Keymaster

    QuestionQuestion

    I just have a curiosity question more than anything. I am working on a wpf app that exports from a listview to a csv. The code I wrote (which works) is below:

            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);
        }
    

    As mentioned, this works, but is slow. I have Excel set to visible during the testing phase, and noticed it takes about 7 seconds just to open. Then another 2 to fill 10 rows.

    I am also writing code to do the reverse, import from excel into the listview. For this, I used a StreamReader object, and the result is almost immediate. So I thought changing the code for the export would allow me the same speed. I tried this code:

                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");
    

    But is seems exponentially slower, like 40 seconds to return the Message Box. Also, I noticed that even though the loop is complete, and shows the Message, it seems the stream is still writing. If I open the file too quickly is states it is in use by “Another User”. So by the time the file is available to me it is actually closer to a minute.

    I’m just wondering why the difference in speed read vs write using FileStream. Is it something I borked on implementation, or is this a known issue? If interacting with Excel is the way to go (I’d rather not as not all machines may have Excel installed) is there a way to shorten that initial 6 or 7 second delay?

    #246866

    Cloudy Point
    Keymaster

    Accepted AnswerAnswer

    Try with rewriting your function to:

    StringBuilder builder = new StringBuilder();
        builder.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(',');
            .Append(Environment.NewLine);
    
    foreach (MyItem item in MyItems) 
    {  
        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)
            .Append(Environment.NewLine);
    } 
    
    using(FileStream srcFS = new FileStream(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory) + "\testingout.csv", FileMode.CreateNew, FileAccess.Write))
    {
        using(StreamWriter srcWrt = new StreamWriter(srcFS, System.Text.Encoding.Default)) 
        {
            srcWrt.WriteText(builder.);
        } 
    } 
    

    I’d guess it’s that you write to file every loop that slows down your code. If you instead write to file in one batch, when the string is complete. That is usually a better option

    Source: https://stackoverflow.com/questions/47947591/c-exporting-from-listview-to-csv-excel-application-vs-filestream
    Author: user3532232
    Creative Commons License
    This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.