ExcelDataReader is only read the 1st column from XSSFWorkbook-saved-file (C#)

Question

ExcelDataReader is only able to read the 1st column (header/data) from an excel generated by NPOI.

For example the code below, it will only read 1st column "111" with data "444"

Umm, any idea would be nice, thanks in advance.

One odd part is the file size will change after excel.exe (2019) saves it. 4KB -> 10KB, not sure why. *Then, the file is fully readable by ExcelDataReader.

HSSFWorkbook(.xls) also works, hopfully i am only missing very few pieces?

Write:

XSSFWorkbook EWorkbook = new XSSFWorkbook();
XSSFSheet ESheet1 = (XSSFSheet)EWorkbook.CreateSheet("Test");
XSSFRow ERow = (XSSFRow)ESheet1.CreateRow(0);
XSSFCell ECell = (XSSFCell)ERow.CreateCell(0); ECell.SetCellValue("111");
ECell = (XSSFCell)ERow.CreateCell(1); ECell.SetCellValue("222");
ECell = (XSSFCell)ERow.CreateCell(2); ECell.SetCellValue("333");

ERow = (XSSFRow)ESheet1.CreateRow(1); 
ECell = (XSSFCell)ERow.CreateCell(0); ECell.SetCellValue("444");
ECell = (XSSFCell)ERow.CreateCell(1); ECell.SetCellValue("555");
ECell = (XSSFCell)ERow.CreateCell(2); ECell.SetCellValue("666");

using (var file2 = new FileStream(@"C:\inetpub\temp.xlsx", FileMode.Create, FileAccess.ReadWrite))
{
EWorkbook.Write(file2);
file2.Close();
}

Read:

excelSpreadSheet = Excel.ExcelReaderFactory.CreateOpenXmlReader(fs);

Answer 1

The following shows how to create an Excel (.xlsx) file with Nuget package NPOI (v2.6.2) and then read the file with Nuget packages ExcelDataReader (v3.6.0) and ExcelDataReader.DataSet (v3.6.0). It's been tested using a Windows Forms App (.NET Framework) v4.8.

Pre-requisites: Download and install the Nuget packages mentioned above.

Create Excel (.xlsx) file using NPOI.

HelperNPOI:

using System;
using System.Collections.Generic;
using System.IO;
using NPOI;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace ExcelNPOITest
{

    public class SpreadSheetData
    {
        public List<SpreadSheetRow> Rows = new List<SpreadSheetRow>();
    }

    public class SpreadSheetRow
    {
        public List<SpreadSheetCell> Cells { get; set; } = new List<SpreadSheetCell>();
    }

    public class SpreadSheetCell
    {
        public object CellValue { get; set; }
    }

    public class HelperNPOI
    {
        public static void CreateExcel(string filename, SpreadSheetData data)
        {
            using (IWorkbook workbook = new XSSFWorkbook())
            {
                //create sheet
                ISheet sheet1 = workbook.CreateSheet("Sheet1");

                //add data to sheet

                if (data != null)
                {
                    //0-based indices
                    for (int i = 0; i < data.Rows.Count; i++)
                    {
                        //create row
                        IRow row = sheet1.CreateRow(i);

                        for (int j = 0; j < data.Rows[i].Cells.Count; j++)
                        {
                            ICell cell = row.CreateCell(j);

                            //set cell value
                            if (data.Rows[i].Cells[j].CellValue.GetType() == typeof(bool))
                                cell.SetCellValue((bool)data.Rows[i].Cells[j].CellValue);
                            else if (data.Rows[i].Cells[j].CellValue.GetType() == typeof(DateTime))
                                cell.SetCellValue((DateTime)data.Rows[i].Cells[j].CellValue);
                            else if (data.Rows[i].Cells[j].CellValue.GetType() == typeof(double))
                                cell.SetCellValue((double)data.Rows[i].Cells[j].CellValue);
                            else if (data.Rows[i].Cells[j].CellValue.GetType() == typeof(int))
                                cell.SetCellValue((int)data.Rows[i].Cells[j].CellValue);
                            else if (data.Rows[i].Cells[j].CellValue.GetType() == typeof(string))
                                cell.SetCellValue((string)data.Rows[i].Cells[j].CellValue);
                        }

                        using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.ReadWrite))
                        {
                            //save workbook
                            workbook.Write(fs);
                        }
                    }
                }
            }
        }
    }
}

