private static XlRows GetSensorRows(XlWorksheet sheet) { XlRows rows = sheet.Rows; AddDummyRows(rows, sheet.Schema); return(rows); }
private static XlRows GetMeasurementRows(XlWorksheet sheet) { XlRows rows = sheet.Rows; AddDummyRows(rows, sheet.Schema); return(rows); }
private static void AddDummyRows(XlRows rows, XlSchema schema) { for (int rowIndex = 1; rowIndex < 12; rowIndex++) { IXlRowData rowData = new DummySchemaRowData(schema, rowIndex); rows.AddRow(rowData); } }
private Guid CreateExcelFile(Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict, Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList, Dictionary <CompoundIdentity, Organization> orgDict, Dictionary <CompoundIdentity, FieldTrip> fieldTripDict, Dictionary <CompoundIdentity, FieldActivity> fieldActivityDict, Dictionary <CompoundIdentity, Project> projectDict) { IFileStoreProvider provider = FileStoreManager.Instance.GetProvider(); //Setting up file and Excel Workbook FilestoreFile deployFile = provider.MakeTemp(DateTime.UtcNow.AddHours(4)); XlWorkbook book = new XlWorkbook(); XlWorksheets sheets = book.Worksheets; //Generating Sampling Event Sheet XlSchema eventSchema = GetSampleEventSchema(); XlWorksheet eventSheet = sheets.AddWorksheet("SamplingEvents", XlColor.White, eventSchema); XlRows eventRows = eventSheet.Rows; var orderedEvents = eventDict.OrderBy(x => x.Value.Item1); foreach (var evt in orderedEvents) { string orgName = na; if (orgDict.ContainsKey(evt.Value.Item2.PrincipalOrgId)) { orgName = orgDict[evt.Value.Item2.PrincipalOrgId].Name; } string ftripName = na; string factivityName = na; string projName = na; if (fieldTripDict.ContainsKey(evt.Value.Item2.FieldTripId)) { FieldTrip ftrip = fieldTripDict[evt.Value.Item2.FieldTripId]; ftripName = ftrip.Name; if (fieldActivityDict.ContainsKey(ftrip.FieldActivityId)) { FieldActivity factivity = fieldActivityDict[ftrip.FieldActivityId]; factivityName = factivity.Name; if (projectDict.ContainsKey(factivity.ProjectId)) { projName = projectDict[factivity.ProjectId].Name; } } } List <string> evtItems = new List <string>(); evtItems.Add(orgName); evtItems.Add(projName); evtItems.Add(factivityName); evtItems.Add(ftripName); evtItems.Add(evt.Value.Item2.Name); evtItems.Add(evt.Value.Item1.ToString()); evtItems.Add(evt.Value.Item2.Description); //evtItems.Add(evt.Value.Item2.DateRange.Min.ToString()); //evtItems.Add(evt.Value.Item2.DateRange.Max.ToString()); SchemaRowData row = new SchemaRowData(eventSchema, evtItems); eventRows.AddRow(row); } //Generating Deployment/Measurement Sheet XlSchema measSchema = GetDeployMeasurementSchema(); XlWorksheet measSheet = sheets.AddWorksheet("WaterQualityMeasurements", XlColor.White, measSchema); XlRows measRows = measSheet.Rows; var orderedMeasurements = measurementList.OrderBy(x => x.Item1); foreach (var meas in orderedMeasurements) { WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2; WaterQualityMeasurement measurement = meas.Item2; int eventIndex = eventDict[deploy.SampleEventId].Item1; //deploy.SiteId could be a dangling reference string siteFK = na; if (siteDict.ContainsKey(deploy.SiteId)) { siteFK = siteDict[deploy.SiteId].Item1.ToString(); } List <string> measItems = new List <string>(); measItems.Add(meas.Item1.ToString()); measItems.Add(deploy.Name); measItems.Add(deploy.Description); measItems.Add(eventIndex.ToString()); measItems.Add(siteFK); measItems.Add(deploy.Range.StartDate.ToString()); measItems.Add(deploy.Range.EndDate.ToString()); measItems.Add(measurement.SampleDate.ToString()); measItems.Add(measurement.SurfaceElevation.ToString()); measItems.Add(measurement.Temperature.ToString()); measItems.Add(measurement.pH.ToString()); measItems.Add(measurement.DissolvedOxygen.ToString()); measItems.Add(measurement.Conductivity.ToString()); measItems.Add(measurement.Salinity.ToString()); measItems.Add(measurement.Velocity.ToString()); SchemaRowData row = new SchemaRowData(measSchema, measItems); measRows.AddRow(row); } //Generating Site Sheet XlSchema siteSchema = GetSiteSchema(); XlWorksheet siteSheet = sheets.AddWorksheet("Sites", XlColor.White, siteSchema); XlRows siteRows = siteSheet.Rows; var orderedSites = siteDict.OrderBy(x => x.Value.Item1); foreach (var site in orderedSites) { Site s = site.Value.Item2; List <string> siteItems = new List <string>(); siteItems.Add(site.Value.Item1.ToString()); siteItems.Add(s.Name); siteItems.Add(s.Description); IGeometry2 <double> geom = s.Location; if (geom != null) { if (geom is PolygonBag2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as PolygonBag2 <double>).ToString()); } else if (geom is Polygon2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Polygon2 <double>).ToString()); } else if (geom is Polyline2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Polyline2 <double>).ToString()); } else if (geom is PolylineBag2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as PolylineBag2 <double>).ToString()); } else if (geom is Point2 <double> ) { siteItems.Add(WktUtils.ToWkt(geom as Point2 <double>).ToString()); } } else { siteItems.Add(""); } Point2 <double> geom2 = s.LocationMark; if (geom2 != null) { siteItems.Add(WktUtils.ToWkt(geom2 as Point2 <double>).ToString()); } else { siteItems.Add(""); } SchemaRowData row = new SchemaRowData(siteSchema, siteItems); siteRows.AddRow(row); } book.Save(deployFile); deployFile.Flush(); deployFile.Close(); deployFile.Dispose(); return(deployFile.FileId); }