private void bindGridview(object sender, EventArgs e)
{
string connectionString = "";
if (FileUpload.FileName == "")
{
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('Please Select/Browse to Upload the File!')</script>");
}
if (FileUpload.HasFile)
{
string savePath = ConfigurationManager.AppSettings["UploadUserPath"];
string strFileType = Path.GetExtension(FileUpload.FileName).ToLower();
// string path = string.Concat(Server.MapPath(savePath + F_Upload.FileName));
// F_Upload.PostedFile.SaveAs(path);
string path = string.Concat(savePath + "/" + FileUpload.FileName);
FileUpload.SaveAs(path);
if (strFileType == ".xls")
{
//connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
connectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"", path);
}
/*else if (strFileType == ".xlsx")
* {
* //connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
* connectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=0\"", path);
* }*/
else
{
validExcel = 0;
return;
}
string query = "SELECT * FROM [Sheet1$]";
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridExcelData.DataSource = ds.Tables[0];
GridExcelData.DataBind();
da.Dispose();
conn.Close();
conn.Dispose();
}
}
protected void GridExcelData_PageChanged(object sender, GridViewPageEventArgs e)
{
GridExcelData.PageIndex = e.NewPageIndex;
GridExcelData.DataBind();
}
protected void bindGridview(object sender, EventArgs e)
{
log.Debug("Inside bindGridview function");
User_Mangement b = new User_Mangement();
DataSet ds = b.GetHREmpData();
if (ds.Tables[0].Rows.Count == 0)
{
log.Debug("No records to update or insert");
ClientScript.RegisterStartupScript(Page.GetType(), "validation1", "<script language='javascript'>alert('No HR record exists to update or add')</script>");
return;
}
else
{
string connectionString = "";
string isfilegenerate = ConfigurationManager.AppSettings["UserExcelFileGenerate"].ToString();
if (isfilegenerate == "Y")
{
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets xlSheets = null;
Worksheet xlWorksheet = null;
object misValue = System.Reflection.Missing.Value;
System.Data.DataTable dataTable = ds.Tables[0];
int rowNo = dataTable.Rows.Count;
int columnNo = dataTable.Columns.Count;
int colIndex = 0;
//Create Excel Sheets
xlSheets = xlWorkbook.Sheets;
xlWorksheet = (Worksheet)xlSheets.Add(xlSheets[1],
Type.Missing, Type.Missing, Type.Missing);
//xlWorksheet.Name = dataSet.DataSetName;
//Generate Field Names
foreach (DataColumn dataColumn in dataTable.Columns)
{
colIndex++;
xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
}
object[,] objData = new object[rowNo, columnNo];
//Convert DataSet to Cell Data
for (int row = 0; row < rowNo; row++)
{
for (int col = 0; col < columnNo; col++)
{
objData[row, col] = dataTable.Rows[row][col];
}
}
//Add the Data
Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
range.Value2 = objData;
//Remove the Default Worksheet
((Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();
string servername = ConfigurationManager.AppSettings["ServerName"].ToString();
string domainame = ConfigurationManager.AppSettings["DomainName"].ToString();
string username = ConfigurationManager.AppSettings["UserName"].ToString();
string password = ConfigurationManager.AppSettings["Password"].ToString();
string mainpath = ConfigurationManager.AppSettings["UploadUserPath"].ToString();
string timestamp = DateTime.Now.ToString("yyyy-MM-dd-hh_mm_ss");
string ExcelFileVersion = ConfigurationManager.AppSettings["ExcelFileVersion"].ToString();
string FileName = "HRUserData" + timestamp + ExcelFileVersion;
using (NetworkShareAccesser.Access(servername, domainame, username, password))
{
if (Directory.Exists(mainpath))
{
path = Path.Combine(mainpath, FileName);
// xlWorkbook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.SaveAs(path,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
xlWorkbook.Close();
xlApp.Quit();
}
else
{
Directory.CreateDirectory(mainpath);
path = Path.Combine(mainpath, FileName);
// xlWorkbook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkbook.SaveAs(path,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
XlSaveAsAccessMode.xlNoChange,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
xlWorkbook.Close();
xlApp.Quit();
}
}
}
//string query = "SELECT * FROM [Sheet2$]";
//connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//OleDbConnection conn = new OleDbConnection(connectionString);
//if (conn.State == ConnectionState.Closed)
// conn.Open();
//OleDbCommand cmd = new OleDbCommand(query, conn);
//OleDbDataAdapter da = new OleDbDataAdapter(cmd);
//DataSet ds1 = new DataSet();
//da.Fill(ds1);
GridExcelData.DataSource = ds.Tables[0];
GridExcelData.DataBind();
//da.Dispose();
//conn.Close();
//conn.Dispose();
}
}
protected void bindGridview(object sender, EventArgs e)
{
string connectionString = "";
if (F_Upload.HasFile)
{
string savePath = ConfigurationManager.AppSettings["UploadPath"];
string strFileType = Path.GetExtension(F_Upload.FileName).ToLower();
// string attachmentFile = F_Upload.PostedFile.FileName;
//string path = string.Concat(Server.MapPath(savePath + F_Upload.FileName));
//F_Upload.PostedFile.SaveAs(path);
string path = string.Concat(savePath + "/" + F_Upload.FileName);
F_Upload.SaveAs(path);
string attachmentFile = F_Upload.PostedFile.FileName;
if (strFileType == ".xls")
{
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (strFileType == ".csv")
{
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"text;HDR=Yes;FMT=Fixed\"";
// connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"";
//connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(path) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"";
}
else
{
validExcel = 0;
return;
}
string query = String.Format("SELECT * FROM {0}", Path.GetFileName(path));
// string query = String.Format("SELECT * FROM [S_JVUPLOADcsv$]", Path.GetFileName(path));
string filename = null;
filename = Path.GetFileName(path);
string[] fname = filename.Split('_');
string nmae = fname[0];
// OleDbConnection conn = new OleDbConnection(connectionString);
// OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(path) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
OleDbConnection conn = new OleDbConnection(connectionString);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
OleDbCommand cmd = new OleDbCommand(query, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
GridExcelData.DataSource = ds.Tables[0];
GridExcelData.DataBind();
int c = 0;
c = GridExcelData.Rows.Count;
da.Dispose();
conn.Close();
conn.Dispose();
}
}
