ExcelDataReader reads all invisible sheets

Question

In Excel there is a feature to hide some worksheets. I am reading a document which contains these kind of sheets and I want to ignore them.

This is the place which I can hide, or unhide worksheets:

1. On the Home tab, in the Cells group, click Format.
2. Under Visibility, click Hide & Unhide, and then click Unhide Sheet.

How to get list of ONLY Excel VISIBLE worksheet names in Excel using ExcelDataReader?

Answer 1

If using the reader interface, the IExcelDataReader.VisibleState property returns the visibility state of the currently read sheet.

If using .AsDataSet(), the same value can be retreived from DataTable.ExtendedProperties["visiblestate"]

Answer 2

How to get list of visible worksheet names in Excel using ExcelDataReader?

// Prepare your reader by 
var stream = File.Open(yourExcelFilename, FileMode.Open, FileAccess.Read);
var excelDataReader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream);

// This variable will store visible worksheet names
List<string> visibleWorksheetNames;

// Use a loop to read workbook    
visibleWorksheetNames = new List<string>();
for (var i = 0; i < excelDataReader.ResultsCount; i++)
{
    // checking visible state
    if (excelDataReader.VisibleState == "visible")
    {
        visibleWorksheetNames.Add(excelDataReader.Name);
    }

    excelDataReader.NextResult();
}

Answer 3

Read only visible sheets to DataSet:

using (var stream = File.Open("test.xlsx", FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            FilterSheet = (tableReader, sheetIndex) => tableReader.VisibleState == "visible",
        });
    }
}

Answer 4

Use reader.RowHeight. Setting RowHeight = 0 results in a hidden row.