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

Answer 1

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