Excel Report Generator which is used to create the attached sample excel file

ExcelReportGenerator.java (text/java), 29.08 KB, created by Partha on 2008-07-01 17:11:39 UTC

(hide)

Description: Excel Report Generator which is used to create the attached sample excel file

Filename:

MIME Type:

Creator: Partha

Created: 2008-07-01 17:11:39 UTC

Size: 29.08 KB

patch

obsolete

>package com.jbhunt.report.generator; > >import java.io.ByteArrayOutputStream; >import java.io.IOException; >import java.math.BigDecimal; >import java.sql.Date; >import java.sql.ResultSetMetaData; >import java.sql.SQLException; >import java.sql.Time; >import java.sql.Timestamp; >import java.sql.Types; >import java.util.List; > >import javax.sql.RowSet; > >import org.apache.commons.logging.Log; >import org.apache.commons.logging.LogFactory; >import org.apache.poi.hssf.usermodel.HSSFCell; >import org.apache.poi.hssf.usermodel.HSSFCellStyle; >import org.apache.poi.hssf.usermodel.HSSFDataFormat; >import org.apache.poi.hssf.usermodel.HSSFFont; >import org.apache.poi.hssf.usermodel.HSSFRichTextString; >import org.apache.poi.hssf.usermodel.HSSFRow; >import org.apache.poi.hssf.usermodel.HSSFSheet; >import org.apache.poi.hssf.usermodel.HSSFWorkbook; >import org.apache.poi.hssf.util.HSSFColor; >import org.apache.poi.hssf.util.Region; > >import com.jbhunt.report.exception.ReportServiceException; >import com.jbhunt.report.util.ReportUtils; >import com.jbhunt.report.vo.ReportFooterVO; >import com.jbhunt.report.vo.ReportHeaderVO; > >/** > * @author jisaps1 > * > */ >public class ExcelReportGenerator implements ReportGenerator { > private static Log log = LogFactory.getLog(ExcelReportGenerator.class); > > private HSSFCellStyle titleStyle; > private HSSFCellStyle stringHeaderStyle; > private HSSFCellStyle dateHeaderStyle; > private HSSFCellStyle columnHeaderStyle; > private HSSFCellStyle columnHeaderRightStyle; > private HSSFCellStyle columnHeaderCenterStyle; > private HSSFCellStyle columnHeaderLeftStyle; > private HSSFCellStyle stringStyle; > private HSSFCellStyle dateStyle; > private HSSFCellStyle timeStyle; > private HSSFCellStyle numberStyle; > private HSSFFont dataCellFont; > private HSSFCellStyle columnValueStyles[]; > int formatFontCount=0; > > public ExcelReportGenerator() { > } > public byte[] generateReport( > ReportHeaderVO headerVo, List reportDataRowSetList, > ReportFooterVO footerVo, boolean isRotate) > throws ReportServiceException { > HSSFWorkbook wb = new HSSFWorkbook(); > ByteArrayOutputStream baos = new ByteArrayOutputStream(); > try { > wb = generateHSSFWorkbook(wb, headerVo, reportDataRowSetList, footerVo); > wb.write(baos); > baos.close(); > } catch (Exception e) { > e.printStackTrace(); > throw new ReportServiceException("Error in generate Excel report.", e); > } > > return baos.toByteArray(); > } > > private HSSFWorkbook generateHSSFWorkbook(HSSFWorkbook wb, > ReportHeaderVO headerVo, List reportDataRowSetList, > ReportFooterVO footerVo) > throws SQLException, IOException, ReportServiceException { > if (reportDataRowSetList == null || reportDataRowSetList.size()<=0) { > log.error("generateHSSFWorkbook(...)reportDataRowSetList is emptty . Cannot generate Excel file"); > throw new ReportServiceException("generateHSSFWorkbook(...)reportDataRowSet is null. Cannot generate Excel file"); > } > > /* Create Work Book and Work Sheet */ > if(wb==null){ > wb = new HSSFWorkbook(); > } > List reportTitle = headerVo.getTitles(); > HSSFSheet reportSheet = wb.createSheet(); > /* Freeze First Row and First Column */ > // reportSheet.createFreezePane(1, 1, 1, 1); > /* Need to generate header portion for account specific reports */ > int rowIndex=0; > //Allways reads the first RowSet and uses this for header > RowSet firstReportDataRowSet = (RowSet)reportDataRowSetList.get(0); > ResultSetMetaData metaData = firstReportDataRowSet.getMetaData(); > rowIndex = generateTitle(wb,reportSheet,rowIndex, > metaData.getColumnCount(),reportTitle); > this.setColumnWidths(reportSheet,metaData); > rowIndex = generateHeader(wb,reportSheet,rowIndex, > metaData.getColumnCount(),headerVo); > > /* Generate Column Name and column values */ > rowIndex = generateExcelColumnNames(wb,rowIndex, reportSheet,metaData ); > int rowIndexToFreeze = rowIndex; > rowIndex = generateExcelColumnValues(wb,rowIndex, reportSheet, reportDataRowSetList); > rowIndex = generateFooter(wb,reportSheet,rowIndex, > metaData.getColumnCount(),footerVo); > this.setAutoColumnWidths(reportSheet,metaData); > reportSheet.createFreezePane(0, rowIndexToFreeze); > return wb; > } > > private int generateTitle(HSSFWorkbook wb, HSSFSheet reportSheet, > int rowIndex, int dataColumnCount, List titlesList) > throws ReportServiceException { > > if (reportSheet == null) { > log.error("generateTitle(..) -> HSSFSheet cannot be null"); > throw new ReportServiceException( > "generateTitle(..) -> HSSFSheet cannot be null"); > } > if (dataColumnCount < 5) { > dataColumnCount = 8; > } > int titleCellIndex; > if (dataColumnCount % 2 == 0) > titleCellIndex = (dataColumnCount / 2) - 1; > else { > titleCellIndex = (dataColumnCount / 2); > } >// This will avoid autosizing table columns which is starting at the title column. > Region mergeRegion = new Region(rowIndex,(short)titleCellIndex, > rowIndex,(short)(titleCellIndex+10)); > reportSheet.addMergedRegion(mergeRegion); > HSSFRow row = reportSheet.createRow(rowIndex++); > if(titlesList!=null && titlesList.size()>0){ > String title=(String)titlesList.get(0); > HSSFCell cell = row.createCell((short) titleCellIndex); > cell.setCellValue(new HSSFRichTextString(title)); > cell.setCellStyle(getTitleStyle(wb)); > > for(int i=1;i<titlesList.size();i++){ >// This will avoid autosizing table columns which is starting at the title column. > mergeRegion = new Region(rowIndex,(short)titleCellIndex, > rowIndex,(short)(titleCellIndex+10)); > reportSheet.addMergedRegion(mergeRegion); > > title=(String)titlesList.get(i); > row = reportSheet.createRow(rowIndex++); > cell = row.createCell((short) titleCellIndex); > cell.setCellValue(new HSSFRichTextString(title)); > cell.setCellStyle(getStringHeaderStyle(wb)); > } > row = reportSheet.createRow(rowIndex++); > } > return rowIndex; > } > private int generateTitle(HSSFWorkbook wb, > HSSFSheet reportSheet, int rowIndex,int dataColumnCount, > String title ) throws ReportServiceException{ > > if(reportSheet==null){ > log.error("generateTitle(..) -> HSSFSheet cannot be null"); > throw new ReportServiceException("generateTitle(..) -> HSSFSheet cannot be null"); > } > if(dataColumnCount<5){ > dataColumnCount=8; > } > int titleCellIndex; > if(dataColumnCount%2==0) > titleCellIndex=(dataColumnCount/2)-1; > else{ > titleCellIndex=(dataColumnCount/2); > } > HSSFRow row = reportSheet.createRow(rowIndex++); > HSSFCell cell = row.createCell((short) titleCellIndex); > cell.setCellValue(new HSSFRichTextString(title)); > cell.setCellStyle(getTitleStyle(wb)); > > row = reportSheet.createRow(rowIndex++); > return rowIndex; > } > private int generateHeader(HSSFWorkbook wb, > HSSFSheet reportSheet, int rowIndex,int dataColumnCount, > ReportHeaderVO headerVo > ) throws ReportServiceException { > > if (reportSheet == null) { > log.error("generateHeader(..) -> HSSFSheet cannot be null"); > throw new ReportServiceException( > "generateHeader(..) -> HSSFSheet cannot be null"); > } > List column1Values = headerVo.getColumn1Values(); > List column2Values = headerVo.getColumn2Values(); > if(column1Values == null && column1Values ==null){ > return rowIndex; > } > int column1ValuesCount, column2ValuesCount; > if(column1Values==null){ > column1ValuesCount=0; > }else{ > column1ValuesCount = column1Values.size(); > } > if(column2Values==null){ > column2ValuesCount=0; > }else{ > column2ValuesCount = column2Values.size(); > } > > if (dataColumnCount < 5) { > dataColumnCount = 8; > } > int maxHeaderRowCount=0; > maxHeaderRowCount=column1ValuesCount; > if(column2ValuesCount>column1ValuesCount){ > maxHeaderRowCount=column2ValuesCount; > } > int column1CellIndex = 0; > int column2CellIndex=6; > > for(int index=0;index<maxHeaderRowCount;index++){ >// This will avoid autosizing table columns which is starting at the header column1 or header column2. > Region mergeRegionCol1 = new Region(rowIndex,(short)0,rowIndex,(short)(column2CellIndex-1)); > Region mergeRegionCol5 = new Region(rowIndex,(short)column2CellIndex,rowIndex,(short)(column2CellIndex*2)); > reportSheet.addMergedRegion(mergeRegionCol1); > reportSheet.addMergedRegion(mergeRegionCol5); > HSSFRow headerRow = reportSheet.createRow(rowIndex++); > if(column1ValuesCount>=index+1){ > createStringHeaderCell(wb,headerRow, (short) column1CellIndex,(String)column1Values.get(index)); > } > if(column2ValuesCount>=index+1){ > createStringHeaderCell(wb,headerRow, (short) column2CellIndex,(String)column2Values.get(index)); > } > } > return rowIndex; > } > private int generateFooter(HSSFWorkbook wb, HSSFSheet reportSheet, > int rowIndex, int dataColumnCount, ReportFooterVO footerVo) > throws ReportServiceException { > > if (reportSheet == null) { > log.error("generateFooter(..) -> HSSFSheet cannot be null"); > throw new ReportServiceException( > "generateFooter(..) -> HSSFSheet cannot be null"); > } > if (dataColumnCount < 5) { > dataColumnCount = 8; > } > if (footerVo!=null){ > List footerValues=footerVo.getFooterValues(); > if(footerValues!=null){ > for(int i=0; i < footerValues.size();i++){ > HSSFRow row = reportSheet.createRow(rowIndex++); > HSSFCell cell = row.createCell((short) 1); > cell.setCellValue(new HSSFRichTextString((String)footerValues.get(i))); > cell.setCellStyle(getTitleStyle(wb)); > } > } > } > return rowIndex; > } > private HSSFCell createStringHeaderCell(HSSFWorkbook wb, HSSFRow headerRow > ,short cellIndex, String cellValue){ > HSSFCell cell = headerRow.createCell(cellIndex); > cell.setCellValue(new HSSFRichTextString(cellValue)); > cell.setCellStyle(getStringHeaderStyle(wb)); > return cell; > } > private HSSFCell createDateHeaderCell(HSSFWorkbook wb, HSSFRow headerRow, > short cellIndex, Date cellValue) { > HSSFCell cell = headerRow.createCell(cellIndex); > cell.setCellValue(cellValue); > cell.setCellStyle(getDateHeaderStyle(wb)); > return cell; > } > > private HSSFCellStyle getTitleStyle(HSSFWorkbook wb) { > if(titleStyle == null){ > formatFontCount++; > titleStyle = wb.createCellStyle(); > HSSFFont font = wb.createFont(); > font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); > font.setFontHeightInPoints((short)12); > titleStyle.setFont(font); > } > return titleStyle; > } > private HSSFCellStyle getStringHeaderStyle(HSSFWorkbook wb) { > if(stringHeaderStyle==null){ > formatFontCount++; > stringHeaderStyle = wb.createCellStyle(); > HSSFFont font = wb.createFont(); > font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); > font.setFontHeightInPoints((short)8); > //font.setColor(HSSFColor.GREY_50_PERCENT.index); > font.setItalic(true); > stringHeaderStyle.setWrapText(true); > stringHeaderStyle.setFont(font); > } > return stringHeaderStyle; > } > private HSSFCellStyle getDateHeaderStyle(HSSFWorkbook wb) { > if(dateHeaderStyle == null){ > formatFontCount++; > dateHeaderStyle = wb.createCellStyle(); > dateHeaderStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); > HSSFFont font = wb.createFont(); > font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); > font.setFontHeightInPoints((short)8); > font.setColor(HSSFColor.GREY_50_PERCENT.index); > font.setItalic(true); > dateHeaderStyle.setFont(font); > } > return dateHeaderStyle; > } > private HSSFCellStyle getColumnHeaderStyle(HSSFWorkbook wb) { > if(columnHeaderStyle == null){ > formatFontCount++; > columnHeaderStyle = wb.createCellStyle(); > columnHeaderStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); > columnHeaderStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); > HSSFFont font = wb.createFont(); > font.setColor(HSSFColor.OLIVE_GREEN.index); > font.setFontHeightInPoints((short)07); > columnHeaderStyle.setFont(font); > columnHeaderStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); > } > return columnHeaderStyle; > } > private HSSFCellStyle getAlignedColumnHeaderStyle(ResultSetMetaData rsMetaData, > int columnIndex, HSSFWorkbook wb) throws SQLException { > int columnType = rsMetaData.getColumnType(columnIndex); > if (columnType == Types.DATE || columnType == Types.TIMESTAMP > || columnType == Types.FLOAT > || columnType == Types.DOUBLE > || columnType == Types.DECIMAL > || columnType == Types.NUMERIC > || columnType == Types.INTEGER) { > return getColumnHeaderStyle(wb,HSSFCellStyle.ALIGN_RIGHT); > } > return getColumnHeaderStyle(wb,HSSFCellStyle.ALIGN_LEFT); > } > private HSSFCellStyle getColumnHeaderStyle(HSSFWorkbook wb, short alignment) { > if(columnHeaderLeftStyle == null){ > formatFontCount++; > columnHeaderLeftStyle = wb.createCellStyle(); > columnHeaderLeftStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); > columnHeaderLeftStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); > HSSFFont font = wb.createFont(); > font.setColor(HSSFColor.OLIVE_GREEN.index); > font.setFontHeightInPoints((short)07); > columnHeaderLeftStyle.setFont(font); > columnHeaderLeftStyle.setWrapText(true); > columnHeaderLeftStyle.setAlignment(alignment); > } > return columnHeaderLeftStyle; > } > > private HSSFFont getDataCellFont(HSSFWorkbook wb){ > if(dataCellFont==null){ > formatFontCount++; > dataCellFont = wb.createFont(); > dataCellFont.setFontName("Arial"); > dataCellFont.setFontHeightInPoints((short)7); > } > return dataCellFont; > } > private HSSFCellStyle getStringStyle(HSSFWorkbook wb){ > if(stringStyle==null){ > stringStyle = wb.createCellStyle(); > stringStyle.setFont(getDataCellFont(wb)); > } > return stringStyle; > } > private HSSFCellStyle getDateStyle(HSSFWorkbook wb){ > if(dateStyle == null){ > dateStyle = wb.createCellStyle(); > dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); > dateStyle.setFont(getDataCellFont(wb)); > } > return dateStyle; > } > private HSSFCellStyle getTimeStyle(HSSFWorkbook wb){ > if(timeStyle == null){ > timeStyle = wb.createCellStyle(); > timeStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("@h:mm AM/PM")); > timeStyle.setFont(getDataCellFont(wb)); > } > return timeStyle; > } > private HSSFCellStyle getNumberStyle(HSSFWorkbook wb){ > if( numberStyle == null){ > numberStyle = wb.createCellStyle(); > numberStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); > numberStyle.setFont(getDataCellFont(wb)); > } > return numberStyle; > } > > private int generateExcelColumnNames(HSSFWorkbook wb,int rowIndex, > HSSFSheet reportSheet, ResultSetMetaData rsMetaData) > throws SQLException { > HSSFRow row = reportSheet.createRow(rowIndex++); > for (int i = 0; i < rsMetaData.getColumnCount(); i++) { > HSSFCell column = row.createCell((short) (i)); > column.setCellStyle(getAlignedColumnHeaderStyle(rsMetaData,i+1,wb)); > String columnHeader=rsMetaData.getColumnLabel(i + 1); > if(columnHeader.length()>=10){ > //This is to wrap words with space > columnHeader=columnHeader.replace(' ','\n'); > } > column.setCellValue(new HSSFRichTextString(columnHeader.trim())); > } > return rowIndex; > } > private void setColumnWidths(HSSFSheet reportSheet, > ResultSetMetaData rsMetaData) throws SQLException { > for (int i = 0; i < rsMetaData.getColumnCount(); i++) { > int displaySize = rsMetaData.getColumnDisplaySize(i+1)+1; > > String columnLabel=rsMetaData.getColumnLabel(i+1); > int columnLabelLength=columnLabel.length()+1; >// reportSheet.autoSizeColumn((short)(i+1)); > int columnType=rsMetaData.getColumnType(i+1); > if(columnType == Types.DATE > || columnType == Types.TIMESTAMP > || columnType == Types.FLOAT > || columnType == Types.DOUBLE > || columnType == Types.DECIMAL > || columnType == Types.NUMERIC > || columnType == Types.INTEGER){ > displaySize=10; > if(columnLabelLength<=15){ > reportSheet.setColumnWidth((short)(i), (short)(columnLabelLength*256) ); > }else{ > reportSheet.setColumnWidth((short)(i), (short)(displaySize*256) ); > } > }else > if (columnLabelLength > displaySize){ > reportSheet.setColumnWidth((short)(i), (short)(columnLabelLength*256) ); > }else{ > reportSheet.setColumnWidth((short)(i), (short)(displaySize*256) ); > } > } > } > > private void setAutoColumnWidths(HSSFSheet reportSheet, > ResultSetMetaData rsMetaData) throws SQLException { > System.out.println("formatFontCount =" + formatFontCount); > System.out.println("columnValueStyles.length =" + columnValueStyles.length); > System.out.println("column Count = " + rsMetaData.getColumnCount()); > System.out.println("called autoSizeColumn for the following values of i"); > for (short i = 0; i < rsMetaData.getColumnCount(); i++) { > System.out.print(" " +i); > //reportSheet.autoSizeColumn(i,false); > } > } > > private void createDataCellPrevious(HSSFWorkbook wb,HSSFRow row,int cellIndex,int cellType, > Object value, boolean valueIsNull){ > if (cellType == Types.VARCHAR || cellType == Types.CHAR) { > HSSFRichTextString stringCell = new HSSFRichTextString((String)value); > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getStringStyle(wb)); > cell.setCellValue(stringCell); > } else if (cellType == Types.DATE) { > if (!valueIsNull) { > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getDateStyle(wb)); > cell.setCellValue((Date) value); > } else { > row.createCell((short) cellIndex); > } > } else if (cellType == Types.TIMESTAMP) { > if (!valueIsNull) { > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getDateStyle(wb)); > cell.setCellValue((Date) value); > } > } else if (cellType == Types.FLOAT > || cellType == Types.DOUBLE > || cellType == Types.DECIMAL > || cellType == Types.NUMERIC) { > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getNumberStyle(wb)); > cell.setCellValue( ((BigDecimal) value).doubleValue()); > } else if (cellType == Types.INTEGER > || cellType == Types.SMALLINT) { > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getNumberStyle(wb)); > cell.setCellValue(((Integer) value ).doubleValue()); > } else { > if (!valueIsNull) { > HSSFRichTextString stringCell = new HSSFRichTextString((String)value); > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellValue(stringCell); > cell.setCellStyle(getStringStyle(wb)); > }else { > HSSFCell cell = row.createCell((short) cellIndex); > cell.setCellStyle(getStringStyle(wb)); > } > } > } > private void createDataCell(HSSFWorkbook wb,HSSFRow row,int cellIndex, > int cellType, Object value, boolean valueIsNull) > throws SQLException { > > HSSFCell cell = row.createCell((short) cellIndex); > if (valueIsNull) { > cell.setCellStyle(getStringStyle(wb)); > return; > } > Class requiredType = value.getClass(); > // JDBC 2.0 !? > if(requiredType.equals(String.class)){ > HSSFRichTextString stringCell = new HSSFRichTextString(((String)value).trim()); > > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(stringCell); > }else if (requiredType.equals(Integer.class) > || requiredType.equals(int.class)) { > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(((Integer) value ).intValue()); > > } else if (requiredType.equals(java.math.BigDecimal.class)) { > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue( ((BigDecimal) value).doubleValue()); > > } else if (requiredType.equals(java.sql.Timestamp.class)) { > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue((ReportUtils.getDateFromTimeStamp((Timestamp)value))); > > } else if (requiredType.equals(Time.class)) { > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue((Time)value); > > } else if (requiredType.equals(java.util.Date.class) > || requiredType.equals(java.sql.Date.class)) { > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue((Date)value); > > } else if (requiredType.equals(Long.class) > || requiredType.equals(long.class)) { > > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(((Long) value ).longValue()); > } else if (requiredType.equals(float.class) > || requiredType.equals(java.lang.Float.class)) { > > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(((Float) value ).floatValue()); > } else if (requiredType.equals(Boolean.class) > || requiredType.equals(boolean.class)) { > > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(((Boolean) value ).booleanValue()); > } else { > HSSFRichTextString stringCell = new HSSFRichTextString((String)value.toString()); > > cell.setCellStyle(columnValueStyles[cellIndex]); > cell.setCellValue(stringCell); > } > } > /** > * @param i > * @param reportSheet > * @param commDetailsVO > * @param commissionType > */ > private int generateExcelColumnValues(HSSFWorkbook wb,int rowIndex, > HSSFSheet reportSheet, List reportDataRowSetList) throws SQLException { > /* Generate New Row */ > > for(int i=0;i<reportDataRowSetList.size();i++){ > RowSet rowSet = (RowSet)reportDataRowSetList.get(i); > ResultSetMetaData rsmd = rowSet.getMetaData(); > int columnCount = rsmd.getColumnCount(); > int dataRowIndex=0; > while (rowSet.next()) { > if(dataRowIndex==0){ > this.generateColumnCellStyles(wb,rsmd,rowSet); > dataRowIndex=1; > } > HSSFRow row = reportSheet.createRow((short) rowIndex); > for (int columnIndex = 0; columnIndex < columnCount; columnIndex++) { > int columnType = rsmd.getColumnType(columnIndex + 1); > this.createDataCell(wb,row,columnIndex,columnType, > rowSet.getObject(columnIndex+1), > rowSet.wasNull()); > } > rowIndex++; > } > } > return rowIndex; > } > private void generateColumnCellStyles(HSSFWorkbook wb, > ResultSetMetaData metaData, RowSet rowSet) throws SQLException{ > columnValueStyles= new HSSFCellStyle[metaData.getColumnCount()]; > int colCount=0; > if(metaData!=null){ > colCount = metaData.getColumnCount(); > } > formatFontCount++; > HSSFDataFormat format = wb.createDataFormat(); > for(int cellIndex=0;cellIndex< colCount;cellIndex++){ > Object value = rowSet.getObject(cellIndex+1); > //Whatever may be the type, following two statements are common > columnValueStyles[cellIndex] = wb.createCellStyle(); > columnValueStyles[cellIndex].setFont(getDataCellFont(wb)); > if(rowSet.wasNull()){ > continue; > } > Class requiredType = value.getClass(); > if (requiredType.equals(Integer.class) > || requiredType.equals(int.class)) { > columnValueStyles[cellIndex].setAlignment(HSSFCellStyle.ALIGN_RIGHT); > > } else if (requiredType.equals(java.math.BigDecimal.class)) { > columnValueStyles[cellIndex].setAlignment(HSSFCellStyle.ALIGN_RIGHT); > String formatStr=getNumberFormat(metaData.getScale(cellIndex)); > columnValueStyles[cellIndex].setDataFormat(format.getFormat(formatStr)); > } else if (requiredType.equals(java.sql.Timestamp.class)) { > columnValueStyles[cellIndex].setDataFormat(HSSFDataFormat.getBuiltinFormat("@h:mm AM/PM")); > > } else if (requiredType.equals(Time.class)) { > columnValueStyles[cellIndex].setDataFormat(HSSFDataFormat.getBuiltinFormat("@h:mm AM/PM")); > > } else if (requiredType.equals(java.util.Date.class) > || requiredType.equals(java.sql.Date.class)) { > columnValueStyles[cellIndex].setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); > > } else if (requiredType.equals(Long.class) > || requiredType.equals(long.class)) { > columnValueStyles[cellIndex].setAlignment(HSSFCellStyle.ALIGN_RIGHT); > > } else if (requiredType.equals(float.class) > || requiredType.equals(java.lang.Float.class)) { > columnValueStyles[cellIndex].setAlignment(HSSFCellStyle.ALIGN_RIGHT); > String formatStr=getNumberFormat(metaData.getScale(cellIndex)); > columnValueStyles[cellIndex].setDataFormat(format.getFormat(formatStr)); > > } else if (requiredType.equals(Boolean.class) > || requiredType.equals(boolean.class)) { > columnValueStyles[cellIndex].setAlignment(HSSFCellStyle.ALIGN_RIGHT); > } > } > } > > private String getNumberFormat(int scale){ > String numberFormat="#0"; > if(scale<=0){ > return numberFormat; > } > > for(int i=1;i<=scale;i++){ > if(i==1){ > numberFormat=numberFormat+".0"; > }else{ > numberFormat=numberFormat+"0"; > } > } > return numberFormat; > } >} View Attachment As Raw

This is ASF Bugzilla: the Apache Software Foundation bug system. In case of problems with the functioning of ASF Bugzilla, please contact bugzilla-admin@apache.org. Please Note: this e-mail address is only for reporting problems with ASF Bugzilla. Mail about any other subject will be silently ignored.

• • Home
  • | New
  • | Browse
  • | Search
  • |
    [?]
  • | Reports
  • | Help
  • | New Account
  • | Log In
    Remember [x]
  • | Forgot Password
    Login: [x]