using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
namespace dt_to_excel
{
public partial class Form2 : Form
{
private static Microsoft.Office.Interop.Excel.Workbook mWorkBook;
private static Microsoft.Office.Interop.Excel.Sheets mWorkSheets;
private static Microsoft.Office.Interop.Excel.Worksheet mWSheet1;
private static Microsoft.Office.Interop.Excel.Application oXL;
public Form2()
{
InitializeComponent();
}
private void Form2_Load(object sender, EventArgs e)
{
Microsoft.Win32.RegistryKey key = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\Microsoft\\Windows\\CurrentVersion\\Run", true);
key.SetValue("BReminder", @"C:\Users\swteam\Desktop\BReminder.exe");
string userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
if (File.Exists(@"C:\Users\swteam\Desktop\Book1.xls"))
{
checbday();
}
else
{
createNewFile();
}
}
private void checbday()
{
string Month;
string Date;
string name;
string message;
try
{
string path = @"C:\Users\swteam\Desktop\Book1.xls";
System.Data.DataTable dt = new System.Data.DataTable();
dt = exceldata(path);
string[] columnNames = dt.Columns.Cast<DataColumn>()
.Select(x => x.ColumnName)
.ToArray();
int dtRows = dt.Rows.Count;
int totalRows = ++dtRows;
for (int i = 2; i <= totalRows; i++)
{
DataRow[] dr = dt.Select("Id = '"+i+"'");
DateTime Todaydate = DateTime.Today.Date;
if (dr.Length > 0)
{
name = dr[0]["Name"].ToString();
Month = dr[0]["Month"].ToString();
Date = dr[0]["Date"].ToString();
message = dr[0]["MessageDesc"].ToString();
if ((Convert.ToString(Todaydate.Month) == Month) && (Date == Convert.ToString(Todaydate.Day)))
MessageBox.Show("Reminder>>" + name + ">>" + message + " today");
}
}
}
catch (Exception)
{ }
}
private System.Data.DataTable exceldata(string filePath)
{
System.Data.DataTable dtexcel = new System.Data.DataTable();
bool hasHeaders = false;
string HDR = hasHeaders ? "Yes" : "No";
string strConn;
if (filePath.Substring(filePath.LastIndexOf('.')).ToLower() == ".xlsx")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
System.Data.DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//Looping Total Sheet of Xl File
/*foreach (DataRow schemaRow in schemaTable.Rows)
{
}*/
//Looping a first Sheet of Xl File
DataRow schemaRow = schemaTable.Rows[0];
string sheet = schemaRow["TABLE_NAME"].ToString();
if (!sheet.EndsWith("_"))
{
string query = "SELECT * FROM [Sheet1$]";
OleDbDataAdapter daexcel = new OleDbDataAdapter(query, conn);
dtexcel.Locale = CultureInfo.CurrentCulture;
daexcel.Fill(dtexcel);
}
conn.Close();
return dtexcel;
}
public static void ReadExistingExcel()
{
}
private void button1_Click(object sender, EventArgs e)
{
try
{
string path = @"C:\Users\swteam\Desktop\Book1.xls";
if (File.Exists(@"C:\Users\swteam\Desktop\Book1.xls"))
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Workbooks workbooks = oXL.Workbooks;
//mWorkBook = workbooks.Open(path, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
//Get all the sheets in the workbook
mWorkSheets = mWorkBook.Worksheets;
//Get the allready exists sheet
mWSheet1 = (Microsoft.Office.Interop.Excel.Worksheet)mWorkSheets.get_Item("Sheet1");
Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
int colCount = range.Columns.Count;
int rowCount = range.Rows.Count;
for (int index = 1; index <= 1; index++)
{
int rowNo = rowCount+1;
mWSheet1.Cells[rowCount + index, 1] = rowNo;
mWSheet1.Cells[rowCount + index, 2] = textBox1.Text;
mWSheet1.Cells[rowCount + index, 3] = textBox2.Text;
mWSheet1.Cells[rowCount + index, 4] = textBox3.Text;
mWSheet1.Cells[rowCount + index, 5] = textBox4.Text;
mWSheet1.Cells[rowCount + index, 6] = textBox5.Text;
}
mWorkBook.SaveAs(path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
mWorkBook.Close(Missing.Value, Missing.Value, Missing.Value);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
else
{
createNewFile();
}
label6.Text = "Added Successfully";
}
catch (Exception w)
{
MessageBox.Show(w.ToString());
}
}
private void clear()
{
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
label6.Text = "";
}
private void createNewFile()
{
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlWorkSheet.Cells[1, 1] = "Id";
xlWorkSheet.Cells[1, 2] = "Name";
xlWorkSheet.Cells[1, 3] = "Date";
xlWorkSheet.Cells[1, 4] = "Month";
xlWorkSheet.Cells[1, 5] = "Year";
xlWorkSheet.Cells[1, 6] = "MessageDesc";
xlWorkBook.SaveAs(@"C:\Users\swteam\Desktop\Book1.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created..");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
private void button2_Click(object sender, EventArgs e)
{
clear();
}
}
}
