Solved

read excel spreadsheet using c# .net

Posted on 2014-12-31
8
474 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 70

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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

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

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 70

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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