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