var list = _DarpanMISServices.GetServiceDetailsByServiceIdCount(ServiceName, ActionTypeId, ServiceId, DepartmentId);
//if (list != null)
//{
// Excel.Application excelApp = new Excel.Application();
// Excel.Workbook workbook = excelApp.Workbooks.Add();
// Excel.Worksheet worksheet = workbook.Worksheets[1];
// // Header
// string currentDate = System.DateTime.Now.ToString("dd/MM/yyyy");
// // Merge cells for the title
// Excel.Range titleRange = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, 14]];
// titleRange.Merge();
// titleRange.Value = "NEW OKHLA INDUSTRIAL DEVELOPMENT AUTHORITY(NOIDA)";
// titleRange.Font.Size = 16;
// titleRange.Font.Bold = true;
// titleRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// //Merge cells for athority
// Excel.Range infoRange = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[2, 14]];
// infoRange.Merge();
// infoRange.Value= $"Report As On:- {currentDate}";
// infoRange.Font.Size = 11;
// infoRange.Font.Bold = true;
// infoRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// // Set the date in a separate cell
// Excel.Range blanckrow = worksheet.Range[worksheet.Cells[3, 1], worksheet.Cells[3, 14]];
// blanckrow.Merge();
// // Populate header data
// worksheet.Cells[4, 1].Resize(1, 14).Value = new object[] { "Sr.No.", "Ref No", "RegistrationId", "Department", "Property No", "Applicant Name", "Mobile No.", "Service Name", "Request Date", "Forward By", "Forward To", "Forward Date", "Status", "Approval Date" };
// // Make the header row bold
// Excel.Range headerRow = worksheet.Range[worksheet.Cells[4, 1],
// worksheet.Cells[4, 14]]; //header row bold 7 cell tak
// headerRow.Font.Bold = true;
// headerRow.Interior.Color = Excel.XlRgbColor.rgbLightGray;
// headerRow.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
// // Populate data
// int count = 0;
// foreach (var item in list)
// {
// worksheet.Cells[count + 5, 1] = count + 1;
// worksheet.Cells[count + 5, 2] = item.RequestId;
// worksheet.Cells[count + 5, 3] = item.RegistrationNo;
// worksheet.Cells[count + 5, 4] = item.Department;
// worksheet.Cells[count + 5, 5] = item.PropertyNo;
// worksheet.Cells[count + 5, 6] = item.Applicant;
// worksheet.Cells[count + 5, 7] = item.MobileNumber;
// worksheet.Cells[count + 5, 8] = item.ServiceName;
// worksheet.Cells[count + 5, 9] = item.RequestDate;
// worksheet.Cells[count + 5, 10] = item.ForwardBy;
// worksheet.Cells[count + 5, 11] = item.ForwardTo;
// worksheet.Cells[count + 5, 12] = item.ForwardDate;
// worksheet.Cells[count + 5, 13] = item.Status;
// worksheet.Cells[count + 5, 14] = item.ApprovalDate;
// Excel.Range rowRange1 = worksheet.Range[worksheet.Cells[count + 5,
// 1], worksheet.Cells[count + 5, 14]];
// rowRange1.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
// count++;
// }
// string workbookPassword = GenerateRandomPassword();
// workbook.Password = workbookPassword;
// // Save the file
// string fileName =
// $"Departmentwise_serviceData_{DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx";
// string path = Path.Combine(Server.MapPath("/Content/Images/NAFullMap"), fileName);
// workbook.SaveAs(path);
// // Close and release resources
// workbook.Close();
// excelApp.Quit();
// System.Runtime.InteropServices.Marshal.ReleaseComObject(titleRange);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(infoRange);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(headerRow);
// //System.Runtime.InteropServices.Marshal.ReleaseComObject(totalRange);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
// System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
// sendmail("Department wise service details", workbookPassword, path);
// removeExcelFile(path);
// return Json("success", JsonRequestBehavior.AllowGet);
//}
//else
//{
// return Json("error", JsonRequestBehavior.AllowGet);
//}
if (list != null && list.Any())
{
using (var package = new ExcelPackage())
{
var worksheet = package.Workbook.Worksheets.Add("Department Wise Service Details");
// Header
string currentDate = DateTime.Now.ToString("dd/MM/yyyy");
// Merge cells for the title
ExcelRange titleRange = worksheet.Cells["A1:N1"];
titleRange.Merge = true;
titleRange.Value = "NEW OKHLA INDUSTRIAL DEVELOPMENT AUTHORITY(NOIDA)";
titleRange.Style.Font.Size = 16;
titleRange.Style.Font.Bold = true;
titleRange.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//Merge cells for authority
// Set the date in a separate cell
ExcelRange dateRange1 = worksheet.Cells["A2:N2"];
dateRange1.Merge = true;
dateRange1.Value = $"Report As On: {currentDate}";
dateRange1.Style.Font.Bold = true;
dateRange1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
// worksheet.Cells[2, 7].Value = $"Report As On: {currentDate}";
ExcelRange Mergerow = worksheet.Cells["A3:N3"];
Mergerow.Merge = true;
// Populate data
var headers = new List<string> { "Sr.No.", "Ref No", "RegistrationId", "Department", "Property No", "Applicant Name", "Mobile No.", "Service Name", "Request Date", "Forward By", "Forward To", "Forward Date", "Status", "Approval Date" };
for (int i = 0; i < headers.Count; i++)
{
worksheet.Cells[4, i + 1].Value = headers[i];
}
for (int i = 0; i < list.Count; i++)
{
worksheet.Cells[i + 5, 1].Value = i + 1;
worksheet.Cells[i + 5, 2].Value = list[i].RequestId;
worksheet.Cells[i + 5, 3].Value = list[i].RegistrationNo;
worksheet.Cells[i + 5, 4].Value = list[i].Department;
worksheet.Cells[i + 5, 5].Value = list[i].PropertyNo;
worksheet.Cells[i + 5, 6].Value = list[i].Applicant;
worksheet.Cells[i + 5, 7].Value = list[i].MobileNumber;
worksheet.Cells[i + 5, 8].Value = list[i].ServiceName;
worksheet.Cells[i + 5, 9].Value = list[i].RequestDate;
worksheet.Cells[i + 5, 10].Value = list[i].ForwardBy;
worksheet.Cells[i + 5, 11].Value = list[i].ForwardTo;
worksheet.Cells[i + 5, 12].Value = list[i].ForwardDate;
worksheet.Cells[i + 5, 13].Value = list[i].Status;
worksheet.Cells[i + 5, 14].Value = list[i].ApprovalDate;
}
// Set password for the workbook
string workbookPassword = GenerateRandomPassword();
package.Encryption.Password = workbookPassword;
// Save the file
string fileName = $"Department Wise Service Data_{ DateTime.Now.ToString("yyyyMMdd_HHmmss")}.xlsx";
string path = Path.Combine(Server.MapPath("/Content/Images/NAFullMap"), fileName);
FileInfo excelFile = new FileInfo(path);
package.SaveAs(excelFile);
sendmail("Department Wise Service Details", workbookPassword, path);
removeExcelFile(path);
return Json("success", JsonRequestBehavior.AllowGet);
}
}
else
{
return Json("error", JsonRequestBehavior.AllowGet);
}
}