Solved

read excel spreadsheet using c# .net

Posted on 2014-12-31
8
430 Views
Last Modified: 2015-01-05
i have an excel spreadsheet . There are 2 columns in that spreadsheet.
Column1 -Name of employee
Column2 - Contract Expiry Date
I want to write an app in c# which reads that spreadsheet, checks Column2 to see f the "Contract Expiry Date" is within 30 days and then it should send email to the admin.    
How can i write such app?

Thanks
0
Comment
Question by:ts84zs
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 69

Assisted Solution

by:Éric Moreau
Éric Moreau earned 100 total points
ID: 40525519
0
 
LVL 16

Assisted Solution

by:Rose Babu
Rose Babu earned 100 total points
ID: 40527323
Hi,

Have a look on the below tutorials which will help you read excel data...

http://csharp.net-informations.com/excel/csharp-excel-oledb.htm 

http://www.codeproject.com/Tips/509179/Read-Excel-File-into-DataSet-in-ASP-NET-Using-Csha

Read the dataset and do your email process.

--
Rose
0
 
LVL 12

Expert Comment

by:jitendra patil
ID: 40527383
please try the below code

  using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using System.Xml;
using System.Data.OleDb;
 public static void Main(string[] args)
        {
            string fileLocation = System.IO.Path.GetFullPath(@"../../ExcelFiles/Employees.xlsx");
            string connectionString = "";
            string fileExtension = Path.GetExtension(fileLocation);
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }

            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            //Create OleDB Connection and OleDb Command
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            con.Close();

            DateTime startdate = DateTime.Now.Date;
            DateTime enddate = DateTime.Now.Date.AddDays(30);

            foreach (DataRow dr in dtExcelRecords.Rows)
            {
                DateTime DT = DateTime.Parse(dr["Contract expiry date"].ToString());

                if (DT >= startdate && DT <= enddate)
                {
                    Console.WriteLine("Employee Name =>" + dr["Employee name"].ToString() + "Contract Expiry Date =>" + DT.ToShortDateString()); 
 //instead of above line you can write the email sending code here.

                }
            }

            Console.ReadLine();
        }

Open in new window

hope this helps.
0
 

Author Comment

by:ts84zs
ID: 40527804
thanks  a lot
i m looking into it
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:ts84zs
ID: 40527810
can i use odbc instead of oledb also is oledb future OK?

how do i send email from c# thanks
0
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40527814
0
 
LVL 12

Accepted Solution

by:
jitendra patil earned 300 total points
ID: 40528848
as far as i know oledb would be good approach, as i am being using it from last couple of years.

try the below complete code
  using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.IO;
using System.Xml;
using System.Data.OleDb;
//Newly added 
using System.Net.Mail;

 public static void Main(string[] args)
        {
            string fileLocation = System.IO.Path.GetFullPath(@"../../ExcelFiles/Employees.xlsx");
            string connectionString = "";
            string fileExtension = Path.GetExtension(fileLocation);
            if (fileExtension == ".xls")
            {
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
            }

            else if (fileExtension == ".xlsx")
            {
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
            }
            //Create OleDB Connection and OleDb Command
            OleDbConnection con = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.Connection = con;
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
            DataTable dtExcelRecords = new DataTable();
            con.Open();
            DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
            cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
            dAdapter.SelectCommand = cmd;
            dAdapter.Fill(dtExcelRecords);
            con.Close();

            DateTime startdate = DateTime.Now.Date;
            DateTime enddate = DateTime.Now.Date.AddDays(30);

          StringBuilder SB = new StringBuilder();
           SB.Append("<table>");
           SB.Append("<tr><td><b>Employee name</b></td><td><b>Contract Expiry Date</b></td></tr>");


            foreach (DataRow dr in dtExcelRecords.Rows)
            {
                DateTime DT = DateTime.Parse(dr["Contract expiry date"].ToString());

                if (DT >= startdate && DT <= enddate)
                {
			SB.Append("<tr><td style=\"text-align:center\">" + dr["Employee name"].ToString() + "</td><td style=\"text-align:center\">" + DT.ToShortDateString() + "</td></tr>");

                   //Console.WriteLine("Employee Name =>" + dr["Employee name"].ToString() + "Contract Expiry Date =>" + DT.ToShortDateString()); 
                   //instead of above line you can write the email sending code here.

                }
            }
	SB.Append("</table>");

	      //Email Sending code 
              // here showing the sample code for using gmail account, you can configure it and get the SMTP info from network admin of your company

  try
         {
                MailMessage mail = new MailMessage();
                SmtpClient SmtpServer = new SmtpClient("smtp.gmail.com");

                mail.From = new MailAddress("your_email_address@gmail.com");
                mail.To.Add("to_address");
                mail.Subject = "Test Mail";
                mail.Body = SB.ToString();
		mail.IsBodyHtml = true;

                SmtpServer.Port = 587;
                SmtpServer.Credentials = new System.Net.NetworkCredential("username", "password");
                SmtpServer.EnableSsl = true;

                SmtpServer.Send(mail);
                MessageBox.Show("mail Send");
	}

       catch(Exception ex)
{
   Console.WriteLine(ex.ToString());
}	
            Console.ReadLine();
        }
                                          

Open in new window

hope this helps.
0
 

Author Comment

by:ts84zs
ID: 40530677
thanks a lot.. that will work...

Also, I have to schedule a task to run that application on a server with operating system - windows server 2008...
MS Office or excel is not installed on that server... Which libraries should I copy to make it work...(In case of odbc or oledb)  
pl help thanks a lot
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
which object should i use (efficiency) 5 45
LINQ - C# to VB convertion 12 46
Angular JS Route 3 45
Counting connections to SQL Server through C# 3 27
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now