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