=== modified file 'dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/ExcelUtils.java' --- dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/ExcelUtils.java 2015-09-14 10:15:27 +0000 +++ dhis-2/dhis-support/dhis-support-system/src/main/java/org/hisp/dhis/system/util/ExcelUtils.java 2015-09-14 10:20:41 +0000 @@ -30,266 +30,16 @@ import jxl.Workbook; import jxl.WorkbookSettings; -import jxl.format.Colour; -import jxl.format.UnderlineStyle; -import jxl.write.Label; -import jxl.write.WritableCellFormat; -import jxl.write.WritableFont; -import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; -import jxl.write.WriteException; -import jxl.write.biff.RowsExceededException; -import org.hisp.dhis.common.Grid; -import org.hisp.dhis.common.GridHeader; -import org.hisp.dhis.databrowser.MetaValue; -import org.hisp.dhis.dataelement.DataElement; -import org.hisp.dhis.expression.ExpressionService; -import org.hisp.dhis.i18n.I18n; -import org.hisp.dhis.i18n.I18nFormat; -import org.hisp.dhis.indicator.Indicator; -import org.hisp.dhis.organisationunit.OrganisationUnit; import java.io.IOException; import java.io.OutputStream; -import java.util.HashMap; -import java.util.Iterator; -import java.util.List; -import java.util.Map; /** * @author Dang Duy Hieu */ public class ExcelUtils { - public static final WritableCellFormat FORMAT_LABEL = new WritableCellFormat( new WritableFont( WritableFont.ARIAL, 13, - WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK ) ); - - public static final WritableCellFormat FORMAT_TEXT = new WritableCellFormat( new WritableFont( WritableFont.ARIAL, 11, - WritableFont.NO_BOLD, false ) ); - - public static void printDataElementHeaders( WritableSheet sheet, I18n i18n, int row, - int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, i18n.getString( "name" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "short_name" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "code" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "description" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "active" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "type" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "aggregation_operator" ), FORMAT_LABEL ) ); - } - - public static void addDataElementCellToSheet( WritableSheet sheet, DataElement element, - I18n i18n, int row, int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, element.getName(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, element.getShortName(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, element.getCode(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, element.getDescription(), FORMAT_TEXT ) ); - // sheet.addCell( new Label( column++, row, getType().get( element.getType() ), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, getAggregationOperator().get( element.getAggregationOperator() ), - FORMAT_TEXT ) ); - } - - public static void printIndicatorHeaders( WritableSheet sheet, I18n i18n, int row, - int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, i18n.getString( "name" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "short_name" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "code" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "description" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "annualized" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "indicator_type" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "numerator_description" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "numerator_formula" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "denominator_description" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "denominator_formula" ), FORMAT_LABEL ) ); - - } - - public static void addIndicatorCellToSheet( WritableSheet sheet, Indicator indicator, - I18n i18n, ExpressionService expressionService, int row, int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, indicator.getName(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, indicator.getShortName(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, indicator.getCode(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, indicator.getDescription(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, getBoolean().get( indicator.isAnnualized() ), FORMAT_TEXT ) ); - // sheet.addCell( new Label( column++, row, getType().get( indicator.getIndicatorType().getName() ), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, indicator.getNumeratorDescription(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, expressionService.getExpressionDescription( indicator.getNumerator() ), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, indicator.getDenominatorDescription(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, expressionService.getExpressionDescription( indicator.getDenominator() ), FORMAT_TEXT ) ); - } - - public static void printOrganisationUnitHeaders( WritableSheet sheet, I18n i18n, - int row, int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, i18n.getString( "short_name" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "code" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "opening_date" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "closed_date" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "active" ), FORMAT_LABEL ) ); - sheet.addCell( new Label( column++, row, i18n.getString( "comment" ), FORMAT_LABEL ) ); - - } - - public static void addOrganisationUnitCellToSheet( WritableSheet sheet, - OrganisationUnit unit, I18n i18n, I18nFormat i18nFormat, int row, int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column++, row, unit.getShortName(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, unit.getCode(), FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, unit.getOpeningDate() != null ? i18nFormat.formatDate( unit.getOpeningDate() ) : "", FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, unit.getClosedDate() != null ? i18nFormat.formatDate( unit.getClosedDate() ) : "", FORMAT_TEXT ) ); - sheet.addCell( new Label( column++, row, unit.getComment(), FORMAT_TEXT ) ); - } - - public static void printOrganisationUnitHierarchyHeaders( WritableSheet sheet, - I18n i18n, int row, int column, int level ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column, row, i18n.getString( "organisation_unit_level" ), FORMAT_LABEL ) ); - sheet.mergeCells( column, row, level - 1, row ); - - row++; - - for ( int i = 1; i <= level; i++ ) - { - sheet.addCell( new Label( column++, row, (i + ""), FORMAT_TEXT ) ); - } - } - - public static void addOrganisationUnitHierarchyCellToSheet( WritableSheet sheet, - OrganisationUnit unit, I18n i18n, int row, int column ) - throws RowsExceededException, WriteException - { - sheet.addCell( new Label( column, row, unit.getName(), FORMAT_TEXT ) ); - } - - // ------------------------------------------------------------------------- - // DataBrowser - // ------------------------------------------------------------------------- - - public static void writeDataBrowserTitle( WritableSheet sheet, WritableCellFormat formatTitle, - WritableCellFormat formatSubTitle, String dataBrowserTitleName, String dataBrowserFromDate, - String dataBrowserToDate, String dataBrowserPeriodType, I18n i18n ) - { - try - { - sheet.addCell( new Label( 0, 0, i18n.getString( "export_results_for" ) + " " + dataBrowserTitleName, - formatTitle ) ); - sheet.mergeCells( 0, 0, 5, 0 ); - - if ( dataBrowserFromDate.length() == 0 ) - { - dataBrowserFromDate = i18n.getString( "earliest" ); - } - - if ( dataBrowserToDate.length() == 0 ) - { - dataBrowserToDate = i18n.getString( "latest" ); - } - - sheet.addCell( new Label( 0, 1, i18n.getString( "from_date" ) + ": " + dataBrowserFromDate + " " - + i18n.getString( "to_date" ) + ": " + dataBrowserToDate + ", " + i18n.getString( "period_type" ) - + ": " + i18n.getString( dataBrowserPeriodType ), formatSubTitle ) ); - sheet.mergeCells( 0, 1, 5, 1 ); - } - catch ( RowsExceededException e ) - { - e.printStackTrace(); - } - catch ( WriteException e ) - { - e.printStackTrace(); - } - } - - public static void writeDataBrowserHeaders( WritableSheet sheet, WritableCellFormat cellFormat, Grid grid, I18n i18n ) - { - int column = 0; - - try - { - for ( GridHeader col : grid.getVisibleHeaders() ) - { - //TODO use i18nFormat.formatDate for label - - sheet.addCell( new Label( column++, 3, col.getName(), cellFormat ) ); - } - } - catch ( RowsExceededException e ) - { - e.printStackTrace(); - } - catch ( WriteException e ) - { - e.printStackTrace(); - } - } - - public static void writeDataBrowserResults( WritableSheet sheet, WritableCellFormat parFormat, - WritableCellFormat oddFormat, int fontSize, Grid grid ) - { - // Data rows - int i = 0; - int row = 4; - int column = 0; - - WritableCellFormat cellFormat = null; - WritableFont zeroFont = new WritableFont( WritableFont.ARIAL, fontSize, WritableFont.BOLD, false, - UnderlineStyle.NO_UNDERLINE, Colour.RED ); - - Iterator rowIt = grid.getColumn( 0 ).iterator(); - - for ( List rows : grid.getRows() ) - { - i++; - MetaValue rowMeta = (MetaValue) rowIt.next(); - - cellFormat = (i % 2 != 0) ? parFormat : oddFormat; - - try - { - sheet.addCell( new Label( column++, row, rowMeta.getName(), cellFormat ) ); - - for ( Object rowItem : rows ) - { - String temp = (String) rowItem; - - if ( temp == null ) - { - temp = ""; - } - else if ( temp.trim().matches( "0" ) ) - { - cellFormat.setFont( zeroFont ); - } - - // Color zero values as bold red - sheet.addCell( new Label( column++, row, temp, cellFormat ) ); - } - } - catch ( RowsExceededException e ) - { - e.printStackTrace(); - } - catch ( WriteException e ) - { - e.printStackTrace(); - } - - row++; - column = 0; - } - } - /** * Creates a writable workbook. * @@ -311,60 +61,4 @@ throw new RuntimeException( "Failed to open a writable workbook", e ); } } - - /** - * @throws IOException - * @throws WriteException - */ - public static void writeAndCloseWorkbook( WritableWorkbook workbook ) - { - if ( workbook != null ) - { - try - { - workbook.write(); - workbook.close(); - } - catch ( IOException ioe ) - { - throw new RuntimeException( "Failed to write data to workbook", ioe ); - } - catch ( WriteException we ) - { - throw new RuntimeException( "Failed to close the workbook", we ); - } - } - } - - // ------------------------------------------------------------------------- - // Supportive methods - // ------------------------------------------------------------------------- - - private static Map getBoolean() - { - Map map = new HashMap<>(); - map.put( true, "Yes" ); - map.put( false, "No" ); - return map; - } - - /* - private static Map getType() - { - Map map = new HashMap<>(); - map.put( DataElement.VALUE_TYPE_STRING, "Text" ); - map.put( DataElement.VALUE_TYPE_INT, "Number" ); - map.put( DataElement.VALUE_TYPE_BOOL, "Yes/No" ); - return map; - } - */ - - private static Map getAggregationOperator() - { - Map map = new HashMap<>(); - map.put( DataElement.AGGREGATION_OPERATOR_SUM, "Sum" ); - map.put( DataElement.AGGREGATION_OPERATOR_AVERAGE_SUM, "Average" ); - map.put( DataElement.AGGREGATION_OPERATOR_COUNT, "Count" ); - return map; - } }