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

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

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

npm install gets stuck at fetchMetadata

-
  This is how I resolved this after spending half an hour: npm config set registry http://registry.npmjs.org/ --global npm cache clear --force setting package-lock.json to  {}  only npm install --verbose node: v12.14.1 npm: v6.13.4 This issue occure when I tried running  ng update  on angular 6 app to update it to angular 9.
Read more