public DataTab(string name, XlColor color, Schema schema, IEnumerable <BaseDTO> list)
{
Name = name;
Color = color;
Schema = MakeSchema(schema);
_list = list == null ? new List <BaseDTO>() : new List <BaseDTO>(list);
}
private static void WriteWorkbook(string fName)
{
Console.WriteLine("Creating workbook named [{0}]", fName);
XlWorkbook book = new XlWorkbook();
XlWorksheets sheets = book.Worksheets;
XlSchema schema = GetDeploymentSchema();
XlWorksheet sheet = sheets.AddWorksheet("DET_Deployments", XlColor.White, schema);
GetDeploymentRows(sheet);
schema = GetMeasurementSchema();
sheet = sheets.AddWorksheet("DET_Measurements", XlColor.White, schema);
GetMeasurementRows(sheet);
schema = GetSiteSchema();
sheet = sheets.AddWorksheet("LIST_Sites", XlColor.Orange, schema);
GetSiteRows(sheet);
schema = GetSensorSchema();
sheet = sheets.AddWorksheet("LIST_Sensors", XlColor.Orange, schema);
GetSensorRows(sheet);
XlCustomProperties properties = book.Properties;
properties.AddCustomProperty("oncorId", Guid.NewGuid().ToString());
properties.AddCustomProperty("oncorUser", "Dr. Frank N. Furter, ESQ");
book.Save(fName);
Console.WriteLine("Closing workbook named [{0}]", fName);
}
private static void AddDummyRows(XlRows rows, XlSchema schema)
{
for (int rowIndex = 1; rowIndex < 12; rowIndex++)
{
IXlRowData rowData = new DummySchemaRowData(schema, rowIndex);
rows.AddRow(rowData);
}
}
public DummySchemaRowData(XlSchema schema, int rowIndex)
{
int columnIndex = 1;
foreach (XlColumn column in schema.Columns)
{
XlCell cell = new XlCell(0U, column.Type, GetValue(column.Type, rowIndex, columnIndex++));
_list.Add(cell);
}
}
private static XlSchema GetSiteSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("SiteKey", typeof(int), StyleSheetHelper.Normal);
schema.AddColumn("Name", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Description", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Location", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("LocationMark", typeof(string), StyleSheetHelper.Normal);
return(schema);
}
private XlSchema MakeSchema(Schema baseSchema)
{
XlSchema newSchema = new XlSchema();
foreach (SchemaEntry column in baseSchema)
{
uint color = ConvertTypeToColor(column.ColumnType);
newSchema.AddColumn(column.ColumnName, column.ValueType, color);
}
return(newSchema);
}
private static XlSchema GetSiteSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("Key", typeof(string), StyleSheetHelper.Orange);
schema.AddColumn("SiteAlias", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Name", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("PrincipalName", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Description", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("InternalId", typeof(int), StyleSheetHelper.Normal);
return(schema);
}
private static XlSchema GetDeploymentSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("DeployCode", typeof(string), StyleSheetHelper.Blue);
schema.AddColumn("SiteId", typeof(int), StyleSheetHelper.Orange);
schema.AddColumn("SensorId", typeof(int), StyleSheetHelper.Orange);
schema.AddColumn("StartDate", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("EndDate", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("Comments", typeof(string), StyleSheetHelper.Normal);
return(schema);
}
private static XlSchema GetSensorSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("Key", typeof(string), StyleSheetHelper.Orange);
schema.AddColumn("InstrumentName", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Model", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("InstrumentType", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("InstrumentClass", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Manufacturer", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("SensorList", typeof(string), StyleSheetHelper.Normal);
return(schema);
}
private static XlSchema GetSampleEventSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("Org", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("FieldTrip", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Activity", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("Project", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("SampleEvent", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("SampleEventKey", typeof(int), StyleSheetHelper.Normal);
schema.AddColumn("SampleEventDesc", typeof(string), StyleSheetHelper.Normal);
//schema.AddColumn("SampleEventStart", typeof(DateTime), StyleSheetHelper.Normal);
//schema.AddColumn("SampleEventEnd", typeof(DateTime), StyleSheetHelper.Normal);
return(schema);
}
private static XlSchema GetMeasurementSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("DeployCode", typeof(string), StyleSheetHelper.Blue);
schema.AddColumn("MeasureDateTime", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("Temperature", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("SurfaceElevation", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("pH", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("DO", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Conductivity", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Salinity", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Velocity", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Turbidity", typeof(int), StyleSheetHelper.Normal);
schema.AddColumn("Color", typeof(string), StyleSheetHelper.Normal);
return(schema);
}
public SchemaRowData(XlSchema schema, List <string> items)
{
int columnIndex = 0;
foreach (XlColumn column in schema.Columns)
{
string item;
if (items[columnIndex] != null)
{
item = items[columnIndex];
}
else
{
item = "";
}
XlCell cell = new XlCell(0U, column.Type, item);
_list.Add(cell);
columnIndex++;
}
}
private static XlSchema GetDeployMeasurementSchema()
{
XlSchema schema = new XlSchema();
schema.AddColumn("DataKey", typeof(int), StyleSheetHelper.Normal);
schema.AddColumn("Deployment", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("DeployDesc", typeof(string), StyleSheetHelper.Normal);
schema.AddColumn("SamplingEventKey", typeof(int), StyleSheetHelper.Normal);
schema.AddColumn("SiteKey", typeof(string), StyleSheetHelper.Normal); //must be a string in case of "unknown or unauthorized", to avoid excel xml validation issues
schema.AddColumn("DeployStart", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("DeployEnd", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("SampleDate", typeof(DateTime), StyleSheetHelper.Normal);
schema.AddColumn("SurfaceElevation", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Temperature", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("pH", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("DissolvedOxygen", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Conductivity", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Salinity", typeof(double), StyleSheetHelper.Normal);
schema.AddColumn("Velocity", typeof(double), StyleSheetHelper.Normal);
return(schema);
}
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);
}
