?
Solved

read excel spreadsheet using c# .net

Posted on 2014-12-31
8
Medium Priority
?
516 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 400 total points
ID: 40525519
0
 
LVL 16

Assisted Solution

by:Rose Babu
Rose Babu earned 400 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 13

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
Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

 

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 13

Accepted Solution

by:
Jitendra Patil earned 1200 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

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

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 is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

752 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