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
}

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

Solution 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

Solution 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

Comments

[Admin]

It should rather be a comment, not answer.

[Kaushik Das]

Thanks for the info Aalawlx. I am not sure if they would want to change the existing code by introducing Open XML SDK but let me try using the approach you mentioned. Thanks a lot again.

[Kaushik Das]

Thanks a lot for the informatin Rob. I am not too sure if the client would go for a total new solution but I will take a look at it.

[Jason Foglia]

This answers the question, from the title to the very last sentence above in the question. While the person asking the question provided code and a library doesn't mean it was apart of the question. "any other efficient way of reading xlsb" says it all, thus this was a great answer.

[Muflix]

it has dependency on Microsoft.ACE.OLEDB.12.0 which may not be installed on computer.

[Krzysiek Mastalerz]

I found this earlier, unfortunately aspose pricing would not fly in my project. Although the product is great.