Changeset 1790
- Timestamp:
- Apr 21, 2011, 3:49:23 PM (11 years ago)
- Location:
- trunk
- Files:
-
- 8 edited
- 1 moved
Legend:
- Unmodified
- Added
- Removed
-
trunk/grails-app/controllers/dbnp/studycapturing/AssayController.groovy
r1761 r1790 120 120 } 121 121 122 def excelExportFlow = {122 def assayExportFlow = { 123 123 entry { 124 124 action{ … … 134 134 135 135 // check if assay exists 136 if (!flow.assay) throw new Exception("No assay found with id: ${ flow.assay.id}")136 if (!flow.assay) throw new Exception("No assay found with id: ${params.assayId}") 137 137 138 138 // obtain fields for each category … … 173 173 def assayData = assayService.collectAssayData(flow.assay, fieldMapSelection, measurementTokens) 174 174 flow.rowData = assayService.convertColumnToRowStructure(assayData) 175 flow.assayDataPreview = flow.rowData[0..4].collect{ it[0..4] as ArrayList } 175 176 def previewRows = Math.min(flow.rowData.size() as int, 5) - 1 177 def previewCols = Math.min(flow.rowData[0].size() as int, 5) - 1 178 179 flow.assayDataPreview = flow.rowData[0..previewRows].collect{ it[0..previewCols] as ArrayList } 176 180 177 181 }.to "compileExportData" … … 194 198 } 195 199 200 /** 201 * Export the row data in session.rowData to the outputStream of the http 202 * response. 203 */ 196 204 def doExport = { 197 205 198 def filename = 'export. xlsx'206 def filename = 'export.csv' 199 207 response.setHeader("Content-disposition", "attachment;filename=\"${filename}\"") 200 208 response.setContentType("application/octet-stream") 201 209 try { 202 210 203 def file = fileService.get('tempAssayExportFile') 204 def os = file.newOutputStream() 205 206 assayService.exportRowWiseDataToExcelFile(session.rowData, os) 207 os.flush() 208 209 response.outputStream << file.newInputStream() 210 211 file.delete() 211 // assayService.exportRowWiseDataToExcelFile(session.rowData, response.outputStream) 212 assayService.exportRowWiseDataToCSVFile(session.rowData, response.outputStream) 213 214 // clear the data from the session 215 session.removeAttribute('rowData') 212 216 213 217 } catch (Exception e) { … … 345 349 346 350 def errorPage = { 347 render(view: ' excelExport/errorPage')351 render(view: 'assayExport/errorPage') 348 352 } 349 353 } -
trunk/grails-app/controllers/dbnp/studycapturing/StudyController.groovy
r1768 r1790 1 1 package dbnp.studycapturing 2 2 3 import grails.converters.*4 3 import grails.plugins.springsecurity.Secured 5 4 import org.dbnp.gdt.TemplateFieldType 6 5 import org.dbnp.gdt.RelTime 7 6 import grails.converters.JSON 8 7 9 8 /** … … 383 382 response.contentType = 'application/json' 384 383 385 render study?.assays?.collect{[name: it.name, id: it.id]} as JSON384 render ((study?.assays?.collect{[name: it.name, id: it.id]} ?: []) as JSON) 386 385 } 387 386 -
trunk/grails-app/services/dbnp/studycapturing/AssayService.groovy
r1752 r1790 16 16 import org.apache.poi.xssf.usermodel.XSSFWorkbook 17 17 import org.apache.poi.hssf.usermodel.HSSFWorkbook 18 import org.codehaus.groovy.grails.web.json.JSONObject; 18 import org.codehaus.groovy.grails.web.json.JSONObject 19 import org.dbnp.gdt.RelTime 20 import org.dbnp.gdt.TemplateFieldType; 19 21 20 22 class AssayService { … … 39 41 templateEntities*.giveFields().flatten().unique().findAll{ field -> 40 42 41 field && templateEntities.any { it && it.fieldExists(field.name) && it.getFieldValue(field.name) } 42 43 }.collect{[name: it.name, comment: it.comment]} 44 43 field && templateEntities.any { it?.fieldExists(field.name) && it.getFieldValue(field.name) != null } 44 45 }.collect{[name: it.name, comment: it.comment, displayName: it.name + (it.unit ? " ($it.unit)" : '')]} 45 46 } 46 47 … … 49 50 'Sampling Event Data' : getUsedTemplateFields( samples*."parentEvent".unique() ), 50 51 'Sample Data' : getUsedTemplateFields( samples ), 51 'Event Group' : [[name: 'name', comment: 'Name of Event Group' ]],52 52 'Event Group' : [[name: 'name', comment: 'Name of Event Group', displayName: 'name']], 53 53 54 // If module is not reachable, only the field 'module error' is returned, and is filled later on. 54 55 'Module Measurement Data': moduleCommunicationService.isModuleReachable(assay.module.url) ? requestModuleMeasurementNames(assay) : [ [ name: "Module error" ] ] … … 72 73 def collectAssayData(assay, fieldMap, measurementTokens) throws Exception { 73 74 74 def collectFieldValuesForTemplateEntities = { templateFieldNames, templateEntities ->75 def collectFieldValuesForTemplateEntities = { headerFields, templateEntities -> 75 76 76 77 // return a hash map with for each field name all values from the 77 78 // template entity list 78 templateFieldNames.inject([:]) { map, fieldName -> 79 80 map + [(fieldName): templateEntities.collect { 81 82 it?.fieldExists(fieldName) ? it.getFieldValue(fieldName) : '' 83 84 }] 85 86 } 87 88 } 89 90 def getFieldValues = { templateEntities, fieldNames, propertyName = '' -> 79 headerFields.inject([:]) { map, headerField -> 80 81 map + [(headerField.displayName): templateEntities.collect { 82 83 // default to an empty string 84 def val = '' 85 86 def field 87 try { 88 89 val = it.getFieldValue(headerField.name) 90 91 // Convert RelTime fields to human readable strings 92 field = it.getField(headerField.name) 93 if (field.type == TemplateFieldType.RELTIME) 94 val = new RelTime( val as long ) 95 96 } catch (NoSuchFieldException e) { /* pass */ } 97 98 val.toString()}] 99 } 100 } 101 102 def getFieldValues = { templateEntities, headerFields, propertyName = '' -> 91 103 92 104 def returnValue … … 96 108 if (propertyName == '') { 97 109 98 returnValue = collectFieldValuesForTemplateEntities( fieldNames, templateEntities)110 returnValue = collectFieldValuesForTemplateEntities(headerFields, templateEntities) 99 111 100 112 } else { … … 114 126 def uniqueProperties = templateEntities*."$propertyName".unique() 115 127 116 fieldValues = collectFieldValuesForTemplateEntities( fieldNames, uniqueProperties)128 fieldValues = collectFieldValuesForTemplateEntities(headerFields, uniqueProperties) 117 129 118 130 // prepare a lookup hashMap to be able to map an entities' … … 124 136 // prepare the return value so that it has an entry for field 125 137 // name. This will be the column name (second header line). 126 returnValue = fieldNames.inject([:]) { map, item -> map + [(item):[]] }138 returnValue = headerFields*.displayName.inject([:]) { map, item -> map + [(item):[]] } 127 139 128 140 // finally, fill map the unique field values to the (possibly … … 132 144 templateEntities.each{ te -> 133 145 134 fieldNames.each{146 headerFields*.displayName.each{ 135 147 136 148 returnValue[it] << fieldValues[it][propertyToFieldValueIndexMap[te[propertyName]]] … … 159 171 if (!names.every {!it}) eventFieldMap['name'] = names 160 172 161 } 162 163 [ 'Subject Data' : getFieldValues(samples, fieldMap['Subject Data']*.name, 'parentSubject'),164 'Sampling Event Data' : getFieldValues(samples, fieldMap['Sampling Event Data']*.name, 'parentEvent'),165 'Sample Data' : getFieldValues(samples, fieldMap['Sample Data']*.name),166 167 168 169 170 171 173 } 174 175 [ 'Subject Data' : getFieldValues(samples, fieldMap['Subject Data'], 'parentSubject'), 176 'Sampling Event Data' : getFieldValues(samples, fieldMap['Sampling Event Data'], 'parentEvent'), 177 'Sample Data' : getFieldValues(samples, fieldMap['Sample Data']), 178 'Event Group' : eventFieldMap, 179 180 // If module is not reachable, only the message 'module not reachable' is given for each sample 181 'Module Measurement Data': moduleCommunicationService.isModuleReachable(assay.module.url) ? 182 ( measurementTokens ? requestModuleMeasurements(assay, measurementTokens, samples) : [:] ) : 183 [ "Module error": [ "Module not reachable" ] * samples.size() ] 172 184 ] 173 185 } 174 186 175 187 /** 176 188 * Prepend data from study to the data structure … … 183 195 if( !assay ) 184 196 return inputData; 185 197 186 198 // Retrieve study data 187 199 def studyData =[:] 188 assay.parent?.giveFields().each { 200 assay.parent?.giveFields().each { 189 201 def value = assay.parent.getFieldValue( it.name ) 190 if( value ) 191 studyData[ it.name ] = [value] * numValues 202 if( value ) 203 studyData[ it.name ] = [value] * numValues 192 204 } 193 205 … … 207 219 if( !assay ) 208 220 return inputData; 209 221 210 222 // Retrieve assay data 211 223 def assayData = [:] 212 224 assay.giveFields().each { 213 225 def value = assay.getFieldValue( it.name ) 214 if( value ) 215 assayData[ it.name ] = [value] * numValues 226 if( value ) 227 assayData[ it.name ] = [value] * numValues 216 228 } 217 229 … … 250 262 * @param assay Assay for which the module measurements should be retrieved 251 263 * @param measurementTokens List with the names of the fields to be retrieved. Format: [ 'measurementName1', 'measurementName2' ] 252 * @param samples Samples for which the module 264 * @param samples Samples for which the module 253 265 * @return 254 266 */ … … 309 321 /** 310 322 * Merges the data from multiple studies into a structure that can be exported to an excel file. The format for each assay is 311 * 323 * 312 324 * [Category1: 313 325 * [Column1: [1,2,3], Column2: [4,5,6]], 314 326 * Category2: 315 327 * [Column3: [7,8,9], Column4: [10,11,12], Column5: [13,14,15]]] 316 * 328 * 317 329 * Where the category describes the category of data that is presented (e.g. subject, sample etc.) and the column names describe 318 330 * 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. 319 * Each field should give values for all entities, so the length of all value-lists should be the same. 331 * Each field should give values for all entities, so the length of all value-lists should be the same. 320 332 * 321 333 * Example: If the following input is given (2 assays) 322 * 334 * 323 335 * [ 324 336 * [Category1: … … 331 343 * [Column3: [20,21], Column8: [22,23]]] 332 344 * ] 333 * 345 * 334 346 * the output will be (5 entries for each column, empty values for fields that don't exist in some assays) 335 * 347 * 336 348 * [ 337 349 * [Category1: … … 342 354 * [Column3: [,,,20,21], Column8: [,,,22,23]] 343 355 * ] 344 * 345 * 356 * 357 * 346 358 * @param columnWiseAssayData List with each entry being the column wise data of an assay. The format for each 347 359 * entry is described above … … 363 375 } 364 376 } 365 377 366 378 return 0; 367 379 } 368 380 369 381 // Merge categories from all assays. Create a list for all categories 370 382 def categories = columnWiseAssayData*.keySet().toList().flatten().unique(); … … 373 385 // Only work with this category for all assays 374 386 def categoryData = columnWiseAssayData*.getAt( category ); 375 387 376 388 // Find the different fields in all assays 377 389 def categoryFields = categoryData.findAll{ it }*.keySet().toList().flatten().unique(); … … 396 408 mergedColumnWiseData[ category ] = categoryValues 397 409 } 398 410 399 411 return mergedColumnWiseData; 400 412 } 401 413 402 414 /** 403 415 * Converts column … … 501 513 502 514 /** 515 * Export row wise data in CSV to a stream. All values are surrounded with 516 * double quotes (" "). 517 * 518 * @param rowData List of lists containing for each row all cell values 519 * @param outputStream Stream to write to 520 * @return 521 */ 522 def exportRowWiseDataToCSVFile(rowData, outputStream) { 523 524 outputStream << rowData.collect { row -> 525 row.collect{ it ? "\"$it\"" : '""' }.join(',') 526 }.join('\n') 527 528 outputStream.close() 529 } 530 531 /** 503 532 * Export row wise data for multiple assays in Excel format (separate sheets) to a stream 504 533 * … … 525 554 * 526 555 * @param rowData List of lists containing for each row all cell values 527 * @param sheet Excel sheet to append the 556 * @param sheet Excel sheet to append the 528 557 * @return 529 558 */ … … 536 565 // create appropriate number of cells for this row 537 566 rowData[ri].size().times { row.createCell it } 538 567 539 568 row.eachWithIndex { Cell cell, ci -> 540 569 541 570 // Numbers and values of type boolean, String, and Date can be 542 571 // written as is, other types need converting to String 543 572 def value = rowData[ri][ci] 544 573 545 574 value = (value instanceof Number | value?.class in [boolean.class, String.class, Date.class]) ? value : value?.toString() 546 575 547 576 // write the value (or an empty String if null) to the cell 548 577 cell.setCellValue(value ?: '') 549 578 550 579 } 551 580 } -
trunk/grails-app/views/assay/assayExport/compileExportData.gsp
r1716 r1790 42 42 </table> 43 43 44 <g:link action=" excelExport" event="ok">OK</g:link>45 <g:link action=" excelExport" event="cancel">Cancel</g:link>44 <g:link action="assayExport" event="ok">OK</g:link> 45 <g:link action="assayExport" event="cancel">Cancel</g:link> 46 46 47 47 </body> -
trunk/grails-app/views/assay/assayExport/selectAssay.gsp
r1716 r1790 15 15 var a = eval(jsonData); 16 16 var sel = $('#'+selectID).empty(); 17 18 $('#submit').attr("disabled", a.length == 0); 17 19 18 20 $(a).each(function(i, el){ … … 36 38 <h1>Select the assay you want to export data from</h1> 37 39 38 <g:form name="assaySelect" action="excelExport"> 40 With this exporter you can export (meta) data about samples from an assay to an Excel file. 41 First, select a study from the first list and then select an assay from that study from the second list.<br /> 42 43 <g:form name="assaySelect" action="assayExport"> 39 44 <g:select optionKey="id" optionValue="title" name="studyId" from="${userStudies}" id="study" 40 45 onChange="${remoteFunction(controller:'study',action:'ajaxGetAssays',params:'\'id=\'+escape(this.value)',onComplete: 'updateAssay(XMLHttpRequest.responseText, \'assay\')')}"/> 41 46 <g:select optionKey="id" name="assayId" id="assay" from=""/> 42 <g:submitButton name="submit" value="Submit" />47 <g:submitButton name="submit" value="Submit" id="submit"/> 43 48 </g:form> 44 49 </body> -
trunk/grails-app/views/assay/assayExport/selectFields.gsp
r1716 r1790 40 40 <h1>Select the columns that you want to be included in the resulting Excel file</h1> 41 41 42 <g:form name="fieldSelectForm" action="excelExport"> 42 In this step you can make a selection from the available fields stored in the database related to the samples, including measurement data from a module (if available). 43 44 <g:form name="fieldSelectForm" action="assayExport"> 43 45 44 46 <g:set var="catNum" value="${0}"/> -
trunk/grails-app/views/common/_topnav.gsp
r1764 r1790 26 26 <a href="#">Assays</a> 27 27 <ul class="subnav"> 28 <li><g:link controller="assay" action=" excelExport">Export Data to Excel</g:link> </li>28 <li><g:link controller="assay" action="assayExport">Export Data</g:link> </li> 29 29 </ul> 30 30 </li> -
trunk/test/unit/dbnp/studycapturing/AssayServiceTests.groovy
r1659 r1790 233 233 } 234 234 235 // // Test for out of memory exception when exporting large excel workbooks 236 // // - xls format can handle max 256 columns 237 // // - but, xls format can handle more data (1000000 cells, no problem -> 27.2 MB) 238 // // - we'll need a good method to overcome the xlsx heap space problem 239 // void testExportLargeExcelWorkbook() { 240 // 241 // def file = new File( '/tmp', 'tmpFile.xls' ) 242 // 243 // def os = file.newOutputStream() 244 // 245 // def rowData = (0..1).collect { row -> 246 // 247 // (0..256).collect { col -> 248 // "$row - $col" 249 // } 250 // 251 // } 252 // 253 //// try { 254 // service.exportRowWiseDataToExcelFile rowData, os, false 255 //// } catch (Exception e) { 256 //// e.printStackTrace() 257 //// assert false 258 //// } finally { 259 // os.flush() 260 //// file.delete() 261 //// } 262 // } 263 264 235 265 }
Note: See TracChangeset
for help on using the changeset viewer.