public void UpdateListObjectStatus(Excel.ListObject pListObject, Report[] pReports) { var loDataRange = pListObject.DataBodyRange.Resize[Type.Missing, ColPrompt - 1]; var data = ExcelArray <object> .Get(loDataRange.Value); var nbrow = data.GetLength(0); //Get columns index int colLogIndex = pListObject.ListColumns[ColLogName].Index; int colStatusIndex = pListObject.ListColumns[ColInsStatusName].Index; int colDateIndex = pListObject.ListColumns[ColInsDateName].Index; int colTimeIndex = pListObject.ListColumns[ColInsTimeName].Index; int colInsCuidIndex = pListObject.ListColumns[ColInsCuidName].Index; int colInsIdIndex = pListObject.ListColumns[ColInsIdName].Index; int colIdIndex = pListObject.ListColumns[ColDocIdName].Index; int colNbInsIndex = pListObject.ListColumns[ColNbInstName].Index; //Update values var refRow = new Dictionary <int?, int>(); for (var r = 1; r <= nbrow; r++) { refRow.Add((int)(double)(data[r, colIdIndex]), r); } foreach (Report lReport in pReports) { if (lReport.Id == null) { continue; } var lRow = refRow[lReport.Id]; data[lRow, colLogIndex] = lReport.LOG; if (lReport.Instance != null) { data[lRow, colInsCuidIndex] = lReport.Instance.Cuid; data[lRow, colInsIdIndex] = lReport.Instance.Id; data[lRow, colStatusIndex] = lReport.Instance.Status; data[lRow, colDateIndex] = lReport.Instance.Date; data[lRow, colTimeIndex] = lReport.Instance.Time; } data[lRow, colNbInsIndex] = lReport.NbChild; } loDataRange.Value = data; loDataRange.WrapText = false; }
public StringBuilder GetReportsList(Excel.ListObject pListObject) { var ids = ExcelArray <object> .Get(GetDataBodyRange(pListObject.ListColumns[ColDocIdName].Range).Value); var names = ExcelArray <object> .Get(GetDataBodyRange(pListObject.ListColumns[ColDocNameName].Range).Value); var worksheet = (Excel.Worksheet)pListObject.Parent; int rowOffest = pListObject.DataBodyRange.Row; int nbRow = ids.GetLength(0); var reports = new StringBuilder(); for (int r = 1, rr = rowOffest; r <= nbRow; r++, rr++) { if ((bool)((Excel.Range)worksheet.Rows[rr]).Hidden == false) { reports.AppendLine(ids[r, 1] + " - " + names[r, 1]); } } return(reports); }
public Report[] ParseListObjectToReports(Excel.ListObject pListObject, bool pIncludePrompts) { var loDataRange = pIncludePrompts ? pListObject.DataBodyRange : pListObject.DataBodyRange.Resize[Type.Missing, ColPrompt - 1]; var data = ExcelArray <object> .Get(loDataRange.Value); var nbRow = data.GetLength(0); //Get columns index var colInsIdIndex = pListObject.ListColumns[ColInsIdName].Index; var colInsCuidIndex = pListObject.ListColumns[ColInsCuidName].Index; var colInsStatusIndex = pListObject.ListColumns[ColInsStatusName].Index; var colInsDateIndex = pListObject.ListColumns[ColInsDateName].Index; var colInsTimeIndex = pListObject.ListColumns[ColInsTimeName].Index; var colCuidIndex = pListObject.ListColumns[ColDocCuidName].Index; var colIdIndex = pListObject.ListColumns[ColDocIdName].Index; var colNameIndex = pListObject.ListColumns[ColDocNameName].Index; var colTypeIndex = pListObject.ListColumns[ColDocTypeName].Index; var colNbChildIndex = pListObject.ListColumns[ColNbInstName].Index; var colNbPromptsIndex = pListObject.ListColumns[ColNbPromptName].Index; int[] colPromptName = null; const string pattern = "^" + ColPromptName + @"\d+$"; if (pIncludePrompts) { var colPrompt = new List <int>(); foreach (Excel.ListColumn listcolumn in pListObject.ListColumns) { if (System.Text.RegularExpressions.Regex.IsMatch(listcolumn.Name, pattern)) { colPrompt.Add(listcolumn.Index); } } colPromptName = colPrompt.ToArray(); } var activesheet = (Excel.Worksheet)pListObject.Parent; //Set values var lReports = new List <Report>(); for (int r = 1, rr = loDataRange.Row; r <= nbRow; r++, rr++) { if ((bool)((Excel.Range)activesheet.Rows[rr]).Hidden) { continue; } var report = new Report { Cuid = (string)data[r, colCuidIndex], Id = GetInteger(data[r, colIdIndex]), Name = (string)data[r, colNameIndex], Type = (string)data[r, colTypeIndex], NbChild = GetInteger(data[r, colNbChildIndex]), Instance = data[r, colInsCuidIndex] == null ? null : new Instance { Id = GetInteger(data[r, colInsIdIndex]), Cuid = (string)data[r, colInsCuidIndex], Date = (string)data[r, colInsDateIndex], Time = (string)data[r, colInsTimeIndex], } }; if (pIncludePrompts) { var nbPrompts = GetInteger(data[r, colNbPromptsIndex]); if (nbPrompts != null && nbPrompts != 0) { report.Prompts = new Prompt[(int)nbPrompts]; for (var ip = 0; ip < (int)nbPrompts; ip++) { report.Prompts[ip] = new Prompt { Name = ((Excel.Range)loDataRange.Cells[r, colPromptName[ip]]).Comment.Text(), Value = (string)data[r, colPromptName[ip]] }; } } } lReports.Add(report); } return(lReports.ToArray()); }