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(); } }