protected void ibtnExportToExcel_Click(object sender, ImageClickEventArgs e) { Array.ForEach(Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory + "Uploads/Temp"), File.Delete); //Converting PSC Gridview Html Data to string var DataPSc = new StringBuilder(); dvPSCinspections.RenderControl(new HtmlTextWriter(new StringWriter(DataPSc))); string sDataPSc = DataPSc.ToString(); //Converting Average Deficiency Html Data to string var DataAverageDeficiency = new StringBuilder(); dvGridAvgDeficiency.RenderControl(new HtmlTextWriter(new StringWriter(DataAverageDeficiency))); string sDataAverageDeficiency = DataAverageDeficiency.ToString(); //Creating SpreadSheetLight Object //Used to create a new excel document SLDocument sl = new SLDocument(); //PSC Defects NCR string PSC = hdfPSC.Value; byte[] bPSC = ConvertChartPathToImage(PSC); SLPicture pic = new SLPicture(bPSC, DocumentFormat.OpenXml.Packaging.ImagePartType.Png); sl.MergeWorksheetCells("A1", "Q1"); SLStyle style = sl.CreateStyle(); sl.SetColumnWidth(12, 40); sl.SetColumnWidth(13, 10); sl.SetColumnWidth(14, 30); sl.SetColumnWidth(15, 15); sl.SetColumnWidth(16, 16); sl.SetColumnWidth(17, 10); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(1, 1, style); pic.SetPosition(2, 2); sl.InsertPicture(pic); sl.SetCellValue("A1", "PSC Inspections Per Ship"); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(3, 14, style); sl.SetCellStyle(3, 15, style); sl.SetCellStyle(3, 16, style); sl.SetCellStyle(3, 17, style); DataTable dtPSC = (DataTable)ViewState["dtPSC"]; dtPSC.Columns.Remove("VESSELID"); dtPSC.Columns[0].ColumnName = "VESSEL/FLEET"; dtPSC.Columns[2].ColumnName = "DEFICIENCIES"; dtPSC.Columns[3].ColumnName = "NCR"; // Declare an object variable. object sumObjectPSC; sumObjectPSC = dtPSC.Compute("Sum(PSC)", ""); object sumObjectDefects; sumObjectDefects = dtPSC.Compute("Sum(DEFICIENCIES)", ""); object sumObjectNCR; sumObjectNCR = dtPSC.Compute("Sum(NCR)", ""); dtPSC.Rows.Add("Grand Total", sumObjectPSC.ToString(), sumObjectDefects.ToString(), sumObjectNCR.ToString()); int iStartRowIndex = 3; int iStartColumnIndex = 14; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtPSC, true); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(3 + dtPSC.Rows.Count, 14, style); sl.SetCellStyle(3 + dtPSC.Rows.Count, 15, style); sl.SetCellStyle(3 + dtPSC.Rows.Count, 16, style); sl.SetCellStyle(3 + dtPSC.Rows.Count, 17, style); int PSCCellCount = 4; for (int i = 0; i < dtPSC.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 15, style); sl.SetCellStyle(PSCCellCount, 16, style); sl.SetCellStyle(PSCCellCount, 17, style); sl.SetCellStyle(PSCCellCount, 18, style); PSCCellCount++; } //PSC Defects NCR //Average Defects int CellCount = 3; if (dtPSC.Rows.Count > 20) { CellCount += dtPSC.Rows.Count + 2; } else { CellCount += 22; } style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(CellCount, 1, style); sl.MergeWorksheetCells("A" + CellCount.ToString(), "Q" + CellCount.ToString()); sl.SetCellValue("A" + CellCount.ToString(), "Average Deficiency Per Ship"); DataTable dtAverageDefects = (DataTable)ViewState["dtAverageDefects"]; iStartRowIndex = CellCount; iStartColumnIndex = 12; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtAverageDefects, true); CellCount = CellCount + 2; style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount, 12, style); sl.SetCellStyle(CellCount, 13, style); CellCount = CellCount + 1; sl.SetCellStyle(CellCount, 12, style); sl.SetCellStyle(CellCount, 13, style); CellCount = CellCount + 1; sl.SetCellStyle(CellCount, 12, style); sl.SetCellStyle(CellCount, 13, style); PSCCellCount = CellCount - 3; for (int i = 0; i < dtAverageDefects.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 13, style); PSCCellCount++; } //Average Defects //NCR Count string NCR = hdfNCR.Value; byte[] bNCR = ConvertChartPathToImage(NCR); pic = new SLPicture(bNCR, DocumentFormat.OpenXml.Packaging.ImagePartType.Png); CellCount = CellCount + 3; sl.MergeWorksheetCells("A" + CellCount.ToString(), "Q" + CellCount.ToString()); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(CellCount, 1, style); sl.SetCellValue("A" + CellCount.ToString(), "Total NCRs Raised In The Fleet, Per Vessel"); CellCount = CellCount + 2; pic.SetPosition(CellCount, 2); sl.InsertPicture(pic); DataTable dtNCR = (DataTable)ViewState["dtNCR"]; dtNCR.Columns[0].ColumnName = "VESSEL/FLEET"; dtNCR.Columns[1].ColumnName = "NCR"; // Declare an object variable. object sumObjectNCR2; sumObjectNCR2 = dtNCR.Compute("Sum(NCR)", ""); dtNCR.Rows.Add("Grand Total", sumObjectNCR2.ToString()); CellCount = CellCount + 1; style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount, 14, style); sl.SetCellStyle(CellCount, 15, style); iStartRowIndex = CellCount; iStartColumnIndex = 14; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtNCR, true); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 14, style); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 15, style); PSCCellCount = CellCount + 1; for (int i = 0; i < dtNCR.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 15, style); PSCCellCount++; } //NCR Count //Near Miss if (dtNCR.Rows.Count > (54 - CellCount)) { CellCount += dtNCR.Rows.Count + 2; } else { CellCount += 22; } string NearMiss = hdfNearMiss.Value; byte[] bNearMiss = ConvertChartPathToImage(NearMiss); pic = new SLPicture(bNearMiss, DocumentFormat.OpenXml.Packaging.ImagePartType.Png); CellCount = CellCount + 3; sl.MergeWorksheetCells("A" + CellCount.ToString(), "Q" + CellCount.ToString()); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(CellCount, 1, style); sl.SetCellValue("A" + CellCount.ToString(), "Total Near Miss Raised In The Fleet, Per Vessel"); CellCount = CellCount + 2; pic.SetPosition(CellCount, 0); sl.InsertPicture(pic); DataTable dtNearMiss = (DataTable)ViewState["dtNearMiss"]; object sumObjectNearMiss; sumObjectNearMiss = dtNearMiss.Compute("Sum(NEARMISS)", ""); dtNearMiss.Columns[0].ColumnName = "VESSEL/FLEET"; dtNearMiss.Columns[1].ColumnName = "Near-Miss"; // Declare an object variable. dtNearMiss.Rows.Add("Grand Total", sumObjectNearMiss.ToString()); CellCount = CellCount + 1; style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount, 14, style); sl.SetCellStyle(CellCount, 15, style); iStartRowIndex = CellCount; iStartColumnIndex = 14; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtNearMiss, true); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 14, style); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 15, style); PSCCellCount = CellCount + 1; for (int i = 0; i < dtNearMiss.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 15, style); PSCCellCount++; } //Near Miss //Injury if (dtNearMiss.Rows.Count > (77 - CellCount)) { CellCount += dtNearMiss.Rows.Count + 2; } else { CellCount += 15; } string Injury = hdfInjury.Value; byte[] bInjury = ConvertChartPathToImage(Injury); pic = new SLPicture(bInjury, DocumentFormat.OpenXml.Packaging.ImagePartType.Png); CellCount = CellCount + 3; sl.MergeWorksheetCells("A" + CellCount.ToString(), "Q" + CellCount.ToString()); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(CellCount, 1, style); sl.SetCellValue("A" + CellCount.ToString(), "Total Injuries In The Fleet"); CellCount = CellCount + 2; pic.SetPosition(CellCount, 0); sl.InsertPicture(pic); DataTable dtInjury = (DataTable)ViewState["dtInjury"]; object sumObjectInjury; sumObjectInjury = dtInjury.Compute("Sum(INJURIES)", ""); dtInjury.Columns[0].ColumnName = "VESSEL/FLEET"; dtInjury.Columns[1].ColumnName = "Sum Of Injury"; // Declare an object variable. dtInjury.Rows.Add("Grand Total", sumObjectInjury.ToString()); CellCount = CellCount + 1; style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount, 14, style); sl.SetCellStyle(CellCount, 15, style); iStartRowIndex = CellCount; iStartColumnIndex = 14; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtInjury, true); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 14, style); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 15, style); PSCCellCount = CellCount + 1; for (int i = 0; i < dtInjury.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 15, style); PSCCellCount++; } //Injury //Incident Accident if (dtInjury.Rows.Count > (102 - CellCount)) { CellCount += dtInjury.Rows.Count + 2; } else { CellCount += 15; } string IncidentAccident = hdfIncidentAccident.Value; byte[] bIncidentAccident = ConvertChartPathToImage(IncidentAccident); pic = new SLPicture(bIncidentAccident, DocumentFormat.OpenXml.Packaging.ImagePartType.Png); CellCount = CellCount + 3; sl.MergeWorksheetCells("A" + CellCount.ToString(), "Q" + CellCount.ToString()); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(CellCount, 1, style); sl.SetCellValue("A" + CellCount.ToString(), "Total Incidents/Accidents In The Fleet"); CellCount = CellCount + 2; pic.SetPosition(CellCount, 0); sl.InsertPicture(pic); DataTable dtIncidentAccident = (DataTable)ViewState["dtIncidentAccident"]; object sumObjectIncidentAccident; sumObjectIncidentAccident = dtIncidentAccident.Compute("Sum(PROPERTYPOLLUTION)", ""); dtIncidentAccident.Columns[0].ColumnName = "VESSEL/FLEET"; dtIncidentAccident.Columns[1].ColumnName = "Sum Of Count"; // Declare an object variable. dtIncidentAccident.Rows.Add("Grand Total", sumObjectIncidentAccident.ToString()); CellCount = CellCount + 1; style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount, 14, style); sl.SetCellStyle(CellCount, 15, style); iStartRowIndex = CellCount; iStartColumnIndex = 14; sl.ImportDataTable(iStartRowIndex, iStartColumnIndex, dtIncidentAccident, true); style = sl.CreateStyle(); style.SetPatternFill(PatternValues.Solid, System.Drawing.Color.LightGray, System.Drawing.Color.LightGray); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 14, style); sl.SetCellStyle(CellCount + dtPSC.Rows.Count, 15, style); PSCCellCount = CellCount + 1; for (int i = 0; i < dtIncidentAccident.Rows.Count; i++) { style = sl.CreateStyle(); style.Alignment.Horizontal = HorizontalAlignmentValues.Center; sl.SetCellStyle(PSCCellCount, 15, style); PSCCellCount++; } //Incident Accident string fileNamewithpath = ""; string SaveExcelFileName = ""; string folder = AppDomain.CurrentDomain.BaseDirectory + "Uploads/Temp"; string[] BaseDirectory = AppDomain.CurrentDomain.BaseDirectory.Split('\\'); string domainname = BaseDirectory[BaseDirectory.Length - 2]; if (!Directory.Exists(folder)) { Directory.CreateDirectory(folder); } SaveExcelFileName = "TrendAnalysis" + DateTime.Now.Day + DateTime.Now.Month + DateTime.Now.Second + DateTime.Now.Millisecond + ".xlsx"; fileNamewithpath = folder + "/" + SaveExcelFileName; sl.SaveAs(fileNamewithpath); BindPSCGrid(); Response.ContentType = "Application/vnd.ms-excel"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + SaveExcelFileName); Response.TransmitFile(Server.MapPath("../../Uploads/Temp/" + SaveExcelFileName)); Response.End(); }