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://stackoverflow.com/questions/1008556/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.

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.

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