List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getDefaultRowHeightInPoints
@Override public float getDefaultRowHeightInPoints()
From source file:com.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;//from ww w. jav a 2 s .co m dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:com.ideaspymes.arthyweb.ventas.web.controllers.VentasCantadasBean.java
private void creaCabecera(HSSFWorkbook workbook, HSSFSheet sheet) { Font fontSubTitulo = workbook.createFont(); fontSubTitulo.setFontHeightInPoints((short) 8); fontSubTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloAzul = workbook.createFont(); fontSubTituloAzul.setFontHeightInPoints((short) 8); fontSubTituloAzul.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloAzul.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontSubTituloCaje = workbook.createFont(); fontSubTituloCaje.setFontHeightInPoints((short) 7); fontSubTituloCaje.setColor(HSSFColor.DARK_BLUE.index); fontSubTituloCaje.setBoldweight(Font.BOLDWEIGHT_BOLD); Font font7 = workbook.createFont(); font7.setFontHeightInPoints((short) 6); Font fontTerritorio = workbook.createFont(); fontTerritorio.setItalic(true);//from w ww. j a v a 2 s . c o m fontTerritorio.setFontHeightInPoints((short) 12); fontTerritorio.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTerritorioTotal1 = workbook.createFont(); fontTerritorioTotal1.setItalic(true); fontTerritorioTotal1.setFontHeightInPoints((short) 8); fontTerritorioTotal1.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fontTitulo9 = workbook.createFont(); fontTitulo9.setFontHeightInPoints((short) 8); Font fontTitulo = workbook.createFont(); fontTitulo.setFontHeightInPoints((short) 12); fontTitulo.setBoldweight(Font.BOLDWEIGHT_BOLD); styleTitulo = workbook.createCellStyle(); styleTitulo.setFont(fontTitulo); styleTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleTerritorio = workbook.createCellStyle(); styleTerritorio.setFont(fontTerritorio); styleTerritorioTotal1 = workbook.createCellStyle(); styleTerritorioTotal1.setFont(fontTerritorioTotal1); styleTerritorioTotal1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTerritorioTotal1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleTitulo9 = workbook.createCellStyle(); styleTitulo9.setFont(fontTitulo9); styleTitulo9.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderTop(HSSFCellStyle.BORDER_THIN); //styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleTitulo9.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleSubTitulo = workbook.createCellStyle(); styleSubTitulo.setFont(fontSubTitulo); styleSubTitulo.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTitulo.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTitulo.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje = workbook.createCellStyle(); styleSubTituloCaje.setFont(fontSubTituloCaje); styleSubTituloCaje.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleSubTituloCaje.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleSubTituloCaje.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado = workbook.createCellStyle(); styleMergeCentrado.setFont(fontSubTituloAzul); styleMergeCentrado.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeCentrado.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeCentrado.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleMergeCentrado.setWrapText(true); styleMergeTotal = workbook.createCellStyle(); styleMergeTotal.setFont(fontSubTitulo); styleMergeTotal.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleMergeTotal.setFillPattern(CellStyle.SOLID_FOREGROUND); styleMergeTotal.setBottomBorderColor(IndexedColors.BLACK.getIndex()); styleMergeTotal.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleMergeTotal.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataFormat format = workbook.createDataFormat(); styleCantidadAzul = workbook.createCellStyle(); styleCantidadAzul.setFont(fontSubTituloAzul); styleCantidadAzul.setDataFormat(format.getFormat("#,##0")); styleCantidadAzul.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadAzul.setAlignment(HSSFCellStyle.ALIGN_RIGHT); styleCantidadNegrita = workbook.createCellStyle(); styleCantidadNegrita.setFont(fontSubTitulo); styleCantidadNegrita.setDataFormat(format.getFormat("#,##0")); styleCantidadNegrita.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadNegrita.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris = workbook.createCellStyle(); styleCantidadGris.setFont(fontSubTitulo); styleCantidadGris.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris.setDataFormat(format.getFormat("#,##0")); styleCantidadGris.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); styleCantidadGris.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); styleCantidad = workbook.createCellStyle(); styleCantidad.setFont(fontTitulo9); styleCantidad.setDataFormat(format.getFormat("#,##0")); styleCantidad.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidad.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadGris1 = workbook.createCellStyle(); styleCantidadGris1.setFont(fontTitulo9); styleCantidadGris1.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); styleCantidadGris1.setFillPattern(CellStyle.SOLID_FOREGROUND); styleCantidadGris1.setDataFormat(format.getFormat("#,##0")); styleCantidadGris1.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadGris1.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleCantidadCaje = workbook.createCellStyle(); styleCantidadCaje.setFont(font7); styleCantidadCaje.setDataFormat(format.getFormat("#,##0")); styleCantidadCaje.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderTop(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderRight(HSSFCellStyle.BORDER_THIN); styleCantidadCaje.setBorderLeft(HSSFCellStyle.BORDER_THIN); HSSFRow rowTitulo = sheet.createRow(0); HSSFCell cellTitulo = rowTitulo.createCell(18); cellTitulo.setCellValue("CONTROL DIARIO DE VENTAS"); cellTitulo.setCellStyle(styleTitulo); HSSFRow rowFecha = sheet.createRow(++indexRow); HSSFCell cellFecha = rowFecha.createCell(18); // SimpleDateFormat sdf = new SimpleDateFormat("EEEE, dd 'de' MMMM 'de' yyyy", new Locale("es", "py")); SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy", new Locale("es", "py")); cellFecha.setCellValue("De " + sdf.format(inicio) + " al " + sdf.format(fin)); //Calendar calender = Calendar.getInstance(); //cellFecha.setCellValue(calender.get(getInicio())); cellFecha.setCellStyle(styleTitulo); ++indexRow; HSSFRow rowCabeceraMarca = sheet.createRow((++indexRow)); HSSFRow rowCabeceraProducto = sheet.createRow((++indexRow)); HSSFRow rowCabecerauM = sheet.createRow((++indexRow)); rowCabeceraProducto.setHeightInPoints((2 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell0 = rowCabecerauM.createCell(indexCol); cell0.setCellValue("Zona"); cell0.setCellStyle(styleSubTitulo); HSSFCell cell1 = rowCabecerauM.createCell(indexCol + 1); cell1.setCellValue("Vendedor"); cell1.setCellStyle(styleSubTitulo); HSSFCell cell2 = rowCabecerauM.createCell(indexCol + 2); cell2.setCellValue("Boletas"); cell2.setCellStyle(styleSubTitulo); HSSFCell cell3 = rowCabeceraProducto.createCell(indexCol + 3); HSSFCell cell4 = rowCabeceraProducto.createCell(indexCol + 4); cell3.setCellValue("Palermo Red Box 20"); cell3.setCellStyle(styleMergeCentrado); cell4.setCellValue(""); cell4.setCellStyle(styleMergeCentrado); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 3) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 4) + "$" + indexRow)); HSSFCell cell3g = rowCabecerauM.createCell(indexCol + 3); cell3g.setCellValue("Grue"); cell3g.setCellStyle(styleMergeCentrado); HSSFCell cell3c = rowCabecerauM.createCell(indexCol + 4); cell3c.setCellValue("Caj"); cell3c.setCellStyle(styleSubTituloCaje); HSSFCell cell5 = (rowCabeceraProducto).createCell(indexCol + 5); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 5) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 6) + "$" + indexRow)); cell5.setCellValue("Palermo Blue Box 20"); cell5.setCellStyle(styleMergeCentrado); HSSFCell cell5g = rowCabecerauM.createCell(indexCol + 5); cell5g.setCellValue("Grue"); cell5g.setCellStyle(styleMergeCentrado); HSSFCell cell5c = rowCabecerauM.createCell(indexCol + 6); cell5c.setCellValue("Caj"); cell5c.setCellStyle(styleSubTituloCaje); HSSFCell cell7 = (rowCabeceraProducto).createCell(indexCol + 7); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 7) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 8) + "$" + indexRow)); cell7.setCellValue("Palermo Green Box 20"); cell7.setCellStyle(styleMergeCentrado); HSSFCell cell7g = rowCabecerauM.createCell(indexCol + 7); cell7g.setCellValue("Grue"); cell7g.setCellStyle(styleMergeCentrado); HSSFCell cell7c = rowCabecerauM.createCell(indexCol + 8); cell7c.setCellValue("Caj"); cell7c.setCellStyle(styleSubTituloCaje); HSSFCell cell9 = (rowCabeceraProducto).createCell(indexCol + 9); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 9) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 10) + "$" + indexRow)); cell9.setCellValue("Palermo Tres Box 20"); cell9.setCellStyle(styleMergeCentrado); HSSFCell cell9g = rowCabecerauM.createCell(indexCol + 9); cell9g.setCellValue("Grue"); cell9g.setCellStyle(styleMergeCentrado); HSSFCell cell9c = rowCabecerauM.createCell(indexCol + 10); cell9c.setCellValue("Caj"); cell9c.setCellStyle(styleSubTituloCaje); HSSFCell cell11 = (rowCabeceraProducto).createCell(indexCol + 11); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 11) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 12) + "$" + indexRow)); cell11.setCellValue("Palermo Duo Box 20"); cell11.setCellStyle(styleMergeCentrado); HSSFCell cell11g = rowCabecerauM.createCell(indexCol + 11); cell11g.setCellValue("Grue"); cell11g.setCellStyle(styleMergeCentrado); HSSFCell cell11c = rowCabecerauM.createCell(indexCol + 12); cell11c.setCellValue("Caj"); cell11c.setCellStyle(styleSubTituloCaje); HSSFCell cell13 = rowCabeceraProducto.createCell(indexCol + 13); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 13) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 14) + "$" + indexRow)); cell13.setCellValue("Palermo Red Box 10"); cell13.setCellStyle(styleMergeCentrado); HSSFCell cell13g = rowCabecerauM.createCell(indexCol + 13); cell13g.setCellValue("Grue"); cell13g.setCellStyle(styleMergeCentrado); HSSFCell cell13c = rowCabecerauM.createCell(indexCol + 14); cell13c.setCellValue("Caj"); cell13c.setCellStyle(styleSubTituloCaje); HSSFCell cell15 = (rowCabeceraProducto).createCell(indexCol + 15); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 15) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 16) + "$" + indexRow)); cell15.setCellValue("Palermo Blue Box 10"); cell15.setCellStyle(styleMergeCentrado); HSSFCell cell15g = rowCabecerauM.createCell(indexCol + 15); cell15g.setCellValue("Grue"); cell15g.setCellStyle(styleMergeCentrado); HSSFCell cell15c = rowCabecerauM.createCell(indexCol + 16); cell15c.setCellValue("Caj"); cell15c.setCellStyle(styleSubTituloCaje); HSSFCell cell17 = (rowCabeceraProducto).createCell(indexCol + 17); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 17) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 18) + "$" + indexRow)); cell17.setCellValue("Palermo Green Box 10"); cell17.setCellStyle(styleMergeCentrado); HSSFCell cell17g = rowCabecerauM.createCell(indexCol + 17); cell17g.setCellValue("Grue"); cell17g.setCellStyle(styleMergeCentrado); HSSFCell cell17c = rowCabecerauM.createCell(indexCol + 18); cell17c.setCellValue("Caj"); cell17c.setCellStyle(styleSubTituloCaje); HSSFCell cell19 = (rowCabeceraProducto).createCell(indexCol + 19); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 19) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 20) + "$" + indexRow)); cell19.setCellValue("Palermo Tres Box 10"); cell19.setCellStyle(styleMergeCentrado); HSSFCell cell19g = rowCabecerauM.createCell(indexCol + 19); cell19g.setCellValue("Grue"); cell19g.setCellStyle(styleMergeCentrado); HSSFCell cell19c = rowCabecerauM.createCell(indexCol + 20); cell19c.setCellValue("Caj"); cell19c.setCellStyle(styleSubTituloCaje); HSSFCell cell21 = (rowCabeceraProducto).createCell(indexCol + 21); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 21) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 22) + "$" + indexRow)); cell21.setCellValue("Palermo Duo Box 10"); cell21.setCellStyle(styleMergeCentrado); HSSFCell cell21g = rowCabecerauM.createCell(indexCol + 21); cell21g.setCellValue("Grue"); cell21g.setCellStyle(styleMergeCentrado); HSSFCell cell21c = rowCabecerauM.createCell(indexCol + 22); cell21c.setCellValue("Caj"); cell21c.setCellStyle(styleSubTituloCaje); HSSFCell cell23 = (rowCabeceraProducto).createCell(indexCol + 23); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 23) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 24) + "$" + indexRow)); cell23.setCellValue("San Marino TYPE Box 20"); cell23.setCellStyle(styleMergeCentrado); HSSFCell cell23g = rowCabecerauM.createCell(indexCol + 23); cell23g.setCellValue("Grue"); cell23g.setCellStyle(styleMergeCentrado); HSSFCell cell23c = rowCabecerauM.createCell(indexCol + 24); cell23c.setCellValue("Caj"); cell23c.setCellStyle(styleSubTituloCaje); HSSFCell cell25 = (rowCabeceraProducto).createCell(indexCol + 25); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 25) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 26) + "$" + indexRow)); cell25.setCellValue("San Marino TYPE Box 10"); cell25.setCellStyle(styleMergeCentrado); HSSFCell cell25g = rowCabecerauM.createCell(indexCol + 25); cell25g.setCellValue("Grue"); cell25g.setCellStyle(styleMergeCentrado); HSSFCell cell25c = rowCabecerauM.createCell(indexCol + 26); cell25c.setCellValue("Caj"); cell25c.setCellStyle(styleSubTituloCaje); HSSFCell cell27 = (rowCabeceraProducto).createCell(indexCol + 27); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 27) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 28) + "$" + indexRow)); cell27.setCellValue("San Marino TYPE Soft"); cell27.setCellStyle(styleMergeCentrado); HSSFCell cell27g = rowCabecerauM.createCell(indexCol + 27); cell27g.setCellValue("Grue"); cell27g.setCellStyle(styleMergeCentrado); HSSFCell cell27c = rowCabecerauM.createCell(indexCol + 28); cell27c.setCellValue("Caj"); cell27c.setCellStyle(styleSubTituloCaje); HSSFCell cell29 = (rowCabeceraProducto).createCell(indexCol + 29); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 29) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 30) + "$" + indexRow)); cell29.setCellValue("Kentucky Box 20"); cell29.setCellStyle(styleMergeCentrado); HSSFCell cell29g = rowCabecerauM.createCell(indexCol + 29); cell29g.setCellValue("Grue"); cell29g.setCellStyle(styleMergeCentrado); HSSFCell cell29c = rowCabecerauM.createCell(indexCol + 30); cell29c.setCellValue("Caj"); cell29c.setCellStyle(styleSubTituloCaje); HSSFCell cell31 = (rowCabeceraProducto).createCell(indexCol + 31); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 31) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 32) + "$" + indexRow)); cell31.setCellValue("Kentucky Box 10"); cell31.setCellStyle(styleMergeCentrado); HSSFCell cell31g = rowCabecerauM.createCell(indexCol + 31); cell31g.setCellValue("Grue"); cell31g.setCellStyle(styleMergeCentrado); HSSFCell cell31c = rowCabecerauM.createCell(indexCol + 32); cell31c.setCellValue("Caj"); cell31c.setCellStyle(styleSubTituloCaje); HSSFCell cell33 = (rowCabeceraProducto).createCell(indexCol + 33); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 33) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 34) + "$" + indexRow)); cell33.setCellValue("Kentucky Soft"); cell33.setCellStyle(styleMergeCentrado); HSSFCell cell33g = rowCabecerauM.createCell(indexCol + 33); cell33g.setCellValue("Grue"); cell33g.setCellStyle(styleMergeCentrado); HSSFCell cell33c = rowCabecerauM.createCell(indexCol + 34); cell33c.setCellValue("Caj"); cell33c.setCellStyle(styleSubTituloCaje); HSSFCell cell35 = rowCabeceraProducto.createCell(indexCol + 35); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + CellReference.convertNumToColString(indexCol + 35) + "$" + indexRow + ":$" + CellReference.convertNumToColString(indexCol + 36) + "$" + indexRow)); cell35.setCellValue("Total"); cell35.setCellStyle(styleMergeTotal); HSSFCell cell38 = rowCabeceraProducto.createCell(indexCol + 36); cell38.setCellValue(""); cell38.setCellStyle(styleMergeTotal); HSSFCell cell35g = rowCabecerauM.createCell(indexCol + 35); cell35g.setCellValue("Gruesas"); cell35g.setCellStyle(styleSubTitulo); HSSFCell cell35c = rowCabecerauM.createCell(indexCol + 36); cell35c.setCellValue("Caje"); cell35c.setCellStyle(styleSubTitulo); HSSFCell cell37c = rowCabecerauM.createCell(indexCol + 37); cell37c.setCellValue("Cajas"); cell37c.setCellStyle(styleSubTitulo); HSSFCell cell38g = rowCabecerauM.createCell(indexCol + 38); cell38g.setCellValue("+gr"); cell38g.setCellStyle(styleSubTitulo); sheet.createFreezePane(2, 7); }
From source file:com.smi.travel.controller.excel.checking.airticket.CheckingAirOthersummary.java
private void genTicketProfitLossReport(HSSFWorkbook wb, List ticketProfitLoss) { String sheetName = "Sheet1";// name of sheet HSSFSheet sheet = wb.createSheet(sheetName); TicketProfitLoss dataheader = new TicketProfitLoss(); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFDataFormat currency = wb.createDataFormat(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); System.out.println(" ticketProfitLoss.size() " + ticketProfitLoss.size()); if (ticketProfitLoss != null && ticketProfitLoss.size() != 0) { dataheader = (TicketProfitLoss) ticketProfitLoss.get(0); // set Header Report (Row 1) HSSFCellStyle styleC1 = wb.createCellStyle(); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Ticket Profit Loss"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1); sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Row 2/*from w ww .ja v a 2s . c o m*/ HSSFRow row2 = sheet.createRow(1); HSSFCell cell21 = row2.createCell(0); cell21.setCellValue("Invoice Date From : " + dataheader.getInvoicedatefrom() + " To : " + dataheader.getInvoicedateto()); cell21.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A2:F2")); // Row 3 HSSFRow row3 = sheet.createRow(2); HSSFCell cell31 = row3.createCell(0); cell31.setCellValue("Print by : " + dataheader.getPrintby()); cell31.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A3:F3")); // HSSFCell cell32 = row3.createCell(4); // cell32.setCellValue("Print on : "+dataheader.getPrinton()); // cell32.setCellStyle(styleC22); // sheet.addMergedRegion(CellRangeAddress.valueOf("C3:D3")); // HSSFCell cell33 = row3.createCell(4); // cell33.setCellValue("Print on : "); // cell33.setCellStyle(styleC21); // HSSFCell cell34 = row3.createCell(5); // cell34.setCellValue(dataheader.getPrinton()); // cell34.setCellStyle(styleC22); // sheet.addMergedRegion(CellRangeAddress.valueOf("E3:F3")); //Row 4 HSSFRow row4 = sheet.createRow(3); HSSFCell cell41 = row4.createCell(0); cell41.setCellValue("Print on : " + dataheader.getPrinton()); cell41.setCellStyle(styleC22); sheet.addMergedRegion(CellRangeAddress.valueOf("A4:F4")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row6 = sheet.createRow(5); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue(""); cell61.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Inv No."); cell62.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Date"); cell63.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Dep"); cell64.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell651 = row6.createCell(4); cell651.setCellValue("Agent Name"); cell651.setCellStyle(styleC3Center); sheet.addMergedRegion(CellRangeAddress.valueOf("E6:H6")); HSSFCell cell652 = row6.createCell(5); cell652.setCellStyle(styleC3Center); HSSFCell cell653 = row6.createCell(6); cell653.setCellStyle(styleC3Center); HSSFCell cell654 = row6.createCell(7); cell654.setCellStyle(styleC3Center); HSSFCell cell66 = row6.createCell(8); cell66.setCellValue("Type"); cell66.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell67 = row6.createCell(9); cell67.setCellValue("Rou"); cell67.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell68 = row6.createCell(10); cell68.setCellValue("Pax"); cell68.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell69 = row6.createCell(11); cell69.setCellValue("Air"); cell69.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell610 = row6.createCell(12); cell610.setCellValue("Doc No."); cell610.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell611 = row6.createCell(13); cell611.setCellValue("Issue Date"); cell611.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell612 = row6.createCell(14); cell612.setCellValue("Comm"); cell612.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell613 = row6.createCell(15); cell613.setCellValue("Vat"); cell613.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell614 = row6.createCell(16); cell614.setCellValue("Total"); cell614.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); } //Detail of Table int count = 6; HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC22.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC23.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC23.setWrapText(true); styleC23.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC24.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC24.setWrapText(true); styleC24.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); styleC25.setWrapText(true); styleC25.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); for (int i = 0; i < ticketProfitLoss.size(); i++) { TicketProfitLoss data = (TicketProfitLoss) ticketProfitLoss.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getNo()); cell0.setCellStyle(styleC23); sheet.autoSizeColumn(0); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(data.getInvno()); cell1.setCellStyle(styleC23); sheet.autoSizeColumn(1); HSSFCell cell2 = row.createCell(2); cell2.setCellValue(data.getDate()); cell2.setCellStyle(styleC23); sheet.autoSizeColumn(2); HSSFCell cell3 = row.createCell(3); cell3.setCellValue(data.getDepartment()); cell3.setCellStyle(styleC23); sheet.autoSizeColumn(3); // String air = data.getDocno().substring(0, 4); // String doc = data.getDocno().substring(4); // System.out.println("Air : " + air + " Doc : " + doc); HSSFCell cell4 = row.createCell(4); cell4.setCellValue(data.getAgentname()); cell4.setCellStyle(styleC24); sheet.addMergedRegion(CellRangeAddress.valueOf("E" + (count + i + 1) + ":H" + (count + i + 1))); HSSFCell cell5 = row.createCell(5); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(6); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(7); cell7.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(8); cell18.setCellValue(data.getType()); cell18.setCellStyle(styleC23); sheet.autoSizeColumn(8); HSSFCell cell9 = row.createCell(9); cell9.setCellValue(data.getRount()); cell9.setCellStyle(styleC23); sheet.autoSizeColumn(9); HSSFCell cell10 = row.createCell(10); cell10.setCellValue(data.getPax()); cell10.setCellStyle(styleC25); sheet.autoSizeColumn(10); HSSFCell cell11 = row.createCell(11); cell11.setCellValue(data.getAir()); cell11.setCellStyle(styleC25); sheet.autoSizeColumn(11); HSSFCell cell12 = row.createCell(12); cell12.setCellValue((data.getDocno()).replaceAll(",", "\n")); cell12.setCellStyle(styleC23); sheet.autoSizeColumn(12); row.setHeightInPoints( (((((data.getDocno()).split(",")).length) + 1) * sheet.getDefaultRowHeightInPoints())); HSSFCell cell13 = row.createCell(13); cell13.setCellValue(data.getIssuedate()); cell13.setCellStyle(styleC23); sheet.autoSizeColumn(13); HSSFCell cell14 = row.createCell(14); cell14.setCellValue( !"".equalsIgnoreCase(data.getLittlecomm()) ? new BigDecimal(data.getLittlecomm()).doubleValue() : 0); cell14.setCellStyle(styleC25); sheet.autoSizeColumn(14); HSSFCell cell15 = row.createCell(15); cell15.setCellValue( !"".equalsIgnoreCase(data.getVat()) ? new BigDecimal(data.getVat()).doubleValue() : 0); cell15.setCellStyle(styleC25); sheet.autoSizeColumn(15); HSSFCell cell16 = row.createCell(16); cell16.setCellValue( !"".equalsIgnoreCase(data.getTotal()) ? new BigDecimal(data.getTotal()).doubleValue() : 0); cell16.setCellStyle(styleC25); sheet.autoSizeColumn(16); if (i == (ticketProfitLoss.size() - 1)) { row = sheet.createRow(count + i + 1); int count2 = count + i + 1; HSSFRow rowtotal = sheet.createRow(count2); String sumComm = "SUM(O" + 7 + ":O" + (count2) + ")"; String sumVat = "SUM(P" + 7 + ":P" + (count2) + ")"; String sumTotal = "SUM(Q" + 7 + ":Q" + (count2) + ")"; sheet.addMergedRegion(CellRangeAddress.valueOf("A" + (count2 + 1) + ":N" + (count2 + 1))); HSSFCell cellTotal0 = rowtotal.createCell(0); cellTotal0.setCellValue("Total"); cellTotal0.setCellStyle(styleC25); HSSFCell cellTotal = rowtotal.createCell(14); cellTotal.setCellFormula(sumComm); cellTotal.setCellStyle(styleC25); HSSFCell cellTotal2 = rowtotal.createCell(15); cellTotal2.setCellFormula(sumVat); cellTotal2.setCellStyle(styleC25); HSSFCell cellTotal3 = rowtotal.createCell(16); cellTotal3.setCellFormula(sumTotal); cellTotal3.setCellStyle(styleC25); for (int k = 1; k < 14; k++) { HSSFCell cellTotal1 = rowtotal.createCell(k); cellTotal1.setCellStyle(styleC25); } // for(int k=0;k<16;k++){ // HSSFCellStyle styleSum = wb.createCellStyle(); // styleSum.setBorderTop(HSSFCellStyle.BORDER_THIN); // HSSFCell cellSum = row.createCell(k); // cellSum.setCellStyle(styleSum); // } } // for(int j =0;j<15;j++){ // if(j==4){ // sheet.addMergedRegion(CellRangeAddress.valueOf("E"+(count + i)+":G"+(count + i))); // }else{ // sheet.autoSizeColumn(j); // } // // } } }
From source file:org.rti.zcore.dar.report.StockUsageReport.java
License:Apache License
@Override public void getPatientRegister(Date beginDate, Date endDate, int siteId) { Connection conn = null;/* w w w . ja va 2 s .c o m*/ try { conn = DatabaseUtils.getZEPRSConnection(org.rti.zcore.Constants.DATABASE_ADMIN_USERNAME); HSSFWorkbook wb = new HSSFWorkbook(); HSSFCellStyle boldStyle = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); boldStyle.setFont(font); HSSFCellStyle headerStyle = wb.createCellStyle(); headerStyle.setWrapText(true); headerStyle.setFont(font); //log.debug("Before getPatientStockMap:" + DateUtils.getTime()); HashMap<Long, List<StockControl>> stockMap = InventoryDAO.getPatientStockMap(conn, siteId, beginDate, endDate); //log.debug("Before EncountersDAO.getAll:" + DateUtils.getTime()); // loop through all of the items //List<Item> items = EncountersDAO.getAll(conn, Long.valueOf(131), "SQL_RETRIEVE_ALL_ADMIN131", Item.class); List<Item> items = null; Map queries = QueryLoader.instance().load("/" + Constants.SQL_GENERATED_PROPERTIES); String sql = (String) queries.get("SQL_RETRIEVE_ALL_ADMIN131") + " ORDER BY item.name"; ArrayList values = new ArrayList(); items = DatabaseUtils.getList(conn, Item.class, sql, values); //log.debug("Before looping through items:" + DateUtils.getTime()); //int j = 0; boolean generateReport = false; for (Item item : items) { Boolean useInReport = item.getUse_in_report(); Long itemId = item.getId(); useInReport = Boolean.TRUE; if ((useInReport != null) && (useInReport.booleanValue() == Boolean.TRUE)) { List<StockControl> patientStockChanges = stockMap.get(itemId); if (patientStockChanges == null) { patientStockChanges = new ArrayList<StockControl>(); } List<StockControl> stockChanges = InventoryDAO.getStockEncounterChanges(conn, itemId, siteId, beginDate, endDate, null, patientStockChanges); int stockChangesSize = stockChanges.size(); //if ((stockChangesSize > 0) || ((stockChangesSize == 0 && (item.getUse_in_report() != null) && (!item.getUse_in_report().equals(Boolean.FALSE))))) { if ((stockChangesSize > 0)) { generateReport = true; StockReport stockReport = InventoryDAO.generateStockSummary(conn, itemId, beginDate, stockChanges, false); // populate the common fields //HSSFSheet sheet = wb.getSheetAt(j); String fixedName = StringManipulation.escapeString(item.getName()).replace("/", " ") .replace("\\", " ").replace(",", "_").replace("[", "").replace("(", "-") .replace(")", "-"); int lenName = fixedName.length(); String itemIdString = String.valueOf(itemId); int lenItemId = itemIdString.length(); String uniqueName = null; if ((lenName + lenItemId) < 31) { uniqueName = fixedName + "_" + itemId; //log.debug(itemId + " size: " + uniqueName.length()); } else { int offset = (30 - lenItemId) - 1; if (lenName > offset) { uniqueName = fixedName.substring(0, offset) + "_" + itemIdString; //log.debug(itemId + " size: " + uniqueName.length()); } else { uniqueName = fixedName + "_" + itemId; } } HSSFSheet sheet = null; try { sheet = wb.createSheet(uniqueName); } catch (IllegalArgumentException e) { log.debug("Problem with name : " + uniqueName + " Error: " + e.getMessage()); //this.setType("error"); // e.printStackTrace(); } String code = ""; if (item.getCode() != null) { code = " (" + item.getCode() + ")"; } if (sheet != null) { //sheet.createRow(0).createCell(0).setCellValue(new HSSFRichTextString(item.getName() + code)); HSSFHeader header = sheet.getHeader(); header.setCenter(item.getName() + code); /*HSSFCell titleCell = sheet.createRow(0).createCell(0); titleCell.setCellStyle(boldStyle); titleCell.setCellValue(new HSSFRichTextString(item.getName() + code));*/ //sheet.createRow(2).createCell(0).setCellValue(new HSSFRichTextString("Beginning Balance")); HSSFRow row = sheet.createRow(0); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); HSSFCell cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Beginning \nBalance")); //sheet.getRow(0).createCell(1).setCellValue(new HSSFRichTextString("Quantity Received this period")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \nReceived \nthis period")); //sheet.getRow(0).createCell(2).setCellValue(new HSSFRichTextString("Quantity dispensed this period")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Quantity \ndispensed \nthis period")); //sheet.getRow(0).createCell(3).setCellValue(new HSSFRichTextString("Total Issued to Patients")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Total Issued \nto Patients")); //sheet.getRow(0).createCell(4).setCellValue(new HSSFRichTextString("Positive Adjustments")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Positive \nAdjustments")); //sheet.getRow(0).createCell(5).setCellValue(new HSSFRichTextString("Negative Adjustments")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Negative \nAdjustments")); //sheet.getRow(0).createCell(6).setCellValue(new HSSFRichTextString("Ending Balance / Physical Count")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Ending Balance \nPhysical Count")); sheet.autoSizeColumn((short) 0); sheet.autoSizeColumn((short) 1); sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.createRow(1).createCell(0).setCellValue(stockReport.getBalanceBF()); sheet.getRow(1).createCell(1).setCellValue(stockReport.getAdditionsTotal()); sheet.getRow(1).createCell(2).setCellValue(stockReport.getDeletionsTotal()); sheet.getRow(1).createCell(3).setCellValue(stockReport.getTotalDispensed()); sheet.getRow(1).createCell(4).setCellValue(stockReport.getPosAdjustments()); sheet.getRow(1).createCell(5).setCellValue(stockReport.getNegAdjustments()); sheet.getRow(1).createCell(6).setCellValue(stockReport.getOnHand()); row = sheet.createRow(4); row.setHeightInPoints((3 * sheet.getDefaultRowHeightInPoints())); //sheet.createRow(4).createCell(0).setCellValue(new HSSFRichTextString("Date")); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Date")); //sheet.getRow(4).createCell(1).setCellValue(new HSSFRichTextString("Type of Stock Change")); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Type of \nStock \nChange")); //sheet.getRow(4).createCell(2).setCellValue(new HSSFRichTextString("Expiry Date")); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Expiry \nDate")); //sheet.getRow(4).createCell(3).setCellValue(new HSSFRichTextString("Reference / Notes")); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Reference/ \n Notes")); //sheet.getRow(4).createCell(4).setCellValue(new HSSFRichTextString("Additions")); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Additions")); //sheet.getRow(4).createCell(5).setCellValue(new HSSFRichTextString("Subtractions")); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Subtractions")); //sheet.getRow(4).createCell(6).setCellValue(new HSSFRichTextString("Recorded Balance")); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Recorded \nBalance")); //sheet.getRow(4).createCell(7).setCellValue(new HSSFRichTextString("Calculated Balance")); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue(new HSSFRichTextString("Calculated \nBalance")); sheet.autoSizeColumn((short) 7); int k = 4; for (StockControl stockControl : stockChanges) { if (stockControl.getDate_of_record() != null) { k++; String stockChangeTypeString = null; Integer posAdjust = null; Integer negAdjust = null; if (stockControl.getType_of_change() != null) { int stockChangeType = stockControl.getType_of_change(); switch (stockChangeType) { case 3263: stockChangeTypeString = "Received"; posAdjust = stockControl.getChange_value(); break; case 3264: stockChangeTypeString = "Issued"; negAdjust = stockControl.getChange_value(); break; case 3265: stockChangeTypeString = "Losses"; negAdjust = stockControl.getChange_value(); break; case 3266: stockChangeTypeString = "Pos. Adjust."; posAdjust = stockControl.getChange_value(); break; case 3267: stockChangeTypeString = "Neg. Adjust."; negAdjust = stockControl.getChange_value(); break; case 3279: stockChangeTypeString = "Out-of-stock"; break; default: break; } } row = sheet.createRow(k); if (stockControl.getDate_of_record() != null) { Date dateRecord = stockControl.getDate_of_record(); row.createCell(0) .setCellValue(new HSSFRichTextString(dateRecord.toString())); } else { row.createCell(0).setCellValue(new HSSFRichTextString("")); } row.createCell(1).setCellValue(new HSSFRichTextString(stockChangeTypeString)); if (stockControl.getExpiry_date() != null) { Date expiryDate = stockControl.getExpiry_date(); row.createCell(2) .setCellValue(new HSSFRichTextString(expiryDate.toString())); } else { row.createCell(2).setCellValue(new HSSFRichTextString("")); } row.createCell(3).setCellValue(new HSSFRichTextString(stockControl.getNotes())); if (posAdjust != null) { row.createCell(4).setCellValue(posAdjust); } if (negAdjust != null) { row.createCell(5).setCellValue(negAdjust); } if (stockControl.getBalance() != null) { row.createCell(6).setCellValue(stockControl.getBalance()); } if (stockControl.getComputedBalance() != null) { row.createCell(7).setCellValue(stockControl.getComputedBalance()); } } } } } } //j++; } if (generateReport) { FileOutputStream stream = new FileOutputStream(this.getReportPath()); wb.write(stream); stream.close(); } else { this.setType("empty"); } } catch (Exception e) { e.printStackTrace(); } finally { try { conn.close(); } catch (SQLException e) { log.error(e); } } }
From source file:poi.hssf.view.SViewerPanel.java
License:Apache License
protected JComponent makeSheetView(HSSFSheet sheet) { JTable sheetView = new JTable(new SVTableModel(sheet)); sheetView.setAutoResizeMode(JTable.AUTO_RESIZE_OFF); sheetView.setDefaultRenderer(HSSFCell.class, cellRenderer); if (allowEdits) sheetView.setDefaultEditor(HSSFCell.class, cellEditor); JTableHeader header = sheetView.getTableHeader(); //Dont allow column reordering header.setReorderingAllowed(false);/*from w ww .j a v a 2 s .com*/ //Only allow column resizing if editing is allowed header.setResizingAllowed(allowEdits); //Set the columns the correct size TableColumnModel columns = sheetView.getColumnModel(); for (int i = 0; i < columns.getColumnCount(); i++) { TableColumn column = columns.getColumn(i); int width = sheet.getColumnWidth(i); //256 is because the width is in 256ths of a character column.setPreferredWidth(width / 256 * magicCharFactor); } //Set the rows to the correct size int rows = sheet.getPhysicalNumberOfRows(); Insets insets = cellRenderer.getInsets(); //Need to include the insets in the calculation of the row height to use. int extraHeight = insets.bottom + insets.top; for (int i = 0; i < rows; i++) { HSSFRow row = sheet.getRow(i); if (row == null) { sheetView.setRowHeight(i, (int) sheet.getDefaultRowHeightInPoints() + extraHeight); } else { sheetView.setRowHeight(i, (int) row.getHeightInPoints() + extraHeight); } } //Add the row header to the sheet SVRowHeader rowHeader = new SVRowHeader(sheet, sheetView, extraHeight); JScrollPane scroll = new JScrollPane(sheetView); scroll.setRowHeaderView(rowHeader); return scroll; }