private void Btn_Import_Click(object sender, EventArgs e)
{
try
{
FileStream stream = File.Open(@"E:\Projects\Sheet1.xls", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
DataSet result = excelReader.AsDataSet();
excelReader.Close();
System.Data.DataTable table = result.Tables[0];
string date = string.Empty;
foreach(DataRow row in table.Rows)
{
if (!string.IsNullOrEmpty(row[0].ToString()) && row[0].ToString() != "Date")
{
row[0] = DateTime.FromOADate(double.Parse(row[0].ToString())).ToString("dd-MMM");
date = row[0].ToString();
}
else
{
row[0] = date;
}
}
datagridView.DataSource = table;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error in Import");
return;
}
finally
{
GFun.OleDbCon.Close();
}
}
try
{
PBar.Value = 1;
PBar.Visible = true;
for (int i = 1; i < datagridView.RowCount; i++)
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
for (int j = 3; j < datagridView.ColumnCount; j++)
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
//if (datagridView[j, i].Value != DBNull.Value && datagridView[j, i].Value != "" && datagridView[j, i].Value != null && datagridView[j,i].Value != "0")
if (datagridView[j, i].Value != DBNull.Value && datagridView[j, i].Value.ToString() != "" && datagridView[j, i].Value != null)
{
if (datagridView[j, i].Value != null)
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
if (datagridView[j, i].Value.ToString().Contains("/"))
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
//split the course
string strvalue = datagridView[j, i].Value.ToString();
string[] strarr = strvalue.Split('/');
foreach(object obj in strarr)
{
try
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
sql = "insert into Tb_SCh_TIme_Table([Sch_Date], [Session], [Course],[Faculty_Code])" + " values ";
sql = sql + "('" + Convert.ToDateTime(datagridView.Rows[i].Cells[0].Value.ToString()) + "', " + int.Parse(datagridView.Rows[i].Cells[1].Value.ToString()) + ",'" + obj.ToString() + "','" + datagridView.Columns[j].HeaderText.ToString() + "')";
GFun.Error = "";
GFun.InsertAccessData(sql);
if (GFun.Error.ToString() != "")
{
PBar.Visible = false;
MessageBox.Show(GFun.Error.ToString(), "Error");
this.Cursor = Cursors.Arrow;
return;
}
GFun.OleDbCon.Close();
}
catch (Exception Ex)
{
PBar.Visible = false;
MessageBox.Show(Ex.ToString(), "Error");
this.Cursor = Cursors.Arrow;
return;
}
}
}
else
{
try
{
if (PBar.Value >= 100)
PBar.Value = 1;
else
PBar.Value = PBar.Value + 40;
sql = "insert into Tb_SCh_TIme_Table([Sch_Date], [Session], [Course],[Faculty_Code])" + " values ('" + Convert.ToDateTime(datagridView.Rows[i].Cells[0].Value.ToString()) + "', " + int.Parse(datagridView.Rows[i].Cells[1].Value.ToString()) + ", '" + datagridView[j, i].Value.ToString() + "','" + datagridView.Columns[j].HeaderText.ToString() + "')";
GFun.Error = "";
GFun.InsertAccessData(sql);
if (GFun.Error.ToString() != "")
{
PBar.Visible = false;
MessageBox.Show(GFun.Error.ToString(), "Error");
this.Cursor = Cursors.Arrow;
return;
}
GFun.OleDbCon.Close();
}
catch (Exception Ex)
{
PBar.Visible = false;
MessageBox.Show(Ex.ToString(), "Error");
this.Cursor = Cursors.Arrow;
return;
}
}
}
}
}
this.Cursor = Cursors.Arrow;
}
PBar.Visible = false;
}
catch (Exception Ex1)
{
PBar.Visible = false;
MessageBox.Show(Ex1.ToString(), "Error in SaveDetails", MessageBoxButtons.OK);
}
PBar.Visible = false;