Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -r082bf8f436f01910fa9188202cf1d54b7208ee73 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision 082bf8f436f01910fa9188202cf1d54b7208ee73) @@ -18,6 +18,13 @@ cells.add(cell); return cell; } + + public ExcelCell addPercentageCell(Object cellValue) { + ExcelCell cell = new ExcelCell(cellValue); + cell.setCellFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); + cells.add(cell); + return cell; + } public ExcelCell addCell(Object cellValue, Boolean isBold) { ExcelCell cell = new ExcelCell(cellValue, isBold); Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -r082bf8f436f01910fa9188202cf1d54b7208ee73 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision 082bf8f436f01910fa9188202cf1d54b7208ee73) @@ -60,15 +60,14 @@ */ public class ExcelUtil { //other built in formats https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html - private static final String FORMAT_PERCENTAGE = "0.00%"; + private static final String FORMAT_PERCENTAGE = "0%"; + private static short numberFormat; + private static short floatFormat; + private static short dateFormat; + private static short timeFormat; + private static short percentageFormat; private static CellStyle defaultStyle; - private static CellStyle numberStyle; - private static CellStyle floatStyle; - private static CellStyle dateStyle; - private static CellStyle timeStyle; - private static CellStyle percentageStyle; - private static CellStyle boldStyle; private static CellStyle greenColor; @@ -77,17 +76,13 @@ private static CellStyle yellowColor; private static CellStyle borderStyleLeftThin; - private static CellStyle borderStyleLeftThinPercentage; private static CellStyle borderStyleLeftThick; private static CellStyle borderStyleRightThick; private static CellStyle borderStyleLeftThinBoldFont; - private static CellStyle borderStyleLeftThinBoldFontPercentage; private static CellStyle borderStyleLeftThickBoldFont; private static CellStyle borderStyleRightThickBoldFont; private static CellStyle borderStyleBottomThin; private static CellStyle borderStyleBottomThinBoldFont; - private static CellStyle borderStyleRightThickPercentage; - private static CellStyle borderStyleRightThickBoldFontPercentage; public final static String DEFAULT_FONT_NAME = "Calibri-Regular"; @@ -197,25 +192,12 @@ yellowColor.setFillPattern(FillPatternType.SOLID_FOREGROUND); yellowColor.setFont(defaultFont); - // create number style - floatStyle = workbook.createCellStyle(); - floatStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00")); - - numberStyle = workbook.createCellStyle(); - numberStyle.setDataFormat(workbook.createDataFormat().getFormat("0")); - - // create date style - dateStyle = workbook.createCellStyle(); - dateStyle.setDataFormat((short)14);// built-in 0xe format - "m/d/yy" - - // create time style - timeStyle = workbook.createCellStyle(); - timeStyle.setDataFormat((short)19);// built-in 0x13 format - "h:mm:ss AM/PM" - - // create percentage style - percentageStyle = workbook.createCellStyle(); - short percentageDataFormatId = workbook.createDataFormat().getFormat(FORMAT_PERCENTAGE); - percentageStyle.setDataFormat(percentageDataFormatId); + // create data formats + floatFormat = workbook.createDataFormat().getFormat("0.00"); + numberFormat = workbook.createDataFormat().getFormat("0"); + dateFormat = (short)14;// built-in 0xe format - "m/d/yy" + timeFormat = (short)19;// built-in 0x13 format - "h:mm:ss AM/PM" + percentageFormat = workbook.createDataFormat().getFormat(FORMAT_PERCENTAGE); //create border style borderStyleLeftThin = workbook.createCellStyle(); @@ -242,23 +224,6 @@ borderStyleBottomThinBoldFont = workbook.createCellStyle(); borderStyleBottomThinBoldFont.setBorderBottom(BorderStyle.THIN); borderStyleBottomThinBoldFont.setFont(boldFont); - - borderStyleLeftThinPercentage = workbook.createCellStyle(); - borderStyleLeftThinPercentage.setBorderLeft(BorderStyle.THIN); - borderStyleLeftThinPercentage.setFont(defaultFont); - borderStyleLeftThinPercentage.setDataFormat(percentageDataFormatId); - borderStyleLeftThinBoldFontPercentage = workbook.createCellStyle(); - borderStyleLeftThinBoldFontPercentage.setBorderLeft(BorderStyle.THIN); - borderStyleLeftThinBoldFontPercentage.setFont(boldFont); - borderStyleLeftThinBoldFontPercentage.setDataFormat(percentageDataFormatId); - - borderStyleRightThickPercentage = workbook.createCellStyle(); - borderStyleRightThickPercentage.setBorderRight(BorderStyle.THICK); - borderStyleRightThickPercentage.setDataFormat(percentageDataFormatId); - borderStyleRightThickBoldFontPercentage = workbook.createCellStyle(); - borderStyleRightThickBoldFontPercentage.setBorderRight(BorderStyle.THICK); - borderStyleRightThickBoldFontPercentage.setFont(boldFont); - borderStyleRightThickBoldFontPercentage.setDataFormat(percentageDataFormatId); int i = 0; for (String sheetName : dataToExport.keySet()) { @@ -364,30 +329,9 @@ //figure out cell's style CellStyle cellStyle = defaultStyle; - if (excelCellValue != null - && (excelCellValue instanceof java.lang.Integer || excelCellValue instanceof java.lang.Long)) { - cellStyle = numberStyle; - } else if (excelCellValue != null - && (excelCellValue instanceof java.lang.Float || excelCellValue instanceof java.lang.Double)) { - cellStyle = floatStyle; - } if (excelCell.isBold()) { cellStyle = boldStyle; } - switch (excelCell.getCellFormat()) { - case 0: - //default - do nothing - break; - case ExcelCell.CELL_FORMAT_DATE: - cellStyle = dateStyle; - break; - case ExcelCell.CELL_FORMAT_TIME: - cellStyle = timeStyle; - break; - case ExcelCell.CELL_FORMAT_PERCENTAGE: - cellStyle = percentageStyle; - break; - } if (excelCell.getColor() != null) { switch (excelCell.getColor()) { case BLUE: @@ -407,16 +351,10 @@ } } if (excelCell.getBorderStyle() != 0) { - boolean isPercentageFormat = excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_PERCENTAGE; - switch (excelCell.getBorderStyle()) { case ExcelCell.BORDER_STYLE_LEFT_THIN: - if (excelCell.isBold() && isPercentageFormat) { - cellStyle = borderStyleLeftThinBoldFontPercentage; - } else if (excelCell.isBold()) { + if (excelCell.isBold()) { cellStyle = borderStyleLeftThinBoldFont; - } else if (isPercentageFormat) { - cellStyle = borderStyleLeftThinPercentage; } else { cellStyle = borderStyleLeftThin; } @@ -429,12 +367,8 @@ } break; case ExcelCell.BORDER_STYLE_RIGHT_THICK: - if (excelCell.isBold() && isPercentageFormat ) { - cellStyle = borderStyleRightThickBoldFontPercentage; - } else if (excelCell.isBold() ) { + if (excelCell.isBold() ) { cellStyle = borderStyleRightThickBoldFont; - } else if (isPercentageFormat ) { - cellStyle = borderStyleRightThickPercentage; } else { cellStyle = borderStyleRightThick; } @@ -451,13 +385,27 @@ } } cell.setCellStyle(cellStyle); + + //set data format if (excelCellValue != null - && (excelCellValue instanceof java.lang.Float || (excelCellValue instanceof java.lang.Double))) { - //only float and double will have this style. int will have "General" style - //cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##")); -// cell.getCellStyle().setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0")); + && (excelCellValue instanceof java.lang.Integer || excelCellValue instanceof java.lang.Long)) { + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, numberFormat); + + } else if (excelCellValue != null + && (excelCellValue instanceof java.lang.Float || excelCellValue instanceof java.lang.Double)) { + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, floatFormat); + + } else if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_DATE) { + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dateFormat); + + } else if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_TIME) { + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, timeFormat); + } + if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_PERCENTAGE) { + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } + //set alignment if (excelCell.getAlignment() != 0) { switch (excelCell.getAlignment()) { case ExcelCell.ALIGN_GENERAL: