Code2night
  • Home
  • Blogs
  • Tutorial
  • Post Blog
  • Tools
    • Json Beautifier
    • Html Beautifier
  • Members
    • Register
    • Login
  1. Home
  2. Blogpost
20 Sep
2021

Import data from Excel in Asp.Net

by Shubham Batra

4901

Download Attachment

ExcelDataReader

Excel Data Reader is a c# library which is used for reading data from excel in table format and we can fill the data in DataTable or DataSet. So , for installing ExcelDataReader. You have to go to

Project -> Manage Nugget Packages 

Now add two nugget packages. ExcelDataReader and ExcelDataReader.DataSet . You can check in the given image

So, after you have added these two nugget packages in your project. You have to go to your c# code and use this


private DataTable ReadData()
        {
            var filePath = HttpContext.Current.Server.MapPath("~/ExcelFile/Test.xlsx");
            var dataTable = new DataTable();
            using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader;
                if (Path.GetExtension(filePath).ToUpper() == ".XLS")
                {
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else
                {
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                var result = excelReader.AsDataSet();
                excelReader = ExcelReaderFactory.CreateReader(stream);

                var conf = new ExcelDataSetConfiguration
                {
                    ConfigureDataTable = _ => new ExcelDataTableConfiguration
                    {
                        UseHeaderRow = true
                    }
                };
                var dataSet = excelReader.AsDataSet(conf);
                dataTable = dataSet.Tables[0];
                dataTable.Rows.RemoveAt(0);
            }
            return dataTable;
        }

So , first of all you must place a xlsx file  in your project. 

var filePath = HttpContext.Current.Server.MapPath("~/ExcelFile/Test.xlsx");

So , this was a sample file. Placed inside Excel folder inside project root.

 var conf = new ExcelDataSetConfiguration
 {
    ConfigureDataTable = _ => new ExcelDataTableConfiguration
    {
        UseHeaderRow = true
    }
 };
 var dataSet = excelReader.AsDataSet(conf);

This , will return you data as dataSet. UseHeaderRow will take first row as column headers. So, this is how you can ready your excel data and populate in datatable or dataset in Asp.Net.


  • |
  • ExcelDataReader , Import data from excel in AspNet , AspNet , c#

Comments

Follow Us On Social Media - Like Us On Facebook

Best Sellers

product 1

Hand Hug Bracelet For Women Men Cuff Bangle Adjustable Lover Couple Bracelets

Can be given as a gift to your family, relatives, or friends

Buy $15.99
product 1

Teddy bear hug bear plush toy bear cub

Can be given as a gift to your family, relatives, or friends


Buy $49.99

Tags

LinkedinLogin
LinkedinProfile
GetLinkedinProfile
C#
Aspnet
MVC
Linkedin
ITextSharp
Export to Pdf
AspNet Core
AspNet
View to Pdf in Aspnet
Model Validation In ASPNET Core MVC 60
Model Validation
Model Validation In ASPNET Core MVC
Model Validation In ASPNET
Image Compression in AspNet
Compress Image in c#
AspNet MVC
Thank you for Downloading....!

Subscribe for more tutorials

Support our team

Continue with Downloading

Welcome To Code2night, A common place for sharing your programming knowledge,Blogs and Videos

  • Panipat
  • info@Code2night.com

Links

  • Home
  • Blogs
  • Tutorial
  • Post Blog

Popular Tags

Copyright © 2023 by Code2night. All Rights Reserved

  • Home
  • Blog
  • Login
  • SignUp
  • Contact
  • Terms & Conditions
  • Refund Policy
  • About Us
  • Privacy Policy
  • Json Beautifier