Solved

read excel spreadsheet using c# .net

Posted on 2014-12-31
8
422 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

758 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

21 Experts available now in Live!

Get 1:1 Help Now