How to repair an excel file?

Question

I have an excel file which contains 4 worksheets. An outer system sends the excel files daily and my program reads then stores data to db. i have 3 files which may have been corrupted when my program tries to read the files it gives the error "External table is not in the expected format." when i try to open files in office interface it returns me Excel found ureadable content in the file.xlsx Do you want to recover the contents of this workbook? etc...

When i start program after i repaired the file with office excel and save again, it works. but i need to repair this files before program starts to read. Is there a way to repair excel files like office does?

I am using Microsoft.ACE.OLEDB.12.0;

Answer 1

You can use Excel Interop to open the file and repair as Excel does. But you can't use your program on a machine without MS Office. You may try third party libraries like:

• http://closedxml.codeplex.com/
• http://code.google.com/p/excellibrary/
• http://simpleooxml.codeplex.com/

The code for Excel Interop is as follows:

Missing missing = Missing.Value;
Application excel = new Application();
Workbook workbook = excel.Workbooks.Open(sourceFilePath,
    missing, missing, missing, missing, missing,
    missing, missing, missing, missing, missing,
    missing, missing, missing, XlCorruptLoad.xlRepairFile);
workbook.SaveAs(savedFile, XlFileFormat.xlWorkbookDefault,
    missing, missing, missing, missing,
    XlSaveAsAccessMode.xlExclusive, missing,
    missing, missing, missing, missing);
workbook.Close(true, missing, missing);

Answer 2

As your file comes from an external source it may be blocked as a security precaution. The solution could be to unblock the Excel file programmatically like this:

public class FileUnblocker {
    [DllImport("kernel32", CharSet = CharSet.Unicode, SetLastError = true)]
    [return: MarshalAs(UnmanagedType.Bool)]
    private static extern bool DeleteFile(string name);

    public bool Unblock(string fileName) {
        return DeleteFile(fileName + ":Zone.Identifier");
    }
}

Taken from this answer: Unblock File from within .net 4 c#