Open a CSV file in Excel via C#?

Question

Using a solution presented by Max Galkin, I put together the following procedure which transfers all data in a ListView to a .CSV file. I'm having issues with 2 parts however:

1. I have not figured out how to successfully open the newly created .CSV when user clicks YES on the appropriate dialogue.
2. It seems that some users (on Virtual PC's unlike myself) are not able to open or view the the new file unless they first close down the application. I believe this is due to the Application still having a process tied to the created file. The message that appears when trying to open the file is: 'FileName.csv' cannot be accessed. The file may be corrupted, located on a server that is not responding, or read-only.

Here is my current code:

private void btnCSVExcel_Click(object sender, EventArgs e)
        {
            if (!Directory.Exists(@"C:\TEMP\"))
            {
                Directory.CreateDirectory(@"C:\temp\");
            }
            if (!Directory.Exists(@"C:\temp\Exported CSV Files\"))
            {
                Directory.CreateDirectory(@"C:\temp\Exported CSV Files\");
            }

            string csvPath = @"C:\temp\Exported CSV Files\";

            ListViewToCSV(lvData, csvPath, false);
        }

        // https://mcmap.net/q/1630765/-export-listview-to-csv
        public static void ListViewToCSV(ListView listView, string filePath, bool includeHidden)
        {
            string csvFileName = filePath + DateTime.Now.ToString("yyyy-MM-dd-hh.mm.ss.ffffff") + ".csv";

            //make header string
            StringBuilder result = new StringBuilder();
            WriteCSVRow(result, listView.Columns.Count, i => includeHidden || listView.Columns[i].Width > 0, i => listView.Columns[i].Text);

            //export data rows
            foreach (ListViewItem listItem in listView.Items)
            {
                WriteCSVRow(result, listView.Columns.Count, i => includeHidden || listView.Columns[i].Width > 0, i => listItem.SubItems[i].Text);
            }

            File.WriteAllText(csvFileName, result.ToString());

            var openCSVFile = MessageBox.Show("Export Complete. CSV file saved as: " + csvFileName + ". \n\n Open File Now?", "CSV Exported", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
            if (openCSVFile == DialogResult.Yes)
            {
                // NEED TO OPEN THE CSV FILE IN EXCEL....?
                File.Open(csvFileName, FileMode.Open, FileAccess.ReadWrite, FileShare.None);
            }
        }

        private static void WriteCSVRow(StringBuilder result, int itemsCount, Func<int, bool> isColumnNeeded, Func<int, string> columnValue)
        {
            bool isFirstTime = true;
            for (int i = 0; i < itemsCount; i++)
            {
                if (!isColumnNeeded(i))
                    continue;

                if (!isFirstTime)
                    result.Append(",");
                isFirstTime = false;

                result.Append(String.Format("\"{0}\"", columnValue(i)));
            }
            result.AppendLine();
        }

Anyone have any thoughts for how I might resolve these last 2 issues? I thought File.Open() may suffice for the first issue, but visually nothing occurs and the document does not open.

Adnah

This worked for me. – Babi

Answer 1

1) Excel registers itself as the default handler for .csv files. To open any file using the default file handler for the type, just do this:

Process.Start(@"c:\full\path\to\file.csv");

2) File.WriteAllText should close the file at the end. The code you show above also has a line that reads File.Open(csvFileName... that opens the file in exclusive write mode. Since you don't close that file, Excel can't open it until either a) the process terminates or b) the file is closed by the garbage collector. I bet the garbage collector hasn't yet run in cases where the access-denied error occurs.

Final recommendation: Remove the File.Open and replace with Process.Start and you may see different results.

Burglarize

Thanks for responding Michael! I tried out your solution to issue 1 based on your comment towards Sebs solution and your point differences. It appears to be working. I did actually have a reference to Excel Interop from a different Export procedure, but that one kept timing out due to my user not having enough processing power on a virtual machine to load all the data directly into an Excel spreadsheet (For normal PC users it worked fine). Any thoughts on issue #2? – Adnah

Just saw your part on #2, sorry. I will give it a shot! – Adnah

Thanks for the help Michael! That appears to have done the trick :) – Adnah

Answer 2

You need to add a reference to the Microsoft Excel Interop library in your project :

using Excel = Microsoft.Office.Interop.Excel;

Then you can open the CSV file by using this code :

    static void OpenCSVWithExcel(string path)
    {
        var ExcelApp = new Excel.Application();
        ExcelApp.Workbooks.OpenText( path, Comma:true);

        ExcelApp.Visible = true;
    }

Mechanics

This will work, but it is error prone and requires an additional dependency. I recommend just using Process.Start against the csv file instead of using automation. – Burglarize

I seen your Answer but I'm not agreeing with you. Process.Start will start the CSV with the default application in Windows. If an user mapped the CSV file with textpad, the file will not open in Excel. – Mechanics

Agreed. In cases where I absolutely have to have this sort of thing work, I manually look up the default value of registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\excel.exe and manually start the process. Automation just has too many gotchas. (13 years experience with Office automation here.) – Burglarize

'Automation just has too many gotchas' : I can't contradict you on that ;) (13 years experience with Office Automation too) – Mechanics