Usage:

private void CreateExcelFile(string filename)
{
    //create new instance
    SpreadSheetData data = new SpreadSheetData();

    //add test data
    data.Rows.Add(new SpreadSheetRow()
    {
        Cells = new List<SpreadSheetCell> {
        new SpreadSheetCell() { CellValue = "Row 1 Cell 1" },
        new SpreadSheetCell() { CellValue = "Row 1 Cell 2" },
        new SpreadSheetCell() { CellValue = "Row 1 Cell 3" }
    }
    });

    data.Rows.Add(new SpreadSheetRow()
    {
        Cells = new List<SpreadSheetCell> {
        new SpreadSheetCell() { CellValue = "Row 2 Cell 1" },
        new SpreadSheetCell() { CellValue = "Row 2 Cell 2" },
        new SpreadSheetCell() { CellValue = "Row 2 Cell 3" }
    }
    });

    HelperNPOI.CreateExcel(filename, data);
}

---

Read Excel (.xlsx) file using ExcelDataReader

Note: The code below is adapted from Using ExcelDataReader to read Excel data starting from a particular cell and ExcelDataReader (Github).

HelperExcelDataReader:

using ExcelDataReader;
using System.Data;
using System.IO;
using System.Diagnostics;

namespace ExcelNPOITest
{
    public class HelperExcelDataReader
    {
        public static DataSet ReadDataFromExcelFile(string filename)
        {
            DataSet dataSet = new DataSet();

            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                //create new instance
                IExcelDataReader excelDataReader = ExcelDataReader.ExcelReaderFactory.CreateReader(fs);

                //get data
                dataSet = excelDataReader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    UseColumnDataType = false,
                    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    {
                        UseHeaderRow = false
                    }
                });

                //var dataTable = dataSet.Tables[0];
            }

            return dataSet;
        }
    }
}

Usage:

private void DisplayDataSet(string filename)
{
    DataSet ds = HelperExcelDataReader.ReadDataFromExcelFile(filename);

    if (ds != null)
    {
        //option 1
        foreach (DataRow row in ds.Tables[0].Rows)
        {
            foreach (var value in row.ItemArray)
            {
                Debug.WriteLine("{0}, {1}", value, value.GetType());
            }
        }

        /*
        //option 2
        for (int i = 0; i < ds.Tables.Count; i++)
        {
            Debug.WriteLine($"DataTable: {ds.Tables[i].TableName}");

            for (int rowIndex = 0; rowIndex < ds.Tables[i].Rows.Count; rowIndex++)
            {
                Debug.Write($"row[{rowIndex}]: ");

                for (int columnIndex = 0; columnIndex < ds.Tables[i].Columns.Count; columnIndex++)
                {
                    Debug.Write($"\"{ds.Tables[i].Rows[rowIndex][columnIndex].ToString()}\" ");
                }

                Debug.WriteLine("");
            }

            Debug.WriteLine("");
        }
        */
    }
}

Output:

Row 1 Cell 1, System.String
Row 1 Cell 2, System.String
Row 1 Cell 3, System.String
Row 2 Cell 1, System.String
Row 2 Cell 2, System.String
Row 2 Cell 3, System.String

Resources:

• Using ExcelDataReader to read Excel data starting from a particular cell
• ExcelDataReader (Github)
• NPOI Examples (Github)
• Open XML SDK 2.5 Productivity Tool