private void OpenImageToXLSReporteGrafico(List <EstadoCitaCortoDTO> EstadosCitas, byte[] Contenido, string Excel)
{
using (var sl = new SLDocument())
{
var pic = new SLPicture(Contenido, ImagePartType.Png);
pic.SetPosition(3, 2);
sl.InsertPicture(pic);
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Grafica UADyCS");
sl.AddWorksheet("Tabla Reporte");
sl.SelectWorksheet("Tabla Reporte");
int filaInicio = 6;
int numeroColumnaInicio = 4;
int numeroColumnaCuerpo = numeroColumnaInicio;
int numeroFila = filaInicio;
sl.SetCellValue(numeroFila - 2, numeroColumnaInicio, "Reporte UADyCS");
sl.SetCellValue(numeroFila, numeroColumnaInicio, "UADyCS");
EstadosCitas.ForEach(fila =>
{
numeroColumnaCuerpo = numeroColumnaInicio + 1;
if (numeroFila == filaInicio)
{
fila.Valores.ForEach(estado => {//Descripcion del estado=> solicitado,atendido,..
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item1); numeroColumnaCuerpo++;
});
}
numeroFila++;
//Nombre de la UADyCS
sl.SetCellValue(numeroFila, numeroColumnaInicio, fila.Descripcion);
numeroColumnaCuerpo = numeroColumnaInicio;
fila.Valores.ForEach(estado => {
numeroColumnaCuerpo++;
//Cantidad del estado
sl.SetCellValue(numeroFila, numeroColumnaCuerpo, estado.Item2);
});
});
SLTable tbl = sl.CreateTable(filaInicio, numeroColumnaInicio, numeroFila, numeroColumnaCuerpo);
tbl.SetTableStyle(SLTableStyleTypeValues.Medium9);
sl.InsertTable(tbl);
sl.AutoFitColumn(numeroColumnaInicio, numeroColumnaCuerpo);
sl.SaveAs(Excel);
sl.Dispose();
}
}
private void OpenImageToXLS(byte[] Contenido, string Excel)
{
using (var sl = new SLDocument())
{
var pic = new SLPicture(Contenido, ImagePartType.Png);
pic.SetPosition(3, 2);
sl.InsertPicture(pic);
sl.SaveAs(Excel);
sl.Dispose();
}
}
public void WriteReclamationReport(string filename)
{
if (App.MainReportInterface.SheetName == null)
{
App.pIzvjestaji.b_generateReport2.Foreground = new SolidColorBrush(System.Windows.Media.Colors.Red);
return;
}
int test_report_no = -1;
//using (SLDocument sl = new SLDocument(filename, "SRH"))
using (SLDocument sl = new SLDocument(filename))
{
// Crveni
SLStyle style1 = sl.CreateStyle();
style1.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Red, System.Drawing.Color.Red);
// Zeleni
SLStyle style2 = sl.CreateStyle();
style2.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.Green);
// ******************* Sheet za SRH *******************
sl.SelectWorksheet("SRH");
// Kontrolni list broj
try
{
String readText = File.ReadAllText("Kontrolni_list_br.txt");
if (int.TryParse(readText, out test_report_no))
{
test_report_no++;
File.WriteAllText("Kontrolni_list_br.txt", test_report_no.ToString());
}
}
catch (FileNotFoundException)
{
// Stvori novi file
File.WriteAllText("Kontrolni_list_br.txt", "1");
test_report_no = 1;
}
sl.SetCellValue(3, 5, test_report_no);
// Zaglavlje
sl.SetCellValue(7, 4, App.pPostavke.tbProizvodac.Text);
sl.SetCellValue(8, 4, App.pPostavke.tbTvornickiBroj.Text);
sl.SetCellValue(6, 9, App.pPostavke.tbVrstaLima.Text);
sl.SetCellValue(7, 9, App.pPostavke.tbSirina.Text);
sl.SetCellValue(8, 9, App.pPostavke.tbBrojKoluta.Text);
// Slika lima
SLPicture pic = new SLPicture("Slike/srh_slika_lima.png");
pic.ResizeInPercentage(80, 80);
//pic.ResizeInPixels();
//pic.SetPosition(11, 1);
pic.SetPosition(62, 370);
sl.InsertPicture(pic);
// Slika grafa srha
pic = new SLPicture("Slike/srh_graf.png");
pic.ResizeInPixels(343, 260);
pic.SetPosition(9, 8);
sl.InsertPicture(pic);
// Max. srh
sl.SetCellValue(25, 6, ((float)Srh_max));
int diff = Srh_max - 15;
if (diff < 0)
{
diff = 0;
}
sl.SetCellValue(25, 12, ((float)diff));
if (diff > 0)
{
sl.SetCellStyle(25, 12, style1);
}
else
{
sl.SetCellStyle(25, 12, style2);
}
// Srh postotak
sl.SetCellValue(26, 6, Srh_postotak);
if (Srh_postotak > 30)
{
sl.SetCellStyle(26, 6, style1);
}
else
{
sl.SetCellStyle(26, 6, style2);
}
// Datum
String dat = String.Format("{0:D}.{1:D}.{2:D4}.", DateAndTime.Now.Day, DateAndTime.Now.Month, DateAndTime.Now.Year);
sl.SetCellValue(31, 2, dat);
// Operater
sl.SetCellValue(31, 5, KorisnickoIme);
// **************** Sheet za VALOVITOST ***************
sl.SelectWorksheet("VALOVITOST");
// Kontrolni list broj
sl.SetCellValue(3, 5, test_report_no);
// Zaglavlje
sl.SetCellValue(7, 4, App.pPostavke.tbProizvodac.Text);
sl.SetCellValue(8, 4, App.pPostavke.tbTvornickiBroj.Text);
sl.SetCellValue(6, 9, App.pPostavke.tbVrstaLima.Text);
sl.SetCellValue(7, 9, App.pPostavke.tbSirina.Text);
sl.SetCellValue(8, 9, App.pPostavke.tbBrojKoluta.Text);
// Slika lima za valovitost
pic = new SLPicture("Slike/valovitost_slika_lima.png");
pic.ResizeInPercentage(80, 80);
//pic.ResizeInPixels();
//pic.SetPosition(11, 1);
pic.SetPosition(62, 370);
sl.InsertPicture(pic);
// Slika grafa valovitosti
pic = new SLPicture("Slike/valovitost_graf.png");
pic.ResizeInPixels(343, 260);
pic.SetPosition(9, 8);
sl.InsertPicture(pic);
// Dužina vala
sl.SetCellValue(25, 6, Valovitost_duzinaVala);
// Visina vala
sl.SetCellValue(26, 6, Valovitost_visinaVala);
// Faktor
sl.SetCellValue(27, 6, Valovitost_faktor);
float delta = Valovitost_faktor - 1.5f;
if (delta < 0)
{
delta = 0;
}
sl.SetCellValue(27, 12, delta);
if (delta > 0)
{
sl.SetCellStyle(27, 12, style1);
}
else
{
sl.SetCellStyle(27, 12, style2);
}
// Broj valova na 1 m
sl.SetCellValue(28, 6, Valovitost_brojValova / (Valovitost_duzinaVala / 1000.0f));
// Datum
sl.SetCellValue(33, 2, dat);
// Operater
sl.SetCellValue(33, 5, KorisnickoIme);
// ******************* Sheet za SABLJU *******************
sl.SelectWorksheet("SABLJA");
// Kontrolni list broj
sl.SetCellValue(3, 5, test_report_no);
// Zaglavlje
sl.SetCellValue(7, 4, App.pPostavke.tbProizvodac.Text);
sl.SetCellValue(8, 4, App.pPostavke.tbTvornickiBroj.Text);
sl.SetCellValue(6, 9, App.pPostavke.tbVrstaLima.Text);
sl.SetCellValue(7, 9, App.pPostavke.tbSirina.Text);
sl.SetCellValue(8, 9, App.pPostavke.tbBrojKoluta.Text);
// Slika lima
pic = new SLPicture("Slike/sablja_slika_lima.png");
pic.ResizeInPercentage(80, 80);
//pic.ResizeInPixels();
//pic.SetPosition(11, 1);
pic.SetPosition(62, 370);
sl.InsertPicture(pic);
// Slika grafa sablje
pic = new SLPicture("Slike/sablja_graf.png");
pic.ResizeInPixels(343, 260);
pic.SetPosition(9, 8);
sl.InsertPicture(pic);
// Duljina sablje
sl.SetCellValue(25, 6, Sablja_duljina);
// Visina sablje
sl.SetCellValue(26, 6, Sablja_visina);
// Sablja postotak
sl.SetCellValue(27, 6, Sablja_posto);
delta = Sablja_posto - 0.033f;
if (delta < 0)
{
delta = 0;
}
sl.SetCellValue(27, 12, delta);
if (delta > 0)
{
sl.SetCellStyle(27, 12, style1);
}
else
{
sl.SetCellStyle(27, 12, style2);
}
// Datum
sl.SetCellValue(32, 2, dat);
// Operater
sl.SetCellValue(32, 5, KorisnickoIme);
// ******************* Sheet za ŠIRINA; DEBLJINA *******************
sl.SelectWorksheet("ŠIRINA; DEBLJINA");
// Kontrolni list broj
sl.SetCellValue(3, 5, test_report_no);
// Zaglavlje
sl.SetCellValue(7, 4, App.pPostavke.tbProizvodac.Text);
sl.SetCellValue(8, 4, App.pPostavke.tbTvornickiBroj.Text);
sl.SetCellValue(6, 9, App.pPostavke.tbVrstaLima.Text);
sl.SetCellValue(7, 9, App.pPostavke.tbSirina.Text);
sl.SetCellValue(8, 9, App.pPostavke.tbBrojKoluta.Text);
// Širina lima (mjerenje dimenzija na test uzorku)
if (SheetName == "TU1")
{
float calculatedDelta = Dimensions[0].Mjereno - Dimensions[0].Nazivno;
sl.SetCellValue(25, 3, Dimensions[0].Nazivno);
sl.SetCellValue(25, 6, Dimensions[0].Mjereno);
sl.SetCellValue(25, 9, Dimensions[0].DeltaPlus);
sl.SetCellValue(25, 11, Dimensions[0].DeltaMinus);
sl.SetCellValue(25, 12, calculatedDelta);
if (calculatedDelta > Dimensions[0].DeltaPlus || calculatedDelta < Dimensions[0].DeltaMinus)
{
sl.SetCellStyle(25, 12, style1);
}
else
{
sl.SetCellStyle(25, 12, style2);
}
}
// Debljina lima (ručno mjerenje laserom ili ticalom)
sl.SetCellValue(26, 6, ManualThicknessMeas);
const float tolerancija = 0.03f;
sl.SetCellValue(26, 9, tolerancija); // DeltaPlus
sl.SetCellValue(26, 11, tolerancija); // DeltaMinus
if (Single.TryParse(App.pPostavke.tbNazivnaDebljina.Text.Replace(".", ","), out float nazivnaDebljina))
{
sl.SetCellValue(26, 3, nazivnaDebljina);
delta = ManualThicknessMeas - nazivnaDebljina;
sl.SetCellValue(26, 12, delta);
if (delta > tolerancija || delta < -tolerancija)
{
sl.SetCellStyle(26, 12, style1);
}
else
{
sl.SetCellStyle(26, 12, style2);
}
}
// Datum
sl.SetCellValue(31, 2, dat);
// Operater
sl.SetCellValue(31, 5, KorisnickoIme);
// ******************* Ime Excel datoteke *******************
String ime_lima = App.MainReportInterface.SheetName.Replace("\n", " ");
String datum = String.Format("{0:D4}-{1:D2}-{2:D2}", DateAndTime.Now.Year, DateAndTime.Now.Month, DateAndTime.Now.Day);
String vrijeme = String.Format("{0:D2}-{1:D2}-{2:D2}", DateAndTime.Now.Hour, DateAndTime.Now.Minute, DateAndTime.Now.Second);
Directory.CreateDirectory("Izvjestaji/Reklamacijski"); // Stvara novi folder samo ako već ne postoji
sl.SaveAs("Izvjestaji/Reklamacijski/" + test_report_no.ToString() + "_" + ime_lima + "_" + datum + "-" + vrijeme + ".xlsx"); // ili xls?
// Success
App.pIzvjestaji.b_generateReport2.Foreground = new SolidColorBrush(System.Windows.Media.Colors.Green);
}//using
}
public void WriteControlSheetReport(string filename, string sheet, int rows)
{
if (App.pDimenzije.odabirLimova.SheetName == null)
{
App.pIzvjestaji.b_generateReport1.Foreground = new SolidColorBrush(System.Windows.Media.Colors.Red);
return;
}
using (SLDocument sl = new SLDocument(filename, sheet))
{
//SLWorksheetStatistics stats = sl.GetWorksheetStatistics();
int iStartColumnIndex = CollumnOffset;
SLStyle style1 = sl.CreateStyle();
style1.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.Red, System.Drawing.Color.Red);
SLStyle style2 = sl.CreateStyle();
style2.Fill.SetPattern(PatternValues.Solid, System.Drawing.Color.LightGreen, System.Drawing.Color.Green);
// Kontrolni list broj
int test_report_no;
try
{
String readText = File.ReadAllText("Kontrolni_list_br.txt");
if (int.TryParse(readText, out test_report_no))
{
test_report_no++;
File.WriteAllText("Kontrolni_list_br.txt", test_report_no.ToString());
}
}
catch (FileNotFoundException)
{
// Stvori novi file
File.WriteAllText("Kontrolni_list_br.txt", "1");
test_report_no = 1;
}
sl.SetCellValue(3, 5, test_report_no);
// Zaglavlje
sl.SetCellValue(6, 4, App.pPostavke.tbTipTransformatora.Text);
sl.SetCellValue(7, 4, App.pPostavke.tbObjekt.Text);
sl.SetCellValue(8, 4, App.pPostavke.tbTvornickiBroj.Text);
sl.SetCellValue(6, 9, App.pPostavke.tbVrstaLima.Text);
sl.SetCellValue(7, 9, App.pDimenzije.odabirLimova.SheetName); // Oblik lima (odabran u dimenzijama)
sl.SetCellValue(8, 9, App.pPostavke.tbPaketBroj.Text);
sl.SetCellValue(7, 12, App.pPostavke.tbBrojKoluta.Text);
sl.SetCellValue(9, 12, App.pPostavke.tbCrtezBroj.Text);
// Slika lima
SLPicture pic = new SLPicture("Slike/dimenzije_slika_lima.png");
pic.SetPosition(10, 2);
sl.InsertPicture(pic);
// Datum
String dat = String.Format("{0:D}.{1:D}.{2:D4}.", DateAndTime.Now.Day, DateAndTime.Now.Month, DateAndTime.Now.Year);
sl.SetCellValue(48, 2, dat);
// Operater
sl.SetCellValue(48, 5, KorisnickoIme);
// Širina B
int dimensions_index = 0;
if (Dimensions[0].Kote[0] == 'B')
{
float calculatedDelta = Dimensions[0].Mjereno - Dimensions[0].Nazivno;
sl.SetCellValue(41, iStartColumnIndex, Dimensions[0].Kote); // ŠIRINA B WIDTH B
sl.SetCellValue(41, iStartColumnIndex + 7, Dimensions[0].DeltaPlus);
sl.SetCellValue(41, iStartColumnIndex + 9, Dimensions[0].DeltaMinus);
sl.SetCellValue(41, iStartColumnIndex + 1, Dimensions[0].Nazivno);
sl.SetCellValue(41, iStartColumnIndex + 4, Dimensions[0].Mjereno);
sl.SetCellValue(41, iStartColumnIndex + 10, calculatedDelta);
if (calculatedDelta > Dimensions[0].DeltaPlus || calculatedDelta < Dimensions[0].DeltaMinus)
{
sl.SetCellStyle(41, iStartColumnIndex + 10, style1);
}
else
{
sl.SetCellStyle(41, iStartColumnIndex + 10, style2);
}
dimensions_index = 1;
}
// Ostale dimenzije
for (int row = RowOffset; (row < (RowOffset + rows - dimensions_index)); ++row)
{
int ind = row - RowOffset + dimensions_index;
float calculatedDelta = Dimensions[ind].Mjereno - Dimensions[ind].Nazivno;
sl.SetCellValue(row, iStartColumnIndex, Dimensions[ind].Kote);
sl.SetCellValue(row, iStartColumnIndex + 7, Dimensions[ind].DeltaPlus);
sl.SetCellValue(row, iStartColumnIndex + 9, Dimensions[ind].DeltaMinus);
sl.SetCellValue(row, iStartColumnIndex + 1, Dimensions[ind].Nazivno);
sl.SetCellValue(row, iStartColumnIndex + 4, Dimensions[ind].Mjereno);
sl.SetCellValue(row, iStartColumnIndex + 10, calculatedDelta);
if (calculatedDelta > Dimensions[ind].DeltaPlus || calculatedDelta < Dimensions[ind].DeltaMinus)
{
sl.SetCellStyle(row, iStartColumnIndex + 10, style1);
}
else
{
sl.SetCellStyle(row, iStartColumnIndex + 10, style2);
}
}
// Prazni retci
for (int row = RowOffset + rows - dimensions_index; row < 41; row++)
{
sl.SetCellValue(row, iStartColumnIndex, "");
}
// Debljina lima
sl.SetCellValue(42, 6, ManualThicknessMeas);
const float tolerancija = 0.03f;
sl.SetCellValue(42, 9, tolerancija); // DeltaPlus
sl.SetCellValue(42, 11, tolerancija); // DeltaMinus
float delta;
if (Single.TryParse(App.pPostavke.tbNazivnaDebljina.Text.Replace(".", ","), out float nazivnaDebljina))
{
sl.SetCellValue(42, 3, nazivnaDebljina);
delta = ManualThicknessMeas - nazivnaDebljina;
sl.SetCellValue(42, 12, delta);
if (delta > tolerancija || delta < -tolerancija)
{
sl.SetCellStyle(42, 12, style1);
}
else
{
sl.SetCellStyle(42, 12, style2);
}
}
// Srh (poprečni?)
float srh = ((float)Srh_max) / 1000.0f;
sl.SetCellValue(43, 6, srh);
const float tolerancijaSrh = 0.02f;
sl.SetCellValue(43, 9, tolerancijaSrh);
sl.SetCellValue(43, 11, tolerancijaSrh);
float diff = srh - tolerancijaSrh;
if (diff < 0)
{
diff = 0;
}
sl.SetCellValue(43, 12, diff);
if (diff > 0)
{
sl.SetCellStyle(43, 12, style1);
}
else
{
sl.SetCellStyle(43, 12, style2);
}
String ime_lima = App.pDimenzije.odabirLimova.SheetName.Replace("\n", " ");
String datum = String.Format("{0:D4}-{1:D2}-{2:D2}", DateAndTime.Now.Year, DateAndTime.Now.Month, DateAndTime.Now.Day);
String vrijeme = String.Format("{0:D2}-{1:D2}-{2:D2}", DateAndTime.Now.Hour, DateAndTime.Now.Minute, DateAndTime.Now.Second);
if (!Directory.Exists("Izvjestaji/Kontrolni"))
{
Directory.CreateDirectory("Izvjestaji/Kontrolni");
}
var objectname = string.Join("_", App.pPostavke.tbObjekt.Text.Split(System.IO.Path.GetInvalidFileNameChars()));
if (objectname.Length != 0)
{
objectname = "/" + objectname;
}
if (!Directory.Exists("Izvjestaji/Kontrolni" + objectname))
{
Directory.CreateDirectory("Izvjestaji/Kontrolni" + objectname);
}
sl.SaveAs("Izvjestaji/Kontrolni" + objectname + "/" + test_report_no.ToString() + "_" + ime_lima + "_" + datum + "-" + vrijeme + ".xlsx");
// Success
App.pIzvjestaji.b_generateReport1.Foreground = new SolidColorBrush(System.Windows.Media.Colors.Green);
}
}
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();
}