/// <summary>
///
/// </summary>
/// <param name="excelWorksheet"></param>
/// <param name="sheetFormat"></param>
/// <param name="selectedAreaJsonArray"></param>
/// <returns></returns>
private List <String> GetExcelHeaderFields(ExcelWorksheet excelWorksheet, SheetFormat sheetFormat, string selectedAreaJsonArray)
{
List <String> headerValues = new List <string>();
int[] areaValues = JsonConvert.DeserializeObject <int[]>(selectedAreaJsonArray);
if (areaValues.Length != 4)
{
throw new InvalidOperationException("Given JSON string for selected area got an invalid length of [" + Convert.ToString(areaValues.Length) + "]");
}
SheetArea selectedArea = new SheetArea(areaValues[1], areaValues[3], areaValues[0], areaValues[2]);
switch (sheetFormat)
{
case SheetFormat.TopDown:
headerValues = GetExcelHeaderFieldsTopDown(excelWorksheet, selectedArea);
break;
case SheetFormat.LeftRight:
headerValues = GetExcelHeaderFieldsLeftRight(excelWorksheet, selectedArea);
break;
case SheetFormat.Matrix:
headerValues.AddRange(GetExcelHeaderFieldsTopDown(excelWorksheet, selectedArea));
headerValues.AddRange(GetExcelHeaderFieldsLeftRight(excelWorksheet, selectedArea));
break;
default:
break;
}
return(headerValues);
}
public ActionResult ChangeWorksheet(string sheetIdentifier)
{
TaskManager = (EasyUploadTaskManager)Session["TaskManager"];
#region Reset selected units, datatypes and suggestions
TaskManager.Bus.Remove(EasyUploadTaskManager.VERIFICATION_AVAILABLEUNITS);
TaskManager.Bus.Remove(EasyUploadTaskManager.VERIFICATION_HEADERFIELDS);
TaskManager.Bus.Remove(EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS);
TaskManager.Bus.Remove(EasyUploadTaskManager.VERIFICATION_ATTRIBUTESUGGESTIONS);
TaskManager.Bus.Remove(EasyUploadTaskManager.ROWS);
#endregion Reset selected units, datatypes and suggestions
#region Generate table for selected sheet
string filePath = TaskManager.Bus[EasyUploadTaskManager.FILEPATH].ToString();
FileStream fis = null;
string jsonTable = "[]";
try
{
//FileStream for the users file
fis = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//Grab the sheet format from the bus
string sheetFormatString = Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_FORMAT]);
SheetFormat CurrentSheetFormat = 0;
Enum.TryParse <SheetFormat>(sheetFormatString, true, out CurrentSheetFormat);
//Transforms the content of the file into a 2d-json-array
JsonTableGenerator EUEReader = new JsonTableGenerator(fis);
jsonTable = EUEReader.GenerateJsonTable(CurrentSheetFormat, sheetIdentifier);
if (!String.IsNullOrEmpty(jsonTable))
{
TaskManager.AddToBus(EasyUploadTaskManager.SHEET_JSON_DATA, jsonTable);
}
TaskManager.AddToBus(EasyUploadTaskManager.ACTIVE_WOKSHEET_URI, sheetIdentifier);
}
catch (Exception ex)
{
LoggerFactory.LogCustom(ex.Message);
}
finally
{
if (fis != null)
{
fis.Close();
}
}
#endregion Generate table for selected sheet
//Send back the table-data
return(Content(jsonTable, "application/json"));
}
private void NewSheetFromSheetFormat()
{
Sheet createdSheet;
string workspacePath = InventorApp.FileLocations.Workspace;
SheetFormat sheetFormatForUse = GetSheetFormat();
string modelForViewsPath = "";
if (sheetFormatForUse.HasDrawingViews)
{
Inventor.FileDialog fileDialog;
this.InventorApp.CreateFileDialog(out fileDialog);
fileDialog.Filter = "Autodesk Inventor Files (*.ipt;*.iam;*.ipn)|"
+ "*.ipt;*.iam;*.ipn|"
+ "Autodesk Inventor Part Files (*.ipt)|*.ipt|"
+ "Autodesk Inventor Assembly Files (*.iam)|*.iam|"
+ "Autodesk Inventor Presentation Files (*.ipn)|*.ipn";
fileDialog.FilterIndex = 1;
fileDialog.DialogTitle = "Choose the Model File";
fileDialog.InitialDirectory = workspacePath;
fileDialog.CancelError = false;
fileDialog.OptionsEnabled = false;
fileDialog.MultiSelectEnabled = false;
fileDialog.SuppressResolutionWarnings = true;
fileDialog.ShowOpen();
modelForViewsPath = fileDialog.FileName;
if ((String.IsNullOrEmpty(modelForViewsPath)))
{
MessageBox.Show("No file selected.");
throw new Exception("No file selected");
}
createdSheet = drawDoc.Sheets.AddUsingSheetFormat(sheetFormatForUse,
Model: modelForViewsPath, SheetName: SheetName);
createdSheet.ExcludeFromCount = ExcludeFromCount;
createdSheet.ExcludeFromPrinting = ExcludeFromPrinting;
createdSheet.Name = (String.IsNullOrEmpty(SheetName)) ? "Sheet" : SheetName;
createdSheet.Orientation = this.GetPageOrientation();
createdSheet.Activate();
}
else
{
createdSheet = drawDoc.Sheets.AddUsingSheetFormat(sheetFormatForUse,
SheetName: SheetName);
createdSheet.ExcludeFromCount = ExcludeFromCount;
createdSheet.ExcludeFromPrinting = ExcludeFromPrinting;
createdSheet.Name = (String.IsNullOrEmpty(SheetName)) ? "Sheet" : SheetName;
createdSheet.Orientation = this.GetPageOrientation();
createdSheet.Activate();
}
}
public void SetSheetToFormatStyle()
{
SheetFormat activeFormat = drawDoc.SheetFormats[1];
foreach (SheetFormat format in drawDoc.SheetFormats)
{
if (format.Name.Equals(SelectedSheetFormat))
{
activeFormat = format;
}
}
SelectedSheetSize = GetSheetSize(activeFormat.Size);
SheetHeight = Convert.ToString(activeFormat.Height);
SheetWidth = Convert.ToString(activeFormat.Width);
SelectedOrientation = GetActiveOrientation(activeFormat.Orientation);
SelectedTitleBlock = activeFormat.ReferencedTitleBlockDefinition.Name;
SelectedTitleBlockPlacement = GetActiveTitleBlockPlacement
(activeFormat.TitleBlockLocation);
SelectedBorder = activeFormat.ReferencedBorderDefinition.Name;
}
public string GenerateJsonTable(SheetFormat sheetFormat, String worksheetUri)
{
// open excel file
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(this.fileStream, false))
{
// get workbookpart
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
_sharedStrings = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ToArray();
_stylesheet = workbookPart.WorkbookStylesPart.Stylesheet;
WorksheetPart worksheetPart = null;
foreach (Sheet worksheet in workbookPart.Workbook.Descendants <Sheet>())
{
//Get the current worksheetpart and see if it is the correct one
WorksheetPart tmp = (WorksheetPart)workbookPart.GetPartById(worksheet.Id);
if (tmp.Uri.ToString() == worksheetUri)
{
//Found the correct WorksheetPart
worksheetPart = tmp;
}
}
using (OpenXmlReader reader = OpenXmlReader.Create(worksheetPart))
{
int expectedRowIndex = 1;
while (reader.Read())
{
if (reader.ElementType == typeof(DocumentFormat.OpenXml.Spreadsheet.Row))
{
do
{
DocumentFormat.OpenXml.Spreadsheet.Row row = (DocumentFormat.OpenXml.Spreadsheet.Row)reader.LoadCurrentElement();
List <String> rowAsStringList = new List <string>();
//Since this library will ignore empty rows, check if we skipped some and add empty rows if necessary
//This will still ignore empty rows at the end of the file but those wouldn't have any influence on the indices of data & header anyway
while (row.RowIndex > expectedRowIndex)
{
List <String> dummyRow = new List <string>();
dummyRow.Add("");
table.Add(dummyRow);
expectedRowIndex++;
}
// create a new cell
Cell c = new Cell();
int expectedIndex = 0; //To check whether we skipped cells because they were empty
for (int i = 0; i < row.ChildElements.Count(); i++)
{
// get current cell at i
c = row.Elements <Cell>().ElementAt(i);
string value = "";
if (c != null)
{
//See if cells have been skipped (empty cells are not contained in the xml and therefore not contained in row.ChildElements)
//See: https://stackoverflow.com/a/3981249
// Gets the column index of the cell with data
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(c.CellReference));
if (expectedIndex < cellColumnIndex)
{
//We skipped one or more cells so add some blank data
do
{
rowAsStringList.Add(""); //Insert blank data
expectedIndex++;
}while (expectedIndex < cellColumnIndex);
}
//We now have the correct index and can grab the value of the cell
if (c.CellValue != null && !string.IsNullOrEmpty(c.CellValue.Text))
{
// if Value a text
if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.SharedString)
{
int sharedStringIndex = int.Parse(c.CellValue.Text, CultureInfo.InvariantCulture);
SharedStringItem sharedStringItem = _sharedStrings[sharedStringIndex];
value = sharedStringItem.InnerText;
}
//If cell contains boolean (doesn't always work for files saved with libre office)
else if (c.DataType != null && c.DataType.HasValue && c.DataType.Value == CellValues.Boolean)
{
if (c.InnerText == "1")
{
value = "true";
}
else
{
value = "false";
}
}
// not a text
else if (c.StyleIndex != null && c.StyleIndex.HasValue)
{
uint styleIndex = c.StyleIndex.Value;
CellFormat cellFormat = _stylesheet.CellFormats.ChildElements[(int)styleIndex] as CellFormat;
if (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
{
uint numberFormatId = cellFormat.NumberFormatId.Value;
NumberingFormat numberFormat = _stylesheet.NumberingFormats?.FirstOrDefault(numFormat => ((NumberingFormat)numFormat).NumberFormatId.Value == numberFormatId) as NumberingFormat;
//
if (numberFormat != null)
{
if (numberFormat != null && numberFormat.FormatCode != null && numberFormat.FormatCode.HasValue)
{
string formatCode = numberFormat.FormatCode.Value;
if ((formatCode.ToLower().Contains("d") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("y")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("d")) ||
(formatCode.ToLower().Contains("h") && formatCode.ToLower().Contains("m")) ||
(formatCode.ToLower().Contains("m") && formatCode.ToLower().Contains("s"))
)
{
DateTime dateTime = DateTime.FromOADate(double.Parse(c.CellValue.Text, CultureInfo.InvariantCulture));
//value = dateTime.ToString(new CultureInfo("en-us"));
//get c# display pattern
DataTypeDisplayPattern dataTypeDisplayPattern = DataTypeDisplayPattern.GetByExcelPattern(formatCode);
value = dataTypeDisplayPattern != null?dateTime.ToString(dataTypeDisplayPattern.StringPattern) : dateTime.ToString(new CultureInfo("en-us"));
//Debug.WriteLine("----");
//Debug.WriteLine(formatCode);
}
}
} // numberformat not null end
} // (cellFormat != null && cellFormat.NumberFormatId != null && cellFormat.NumberFormatId.HasValue)
//It may happen that values are in a cell, but the associated information such as numberformat or style are missing.
// In this case, we decide to display the values, even if they are incorrect.
if (string.IsNullOrEmpty(value) && (!string.IsNullOrEmpty(c?.CellValue?.Text)))
{
value = c.CellValue.Text;
}
}
else
{
value = c.CellValue.Text;
}
rowAsStringList.Add(value);
}//end if cell value null
else
{
rowAsStringList.Add("");
}
}//end if cell null
expectedIndex++;
}//for children of row
//Check if there's a new max length for the length of a row
maxCellCount = Math.Max(maxCellCount, rowAsStringList.Count);
//Just read a row, so increase the expected index for the next one
expectedRowIndex++;
table.Add(rowAsStringList);
} while (reader.ReadNextSibling()); // Skip to the next row
break;
}
}
}
//Make sure each row has the same number of values in it
foreach (List <String> row in table)
{
while (row.Count < maxCellCount)
{
row.Add("");
}
}
//Convert the Lists to Arrays
List <String>[] rowArray = table.ToArray(); //The elements of the Array are the rows in form of String-lists
String[][] tableArray = new String[rowArray.Length][];
for (int i = 0; i < rowArray.Length; i++)
{
tableArray[i] = rowArray[i].ToArray();
}
return(JsonConvert.SerializeObject(tableArray));
}
}
public ActionResult SelectAreas(int index)
{
TaskManager = (EasyUploadTaskManager)Session["TaskManager"];
//set current stepinfo based on index
if (TaskManager != null)
{
TaskManager.SetCurrent(index);
// remove if existing
TaskManager.RemoveExecutedStep(TaskManager.Current());
}
//Use the given file and the given sheet format to create a json-table
string filePath = TaskManager.Bus[EasyUploadTaskManager.FILEPATH].ToString();
FileStream fis = null;
string jsonTable = "[]";
SelectAreasModel model = new SelectAreasModel();
try
{
//FileStream for the users file
fis = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//Grab the sheet format from the bus
string sheetFormatString = Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_FORMAT]);
SheetFormat CurrentSheetFormat = 0;
Enum.TryParse <SheetFormat>(sheetFormatString, true, out CurrentSheetFormat);
//Transforms the content of the file into a 2d-json-array
JsonTableGenerator EUEReader = new JsonTableGenerator(fis);
//If the active worksheet was never changed, we default to the first one
string activeWorksheet;
if (!TaskManager.Bus.ContainsKey(EasyUploadTaskManager.ACTIVE_WOKSHEET_URI))
{
activeWorksheet = EUEReader.GetFirstWorksheetUri().ToString();
TaskManager.AddToBus(EasyUploadTaskManager.ACTIVE_WOKSHEET_URI, activeWorksheet);
}
else
{
activeWorksheet = TaskManager.Bus[EasyUploadTaskManager.ACTIVE_WOKSHEET_URI].ToString();
}
//Generate the table for the active worksheet
jsonTable = EUEReader.GenerateJsonTable(CurrentSheetFormat, activeWorksheet);
//Save the worksheet uris to the model
model.SheetUriDictionary = EUEReader.GetWorksheetUris();
if (!String.IsNullOrEmpty(jsonTable))
{
TaskManager.AddToBus(EasyUploadTaskManager.SHEET_JSON_DATA, jsonTable);
}
//Add uri of the active sheet to the model to be able to preselect the correct option in the dropdown
model.activeSheetUri = activeWorksheet;
}
catch (Exception ex)
{
LoggerFactory.LogCustom(ex.Message);
}
finally
{
if (fis != null)
{
fis.Close();
}
}
// Check if the areas have already been selected, if yes, use them (Important when jumping back to this step)
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_DATA_AREA))
{
model.DataArea = (List <string>)TaskManager.Bus[EasyUploadTaskManager.SHEET_DATA_AREA];
}
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_HEADER_AREA))
{
model.HeaderArea = TaskManager.Bus[EasyUploadTaskManager.SHEET_HEADER_AREA].ToString();
}
model.StepInfo = TaskManager.Current();
return(PartialView(model));
}
public ActionResult SelectAreas(object[] data)
{
TaskManager = (EasyUploadTaskManager)Session["TaskManager"];
SelectAreasModel model = new SelectAreasModel();
model.StepInfo = TaskManager.Current();
if (TaskManager != null)
{
TaskManager.Current().SetValid(false);
//If Header and data areas have been selected, you can proceed with the next step
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_JSON_DATA) &&
TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_DATA_AREA) &&
TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_HEADER_AREA))
{
bool isJsonDataEmpty = String.IsNullOrEmpty(Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_JSON_DATA]));
bool isDataAreaEmpty = ((List <string>)TaskManager.Bus[EasyUploadTaskManager.SHEET_DATA_AREA]).Count == 0;
bool isHeadAreaEmpty = String.IsNullOrEmpty(Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_HEADER_AREA]));
if (!isJsonDataEmpty && !isDataAreaEmpty && !isHeadAreaEmpty)
{
TaskManager.Current().SetValid(true);
}
else
{
model.ErrorList.Add(new Error(ErrorType.Other, "Some Areas are not selected."));
}
}
else //Else stay on the same page, display an error
{
model.ErrorList.Add(new Error(ErrorType.Other, "Some Areas are not selected."));
}
if (TaskManager.Current().valid == true) //Redirect if the state is valid
{
TaskManager.AddExecutedStep(TaskManager.Current());
TaskManager.GoToNext();
Session["TaskManager"] = TaskManager;
ActionInfo actionInfo = TaskManager.Current().GetActionInfo;
return(RedirectToAction(actionInfo.ActionName, actionInfo.ControllerName, new RouteValueDictionary {
{ "area", actionInfo.AreaName }, { "index", TaskManager.GetCurrentStepInfoIndex() }
}));
}
else
{
TaskManager.Current().SetStatus(StepStatus.error);
//reload model
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_DATA_AREA))
{
model.DataArea = (List <string>)TaskManager.Bus[EasyUploadTaskManager.SHEET_DATA_AREA];
}
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_HEADER_AREA))
{
model.HeaderArea = TaskManager.Bus[EasyUploadTaskManager.SHEET_HEADER_AREA].ToString();
}
#region Generate sheet-list and table for active sheet
//Grab the sheet format from the bus
string sheetFormatString = Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_FORMAT]);
SheetFormat CurrentSheetFormat = 0;
Enum.TryParse <SheetFormat>(sheetFormatString, true, out CurrentSheetFormat);
//Open the users file
string filePath = TaskManager.Bus[EasyUploadTaskManager.FILEPATH].ToString();
FileStream fis = null;
string jsonTable = "{}";
fis = new FileStream(filePath, FileMode.Open, FileAccess.Read);
//Generate the Sheet-List and grab the active worksheet
JsonTableGenerator EUEReader = new JsonTableGenerator(fis);
//If the active worksheet was never changed, we default to the first one
string activeWorksheet;
if (!TaskManager.Bus.ContainsKey(EasyUploadTaskManager.ACTIVE_WOKSHEET_URI))
{
activeWorksheet = EUEReader.GetFirstWorksheetUri().ToString();
TaskManager.AddToBus(EasyUploadTaskManager.ACTIVE_WOKSHEET_URI, activeWorksheet);
}
else
{
activeWorksheet = TaskManager.Bus[EasyUploadTaskManager.ACTIVE_WOKSHEET_URI].ToString();
}
//Generate the table for the active worksheet
jsonTable = EUEReader.GenerateJsonTable(CurrentSheetFormat, activeWorksheet);
//Save the worksheet uris to the model
model.SheetUriDictionary = EUEReader.GetWorksheetUris();
if (!String.IsNullOrEmpty(jsonTable))
{
TaskManager.AddToBus(EasyUploadTaskManager.SHEET_JSON_DATA, jsonTable);
}
//Add uri of the active sheet to the model to be able to preselect the correct option in the dropdown
model.activeSheetUri = activeWorksheet;
#endregion
model.StepInfo = TaskManager.Current();
}
}
return(PartialView(model));
}
public ActionResult Verification(int index)
{
TaskManager = (EasyUploadTaskManager)Session["TaskManager"];
List <Dlm.Entities.DataStructure.Unit> tempUnitList = new List <Dlm.Entities.DataStructure.Unit>();
List <DataType> allDataypes = new List <DataType>();
List <DataAttribute> allDataAttributes = new List <DataAttribute>();
using (IUnitOfWork unitOfWork = this.GetUnitOfWork())
{
//set current stepinfo based on index
if (TaskManager != null)
{
TaskManager.SetCurrent(index);
// remove if existing
TaskManager.RemoveExecutedStep(TaskManager.Current());
}
SelectVerificationModel model = new SelectVerificationModel();
List <DataTypeInfo> dataTypeInfos = new List <DataTypeInfo>();
List <UnitInfo> unitInfos = new List <UnitInfo>();
List <DataAttrInfo> dataAttributeInfos = new List <DataAttrInfo>();
List <EasyUploadSuggestion> suggestions = new List <EasyUploadSuggestion>();
List <string> headers = new List <string>();
tempUnitList = unitOfWork.GetReadOnlyRepository <Dlm.Entities.DataStructure.Unit>().Get().ToList();
allDataypes = unitOfWork.GetReadOnlyRepository <DataType>().Get().ToList();
allDataAttributes = unitOfWork.GetReadOnlyRepository <DataAttribute>().Get().ToList();
//Important for jumping back to this step
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.ROWS))
{
model.Rows = (List <RowModel>)TaskManager.Bus[EasyUploadTaskManager.ROWS];
}
// get all DataTypes for each Units
foreach (Dlm.Entities.DataStructure.Unit unit in tempUnitList)
{
UnitInfo unitInfo = new UnitInfo();
unitInfo.UnitId = unit.Id;
unitInfo.Description = unit.Description;
unitInfo.Name = unit.Name;
unitInfo.Abbreviation = unit.Abbreviation;
unitInfo.DimensionId = unit.Dimension.Id;
if (unit.Name.ToLower() == "none")
{
foreach (DataType fullDataType in allDataypes)
{
DataTypeInfo dataTypeInfo = new DataTypeInfo();
dataTypeInfo.DataTypeId = fullDataType.Id;
dataTypeInfo.Description = fullDataType.Description;
dataTypeInfo.Name = fullDataType.Name;
unitInfo.DataTypeInfos.Add(dataTypeInfo);
}
unitInfos.Add(unitInfo);
}
else
{
Boolean hasDatatype = false; //Make sure only units that have at least one datatype are shown
foreach (DataType dummyDataType in unit.AssociatedDataTypes)
{
if (!hasDatatype)
{
hasDatatype = true;
}
DataTypeInfo dataTypeInfo = new DataTypeInfo();
DataType fullDataType = allDataypes.Where(p => p.Id == dummyDataType.Id).FirstOrDefault();
dataTypeInfo.DataTypeId = fullDataType.Id;
dataTypeInfo.Description = fullDataType.Description;
dataTypeInfo.Name = fullDataType.Name;
unitInfo.DataTypeInfos.Add(dataTypeInfo);
}
if (hasDatatype)
{
unitInfos.Add(unitInfo);
}
}
}
//Sort the units by name
unitInfos.Sort(delegate(UnitInfo u1, UnitInfo u2)
{
return(String.Compare(u1.Name, u2.Name, StringComparison.InvariantCultureIgnoreCase));
});
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_AVAILABLEUNITS, unitInfos);
// all datatypesinfos
dataTypeInfos = unitInfos.SelectMany(u => u.DataTypeInfos).GroupBy(d => d.DataTypeId).Select(g => g.Last()).ToList();
TaskManager.AddToBus(EasyUploadTaskManager.ALL_DATATYPES, dataTypeInfos);
//Setall Data AttrInfos to Session -> default
allDataAttributes.ForEach(d => dataAttributeInfos.Add(new DataAttrInfo(d.Id, d.Unit.Id, d.DataType.Id, d.Description, d.Name, d.Unit.Dimension.Id)));
Session["DataAttributes"] = dataAttributeInfos;
string filePath = TaskManager.Bus[EasyUploadTaskManager.FILEPATH].ToString();
string selectedHeaderAreaJson = TaskManager.Bus[EasyUploadTaskManager.SHEET_HEADER_AREA].ToString();
FileStream fis = null;
fis = new FileStream(filePath, FileMode.Open, FileAccess.Read);
using (ExcelPackage ep = new ExcelPackage(fis))
{
fis.Close();
ExcelWorkbook excelWorkbook = ep.Workbook;
ExcelWorksheet firstWorksheet = excelWorkbook.Worksheets[1];
string sheetFormatString = Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_FORMAT]);
SheetFormat sheetFormat = 0;
Enum.TryParse <SheetFormat>(sheetFormatString, true, out sheetFormat);
headers = GetExcelHeaderFields(firstWorksheet, sheetFormat, selectedHeaderAreaJson);
headers = makeHeaderUnique(headers);
suggestions = new List <EasyUploadSuggestion>();
if (!model.Rows.Any())
{
foreach (string varName in headers)
{
#region suggestions
//Add a variable to the suggestions if the names are similar
suggestions = getSuggestions(varName, dataAttributeInfos);
#endregion
//set rowmodel
RowModel row = new RowModel(
headers.IndexOf(varName),
varName,
null,
null,
null,
suggestions,
unitInfos,
dataAttributeInfos,
dataTypeInfos
);
model.Rows.Add(row);
TaskManager.AddToBus(EasyUploadTaskManager.ROWS, model.Rows);
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS, RowsToTuples());
}
}
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS, headers);
model.StepInfo = TaskManager.Current();
return(PartialView(model));
}
}
}
private SheetFormat GetSheetFormat()
{
SheetFormat sheetFormat = drawDoc.SheetFormats[CurrentSheetFormat];
return(sheetFormat);
}
private void CreateNewSheetFormat(Sheet sheetUsed)
{
NewSheetFormatName = (String.IsNullOrEmpty(NewSheetFormatName)) ? "New Sheet Format" :
NewSheetFormatName;
SheetFormat newSheetFormat = drawDoc.SheetFormats.Add(sheetUsed, NewSheetFormatName);
}
public ActionResult Verification(int index)
{
TaskManager = (EasyUploadTaskManager)Session["TaskManager"];
List <Dlm.Entities.DataStructure.Unit> tempUnitList = new List <Dlm.Entities.DataStructure.Unit>();
List <DataType> allDataypes = new List <DataType>();
List <DataAttribute> allDataAttributes = new List <DataAttribute>();
using (IUnitOfWork unitOfWork = this.GetUnitOfWork())
{
//set current stepinfo based on index
if (TaskManager != null)
{
TaskManager.SetCurrent(index);
// remove if existing
TaskManager.RemoveExecutedStep(TaskManager.Current());
}
SelectVerificationModel model = new SelectVerificationModel();
//Grab all necessary managers and lists
//UnitManager unitManager = new UnitManager();
//this.Disposables.Add(unitManager);
//DataTypeManager dataTypeManager = new DataTypeManager();
//this.Disposables.Add(dataTypeManager);
tempUnitList = unitOfWork.GetReadOnlyRepository <Dlm.Entities.DataStructure.Unit>().Get().ToList();
allDataypes = unitOfWork.GetReadOnlyRepository <DataType>().Get().ToList();
allDataAttributes = unitOfWork.GetReadOnlyRepository <DataAttribute>().Get().ToList();
//DataStructureManager dsm = new DataStructureManager();
//this.Disposables.Add(dsm);
//DataContainerManager dam = new DataContainerManager();
//this.Disposables.Add(dam);
//Important for jumping back to this step
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS))
{
model.AssignedHeaderUnits = (List <Tuple <int, string, UnitInfo> >)TaskManager.Bus[EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS];
}
// get all DataTypes for each Units
foreach (Dlm.Entities.DataStructure.Unit unit in tempUnitList)
{
UnitInfo unitInfo = new UnitInfo();
unitInfo.UnitId = unit.Id;
unitInfo.Description = unit.Description;
unitInfo.Name = unit.Name;
unitInfo.Abbreviation = unit.Abbreviation;
if (unit.Name.ToLower() == "none")
{
foreach (DataType fullDataType in allDataypes)
{
DataTypeInfo dataTypeInfo = new DataTypeInfo();
dataTypeInfo.DataTypeId = fullDataType.Id;
dataTypeInfo.Description = fullDataType.Description;
dataTypeInfo.Name = fullDataType.Name;
unitInfo.DataTypeInfos.Add(dataTypeInfo);
}
model.AvailableUnits.Add(unitInfo);
}
else
{
Boolean hasDatatype = false; //Make sure only units that have at least one datatype are shown
foreach (DataType dummyDataType in unit.AssociatedDataTypes)
{
if (!hasDatatype)
{
hasDatatype = true;
}
DataTypeInfo dataTypeInfo = new DataTypeInfo();
DataType fullDataType = allDataypes.Where(p => p.Id == dummyDataType.Id).FirstOrDefault();
dataTypeInfo.DataTypeId = fullDataType.Id;
dataTypeInfo.Description = fullDataType.Description;
dataTypeInfo.Name = fullDataType.Name;
unitInfo.DataTypeInfos.Add(dataTypeInfo);
}
if (hasDatatype)
{
model.AvailableUnits.Add(unitInfo);
}
}
}
//Sort the units by name
model.AvailableUnits.Sort(delegate(UnitInfo u1, UnitInfo u2)
{
return(String.Compare(u1.Name, u2.Name, StringComparison.InvariantCultureIgnoreCase));
});
if (!TaskManager.Bus.ContainsKey(EasyUploadTaskManager.VERIFICATION_AVAILABLEUNITS))
{
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_AVAILABLEUNITS, model.AvailableUnits);
}
string filePath = TaskManager.Bus[EasyUploadTaskManager.FILEPATH].ToString();
string selectedHeaderAreaJson = TaskManager.Bus[EasyUploadTaskManager.SHEET_HEADER_AREA].ToString();
FileStream fis = null;
fis = new FileStream(filePath, FileMode.Open, FileAccess.Read);
ExcelPackage ep = new ExcelPackage(fis);
fis.Close();
ExcelWorkbook excelWorkbook = ep.Workbook;
ExcelWorksheet firstWorksheet = excelWorkbook.Worksheets[1];
string sheetFormatString = Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_FORMAT]);
SheetFormat sheetFormat = 0;
Enum.TryParse <SheetFormat>(sheetFormatString, true, out sheetFormat);
model.HeaderFields = GetExcelHeaderFields(firstWorksheet, sheetFormat, selectedHeaderAreaJson).ToArray();
if (!TaskManager.Bus.ContainsKey(EasyUploadTaskManager.VERIFICATION_HEADERFIELDS))
{
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_HEADERFIELDS, model.HeaderFields);
}
model.Suggestions = new Dictionary <int, List <EasyUploadSuggestion> >();
for (int i = 0; i < model.HeaderFields.Length; i++)
{
//Default unit should be "none" if it exists, otherwise just take the first unit
UnitInfo currentUnitInfo = model.AvailableUnits.FirstOrDefault(u => u.Name.ToLower() == "none");
if (currentUnitInfo != null)
{
currentUnitInfo = (UnitInfo)currentUnitInfo.Clone();
}
else
{
currentUnitInfo = (UnitInfo)model.AvailableUnits.FirstOrDefault().Clone();
}
DataTypeInfo dtinfo = currentUnitInfo.DataTypeInfos.FirstOrDefault();
currentUnitInfo.SelectedDataTypeId = dtinfo.DataTypeId;
ViewData["defaultUnitID"] = currentUnitInfo.UnitId;
ViewData["defaultDatatypeID"] = dtinfo.DataTypeId;
if (model.AssignedHeaderUnits.Where(t => t.Item1 == i).FirstOrDefault() == null)
{
model.AssignedHeaderUnits.Add(new Tuple <int, string, UnitInfo>(i, model.HeaderFields[i], currentUnitInfo));
}
#region suggestions
//Add a variable to the suggestions if the names are similar
model.Suggestions.Add(i, new List <EasyUploadSuggestion>());
//Calculate similarity metric
//Accept suggestion if the similarity is greater than some threshold
double threshold = 0.5;
IEnumerable <DataAttribute> suggestions = allDataAttributes.Where(att => similarity(att.Name, model.HeaderFields[i]) >= threshold);
//Order the suggestions according to the similarity
List <DataAttribute> ordered = suggestions.ToList <DataAttribute>();
ordered.Sort((x, y) => (similarity(y.Name, model.HeaderFields[i])).CompareTo(similarity(x.Name, model.HeaderFields[i])));
//Add the ordered suggestions to the model
foreach (DataAttribute att in ordered)
{
model.Suggestions[i].Add(new EasyUploadSuggestion(att.Name, att.Unit.Id, att.DataType.Id, att.Unit.Name, att.DataType.Name, true));
}
//Use the following to order suggestions alphabetically instead of ordering according to the metric
//model.Suggestions[i] = model.Suggestions[i].Distinct().OrderBy(s => s.attributeName).ToList<EasyUploadSuggestion>();
//Each Name-Unit-Datatype-Tuple should be unique
model.Suggestions[i] = model.Suggestions[i].Distinct().ToList <EasyUploadSuggestion>();
#endregion
}
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_ATTRIBUTESUGGESTIONS, model.Suggestions);
TaskManager.AddToBus(EasyUploadTaskManager.VERIFICATION_MAPPEDHEADERUNITS, model.AssignedHeaderUnits);
// when jumping back to this step
if (TaskManager.Bus.ContainsKey(EasyUploadTaskManager.SHEET_JSON_DATA))
{
if (!String.IsNullOrEmpty(Convert.ToString(TaskManager.Bus[EasyUploadTaskManager.SHEET_JSON_DATA])))
{
}
}
model.StepInfo = TaskManager.Current();
return(PartialView(model));
}
}
