C# - ExcelDataReader - Is it possible to include empty cells in DataSet?

Question

I have this excel file:

When I read cell 'A1' I get "Daniel".

Is it possible to make ExcelDataReader include empty rows/columns cells?

Instead of reading the value of cell 'A1' -> 'Daniel'

I want 'A1' cell to contain null as the excel file presents

and cell 'B2' to contain 'Daniel'

I don't see anything relavant in configuration I can set:

        DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration() {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration() {
                UseHeaderRow = treatFirstRowAsRowHeader
            }
        });

How exactly do you read cell 'A1'? Afaik ExcelDataReader doesn't behave like you describe, could it be an indexing bug elsewhere? — user8728340, Jun 27 '23 at 18:14

score 1 · Accepted Answer · answered Jun 26 '23 at 12:37

try this code

using System;
using System.Data;
using ExcelDataReader;

class Program
{
    static void Main(string[] args)
    {
        using (var stream = File.Open("your_excel_file.xlsx", FileMode.Open, FileAccess.Read))
        {
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                var dataSet = reader.AsDataSet(new ExcelDataSetConfiguration
                {
                    // Configure the ExcelDataReader options here
                    // if needed (e.g., setting the FirstRowAsColumnNames)
                });

                DataTable dataTable = dataSet.Tables[0];

                // Shift the cell values to include empty rows/columns
                DataTable shiftedDataTable = ShiftData(dataTable);

                // Access the desired cells
                var cellA1Value = shiftedDataTable.Rows[0][0]; // will be null
                var cellB2Value = shiftedDataTable.Rows[1][1]; // will be "Daniel"

                Console.WriteLine("Cell A1 Value: " + cellA1Value);
                Console.WriteLine("Cell B2 Value: " + cellB2Value);
            }
        }
    }

    static DataTable ShiftData(DataTable dataTable)
    {
        // Create a new DataTable with shifted cell values
        DataTable shiftedTable = new DataTable();

        // Add empty columns
        for (int i = 0; i < dataTable.Columns.Count; i++)
        {
            shiftedTable.Columns.Add();
        }

        // Add empty rows
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            shiftedTable.Rows.Add(shiftedTable.NewRow());
        }

        // Copy the cell values to the shifted positions
        for (int i = 0; i < dataTable.Rows.Count; i++)
        {
            for (int j = 0; j < dataTable.Columns.Count; j++)
            {
                shiftedTable.Rows[i + 1][j + 1] = dataTable.Rows[i][j];
            }
        }

        return shiftedTable;
    }
}

More than a helpful answer!. I was more interested to know whether ExcelReader itself provide a solution for these kind of situations though. I really appreciate your help, thank you. — Saraf, Jun 26 '23 at 14:29
By the way, I think "shiftedTable.Rows[i + 1][j + 1] = dataTable.Rows[i][j];" should be "shiftedTable.Rows[i][j] = dt.Rows[i][j];" when I tried to run the code it gave me an exception that the index was out of bounds - so I've changed it. Kind suggestion to update the answer :) — Saraf, Jun 27 '23 at 09:53

C# - ExcelDataReader - Is it possible to include empty cells in DataSet?

1 Answers1