C# Tutorial – How to read excel file | FoxLearn

38
32



How to read excel (*.xls,*.xlsx) file using ExcelDataReader c# excel programming tutorial c# windows forms application.

The c# basics beginner course is a free c# tutorial series that helps beginning programmers learn the basics of the c# programming language.

Website:

Through this video you will learn the following topics:

Read Excel File in C# Example
how to read excel file in c# windows application
best way to read excel file in c#
C# Open and Read Excel Files
C# How To Get Data From Excel File In C#
C# Programming – Reading in a File from Excel
How to import excel file to DataGridView in c#
How to Open Excel File in C#
C# DataGridView Excel Load Data From Excel File
How to read an Excel file using C#
How to read data from excel file using c#
Open and read Excel file from C#
C# Read Data From Excel File
c# excel parser, c# import excel file, c# open excel workbook
read data from excel in c#
c# excel read cell value, read excel sheet in c#

Nguồn:https://wijstaanvooronzegrondrechten.org/

38 COMMENTS

  1. First of all – THANKS!
    Second, "IExcelDataReader" don't working – "The type or namespace could not ne found"

  2. and how we can for example modify some rows and colums and then add it to the datagridview and then print it ??
    thanks for the nice video and also yur help

  3. Resume of all video………… (With Code)

    In Manage NuGet Pakages…
    FIND "ExcelDataReader"

    Install "ExcelDataReader"
    (currently version 3.6.0)

    AND

    Install "ExcelDataReader.DataSet"
    (currently version 3.6.0)

    in combobox
    in properties (DropDownStyle – Change to DropDownList)
    AND
    create method "SelectedIndexChanged" (My name: cmbExcel_SelectedIndexChanged)

    in button
    create method "Click" (My name: btnAbrirPlanilha_Click)

    in datagridview
    in properties (AllowUserToAddRows – Change to False)

    using System;

    using System.IO;
    using System.Windows.Forms;
    using System.Data;
    using ExcelDataReader;

    //START CODE

    //Create variable
    DataSet result;

    private void btnAbrirPlanilha_Click(object sender, EventArgs e)

    {

    using (OpenFileDialog of = new OpenFileDialog() { Filter = "Excel Workbook|* .xlsx|Excel Work Book 97-2003|*.xls", ValidateNames = true })

    {

    if (of.ShowDialog() == DialogResult.OK)

    {

    FileStream fs = File.Open(of.FileName, FileMode.Open, FileAccess.Read);

    IExcelDataReader reader;

    if (of.FilterIndex == 1)

    reader = ExcelReaderFactory.CreateOpenXmlReader(fs);

    else

    reader = ExcelReaderFactory.CreateBinaryReader(fs);

    result = reader.AsDataSet(new ExcelDataSetConfiguration()

    {

    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()

    {

    UseHeaderRow = true // set to true to use excel first row as column in datagridview

    }

    });

    cmbExcel.Items.Clear();

    foreach (DataTable dt in result.Tables)

    {

    cmbExcel.Items.Add(dt.TableName);

    }

    reader.Close();

    }

    }

    }

    private void cmbExcel_SelectedIndexChanged(object sender, EventArgs e)

    {

    datGridExcel.DataSource = result.Tables[cmbExcel.SelectedIndex];

    }

    //END CODE

    If you want to change the default file type of Excel, just change the code:

    Excel Work Book 97-2003|*.xls ——- (old type worksheets 97-2003)

    Change to:

    using (OpenFileDialog of = new OpenFileDialog() { Filter = "Excel Work Book 97-2003|*.xls|Excel Workbook|* .xlsx", ValidateNames = true })

    {

    if (of.ShowDialog() == DialogResult.OK)

    {

    FileStream fs = File.Open(of.FileName, FileMode.Open, FileAccess.Read);

    IExcelDataReader reader;

    if (of.FilterIndex == 1)

    reader = ExcelReaderFactory.CreateBinaryReader(fs);
    else
    reader = ExcelReaderFactory.CreateOpenXmlReader(fs);

    ORRRRRR

    Excel Workbook|* .xlsx —– (NEW worksheets currently)

    Change to:

    using (OpenFileDialog of = new OpenFileDialog() { Filter = "Excel Workbook|* .xlsx|Excel Work Book 97-2003|*.xls", ValidateNames = true })

    {

    if (of.ShowDialog() == DialogResult.OK)

    {

    FileStream fs = File.Open(of.FileName, FileMode.Open, FileAccess.Read);

    IExcelDataReader reader;

    if (of.FilterIndex == 1)

    reader = ExcelReaderFactory.CreateOpenXmlReader(fs);

    else

    reader = ExcelReaderFactory.CreateBinaryReader(fs);

  4. error 'FileStream' not found. Probabilmente manca una direttiva using o un riferimento all'assembly. readexcel …file not exist, filemode not exist, fileaccess not exist, IExcelDataReader not found…anyone know why?

  5. this code is working on the latest update of ExcelDataReader 3.4.2

    private void OpenExcel_Click(object sender, EventArgs e)

    {

    using(OpenFileDialog ofd = new OpenFileDialog() {Filter="Excel Workbook | *.xlsx",ValidateNames=true})

    {

    IExcelDataReader reader;

    if (ofd.ShowDialog() == DialogResult.OK)

    {

    FileStream fs = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read);

    //IExcelDataReader reader = ExcelReaderFactory.CreateBinaryReader(fs);

    //reader.IsFirstRowAsColumnNames = true;

    reader = ExcelDataReader.ExcelReaderFactory.CreateReader(fs);

    var conf = new ExcelDataSetConfiguration

    {

    ConfigureDataTable = _ => new ExcelDataTableConfiguration

    {

    UseHeaderRow = true

    }

    };

    ds = reader.AsDataSet(conf);

    cbxChange.Items.Clear();

    foreach (DataTable dt in ds.Tables)

    {

    cbxChange.Items.Add(dt.TableName);

    }

    reader.Close();

    }

    }

    }

  6. i code is like you but something wrong with my code, can you send the code to tiendatntd2906@gmail.com. thank you so much!

  7. this code keeps giving me the error "System.NullReferenceException: 'Object reference not set to an instance of an object.' result was null. " can you please tell me how to fix it?
    thanks

  8. for those who want to use the first row as column for datagrid
    use this
    using ExcelDataReader;
    using System;
    using System.Data;
    using System.IO;
    using System.Windows.Forms;

    namespace ReadmyExce_
    {
    public partial class Form1 : Form
    {
    public Form1()
    {
    InitializeComponent();
    }

    DataSet result;
    private void button1_Click(object sender, EventArgs e)
    {
    using (OpenFileDialog of = new OpenFileDialog(){Filter="Excel Work Book 97-2003|*.xls|Excel Workbook|* .xlsx",ValidateNames=true })
    {
    if(of.ShowDialog() == DialogResult.OK)
    {

    FileStream fs = File.Open(of.FileName, FileMode.Open, FileAccess.Read);
    IExcelDataReader reader;
    if (of.FilterIndex == 1)
    reader = ExcelReaderFactory.CreateBinaryReader(fs);
    else
    reader = ExcelReaderFactory.CreateOpenXmlReader(fs);

    result = reader.AsDataSet(new ExcelDataSetConfiguration() {
    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
    {
    UseHeaderRow = true // set to true to use excel first row as column in datagridview
    }

    });

    comboBox1.Items.Clear();
    foreach(DataTable dt in result.Tables)
    {
    comboBox1.Items.Add(dt.TableName);

    }
    reader.Close();

    }
    }
    }

    private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
    {
    dataGridView1.DataSource = result.Tables[comboBox1.SelectedIndex];
    }
    }
    }

  9. Followed all the steps, and started VS as an Administrator. When I rebuild the solution I get the following error message on the console :
    Severity Code Description Project File Line Suppression State
    Warning Could not copy "objDebugReadExcel.exe" to "binDebugReadExcel.exe". Beginning retry 9 in 1000ms. The process cannot access the file 'binDebugReadExcel.exe' because it is being used by another process. ReadExcel

    Any idea what could be wrong ?

  10. Hı please help me
    System.NullReferenceException was unhandled

    foreach (datatable dt in result.Tables) : _An unhandled exception of type 'System.NullReferenceException' occurred ???

  11. Hı please help me
    System.NullReferenceException was unhandled

    foreach (datatable dt in result.Tables) : _An unhandled exception of type 'System.NullReferenceException' occurred ???

  12. Hı please help me
    System.NullReferenceException was unhandled

    foreach (datatable dt in result.Tables) : _An unhandled exception of type 'System.NullReferenceException' occurred ????

  13. Hi Fox Learn,
    Can you please share the source code. (franciscoabrao@outlook.com)
    IExcelDataReader does not recognise

  14. For those of you stuck with the IsFirstRowAsColumnNames step, uninstall the latest version of the package and install version 2.1.0. After that is installed, the code changes slightly. Either include "using Excel;" above the namespace or add "Excel." in front of the DataReader and the ExcelReaderFactroy lines (i.e. Excel.IDataReader reader = Excel.ExcelReaderFactory.CreateBinaryReader(fs);)

  15. Thank you very much. I try as below condition and get result as below.
    If possible, would you please give me some advice.( elynis@gmail.com)
    Condition:
    1. ExcelDataReader Version 3.1.0
    2. Microsoft.Office.Interop.Excel, Version 14.0.0.0

    Result:
    1.
    iExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateBinaryReader(fs); → has error for iExcelDataReader
    ExcelDataReader.IExcelDataReader excelreader = ExcelDataReader.ExcelReaderFactory.CreateBinaryReader(fs); → OK

    2.
    excelreader.isFirstRowAsColumnNames = true;
    → no intelligence for isFirstRowAsColumnNames

    3. result = excelreader.AsDataSet();
    → no intelligence for AdDataSet();

  16. hello, i have a problem how can i import files from excel to c# using listview not datagridview is there any way?

  17. first of all thanks for the video.
    I have a problem with '' IsFirstRowColumnNames '' , it says // IExcelDataReader' does not contain a definition for 'IsFirstRowAsColumnNames' and no extension method 'IsFirstRowAsColumnNames' accepting a first argument of type 'IExcelDataReader' could be found (are you missing a using directive or an assembly reference?) //

LEAVE A REPLY

Please enter your comment!
Please enter your name here