/// <summary>
/// Generate all inspection reports
/// </summary>
/// <param name="templatePath">Path of template</param>
/// <param name="itemsource">Item source</param>
/// <returns>MemoryStream</returns>
public static MemoryStream GenerateAllInspectionReports(ExportInspectionReportsModel itemsource,string logoPath)
{
MemoryStream ms = new MemoryStream();
using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms,SpreadsheetDocumentType.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();
int sheetId = 1;
Sheets sheets = new Sheets();
if (itemsource.IsSelectedServiceOrder)
{
//Generate service order report
GenerateServiceOrder(itemsource, workbookPart, sheets,sheetId,logoPath);
sheetId++;
}
//Generate inspection reports
GenerateAllInspectionReports(itemsource, workbookPart, sheets, sheetId, logoPath);
//add the new sheet to the report
workbook.Append(sheets);
//save all report
workbook.Save();
//close the stream.
document.Close();
}
return ms;
}
/// <summary>
/// Get the information of all inspection reports in the service order
/// </summary>
/// <returns>ExportInspectionReportsModel</returns>
public static ExportInspectionReportsModel SearchInspectionReportsByServiceOrder(ParameterSearchAllInspectionReport parameters)
{
ExportInspectionReportsModel result = new ExportInspectionReportsModel();
using (VestalisEntities context = new VestalisEntities())
{
//Get the list of inspection reports by service order
var inspectionReports = (from inspectionRep in context.InspectionReports
join serviceOrd in context.ServiceOrders on inspectionRep.ServiceOrderId equals serviceOrd.ServiceOrderId
where inspectionRep.IsDeleted == false && serviceOrd.IsDeleted == false &&
serviceOrd.ServiceOrderId == parameters.ServiceOrderId && parameters.SelectedReports.Contains(inspectionRep.FormName)
orderby inspectionRep.FormOrder
select new
{
inspectionRep.FormName,
inspectionRep.FormOrder
}).ToList();
bool isClient = Roles.IsUserInRole(parameters.UserName, "Client");
List<string> rolesForUser = Roles.GetRolesForUser(parameters.UserName).ToList();
foreach (var item in inspectionReports)
{
DynamicDataGrid gridColumns = InspectionReportBusiness.GetInspectionReportGridDefinition(parameters.BusinessApplicationId, isClient, item.FormName);
//set the parameters
ParameterSearchInspectionReport parameterSearchInspectionReport = new ParameterSearchInspectionReport
{
BusinessApplicationId = parameters.BusinessApplicationId,
Collection = new FormCollection(),
InspectionReportName = item.FormName,
ServiceOrderId = parameters.ServiceOrderId,
UserName = parameters.UserName,
RolesForUser = rolesForUser,
PageSize = 0,
SelectedPage = 0,
IsClient = isClient,
IsExport = true,
Captions = gridColumns.Captions
};
//get the information of the current inspection report
DynamicDataGrid model = InspectionReportBusiness.SearchInspectionReportList(parameterSearchInspectionReport);
result.InspectionReports.Add(item.FormName, model);
}
result.ServiceOrderData = GetServiceOrderForm(parameters.BusinessApplicationId, parameters.ServiceOrderId);
if (parameters.IsSelectedServiceOrder)
{
result.IsSelectedServiceOrder = true;
result.ServiceOrderSheetName = parameters.ServiceOrderReportName;
}
}
return result;
}
/// <summary>
/// Generate service order report
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateServiceOrder(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets,int sheetId,string logoPath)
{
if (itemsource.IsSelectedServiceOrder)
{
// Remove the sheet reference from the workbook.
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
// The SheetData object will contain all the data.
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet();
Form serviceOrder = itemsource.ServiceOrderData;
Row rowTitle;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[9];
for (int n = 0; n < 9; 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 < 9; cellval++)
{
AppendTextCell(excelColumnNamesTitle[cellval] + i, string.Empty, rowTitle, 3);
}
sheetData.Append(rowTitle);
}
MergeCells mergeCells = new MergeCells();
Row currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)2);
//add the business application name
UpdateStringCellValue("B2", itemsource.BusinessApplicationName, currentRowTitle, 5);
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:E2";
mergeCells.Append(mergeCell);
currentRowTitle = sheetData.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", itemsource.ServiceOrderSheetName, currentRowTitle, 5);
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:E4";
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
Columns columns = new Columns();
columns.Append(CreateColumnData((UInt32Value)(uint)1, (UInt32Value)(uint)1, 26));
columns.Append(CreateColumnData((UInt32Value)(uint)2, (UInt32Value)(uint)2, 73));
worksheet.Append(columns);
int rowIndex = 8;
Row sectionRow;
foreach (var section in serviceOrder.Sections)
{
sectionRow = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
mergeCell = new MergeCell();
mergeCell.Reference = "A" + rowIndex + ":B" + rowIndex;
mergeCells.Append(mergeCell);
AppendTextCell("A" + rowIndex, section.Caption, sectionRow, 6);
AppendTextCell("B" + rowIndex, string.Empty, sectionRow, 6);
sheetData.Append(sectionRow);
foreach (var element in section.FormElements)
{
rowIndex++;
//The current row is obtained for updating the value of the cell
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
switch (element.Field.FieldType)
{
case FieldType.Catalogue:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
if (!string.IsNullOrEmpty(element.Field.FieldValue))
{
string catalogueValue = CatalogueBusiness.GetCatalogueValue(new Guid(element.Field.FieldValue)).CatalogueValueData;
AppendTextCell("B" + rowIndex.ToString(), catalogueValue, rowData, 1);
}
else
{
AppendTextCell("B" + rowIndex.ToString(), string.Empty, rowData, 1);
}
break;
case FieldType.RegularExpressionText:
case FieldType.Time:
case FieldType.SingleTextLine:
case FieldType.MultipleTextLine:
case FieldType.Datepicker:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
case FieldType.Boolean:
string boolValue = element.Field.FieldValue == "True" ? LanguageResource.Yes : LanguageResource.No;
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendTextCell("B" + rowIndex.ToString(), boolValue, rowData, 1);
break;
case FieldType.Integer:
case FieldType.Decimal:
AppendTextCell("A" + rowIndex.ToString(), element.Field.Caption, rowData, 1);
AppendNumberCell("B" + rowIndex.ToString(), element.Field.FieldValue, rowData, 1);
break;
default:
break;
}
sheetData.Append(rowData);
}
rowIndex+=2;
}
worksheet.Append(sheetData);
//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 = itemsource.ServiceOrderSheetName, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
/// <summary>
/// Generate inspection reports
/// </summary>
/// <param name="itemsource">Item source</param>
/// <param name="workbookPart">Worbook part</param>
private static void GenerateAllInspectionReports(ExportInspectionReportsModel itemsource, WorkbookPart workbookPart, Sheets sheets, int sheetId, string logoPath)
{
// Loop through each of the DataTables in our DataSet, and create a new Excel Worksheet for each.
foreach (var item in itemsource.InspectionReports)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
Worksheet worksheet = new Worksheet();
SheetData sheetData1 = new SheetData();
//get the number of columns in the report
Row rowTitle;
int numberOfColumnsCaption = item.Value.Captions.Count;
//get the string name of the columns
string[] excelColumnNamesTitle = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; 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 < numberOfColumnsCaption; 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", item.Value.BusinessApplicationName, currentRowTitle, 5);
string lastColumnName = excelColumnNamesTitle.Last() + "2";
//merge all cells in the title
MergeCell mergeCell = new MergeCell();
mergeCell.Reference = "B2:" + lastColumnName;
mergeCells.Append(mergeCell);
currentRowTitle = sheetData1.Elements<Row>().FirstOrDefault(row => row.RowIndex.Value == (uint)4);
//add the form name
UpdateStringCellValue("B4", item.Key, currentRowTitle, 5);
lastColumnName = lastColumnName.Replace("2", "4");
//merge all cell in the form name
mergeCell = new MergeCell();
mergeCell.Reference = "B4:" + lastColumnName;
mergeCells.Append(mergeCell);
Drawing drawing = AddLogo(logoPath, worksheetPart);
int rowIndex = 7;
//get the names of the columns
string[] excelColumnNamesCaptions = new string[numberOfColumnsCaption];
for (int n = 0; n < numberOfColumnsCaption; n++)
excelColumnNamesCaptions[n] = GetExcelColumnName(n);
Row rowCaption = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
//build column names of the report
Columns columns = new Columns();
for (int i = 0; i < item.Value.Captions.Count; i++)
{
var caption = item.Value.Captions[i];
AppendTextCell(excelColumnNamesCaptions[i] + rowIndex.ToString(), caption.Caption, rowCaption, 2);
columns.Append(CreateColumnData((UInt32Value)(uint)i + 1, (UInt32Value)(uint)i + 1, caption.ExcelColumnWidth));
}
sheetData1.Append(rowCaption);
//add the new row with the name of the columns
worksheet.Append(columns);
rowIndex = 8;
//write the data of the report
foreach (var row in item.Value.DataRows)
{
int numberOfColumnsData = row.FieldValues.Count;
//get column names
string[] excelColumnNamesData = new string[numberOfColumnsData];
for (int n = 0; n < numberOfColumnsData; n++)
excelColumnNamesData[n] = GetExcelColumnName(n);
//build the row
Row rowData = new Row() { RowIndex = (UInt32Value)(uint)rowIndex };
for (int colInx = 0; colInx < numberOfColumnsData; colInx++)
{
DynamicDataRowValue col = row.FieldValues[colInx];
switch (col.FieldType)
{
case (int)FieldType.Catalogue:
case (int)FieldType.RegularExpressionText:
case (int)FieldType.Time:
case (int)FieldType.SingleTextLine:
case (int)FieldType.MultipleTextLine:
case (int)FieldType.Datepicker:
case (int)FieldType.Boolean:
case (int)FieldType.AutoComplete:
case (int)FieldType.StatusField:
AppendTextCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
case (int)FieldType.Integer:
case (int)FieldType.Decimal:
AppendNumberCell(excelColumnNamesData[colInx] + rowIndex.ToString(), col.FieldValue, rowData, 1);
break;
default:
break;
}
}
//add the new row to the report
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 = item.Key, SheetId = (UInt32Value)(uint)sheetId, Id = workbookPart.GetIdOfPart(worksheetPart) };
sheets.Append(sheet);
sheetId++;
}
}
