Reading Excel xlsb files in C#

Question

There is a new requirement for my project to read various types of Excel files. I am able to read .xls and .xlsx files using the ExcelDataReader dll from Codeplex. The problem is when I try to read .xlsb files. ExcelDataReader cannot read from .xlsb files. Is there any other efficient way of reading xlsb files apart from using Microsoft.Office.Interop.Excel dll in server based applications .

IExcelDataReader excelReader = fileName.EndsWith(".xlsx")
                                               ? ExcelReaderFactory.CreateOpenXmlReader(stream)
                                               : ExcelReaderFactory.CreateBinaryReader(stream);
while (excelReader.Read())
{
     //myStuff read the file
}

Answer 1

The quickest and easiest solution is to use the product you have already made.

Simply use Excel Interop to do a save-as to convert the xlsb to xlsx, then read the new file as usual. Usually I wouldn't recommend using Excel Interop as it is extremely slow, but for just converting a file it is fast enough (assuming normal conditions).

I would recommend you use Office Open XML SDK where possible, for reading the resulting xml files.

Here is one I made earlier:

public class XlConversion
{
    public static void MarshalReleaseComObject(object comObject)
    {
        if ((comObject != null) && (Marshal.IsComObject(comObject)))
        {
            Marshal.ReleaseComObject(comObject);
        }
    }

    public static void ConvertTsvToExcel(string inputFullPath, string outputExcelFullPath, bool deleteInput = false)
    {
        if (String.IsNullOrWhiteSpace(inputFullPath))
        {
            throw new ArgumentOutOfRangeException(nameof(inputFullPath));
        }

        if (String.IsNullOrWhiteSpace(outputExcelFullPath))
        {
            throw new ArgumentOutOfRangeException(nameof(outputExcelFullPath));
        }

        var inputFilename = new FileInfo(inputFullPath);
        var xlFilename = new FileInfo(outputExcelFullPath);

        const int maxSupportedXlFilenameLength = 218;

        if (xlFilename.FullName.Length > maxSupportedXlFilenameLength)
        {
            throw new ArgumentOutOfRangeException(nameof(outputExcelFullPath), outputExcelFullPath, ("The full path filename (" + xlFilename.FullName.Length + " characters) is longer than Microsoft Excel supports (" + maxSupportedXlFilenameLength + " characters)"));
        }

        var excelApp = new Application();
        Workbooks wbs = excelApp.Workbooks;
        Workbook wb = wbs.Open(inputFilename.FullName);

        wb.SaveAs(xlFilename.FullName, XlFileFormat.xlOpenXMLWorkbook);

        try
        {
            wb.Close();
            //excel.Quit();
        }
        finally
        {
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();

            MarshalReleaseComObject(wb);
            MarshalReleaseComObject(wbs);
            MarshalReleaseComObject(excelApp);
        }

        if (deleteInput)
        {
            File.Delete(inputFilename.FullName);
        }
    }
}

Answer 2

LinqToExcel supports xlsb as well as xls and xlsx.

Basic usage of this library looks like this:

using (var excelQueryFactory = new ExcelQueryFactory(filePath))
{
     //access your worksheet LINQ way
     var worksheet = excelQueryFactory.Worksheet("worksheetName").Where(...);
}

More detailed tutorial