Skip to main content

How To Read Excel File Content in ASP.NET

Using excel File in asp.net is very easy and very useful in many places For example in many conditions you want to upload data online through excel file into database Like Offline Attendence,Offline entries.Below code not only help you in just reading the excel file but also to apply many validation like excel sheets count,single sheet column count,single column rows count,file upload extension name etc etc.But this code works only for .xls file extension but not for .xlsx.Before Using below code import namespaces written below and use fileupload control for file upload and button to upload excel and parse it.

using ExcelLibrary.SpreadSheet;
using ExcelLibrary;

// on button click

 if (flupload.HasFile == true)
            {
                string path = Path.GetExtension(flupload.PostedFile.FileName);
                if (path == ".xls")
                {
                    string FileName = Path.GetFileName(flupload.PostedFile.FileName);
                    flupload.SaveAs(Server.MapPath("~/" + "Formats/" + FileName));
                    string FullPath = HttpContext.Current.Server.MapPath("~/" + "Formats/" + FileName);
                    string sheetname = null;
                    Workbook book = Workbook.Load(FullPath);
                    int countt = book.Worksheets.Count;
                    if (countt > 1)
                    {
                        DisplayAJAXMessage("Excel Contains More Than one Sheet !! Please Use only one Sheet in Excel File !!");
                    }
                    else
                    {
                        Worksheet sheet = book.Worksheets[0];
                        sheetname = sheet.Name;
                        if (sheetname != null)
                        {
                            exceldt = ExcelLibrary.DataSetHelper.CreateDataTable(FullPath, sheetname);
                        }
                        else
                        {
                            DisplayAJAXMessage("No Sheet in Excel File !!");
                        }
                        if (exceldt != null)
                        {
                            //check for empty list
                            bool emptycheck = true;
                            bool duplicatecheck = true;
                            bool numbercheck = true;
                            bool countreg = true;
                            bool emptyexcel = true;

                            foreach (DataRow gr in exceldt.Rows)
                            {
                                if (gr[0].ToString() == "" || gr[0].ToString() == " " || gr[0].ToString() == " ")
                                {
                                    DisplayAJAXMessage("There is empty row in excel !! Please check it ");
                                    emptycheck = false;
                                    break;
                                }

                            }

                            ArrayList dup = new ArrayList();
                            ArrayList dup1 = new ArrayList();
                            foreach (DataRow gg in exceldt.Rows)
                            {
                                dup.Add(gg[0].ToString());
                            }
                            //check for empty row
                            if (dup.Count == 0)
                            {
                                DisplayAJAXMessage("No Data Found !! Please Check The File");
                                emptyexcel = false;
                            }

                            //count registrationnumber for 400  or not
                            if (dup.Count > 400)
                            {
                                DisplayAJAXMessage("Registration Number count=" + dup.Count.ToString() + " Only 400 Registration Numbers can be promoted at a time !!");
                                countreg = false;
                            }


                            //check duplicate
                            int county = dup.Count - 1;
                            if (countreg == true)
                            {
                                for (int i = 0; i < county; i++)
                                {

                                    dup1.Add(dup[0].ToString());
                                    dup.RemoveAt(0);
                                    if (dup.Contains(dup1[i].ToString()))
                                    {
                                        duplicatecheck = false;
                                        DisplayAJAXMessage("Excel File Contains DUPLICATE values !! Please check");
                                        break;

                                    }
                                }
                            }
                            dup.Clear();
                            dup1.Clear();
                            Int64 num;

                            //check number or string
                            foreach (DataRow gr in exceldt.Rows)
                            {
                                bool isNum = Int64.TryParse(gr[0].ToString(), out num);
                                if (isNum == false)
                                {
                                    DisplayAJAXMessage("There is string in Excel instead of Registration Number !! Please check it");
                                    numbercheck = false;
                                    break;
                                }

                            }


                            //checking all constraints finally generate grid

                            if (emptycheck == true && duplicatecheck == true && numbercheck == true && countreg == true && emptyexcel == true)
                            {
// here you will do your code after checking excel extension,duplicate values ,count in one column,count of sheet ,is excel file empty or not
} 
}

Done !! :)

Comments

Post a Comment

Popular posts from this blog

Export Doc,Access,Image,CSV,Excel,Pdf,XML,HTML,Text,Print of Gridview in Asp.net

First import itextsharp.dll in the solution and use three namespces (basically for pdf) using iTextSharp.text; using iTextSharp.text.pdf; using iTextSharp.text.html.simpleparser;          Then Use the following code :- on each button  click  protected void Page_Load(object sender, EventArgs e)         {             if (!Page.IsPostBack)             {                 BindGridDetails(GridView1);             }         }                 protected DataTable BindGridDetails(GridView GridView1)         {             DataTable dt = new DataTable();             dt.Columns.Add("Student_ID", typeof(Int32));             dt.Columns.Add("Student_Name", typeof(string));             dt.Columns.Add("Education", typeof(string));             dt.Columns.Add("City", typeof(string));             DataRow dtrow = dt.NewRow();             dtrow["Student_ID"] = 1;             dtrow["Student_Name"] = "Musakkhir";   
1 comment
Read more

Ip Sec Internet Security Basic Structure

The IPsec suite is an  open standard . IPsec uses the following  protocols  to perform various functions: Authentication Headers (AH)  provide connectionless  integrity  and data origin  authentication  for IP  datagrams  and provides protection against  replay attacks . Encapsulating Security Payloads (ESP)  provide  confidentiality , data-origin  authentication , connectionless  integrity , an anti-replay service (a form of partial sequence integrity), and limited traffic-flow confidentiality. Security Associations (SA)  provide the bundle of algorithms and data that provide the parameters necessary to AH and/or ESP operations. The  Internet Security Association and Key Management Protocol  (ISAKMP) provides a framework for authentication and key exchange, with actual authenticated keying material provided either by manual configuration with pre-shared keys,  Internet Key Exchange  (IKE and IKEv2),  Kerberized Internet Negotiation of Keys  (KINK), or IPSECKEY  DNS records .
1 comment
Read more

Advent of code 2022 day 22 part 1

  function main(input, input1) {     let grid = input.split( '\n' );     grid.shift();     // only in big input     for ( let i = 0 ; i < 100 ; i++) {         grid[i] = '                                                  ' + grid[i];     }     //console.log(grid[100])     grid = grid.map(x => x.split( '' ))     // find first left top allowed     let temppos = - 1 ;     grid[ 0 ].forEach((element, i) => {         if (temppos == - 1 && element == '.' ) {             temppos = i;         }     });     let initpos = new pos(temppos, 0 )     // console.log(initpos)     // make grid equal shape     let maxlength = 0 ;     grid.forEach(el => {         if (el.length > maxlength) {             maxlength = el.length         }     })     console.log(maxlength)     grid.forEach(el => {         if (el.length < maxlength) {             let diff = maxlength - el.length;             while (diff > 0 ) {                 di
Post a Comment
Read more