import from excel in c#

private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
{
path = openFileDialog1.FileName;
}

private void btnImport_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "excel file |*.xls";
openFileDialog1.FilterIndex = 1;
openFileDialog1.InitialDirectory = "%My Documents%";
openFileDialog1.ShowDialog(this);
// path = openFileDialog1.FileName;
if (path != "")
{
try
{
oledbConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @"; Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";
olebdConn.ConnectionString = oledbConnectionString;
oledbCmmd.Connection = olebdConn;
oledbCmmd.CommandText = "Select * FROM [Sheet1$]";
olebdConn.Open();
da =new OleDbDataAdapter(oledbCmmd);
da.Fill(ds);

dgvExcelData.Visible = true;
////dt = dr.GetData(4);
int count = ds.Tables[0].Rows.Count;
label2.Text = count.ToString();
////dgvExcelData.Rows.Clear();

dgvExcelData.DataSource = ds.Tables[0];
////dgvExcelData.Visible = true;
olebdConn.Close();
}

Export into excel in c# through COM

1. Start Microsoft Visual Studio .NET.
2. On the File menu, click New, and then click Project. Select Windows Application from the Visual C# Project types. Form1 is created by default.
3. Add a reference to the Microsoft Excel Object Library. To do this, follow these steps:
   1. On the Project menu, click Add Reference.
   2. On the COM tab, locate Microsoft Excel Object Library, and click Select.
      

-----------------------------------------------------------------------------------------------
using System.Reflection;
Excel._Application oxl;
Excel._Workbook owb;
Excel._Worksheet oWs;
Excel.Range oRange;
methord( )
{
oxl = new Excel.Application();
oxl.Visible = true;
//Get a new workbook.
owb = (Excel._Workbook)(oxl.Workbooks.Add(Missing.Value));
oWs = (Excel._Worksheet)owb.ActiveSheet;
//Add table headers going cell by cell
oWs.Cells[1, 1] = "First Name";
oWs.Cells[1, 2] = "Last Name";
oWs.Cells[1, 3] = "Full Name";
oWs.Cells[1, 4] = "Salary";

//Format A1:D1 as bold, vertical alignment = center.
oWs.get_Range("A1", "D1").Font.Bold = true;
oWs.get_Range("A1", "D1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// Create an array to multiple values at once.
string[,] saNames = new string[5, 2];
saNames[0, 0] = "John";
saNames[0, 1] = "Smith";
saNames[1, 0] = "Tom";
saNames[1, 1] = "Brown";
saNames[2, 0] = "Sue";
saNames[2, 1] = "Thomas";
saNames[3, 0] = "Jane";
saNames[3, 1] = "Jones";
saNames[ 4, 0] = "Adam";
saNames[ 4, 1] = "Johnson";
//Fill A2:B6 with an array of values (First and Last Names).
oWs.get_Range("A2", "B6").Value2 = saNames;
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
oRange = oxl.get_Range("C2", "C6");
oRange.Formula = "=A2 &\" \"& B2";
//Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRange = oxl.get_Range("D2", "D6");
oRange.Formula = "=RAND()*10000";
oRange.NumberFormat = "$0.00";
//AutoFit columns A:D.
oRange = oWs.get_Range("A1", "D1");
oRange.EntireColumn.AutoFit();
//Manipulate a variable number of columns for Quarterly Sales Data.
//DisplayQuarterlySales(oWs);

//Make sure Excel is visible and give the user control
//of Microsoft Excel's lifetime.
oxl.Visible = true;
oxl.UserControl = true;

generate unique code

private bool getVendorCode()//method to cheek manufacture code and generate code
{
string vendorName = txtNameVandor.Text.Replace("'", "").Replace("~", "").Replace("`", "").Replace("!", "").Replace("@", "").Replace("#", "").Replace("$", "").Replace("%", "").Replace("^", "").Replace("&", "").Replace("*", "").Replace(" ", "");
vendorName = vendorName .Replace("(", "").Replace(")", "").Replace("-", "").Replace("_", "").Replace("+", "").Replace("=", "").Replace("|", "").Replace("\\", "").Replace("{", "").Replace("}", "").Replace("[", "").Replace("]", "").Replace(":", "").Replace("\"", "").Replace(";", "").Replace("'", "").Replace("<", "").Replace(",", "").Replace(">", "").Replace(".", "").Replace("?", "").Replace("/", "");
if (vendorName .Length < tempmanucode = "" vendorname =" vendorName" strsqlquery = "select count(vendor_code) from vendors where vendor_code like '" i =" (Int32)objAccessingBusinessLayer.executeScaler(objAllVariables.strSqlQuery);"> 0)
{
i = i + 1;
tempManuCode = "000" + i;
}
else
{
tempManuCode = "001";
}
vendorName = vendorName + tempManuCode.Substring(tempManuCode.Length - 3);
//objAllVariables.strVenodrCode = vendorName ;


}
catch (Exception er)
{
objCommonMethods.ShowMessageBox(er.Message, objComNam.Caption_Warning, objComNam.Type_Warning);

}
}
if (vendorName .Length < 6)
{
objCommonMethods.ShowMessageBox("Error: Unable To Generate Vendor Code", objComNam.Caption_Warning, objComNam.Type_Warning);
return false;
}


objAllVariables.strVenodorCode = vendorName ;
return true;

}

search in treeView

private void findIntree(TreeNodeCollection tNodeCollection, string p)
{

foreach (TreeNode tNode in tNodeCollection)
{
if (tNode.Name == p)
{

tNode.TreeView.SelectedNode = tNode;
//tNode.= Color.SteelBlue;
tNode.TreeView.SelectedNode.Expand();
break;
}
//else
//{
// tNode.ForeColor = Color.Black;
// //tNode.TreeView.SelectedNode.Collapse();
//}
findIntree(tNode.Nodes, p);
}
}

implement cheakbox in datagrid view of windows formc

code for Add in data from one grid to another grid on basis of selected cheakbox

private void addL5()//Add on select cheakbox;
{
int j = dgvMyL5.Rows.Count;
foreach (DataGridViewRow drow in dgvAllL5.Rows)
{

if (drow.Cells["select1"].Value != null)
{
if (Boolean.Parse(drow.Cells["select1"].Value.ToString()) == true)
{
if (j == 0)
{

dgvMyL5.Rows.Add();
dgvMyL5["serialNo2", j].Value = j + 1;
dgvMyL5["myComponentsFixedID", j].Value = drow.Cells["ComponentsFixedID"].Value;
dgvMyL5["componentsName", j].Value = drow.Cells["L5ComponentsName"].Value;
dgvMyL5["factoryPrice", j].Value = 0;
j += 1;

}
else
{
int flag = 0;
foreach (DataGridViewRow drow2 in dgvMyL5.Rows)
{
if (drow.Cells["ComponentsFixedID"].Value.ToString() == drow2.Cells["myComponentsFixedID"].Value.ToString())
{
flag = 1;
break;
}
else
{
flag = 0;
}
}
if (flag == 0)
{
dgvMyL5.Rows.Add();
dgvMyL5["serialNo2", j].Value = j + 1;
dgvMyL5["myComponentsFixedID", j].Value = drow.Cells["ComponentsFixedID"].Value;
dgvMyL5["componentsName", j].Value = drow.Cells["L5ComponentsName"].Value;
dgvMyL5["factoryPrice", j].Value = 0;
j += 1;
}
}
}
}

}
}


code for Remove data from one grid to another grid on basis of selected cheakbox


private void removeL4()
{
int length = dgvMyL5.Rows.Count;
for (int i = length - 1; i > -1; i--)
{
if (dgvMyL5.Rows[i].Cells["select2"].Value != null)
{
if (Boolean.Parse(dgvMyL5.Rows[i].Cells["select2"].Value.ToString()) == true)
{

dgvMyL5.Rows.RemoveAt(i);

}
}
}
}

creating a SaveFileDialog

private void button1_Click(object sender, System.EventArgs e)
{
  Stream myStream ;
  SaveFileDialog saveFileDialog1 = new SaveFileDialog();

  saveFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"  ;
  saveFileDialog1.FilterIndex = 2 ;
  saveFileDialog1.RestoreDirectory = true ;

  if(saveFileDialog1.ShowDialog() == DialogResult.OK)
  {
      if((myStream = saveFileDialog1.OpenFile()) != null)
      {
          // Code to write the stream goes here.
          myStream.Close();
      }
  }
}

to calculate date time

date Time event and code for calculating day and validate start date nd end date



private void dateTimePickerEnd_ValueChanged(object sender, EventArgs e)
{
dateTimePickerEnd.MinDate = dateTimePickerStart.Value;

DateTime start = DateTime.Parse(dateTimePicker1.Text);
DateTime end = DateTime.Parse(dateTimePicker2.Text);
//dateTimePicker2.MinDate = dateTimePicker1.Value;
if (end.Day >= start.Day)
{
TimeSpan ts = end.Subtract(start);
string ss = ts.TotalDays.ToString();

string s = ts.Days.ToString();
textBox1.Text = (s);
//string time = new DateTime(ts.Ticks);
//MessageBox.Show(time);
}

About Dot Net

I love dot net ...this one of very great platform for software developer, which give comprehensive
tools and other thing to develop good software . During coding we lack in some way ,here we can help each other to shout out some problem and also we can share some ideas too