/// <summary>
/// Search the catalogues for exporting to excel
/// </summary>
/// <param name="businessApplicatioId">Id of business application</param>
/// <param name="catalogueSelectedName">Name of the catalogue</param>
/// <param name="catalogueId">Id of the catalogue</param>
public void SearchCatalogueValuesExcel(Guid? businessApplicatioId, string catalogueSelectedName, Guid? catalogueId)
{
CatalogueValueSearchModel model = new CatalogueValueSearchModel();
PaginatedList<CatalogueValue> searchResult = new PaginatedList<CatalogueValue>();
searchResult = CatalogueBusiness.SearchCatalogueValueList(catalogueId.GetValueOrDefault(), 0, SortDirection.Ascending, "CatalogueValueData", 0, true);
string businessApplicatioName = string.Empty;
businessApplicatioName = AuthorizationBusiness.GetAllBusinessAplications()
.First(data => data.BusinessApplicationId == businessApplicatioId.GetValueOrDefault()).BusinessApplicationName;
model.CatalogueSelectedId = catalogueId.GetValueOrDefault();
model.SearchResult = searchResult;
model.CatalogueSelectedName = CatalogueBusiness.GetCatalogueCategory(catalogueId.Value).CatalogueCategoryName;
model.BusinessApplicatioName = businessApplicatioName;
Session.Add("SearchCatalogueValuesExcel", model);
}
/// <summary>
/// Generate the report for catalogue categories
/// </summary>
/// <param name="templatePath">Path of the template</param>
/// <param name="itemSource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateCatalogueValueReport(CatalogueValueSearchModel itemSource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
//create the new workbook
WorkbookPart workbookPart = document.AddWorkbookPart();
Workbook workbook = new Workbook();
workbookPart.Workbook = workbook;
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
//get and save the stylesheet
Stylesheet stylesheet = VestalisStyleSheet();
workbookStylesPart.Stylesheet = stylesheet;
workbookStylesPart.Stylesheet.Save();
//add the new workseet
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
Sheets sheets = new Sheets();
//get the number of columns in the report
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[2];
for (int n = 0; n < 2; n++)
excelColumnNamesTitle[n] = GetExcelColumnName(n);
//build the title
for (int i = 1; i <= 6; i++)
{
rowTitle = new Row() { RowIndex = (UInt32Value)(uint)i };
for (int cellval = 0; cellval < 2; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData1.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", LanguageResource.CatalogueValuesReport, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:B4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 50));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 71));
worksheet.Append(columns);
int rowIndex = 8;
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.BusinessApplicationName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.BusinessApplicatioName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 9;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex, LanguageResource.CatalogueName, rowData, 2);
AppendTextCell("B" + rowIndex, itemSource.CatalogueSelectedName, rowData, 1);
sheetData1.Append(rowData);
rowIndex = 11;
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), LanguageResource.Value, rowData, 2);
AppendTextCell("B" + rowIndex.ToString(), LanguageResource.Description, rowData, 2);
sheetData1.Append(rowData);
rowIndex = 12;
foreach (var item in itemSource.SearchResult.Collection)
{
rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
AppendTextCell("A" + rowIndex.ToString(), item.CatalogueValueData, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), item.CatalogueValueDescription, rowData, 1);
sheetData1.Append(rowData);
rowIndex++;
}
//add the information of the current sheet
worksheet.Append(sheetData1);
//add merged cells
worksheet.InsertAfter(mergeCells, worksheet.Elements<SheetData>().First());
worksheet.Append(drawing);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
//create the new sheet for this report
Sheet sheet = new Sheet() { Name = LanguageResource.Report, SheetId = (UInt32Value)1, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
