public string loadReportFile(List <TaskReportRecordModel> data, List <TaskReportRecordModel> dataPrev) { var fileinfo = new FileInfo(strTempFile); try { using (_reportExcelPck = new ExcelPackage(fileinfo)) { ExcelWorkbook excelWorkBook = _reportExcelPck.Workbook; // generate sheet 1 ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First(); String reporttitle = excelWorksheet.Cells[4, 1].Value.ToString(); reporttitle = reporttitle.Replace("[day]", _reportTime.Day.ToString()).Replace("[month]", _reportTime.Month.ToString()).Replace("[year]", _reportTime.Year.ToString()); excelWorksheet.Cells[4, 1].Value = reporttitle; int index = 0; int catid = -1; bool newcat = false; int groupcount = 0; int startingrow = 10; foreach (TaskReportRecordModel item in data) { newcat = false; if (catid < 0 || catid != (int)item.TaskType) { catid = (int)item.TaskType; newcat = true; } if (newcat) { excelWorksheet.InsertRow(startingrow + index + groupcount, 1, startingrow - 1); excelWorksheet.Cells[startingrow + index + groupcount, 1].Value = Convert.ToChar(65 + groupcount).ToString(); excelWorksheet.Cells[startingrow + index + groupcount, 2].Value = CategoryService.GetInstance().GetCategoryName((int)item.TaskType, Contain.CatType.Category); groupcount++; } excelWorksheet.InsertRow(startingrow + 1 + index + groupcount, 1, startingrow + index + groupcount); excelWorksheet.Cells[startingrow + index + groupcount, 1].Value = index + 1; excelWorksheet.Cells[startingrow + index + groupcount, 2].Value = item.Title; excelWorksheet.Cells[startingrow + index + groupcount, 3].Value = item.AssigneeName; if (item.CreationDate.Day == _reportTime.Day && item.CreationDate.Month == _reportTime.Month && item.CreationDate.Year == _reportTime.Year) { excelWorksheet.Cells[startingrow + index + groupcount, 4].Value = "X"; } if (item.Status == 1) { excelWorksheet.Cells[startingrow + index + groupcount, 5].Value = "X"; } if (item.Status == 2) { excelWorksheet.Cells[startingrow + index + groupcount, 6].Value = "X"; } if (item.Status == 3 || item.Status == 4 || item.Status == 5) { excelWorksheet.Cells[startingrow + index + groupcount, 7].Value = "X"; } if (item.Status == 6) { excelWorksheet.Cells[startingrow + index + groupcount, 8].Value = "X"; } index++; } // generate sheet 2 excelWorksheet = excelWorkBook.Worksheets[2]; DateTime prevMonth = _reportTime.AddMonths(-1); reporttitle = excelWorksheet.Cells[4, 1].Value.ToString(); reporttitle = reporttitle.Replace("[day]", _reportTime.Day.ToString()).Replace("[month]", _reportTime.Month.ToString()).Replace("[year]", _reportTime.Year.ToString()); excelWorksheet.Cells[4, 1].Value = reporttitle; index = 0; catid = -1; newcat = false; groupcount = 0; foreach (TaskReportRecordModel item in dataPrev) { newcat = false; if (catid < 0 || catid != (int)item.TaskType) { catid = (int)item.TaskType; newcat = true; } if (newcat) { excelWorksheet.InsertRow(startingrow + index + groupcount, 1, startingrow - 1); excelWorksheet.Cells[startingrow + index + groupcount, 1].Value = Convert.ToChar(65 + groupcount).ToString(); excelWorksheet.Cells[startingrow + index + groupcount, 2].Value = CategoryService.GetInstance().GetCategoryName((int)item.TaskType, Contain.CatType.Category); groupcount++; } excelWorksheet.InsertRow(startingrow + 1 + index + groupcount, 1, startingrow + index + groupcount); excelWorksheet.Cells[startingrow + index + groupcount, 1].Value = index + 1; excelWorksheet.Cells[startingrow + index + groupcount, 2].Value = item.Title; excelWorksheet.Cells[startingrow + index + groupcount, 3].Value = item.AssigneeName; excelWorksheet.Cells[startingrow + index + groupcount, 4].Value = item.LastUpdateDate.ToString("dd-MM-yyyy"); if (item.Status == 2) { excelWorksheet.Cells[startingrow + index + groupcount, 5].Value = "X"; } if (item.Status == 3 || item.Status == 4 || item.Status == 5) { excelWorksheet.Cells[startingrow + index + groupcount, 6].Value = "X"; } if (item.Status == 6) { excelWorksheet.Cells[startingrow + index + groupcount, 7].Value = "X"; } index++; } _reportExcelPck.Save(); _reportExcelPck.Dispose(); return(strTempFile); } } catch { } return(String.Empty); }