public IActionResult EditDeleteAllDataLab(InnerModelToPost TableViewModel)
{
if (ModelState.IsValid)
{
EditDeleteAllDataLab(TableViewModel.DateStart, TableViewModel.DateFinish, TableViewModel.NumberOfRecords);
return(View());
}
return(View(ModelState.ErrorCount));
}
public async Task <IActionResult> ImportExcelFileLab(InnerModelToPost TableViewModel)
{
if (ModelState.IsValid)
{
await ImportExcelFileLab(TableViewModel.DateStart, TableViewModel.DateFinish);
return(View());
}
return(View(ModelState.ErrorCount));
}
// Конец
//_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
public IActionResult DownloadExcel(string dateStart, string dateFinish)
{
DataTable tdbl = new DataTable();
try
{
InnerModelToPost TableViewModel = new InnerModelToPost();
using (SqlConnection sqlConnection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection2")))
{
sqlConnection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter("_Excel_GetDataLab", sqlConnection);
sqlDA.SelectCommand.Parameters.AddWithValue("in_DateStart", Convert.ToDateTime(dateStart));
sqlDA.SelectCommand.Parameters.AddWithValue("in_DateFinish", Convert.ToDateTime(dateFinish));
sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDA.Fill(tdbl);
}
}
catch (SqlException ex)
{
throw ex;
}
var stream = new MemoryStream();
using (ExcelPackage package = new ExcelPackage(stream))
{
var worksheet = package.Workbook.Worksheets.Add("Listy1");
worksheet.Cells.LoadFromDataTable(tdbl, true);
worksheet.Column(11).Style.Numberformat.Format = "dd:mm:yyyy hh:mm:ss";
worksheet.Column(14).Style.Numberformat.Format = "dd:mm:yyyy hh:mm:ss";
worksheet.Cells.AutoFitColumns();
worksheet.Cells[1, 1, 1, 16].AutoFilter = true;
using (ExcelRange heading = worksheet.Cells[1, 1, 1, 16])
{
var colory = heading.Style.Fill;
colory.PatternType = ExcelFillStyle.Solid;
colory.BackgroundColor.SetColor(Color.LightBlue);
heading.Style.Border.BorderAround(ExcelBorderStyle.Thick);
}
package.Save();
}
stream.Position = 0;
string excelname = $"VestPlast.DataLab.{DateTime.Now}.xlsx";
return(File(stream, "application/vnd.openformats-officedocument.spreadsheetml.sheet", excelname));
}
public async Task <IActionResult> ImportExcelFileLab(DateTime dateStart, DateTime dateFinish)
{
try
{
InnerModelToPost TableViewModel = new InnerModelToPost();
if (dateStart == DateTime.MinValue || dateFinish == DateTime.MinValue)
{
dateStart = DateTime.Now.AddMonths(-1);
dateFinish = DateTime.Now.AddDays(1);
}
int count = 0;
using (SqlConnection sqlConnection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection2")))
{
sqlConnection.Open();
SqlCommand cmd = new SqlCommand("_Excel_GetNumberOfRecords", sqlConnection)
{
CommandType = CommandType.StoredProcedure
};
cmd.Parameters.AddWithValue("in_DateStart", dateStart);
cmd.Parameters.AddWithValue("in_DateFinish", dateFinish);
cmd.Parameters.AddWithValue("in_SubsType", 2);
SqlParameter parameter = cmd.Parameters.Add("out_Count", SqlDbType.Int);
parameter.Direction = ParameterDirection.ReturnValue;
await cmd.ExecuteNonQueryAsync();
count = (int)cmd.Parameters["out_Count"].Value;
}
TableViewModel.Count = count;
TableViewModel.DateStart = dateStart;
TableViewModel.DateFinish = dateFinish;
return(View(TableViewModel));
}
catch (SqlException ex)
{
throw ex;
}
}
public IActionResult EditDeleteAllDataLab(DateTime dateStart, DateTime dateFinish, int?numberOfRecords)
{
try
{
InnerModelToPost TableViewModel = new InnerModelToPost();
if (dateStart == DateTime.MinValue || dateFinish == DateTime.MinValue || numberOfRecords == null)
{
dateStart = DateTime.Now.AddMonths(-1);
dateFinish = DateTime.Now.AddDays(1);
numberOfRecords = 200;
}
DataTable tdbl = new DataTable();
using (SqlConnection sqlConnection = new SqlConnection(_configuration.GetConnectionString("DefaultConnection2")))
{
sqlConnection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter("BasApp_MultiController_ShowDataForEditOrDelete", sqlConnection);
sqlDA.SelectCommand.Parameters.AddWithValue("in_Id", numberOfRecords);
sqlDA.SelectCommand.Parameters.AddWithValue("in_DateStart", dateStart);
sqlDA.SelectCommand.Parameters.AddWithValue("in_DateFinish", dateFinish);
sqlDA.SelectCommand.Parameters.AddWithValue("in_SubsType", 2);
sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDA.Fill(tdbl);
}
TableViewModel.DateStart = dateStart;
TableViewModel.DateFinish = dateFinish;
TableViewModel.NumberOfRecords = (int)numberOfRecords;
TableViewModel.listil = tdbl;
return(View(TableViewModel));
}
catch (SqlException ex)
{
throw ex;
}
}
