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

Question

Hi all,

I want to read an excel file using C#.

I have to get following information about an excel file.

1. Column list

2. Data type of data particular column is having like Numeric data, Alphanumric etc.

3. Maxlength/ max size of data contained in a column.

e.g. If there is one of the column of excel file as EmployeeNo then

1. EmplyeeNo

2. Alphanumeric

3. Maxlength(size) of data = 20

I got solution to read all coulmn names of excel file but could not get solution for finding data type of column and maxlength.

How can i do this in dotnet? Any help would be appreaciated.

Thank you

Amit Mohanty · Answer

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 (); var maxSizes = new Dictionary (); while (reader.Read()) { for (var i = 0; i 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(); } } }

Tuhin Paul · Answer

The code assumes that the Excel file exists and can be successfully opened. You could add error handling to catch any exceptions that may occur and provide better feedback to the user. The `using` statement is a great way to ensure that unmanaged resources are properly disposed of, but you can make it even simpler by using the C# feature of try-with-resources. This will ensure that the `ExcelReader` is always properly disposed of, even if an exception is thrown. In the current code, the `ToString()` method is called on every cell value, which can be slow for large Excel files. A more efficient way to get the maximum length is to use the `ToString().Length` method on the raw `object` value, like this: `value?.ToString().Length ?? 0`. Instead of checking if the data type is not equal to each possible type individually, you could use a switch statement to make the code more concise and easier to read.

Leon D · Answer

You can install Spire.XLS through NuGet and then use it to read Excel files with ease. https://www.nuget.org/packages/Spire.XLS/ The code sample below explains how to read data in an Excel worksheet into a data table: Workbook workbook = new Workbook(); workbook.LoadFromFile("Sample.xlsx"); Worksheet sheet = workbook.Worksheets[0]; DataTable dt = sheet.ExportDataTable();

dip s · Answer

@Amit Mohanty thank you. its working

Jignesh Kumar · Answer

Hello, You can refer this article, You can use IronXL library to achieve this one and it will allow you to convert sheet to datatable. https://www.c-sharpcorner.com/UploadFile/6b8651/read-excel-file-in-windows-application-using-C-Sharp/