List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
From source file:com.haulmont.mp2xls.writer.LocalizationLogExcelWriter.java
License:Apache License
public static void exportToXls(List<LocalizationLog> differences, String outputXls) throws IOException { FileOutputStream fileOut = new FileOutputStream(outputXls); HSSFWorkbook workbook = new HSSFWorkbook(); try {//from w ww .ja v a 2 s. c o m HSSFSheet worksheet = workbook.createSheet("localizations"); HSSFCellStyle systemStyle = workbook.createCellStyle(); systemStyle.setFillForegroundColor(HSSFColor.RED.index); systemStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); Integer currentRow = 0; HSSFRow headLine = worksheet.createRow(++currentRow); headLine.createCell(0).setCellValue("File"); headLine.createCell(1).setCellValue("Property"); headLine.createCell(2).setCellValue("Source Value"); headLine.createCell(3).setCellValue("Excel Value"); HSSFRow row; for (LocalizationLog.Type type : LocalizationLog.Type.values()) { List<LocalizationLog> logs = getLogsByType(differences, type); if (logs.size() > 0) { row = worksheet.createRow(++currentRow); HSSFCell cell = row.createCell(0); cell.setCellValue(LogHelper.getMessageByType(type)); cell.setCellStyle(LogHelper.getStyleByType(workbook, type)); for (int i = 1; i < 4; i++) { row.createCell(i); } CellRangeAddress region = new CellRangeAddress(currentRow, currentRow, 0, 3); worksheet.addMergedRegion(region); for (LocalizationLog log : logs) { createNewLogRow(worksheet, ++currentRow, log); } row = worksheet.createRow(++currentRow); for (int i = 0; i < 4; i++) { row.createCell(i); } region = new CellRangeAddress(currentRow, currentRow, 0, 3); worksheet.addMergedRegion(region); } } worksheet.setAutoFilter(new CellRangeAddress(headLine.getRowNum(), worksheet.getLastRowNum(), 0, 3)); worksheet.createFreezePane(0, headLine.getRowNum() + 1); for (int i = 0; i < worksheet.getLastRowNum(); i++) { worksheet.autoSizeColumn(i); } } finally { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } }
From source file:com.hris.payroll.thirteenthmonth.ExportDataGridToExcel.java
public void workSheet() { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); int rownum = 1; for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { System.out.println("itemId: " + itemId); }/*from ww w . j a v a 2 s . c o m*/ for (Object itemId : getDataGrid().getContainerDataSource().getItemIds()) { Row row = sheet.createRow(rownum); int cellcount = 0; if ((rownum - 1) == 0) { Row rowHeader = sheet.createRow(rownum - 1); for (Object propertyId : getDataGrid().getContainerDataSource().getContainerPropertyIds()) { Cell cell = rowHeader.createCell(cellcount); if (propertyId.toString().contains("salary")) { if (!propertyId.toString().equals("salary grand total")) { cell.setCellValue(propertyId.toString().replace("salary", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("tax")) { if (!propertyId.toString().equals("tax grand total")) { cell.setCellValue(propertyId.toString().replace("tax", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("sss")) { if (!propertyId.toString().equals("sss grand total")) { cell.setCellValue(propertyId.toString().replace("sss", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("phic")) { if (!propertyId.toString().equals("phic grand total")) { cell.setCellValue(propertyId.toString().replace("phic", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else if (propertyId.toString().contains("hdmf")) { if (!propertyId.toString().equals("hdmf grand total")) { cell.setCellValue(propertyId.toString().replace("hdmf", "").toUpperCase()); } else { cell.setCellValue(propertyId.toString().toUpperCase()); } } else { cell.setCellValue(propertyId.toString().toUpperCase()); } sheet.autoSizeColumn(cellcount); cellcount++; } } Item item = getDataGrid().getContainerDataSource().getItem(itemId); int cellnum = 0; for (Object propertyId : item.getItemPropertyIds()) { Cell cell = row.createCell(cellnum); if (propertyId.equals("employee")) { cell.setCellValue(item.getItemProperty(propertyId).getValue().toString().toUpperCase()); } else { cell.setCellValue((item.getItemProperty(propertyId).getValue() == null) ? " " : item.getItemProperty(propertyId).getValue().toString()); } sheet.autoSizeColumn(cellnum); cellnum++; } rownum++; } FileOutputStream fos; try { Date date = new Date(); path = "C:/payroll-files/format-" + date.getTime() + ".xls"; file = new File(path); fos = new FileOutputStream(path); workbook.write(fos); fos.flush(); fos.close(); } catch (FileNotFoundException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { ErrorLoggedNotification.showErrorLoggedOnWindow(ex.toString(), this.getClass().getName()); Logger.getLogger(this.getClass().getName()).log(Level.SEVERE, null, ex); } StreamResource.StreamSource source = () -> { try { File f = new File(path); FileInputStream fis = new FileInputStream(f); return fis; } catch (Exception e) { e.getMessage(); return null; } }; StreamResource resource = new StreamResource(source, "ThirteenthMonth.xls"); resource.setMIMEType("application/vnd.ms-office"); }
From source file:com.kcs.action.CompareDsFcpDsFtsAction.java
@Override public String export() throws Exception { list = service.findMainList(DateUtil.getDateFromString(dataSetDate, DateUtil.DEFAULT_DATE_FORMAT)); logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + list.size()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Compate Ds FCP with Ds FTS Report"); setFileName("Compate Ds FCP with Ds FTS Report_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != list && list.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("data_set_Date"); row.createCell(cellnum++).setCellValue("pos_item"); row.createCell(cellnum++).setCellValue("cl_nm_thai_fcp"); row.createCell(cellnum++).setCellValue("fcp_amt"); row.createCell(cellnum++).setCellValue("fcp_curr"); row.createCell(cellnum++).setCellValue("tran_item"); row.createCell(cellnum++).setCellValue("cl_nm_thai_fts"); row.createCell(cellnum++).setCellValue("buy_fts_amt"); row.createCell(cellnum++).setCellValue("sell_fts_amt"); row.createCell(cellnum++).setCellValue("fts_curr"); row.createCell(cellnum++).setCellValue("Diff_amt"); for (CompareDsFcpDsFts obj : list) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;//w w w .j av a2s.co m rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPosItem() + ""); rowData.createCell(cellnum++).setCellValue(obj.getClNmThaiFcp() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFcpAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFcpCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTranItem() + ""); rowData.createCell(cellnum++).setCellValue(obj.getClNmThaiFts() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBuyFtsAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSellFtsAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFtsCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDiffAmt() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.ForwardContractAction.java
@Override @SuppressWarnings("empty-statement") public String export() throws Exception { list = getService().findByCriteria(DateUtil.convertDateFromJsp(getDataSetDate())); getLogger().debug("exportExcel : begin..."); setFileName("Forward contract Report.xls"); DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFCellStyle style = myWorkBook.createCellStyle(); HSSFCellStyle styleFont = myWorkBook.createCellStyle(); HSSFFont fontB = myWorkBook.createFont(); fontB.setBoldweight(Font.BOLDWEIGHT_BOLD); styleFont.setFont(fontB);/*from w w w. ja va 2 s . c om*/ list = sortSheet(list); List<String> listSheet = countSheet(list); for (int iListSheet = 0; iListSheet < listSheet.size(); iListSheet++) { List<ForwardContract> sheetObject = getSheetByAtSheet(list, listSheet.get(iListSheet)); HSSFSheet FW1 = myWorkBook.createSheet(listSheet.get(iListSheet)); List<String> currencyList = getCurrency(sheetObject); //------------------------- ROW 1 -----------------------------// Row FW1_row_0 = FW1.createRow(0); FW1_row_0.createCell(0).setCellValue(" FORWARD CONTRACT"); CellUtil.setAlignment(FW1_row_0.getCell(0), myWorkBook, CellStyle.ALIGN_CENTER); HSSFCellStyle styleRow1 = myWorkBook.createCellStyle(); styleRow1.setBorderRight(HSSFCellStyle.BORDER_THIN); styleRow1.setAlignment(HSSFCellStyle.ALIGN_CENTER); styleRow1.setFont(fontB); FW1_row_0.getCell(0).setCellStyle(styleRow1); FW1_row_0.createCell(1) .setCellValue(" ? ?()"); FW1_row_0.getCell(1).setCellStyle(styleRow1); // FW1_row_0.getCell(8).setCellStyle(styleFont); //------------------------- ROW 2 -----------------------------// Row FW1_row_1 = FW1.createRow(1); FW1_row_1.createCell(0).setCellValue("FW"); // FW1_row_1.getCell(0).setCellStyle(styleBR); // FW1.addMergedRegion(new CellRangeAddress(1,1,7,8)); FW1.autoSizeColumn(0); FW1.autoSizeColumn(7); FW1.autoSizeColumn(8); Row FW1_row_2 = FW1.createRow(2); FW1_row_2.createCell(0).setCellValue("?"); //CellUtil.setAlignment(FW1_row_2.getCell(0), myWorkBook, CellStyle.ALIGN_RIGHT); HSSFCellStyle style2 = myWorkBook.createCellStyle(); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_RIGHT); FW1_row_2.getCell(0).setCellStyle(style2); Row FW1_row_3 = FW1.createRow(3); FW1_row_3.createCell(0).setCellValue("?"); HSSFCellStyle style3 = myWorkBook.createCellStyle(); style3.setBorderBottom(HSSFCellStyle.BORDER_THIN); style3.setBorderRight(HSSFCellStyle.BORDER_THIN); style3.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_3.getCell(0).setCellStyle(style3); FW1.createRow(4).createCell(0).setCellValue("?"); FW1.createRow(5).createCell(0).setCellValue("? (129027)"); FW1.createRow(6).createCell(0).setCellValue( "???"); FW1.createRow(7).createCell(0).setCellValue(" ?"); FW1.createRow(8).createCell(0).setCellValue(" - ?? (129030)"); FW1.createRow(9).createCell(0) .setCellValue(" - ??? (129031)"); FW1.createRow(10).createCell(0).setCellValue(" ??"); FW1.createRow(11).createCell(0).setCellValue(" "); FW1.createRow(12).createCell(0).setCellValue(" - ?? (129034)"); FW1.createRow(13).createCell(0) .setCellValue(" - ??? (129035)"); FW1.createRow(14).createCell(0).setCellValue(" ???"); FW1.createRow(15).createCell(0).setCellValue(" - ?? (129037)"); FW1.createRow(16).createCell(0) .setCellValue(" - ??? (129038) "); FW1.createRow(17).createCell(0) .setCellValue(" ?"); FW1.createRow(18).createCell(0).setCellValue(" - ?? (129040)"); FW1.createRow(19).createCell(0) .setCellValue(" - ??? (129041) "); FW1.createRow(20).createCell(0) .setCellValue(" ??"); FW1.createRow(21).createCell(0).setCellValue(" - ?? (129043)"); FW1.createRow(22).createCell(0) .setCellValue(" - ??? (129044)"); FW1.createRow(23).createCell(0) .setCellValue(" ?"); FW1.createRow(24).createCell(0).setCellValue(" - ?? (129046)"); FW1.createRow(25).createCell(0) .setCellValue(" - ??? (129047) "); FW1.createRow(26).createCell(0).setCellValue("????"); FW1.createRow(27).createCell(0).setCellValue(" ??"); FW1.createRow(28).createCell(0).setCellValue(" - ?? (129050)"); FW1.createRow(29).createCell(0) .setCellValue(" - ??? (129051)"); FW1.createRow(30).createCell(0) .setCellValue(" "); FW1.createRow(31).createCell(0).setCellValue(" - ?? (129053)"); FW1.createRow(32).createCell(0) .setCellValue(" - ??? (129054)"); FW1.createRow(33).createCell(0).setCellValue(" ? "); FW1.createRow(34).createCell(0).setCellValue(" - ?? (129056) "); FW1.createRow(35).createCell(0) .setCellValue(" - ??? (129057) "); FW1.createRow(36).createCell(0).setCellValue(" "); FW1.createRow(37).createCell(0).setCellValue(" - ?? (129059) "); FW1.createRow(38).createCell(0) .setCellValue(" - ??? (129060) "); FW1.createRow(39).createCell(0).setCellValue("? (129061) "); FW1.createRow(40).createCell(0) .setCellValue("? (129062) "); // FW1.getRow(40).getCell(0).setCellStyle(styleBR); FW1.getRow(4).getCell(0).setCellStyle(styleFont); FW1.getRow(5).getCell(0).setCellStyle(styleFont); FW1.getRow(6).getCell(0).setCellStyle(styleFont); FW1.getRow(7).getCell(0).setCellStyle(styleFont); FW1.getRow(11).getCell(0).setCellStyle(styleFont); FW1.getRow(14).getCell(0).setCellStyle(styleFont); FW1.getRow(17).getCell(0).setCellStyle(styleFont); FW1.getRow(20).getCell(0).setCellStyle(styleFont); FW1.getRow(23).getCell(0).setCellStyle(styleFont); FW1.getRow(26).getCell(0).setCellStyle(styleFont); FW1.getRow(30).getCell(0).setCellStyle(styleFont); FW1.getRow(33).getCell(0).setCellStyle(styleFont); FW1.getRow(36).getCell(0).setCellStyle(styleFont); FW1.getRow(39).getCell(0).setCellStyle(styleFont); FW1.getRow(40).getCell(0).setCellStyle(styleFont); int cellCurrency = 1; for (int iCurrencyList = 0; iCurrencyList < currencyList.size(); iCurrencyList++) { style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); String currencyName = currencyList.get(iCurrencyList); FW1_row_2.createCell(cellCurrency).setCellValue(currencyName); FW1_row_3.createCell(cellCurrency).setCellValue(""); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1_row_2.createCell(cellCurrency + 1).setCellStyle(style); FW1_row_3.getCell(cellCurrency).setCellStyle(style); List<ForwardContract> tmpGroupCCY = getGroupByCCY(sheetObject, currencyName); for (ForwardContract objCCY : tmpGroupCCY) { FW1.getRow(5).createCell(cellCurrency).setCellValue(objCCY.getSELL_AMT_129027() + ""); if ("????" .equals(objCCY.getTRANS_TYPE())) { if ("New Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(8).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), true)); FW1.getRow(8).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), true)); FW1.getRow(12).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), true)); FW1.getRow(15).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), true)); FW1.getRow(18).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), true)); FW1.getRow(21).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), true)); FW1.getRow(24).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(9).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129045(), false)); FW1.getRow(9).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129029(), false)); FW1.getRow(13).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129033(), false)); FW1.getRow(16).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129036(), false)); FW1.getRow(19).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129039(), false)); FW1.getRow(22).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129042(), false)); FW1.getRow(25).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129045(), false)); } } else if ("????" .equals(objCCY.getTRANS_TYPE().trim())) { if ("New Forward".equals(objCCY.getLIST_ITEM())) { FW1.getRow(28).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), true)); FW1.getRow(28).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), true)); FW1.getRow(31).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), true)); FW1.getRow(34).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), true)); FW1.getRow(37).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), true)); } else if ("Utilize Forward".equals(objCCY.getLIST_ITEM().trim())) { FW1.getRow(29).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129058(), false)); FW1.getRow(29).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129049(), false)); FW1.getRow(32).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129052(), false)); FW1.getRow(35).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129055(), false)); FW1.getRow(38).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129058(), false)); } } else if ("?".equals(objCCY.getTRANS_TYPE())) { } FW1.getRow(39).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency) .setCellValue(convertNewForward(objCCY.getSELL_AMT_129062(), false)); FW1.getRow(39).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129061(), false)); FW1.getRow(40).createCell(cellCurrency + 1) .setCellValue(convertNewForward(objCCY.getBUY_AMT_129062(), false)); } // // FW1.getRow(createRow++).createCell(cellCurrency).setCellValue(tmpGroup.getSELL_AMT_129033()+""); CellUtil.setAlignment(FW1_row_2.getCell(cellCurrency), myWorkBook, CellStyle.ALIGN_CENTER); FW1_row_2.getCell(cellCurrency).setCellStyle(style); FW1.addMergedRegion(new CellRangeAddress(2, 2, cellCurrency, ++cellCurrency)); FW1_row_3.createCell(cellCurrency).setCellValue(""); HSSFCellStyle styleTop = style; styleTop.setBorderTop(HSSFCellStyle.BORDER_THIN); FW1_row_3.getCell(cellCurrency).setCellStyle(styleTop); cellCurrency++; } System.out.println("cellCurrency ++++ " + cellCurrency); int finishM = cellCurrency - 3; int noOfColumns = FW1.getRow(2).getLastCellNum(); int noOfRow = FW1.getLastRowNum(); // HSSFPalette palette = myWorkBook.getCustomPalette(); // HSSFColor hssfColor = null; // // // palette.setColorAtIndex(HSSFColor.WHITE.index, (byte) 255, (byte) 255, (byte) 255); // hssfColor = palette.getColor(HSSFColor.WHITE.index); // // // // HSSFCellStyle styleBG = myWorkBook.createCellStyle(); // styleBG.setFillForegroundColor(hssfColor.getIndex()); // styleBG.setFillPattern(CellStyle.SOLID_FOREGROUND); for (int i = 0; i < noOfRow; i++) { for (int j = 0; j < noOfColumns; j++) { FW1.autoSizeColumn(i); // row.getCell(j).setCellStyle(styleBG); } } for (int i = 4; i < noOfRow + 1; i++) { for (int j = 1; j < noOfColumns; j++) { HSSFCellStyle style6 = myWorkBook.createCellStyle(); try { if (j == 1) { style6.setBorderLeft(HSSFCellStyle.BORDER_THIN); style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } else { style6.setBorderRight(HSSFCellStyle.BORDER_THIN); style6.setAlignment(HSSFCellStyle.ALIGN_RIGHT); } if (i == noOfRow) { style6.setBorderBottom(HSSFCellStyle.BORDER_THIN); } FW1.getRow(i).getCell(j).setCellStyle(style6); } catch (Exception e) { FW1.getRow(i).createCell(j).setCellStyle(style6); } } } HSSFFont fontBold = myWorkBook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); HSSFCellStyle borderRightCenterFontBold = myWorkBook.createCellStyle(); borderRightCenterFontBold.setBorderRight(HSSFCellStyle.BORDER_THIN); borderRightCenterFontBold.setAlignment(HSSFCellStyle.ALIGN_CENTER); borderRightCenterFontBold.setFont(fontBold); HSSFCellStyle borderRightBottomCenterFontBold = borderRightCenterFontBold; borderRightBottomCenterFontBold.setBorderBottom(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleR = myWorkBook.createCellStyle(); styleR.setBorderRight(HSSFCellStyle.BORDER_THIN); // Forward contract // FW1.getRow(0).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(0).getCell(1).setCellStyle(borderRightCenterFontBold); FW1.addMergedRegion(new CellRangeAddress(0, 1, 1, finishM)); FW1_row_0.createCell(finishM + 1) .setCellValue(" " + dateFormat.format(new Date()) + ""); HSSFCellStyle style4 = myWorkBook.createCellStyle(); style4.setBorderRight(HSSFCellStyle.BORDER_THIN); style4.setBorderLeft(HSSFCellStyle.BORDER_THIN); style4.setAlignment(HSSFCellStyle.ALIGN_LEFT); FW1_row_0.getCell(finishM + 1).setCellStyle(style4); FW1_row_0.getCell(finishM + 1).setCellStyle(styleFont); FW1.addMergedRegion(new CellRangeAddress(0, 0, finishM + 1, finishM + 2)); FW1_row_1.createCell(finishM + 1).setCellValue( ":"); FW1.addMergedRegion(new CellRangeAddress(1, 1, finishM + 1, finishM + 2)); FW1_row_1.getCell(finishM + 1).setCellStyle(style4); FW1_row_1.getCell(finishM + 1).setCellStyle(styleFont); FW1_row_0.createCell(finishM + 2).setCellStyle(styleR); FW1_row_1.createCell(finishM + 2).setCellStyle(styleR); FW1.getRow(1).getCell(0).setCellStyle(borderRightCenterFontBold); // FW1.getRow(1).getCell(1).setCellStyle(borderRightBottomCenterFontBold); HSSFCellStyle borderBottom = myWorkBook.createCellStyle(); borderBottom.setBorderRight(HSSFCellStyle.BORDER_THIN); borderBottom.setBorderBottom(HSSFCellStyle.BORDER_THIN); borderBottom.setAlignment(HSSFCellStyle.ALIGN_LEFT); borderBottom.setFont(fontBold); FW1.getRow(40).getCell(0).setCellStyle(borderBottom); HSSFPatriarch patriarch = (HSSFPatriarch) FW1.createDrawingPatriarch(); /* Here is the thing: the line will go from top left in cell (0,0) to down left of cell (0,1) */ // int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 255, (short) 0, 2, (short) 1, 3); HSSFSimpleShape shape = patriarch.createSimpleShape(anchor); shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE); shape.setLineStyleColor(10, 10, 10); shape.setFillColor(90, 10, 200); shape.setLineWidth(HSSFShape.LINEWIDTH_ONE_PT); shape.setLineStyle(HSSFShape.LINESTYLE_SOLID); // createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow); // FW1.createFreezePane(0,4); } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.ImportExportDsDipAction.java
@Override public String export() throws Exception { resultList = service.findByCriteriaServiceList(DateUtil.convertDateFromJsp(getDataSetDate()), ""); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + resultList.size()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("DS_DIP"); setFileName("DS_DIP_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != resultList && resultList.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("OrgId"); row.createCell(cellnum++).setCellValue("DataPvdrBrcNo"); row.createCell(cellnum++).setCellValue("FiRptGrp"); row.createCell(cellnum++).setCellValue("DataSetDate"); row.createCell(cellnum++).setCellValue("ItemType"); row.createCell(cellnum++).setCellValue("ItemDesc"); row.createCell(cellnum++).setCellValue("CtryId"); row.createCell(cellnum++).setCellValue("Curr"); row.createCell(cellnum++).setCellValue("Amt"); row.createCell(cellnum++).setCellValue("UpdDate"); row.createCell(cellnum++).setCellValue("UpdBy"); row.createCell(cellnum++).setCellValue("SysCode"); row.createCell(cellnum++).setCellValue("Seq"); row.createCell(cellnum++).setCellValue("CustCode"); for (Datasetdip obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;/*from ww w . j a v a 2 s . c o m*/ rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataPvdrBrcNo() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFiRptGrp() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getItemType() + ""); rowData.createCell(cellnum++).setCellValue(obj.getItemDesc() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCtryId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCustCode() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.PositionReportAction.java
@Override public String export() throws Exception { positionReportList = service//from ww w . ja va2s . c o m .findMainList(DateUtil.getDateFromString(dataSetDate, DateUtil.DEFAULT_DATE_FORMAT)); logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + positionReportList.size()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Position Report"); setFileName("Position Report_" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != positionReportList && positionReportList.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("dataSetDate"); row.createCell(cellnum++).setCellValue("curr"); row.createCell(cellnum++).setCellValue("bankNoteAmt"); row.createCell(cellnum++).setCellValue("nostroAmt"); row.createCell(cellnum++).setCellValue("plcmentAmt"); row.createCell(cellnum++).setCellValue("fibAmt"); row.createCell(cellnum++).setCellValue("exportAmt"); row.createCell(cellnum++).setCellValue("loanAmt"); row.createCell(cellnum++).setCellValue("otherAmt"); row.createCell(cellnum++).setCellValue("fwdbAmt"); row.createCell(cellnum++).setCellValue("vostroAmt"); row.createCell(cellnum++).setCellValue("brAmt"); row.createCell(cellnum++).setCellValue("firAmt"); row.createCell(cellnum++).setCellValue("fwdsAmt"); for (PositionReport obj : positionReportList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurr() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBankNoteAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getNostroAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPlcmentAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFibAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getExportAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getLoanAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getOtherAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFwdbAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getVostroAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBrAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFirAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFwdsAmt() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.report.ForwardContractAction.java
@Override public String export() throws Exception { setList(getService().findMaturitySummaryByCriteria(DateUtil.convertDateFromJsp(getDataSetDate()))); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + getList()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Journal"); setFileName("Export Data Maturity (Summary) " + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != getList() && getList().size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); // mySheet.addMergedRegion(rowFrom,rowTo,colFrom,colTo); int cellnum = 0; row.createCell(cellnum++).setCellValue("MaturityGroup"); row.createCell(cellnum++).setCellValue("Ccy"); row.createCell(cellnum++).setCellValue("BuyAmt"); row.createCell(cellnum++).setCellValue("SellAmt"); row.createCell(cellnum++).setCellValue("TranType"); row.createCell(cellnum++).setCellValue("Proc Date"); for (Maturity obj : getList()) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;// w ww. j av a 2 s . com rowData.createCell(cellnum++).setCellValue(obj.getMATURITY_GROUP() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCCY() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBUY_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSELL_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTRANS_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPROC_DATE() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.report.MaturityMediumAction.java
@Override public String export() throws Exception { setList(getService().findMaturityMediumByCriteria(DateUtil.convertDateFromJsp(getDataSetDate()))); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + getList()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Journal"); setFileName("Export Data Maturity (Medium) " + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != getList() && getList().size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("MaturityGroup"); row.createCell(cellnum++).setCellValue("Ccy"); row.createCell(cellnum++).setCellValue("BuyAmt"); row.createCell(cellnum++).setCellValue("SellAmt"); row.createCell(cellnum++).setCellValue("ReportType"); row.createCell(cellnum++).setCellValue("TranType"); row.createCell(cellnum++).setCellValue("IpType"); row.createCell(cellnum++).setCellValue("ClNmThai"); row.createCell(cellnum++).setCellValue("Proc Date"); for (Maturity obj : getList()) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;/*from w w w. j a va 2 s .co m*/ rowData.createCell(cellnum++).setCellValue(obj.getMATURITY_GROUP() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCCY() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBUY_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSELL_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getREPORT_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTRANS_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getIP_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCL_NM_THAI() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPROC_DATE() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.report.MaturitySummaryAction.java
@Override public String export() throws Exception { setList(getService().findMaturitySummaryByCriteria(DateUtil.convertDateFromJsp(getDataSetDate()))); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + getList()); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Journal"); setFileName("Export Data Maturity (Summary) " + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != getList() && getList().size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("MaturityGroup"); row.createCell(cellnum++).setCellValue("Ccy"); row.createCell(cellnum++).setCellValue("BuyAmt"); row.createCell(cellnum++).setCellValue("SellAmt"); row.createCell(cellnum++).setCellValue("TranType"); row.createCell(cellnum++).setCellValue("Proc Date"); for (Maturity obj : getList()) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;/*from ww w . ja v a 2 s . co m*/ rowData.createCell(cellnum++).setCellValue(obj.getMATURITY_GROUP() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCCY() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBUY_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSELL_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTRANS_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPROC_DATE() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.report.ReportJournalAction.java
@Override public String export() throws Exception { list = service.findByCriteria(DateUtil.convertDateFromJsp(dataSetDate)); getLogger().debug("exportExcel : begin..."); getLogger().debug("exportExcel : list >>> " + list); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Journal"); setFileName("Export Data Journal" + dateFormat.format(new Date()) + "excel".concat(".xls")); if (null != list && list.size() > 0) { int rownum = 0; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("JournalGroup"); row.createCell(cellnum++).setCellValue("Ccy"); row.createCell(cellnum++).setCellValue("BuyAmt"); row.createCell(cellnum++).setCellValue("SellAmt"); row.createCell(cellnum++).setCellValue("ReportType"); row.createCell(cellnum++).setCellValue("TranType"); row.createCell(cellnum++).setCellValue("IpType"); row.createCell(cellnum++).setCellValue("ClNmThai"); for (Journal obj : list) { Row rowData = mySheet.createRow(rownum++); cellnum = 0;//from ww w .j a v a 2 s .co m rowData.createCell(cellnum++).setCellValue(obj.getJOURNAL_GROUP() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCCY() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBUY_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSELL_AMT() + ""); rowData.createCell(cellnum++).setCellValue(obj.getREPORT_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getTRANS_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getIP_TYPE() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCL_NM_THAI() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); getLogger().debug("exportExcel : end..."); return "excel"; }