Solved

read excel spreadsheet using c# .net

Posted on 2014-12-31
8
439 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 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
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: 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

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 …
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

786 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