Reading Excel file (Column's Name, Data Type, Maxlength) using c#.net

To read an Excel file using C# and obtain the column list, data types of each column, and the maximum length of data in each column, you can use the ExcelDataReader library, which is available as a NuGet package.

using System;
using System.Collections.Generic;
using System.IO;
using ExcelDataReader;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = @"your_excel_file_path";
            // Create an instance of FileStream to read the Excel file
            using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                // Create an instance of ExcelDataReader to read the stream
                using (var reader = ExcelReaderFactory.CreateReader(stream))
                {
                    // Read the header row to get the column names
                    reader.Read();
                    var columnNames = new List();
                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        columnNames.Add(reader.GetValue(i).ToString());
                    }

                    // Loop through each row and get the data type and max length of each column
                    var dataTypes = new Dictionary();
                    var maxSizes = new Dictionary();
                    while (reader.Read())
                    {
                        for (var i = 0; i < reader.FieldCount; i++)
                        {
                            var columnName = columnNames[i];
                            var value = reader.GetValue(i);

                            // Check if the column has been seen before
                            if (!dataTypes.ContainsKey(columnName))
                            {
                                // If not, add it to the dictionaries with the initial value
                                dataTypes[columnName] = value.GetType().Name;
                                maxSizes[columnName] = value.ToString().Length;
                            }
                            else
                            {
                                // If the column has been seen before, update the data type and max size if necessary
                                if (value.GetType().Name != dataTypes[columnName])
                                {
                                    dataTypes[columnName] = "Mixed"; // If the column has mixed data types, set the type to "Mixed"
                                }
                                if (value.ToString().Length > maxSizes[columnName])
                                {
                                    maxSizes[columnName] = value.ToString().Length;
                                }
                            }
                        }
                    }

                    // Output the column names, data types, and max sizes
                    Console.WriteLine("Column List:");
                    foreach (var columnName in columnNames)
                    {
                        Console.WriteLine(columnName);
                        Console.WriteLine("Data Type: " + dataTypes[columnName]);
                        Console.WriteLine("Max Size: " + maxSizes[columnName]);
                    }
                }
            }
            Console.ReadLine();
        }
    }
}