List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow
@Override public HSSFRow createRow(int rownum)
From source file:com.jubinationre.service.AdminMaintainService.java
public boolean createExcel(List<CallAPIMessage> list) { boolean flag = false; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("Sample sheet"); CallAPIMessage[] messageArray = new CallAPIMessage[list.size()]; list.toArray(messageArray);//from w w w . j a va 2 s. c o m Map<String, Object[]> data = new LinkedHashMap<>(); Integer index = 1; data.put(index.toString(), new Object[] { "CallFrom", "CallTo", "Status", "TrackStatus", "CallType", "DailWhomNumber", "DailCallDuration", "Message", "DateCreated", "AnsweredBy", "StartTime", "EndTime", "DateUpdated", "Duration", "Price", "Direction", "Digits", "Sid", "Uri", "RecordingUrl", "PhoneNumberSid", "AccountSid", "ForwardedFrom", "CallerName", "ParentCallSid" }); index++; for (CallAPIMessage message : messageArray) { data.put(index.toString(), new Object[] { message.getCallFrom(), message.getCallTo(), message.getStatus(), message.getTrackStatus(), message.getCallType(), message.getDailWhomNumber(), message.getDailCallDuration(), message.getMessage(), message.getDateCreated(), message.getAnsweredBy(), message.getStartTime(), message.getEndTime(), message.getDateUpdated(), message.getDuration(), message.getPrice(), message.getDirection(), message.getDigits(), message.getSid(), message.getUri(), message.getRecordingUrl(), message.getPhoneNumberSid(), message.getAccountSid(), message.getForwardedFrom(), message.getCallerName(), message.getParentCallSid() }); index++; } Set<String> keyset = data.keySet(); int rownum = 0; for (String key : keyset) { Row row = sheet.createRow(rownum++); Object[] objArr = data.get(key); int cellnum = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) cell.setCellValue((Date) obj); else if (obj instanceof Boolean) cell.setCellValue((Boolean) obj); else if (obj instanceof String) cell.setCellValue((String) obj); else if (obj instanceof Double) cell.setCellValue((Double) obj); } } try { FileOutputStream out = new FileOutputStream(new File(excelOutputFilePath)); workbook.write(out); out.close(); flag = true; System.out.println("Excel written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return flag; }
From source file:com.kazak.comeet.admin.gui.misc.XLSExporter.java
License:Open Source License
@SuppressWarnings("deprecation") private void createXLSPage(Document doc, String title, TableColumnModel columnsInfo, String pageTitle) { HSSFSheet sheet = wb.createSheet(title); int columnsSize = columnsInfo.getColumnCount(); setColumnsWidth(sheet, columnsSize); HSSFRow titleRow = sheet.createRow(0); titleRow.createCell((short) 1).setCellValue(reportTitle); HSSFRow pageTitleRow = sheet.createRow(1); pageTitleRow.createCell((short) 1).setCellValue(pageTitle); HSSFRow blankRow = sheet.createRow(2); blankRow.createCell((short) 0).setCellValue(" "); HSSFRow row0 = sheet.createRow(3);/*www . j a v a 2s . c o m*/ for (int j = 0; j < columnsSize; j++) { TableColumn tmp = columnsInfo.getColumn(j); String columnName = (String) tmp.getHeaderValue(); row0.createCell((short) j).setCellValue(columnName); } blankRow = sheet.createRow(4); blankRow.createCell((short) 0).setCellValue(" "); List messagesList = doc.getRootElement().getChildren("row"); Iterator messageIterator = messagesList.iterator(); // Loading new info int i = 5; for (; messageIterator.hasNext();) { Element oneMessage = (Element) messageIterator.next(); List messagesDetails = oneMessage.getChildren(); HSSFRow row = sheet.createRow((short) i); for (int k = 0; k < columnsSize; k++) { String data = ((Element) messagesDetails.get(k)).getText(); row.createCell((short) k).setCellValue(data); } i++; } if (i == 0) { HSSFRow row = sheet.createRow((short) i); row.createCell((short) 0).setCellValue("No hay registros en esta pgina."); } }
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;/*from ww w . ja v a 2 s . c om*/ 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. j a v a2 s .co m 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.FrfImpExpAction.java
@Override public String export() throws Exception { // paging = datasetIrfService.findListIrf(createPaginate(), dataSetDate, systemCode); resultList = getDatasetFrfService().findMainList(dataSetDate, ""); resultSize = resultList.size();/*from w ww. j a v a 2 s.c om*/ logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset FRF"); setFileName("Export Data DS_FRF_" + 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("dataSetDate"); row.createCell(cellnum++).setCellValue("loanDepsitTrnTye"); row.createCell(cellnum++).setCellValue("currCode"); row.createCell(cellnum++).setCellValue("paymentMethod"); row.createCell(cellnum++).setCellValue("brOrBcFlg"); row.createCell(cellnum++).setCellValue("commInLieuRate"); row.createCell(cellnum++).setCellValue("minCommInLieu"); row.createCell(cellnum++).setCellValue("maxCommInLieu"); row.createCell(cellnum++).setCellValue("othFeeDesc"); row.createCell(cellnum++).setCellValue("effectiveDate"); row.createCell(cellnum++).setCellValue("endDate"); row.createCell(cellnum++).setCellValue("seq"); row.createCell(cellnum++).setCellValue("updBy"); row.createCell(cellnum++).setCellValue("updDate"); row.createCell(cellnum++).setCellValue("sysCode"); for (Datasetfrf obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getLoanDepsitTrnTye() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurrCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getPaymentMethod() + ""); rowData.createCell(cellnum++).setCellValue(obj.getBrOrBcFlg() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieu() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieu() + ""); rowData.createCell(cellnum++).setCellValue(obj.getOthFeeDesc() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEffectiveDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEndDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.FrwImpExpAction.java
@Override public String export() throws Exception { resultList = datasetFrwService.findMainList(dataSetDate, ""); resultSize = resultList.size();//from w w w. j a v a2 s .c om logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset FRW"); setFileName("Export Data DS_FRW_" + 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("dataSetDate"); row.createCell(cellnum++).setCellValue("currCode"); row.createCell(cellnum++).setCellValue("commInLieuRateForDepsit"); row.createCell(cellnum++).setCellValue("minCommInLieuForDepsit"); row.createCell(cellnum++).setCellValue("maxCommInLieuForDepsit"); row.createCell(cellnum++).setCellValue("inwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("minInwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("maxInwTransfFeeForDepsit"); row.createCell(cellnum++).setCellValue("othFeeDescForDepsit"); row.createCell(cellnum++).setCellValue("commInLieuRateForWithdw"); row.createCell(cellnum++).setCellValue("minCommInLieuRateFWithdw"); row.createCell(cellnum++).setCellValue("maxCommInLieuRateFWithdw"); row.createCell(cellnum++).setCellValue("withdwFeeForOthTransf"); row.createCell(cellnum++).setCellValue("effectiveDate"); row.createCell(cellnum++).setCellValue("endDate"); row.createCell(cellnum++).setCellValue("seq"); row.createCell(cellnum++).setCellValue("updBy"); row.createCell(cellnum++).setCellValue("updDate"); row.createCell(cellnum++).setCellValue("sysCode"); for (Datasetfrw obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurrCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRateForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieuForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieuForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxInwTransfFeeForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getOthFeeDescForDepsit() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCommInLieuRateForWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinCommInLieuRateFWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaxCommInLieuRateFWithdw() + ""); rowData.createCell(cellnum++).setCellValue(obj.getWithdwFeeForOthTransf() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEffectiveDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEndDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.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;// w ww . ja v a2 s . c om 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.IrfImpExpAction.java
@Override public String export() throws Exception { // paging = datasetIrfService.findListIrf(createPaginate(), dataSetDate, systemCode); resultList = datasetIrfService.findMainList(dataSetDate, ""); resultSize = resultList.size();/*from ww w. jav a 2 s . co m*/ logger.debug("exportExcel : begin..."); // resultList = manageService.findListDinamicSql(sql); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset IRF"); setFileName("Export Data DS_IRF_" + dateFormat.format(new Date()) + "excel".concat(".xls")); Row headerRow = mySheet.createRow(0); if (null != resultList && resultList.size() > 0) { /* create header excel */ // Map<String, String[]> column = new HashMap<String, String[]>(); // column.put("column", new String[]{"orgId", "dataSetDate", "arrgmentTye", "invPartyTye", "currCode", "depsitTerm", "depsitTermUnt", "balTierAmt", "interestRate", "effectiveDate", "endDate", "seq", "updBy", "updDate", "sysCode"}); // Set<String> newRowsColumn = column.keySet(); //Iterate over data and write to sheet // Set<String> keyset = column.keySet(); int rownum = 0; String[] column = new String[] { "orgId", "dataSetDate", "arrgmentTye", "invPartyTye", "currCode", "depsitTerm", "depsitTermUnt", "balTierAmt", "interestRate", "effectiveDate", "endDate", "seq", "updBy", "updDate", "sysCode" }; // Row rowColumn = mySheet.createRow(1); // int cellnum2 = 0; for (int i = 0; i < 1; i++) { // logger.debug("PALM : columnName >>> " + columnName); // Cell cell = rowColumn.createCell(cellnum2++); // cell.setCellValue(columnName); Row row = mySheet.createRow(rownum++); int cellnum = 0; //----- orgId Cell cellOrgId = row.createCell(cellnum++); cellOrgId.setCellValue("orgId"); //----- dataSetDate Cell cellDatasetDate = row.createCell(cellnum++); cellDatasetDate.setCellValue("dataSetDate"); //----- arrgmentTye Cell cellArrgmentType = row.createCell(cellnum++); cellArrgmentType.setCellValue("arrgmentTye"); //----- invPartyTye Cell cellInvPartyTye = row.createCell(cellnum++); cellInvPartyTye.setCellValue("invPartyTye"); //----- currCode Cell cellCurrCode = row.createCell(cellnum++); cellCurrCode.setCellValue("currCode"); //----- depsitTerm Cell cellDepsitTerm = row.createCell(cellnum++); cellDepsitTerm.setCellValue("depsitTerm"); //----- depsitTerm Unit Cell cellDepsitTermUnit = row.createCell(cellnum++); cellDepsitTermUnit.setCellValue("depsitTermUnit"); //----- balTierAmt Cell cellBalTierAmt = row.createCell(cellnum++); cellBalTierAmt.setCellValue("balTierAmt"); //----- interestRate Cell cellInterestRate = row.createCell(cellnum++); cellInterestRate.setCellValue("interestRate"); //----- effectiveDate Cell cellEffectiveDate = row.createCell(cellnum++); cellEffectiveDate.setCellValue("effectiveDate"); //----- endDate Cell cellEndDate = row.createCell(cellnum++); cellEndDate.setCellValue("endDate"); //----- seq Cell cellSeq = row.createCell(cellnum++); cellSeq.setCellValue("seq"); //----- updBy Cell cellUpdBy = row.createCell(cellnum++); cellUpdBy.setCellValue("updBy"); //----- updDate Cell cellUpdDate = row.createCell(cellnum++); cellUpdDate.setCellValue("updDate"); //----- sysCode Cell cellSysCode = row.createCell(cellnum++); cellSysCode.setCellValue("sysCode"); } // rownum = 2; for (Datasetirf obj : resultList) { Row row = mySheet.createRow(rownum++); int cellnum = 0; //----- orgId Cell cellOrgId = row.createCell(cellnum++); cellOrgId.setCellValue(obj.getOrgId() + ""); //----- dataSetDate Cell cellDatasetDate = row.createCell(cellnum++); cellDatasetDate.setCellValue(obj.getDataSetDate() + ""); //----- arrgmentTye Cell cellArrgmentType = row.createCell(cellnum++); cellArrgmentType.setCellValue(obj.getArrgmentTye() + ""); //----- invPartyTye Cell cellInvPartyTye = row.createCell(cellnum++); cellInvPartyTye.setCellValue(obj.getInvPartyTye() + ""); //----- currCode Cell cellCurrCode = row.createCell(cellnum++); cellCurrCode.setCellValue(obj.getCurrCode() + ""); //----- depsitTerm Cell cellDepsitTerm = row.createCell(cellnum++); cellDepsitTerm.setCellValue(obj.getDepsitTerm() + ""); //----- depsitTerm Cell cellDepsitTermUnit = row.createCell(cellnum++); cellDepsitTermUnit.setCellValue(obj.getDepsitTermUnt() + ""); //----- balTierAmt Cell cellBalTierAmt = row.createCell(cellnum++); cellBalTierAmt.setCellValue(obj.getBalTierAmt() + ""); //----- interestRate Cell cellInterestRate = row.createCell(cellnum++); cellInterestRate.setCellValue(obj.getInterestRate() + ""); //----- effective Date Cell cellEffectiveDate = row.createCell(cellnum++); cellEffectiveDate.setCellValue(obj.getEffectiveDate() + ""); //----- endDate Cell cellEndDate = row.createCell(cellnum++); cellEndDate.setCellValue(obj.getEndDate() + ""); //----- seq Cell cellSeq = row.createCell(cellnum++); cellSeq.setCellValue(obj.getSeq() + ""); //----- updBy Cell cellUpdBy = row.createCell(cellnum++); cellUpdBy.setCellValue(obj.getUpdBy() + ""); //----- updDate Cell cellUpdDate = row.createCell(cellnum++); cellUpdDate.setCellValue(obj.getUpdDate() + ""); //----- sysCode Cell cellSysCode = row.createCell(cellnum++); cellSysCode.setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.OffImpExpAction.java
@Override public String export() throws Exception { resultList = getDatasetOffService().findMainList(dataSetDate, ""); resultSize = resultList.size();//from www . j a v a 2 s.c o m logger.debug("exportExcel : begin..."); logger.debug("exportExcel : list >>> " + resultSize); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset OFF"); setFileName("Export Data DS_OFF_" + 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("dataSetDate"); row.createCell(cellnum++).setCellValue("arrgmentTye"); row.createCell(cellnum++).setCellValue("currCode"); row.createCell(cellnum++).setCellValue("minimOpenAcctAmt"); row.createCell(cellnum++).setCellValue("minimBalForMaintenanceFee"); row.createCell(cellnum++).setCellValue("maintenanceFee"); row.createCell(cellnum++).setCellValue("inactiveTerm"); row.createCell(cellnum++).setCellValue("inactiveTermUnt"); row.createCell(cellnum++).setCellValue("inactiveFee"); row.createCell(cellnum++).setCellValue("effectiveDate"); row.createCell(cellnum++).setCellValue("endDate"); row.createCell(cellnum++).setCellValue("seq"); row.createCell(cellnum++).setCellValue("updBy"); row.createCell(cellnum++).setCellValue("updDate"); row.createCell(cellnum++).setCellValue("sysCode"); for (Datasetoff obj : resultList) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getOrgId() + ""); rowData.createCell(cellnum++).setCellValue(obj.getDataSetDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getArrgmentTye() + ""); rowData.createCell(cellnum++).setCellValue(obj.getCurrCode() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinimOpenAcctAmt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMinimBalForMaintenanceFee() + ""); rowData.createCell(cellnum++).setCellValue(obj.getMaintenanceFee() + ""); rowData.createCell(cellnum++).setCellValue(obj.getInactiveTerm() + ""); rowData.createCell(cellnum++).setCellValue(obj.getInactiveTermUnt() + ""); rowData.createCell(cellnum++).setCellValue(obj.getInactiveFee() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEffectiveDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getEndDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSeq() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdBy() + ""); rowData.createCell(cellnum++).setCellValue(obj.getUpdDate() + ""); rowData.createCell(cellnum++).setCellValue(obj.getSysCode() + ""); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; }
From source file:com.kcs.action.OutstandingReportAction.java
@Override public String export() throws Exception { logger.debug("= = = = = EXPORT = = = = ="); logger.debug("txtDatasetDate >>> " + txtDatasetDate + " >>> " + txtParamConfig); // servletRequest = ServletActionContext.getRequest(); // servletResponse = ServletActionContext.getResponse(); // //from w ww .j a v a2s. c om // servletResponse.setContentType("application/vnd.ms-excel"); // servletResponse.setCharacterEncoding("UTF-8"); // DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); // servletResponse.setHeader("Content-Disposition", "attachment; filename=excelTracking_" + dateFormat.format(new Date()) + ".xls"); // PrintWriter out = servletResponse.getWriter(); // // out.println(" <head> "); // out.println(" <meta http-equiv=\"Content-Type\" content=\"application/vnd.ms-excel; charset=UTF-8\"> "); // out.println(" </head> "); // out.println(" <table width=\"100%\" border=\"1\" cellspacing=\"1\" cellpadding=\"0\"> "); // out.println(" <tr> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">App Date</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">App Ref</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Company ID</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Company Name</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Action</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">System</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Submit ID</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Submit Name</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Overall Status</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Accepted Date</td> "); // out.println(" <td style=\"width:150px;text-align:center;background:#CCC;font-weight:bold\">Completed Date</td> "); // out.println(" </tr> "); list = outstReportService.findByCriteriaDepositService(DateUtil.convertDateFromJsp(txtDatasetDate)); DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd_HHmmss"); HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Dataset IRF"); setFileName("outstanding_report_deposit_" + dateFormat.format(new Date()) + "excel".concat(".xls")); Row headerRow = mySheet.createRow(0); if (null != list && list.size() > 0) { int rownum = 1; String[] column = new String[] { "DATA_DATE", "SYS_CODE", "DATA_PVDR_IBF_IND", "FI_ARRG_NO", "CURR", "TRAN_AMT", "LAST_OUTST_DATA_SET_DATE", "CUST_CODE", "NAT", "CTRY" }; Row rowColumn = mySheet.createRow(1); for (String columnName : column) { int cellnum = 0; Cell cell = rowColumn.createCell(cellnum++); cell.setCellValue(columnName); } for (OutstandingReport obj : list) { Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue(obj.getDepositDataDate()); row.createCell(cellnum++).setCellValue(obj.getDepositSysCode()); row.createCell(cellnum++).setCellValue(obj.getDepositDataPvdrIbfInd()); row.createCell(cellnum++).setCellValue(obj.getDepositFiArrgNo()); row.createCell(cellnum++).setCellValue(obj.getDepositCurr()); row.createCell(cellnum++).setCellValue(String.valueOf(obj.getDepositTranAmt())); row.createCell(cellnum++).setCellValue(obj.getDepositLastOutstDataSetDate()); row.createCell(cellnum++).setCellValue(obj.getDepositCustCode()); row.createCell(cellnum++).setCellValue(obj.getDepositNat()); row.createCell(cellnum++).setCellValue(obj.getDepositCtry()); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excelDeposit"; // String strReturn = SUCCESS; // if("Deposit".equals(txtParamConfig)){ // strReturn = "exportDeposit"; // list = outstReportService.findByCriteriaDepositService(DateUtil.convertDateFromJsp(txtDatasetDate)); // }else if("Forward".equals(txtParamConfig)){ // strReturn = "exportForward"; // list = outstReportService.findByCriteriaForwardService(DateUtil.convertDateFromJsp(txtDatasetDate)); // }else if("Loan".equals(txtParamConfig)){ // strReturn = "exportLoan"; // list = outstReportService.findByCriteriaLoanService(DateUtil.convertDateFromJsp(txtDatasetDate)); // } // return strReturn; //To change body of generated methods, choose Tools | Templates. }