Why would there be no data in the visualiser when there is valid data in the DataTable?

Question

I'm trying to build a wrapper for SpreadsheetLight that returns a DataSet from any .xlsx document passed through it. However, I seem to be having a problem with DataRows not being added to a temporary DataTable.

Here's part of the code that parses a worksheet and generates a DataTable from it:

    public DataSet ReadToDataSet(string fileName)
    {

        using (var wb = new SLDocument(fileName))
        {
            var set = new DataSet(GenerateTitle(wb.DocumentProperties.Title));

            foreach (var wsName in wb.GetWorksheetNames())
            {

                var ws = wb.SelectWorksheet(wsName);

                // Select worksheet returns a bool, so if it comes back false, try the next worksheet instead.
                if (!ws) continue;

                // Statistics gives indecies of the first and last data cells
                var stats = wb.GetWorksheetStatistics();

                // Create a new DataTable for each worksheet
                var dt = new DataTable(wsName);

                //var addDataColumns = true;

                for (var colIdx = stats.StartColumnIndex; colIdx < stats.EndColumnIndex; colIdx++)
                    dt.Columns.Add(colIdx.ToString(), typeof(string));


                // Scan each row
                for (var rowIdx = stats.StartRowIndex; rowIdx < stats.EndRowIndex; rowIdx++)
                {
                    //dt.Rows.Add();

                    var newRow = dt.NewRow();

                    // And each column for data
                    for (var colIdx = stats.StartColumnIndex; colIdx < stats.EndColumnIndex; colIdx++)
                    {
                        //if (addDataColumns)
                        //    dt.Columns.Add();

                        newRow[colIdx - 1] = wb.GetCellValueAsString(rowIdx, colIdx);

                        //if (colIdx >= stats.EndColumnIndex)
                        //    addDataColumns = false;
                    }
                    dt.Rows.Add(newRow);
                }

                set.Tables.Add(dt);
            }

            // Debug output
            foreach (DataRow row in set.Tables[0].Rows)
            {
                foreach (var output in row.ItemArray)
                {
                    Console.WriteLine(output.ToString());
                }
            }

            return set;
        }
    }

Note: SpreadsheetLight indicies start from 1 instead of 0;

Now, I've tried replacing dt.Rows.Add() with new object[stats.EndColumnIndex -1];, as well as a temporary variable from var newRow = dt.NewRow(); and then passing them into the DataTable afterwards, but still get the same end result. The row objects are populating correctly, but aren't transferring to the DataTable at the end.

When you explore the object during runtime, it shows the correct number of rows and columns in the relevant properties. But when you open it up in the DataVisualiser you can only see the columns, no rows.

I must be missing something obvious.

Update

I looped through the resulting table and output the values to the console as a test. All the correct values appear, but the visualiser remains empty:

I guess the question now is, why would there be no data in the visualiser when there is valid data in the DataTable?

Update 2 Added the full method for reference, including a simple set of for loops to loop through all rows and columns in the first DataTable. Note: I also experimented with pulling the column creation out of the loop and even setting the datatypes. Made no difference. Commented code shows the original.

Answer 1

Ok, turns out the problem was most likely from the columns being added. Either there were too many columns for the visualiser to handle (1024) which I find hard to believe, or there was a bug in visual studio that's randomly corrected itself.

There's also a bug in SpreadsheetLight that lists all columns as having data when you call GetWorksheetStatistics(); so I've used a workaround that uses the maximum number of total cells available OR the stats.NumberOfColumns, whichever is the smallest.

Either way, the below code now functions.

public DataSet ReadToDataSet(string fileName)
{

    using (var wb = new SLDocument(fileName))
    {
        var set = new DataSet(GenerateTitle(wb.DocumentProperties.Title));

        foreach (var wsName in wb.GetWorksheetNames())
        {

            var ws = wb.SelectWorksheet(wsName);

            // Select worksheet returns a bool, so if it comes back false, try the next worksheet instead.
            if (!ws) continue;

            // Statistics gives indecies of the first and last data cells
            var stats = wb.GetWorksheetStatistics();

            // There is a bug with the stats columns. Take the total number of elements available or the columns from the stats table, whichever is the smallest
            var newColumnIndex = stats.NumberOfCells < stats.NumberOfColumns
                                ? stats.NumberOfCells
                                : stats.NumberOfColumns;

            // Create a new DataTable for each worksheet
            var dt = new DataTable(wsName);

            var addDataColumns = true;

            // Scan each row
            for (var rowIdx = stats.StartRowIndex; rowIdx < stats.EndRowIndex; rowIdx++)
            {

                var newRow = dt.NewRow();

                // And each column for data
                for (var colIdx = stats.StartColumnIndex; colIdx < newColumnIndex; colIdx++)
                {
                    if (addDataColumns)
                        dt.Columns.Add();

                    newRow[colIdx - 1] = wb.GetCellValueAsString(rowIdx, colIdx);

                }

                addDataColumns = false;

                dt.Rows.Add(newRow);
            } 

            set.Tables.Add(dt);
        }

        return set;
    }
}

Hopefully someone else finds this as a useful reference in the future, either for SpreadsheetLight or DataVisualiser in Visual Studio. If anyone know's of any limits for the visualiser, I'm all ears!