public void Export(DataTable dt)
{
String titulo = "Reporte_Clientes";
String archivo = titulo + " " + DateTime.Now.ToString("MM-dd-yyyy") + ".xlsx";
clsExcel objExcel = new clsExcel();
objExcel.ToExcelXL(dt, archivo, Page.Response);
}
/// <summary>
/// start ms excel and open supplied workbook name
/// </summary>
/// <param name="fileName"></param>
/// <returns>boolean indicating success status</returns>
private bool open_excel(string fileName)
{
bool result = false;
string msg = "";
if (File.Exists(fileName))
{
var xlFile = Path.GetFileName(fileName);
try
{
oXl = new clsExcel();
oXl.Visible = showExcel;
result = oXl.OpenExcel(xlPathName);
}
catch (Exception ex)
{
msg = $"Error opening Excel file \"{xlFile}\": {ex.Message}";
LogIt.LogError(msg);
}
}
return(result);
}
private void btnImport_Click(object sender, EventArgs e)
{
btnImport.Enabled = false;
string msg = "";
List <string> timesheetsEntered = new List <string>();
// continue if we can open excel file
xlPathName = txtExcelFile.Text;
if (open_excel(xlPathName))
{
xlFile = Path.GetFileName(xlPathName);
msg = $"Opened Excel file \"{xlFile}\"";
Status = msg;
LogIt.LogInfo(msg);
// if csv file, save-as excel file.
if (save_csv_as_excel())
{
// get and resize range
isValid = oXl.GetRange(excelRange);
if (isValid)
{
var activeRows = oXl.ActiveRows;
isValid = oXl.ResizeRange(rows: activeRows);
}
if (isValid)
{
msg = "Identified active timesheets range";
Status = msg;
LogIt.LogInfo(msg);
// add headings for last 2 columns
oXl.WorkSheet.Range("$L$1").Value = "Status";
oXl.WorkSheet.Range("$M$1").Value = "Message";
// resize range
oXl.Range.Columns.AutoFit();
// start processing the file
allValid = true;
// loop thru each invoice row on worksheet
foreach (dynamic xlRow in oXl.Range.Rows)
{
isValid = false;
string timesheetId = "";
DateTime startTime = new DateTime();
DateTime stopTime = new DateTime();
try
{
string empName = (xlRow.Cells[cols.empName].Value ?? "").ToString().Trim();
string workOrder = (xlRow.Cells[cols.workOrder].Value ?? "").ToString().Trim();
string jobNo = (xlRow.Cells[cols.jobNo].Value ?? "").ToString().Trim();
int jobId = 0;
int empId = 0;
float regHours = 0;
float otHours = 0;
float dblOtHours = 0;
// validate employee id is number
xlCell = xlRow.Cells[cols.empID];
if (int.TryParse((xlCell.Value ?? "").ToString().Trim(), out empId))
{
// validate start, stop times
bool validStart = DateTime.TryParse((xlRow.Cells[cols.startTime].Value ?? "").ToString().Trim(), out startTime);
bool validStop = DateTime.TryParse((xlRow.Cells[cols.stopTime].Value ?? "").ToString().Trim(), out stopTime);
bool sameDay = true;
if (validStart && validStop)
{
sameDay = (startTime.Date == stopTime.Date);
}
if (validStart && validStop && sameDay)
{
// validate job id is numeric and in AIMM
bool validJob = int.TryParse(jobNo, out jobId);
if (validJob)
{
validJob = valid_job(jobId, connString);
}
if (validJob)
{
// validate work order belongs to job
bool validWorkOrder = valid_work_order(jobId, workOrder, connString);
if (validWorkOrder)
{
// validate hours
// ignore total hours, it includes 1/2 hour for lunch.
bool validReg = float.TryParse((xlRow.Cells[cols.regHours].Value ?? "").ToString().Trim(), out regHours);
bool validOt = float.TryParse((xlRow.Cells[cols.otHours].Value ?? "").ToString().Trim(), out otHours);
bool validDot = float.TryParse((xlRow.Cells[cols.dblOtHours].Value ?? "").ToString().Trim(), out dblOtHours);
if (validReg && validOt && validDot)
{
// get or create weekly timesheet record for employee
timesheetId = get_timesheet(startTime, empId, connString);
if (timesheetId == "")
{
timesheetId = create_timesheet(startTime, empId, connString);
}
// add detail for employee
isValid = add_timesheet_detail(timesheetId, jobId, workOrder, startTime, regHours, otHours, dblOtHours, connString);
// save timesheet IDs so we can update them at the end
if (isValid)
{
if (!timesheetsEntered.Contains(timesheetId))
{
timesheetsEntered.Add(timesheetId);
}
}
else
{
msg = $"Could not add timesheet for {empName} for {startTime.ToShortDateString()} for job {jobNo}";
Status = msg;
LogIt.LogError(msg);
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has bad hours";
Status = msg;
LogIt.LogError(msg);
if (!validReg)
{
color_excel_cell(xlRow.Cells[cols.regHours], cellColors.errorColor);
}
if (!validOt)
{
color_excel_cell(xlRow.Cells[cols.otHours], cellColors.errorColor);
}
if (!validDot)
{
color_excel_cell(xlRow.Cells[cols.dblOtHours], cellColors.errorColor);
}
set_excel_status(xlRow, "Error", "Bad hours");
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has invalid work order number: {workOrder}";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlRow.Cells[cols.workOrder], cellColors.errorColor);
}
}
else
{
msg = $"Timesheet for {empName} has invalid job ID: {jobNo}";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlRow.Cells[cols.jobNo], cellColors.errorColor);
}
}
else
{
msg = $"Timesheet for {empName} for job {jobNo} has bad time(s) or dates don't agree";
Status = msg;
LogIt.LogError(msg);
if (!validStart)
{
color_excel_cell(xlRow.Cells[cols.startTime], cellColors.errorColor);
}
if (!validStop)
{
color_excel_cell(xlRow.Cells[cols.stopTime], cellColors.errorColor);
}
if (!sameDay)
{
color_excel_cell(xlRow.Cells[cols.startTime], cellColors.errorColor);
color_excel_cell(xlRow.Cells[cols.stopTime], cellColors.errorColor);
}
set_excel_status(xlRow, "Error", "Bad date(s) or dates don't agree");
} // valid start, stop times
}
else
{
isValid = false;
msg = $"Timesheet for {empName} for job {jobNo} has bad employee ID";
Status = msg;
LogIt.LogError(msg);
color_excel_cell(xlCell, cellColors.errorColor);
set_excel_status(xlRow, "Error", "Bad employee ID");
} // valid employee ID
}
catch (Exception ex)
{
msg = $"Error processing timesheet {timesheetId} for {startTime.ToShortDateString()}: {ex.Message}";
LogIt.LogError(msg);
Status = msg;
color_excel_cell(xlRow.Cells[cols.empName], cellColors.errorColor);
set_excel_status(xlRow, "Error", ex.Message);
}
// keep track if all items were valid
allValid = allValid && isValid;
}
// update weekly timesheet totals for each timesheet id in list
isValid = update_timesheet_totals(timesheetsEntered, connString);
if (isValid)
{
msg = "Timesheets imported and totals updated";
Status = msg;
LogIt.LogInfo(msg);
}
// save excel file if any invalid items.
isValid = oXl.CloseWorkbook(!allValid);
// move workbook to archive/errors folder
destPath = allValid ? archivePath : errorPath;
destFile = string.Concat(
Path.GetFileNameWithoutExtension(xlFile),
DateTime.Now.ToString("_yyyy-MM-dd_HH-mm-ss"),
Path.GetExtension(xlFile));
destPathName = Path.Combine(destPath, destFile);
if (move_file(xlPathName, destPathName))
{
txtExcelFile.Text = destPathName;
if (allValid)
{
msg = $"Import completed without errors. Moved \"{xlFile}\" to \"{destPathName}\"";
LogIt.LogInfo(msg);
}
else
{
msg = $"Import completed with errors. Moved \"{xlFile}\" to \"{destPathName}\"";
LogIt.LogWarn(msg);
}
Status = msg;
}
oXl.CloseExcel();
oXl = null;
}
else
{
msg = "Could not identify active timesheets range, timesheets not imported.";
Status = msg;
LogIt.LogError(msg);
} // got active timesheets
}
else
{
msg = $"Could not save {xlFile} as standard Excel workbook, timesheets not imported.";
Status = msg;
LogIt.LogError(msg);
}
}
else
{
msg = $"Could not open Excel file \"{xlPathName}\", timesheets not imported";
LogIt.LogError(msg);
Status = msg;
}
}
protected void btnImport_Click(object sender, EventArgs e)
{
byte[] xd;
xd = FileUpload1.FileBytes;
var namefile = FileUpload1.PostedFile.FileName;
clsExcel lmao = new clsExcel();
var list = lmao.mtdConvertirExcel(xd, null);
//var name = list[1][1].ToString();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Select * from Pays";
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
sda.Dispose();
con.Close();
con.Dispose();
// Import to Database
using (bdposbotEntities dc = new bdposbotEntities())
{
foreach (var ls in list)
{
if (ls[0] != "Id")
{
string ci = ls[1];
var v = dc.Pays.Where(a => a.ci.Equals(ci)).FirstOrDefault();
if (v != null)
{
//Update here
v.ci = ls[1];
v.apellidos = ls[2];
v.nombres = ls[3];
v.cuotaUno = Convert.ToDouble(ls[4]);
v.cuotaDos = Convert.ToDouble(ls[5]);
v.cuotaTres = Convert.ToDouble(ls[6]);
v.cuotaCuatro = Convert.ToDouble(ls[7]);
v.cuotaCinco = Convert.ToDouble(ls[8]);
v.cuotaSeis = Convert.ToDouble(ls[9]);
v.idCurso = ls[10];
}
else
{
//Insert
dc.Pays.Add(new Pays
{
id = Convert.ToInt32(ls[0]),
ci = ls[1],
apellidos = ls[2],
nombres = ls[3],
cuotaUno = Convert.ToDouble(ls[4]),
cuotaDos = Convert.ToDouble(ls[5]),
cuotaTres = Convert.ToDouble(ls[6]),
cuotaCuatro = Convert.ToDouble(ls[7]),
cuotaCinco = Convert.ToDouble(ls[8]),
cuotaSeis = Convert.ToDouble(ls[9]),
idCurso = ls[10]
});
}
}
}
dc.SaveChanges();
}
PopulateData();
lblMessage.Text = "Successfully data import done!";
}
