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 ExcelDataReader Nuget Package](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-5ZwyNlI545gJg4tad1AZup0tEdWklVAvtTYmm8vgPpD0UegaWRI9UBUyGzkNReXG4JPrFULJijwbVgiMaa_e8K7MeydcyQvBSg_SryfpNncTuqgRjwDyS4-5DTgijTnWqrYZ1B4-lks/s640/ExcelDataReader.PNG) |
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 Excel to Datatable](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj58iqFWzTLRiaTLB4Ytbbm3Rjhv3N1wVC5rFOzhsAb8tCnqC6DjDzq8paSTIWjz27VJZC-2NHDHefLTamQBOo5d4ST-s4sQgzmNrk5iHTzmKGYZQ8A9_gNaiQ_Hqg-3qqEj83tdcvVB8/s640/Excel+As+datatable+in+debug+mode.PNG) |
Excel to Datatable |
Let me know
if you have any other idea to read excel file and get its values.