Exporting Datatable to Excel - 'The file you are trying to open is in a different format' error

Hi

When I export from a datatable to Excel and try to open the file I get an error message "The file you are trying to open 'filename.xls' is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. '

I know you can change the registry: HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\Excel\Security]
"ExtensionHardening"=dword:00000000 but is there a better way to re-write the below code?

public static void Output_to_Excel_from_Datatable(DataTable dt, string _FileOutput, bool _showfile, bool _showmessage)
        {
            Excel.Application oXL;
            Excel._Workbook oWB;
            Excel._Worksheet oSheet;

            GC.Collect();// clean up any other excel guys hangin' around...
            oXL = new Excel.Application();
            oXL.Visible = false;

            //Get a new workbook.
            oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
            oSheet = (Excel._Worksheet)oWB.ActiveSheet;

            // Add column headings...
            int iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                oXL.Cells[1, iCol] = c.ColumnName;
            }
            // for each row of data...
            int iRow = 0;
            foreach (DataRow r in dt.Rows)
            {
                iRow++;

                // add each row's cell data...
                iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    oXL.Cells[iRow + 1, iCol] = r[c.ColumnName];
                }
            }

            oXL.Columns.AutoFit();

            // Global missing reference for objects we are not defining...
            object missing = System.Reflection.Missing.Value;
            bool _filelocked = true;

            do
            {
                bool _fileexist = File.Exists(_FileOutput);
                if (_fileexist == true)
                {
                    // Check if the file is locked
                    _filelocked = IsFileLocked(_FileOutput);

                    if (_filelocked == false)
                    {
                        // Delete File
                        File.Delete(_FileOutput);
                        _filelocked = true; // Need to reset to true so that we can run the code again to save the workbook
                    }
                    else
                    {
                        //prompt a dialog result yes or no whether user want to save or not
                        DialogResult CloseFile = MessageBox.Show("The Excel spreadsheet - " + _FileOutput + " is in use" + Environment.NewLine +
                            "If you would like to try again please close the Excel Template." + Environment.NewLine +
                           "Would you like to try again?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                        if (CloseFile == DialogResult.Yes)
                        {
                            _filelocked = true;
                        }
                        else
                        {
                            _filelocked = false;
                        }
                    }
                }
                else
                {
                    // Save the workbook
                    oWB.SaveAs(_FileOutput,
                        Excel.XlFileFormat.xlXMLSpreadsheet, missing, missing,
                        false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                        missing, missing, missing, missing, missing);

                    if (_showfile == true)
                    {
                        // If wanting to make Excel visible and activate the worksheet...
                        oXL.Visible = true;

                        //Activate the worksheet
                        Excel.Worksheet worksheet = (Excel.Worksheet)oXL.ActiveSheet;
                        ((Excel._Worksheet)worksheet).Activate();
                    }

                    _filelocked = false;
                }

            } while (_filelocked == true);


            // Tidy up Processing if not showing the file
            if (_showfile == false)
            {
                oWB.Close(null, null, null);
                oXL.Workbooks.Close();
                oXL.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                oSheet = null;
                oWB = null;
                oXL = null;
                GC.Collect();
                if (_showmessage == true)
                {
                    MessageBox.Show("The Excel File has been saved to " + _FileOutput);
                }
            }
        }

Select allOpen in new window