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

Open in new window

elmbrookAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LearnedCommented:
Did you try changing the extension to .xlsx?
0
vastoCommented:
You are trying to create a xlXMLSpreadsheet with extension XLS. Either use XLSX or change the type of the spreadsheet
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
elmbrookAuthor Commented:
I ended up saving the file in the correct format.  

oWB.SaveAs(_FileOutput,
                      Excel.XlFileFormat.xlOpenXMLWorkbook, missing, missing,
                      false, false, Excel.XlSaveAsAccessMode.xlNoChange,
                      missing, missing, missing, missing, missing);
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.