Read from excel and insert into data table in c#

-

using System.Data.OleDb;
using System.IO;
 
private DataTable ReadExcelToTable(string path)
{
        //Connection String
        string extenstion = Path.GetExtension(path);
        string connstring = "";
        if (extenstion == "xls" || extenstion == "XLS")
        {
            connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
        }
        else
        {
            connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
        }
        //connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";
          
        using (OleDbConnection conn = new OleDbConnection(connstring))
        {
            DataSet set = new DataSet();
            try
            {
                conn.Open();
                //Get All Sheets Name
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //Get the First Sheet Name
                string firstSheetName = sheetsName.Rows[0]["TABLE_NAME"].ToString();

                //Query String
                string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);

                ada.Fill(set);
            }
            catch (Exception ex)
            {
                MessageBox.Show("File Loading Error, Try again \n" + ex.Message);
                return null;
            }
            return set.Tables[0];
        }
}

Comments

Post a Comment

Popular posts from this blog

Provision AWS EC2 Instance and RDS with Terraform, and Deploy Spring Boot App to EC2 Instance via GitHub Action Pipeline

-
Image
  These days, Terraform is a popular tool for managing infrastructure as code. Manually creating AWS EC2 instances and deploying Spring Boot applications to them can be challenging. In this tutorial, I will demonstrate how to use Terraform to provision an AWS EC2 instance and an AWS RDS MySQL database, and then deploy a Spring Boot project to that EC2 instance using a GitHub Action pipeline. Prerequisites: To follow this tutorial, you need to have the following prerequisites: An AWS account Terraform installed on your local machine. GitHub Account A Spring Boot Project Putty What We will do : Create AWS EC2 & RDS(MYSQL) Instance using Terraform Deploy Spring Boot Project in that EC2 Instance Using GitHub Action Create AWS EC2 & RDS(MYSQL) Instance using Terraform: Step 1: Create and Configure an AWS Free Tier Account AWS offers a free tier account with a 12-month free offer that allows you to use most of its resources for educational purposes. To get started on AWS, you fi...
Read more

JQuery UI Autocomplete, custom HTML structure for result?

-
$( '#myAutocomplete' ).autocomplete( {     source: availableTags,     multiselect: true ,     open: function ( e, ui )     {         var             acData = $( this ).data( 'ui-autocomplete' ),             styledTerm = termTemplate.replace( '%s' , acData.term);         acData             .menu             .element             .find( 'div' )             .each( function ()             {                 var me = $( this );                 console .log(me);                 me.html(me.text().replace(acData.term,                     s...
Read more

Easy Ui Jquery easyui-textbox change onChange event

-
$( '#LastName' ).textbox({ onChange: function (newValue, oldValue) { console.log( 'onchange fired...textbox, newValue:' + newValue + ";oldValue:" + oldValue); $( "#Fullname" ).textbox({ value: $ ( '#FirstName' ).val().trim() + " " + $( '#MiddleName' ).val().trim() + " " + $( '#LastName' ).val().trim() }); } }); <tr> <td> <label style= "font-size: 12px" > First Name : </label> </td> <td> @* <input name= "FirstName" id= "FirstName" class= "easyui-textbox" required= "true" ></input> *@ @* <input name= "FirstName" class= "easyui-textbox" ...
Read more