Esempio n. 1
0
        private static int AddDirectory(ExcelWorksheet ws, DirectoryInfo dir, int row, double height, int level, bool skipIcons)
        {
            //Get the icon as a bitmap
            Console.WriteLine("Directory " + dir.Name);
            if (!skipIcons)
            {
                Bitmap icon = GetIcon(dir.FullName);

                ws.Row(row).Height = height;
                //Add the icon as a picture
                if (icon != null)
                {
                    ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), icon);
                    pic.SetPosition((int)20 * (row - 1) + 2, 0);
                }
            }
            ws.Cells[row, 2].Value = dir.Name;
            ws.Cells[row, 4].Value = dir.CreationTime;
            ws.Cells[row, 5].Value = dir.LastAccessTime;

            ws.Cells[row, 2, row, 5].Style.Font.Bold = true;
            //Sets the outline depth
            ws.Row(row).OutlineLevel = level;

            int prevRow = row;

            row++;
            //Add subdirectories
            foreach (DirectoryInfo subDir in dir.GetDirectories())
            {
                if (level < _maxLevels)
                {
                    row = AddDirectory(ws, subDir, row, height, level + 1, skipIcons);
                }
            }

            //Add files in the directory
            foreach (FileInfo file in dir.GetFiles())
            {
                if (!skipIcons)
                {
                    Bitmap fileIcon = GetIcon(file.FullName);

                    ws.Row(row).Height = height;
                    if (fileIcon != null)
                    {
                        ExcelPicture pic = ws.Drawings.AddPicture("pic" + (row).ToString(), fileIcon);
                        pic.SetPosition((int)20 * (row - 1) + 2, 0);
                    }
                }

                ws.Cells[row, 2].Value = file.Name;
                ws.Cells[row, 3].Value = file.Length;
                ws.Cells[row, 4].Value = file.CreationTime;
                ws.Cells[row, 5].Value = file.LastAccessTime;

                ws.Row(row).OutlineLevel = level + 1;

                AddStatistics(file);

                row++;
            }

            //Add a subtotal for the directory
            if (row - 1 > prevRow)
            {
                ws.Cells[prevRow, 3].Formula = string.Format("SUBTOTAL(9, {0})", ExcelCellBase.GetAddress(prevRow + 1, 3, row - 1, 3));
            }
            else
            {
                ws.Cells[prevRow, 3].Value = 0;
            }

            return(row);
        }
