public JsonResult GetAPKParametersForReport(int APKID, string day, string month, string year)
{
int id = APKContext.GetAPKList()[APKID].ID;
APK apk = APKContext.GetAPKList()[APKID];
TempData["REPORTAPKTYPE"] = (int)apk.Type;
List <List <string> > lst = APKContext.GetListOfParameterValuesReport(id, GetAPKParametersIdForReport(apk), day, month, year);
return(Json(lst, JsonRequestBehavior.AllowGet));
}
public FileResult CreateReportXLS(string hiddenAPKIDSelector, string hiddenPIB, string hiddenDateDay, string hiddenDateMonth, string hiddenDateYear)
{
/*string file = Path.Combine(Server.MapPath("~/Files"), "report.xls");
* Workbook workbook = Workbook.Load(file);
* Worksheet worksheet = workbook.Worksheets[0];
*
* worksheet.Cells[1, 1].Value = "EHUUUU";
* worksheet.Cells[0, 1] = new Cell("1, 2");
* workbook.Save(file);
* return File(file, "application/vnd.ms-excel", "report.xls");*/
int apkNumber = 0;
Int32.TryParse(hiddenAPKIDSelector, out apkNumber);
string file = Path.Combine(Server.MapPath("~/Files"), "report" + ((int)APKContext.GetAPKList()[apkNumber].Type).ToString() + ".xlsx");
//видалимо попередній файл
/*string tmp_path = (string)TempData["tmp_file"];
* if(tmp_path != null)
* System.IO.File.Delete(tmp_path);*/
foreach (string files in System.IO.Directory.EnumerateFiles(Server.MapPath("~/Files/tmp_files")))
{
try
{
string tmp = Path.Combine(Server.MapPath("~/Files/tmp_files"), files);
System.IO.File.Delete(tmp);
}
catch (IOException ex)
{
}
}
//створимо новий файл із шаблону
string tmp_file = Path.Combine(Server.MapPath("~/Files/tmp_files"), "report" + Guid.NewGuid() + ".xlsx");
System.IO.File.Copy(file, tmp_file);
var xlApp = new Excel.Application();
try
{
xlApp.DisplayAlerts = false;
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(tmp_file);
int cnt = xlWorkBook.Sheets.Count;
Excel.Worksheet xlWorkSheet = xlWorkBook.Sheets[1];
xlWorkSheet.Cells[1][1] = hiddenDateDay + "." + hiddenDateMonth + "." + hiddenDateYear;
xlWorkSheet.Cells[17][4] = hiddenPIB;
xlWorkSheet.Cells[17][4] = hiddenPIB;
xlWorkSheet.Cells[1][9] = APKContext.GetAPKList()[apkNumber].Description;
xlWorkSheet.Cells[1][10] = "за адресою: " + APKContext.GetAPKList()[apkNumber].Name;
int id = APKContext.GetAPKList()[apkNumber].ID;
APK apk = APKContext.GetAPKList()[apkNumber];
List <List <string> > lst = APKContext.GetListOfParameterValuesReport(id, GetAPKParametersIdForReport(apk), hiddenDateDay, hiddenDateMonth, hiddenDateYear);
for (int i = 0; i < lst.Count; i++)
{
for (int j = 0; j < 24; j++)
{
xlWorkSheet.Cells[6 + j][15 + i] = "";
}
}
for (int i = 0; i < lst.Count; i++)
{
for (int j = 0; j < lst[i].Count; j++)
{
xlWorkSheet.Cells[6 + j][15 + i] = lst[i][j].ToString();
}
}
xlWorkBook.SaveAs(tmp_file, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);
xlWorkBook.Close(true);
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}
catch (Exception ex)
{
Marshal.ReleaseComObject(xlApp);
}
return(File(tmp_file, "application/vnd.ms-excel", "report.xlsx"));
}