protected void ButtonUpload_Click(object sender, EventArgs e) { try { string SessionCode = DateTime.Now.ToString("ddMMyyHHmmss"); #region Silahkan pilih file if (!FileUploadExcel.HasFile) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, "Silahkan pilih file"); return; } #endregion #region Format file harus .xls if (Path.GetExtension(FileUploadExcel.FileName) != ".xls") { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, "Format file harus .xls"); return; } #endregion string Folder = Server.MapPath("/file_excel/Kurir/Import/"); //MEMBUAT FOLDER JIKA FOLDER TIDAK DITEMUKAN if (!Directory.Exists(Folder)) { Directory.CreateDirectory(Folder); } PenggunaLogin Pengguna = (PenggunaLogin)Session["PenggunaLogin"]; string LokasiFile = Folder + Pengguna.Store + " Import Kurir " + DateTime.Now.ToString("d MMMM yyyy hh.mm.ss") + ".xls"; FileUploadExcel.SaveAs(LokasiFile); #region Silahkan ulangi proses upload if (!File.Exists(LokasiFile)) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, "Silahkan ulangi proses upload"); return; } #endregion ImportExcel_Class ImportExcel_Class = new ImportExcel_Class(Pengguna, LokasiFile); ImportExcel_Class.ImportKurir(); if (!string.IsNullOrWhiteSpace(ImportExcel_Class.Message)) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, ImportExcel_Class.Message); } //else // Response.Redirect("/WITReport/PerpindahanStok/ProdukDetail.aspx?keterangan=Import Excel " + SessionCode + "&returnUrl=/WITAdministrator/Produk/Default.aspx", false); } catch (Exception ex) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, ex.Message); LogError_Class LogError = new LogError_Class(ex, Request.Url.PathAndQuery); } }
private void guardarEstudiante2() { string filename = Path.GetFileName(FileUploadExcel.FileName); FileUploadExcel.SaveAs(Server.MapPath("~/") + filename); string path = Server.MapPath("~/") + filename; SLDocument s1 = new SLDocument(path); int fila = 2; while (!string.IsNullOrEmpty(s1.GetCellValueAsString(fila, 1))) { estudiante.Cedula = s1.GetCellValueAsString(fila, 1); estudiante.Apellidos = s1.GetCellValueAsString(fila, 2); estudiante.Nombres = s1.GetCellValueAsString(fila, 3); estudiante.Genero = s1.GetCellValueAsString(fila, 4); estudiante.EstadoCivil = s1.GetCellValueAsString(fila, 5); estudiante.Discapacidad = s1.GetCellValueAsString(fila, 6); estudiante.Etnia = s1.GetCellValueAsString(fila, 7); estudiante.Telefono = s1.GetCellValueAsString(fila, 8); estudiante.Celular = s1.GetCellValueAsString(fila, 9); estudiante.Correo = s1.GetCellValueAsString(fila, 10); estudiante.CorreoUta = s1.GetCellValueAsString(fila, 11); estudiante.FechaNacimiento = s1.GetCellValueAsString(fila, 12); estudiante.Parroquia = s1.GetCellValueAsString(fila, 13); estudiante.Barrio = s1.GetCellValueAsString(fila, 14); estudiante.CallePrincipal = s1.GetCellValueAsString(fila, 15); estudiante.CalleSecundaria = s1.GetCellValueAsString(fila, 16); estudiante.NumeroCasa = s1.GetCellValueAsString(fila, 17); estudiante.Matricula = s1.GetCellValueAsString(fila, 18); estudiante.Folio = s1.GetCellValueAsString(fila, 19); estudiante.Factura = s1.GetCellValueAsString(fila, 20); estudiante.Valor = s1.GetCellValueAsDouble(fila, 21); estudiante.Canton = s1.GetCellValueAsString(fila, 22); estudiante.Provincia = s1.GetCellValueAsString(fila, 23); estudiante.Carrera = s1.GetCellValueAsString(fila, 24); estudiante = Negocio.GuardarEstudianteNegocio2(estudiante); fila++; } if (estudiante != null) { LabelMensaje.Visible = true; LabelMensaje.Text = "Se insertó Correctamente "; } else { LabelMensaje.Visible = true; LabelMensaje.Text = "Error el estudiante ya existe"; } }
protected void ButtonImport_Click(object sender, EventArgs e) { if (!FileUploadExcel.HasFile) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, "File tidak ditemukan"); return; } if (Path.GetExtension(FileUploadExcel.FileName) != ".xls") { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, "Format file harus .xls"); return; } PenggunaLogin penggunaLogin = (PenggunaLogin)Session["PenggunaLogin"]; string lokasiFolder = Server.MapPath("/file_excel/Survei/Import/"); if (!Directory.Exists(lokasiFolder)) { Directory.CreateDirectory(lokasiFolder); } string lokasiFile = lokasiFolder + "Import Survei " + DateTime.Now.ToString("d MMMM yyyy hh.mm.ss") + ".xls"; FileUploadExcel.SaveAs(lokasiFile); if (File.Exists(lokasiFile)) { ImportExcel_Class ImportExcel_Class = new ImportExcel_Class(penggunaLogin, lokasiFile); int Result = ImportExcel_Class.ImportSurvei(); //Terjadi Error if (ImportExcel_Class.Message != null || Result == 0) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, ImportExcel_Class.Message); } else { Response.Redirect("Pengaturan.aspx?id=" + Result); } } }
protected void ButtonUpload_Click(object sender, EventArgs e) { if (FileUploadExcel.HasFile) { Server.ScriptTimeout = 1000000; PenggunaLogin penggunaLogin = (PenggunaLogin)Session["PenggunaLogin"]; string Folder = string.Empty; Folder = Server.MapPath("/file_excel/Akuntansi/Import/"); string lokasiFile = Folder + "Import Akuntansi " + DateTime.Now.ToString("d MMMM yyyy hh.mm.ss") + ".xls"; if (!Directory.Exists(Folder)) { Directory.CreateDirectory(Folder); } FileUploadExcel.SaveAs(lokasiFile); if (File.Exists(lokasiFile)) { ImportExcel_Class _ImportExcel_Class = new ImportExcel_Class(penggunaLogin, lokasiFile); var _result = _ImportExcel_Class.ImportPemasukanAkuntansi(); //Terjadi Error if (_ImportExcel_Class.Message != null) { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Danger, _ImportExcel_Class.Message); } else { LiteralWarning.Text = Alert_Class.Pesan(TipeAlert.Success, "Import Excel selesai"); RepeaterJurnal.DataSource = _result["DataImport"]; RepeaterJurnal.DataBind(); } } } }
protected void ButtonUpdate_Click(object sender, EventArgs e) { int countNewProducts = 0; int countUpdateProducts = 0; int countBadProducts = 0; int result = 0; if (FileUploadExcel.HasFile) { DataSet ds = new DataSet(); string fileLocation = string.Concat((Server.MapPath("~/temp/" + FileUploadExcel.FileName))); FileUploadExcel.PostedFile.SaveAs(fileLocation); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; string fileExtension = System.IO.Path.GetExtension(FileUploadExcel.FileName); //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return; } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); string query = string.Format("Select * from [{0}]", excelSheets[0]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1)) { dataAdapter.Fill(ds); } if (fileExtension.ToString().ToLower().Equals(".xml")) { FileUploadExcel.SaveAs(fileLocation); XmlTextReader xmlreader = new XmlTextReader(fileLocation); // DataSet ds = new DataSet(); ds.ReadXml(xmlreader); xmlreader.Close(); } int columnNameNumber = Convert.ToInt32(TextBoxColumnNumber.Text) - 1; int columnPriceNumber = Convert.ToInt32(TextBoxPriceNumber.Text) - 1; RegexOptions options = RegexOptions.None; Regex regex = new Regex(@"[ ]{2,}", options); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { Product item = new Product(); try { item.Name = ds.Tables[0].Rows[i][columnNameNumber].ToString().Trim().TrimStart('+').TrimStart('*'); item.Name = regex.Replace(item.Name, @" "); item.Price = Convert.ToDouble(ds.Tables[0].Rows[i][columnPriceNumber].ToString()); item.UserID = User.UserId; item.WhenDate = DateTime.Now; result = DataHelper.UpdateProduct(item); if (result == 0) { countBadProducts++; } if (result == 1) { countNewProducts++; } if (result == 2) { countUpdateProducts++; } } catch { } } } LabelResult.Text = string.Format("Добавлено новых: {0}, обновлено старых: {1}, не загрузилось {2}", countNewProducts, countUpdateProducts, countBadProducts); FillGrid(); }
//Uploading recipients through MS-Excel Sheet protected void btnUploadExcel_Click(object sender, EventArgs e) { int userId = Convert.ToInt32(Session["LoggedIn"]); string cs = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; string excelfile = FileUploadExcel.PostedFile.FileName; if (excelfile.EndsWith("xls") || excelfile.EndsWith("xlsx")) { lblWrongExcel.Visible = false; string path = Server.MapPath("~/UploadedExcel/" + excelfile); if (File.Exists(path)) { File.Delete(path); } FileUploadExcel.SaveAs(path); string categoryName = ""; for (int j = 0; j < excelfile.Length; j++) { if (excelfile[j] == '.') { break; } else { categoryName = categoryName + excelfile[j]; } } int temp = 0; int categoryId = 0; using (SqlConnection con = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand("SELECT count(*) FROM tblCategory WHERE categoryName ='" + categoryName + "' and userID='" + userId + "'")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; con.Open(); temp = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); } } if (temp == 1) { using (SqlCommand cmd = new SqlCommand("SELECT categoryId FROM tblCategory WHERE categoryName ='" + categoryName + "'")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; con.Open(); categoryId = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); } } } else { string query = "INSERT INTO tblCategory(userId, categoryName) VALUES(@userId,@categoryName)"; using (SqlConnection connect = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = connect; connect.Open(); cmd.Parameters.AddWithValue("@userId", userId); cmd.Parameters.AddWithValue("@categoryName", categoryName); cmd.ExecuteNonQuery(); con.Close(); } } using (SqlCommand cmd = new SqlCommand("SELECT categoryId FROM tblCategory WHERE categoryName ='" + categoryName + "'")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; con.Open(); categoryId = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); } } } } Excel.Application application = new Excel.Application(); Excel.Workbook workbook = application.Workbooks.Open(path); Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet; Excel.Range range = worksheet.UsedRange; int rowCount = range.Rows.Count; int colCount = range.Columns.Count; { List <tblRecipients> datacheckUpload = new List <tblRecipients>(); for (int row = 2; row <= rowCount; row++) { if (((Excel.Range)range.Cells[row, 1]).Text.ToString() != "") { datacheckUpload.Add(new tblRecipients { name = ((Excel.Range)range.Cells[row, 1]).Text.ToString(), email = ((Excel.Range)range.Cells[row, 2]).Text.ToString(), }); } else { goto here; } } here: foreach (var item in datacheckUpload) { if (item.name != "") { int exist = 0; using (SqlConnection con = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand("SELECT count(*) FROM tblRecipients WHERE email ='" + item.email + "'and categoryId ='" + categoryId + "'")) { using (SqlDataAdapter sda = new SqlDataAdapter()) { cmd.CommandType = CommandType.Text; cmd.Connection = con; con.Open(); exist = Convert.ToInt32(cmd.ExecuteScalar()); con.Close(); } } if (exist != 1) { string query = "INSERT INTO tblRecipients(categoryId, name, email) VALUES(@categoryId,@name,@email)"; using (SqlConnection connect = new SqlConnection(cs)) { using (SqlCommand cmd = new SqlCommand(query)) { cmd.Connection = connect; connect.Open(); cmd.Parameters.AddWithValue("@categoryId", categoryId); cmd.Parameters.AddWithValue("@name", item.name); cmd.Parameters.AddWithValue("@email", item.email); cmd.ExecuteNonQuery(); } } } } } } workbook.Close(true, null, null); Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(application); } bindDataToDropdown(); ScriptManager.RegisterStartupScript(this, this.GetType(), System.Guid.NewGuid().ToString(), "ShowMessage(' Excel File Uploaded Succesfully.','Success','excelFileUploadStatus');", true); } else { ScriptManager.RegisterStartupScript(this, this.GetType(), System.Guid.NewGuid().ToString(), "ShowMessage(' Only .xls, xlsx file is acceptable.','Error','excelFileUploadStatus');", true); } }