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

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

Answer 2

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 3

Aspose.Cells API can be used to read XLSB file data conveniently. Besides XLSB, all other Excel formats e.g. XLS, XLSX, XLSM etc. data can be read easily with little code.

For demonstration, please see the following C# code, source XLSB file used inside the code and Console Output generated by the code for your reference.

C#

// Print message on Console
Console.WriteLine("Reading XLSB file in C# using Aspose.Cells API.");
Console.WriteLine("----------------------------------------------");

// Directory path of input and output files.
string dirPath = "D:/Download/";

// Load your source XLSB file inside the Aspose.Cells Workbook object.
Workbook wb = new Workbook(dirPath + "Source.xlsb");

// Access first worksheet.
Worksheet ws = wb.Worksheets[0];

// Access cells enumarator
System.Collections.IEnumerator iEnum = ws.Cells.GetEnumerator();

// Print the cells data in while loop on console.
while(iEnum.MoveNext())
{
    Cell cell = (Cell)iEnum.Current;
    Console.WriteLine(cell.Value);
}

Console Output

Reading XLSB file in C# using Aspose.Cells API.
----------------------------------------------
This is C3 data.
This is J4 data.
This is F6 data.
This is D9 data.
This is H10 data.
This is G15 data.
This is L17 data.
This is B20 data.

Snapshot of Source XLSB file used inside the Aspose.Cells C# Code

Snapshot of Console Output generated by the Aspose.Cells C# Code