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();
}
