I received
English and Arabic data in Excel file and I have to migrate this data to SQL
server.
I don’t
want to use Office API for this as it’s a bit
complex.
So for
this, I used Nuget package “ExcelDataReader” and “ExcelDataReader.DataSet” as
below
 |
| ExcelDataReader Nuget Package |
Note :- Must include “ExcelDataReader.DataSet”
Nuget package for getting AsDataSet() method in code.
Code for
reading excel file and to get data table is very simple as
using ExcelDataReader;
using System.IO;
namespace ExcelConsoleApp1
{
class Program
{
static void Main(string[] args)
{
//1. Create
File Stream for Excel file
FileStream stream = File.Open("d:\\NewsListWithAllColumns.xlsx", FileMode.Open, FileAccess.Read);
//2.. Reading
from a Excel file *.xlsx
IExcelDataReader excelReader =
ExcelReaderFactory.CreateReader(stream);
//...
//2. Reading
from a OpenXml Excel file (2007 format; *.xlsx)
//IExcelDataReader
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
//...
//3. DataSet
Configuration - Create column names from first row
ExcelDataSetConfiguration
excelDataSetConfiguration = new ExcelDataSetConfiguration
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
//4. DataSet -
The result of each spreadsheet will be created in the result.Tables
var dataSet = excelReader.AsDataSet(excelDataSetConfiguration);
//5. Get
Datatable from dataset
var dataTable = dataSet.Tables[0];
//6. Read one
of the cell to test
var c11 = dataTable.Rows[2][2];
//7. Free
resources (IExcelDataReader is IDisposable)
excelReader.Close();
}
}
}
Below is
the screenshot while to view datatable output in program debug mode as
 |
| Excel to Datatable |
Let me know
if you have any other idea to read excel file and get its values.
