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