Esempio n. 2
0
        /// <summary>
        /// Método para generar el reporte en excel.
        /// </summary>
        /// <param name="infoTable">Datatable con la información que se desplegará en el Excel</param>
        /// <param name="tituloReporte">Título que se mostrará en el Excel</param>
        /// <returns></returns>
        public MemoryStream GenerarReporteExcel(DataTable infoTable, string tituloReporte, string labFiltro, string firmaUsuario)
        {
            ReportesController objReportesCont = new ReportesController();

            Models.Reportes infoParametros = objReportesCont.ObtenerParametrosReporteJSON();
            MemoryStream    memStream      = new MemoryStream();
            //Cargar la plantilla para reportes en Excel
            var fileinfo = new FileInfo(path_Plantilla);

            using (ExcelPackage pck = new ExcelPackage(fileinfo))
            {
                if (labFiltro == "Mostrar Todos")
                {
                    labFiltro = "Todos";
                }
                else if (labFiltro == "")
                {
                    labFiltro = "-";
                }
                string nombreImage = string.Empty;
                string pathAux     = System.Web.Hosting.HostingEnvironment.MapPath("~/Content/Images/LogoReporte/LogoNuevo.jpg");
                string pathAux2    = System.Web.Hosting.HostingEnvironment.MapPath("~/Content/Images/LogoReporte/LogoNuevo.png");
                if (File.Exists(pathAux))
                {
                    nombreImage = "LogoNuevo.jpg";
                }
                else if (File.Exists(pathAux2))
                {
                    nombreImage = "LogoNuevo.png";
                }
                else
                {
                    nombreImage = "LogoUPS.png";
                }
                //Carga de la primera hoja de trabajo del Excel
                ExcelWorksheet ws = pck.Workbook.Worksheets[1];
                ws.Column(1).Width = 25;
                Image        img = Image.FromFile(System.Web.Hosting.HostingEnvironment.MapPath(string.Format("~/Content/Images/LogoReporte/{0}", nombreImage)));
                ExcelPicture pic = ws.Drawings.AddPicture("Logo", img);
                pic.From.Column = 0;
                pic.From.Row    = 0;
                pic.SetSize(170, 60);
                pic.From.ColumnOff = 2 * 9525;
                pic.From.RowOff    = 2 * 9525;

                ws.Cells["A8"].LoadFromDataTable(infoTable, true);
                //Titulos Organización
                ConfigCell(1, 2, 1, infoTable.Columns.Count, ws, infoTable.Columns.Count, string.Format("{0} {1}", infoParametros.TituloCarrera, infoParametros.TituloSedeCampus), true, true, Color.White, Color.Black, ExcelHorizontalAlignment.Center);
                ConfigCell(2, 2, 2, infoTable.Columns.Count, ws, infoTable.Columns.Count, infoParametros.TituloSistema, true, true, Color.White, Color.Black, ExcelHorizontalAlignment.Center);
                ConfigCell(3, 2, 3, infoTable.Columns.Count, ws, infoTable.Columns.Count, infoParametros.TituloReporte, true, true, Color.White, Color.Black, ExcelHorizontalAlignment.Center);
                //ws.Cells[1, 1, 7, infoTable.Columns.Count].AutoFitColumns();

                //Carga de Parámetros
                //Campus
                //ConfigCell(4,2,4, infoTable.Columns.Count,ws, infoTable.Columns.Count, "Quito, campus sur.",true,false,Color.White, Color.Black, ExcelHorizontalAlignment.Left);
                //Fecha
                ConfigCell(4, 2, 4, infoTable.Columns.Count, ws, infoTable.Columns.Count, DateTime.Now.ToLongDateString(), true, false, Color.White, Color.Black, ExcelHorizontalAlignment.Left);
                //ws.Cells[4, 2, 4, infoTable.Columns.Count].AutoFitColumns();
                //Laboratorio
                ConfigCell(5, 2, 5, infoTable.Columns.Count, ws, infoTable.Columns.Count, labFiltro, true, false, Color.White, Color.Black, ExcelHorizontalAlignment.Left);
                //N° Equipos
                ConfigCell(6, 2, 6, infoTable.Columns.Count, ws, infoTable.Columns.Count, infoTable.Rows.Count + "", true, false, Color.White, Color.Black, ExcelHorizontalAlignment.Left);
                //Título del la lista del reporte
                ConfigCell(7, 1, 7, infoTable.Columns.Count, ws, infoTable.Columns.Count, "LISTADO DE " + tituloReporte.ToUpper(), true, true, ColorTranslator.FromHtml("#3c5a77"), ColorTranslator.FromHtml("#E7E7E7"), ExcelHorizontalAlignment.Center);
                //Encabezado de la tabla
                ConfigCell(8, 1, 8, infoTable.Columns.Count, ws, infoTable.Columns.Count, null, false, true, ColorTranslator.FromHtml("#3c5a77"), ColorTranslator.FromHtml("#E7E7E7"), ExcelHorizontalAlignment.Center);
                //Datos
                ConfigCell(9, 1, 8 + infoTable.Rows.Count, infoTable.Columns.Count, ws, infoTable.Columns.Count, null, false, false, Color.White, Color.Black, ExcelHorizontalAlignment.Left);
                //Elaborado por
                ConfigCell(9 + infoTable.Rows.Count, 1, 9 + infoTable.Rows.Count, 1, ws, 1, "ELABORADO POR: ", false, true, ColorTranslator.FromHtml("#ededed"), ColorTranslator.FromHtml("#23527c"), ExcelHorizontalAlignment.Left);
                ConfigCell(9 + infoTable.Rows.Count, 2, 9 + infoTable.Rows.Count, infoTable.Columns.Count, ws, infoTable.Columns.Count, Regex.Replace(firmaUsuario, @"(^\w)|(\s\w)", m => m.Value.ToUpper()), true, false, ColorTranslator.FromHtml("#ededed"), ColorTranslator.FromHtml("#23527c"), ExcelHorizontalAlignment.Left);
                //Autoajustar las celdas para los datos
                ws.Cells[7, 2, 7 + infoTable.Rows.Count, infoTable.Columns.Count].AutoFitColumns();
                ws.Cells["A8"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                //Transformar el archivo a Bytes
                memStream = new MemoryStream(pck.GetAsByteArray());
                pck.Dispose();
                ws.Dispose();
                img.Dispose();
                pic.Dispose();
            }
            return(memStream);
        }
Esempio n. 3
0
        public void auc_report()
        {
            SaveFileDialog sfd = new SaveFileDialog();

            sfd.Filter   = "Excel Documents (*.xlsx)|*.xlsx";
            sfd.FileName = "AUC_Report.xlsx";

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                string filename = sfd.FileName;

                string path = filename.Substring(0, filename.LastIndexOf('\\')).Substring(0, filename.LastIndexOf('\\'));

                List <string> img_paths   = new List <string>();
                List <string> chart_title = new List <string>();

                Dictionary <string, Chart_Patient> auc_by_descriptor         = _main_tab.get_charts_auc();
                Dictionary <string, Chart_Patient> auc_z_score_by_descriptor = _main_tab.get_charts_auc_z_score();


                if (auc_by_descriptor.Count > 0)
                {
                    foreach (KeyValuePair <string, Chart_Patient> elem in auc_by_descriptor)
                    {
                        Chart_Patient current_chart = elem.Value;
                        string        img_path      = current_chart.save_image(path);
                        img_paths.Add(img_path);

                        chart_title.Add(current_chart.get_descriptor() + "AUC ");
                    }
                }
                else
                {
                    _main_tab.compute_auc("auc");

                    foreach (KeyValuePair <string, Chart_Patient> elem in auc_by_descriptor)
                    {
                        Chart_Patient current_chart = elem.Value;
                        string        img_path      = current_chart.save_image(path);
                        img_paths.Add(img_path);

                        chart_title.Add(current_chart.get_descriptor() + "AUC ");
                    }
                }

                if (auc_z_score_by_descriptor.Count > 0)
                {
                    foreach (KeyValuePair <string, Chart_Patient> elem in auc_z_score_by_descriptor)
                    {
                        Chart_Patient current_chart = elem.Value;
                        string        img_path      = current_chart.save_image(path);
                        img_paths.Add(img_path);

                        chart_title.Add(current_chart.get_descriptor() + "AUC (Z-Score");
                    }
                }
                else
                {
                    _main_tab.compute_auc("z-score");

                    foreach (KeyValuePair <string, Chart_Patient> elem in auc_z_score_by_descriptor)
                    {
                        Chart_Patient current_chart = elem.Value;
                        string        img_path      = current_chart.save_image(path);
                        img_paths.Add(img_path);

                        chart_title.Add(current_chart.get_descriptor() + "AUC (Z-Score");
                    }
                }

                toolStripProgressBar1.Visible = true;

                ExcelPackage   pck      = new ExcelPackage();
                ExcelWorksheet ws       = pck.Workbook.Worksheets.Add("AUC_Graph");
                ExcelWorksheet ws_table = pck.Workbook.Worksheets.Add("AUC_Table");

                List <string> list_cpds = new List <string>();

                Dictionary <string, double> cpd_auc = auc_by_descriptor[auc_by_descriptor.First().Key].get_auc_values();
                foreach (KeyValuePair <string, double> elem in cpd_auc)
                {
                    list_cpds.Add(elem.Key);
                }

                int counter = 0;

                // Compute the number of rows/cols to merge to insert the AUC chart :

                int image_width  = 1100;
                int image_height = 500;

                Graphics g = this.CreateGraphics();

                double height = (double)image_height / g.DpiY * 72.0f;       //  g.DpiY
                double width  = (double)image_width / g.DpiX * 72.0f / 5.1f; // image_width; g.DpiX

                double height_excel = 0.0;
                double width_excel  = 0.0;

                int row_nb_auc_graph = 0;
                int col_nb_auc_graph = 0;

                for (int i = 1; i <= 1000; ++i)
                {
                    height_excel += ws.Row(i).Height;

                    if (height_excel >= height)
                    {
                        row_nb_auc_graph = i;
                        break;
                    }
                }

                for (int i = 1; i <= 1000; ++i)
                {
                    width_excel += ws.Column(i).Width;

                    if (width_excel >= width)
                    {
                        col_nb_auc_graph = i;
                        break;
                    }
                }

                int img_rows = row_nb_auc_graph;
                int img_cols = col_nb_auc_graph;

                // Insert images Graphs AUC :

                foreach (string current_path in img_paths)
                {
                    ws.Cells[counter * img_rows + 3, 1, (counter + 1) * img_rows + 1, img_cols].Merge = true;

                    Bitmap img = (Bitmap)LoadImageNoLock(current_path);

                    string name_idx = counter.ToString();

                    ExcelPicture excelImage = null;

                    excelImage             = ws.Drawings.AddPicture(name_idx, img);
                    excelImage.From.Column = 1;
                    excelImage.From.Row    = counter * img_rows + 2;
                    excelImage.SetSize(1100, 500);

                    counter++;
                }


                // Table DRC :

                Graphics g2 = this.CreateGraphics();

                int drc_width  = 485;
                int drc_height = 350;

                double height_excel_drc = (double)drc_height / g2.DpiY * 72.0f;       //  g.DpiY
                double width_excel_drc  = (double)drc_width / g2.DpiX * 72.0f / 5.1f; // image_width; g.DpiX

                // Columns resize :
                int index_cpd = 0;
                for (int i = 0; i < list_cpds.Count; ++i)
                {
                    if (list_cpds[i] != "DMSO" && list_cpds[i] != "Untreated")
                    {
                        index_cpd = i;
                        break;
                    }
                }

                int max_col_nb = 1 + 5 * _main_tab.get_descriptors_chart()[list_cpds[index_cpd]].Count;

                for (int j = 1; j <= max_col_nb; j++)
                {
                    if (j == 1)
                    {
                        ws_table.Column(j).Width = 35;
                    }
                    else
                    {
                        if (j % 5 == 2)
                        {
                            ws_table.Column(j).Width = width_excel_drc;
                        }
                        if ((j - 1) % 5 == 2 || (j - 1) % 5 == 3 || (j - 1) % 5 == 4)
                        {
                            ws_table.Column(j).Width = 18;
                        }
                        if ((j - 1) % 5 == 0)
                        {
                            ws_table.Column(j).Width = 20;
                        }
                    }
                }

                int max_row_nb = list_cpds.Count;

                for (int i = 1; i <= max_row_nb + 1; i++)
                {
                    if (i == 1)
                    {
                        ws_table.Row(i).Height = 15;
                    }
                    else
                    {
                        ws_table.Row(i).Height = height_excel_drc;
                    }
                }

                int cellRowIndex = 1;

                ws_table.Cells[cellRowIndex, 1].Value = "BATCH_ID";

                ws_table.Cells[cellRowIndex, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                ws_table.Cells[cellRowIndex, 1].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                ws_table.Cells[cellRowIndex, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                ws_table.Cells[cellRowIndex, 1].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                ws_table.Cells[cellRowIndex, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                ws_table.Cells[cellRowIndex, 1].Style.Font.Color.SetColor(Color.White);
                ws_table.Cells[cellRowIndex, 1].Style.Font.Bold = true;

                for (int k = 0; k < img_paths.Count / 2; ++k)
                {
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Value = "DRC  Curve";
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Value = "AUC";
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Value = "AUC Error";
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Value = "AUC (Z-Score)";
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Value = "AUC Error (Z-Score)";

                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.Font.Color.SetColor(Color.White);
                    ws_table.Cells[cellRowIndex, 5 * k + 2].Style.Font.Bold = true;

                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.Font.Color.SetColor(Color.White);
                    ws_table.Cells[cellRowIndex, 5 * k + 3].Style.Font.Bold = true;

                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.Font.Color.SetColor(Color.White);
                    ws_table.Cells[cellRowIndex, 5 * k + 4].Style.Font.Bold = true;

                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.Font.Color.SetColor(Color.White);
                    ws_table.Cells[cellRowIndex, 5 * k + 5].Style.Font.Bold = true;

                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.Font.Color.SetColor(Color.White);
                    ws_table.Cells[cellRowIndex, 5 * k + 6].Style.Font.Bold = true;
                }

                cellRowIndex++;

                toolStripProgressBar1.Visible = true;

                for (int idx = 0; idx < list_cpds.Count; idx++)
                {
                    toolStripProgressBar1.Value = idx * 100 / (list_cpds.Count - 1);
                    //toolStripStatusLabel1.Text = toolStripProgressBar1.Value.ToString();
                    //toolStripStatusLabel1.Visible=true;
                    string BATCH_ID = list_cpds[idx].ToString();

                    if (BATCH_ID.Contains("DMSO") || BATCH_ID.Contains("Untreated"))
                    {
                        continue;
                    }

                    _main_tab.tableLayoutPanel1.Controls.Clear();

                    Dictionary <string, List <Chart_DRC> > cpd_charts = _main_tab.get_descriptors_chart();

                    List <Chart_DRC> list_chart = cpd_charts[BATCH_ID];

                    List <string> list_images = new List <string>();

                    foreach (Chart_DRC current_chart in list_chart)
                    {
                        current_chart.draw_DRC(false, false);

                        if (current_chart.get_window_y_max() < 1.5)
                        {
                            current_chart.set_general_params(true);
                            current_chart.set_data_modified(true);
                            current_chart.set_window_y_min(0.0);
                            current_chart.set_window_y_max(1.5);
                        }

                        string image_path = current_chart.save_image(path);
                        list_images.Add(image_path);
                    }

                    int i_img = 0;

                    foreach (Chart_DRC current_chart in list_chart)
                    {
                        ws_table.Cells[cellRowIndex, 1].Value = BATCH_ID;

                        ws_table.Cells[cellRowIndex, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 1].Style.Fill.BackgroundColor.SetColor(Color.Gray);
                        ws_table.Cells[cellRowIndex, 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 1].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 1].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Medium);
                        ws_table.Cells[cellRowIndex, 1].Style.Font.Color.SetColor(Color.White);
                        ws_table.Cells[cellRowIndex, 1].Style.Font.Bold = true;


                        Bitmap       img        = (Bitmap)LoadImageNoLock(list_images[i_img]);
                        string       name_idx   = "DRC_Curve" + "_" + BATCH_ID + "_" + i_img.ToString();
                        ExcelPicture excelImage = null;

                        excelImage             = ws_table.Drawings.AddPicture(name_idx, img);
                        excelImage.From.Column = 5 * i_img + 1;
                        excelImage.From.Row    = cellRowIndex - 1;
                        excelImage.SetSize(485, 350);

                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Value = auc_by_descriptor[current_chart.get_Descriptor_Name()].get_auc_values()[BATCH_ID];
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Value = auc_by_descriptor[current_chart.get_Descriptor_Name()].get_auc_error_values()[BATCH_ID];
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Value = auc_z_score_by_descriptor[current_chart.get_Descriptor_Name()].get_auc_values()[BATCH_ID];
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Value = auc_z_score_by_descriptor[current_chart.get_Descriptor_Name()].get_auc_error_values()[BATCH_ID];

                        ws_table.Cells[cellRowIndex, 5 * i_img + 2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 2].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        ws_table.Cells[cellRowIndex, 5 * i_img + 2].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 2].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);

                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.Numberformat.Format = "0.00";
                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 3].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);

                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.Numberformat.Format = "0.00";
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 4].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);

                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.Numberformat.Format = "0.00";
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 5].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);

                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.Numberformat.Format = "0.00";
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.Fill.PatternType    = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                        ws_table.Cells[cellRowIndex, 5 * i_img + 6].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Hair);

                        i_img++;
                    }

                    //cellColumnIndex = 1;
                    cellRowIndex++;

                    foreach (string current_img in list_images)
                    {
                        File.Delete(current_img);
                    }
                }

                toolStripProgressBar1.Visible = false;
                pck.SaveAs(new FileInfo(@"" + sfd.FileName));

                pck.Dispose();

                foreach (string current_path in img_paths)
                {
                    File.Delete(current_path);
                }

                MessageBox.Show("Export Successful");
            }
        }
        public MemoryStream GenerateExcelTemplate(GarmentPackingListViewModel viewModel, string fob, string cPrice)
        {
            var newItems   = new List <GarmentPackingListItemViewModel>();
            var newItems2  = new List <GarmentPackingListItemViewModel>();
            var newDetails = new List <GarmentPackingListDetailViewModel>();

            foreach (var item in viewModel.Items)
            {
                foreach (var detail in item.Details)
                {
                    newDetails.Add(detail);
                }
            }
            // if wanna group by order no delete the note by @zhikariz
            newDetails = newDetails.OrderBy(a => a.Index).ThenBy(o => o.Carton1).ThenBy(o => o.Carton2).ToList();

            foreach (var d in newDetails)
            {
                if (newItems.Count == 0)
                {
                    var i = viewModel.Items.Single(a => a.Id == d.PackingListItemId);
                    i.Details = new List <GarmentPackingListDetailViewModel>();
                    i.Details.Add(d);
                    newItems.Add(i);
                }
                else
                {
                    if (newItems.Last().Id == d.PackingListItemId)
                    {
                        newItems.Last().Details.Add(d);
                    }
                    else
                    {
                        var y = viewModel.Items.Select(a => new GarmentPackingListItemViewModel
                        {
                            Id                  = a.Id,
                            RONo                = a.RONo,
                            Article             = a.Article,
                            BuyerAgent          = a.BuyerAgent,
                            ComodityDescription = a.ComodityDescription,
                            OrderNo             = a.OrderNo,
                            AVG_GW              = a.AVG_GW,
                            AVG_NW              = a.AVG_NW,
                            Description         = a.Description
                        })
                                .Single(a => a.Id == d.PackingListItemId);
                        y.Details = new List <GarmentPackingListDetailViewModel>();
                        y.Details.Add(d);
                        newItems.Add(y);
                    }
                }
            }

            foreach (var item in newItems)
            {
                if (newItems2.Count == 0)
                {
                    newItems2.Add(item);
                }
                else
                {
                    if (newItems2.Last().OrderNo == item.OrderNo)
                    {
                        foreach (var d in item.Details.OrderBy(a => a.Carton1))
                        {
                            newItems2.Last().Details.Add(d);
                        }
                    }
                    else
                    {
                        newItems2.Add(item);
                    }
                }
            }

            var sizesCount = false;

            foreach (var item in newItems2)
            {
                var sizesMax = new Dictionary <int, string>();
                foreach (var detail in item.Details.OrderBy(a => a.Carton1).ThenBy(a => a.Carton2))
                {
                    foreach (var size in detail.Sizes)
                    {
                        sizesMax[size.Size.Id] = size.Size.Size;
                    }
                }
                if (sizesMax.Count > 11)
                {
                    sizesCount = true;
                }
            }
            int SIZES_COUNT = sizesCount ? 20 : 11;

            var col       = GetColNameFromIndex(4 + SIZES_COUNT);
            var colCtns   = GetColNameFromIndex(SIZES_COUNT + 5);
            var colPcs    = GetColNameFromIndex(SIZES_COUNT + 6);
            var colQty    = GetColNameFromIndex(SIZES_COUNT + 7);
            var colSatuan = GetColNameFromIndex(SIZES_COUNT + 8);
            var colGw     = GetColNameFromIndex(SIZES_COUNT + 9);
            var colNw     = GetColNameFromIndex(SIZES_COUNT + 10);
            var colNnw    = GetColNameFromIndex(SIZES_COUNT + 11);



            DataTable result = new DataTable();

            ExcelPackage package = new ExcelPackage();
            var          sheet   = package.Workbook.Worksheets.Add("Report");


            sheet.Cells["A1"].Value    = "Invoice No.";
            sheet.Cells["A1:B1"].Merge = true;
            sheet.Column(1).Width      = 6;
            sheet.Column(2).Width      = 5;
            sheet.Column(3).Width      = 6;
            sheet.Column(3).Width      = 7;
            sheet.Cells["C1"].Value    = viewModel.InvoiceNo;
            sheet.Cells["C1:D1"].Merge = true;
            sheet.Cells[$"A1:{colNnw}1"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Medium;
            sheet.Cells["E1"].Value                    = "Date : " + viewModel.Date.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("MMM dd, yyyy.");
            sheet.Cells[$"E1:{col}1"].Merge            = true;
            sheet.Cells[$"{colCtns}1"].Value           = " ";
            sheet.Cells[$"{colCtns}1"].Style.Font.Bold = true;
            sheet.Cells[$"{colCtns}1:{colNnw}1"].Merge = true;
            sheet.Cells["A3"].Value                    = cPrice;
            sheet.Cells["A3:C3"].Merge                 = true;
            sheet.Cells["D3"].Value                    = ":";
            sheet.Cells["E3"].Value                    = fob;
            sheet.Cells[$"E3:{colNnw}3"].Merge         = true;
            if (viewModel.PaymentTerm == "LC")
            {
                sheet.Cells["A4"].Value            = "LC No.";
                sheet.Cells["A4:C4"].Merge         = true;
                sheet.Cells["D4"].Value            = ":";
                sheet.Cells["E4"].Value            = viewModel.LCNo;
                sheet.Cells[$"E4:{colNnw}4"].Merge = true;
                sheet.Cells["A5"].Value            = "Tgl LC.";
                sheet.Cells["A5:C5"].Merge         = true;
                sheet.Cells["D5"].Value            = ":";
                sheet.Cells["E5"].Value            = viewModel.LCDate.GetValueOrDefault().ToOffset(new TimeSpan(_identityProvider.TimezoneOffset, 0, 0)).ToString("dd MMMM yyyy");
                sheet.Cells[$"E5:{colNnw}5"].Merge = true;
                sheet.Cells["A6"].Value            = "ISSUED BY";
                sheet.Cells["A6:C6"].Merge         = true;
                sheet.Cells["D6"].Value            = ":";
                sheet.Cells["E6"].Value            = viewModel.IssuedBy;
                sheet.Cells[$"E6:{colNnw}6"].Merge = true;
            }
            else
            {
                sheet.Cells["A4"].Value            = "Payment Term";
                sheet.Cells["A4:C4"].Merge         = true;
                sheet.Cells["E4"].Value            = viewModel.PaymentTerm;
                sheet.Cells[$"E4:{colNnw}4"].Merge = true;
            }

            double        totalCtns       = 0;
            double        totalGw         = 0;
            double        totalNw         = 0;
            double        totalNnw        = 0;
            double        grandTotal      = 0;
            var           uom             = "";
            var           arrayGrandTotal = new Dictionary <String, double>();
            List <string> cartonNumbers   = new List <string>();

            var index = 8;
            var afterSubTotalIndex = 0;



            foreach (var item in newItems2)
            {
                var afterIndex = index + 1;
                var sizeIndex  = afterIndex + 1;
                var valueIndex = sizeIndex + 1;

                sheet.Cells[$"A{index}"].Value          = "DESCRIPTION OF GOODS";
                sheet.Cells[$"A{index}:C{index}"].Merge = true;
                sheet.Row(index).Height        = 25;
                sheet.Cells[$"D{index}"].Value = ":";
                sheet.Cells[$"E{index}"].Value = item.Description;
                sheet.Cells[$"E{index}:{colNnw}{index}"].Merge = true;

                sheet.Cells[$"A{afterIndex}"].Value = "CARTON NO.";
                sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.VerticalAlignment             = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"A{afterIndex}:A{afterIndex}"].Style.HorizontalAlignment           = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Top.Style       = OfficeOpenXml.Style.ExcelBorderStyle.Double;
                sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex}"].Style.Border.Bottom.Style    = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                sheet.Cells[$"A{afterIndex}:A{afterIndex + 1}"].Style.Border.Left.Style         = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                sheet.Cells[$"A{afterIndex}:{colNnw}{afterIndex + 1}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                sheet.Cells[$"B{afterIndex}"].Value = "COLOUR";
                sheet.Cells[$"B{afterIndex}:B{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"B{afterIndex}:B{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"C{afterIndex}"].Value = "STYLE";
                sheet.Cells[$"C{afterIndex}:C{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"C{afterIndex}:C{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"D{afterIndex}"].Value = "ORDER. NO.";
                sheet.Cells[$"D{afterIndex}:D{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"D{afterIndex}:D{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"E{afterIndex}"].Value = "SIZE";
                sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Merge = true;
                sheet.Cells[$"E{afterIndex}:{col}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;


                var sizes = new Dictionary <int, string>();
                foreach (var detail in item.Details)
                {
                    foreach (var size in detail.Sizes)
                    {
                        sizes[size.Size.Id] = size.Size.Size;
                    }
                }
                ;
                for (int i = 0; i < SIZES_COUNT; i++)
                {
                    var colSize = GetColNameFromIndex(5 + i);
                    var size    = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
                    sheet.Cells[$"{colSize}{sizeIndex}"].Value = size.Key == 0 ? "" : size.Value;
                    sheet.Cells[$"{colSize}{sizeIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                }

                sheet.Cells[$"{colCtns}{afterIndex}"].Value       = "CTNS";
                sheet.Column(GetColNumberFromName(colCtns)).Width = 4;
                sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colCtns}{afterIndex}:{colCtns}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colPcs}{afterIndex}"].Value       = "@";
                sheet.Column(GetColNumberFromName(colPcs)).Width = 4;
                sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colPcs}{afterIndex}:{colPcs}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colQty}{afterIndex}"].Value       = "QTY";
                sheet.Column(GetColNumberFromName(colQty)).Width = 4;
                sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colQty}{afterIndex}:{colQty}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colSatuan}{afterIndex}"].Value       = "SATUAN";
                sheet.Column(GetColNumberFromName(colSatuan)).Width = 6;
                sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colSatuan}{afterIndex}:{colSatuan}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colGw}{afterIndex}"].Value       = "GW";
                sheet.Column(GetColNumberFromName(colGw)).Width = 4;
                sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colGw}{afterIndex}:{colGw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colNw}{afterIndex}"].Value       = "NW";
                sheet.Column(GetColNumberFromName(colNw)).Width = 4;
                sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colNw}{afterIndex}:{colNw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                sheet.Cells[$"{colNnw}{afterIndex}"].Value       = "NNW";
                sheet.Column(GetColNumberFromName(colNnw)).Width = 4;
                sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex + 1}"].Merge = true;
                sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.VerticalAlignment   = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                sheet.Cells[$"{colNnw}{afterIndex}:{colNnw}{afterIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                double subCtns       = 0;
                double subGw         = 0;
                double subNw         = 0;
                double subNnw        = 0;
                double subTotal      = 0;
                var    sizeSumQty    = new Dictionary <int, double>();
                var    arraySubTotal = new Dictionary <String, double>();
                foreach (var detail in item.Details.OrderBy(o => o.Carton1).ThenBy(o => o.Carton2))
                {
                    var ctnsQty = detail.CartonQuantity;
                    uom = viewModel.Items.Where(a => a.Id == detail.PackingListItemId).Single().Uom.Unit;
                    if (cartonNumbers.Contains($"{detail.Carton1}- {detail.Carton2}"))
                    {
                        ctnsQty = 0;
                    }
                    else
                    {
                        cartonNumbers.Add($"{detail.Carton1}- {detail.Carton2}");
                    }
                    sheet.Cells[$"A{valueIndex}"].Value = $"{detail.Carton1}- {detail.Carton2}";
                    sheet.Cells[$"A{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    sheet.Cells[$"B{valueIndex}"].Value = detail.Colour;
                    sheet.Cells[$"B{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    sheet.Cells[$"C{valueIndex}"].Value = detail.Style;
                    sheet.Cells[$"C{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    sheet.Cells[$"D{valueIndex}"].Value = item.OrderNo;
                    sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                    sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;


                    for (int i = 0; i < SIZES_COUNT; i++)
                    {
                        var    colSize  = GetColNameFromIndex(5 + i);
                        var    size     = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
                        double quantity = 0;
                        if (size.Key != 0)
                        {
                            quantity = detail.Sizes.Where(w => w.Size.Id == size.Key).Sum(s => s.Quantity);
                        }

                        if (sizeSumQty.ContainsKey(size.Key))
                        {
                            sizeSumQty[size.Key] += quantity * detail.CartonQuantity;
                        }
                        else
                        {
                            sizeSumQty.Add(size.Key, quantity * detail.CartonQuantity);
                        }


                        sheet.Cells[$"{colSize}{valueIndex}"].Value       = quantity == 0 ? "" : quantity.ToString();
                        sheet.Column(GetColNumberFromName(colSize)).Width = 3.5;
                        sheet.Cells[$"{colSize}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                    }
                    subCtns += ctnsQty;
                    subGw   += detail.GrossWeight;
                    subNw   += detail.NetWeight;
                    subNnw  += detail.NetNetWeight;

                    sheet.Cells[$"{colCtns}{valueIndex}"].Value = ctnsQty.ToString();
                    sheet.Cells[$"{colCtns}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colPcs}{valueIndex}"].Value = detail.QuantityPCS.ToString();
                    sheet.Cells[$"{colPcs}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    var totalQuantity = detail.CartonQuantity * detail.QuantityPCS;
                    subTotal += totalQuantity;
                    if (!arraySubTotal.ContainsKey(uom))
                    {
                        arraySubTotal.Add(uom, totalQuantity);
                    }
                    else
                    {
                        arraySubTotal[uom] += totalQuantity;
                    }

                    sheet.Cells[$"{colQty}{valueIndex}"].Value = totalQuantity.ToString();
                    sheet.Cells[$"{colQty}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colSatuan}{valueIndex}"].Value = uom;
                    sheet.Cells[$"{colSatuan}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colGw}{valueIndex}"].Value = detail.GrossWeight.ToString();
                    sheet.Cells[$"{colGw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colNw}{valueIndex}"].Value = detail.NetWeight.ToString();
                    sheet.Cells[$"{colNw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colNnw}{valueIndex}"].Value = detail.NetNetWeight.ToString();
                    sheet.Cells[$"{colNnw}{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    valueIndex++;
                }

                var sumValueIndex = 0;
                for (int i = 0; i < SIZES_COUNT; i++)
                {
                    var colSize = GetColNameFromIndex(5 + i);
                    sumValueIndex = valueIndex + 1;
                    var    size     = sizes.OrderBy(a => a.Value).ElementAtOrDefault(i);
                    double quantity = 0;
                    if (size.Key != 0)
                    {
                        quantity = sizeSumQty.Where(w => w.Key == size.Key).Sum(a => a.Value);
                    }

                    sheet.Cells[$"D{valueIndex}"].Value = "SUMMARY";
                    sheet.Cells[$"D{valueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                    sheet.Cells[$"{colSize}{valueIndex}"].Value = quantity == 0 ? "" : quantity.ToString();
                }

                sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                sheet.Cells[$"A{valueIndex}:{colNnw}{valueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                totalCtns  += subCtns;
                totalGw    += subGw;
                totalNw    += subNw;
                totalNnw   += subNnw;
                grandTotal += subTotal;
                if (!arrayGrandTotal.ContainsKey(uom))
                {
                    arrayGrandTotal.Add(uom, subTotal);
                }
                else
                {
                    arrayGrandTotal[uom] += subTotal;
                }
                var subTotalResult = string.Join(" / ", arraySubTotal.Select(x => x.Value + " " + x.Key).ToArray());
                sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Merge = true;
                sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Value = "SUB TOTAL";
                sheet.Cells[$"A{sumValueIndex}:{colPcs}{sumValueIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"{colQty}{sumValueIndex}"].Value = subTotalResult;
                sheet.Cells[$"{colQty}{sumValueIndex}"].Style.HorizontalAlignment     = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"{colQty}{sumValueIndex}:{colNnw}{sumValueIndex}"].Merge = true;
                sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                sheet.Cells[$"A{sumValueIndex}:{colNnw}{sumValueIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                afterSubTotalIndex = sumValueIndex + 1;
                sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Merge = true;

                sheet.Cells[$"A{afterSubTotalIndex}"].Value = $"      - Sub Ctns = {subCtns}       - Sub G.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.GrossWeight * a.CartonQuantity))} Kgs      - Sub N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetWeight * a.CartonQuantity))} Kgs     - Sub N.N.W. = {String.Format("{0:0.00}", item.Details.Sum(a => a.NetNetWeight * a.CartonQuantity))} Kgs";
                sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
                sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thin);
                sheet.Cells[$"A{afterSubTotalIndex}:{colNnw}{afterSubTotalIndex}"].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

                afterIndex = sizeIndex++;
                index      = afterSubTotalIndex + 2;
            }

            #region GrandTotal
            var grandTotalResult = string.Join(" / ", arrayGrandTotal.Select(x => x.Value + " " + x.Key).ToArray());
            var grandTotalIndex  = afterSubTotalIndex + 2;
            sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Merge = true;
            sheet.Cells[$"A{grandTotalIndex}:{colPcs}{grandTotalIndex}"].Value = "GRAND TOTAL";
            sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Top.Style    = OfficeOpenXml.Style.ExcelBorderStyle.Double;
            sheet.Cells[$"A{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Double;
            sheet.Cells[$"{colQty}{grandTotalIndex}"].Value = grandTotalResult;
            sheet.Cells[$"{colQty}{grandTotalIndex}:{colNnw}{grandTotalIndex}"].Merge = true;

            var comodities        = viewModel.Items.Select(s => s.Comodity.Id > 0? s.Comodity.Name.ToUpper():"FABRIC").Distinct();
            var spellingWordIndex = grandTotalIndex + 2;
            sheet.Cells[$"A{spellingWordIndex}:{colNnw}{spellingWordIndex}"].Merge = true;
            sheet.Cells[$"A{spellingWordIndex}"].Value = $"{totalCtns} {viewModel.SayUnit} [ {NumberToTextEN.toWords(totalCtns).Trim().ToUpper()} {viewModel.SayUnit} OF {string.Join(" AND ", comodities)}]";

            for (int i = 8; i < grandTotalIndex; i++)
            {
                if (sheet.Row(i).Height != 25)
                {
                    sheet.Row(i).Height = 16;
                }
            }

            #endregion

            #region Mark
            var shippingMarkIndex = spellingWordIndex + 2;
            var sideMarkIndex     = spellingWordIndex + 2;

            sheet.Cells[$"A{shippingMarkIndex}"].Value = "SHIPPING MARKS";
            sheet.Cells[$"A{shippingMarkIndex}:B{shippingMarkIndex}"].Merge = true;
            sheet.Cells[$"A{++shippingMarkIndex}"].Value = viewModel.ShippingMark;

            sheet.Cells[$"F{sideMarkIndex}"].Value = "SIDE MARKS";
            sheet.Cells[$"F{sideMarkIndex}:G{sideMarkIndex}"].Merge = true;
            sheet.Cells[$"F{++sideMarkIndex}"].Value = viewModel.SideMark;

            byte[] shippingMarkImage;
            if (!String.IsNullOrEmpty(viewModel.ShippingMarkImageFile))
            {
                if (IsBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile)))
                {
                    shippingMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.ShippingMarkImageFile));
                    Image shipMarkImage          = byteArrayToImage(shippingMarkImage);
                    var   imageShippingMarkIndex = shippingMarkIndex + 1;

                    ExcelPicture excelPictureShipMarkImage = sheet.Drawings.AddPicture("ShippingMarkImage", shipMarkImage);
                    excelPictureShipMarkImage.From.Column = 0;
                    excelPictureShipMarkImage.From.Row    = imageShippingMarkIndex;
                    excelPictureShipMarkImage.SetSize(200, 200);
                }
            }

            byte[] sideMarkImage;
            if (!String.IsNullOrEmpty(viewModel.SideMarkImageFile))
            {
                if (IsBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile)))
                {
                    sideMarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.SideMarkImageFile));
                    Image _sideMarkImage     = byteArrayToImage(sideMarkImage);
                    var   sideMarkImageIndex = sideMarkIndex + 1;

                    ExcelPicture excelPictureSideMarkImage = sheet.Drawings.AddPicture("SideMarkImage", _sideMarkImage);
                    excelPictureSideMarkImage.From.Column = 5;
                    excelPictureSideMarkImage.From.Row    = sideMarkImageIndex;
                    excelPictureSideMarkImage.SetSize(200, 200);
                }
            }

            #endregion

            #region Measurement

            var grossWeightIndex  = shippingMarkIndex + 18;
            var netWeightIndex    = grossWeightIndex + 1;
            var netNetWeightIndex = netWeightIndex + 1;
            var measurementIndex  = netNetWeightIndex + 1;

            sheet.Cells[$"A{grossWeightIndex}"].Value = "GROSS WEIGHT";
            sheet.Cells[$"A{grossWeightIndex}:B{grossWeightIndex}"].Merge = true;
            sheet.Cells[$"C{grossWeightIndex}"].Value = viewModel.GrossWeight + " KGS";

            sheet.Cells[$"A{netWeightIndex}"].Value = "NET WEIGHT";
            sheet.Cells[$"A{netWeightIndex}:B{netWeightIndex}"].Merge = true;
            sheet.Cells[$"C{netWeightIndex}"].Value = viewModel.NettWeight + " KGS";

            sheet.Cells[$"A{netNetWeightIndex}"].Value = "NET NET WEIGHT";
            sheet.Cells[$"A{netNetWeightIndex}:B{netNetWeightIndex}"].Merge = true;
            sheet.Cells[$"C{netNetWeightIndex}"].Value = viewModel.NetNetWeight + " KGS";

            sheet.Cells[$"A{measurementIndex}"].Value = "MEASUREMENT";
            sheet.Cells[$"A{measurementIndex}:B{measurementIndex}"].Merge = true;

            decimal totalCbm = 0;
            foreach (var measurement in viewModel.Measurements)
            {
                sheet.Cells[$"C{measurementIndex}"].Value = measurement.Length + " X ";
                sheet.Cells[$"D{measurementIndex}"].Value = measurement.Width + " X ";
                sheet.Cells[$"E{measurementIndex}"].Value = measurement.Height + " X ";
                sheet.Cells[$"E{measurementIndex}:G{measurementIndex}"].Merge = true;
                sheet.Cells[$"H{measurementIndex}"].Value = measurement.CartonsQuantity + " CTNS";
                sheet.Cells[$"H{measurementIndex}:I{measurementIndex}"].Merge = true;

                sheet.Cells[$"J{measurementIndex}"].Value = "=";


                var cbm = (decimal)measurement.Length * (decimal)measurement.Width * (decimal)measurement.Height * (decimal)measurement.CartonsQuantity / 1000000;
                totalCbm += cbm;
                sheet.Cells[$"K{measurementIndex}"].Value = string.Format("{0:N2} CBM", cbm);
                sheet.Cells[$"K{measurementIndex}:M{measurementIndex}"].Merge = true;

                measurementIndex++;
            }
            var totalMeasurementIndex = measurementIndex;
            sheet.Cells[$"C{totalMeasurementIndex}:K{totalMeasurementIndex}"].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
            sheet.Cells[$"D{totalMeasurementIndex}"].Value = "TOTAL";
            sheet.Cells[$"D{totalMeasurementIndex}:G{totalMeasurementIndex}"].Merge = true;
            sheet.Cells[$"H{totalMeasurementIndex}"].Value = viewModel.Measurements.Sum(m => m.CartonsQuantity) + " CTNS .";
            sheet.Cells[$"H{totalMeasurementIndex}:I{totalMeasurementIndex}"].Merge = true;
            sheet.Cells[$"K{totalMeasurementIndex}"].Value = string.Format("{0:N2} CBM", totalCbm);
            sheet.Cells[$"J{totalMeasurementIndex}"].Value = "=";
            sheet.Cells[$"K{totalMeasurementIndex}:L{totalMeasurementIndex}"].Merge = true;


            #endregion

            #region remark
            var remarkIndex = totalMeasurementIndex + 1;
            sheet.Cells[$"A{remarkIndex}"].Value   = "REMARK";
            sheet.Cells[$"A{++remarkIndex}"].Value = viewModel.Remark;

            byte[] remarkImage;
            var    remarkImageIndex = remarkIndex + 1;
            if (!String.IsNullOrEmpty(viewModel.RemarkImageFile))
            {
                if (IsBase64String(Base64.GetBase64File(viewModel.RemarkImageFile)))
                {
                    remarkImage = Convert.FromBase64String(Base64.GetBase64File(viewModel.RemarkImageFile));
                    Image        _remarkImage            = byteArrayToImage(remarkImage);
                    ExcelPicture excelPictureRemarkImage = sheet.Drawings.AddPicture("RemarkImage", _remarkImage);
                    excelPictureRemarkImage.From.Column = 0;
                    excelPictureRemarkImage.From.Row    = remarkImageIndex;
                    excelPictureRemarkImage.SetSize(200, 200);
                }
            }


            #endregion

            #region Signature
            var signatureIndex = remarkImageIndex + 14;
            sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true;
            sheet.Cells[$"{colCtns}{signatureIndex}"].Value = "( MRS.ADRIYANA DAMAYANTI )";
            sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            sheet.Cells[$"{colCtns}{++signatureIndex}"].Value = "AUTHORIZED SIGNATURE";
            sheet.Cells[$"{colCtns}{signatureIndex}:{colNnw}{signatureIndex}"].Merge = true;
            sheet.Cells[$"{colCtns}{signatureIndex}"].Style.HorizontalAlignment      = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
            #endregion

            sheet.Cells.Style.Font.SetFromFont(new Font("Tahoma", 7, FontStyle.Regular));
            //sheet.Cells[sheet.Dimension.Address].AutoFitColumns(15, 40);
            sheet.Cells.Style.WrapText = true;

            sheet.PrinterSettings.LeftMargin  = 0.39M;
            sheet.PrinterSettings.TopMargin   = 0;
            sheet.PrinterSettings.RightMargin = 0;

            sheet.PrinterSettings.Orientation = sizesCount ? eOrientation.Landscape : eOrientation.Portrait;


            MemoryStream stream = new MemoryStream();
            package.DoAdjustDrawings = false;
            package.SaveAs(stream);
            return(stream);
        }