/// <summary>
        /// Generates the table.
        /// </summary>
        /// <param name="dataTable">The data table.</param>
        /// <param name="reportName">Name of the report.</param>
        /// <param name="reportTitle">The report title.</param>
        private void GenerateTable(DataTable dataTable, string reportName, string reportTitle)
        {
            int i = 0, j = 0, k = 0;
            string data = null;
            using (var workbook = new XLWorkbook())
            {
                var ws = workbook.Worksheets.Add(reportName);

                for (i = 0; i < dataTable.Columns.Count; i++)
                {
                    data = dataTable.Columns[i].ToString();
                    if (data != "Column1")
                    {
                        ws.Cell(2, k + 1).Value = data;
                        ws.Cell(2, k + 1).Style.Font.Bold = true;
                        ws.Cell(2, k + 1).Style.Font.FontColor = XLColor.Black;
                        ws.Cell(2, k + 1).Style.Font.FontSize = 12;
                    }

                    ws.Cell(2, k + 1).Style.Fill.SetBackgroundColor(XLColor.LightGray);
                    k++;
                }

                var lastColumn = (char)(65 + dataTable.Columns.Count - 1);
                ws.Range("a1", lastColumn + "1").Merge();

                var dataRange = ws.Range("a1", lastColumn + "1");
                dataRange.Value = reportTitle;

                dataRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                dataRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                dataRange.Style.Font.FontColor = XLColor.Black;
                dataRange.Style.Font.FontSize = 23;
                ws.FirstRow().Height = 60;
                for (i = 0; i <= dataTable.Rows.Count - 1; i++)
                {
                    var newj = 0;
                    for (j = 0; j <= dataTable.Columns.Count - 1; j++)
                    {
                        data = dataTable.Rows[i].ItemArray[j].ToString();

                        if (dataTable.Rows[i].ItemArray[j].ToString().ToUpperInvariant().Contains("HTTP"))
                        {
                            var picture = new XLPicture
                            {
                                ImageStream = GetStreamFromUrl(data),
                                MaxHeight = 80,
                                MaxWidth = 70,
                                PaddingY = 5
                            };
                            XLMarker excelMarker = new XLMarker
                            {
                                ColumnId = newj + 1,
                                RowId = i + 3
                            };
                            ws.Row(i + 3).Height = 70;

                            picture.AddMarker(excelMarker);
                            ws.AddPicture(picture);
                        }
                        else
                        {
                            if (data.Contains("/") || data.Contains("+"))
                            {
                                ws.Cell(i + 3, newj + 1).SetValue(data);
                            }
                            else
                            {
                                ws.Cell(i + 3, newj + 1).Value = data;
                            }
                        }

                        ws.Cell(i + 3, newj + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        newj++;
                    }
                }

                ws.Columns().AdjustToContents();

                var workBookName = this.server.UrlEncode(reportName + "Report-" + BaseController.GenerateLocalDateTime(DateTime.UtcNow).ToString("MM-dd-yyyy", CultureInfo.InvariantCulture) + ".xlsx");

                MemoryStream stream = (MemoryStream)GetStream(workbook);
                this.ExportPreparedExcel(workBookName, stream);
            }
        }
        /// <summary>
        /// Generates the guest reconciliation table.
        /// </summary>
        /// <param name="dataSet">The data set.</param>
        /// <param name="reportName">Name of the report.</param>
        /// <param name="reportTitle">The report title.</param>
        /// <param name="voyageIds">The voyage ids.</param>
        private void GenerateGuestReconciliationTable(DataSet dataSet, string reportName, string reportTitle, string voyageIds)
        {
            using (var workbook = new XLWorkbook())
            {
                var ws = workbook.Worksheets.Add(reportName);

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    int i = 0, j = 0, k = 0;
                    string data = null;

                    char lastColumn = (char)(65 + dataSet.Tables[1].Columns.Count - 1);

                    ws.Cell(3, k + 1).Value = "Embarkation Statistics For " + voyageIds;
                    ws.Cell("A3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    ws.Cell("A3").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    ws.Cell("A3").Style.Font.Bold = true;
                    ws.Cell("A3").Style.Font.FontSize = 13;
                    ws.Range("A3:D3").Merge();

                    for (i = 0; i < dataSet.Tables[0].Columns.Count; i++)
                    {
                        data = dataSet.Tables[0].Columns[i].ToString();
                        ws.Cell(4, k + 1).Value = data;
                        ws.Cell(4, k + 1).Style.Font.Bold = true;
                        ws.Cell(4, k + 1).Style.Font.FontColor = XLColor.Black;
                        ws.Cell(4, k + 1).Style.Font.FontSize = 12;
                        ws.Cell(4, k + 1).Style.Fill.SetBackgroundColor(XLColor.LightGray);
                        k++;
                    }

                    ws.Range("a1", lastColumn + "1").Merge();

                    var dataRange = ws.Range("a1", lastColumn + "1");
                    dataRange.Value = reportTitle;

                    dataRange.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    dataRange.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    dataRange.Style.Font.FontColor = XLColor.Black;
                    dataRange.Style.Font.FontSize = 23;
                    ws.FirstRow().Height = 60;

                    for (i = 0; i <= dataSet.Tables[0].Rows.Count - 1; i++)
                    {
                        var newj = 0;
                        for (j = 0; j <= dataSet.Tables[0].Columns.Count - 1; j++)
                        {
                            data = dataSet.Tables[0].Rows[i].ItemArray[j].ToString();
                            ws.Cell(i + 5, newj + 1).Value = data;
                            ws.Cell(i + 5, newj + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                            newj++;
                        }
                    }
                }

                if (dataSet.Tables[1].Columns.Count > 0)
                {
                    int i = 0, j = 0, k = 0;
                    string data = null;

                    ws.Cell(8, k + 1).Value = "Onboard Not Checked In [" + dataSet.Tables[1].Rows.Count + "]";
                    ws.Cell("A8").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    ws.Cell("A8").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    ws.Cell("A8").Style.Font.Bold = true;
                    ws.Cell("A8").Style.Font.FontSize = 14;
                    ws.Range("A8:H8").Merge();

                    for (i = 0; i < dataSet.Tables[1].Columns.Count; i++)
                    {
                        data = dataSet.Tables[1].Columns[i].ToString();
                        ws.Cell(9, k + 1).Value = data;
                        ws.Cell(9, k + 1).Style.Font.Bold = true;
                        ws.Cell(9, k + 1).Style.Font.FontColor = XLColor.Black;
                        ws.Cell(9, k + 1).Style.Font.FontSize = 12;
                        ws.Cell(9, k + 1).Style.Fill.SetBackgroundColor(XLColor.LightGray);
                        k++;
                    }

                    if (dataSet.Tables[1].Rows.Count > 0)
                    {
                        for (i = 0; i <= dataSet.Tables[1].Rows.Count - 1; i++)
                        {
                            var newj = 0;
                            for (j = 0; j <= dataSet.Tables[1].Columns.Count - 1; j++)
                            {
                                data = dataSet.Tables[1].Rows[i].ItemArray[j].ToString();

                                if (dataSet.Tables[1].Rows[i].ItemArray[j].ToString().ToUpperInvariant().Contains("HTTP"))
                                {
                                    var picture = new XLPicture
                                    {
                                        ImageStream = GetStreamFromUrl(data),
                                        MaxHeight = 80,
                                        MaxWidth = 70,
                                        PaddingY = 5
                                    };
                                    XLMarker excelMarker = new XLMarker
                                    {
                                        ColumnId = newj + 1,
                                        RowId = i + 10
                                    };
                                    ws.Row(i + 10).Height = 70;

                                    picture.AddMarker(excelMarker);
                                    ws.AddPicture(picture);
                                }
                                else
                                {
                                    ws.Cell(i + 10, newj + 1).Value = data;
                                }

                                ws.Cell(i + 10, newj + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                                newj++;
                            }
                        }
                    }
                    else
                    {
                        ws.Cell(10, 1).Value = "No record found.";
                        ws.Cell("A10").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        ws.Cell("A10").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        ws.Cell("A10").Style.Font.Bold = true;
                        ws.Cell("A10").Style.Font.FontSize = 12;
                        ws.Range("A10:H10").Merge();
                    }
                }

                if (dataSet.Tables[2].Columns.Count > 0)
                {
                    int i = 0, j = 0, k = 0;
                    string data = null;
                    int tableFirstRow = dataSet.Tables[1].Rows.Count + 11;
                    string headerRow = "A" + tableFirstRow;
                    string headerColumn = "H" + tableFirstRow;
                    int cellHeader = tableFirstRow + 1;
                    int cellDataIndex = cellHeader + 1;
                    ws.Cell(tableFirstRow, k + 1).Value = "Checked-in But Not Onboard [" + dataSet.Tables[2].Rows.Count + "]";
                    ws.Cell(headerRow).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                    ws.Cell(headerRow).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                    ws.Cell(headerRow).Style.Font.Bold = true;
                    ws.Cell(headerRow).Style.Font.FontSize = 14;
                    ws.Range(headerRow + ":" + headerColumn).Merge();

                    for (i = 0; i < dataSet.Tables[2].Columns.Count; i++)
                    {
                        data = dataSet.Tables[2].Columns[i].ToString();
                        ws.Cell(cellHeader, k + 1).Value = data;
                        ws.Cell(cellHeader, k + 1).Style.Font.Bold = true;
                        ws.Cell(cellHeader, k + 1).Style.Font.FontColor = XLColor.Black;
                        ws.Cell(cellHeader, k + 1).Style.Font.FontSize = 12;
                        ws.Cell(cellHeader, k + 1).Style.Fill.SetBackgroundColor(XLColor.LightGray);
                        k++;
                    }

                    if (dataSet.Tables[2].Rows.Count > 0)
                    {
                        for (i = 0; i <= dataSet.Tables[2].Rows.Count - 1; i++)
                        {
                            var newj = 0;
                            for (j = 0; j <= dataSet.Tables[2].Columns.Count - 1; j++)
                            {
                                data = dataSet.Tables[2].Rows[i].ItemArray[j].ToString();

                                if (dataSet.Tables[2].Rows[i].ItemArray[j].ToString().ToUpperInvariant().Contains("HTTP"))
                                {
                                    var picture = new XLPicture
                                    {
                                        ImageStream = GetStreamFromUrl(data),
                                        MaxHeight = 80,
                                        MaxWidth = 70,
                                        PaddingY = 5
                                    };
                                    XLMarker excelMarker = new XLMarker
                                    {
                                        ColumnId = newj + 1,
                                        RowId = i + cellDataIndex
                                    };
                                    ws.Row(i + cellDataIndex).Height = 70;

                                    picture.AddMarker(excelMarker);
                                    ws.AddPicture(picture);
                                }
                                else
                                {
                                    ws.Cell(i + cellDataIndex, newj + 1).Value = data;
                                }

                                ws.Cell(i + cellDataIndex, newj + 1).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                                newj++;
                            }
                        }
                    }
                    else
                    {
                        ws.Cell(cellDataIndex, 1).Value = "No record found.";
                        ws.Cell("A" + cellDataIndex).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        ws.Cell("A" + cellDataIndex).Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        ws.Cell("A" + cellDataIndex).Style.Font.Bold = true;
                        ws.Cell("A" + cellDataIndex).Style.Font.FontSize = 12;
                        ws.Range("A" + cellDataIndex + ":" + "H" + cellDataIndex).Merge();
                    }
                }

                ws.Columns().AdjustToContents();

                var workBookName = this.server.UrlEncode(reportName + "Report-" + BaseController.GenerateLocalDateTime(DateTime.UtcNow).ToString(DateFormatConst, CultureInfo.InvariantCulture) + ".xlsx");

                MemoryStream stream = (MemoryStream)GetStream(workbook);
                this.ExportPreparedExcel(workBookName, stream);
            }
        }
Beispiel #3
-1
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var worksheet = workbook.Worksheets.Add("Sample Sheet");

            var filepath = Path.GetFullPath(
                Path.Combine("..", "..", "Pictures", "test.jpg"));

            var picture = new XLPicture()
            {
                FilePath = filepath,
                WidthPx = 100,
                HeightPx = 100,
                Type = XLPictureType.Jpeg,
                CanUserChangeAspect = false,
            };
            picture.SetMarker(new XLMarker()
            {
                RowIndex = 12,
                ColumnIndex = 13,
                RowOffsetPx = 5,
                ColumnOffsetPx = 10,
            });
            worksheet.Pictures.Add(picture);

            var picture1 = new XLPicture()
            {
                FilePath = filepath,
                Type = XLPictureType.Jpeg,
            };
            picture1.SetMarker(
                new XLMarker()
                {
                    RowIndex = 14,
                    ColumnIndex = 15,
                    RowOffsetPx = 5,
                    ColumnOffsetPx = 10,
                },
                new XLMarker()
                {
                    RowIndex = 18,
                    ColumnIndex = 19,
                });
            worksheet.Pictures.Add(picture1);

            var picture2 = new XLPicture()
            {
                FilePath = filepath,
                Type = XLPictureType.Jpeg,
            };
            worksheet.Pictures.Add(picture2);

            workbook.SaveAs(filePath);
        }