| 1 | /** |
|---|
| 2 | * AssayService Service |
|---|
| 3 | * |
|---|
| 4 | * @author s.h.sikkema@gmail.com |
|---|
| 5 | * @since 20101216 |
|---|
| 6 | * @package dbnp.studycapturing |
|---|
| 7 | * |
|---|
| 8 | * Revision information: |
|---|
| 9 | * $Rev: 1853 $ |
|---|
| 10 | * $Author: s.h.sikkema@gmail.com $ |
|---|
| 11 | * $Date: 2011-05-19 14:19:08 +0000 (do, 19 mei 2011) $ |
|---|
| 12 | */ |
|---|
| 13 | package dbnp.studycapturing |
|---|
| 14 | |
|---|
| 15 | import org.apache.poi.ss.usermodel.* |
|---|
| 16 | import org.apache.poi.xssf.usermodel.XSSFWorkbook |
|---|
| 17 | import org.apache.poi.hssf.usermodel.HSSFWorkbook |
|---|
| 18 | import org.codehaus.groovy.grails.web.json.JSONObject |
|---|
| 19 | import org.dbnp.gdt.RelTime |
|---|
| 20 | import org.dbnp.gdt.TemplateFieldType |
|---|
| 21 | import java.text.DecimalFormat |
|---|
| 22 | import java.text.NumberFormat |
|---|
| 23 | import org.dbnp.gdt.Template |
|---|
| 24 | import org.dbnp.gdt.TemplateField |
|---|
| 25 | |
|---|
| 26 | class AssayService { |
|---|
| 27 | |
|---|
| 28 | boolean transactional = false |
|---|
| 29 | def authenticationService |
|---|
| 30 | def moduleCommunicationService |
|---|
| 31 | |
|---|
| 32 | /** |
|---|
| 33 | * Collects the assay field names per category in a map as well as the |
|---|
| 34 | * module's measurements. |
|---|
| 35 | * |
|---|
| 36 | * @param assay the assay for which to collect the fields |
|---|
| 37 | * @return a map of categories as keys and field names or measurements as |
|---|
| 38 | * values |
|---|
| 39 | */ |
|---|
| 40 | def collectAssayTemplateFields(assay) throws Exception { |
|---|
| 41 | |
|---|
| 42 | def getUsedTemplateFields = { templateEntities -> |
|---|
| 43 | |
|---|
| 44 | // gather all unique and non null template fields that haves values |
|---|
| 45 | templateEntities*.giveFields().flatten().unique().findAll{ field -> |
|---|
| 46 | |
|---|
| 47 | field && templateEntities.any { it?.fieldExists(field.name) && it.getFieldValue(field.name) != null } |
|---|
| 48 | |
|---|
| 49 | }.collect{[name: it.name, comment: it.comment, displayName: it.name + (it.unit ? " ($it.unit)" : '')]} |
|---|
| 50 | } |
|---|
| 51 | |
|---|
| 52 | def samples = assay.samples |
|---|
| 53 | [ 'Subject Data' : getUsedTemplateFields( samples*."parentSubject".unique() ), |
|---|
| 54 | 'Sampling Event Data' : getUsedTemplateFields( samples*."parentEvent".unique() ), |
|---|
| 55 | 'Sample Data' : getUsedTemplateFields( samples ), |
|---|
| 56 | 'Event Group' : [[name: 'name', comment: 'Name of Event Group', displayName: 'name']], |
|---|
| 57 | |
|---|
| 58 | // If module is not reachable, only the field 'module error' is returned, and is filled later on. |
|---|
| 59 | 'Module Measurement Data': moduleCommunicationService.isModuleReachable(assay.module.url) ? requestModuleMeasurementNames(assay) : [ [ name: "Module error" ] ] |
|---|
| 60 | ] |
|---|
| 61 | |
|---|
| 62 | } |
|---|
| 63 | |
|---|
| 64 | /** |
|---|
| 65 | * Gathers all assay related data, including measurements from the module, |
|---|
| 66 | * into 1 hash map containing: Subject Data, Sampling Event Data, Sample |
|---|
| 67 | * Data, and module specific measurement data. |
|---|
| 68 | * Data from each of the 4 hash map entries are themselves hash maps |
|---|
| 69 | * representing a descriptive header (field name) as key and the data as |
|---|
| 70 | * value. |
|---|
| 71 | * |
|---|
| 72 | * @param assay the assay to collect data for |
|---|
| 73 | * @param fieldMap map with categories as keys and fields as values |
|---|
| 74 | * @param measurementTokens selection of measurementTokens |
|---|
| 75 | * @return The assay data structure as described above. |
|---|
| 76 | */ |
|---|
| 77 | def collectAssayData(assay, fieldMap, measurementTokens) throws Exception { |
|---|
| 78 | |
|---|
| 79 | def collectFieldValuesForTemplateEntities = { headerFields, templateEntities -> |
|---|
| 80 | |
|---|
| 81 | // return a hash map with for each field name all values from the |
|---|
| 82 | // template entity list |
|---|
| 83 | headerFields.inject([:]) { map, headerField -> |
|---|
| 84 | |
|---|
| 85 | map + [(headerField.displayName): templateEntities.collect { |
|---|
| 86 | |
|---|
| 87 | // default to an empty string |
|---|
| 88 | def val = '' |
|---|
| 89 | |
|---|
| 90 | def field |
|---|
| 91 | try { |
|---|
| 92 | |
|---|
| 93 | val = it.getFieldValue(headerField.name) |
|---|
| 94 | |
|---|
| 95 | // Convert RelTime fields to human readable strings |
|---|
| 96 | field = it.getField(headerField.name) |
|---|
| 97 | if (field.type == TemplateFieldType.RELTIME) |
|---|
| 98 | val = new RelTime( val as long ) |
|---|
| 99 | |
|---|
| 100 | } catch (NoSuchFieldException e) { /* pass */ } |
|---|
| 101 | |
|---|
| 102 | (val instanceof Number) ? val : val.toString()}] |
|---|
| 103 | } |
|---|
| 104 | } |
|---|
| 105 | |
|---|
| 106 | def getFieldValues = { templateEntities, headerFields, propertyName = '' -> |
|---|
| 107 | |
|---|
| 108 | def returnValue |
|---|
| 109 | |
|---|
| 110 | // if no property name is given, simply collect the fields and |
|---|
| 111 | // values of the template entities themselves |
|---|
| 112 | if (propertyName == '') { |
|---|
| 113 | |
|---|
| 114 | returnValue = collectFieldValuesForTemplateEntities(headerFields, templateEntities) |
|---|
| 115 | |
|---|
| 116 | } else { |
|---|
| 117 | |
|---|
| 118 | // if a property name is given, we'll have to do a bit more work |
|---|
| 119 | // to ensure efficiency. The reason for this is that for a list |
|---|
| 120 | // of template entities, the properties referred to by |
|---|
| 121 | // propertyName can include duplicates. For example, for 10 |
|---|
| 122 | // samples, there may be less than 10 parent subjects. Maybe |
|---|
| 123 | // there's only 1 parent subject. We don't want to collect field |
|---|
| 124 | // values for this single subject 10 times ... |
|---|
| 125 | def fieldValues |
|---|
| 126 | |
|---|
| 127 | // we'll get the unique list of properties to make sure we're |
|---|
| 128 | // not getting the field values for identical template entity |
|---|
| 129 | // properties more then once. |
|---|
| 130 | def uniqueProperties = templateEntities*."$propertyName".unique() |
|---|
| 131 | |
|---|
| 132 | fieldValues = collectFieldValuesForTemplateEntities(headerFields, uniqueProperties) |
|---|
| 133 | |
|---|
| 134 | // prepare a lookup hashMap to be able to map an entities' |
|---|
| 135 | // property (e.g. a sample's parent subject) to an index value |
|---|
| 136 | // from the field values list |
|---|
| 137 | int i = 0 |
|---|
| 138 | def propertyToFieldValueIndexMap = uniqueProperties.inject([:]) { map, item -> map + [(item):i++]} |
|---|
| 139 | |
|---|
| 140 | // prepare the return value so that it has an entry for field |
|---|
| 141 | // name. This will be the column name (second header line). |
|---|
| 142 | returnValue = headerFields*.displayName.inject([:]) { map, item -> map + [(item):[]] } |
|---|
| 143 | |
|---|
| 144 | // finally, fill map the unique field values to the (possibly |
|---|
| 145 | // not unique) template entity properties. In our example with |
|---|
| 146 | // 1 unique parent subject, this means copying that subject's |
|---|
| 147 | // field values to all 10 samples. |
|---|
| 148 | templateEntities.each{ te -> |
|---|
| 149 | |
|---|
| 150 | headerFields*.displayName.each{ |
|---|
| 151 | |
|---|
| 152 | returnValue[it] << fieldValues[it][propertyToFieldValueIndexMap[te[propertyName]]] |
|---|
| 153 | |
|---|
| 154 | } |
|---|
| 155 | |
|---|
| 156 | } |
|---|
| 157 | |
|---|
| 158 | } |
|---|
| 159 | |
|---|
| 160 | returnValue |
|---|
| 161 | |
|---|
| 162 | } |
|---|
| 163 | |
|---|
| 164 | // Find samples and sort by name |
|---|
| 165 | def samples = assay.samples.toList().sort { it.name } |
|---|
| 166 | |
|---|
| 167 | def eventFieldMap = [:] |
|---|
| 168 | |
|---|
| 169 | // check whether event group data was requested |
|---|
| 170 | if (fieldMap['Event Group']) { |
|---|
| 171 | |
|---|
| 172 | def names = samples*.parentEventGroup*.name.flatten() |
|---|
| 173 | |
|---|
| 174 | // only set name field when there's actual data |
|---|
| 175 | if (!names.every {!it}) eventFieldMap['name'] = names |
|---|
| 176 | |
|---|
| 177 | } |
|---|
| 178 | |
|---|
| 179 | [ 'Subject Data' : getFieldValues(samples, fieldMap['Subject Data'], 'parentSubject'), |
|---|
| 180 | 'Sampling Event Data' : getFieldValues(samples, fieldMap['Sampling Event Data'], 'parentEvent'), |
|---|
| 181 | 'Sample Data' : getFieldValues(samples, fieldMap['Sample Data']), |
|---|
| 182 | 'Event Group' : eventFieldMap, |
|---|
| 183 | |
|---|
| 184 | // If module is not reachable, only the message 'module not reachable' is given for each sample |
|---|
| 185 | 'Module Measurement Data': moduleCommunicationService.isModuleReachable(assay.module.url) ? |
|---|
| 186 | ( measurementTokens ? requestModuleMeasurements(assay, measurementTokens, samples) : [:] ) : |
|---|
| 187 | [ "Module error": [ "Module not reachable" ] * samples.size() ] |
|---|
| 188 | ] |
|---|
| 189 | } |
|---|
| 190 | |
|---|
| 191 | /** |
|---|
| 192 | * Prepend data from study to the data structure |
|---|
| 193 | * @param assayData Column wise data structure of samples |
|---|
| 194 | * @param assay Assay object the data should be selected from |
|---|
| 195 | * @param numValues Number of values for this assay |
|---|
| 196 | * @return Extended column wise data structure |
|---|
| 197 | */ |
|---|
| 198 | def prependStudyData( inputData, Assay assay, numValues ) { |
|---|
| 199 | if( !assay ) |
|---|
| 200 | return inputData; |
|---|
| 201 | |
|---|
| 202 | // Retrieve study data |
|---|
| 203 | def studyData =[:] |
|---|
| 204 | assay.parent?.giveFields().each { |
|---|
| 205 | def value = assay.parent.getFieldValue( it.name ) |
|---|
| 206 | if( value ) |
|---|
| 207 | studyData[ it.name ] = [value] * numValues |
|---|
| 208 | } |
|---|
| 209 | |
|---|
| 210 | return [ |
|---|
| 211 | 'Study Data': studyData |
|---|
| 212 | ] + inputData |
|---|
| 213 | } |
|---|
| 214 | |
|---|
| 215 | /** |
|---|
| 216 | * Prepend data from assay to the data structure |
|---|
| 217 | * @param assayData Column wise data structure of samples |
|---|
| 218 | * @param assay Assay object the data should be selected from |
|---|
| 219 | * @param numValues Number of values for this assay |
|---|
| 220 | * @return Extended column wise data structure |
|---|
| 221 | */ |
|---|
| 222 | def prependAssayData( inputData, Assay assay, numValues ) { |
|---|
| 223 | if( !assay ) |
|---|
| 224 | return inputData; |
|---|
| 225 | |
|---|
| 226 | // Retrieve assay data |
|---|
| 227 | def assayData = [:] |
|---|
| 228 | assay.giveFields().each { |
|---|
| 229 | def value = assay.getFieldValue( it.name ) |
|---|
| 230 | if( value ) |
|---|
| 231 | assayData[ it.name ] = [value] * numValues |
|---|
| 232 | } |
|---|
| 233 | |
|---|
| 234 | return [ |
|---|
| 235 | 'Assay Data': assayData |
|---|
| 236 | ] + inputData |
|---|
| 237 | } |
|---|
| 238 | |
|---|
| 239 | /** |
|---|
| 240 | * Retrieves measurement names from the module through a rest call |
|---|
| 241 | * |
|---|
| 242 | * @param consumer the url of the module |
|---|
| 243 | * @param path path of the rest call to the module |
|---|
| 244 | * @return |
|---|
| 245 | */ |
|---|
| 246 | def requestModuleMeasurementNames(assay) { |
|---|
| 247 | |
|---|
| 248 | def moduleUrl = assay.module.url |
|---|
| 249 | |
|---|
| 250 | def path = moduleUrl + "/rest/getMeasurements/query" |
|---|
| 251 | def query = "assayToken=$assay.assayUUID" |
|---|
| 252 | def jsonArray |
|---|
| 253 | |
|---|
| 254 | try { |
|---|
| 255 | jsonArray = moduleCommunicationService.callModuleMethod(moduleUrl, path, query, "POST") |
|---|
| 256 | } catch (e) { |
|---|
| 257 | throw new Exception("An error occured while trying to get the measurement tokens from the $assay.module.name. \ |
|---|
| 258 | This means the module containing the measurement data is not available right now. Please try again \ |
|---|
| 259 | later or notify the system administrator if the problem persists. URL: $path?$query.") |
|---|
| 260 | } |
|---|
| 261 | |
|---|
| 262 | jsonArray.collect { |
|---|
| 263 | if( it == JSONObject.NULL ) |
|---|
| 264 | return "" |
|---|
| 265 | else |
|---|
| 266 | return it.toString() |
|---|
| 267 | } |
|---|
| 268 | |
|---|
| 269 | } |
|---|
| 270 | |
|---|
| 271 | /** |
|---|
| 272 | * Retrieves module measurement data through a rest call to the module |
|---|
| 273 | * |
|---|
| 274 | * @param assay Assay for which the module measurements should be retrieved |
|---|
| 275 | * @param measurementTokens List with the names of the fields to be retrieved. Format: [ 'measurementName1', 'measurementName2' ] |
|---|
| 276 | * @param samples Samples for which the module |
|---|
| 277 | * @return |
|---|
| 278 | */ |
|---|
| 279 | def requestModuleMeasurements(assay, inputMeasurementTokens, samples) { |
|---|
| 280 | |
|---|
| 281 | def moduleUrl = assay.module.url |
|---|
| 282 | |
|---|
| 283 | def tokenString = '' |
|---|
| 284 | |
|---|
| 285 | inputMeasurementTokens.each{ |
|---|
| 286 | tokenString+="&measurementToken=${it.encodeAsURL()}" |
|---|
| 287 | } |
|---|
| 288 | |
|---|
| 289 | def path = moduleUrl + "/rest/getMeasurementData/query" |
|---|
| 290 | |
|---|
| 291 | def query = "assayToken=$assay.assayUUID$tokenString" |
|---|
| 292 | |
|---|
| 293 | def sampleTokens = [], measurementTokens = [], moduleData = [] |
|---|
| 294 | |
|---|
| 295 | try { |
|---|
| 296 | (sampleTokens, measurementTokens, moduleData) = moduleCommunicationService.callModuleMethod(moduleUrl, path, query, "POST") |
|---|
| 297 | } catch (e) { |
|---|
| 298 | throw new Exception("An error occured while trying to get the measurement data from the $assay.module.name. \ |
|---|
| 299 | This means the module containing the measurement data is not available right now. Please try again \ |
|---|
| 300 | later or notify the system administrator if the problem persists. URL: $path?$query.") |
|---|
| 301 | } |
|---|
| 302 | |
|---|
| 303 | if (!sampleTokens?.size()) return [] |
|---|
| 304 | |
|---|
| 305 | // Convert the three different maps into a map like: |
|---|
| 306 | // |
|---|
| 307 | // [ "measurement 1": [ value1, value2, value3 ], |
|---|
| 308 | // "measurement 2": [ value4, value5, value6 ] ] |
|---|
| 309 | // |
|---|
| 310 | // The returned values should be in the same order as the given samples-list |
|---|
| 311 | def map = [:] |
|---|
| 312 | def numSampleTokens = sampleTokens.size(); |
|---|
| 313 | |
|---|
| 314 | measurementTokens.eachWithIndex { measurementToken, measurementIndex -> |
|---|
| 315 | def measurements = []; |
|---|
| 316 | samples.each { sample -> |
|---|
| 317 | |
|---|
| 318 | // Do measurements for this sample exist? If not, a null value is returned |
|---|
| 319 | // for this sample. Otherwise, the measurement is looked up in the list with |
|---|
| 320 | // measurements, based on the sample token |
|---|
| 321 | if( sampleTokens.collect{ it.toString() }.contains( sample.giveUUID() ) ) { |
|---|
| 322 | def tokenIndex = sampleTokens.indexOf( sample.giveUUID() ); |
|---|
| 323 | def valueIndex = measurementIndex * numSampleTokens + tokenIndex; |
|---|
| 324 | |
|---|
| 325 | // If the module data is in the wrong format, show an error in the log file |
|---|
| 326 | // and return a null value for this measurement. |
|---|
| 327 | if( valueIndex >= moduleData.size() ) { |
|---|
| 328 | log.error "Module measurements given by module " + assay.module.name + " are not in the right format: " + measurementTokens?.size() + " measurements, " + sampleTokens?.size() + " samples, " + moduleData?.size() + " values" |
|---|
| 329 | measurements << null |
|---|
| 330 | } else { |
|---|
| 331 | measurements << ( moduleData[ valueIndex ] == JSONObject.NULL ? "" : moduleData[ valueIndex ].toDouble() ); |
|---|
| 332 | } |
|---|
| 333 | } else { |
|---|
| 334 | measurements << null |
|---|
| 335 | } |
|---|
| 336 | } |
|---|
| 337 | map[ measurementToken.toString() ] = measurements |
|---|
| 338 | } |
|---|
| 339 | |
|---|
| 340 | return map; |
|---|
| 341 | } |
|---|
| 342 | |
|---|
| 343 | /** |
|---|
| 344 | * Merges the data from multiple studies into a structure that can be exported to an excel file. The format for each assay is |
|---|
| 345 | * |
|---|
| 346 | * [Category1: |
|---|
| 347 | * [Column1: [1,2,3], Column2: [4,5,6]], |
|---|
| 348 | * Category2: |
|---|
| 349 | * [Column3: [7,8,9], Column4: [10,11,12], Column5: [13,14,15]]] |
|---|
| 350 | * |
|---|
| 351 | * Where the category describes the category of data that is presented (e.g. subject, sample etc.) and the column names describe |
|---|
| 352 | * the fields that are present. Each entry in the lists shows the value for that column for an entity. In this case, 3 entities are described. |
|---|
| 353 | * Each field should give values for all entities, so the length of all value-lists should be the same. |
|---|
| 354 | * |
|---|
| 355 | * Example: If the following input is given (2 assays) |
|---|
| 356 | * |
|---|
| 357 | * [ |
|---|
| 358 | * [Category1: |
|---|
| 359 | * [Column1: [1,2,3], Column2: [4,5,6]], |
|---|
| 360 | * Category2: |
|---|
| 361 | * [Column3: [7,8,9], Column4: [10,11,12], Column5: [13,14,15]]], |
|---|
| 362 | * [Category1: |
|---|
| 363 | * [Column1: [16,17], Column6: [18,19]], |
|---|
| 364 | * Category3: |
|---|
| 365 | * [Column3: [20,21], Column8: [22,23]]] |
|---|
| 366 | * ] |
|---|
| 367 | * |
|---|
| 368 | * the output will be (5 entries for each column, empty values for fields that don't exist in some assays) |
|---|
| 369 | * |
|---|
| 370 | * [ |
|---|
| 371 | * [Category1: |
|---|
| 372 | * [Column1: [1,2,3,16,17], Column2: [4,5,6,,], Column6: [,,,18,19]], |
|---|
| 373 | * Category2: |
|---|
| 374 | * [Column3: [7,8,9,,], Column4: [10,11,12,,], Column5: [13,14,15,,]], |
|---|
| 375 | * Category3: |
|---|
| 376 | * [Column3: [,,,20,21], Column8: [,,,22,23]] |
|---|
| 377 | * ] |
|---|
| 378 | * |
|---|
| 379 | * |
|---|
| 380 | * @param columnWiseAssayData List with each entry being the column wise data of an assay. The format for each |
|---|
| 381 | * entry is described above |
|---|
| 382 | * @return Hashmap Combined assay data, in the same structure as each input entry. Empty values are given as an empty string. |
|---|
| 383 | * So for input entries |
|---|
| 384 | */ |
|---|
| 385 | def mergeColumnWiseDataOfMultipleStudies(def columnWiseAssayData) { |
|---|
| 386 | // Compute the number of values that is expected for each assay. This number is |
|---|
| 387 | // used later on to determine the number of empty fields to add if a field is not present in this |
|---|
| 388 | // assay |
|---|
| 389 | def numValues = columnWiseAssayData.collect { assay -> |
|---|
| 390 | for( cat in assay ) { |
|---|
| 391 | if( cat ) { |
|---|
| 392 | for( field in cat.value ) { |
|---|
| 393 | if( field?.value?.size() > 0 ) { |
|---|
| 394 | return field.value.size(); |
|---|
| 395 | } |
|---|
| 396 | } |
|---|
| 397 | } |
|---|
| 398 | } |
|---|
| 399 | |
|---|
| 400 | return 0; |
|---|
| 401 | } |
|---|
| 402 | |
|---|
| 403 | // Merge categories from all assays. Create a list for all categories |
|---|
| 404 | def categories = columnWiseAssayData*.keySet().toList().flatten().unique(); |
|---|
| 405 | def mergedColumnWiseData = [:] |
|---|
| 406 | categories.each { category -> |
|---|
| 407 | // Only work with this category for all assays |
|---|
| 408 | def categoryData = columnWiseAssayData*.getAt( category ); |
|---|
| 409 | |
|---|
| 410 | // Find the different fields in all assays |
|---|
| 411 | def categoryFields = categoryData.findAll{ it }*.keySet().toList().flatten().unique(); |
|---|
| 412 | |
|---|
| 413 | // Find data for all assays for these fields. If the fields do not exist, return an empty string |
|---|
| 414 | def categoryValues = [:] |
|---|
| 415 | categoryFields.each { field -> |
|---|
| 416 | categoryValues[ field ] = []; |
|---|
| 417 | |
|---|
| 418 | // Loop through all assays |
|---|
| 419 | categoryData.eachWithIndex { assayValues, idx -> |
|---|
| 420 | if( assayValues && assayValues.containsKey( field ) ) { |
|---|
| 421 | // Append the values if they exist |
|---|
| 422 | categoryValues[ field ] += assayValues[ field ]; |
|---|
| 423 | } else { |
|---|
| 424 | // Append empty string for each entity if the field doesn't exist |
|---|
| 425 | categoryValues[ field ] += [""] * numValues[ idx ] |
|---|
| 426 | } |
|---|
| 427 | } |
|---|
| 428 | } |
|---|
| 429 | |
|---|
| 430 | mergedColumnWiseData[ category ] = categoryValues |
|---|
| 431 | } |
|---|
| 432 | |
|---|
| 433 | return mergedColumnWiseData; |
|---|
| 434 | } |
|---|
| 435 | |
|---|
| 436 | /** |
|---|
| 437 | * Converts column |
|---|
| 438 | * @param columnData multidimensional map containing column data. |
|---|
| 439 | * On the top level, the data must be grouped by category. Each key is the |
|---|
| 440 | * category title and the values are maps representing the columns. Each |
|---|
| 441 | * column also has a title (its key) and a list of values. Columns must be |
|---|
| 442 | * equally sized. |
|---|
| 443 | * |
|---|
| 444 | * For example, consider the following map: |
|---|
| 445 | * [Category1: |
|---|
| 446 | * [Column1: [1,2,3], Column2: [4,5,6]], |
|---|
| 447 | * Category2: |
|---|
| 448 | * [Column3: [7,8,9], Column4: [10,11,12], Column5: [13,14,15]]] |
|---|
| 449 | * |
|---|
| 450 | * which will be written as: |
|---|
| 451 | * |
|---|
| 452 | * | Category1 | | Category2 | | | |
|---|
| 453 | * | Column1 | Column2 | Column3 | Column4 | Column5 | |
|---|
| 454 | * | 1 | 4 | 7 | 10 | 13 | |
|---|
| 455 | * | 2 | 5 | 8 | 11 | 14 | |
|---|
| 456 | * | 3 | 6 | 9 | 12 | 15 | |
|---|
| 457 | * |
|---|
| 458 | * @return row wise data |
|---|
| 459 | */ |
|---|
| 460 | def convertColumnToRowStructure(columnData) { |
|---|
| 461 | |
|---|
| 462 | // check if all columns have the dimensionality 2 |
|---|
| 463 | if (columnData.every { it.value.every { it.value instanceof ArrayList } }) { |
|---|
| 464 | |
|---|
| 465 | def headers = [[],[]] |
|---|
| 466 | |
|---|
| 467 | columnData.each { category -> |
|---|
| 468 | |
|---|
| 469 | if (category.value.size()) { |
|---|
| 470 | |
|---|
| 471 | // put category keys into first row separated by null values |
|---|
| 472 | // wherever there are > 1 columns per category |
|---|
| 473 | headers[0] += [category.key] + [null] * (category.value.size() - 1) |
|---|
| 474 | |
|---|
| 475 | // put non-category column headers into 2nd row |
|---|
| 476 | headers[1] += category.value.collect{it.key} |
|---|
| 477 | |
|---|
| 478 | } |
|---|
| 479 | |
|---|
| 480 | } |
|---|
| 481 | |
|---|
| 482 | def d = [] |
|---|
| 483 | |
|---|
| 484 | // add all column wise data into 'd' |
|---|
| 485 | columnData.each { it.value.each { d << it.value } } |
|---|
| 486 | |
|---|
| 487 | // transpose d into row wise data and combine with header rows |
|---|
| 488 | headers + d.transpose() |
|---|
| 489 | } |
|---|
| 490 | |
|---|
| 491 | } |
|---|
| 492 | |
|---|
| 493 | /** |
|---|
| 494 | * Export column wise data in Excel format to a stream. |
|---|
| 495 | * |
|---|
| 496 | * @param columnData Multidimensional map containing column data |
|---|
| 497 | * @param outputStream Stream to write to |
|---|
| 498 | * @param useOfficeOpenXML Flag to specify xlsx (standard) or xls output |
|---|
| 499 | * @return |
|---|
| 500 | */ |
|---|
| 501 | def exportColumnWiseDataToExcelFile(columnData, outputStream, useOfficeOpenXML = true) { |
|---|
| 502 | |
|---|
| 503 | // transform data into row based structure for easy writing |
|---|
| 504 | def rows = convertColumnToRowStructure(columnData) |
|---|
| 505 | |
|---|
| 506 | if (rows) { |
|---|
| 507 | |
|---|
| 508 | exportRowWiseDataToExcelFile(rows, outputStream, useOfficeOpenXML) |
|---|
| 509 | |
|---|
| 510 | } else { |
|---|
| 511 | |
|---|
| 512 | throw new Exception('Wrong column data format.') |
|---|
| 513 | |
|---|
| 514 | } |
|---|
| 515 | |
|---|
| 516 | } |
|---|
| 517 | |
|---|
| 518 | /** |
|---|
| 519 | * Export row wise data in Excel format to a stream |
|---|
| 520 | * |
|---|
| 521 | * @param rowData List of lists containing for each row all cell values |
|---|
| 522 | * @param outputStream Stream to write to |
|---|
| 523 | * @param useOfficeOpenXML Flag to specify xlsx (standard) or xls output |
|---|
| 524 | * @return |
|---|
| 525 | */ |
|---|
| 526 | def exportRowWiseDataToExcelFile(rowData, outputStream, useOfficeOpenXML = true) { |
|---|
| 527 | Workbook wb = useOfficeOpenXML ? new XSSFWorkbook() : new HSSFWorkbook() |
|---|
| 528 | Sheet sheet = wb.createSheet() |
|---|
| 529 | |
|---|
| 530 | exportRowWiseDataToExcelSheet( rowData, sheet ); |
|---|
| 531 | |
|---|
| 532 | wb.write(outputStream) |
|---|
| 533 | outputStream.close() |
|---|
| 534 | } |
|---|
| 535 | |
|---|
| 536 | /** |
|---|
| 537 | * Export row wise data in CSV to a stream. All values are surrounded with |
|---|
| 538 | * double quotes (" "). |
|---|
| 539 | * |
|---|
| 540 | * @param rowData List of lists containing for each row all cell values |
|---|
| 541 | * @param outputStream Stream to write to |
|---|
| 542 | * @return |
|---|
| 543 | */ |
|---|
| 544 | def exportRowWiseDataToCSVFile(rowData, outputStream, outputDelimiter = '\t', locale = java.util.Locale.US) { |
|---|
| 545 | |
|---|
| 546 | def formatter = NumberFormat.getNumberInstance(locale) |
|---|
| 547 | formatter.setGroupingUsed false // we don't want grouping (thousands) separators |
|---|
| 548 | |
|---|
| 549 | outputStream << rowData.collect { row -> |
|---|
| 550 | row.collect{ |
|---|
| 551 | |
|---|
| 552 | // omit quotes in case of numeric values and format using chosen locale |
|---|
| 553 | if (it instanceof Number) return formatter.format(it) |
|---|
| 554 | |
|---|
| 555 | def s = it?.toString() ?: '' |
|---|
| 556 | |
|---|
| 557 | def addQuotes = false |
|---|
| 558 | |
|---|
| 559 | // escape double quotes with double quotes if they exist and |
|---|
| 560 | // enable surround with quotes |
|---|
| 561 | if (s.contains('"')) { |
|---|
| 562 | addQuotes = true |
|---|
| 563 | s = s.replaceAll('"','""') |
|---|
| 564 | } else { |
|---|
| 565 | // enable surround with quotes in case of comma's |
|---|
| 566 | if (s.contains(',') || s.contains('\n')) addQuotes = true |
|---|
| 567 | } |
|---|
| 568 | |
|---|
| 569 | addQuotes ? "\"$s\"" : s |
|---|
| 570 | |
|---|
| 571 | }.join(outputDelimiter) |
|---|
| 572 | }.join('\n') |
|---|
| 573 | |
|---|
| 574 | outputStream.close() |
|---|
| 575 | } |
|---|
| 576 | |
|---|
| 577 | /** |
|---|
| 578 | * Export row wise data for multiple assays in Excel format (separate sheets) to a stream |
|---|
| 579 | * |
|---|
| 580 | * @param rowData List of structures with rowwise data for each assay |
|---|
| 581 | * @param outputStream Stream to write to |
|---|
| 582 | * @param useOfficeOpenXML Flag to specify xlsx (standard) or xls output |
|---|
| 583 | * @return |
|---|
| 584 | */ |
|---|
| 585 | def exportRowWiseDataForMultipleAssaysToExcelFile(assayData, outputStream, useOfficeOpenXML = true) { |
|---|
| 586 | Workbook wb = useOfficeOpenXML ? new XSSFWorkbook() : new HSSFWorkbook() |
|---|
| 587 | |
|---|
| 588 | assayData.each { rowData -> |
|---|
| 589 | Sheet sheet = wb.createSheet() |
|---|
| 590 | |
|---|
| 591 | exportRowWiseDataToExcelSheet( rowData, sheet ); |
|---|
| 592 | } |
|---|
| 593 | |
|---|
| 594 | wb.write(outputStream) |
|---|
| 595 | outputStream.close() |
|---|
| 596 | } |
|---|
| 597 | |
|---|
| 598 | /** |
|---|
| 599 | * Export row wise data in Excel format to a given sheet in an excel workbook |
|---|
| 600 | * |
|---|
| 601 | * @param rowData List of lists containing for each row all cell values |
|---|
| 602 | * @param sheet Excel sheet to append the |
|---|
| 603 | * @return |
|---|
| 604 | */ |
|---|
| 605 | def exportRowWiseDataToExcelSheet(rowData, Sheet sheet) { |
|---|
| 606 | // create all rows |
|---|
| 607 | rowData.size().times { sheet.createRow it } |
|---|
| 608 | |
|---|
| 609 | sheet.eachWithIndex { Row row, ri -> |
|---|
| 610 | if( rowData[ ri ] ) { |
|---|
| 611 | // create appropriate number of cells for this row |
|---|
| 612 | rowData[ri].size().times { row.createCell it } |
|---|
| 613 | |
|---|
| 614 | row.eachWithIndex { Cell cell, ci -> |
|---|
| 615 | |
|---|
| 616 | // Numbers and values of type boolean, String, and Date can be |
|---|
| 617 | // written as is, other types need converting to String |
|---|
| 618 | def value = rowData[ri][ci] |
|---|
| 619 | |
|---|
| 620 | value = (value instanceof Number | value?.class in [boolean.class, String.class, Date.class]) ? value : value?.toString() |
|---|
| 621 | |
|---|
| 622 | // write the value (or an empty String if null) to the cell |
|---|
| 623 | cell.setCellValue(value ?: '') |
|---|
| 624 | |
|---|
| 625 | } |
|---|
| 626 | } |
|---|
| 627 | } |
|---|
| 628 | } |
|---|
| 629 | } |
|---|
