How to convert IExcelDataReader Values to string Datatype

Question

I am using below code to read Excel and store in Dataset.

     public DataSet ReadExcelDataToDataSet(Stream fileStream)
            {
                DataTable dataInExcelSheet = new DataTable();
                IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(fileStream);
                DataSet excelDataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
                {
                    UseColumnDataType = false
//Do we have any property here to convert all rows values to string datatype.
                });
                excelReader.Close();
                return excelDataSet;
            }

Is there any way to convert all values of Excel Sheet to string and store it in Dataset as String values.

Example:

In Excel file, for few columns I have values as 1,22.0 which are of Int32 and Double datatypes. I want to convert these values to string and then store them in Dataset as String.

Answer 1

Adding to @AlwaysLearning answer with a non LINQ version of the extension.

public static class DataSetExtensions
{
    public static DataSet ToAllStringFields(this DataSet ds)
    {
        // Clone function -> does not copy the data, but just the structure.
        var newDs = ds.Clone();
        foreach (DataTable table in newDs.Tables)
        {
            // if the column is not string type -> set as string.
            foreach (DataColumn col in table.Columns)
            {
                if (col.DataType != typeof(string))
                    col.DataType = typeof(string);
            }
        }

        // imports all rows.
        foreach (DataTable table in ds.Tables)
        {
            var targetTable = newDs.Tables[table.TableName];
            foreach (DataRow row in table.Rows)
            {
                targetTable.ImportRow(row);
            }
        }

        return newDs;
    }
}

Usage:

public DataSet ReadExcelDataToDataSet(Stream fileStream)
{
    DataTable dataInExcelSheet = new DataTable();
    IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(fileStream);
    DataSet excelDataSet = excelReader.AsDataSet(new ExcelDataSetConfiguration()
    {
        UseColumnDataType = false
    }).ToAllStringFields();
    excelReader.Close();
    return excelDataSet;
}

Answer 2

When using UseColumnDataType = false ExcelDataReader returns cells as System.Object.

If you need to get System.String back you'll need to clone the DataSet structure, setting each DataColumn's type to System.String as you go, and use DataTable.ImportRow() to do all the conversions to System.String for you. If you need more control of formatting (e.g.: converting Date types) you could also do the conversions explicitly.

Here's an example for you...

// NuGet reference: ExcelDataReader v3.6.0 built from https://github.com/ExcelDataReader/ExcelDataReader/ExcelDataReader
// NuGet reference: ExcelDataReader.DataSet v3.6.0 built from https://github.com/ExcelDataReader/ExcelDataReader/ExcelDataReader.DataSet
using ExcelDataReader;
using System;
using System.Data;
using System.IO;
using System.Linq;

namespace Convert_IExcelDataReader_values_to_string
{
    class MainClass
    {
        public static DataSet ReadExcelDataToDataSet(Stream fileStream)
        {
            DataSet excelDataSet;
            using (IExcelDataReader reader = ExcelReaderFactory.CreateReader(fileStream))
            {
                var dataSetConfiguration = new ExcelDataSetConfiguration()
                {
                    UseColumnDataType = false
                };
                // This reads each Sheet into a DataTable and each column is of type System.Object
                excelDataSet = reader.AsDataSet(dataSetConfiguration);
            }

            var stringDataSet = ConvertToDataSetOfStrings(excelDataSet);
            return stringDataSet;
        }

        private static DataSet ConvertToDataSetOfStrings(DataSet sourceDataSet)
        {
            var result = new DataSet();
            result.Tables.AddRange(
                sourceDataSet.Tables.Cast<DataTable>().Select(srcDataTable =>
                {
                    var destDataTable = new DataTable(srcDataTable.TableName, srcDataTable.Namespace);
                    // Copy each source column as System.String...
                    destDataTable.Columns.AddRange(
                        srcDataTable.Columns.Cast<DataColumn>()
                            .Select(col => new DataColumn(col.ColumnName, typeof(String)))
                            .ToArray()
                            );
                    // Implicitly convert all source cells to System.String using DataTable.ImportRow()
                    srcDataTable.Rows.OfType<DataRow>()
                    .ToList()
                    .ForEach(row => destDataTable.ImportRow(row));
                    return destDataTable;
                })
                .ToArray()
                );
            return result;
        }

        public static void Main(string[] args)
        {
            using (var stream = File.Open("TestOpenXml.xlsx", FileMode.Open, FileAccess.Read, FileShare.Read))
            {
                var dataSet = ReadExcelDataToDataSet(stream);
            }
        }
    }
}

Hope this helps!