public Report GetReport(Query query, string authToken, int profileCounter) { authenticationToken = authToken; NotifySubscribers(10 , "Requesting report" , null); Report report = new Report(); int originalStartIndex = query.StartIndex; CreateRequest(query, profileCounter); if (!RequestData(request)) return report; int dimensionsAndMetrics = query.GetDimensionsAndMetricsCount(); if (xDoc != null) { NotifySubscribers(50, "Extract data", null); report.Data = ExtractDataFromXml(xDoc, dimensionsAndMetrics); report.Query = query.ToString(); report.SiteURI = query.Ids[profileCounter].Value; } report.Headers = SetHeaders(query); // Checks if paging is neccessary. while (totalHitResult > upperLimitBound && upperLimitBound > 10000) { query.StartIndex = query.StartIndex + 10000; query.MaxResults = upperLimitBound = query.MaxResults + 10000; CreateRequest(query, profileCounter); if (!RequestData(request)) return report; NotifySubscribers(50, "Extract data", null); report.Data = ExtractDataFromXml(xDoc, dimensionsAndMetrics); if (upperLimitBound <= query.MaxResults) break; } query.StartIndex = originalStartIndex; return report; }
/// <summary> /// /// </summary> /// <param name="queries"></param> /// <param name="worksheet"></param> private void ExecuteQuery(List<Query> queries, bool worksheet) { if (queries.Count == 0) return; if (_user == null) return; _addressQueries = new List<string>(); bool clearFormat = false; List<Report> reportList = new List<Report>(); int profileCounter = 0; int cellOffset = 0; foreach (Query query in queries) { _reportManager = new ReportManager(); _executionProgressWindow = new ExecutionProgress(_reportManager); _executionProgressWindow.Show(); _currentReport = _reportManager.GetReport(query, _user.AuthToken, profileCounter); if (!_currentReport.ValidateResult()) continue; reportList.Add(_currentReport); if (profileCounter == 0 && _currentReport != null && _currentReport.ValidateResult()) { if (ActiveCellHasQueryResult || worksheet) { clearFormat = Excel2007Addin.Settings.Default.CellFormatting == (int)WPFUIv2.CellFormattingEnum.never || (Excel2007Addin.Settings.Default.CellFormatting == (int)WPFUIv2.CellFormattingEnum.ask && MessageBox.Show("Do you want to erase the format of your excel query columns?", "Document format", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes); ClearPreviousQueryResult(clearFormat, worksheet, query); } } if (!worksheet) { // If a query is executed containing more than one profile the active cell cursor must be // moved to prevent clearing the prior report in Excel. PresentResult(query, _currentReport, profileCounter, cellOffset); cellOffset += _currentReport.Data.GetLength(1) + 1; // +1 for space if (query.Ids.Count > profileCounter) profileCounter++; } } int i = 0; if (_user != null && worksheet) { foreach (Query query in queries) { PresentResultUpdate(query, reportList[i], profileCounter); i++; } } }
private static void PresentResultUpdate(Query query, Report report, int profileCounter) { Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application; Worksheet activeSheet = currentApp.ActiveSheet as Worksheet; if (currentApp.ActiveSheet != null) { int activeColumn = query.Column; int activeRow = query.Row; string addressLocal = ""; string addressHelper = ""; object[] queryInformation = GetQueryInformation(query, report, profileCounter); int infoRows = queryInformation.GetLength(0); int dataLength = dataLength = report.Data.GetLength(1); Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); // addressLocal = queryInformationRange.get_Address().Replace("$", ""); string[] adLocal = addressLocal.Split(':'); // Looks after other reports in the same column. foreach (string address in _addressQueries) { addressHelper = address.Replace("$", ""); // If there exist a report above this report then check where that report's data range ends. if (addressHelper.First().Equals(adLocal[0].First())) { addressHelper = addressHelper.Substring(1, addressHelper.Length - 1); int addressHelperInt = Int32.Parse(addressHelper); int addressLocalInt = Int32.Parse(adLocal[0].Substring(1, adLocal[0].Length - 1)); if (addressHelperInt > addressLocalInt) { activeRow = addressHelperInt + 2; queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); } } } ((Style)queryInformationRange.Style).WrapText = false; queryInformationRange.Font.Italic = true; queryInformationRange.MergeCells = true; queryInformationRange.Borders.Weight = XlBorderWeight.xlThin; queryInformationRange.Value2 = queryInformation; if (report.Data != null) { dataLength = report.Data.GetLength(1); Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn], activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]); dataRange.Value2 = report.Data; // Saves the last cell in this particular data range. string endDataRow = ""; endDataRow = adLocal[0].First() + (activeRow + infoRows + report.Data.GetLength(0)).ToString(); _addressQueries.Add(endDataRow); Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn], activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]); headerRange.Value2 = report.Headers; headerRange.Font.Bold = true; } } }
private static void PresentResult(Query query, Report report, int profileCounter, int cellOffset) { // This method shall include a verification of empty columns. If the user has selected more than one profile // the second query shall be presented on the right of the first query result in Excel. Microsoft.Office.Interop.Excel.Application currentApp = GA_Excel2007.Globals.ThisAddIn.Application; Worksheet activeSheet = currentApp.ActiveSheet as Worksheet; if (currentApp.ActiveSheet != null) { int dataLength = 2; if (report.Data != null) { dataLength = report.Data.GetLength(1); } int activeRow = currentApp.ActiveCell.Row; int activeColumn = currentApp.ActiveCell.Column + cellOffset; /*if (currentApp.ActiveCell.Cells.Value2 != null) { activeColumn = ActiveColumn(profileCounter, dataLength); }*/ object[] queryInformation = GetQueryInformation(query, report, profileCounter); int infoRows = queryInformation.GetLength(0); if (report.Data != null) { dataLength = report.Data.GetLength(1); Range dataRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows + 1, activeColumn], activeSheet.Cells[activeRow + infoRows + report.Data.GetLength(0), activeColumn + report.Data.GetLength(1) - 1]); dataRange.Value2 = report.Data; Range headerRange = currentApp.get_Range(activeSheet.Cells[activeRow + infoRows, activeColumn], activeSheet.Cells[activeRow + infoRows, activeColumn + report.Headers.GetLength(1) - 1]); headerRange.Value2 = report.Headers; headerRange.Font.Bold = true; } Range queryInformationRange = currentApp.get_Range(activeSheet.Cells[activeRow, activeColumn], activeSheet.Cells[activeRow, activeColumn + dataLength - 1]); queryInformationRange.Font.Italic = true; queryInformationRange.MergeCells = true; queryInformationRange.Borders.Weight = XlBorderWeight.xlThin; queryInformationRange.Value2 = queryInformation; //int height = (int)queryInformationRange.Height; queryInformationRange.RowHeight = height; } }
private static object[] GetQueryInformation(Query query, Report report, int profileCounter) { string timePeriod = ""; if (!query.SelectDates) { timePeriod += query.TimePeriod.ToString(); } else { timePeriod = "PeriodNotSpecified"; } object[] queryInformation = new object[] { report.SiteURI + " [ " + query.StartDate.ToShortDateString() + " -> " + query.EndDate.ToShortDateString() + " ]\n" + string.Format( "{0}queryString={1};rows={2};columns={3};timePeriod={4}]", queryInfoIdentifier, query.ToString(profileCounter), report.Hits, query.GetDimensionsAndMetricsCount(), timePeriod)}; return queryInformation; }