List of usage examples for org.apache.poi.ss.usermodel IndexedColors YELLOW
IndexedColors YELLOW
To view the source code for org.apache.poi.ss.usermodel IndexedColors YELLOW.
Click Source Link
From source file:cn.com.zhbook.component.poi.PoiPerformanceTest.java
License:Apache License
static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;//from w w w . j ava 2 s . co m Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 14); headerFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); styles.put("header", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.RED.getIndex()); monthFont.setBold(true); style = wb.createCellStyle(); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("red-bold", style); String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" }; for (String fmt : nfmt) { style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat(fmt)); styles.put(fmt, style); } return styles; }
From source file:com.catexpress.util.FormatosPOI.java
public CellStyle estiloCuadro(Workbook wb, int tipo) { CellStyle estiloCelda = wb.createCellStyle(); Font fuente = wb.createFont(); if (tipo == AMARILLO) { estiloCelda.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); estiloCelda.setFillPattern(CellStyle.SOLID_FOREGROUND); }//from w w w . j a v a 2 s . co m fuente.setFontName("Calibri"); fuente.setBold(true); fuente.setFontHeightInPoints((short) 12); estiloCelda.setFont(fuente); estiloCelda.setVerticalAlignment(VerticalAlignment.CENTER); estiloCelda.setAlignment(HorizontalAlignment.CENTER); estiloCelda.setBorderBottom(BorderStyle.THIN); estiloCelda.setBorderTop(BorderStyle.THIN); estiloCelda.setBorderLeft(BorderStyle.THIN); estiloCelda.setBorderRight(BorderStyle.THIN); return estiloCelda; }
From source file:com.dfpray.formatter.CardModel.java
/** * Exports List of B.C to Excel file, Path should include name and format .xlsx ending * @param path//from w ww . j a v a 2s .c o m * @throws IOException */ public void exportToExcel(String path) throws IOException { System.out.println("Called "); BusinessCard card; Cell cell; String[] info; Double number; String cardInfo; Row row; //Create Blank workbook/sheet @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Business Data"); String[] tmpArray = { "CompanyName", "ContactFirstName", "ContactLastName", "Title", "Street Address", "Suite/PO Box", "City", "State", "ZipCode", "Country", "PhoneNumber", "Extension", "MobilePhone", "FaxNumber", "EmailAddress", "Website", "CsiCodes", "CompanyFunction", "MBEAffiliations", "Labor", "ServiceArea", "CompanyNotes", "ContactLists", "CF_Alternate Email", "CF_Do Not Use", "CF_Supplier/Manuf", "CF_Trade", "CF_Union Value", "CF_Unlicensed States", "CF_Will Not Bid" }; Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(headerFont); XSSFCellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setFont(headerFont); cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle2.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); //Write Template row = sheet.createRow(0); for (int k = 0; k < 30; k++) { cell = row.createCell(k); cell.setCellStyle(cellStyle); if (k == 0 || k == 13 || k == 14 || k == 16 || k == 17) { cell.setCellStyle(cellStyle2); } cell.setCellValue(tmpArray[k]); } //Row = Business for (int i = 1; i <= amtCards(); i++) { row = sheet.createRow(i); card = cards.get(i - 1); info = card.infoToArray(); //Create Column = Data for each Business for (int k = 0; k < 30; k++) { cardInfo = info[k]; cell = row.createCell(k); if (k == 24) continue; try { number = Double.parseDouble(cardInfo); cell.setCellValue(number); } catch (NumberFormatException e) { cell.setCellValue(cardInfo); } } card.setExported(true); } //Create file system using specific name FileOutputStream out; try { out = new FileOutputStream(new File(path)); } catch (FileNotFoundException e) { //Reset cards to not exported for (BusinessCard cardR : cards) { cardR.setExported(false); } throw new IOException(); } workbook.write(out); out.close(); }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.TranslationVerificationReportGenerator.java
License:Apache License
private CellStyle getContentStyle1(Workbook p_workbook) { CellStyle style = p_workbook.createCellStyle(); style.setWrapText(true);//from ww w .j av a 2 s . co m style.setAlignment(CellStyle.ALIGN_LEFT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font font = p_workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); style.setFont(font); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); return style; }
From source file:com.globalsight.everest.webapp.pagehandler.administration.reports.generator.TranslationVerificationReportGenerator.java
License:Apache License
private CellStyle getRtlContentStyle1(Workbook p_workbook) { Font font = p_workbook.createFont(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); CellStyle style = p_workbook.createCellStyle(); style.setFont(font);/*from w w w.j a v a2 s .c om*/ style.setWrapText(true); style.setAlignment(CellStyle.ALIGN_RIGHT); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); return style; }
From source file:com.smi.travel.controller.excel.checking.OverdueSummaryExcel.java
private void getOverdueSummary(HSSFWorkbook wb, List refundTicket) { String sheetName = "overdueSummary";// name of sheet HSSFSheet sheet1 = wb.createSheet(sheetName); UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleAlignRightBorderAllHeaderTable = wb.createCellStyle(); styleAlignRightBorderAllHeaderTable.setFont(excelFunction.getHeaderTable(wb.createFont())); styleAlignRightBorderAllHeaderTable.setAlignment(styleAlignRightBorderAllHeaderTable.ALIGN_CENTER); styleAlignRightBorderAllHeaderTable.setBorderTop(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderBottom(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderRight(styleAlignRightBorderAllHeaderTable.BORDER_THIN); styleAlignRightBorderAllHeaderTable.setBorderLeft(styleAlignRightBorderAllHeaderTable.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAllNumber = wb.createCellStyle(); styleAlignRightBorderAllNumber.setAlignment(styleAlignRightBorderAllNumber.ALIGN_RIGHT); styleAlignRightBorderAllNumber.setDataFormat(currency.getFormat("#,##0.00")); styleAlignRightBorderAllNumber.setBorderTop(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderBottom(styleAlignRightBorderAllNumber.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderRight(styleAlignRightBorderAllNumber.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAll = wb.createCellStyle(); styleAlignRightBorderAll.setAlignment(styleAlignRightBorderAll.ALIGN_LEFT); styleAlignRightBorderAll.setBorderTop(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderBottom(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderRight(styleAlignRightBorderAll.BORDER_THIN); styleAlignRightBorderAll.setBorderLeft(styleAlignRightBorderAll.BORDER_THIN); HSSFCellStyle styleNumber = wb.createCellStyle(); styleNumber.setAlignment(styleNumber.ALIGN_RIGHT); styleNumber.setDataFormat(currency.getFormat("#,##0.00")); HSSFCellStyle styleNumberBorderRight = wb.createCellStyle(); styleNumberBorderRight.setAlignment(styleNumberBorderRight.ALIGN_RIGHT); styleNumberBorderRight.setDataFormat(currency.getFormat("#,##0.00")); styleNumberBorderRight.setBorderRight(styleNumberBorderRight.BORDER_THIN); HSSFCellStyle styleBorderBottom = wb.createCellStyle(); //use styleBorderBottom.setBorderBottom(styleBorderBottom.BORDER_THIN); styleAlignRightBorderAllNumber.setBorderLeft(styleAlignRightBorderAllNumber.BORDER_THIN); HSSFCellStyle styleAlignRightBorderBottomRight = wb.createCellStyle();//use styleAlignRightBorderBottomRight.setAlignment(styleAlignRightBorderBottomRight.ALIGN_LEFT); styleAlignRightBorderBottomRight.setBorderBottom(styleAlignRightBorderBottomRight.BORDER_THIN); styleAlignRightBorderBottomRight.setBorderRight(styleAlignRightBorderBottomRight.BORDER_THIN); HSSFCellStyle styleAlignRightBorderAllColor = wb.createCellStyle(); styleAlignRightBorderAllColor.setFont(excelFunction.getHeaderTable(wb.createFont())); styleAlignRightBorderAllColor.setAlignment(styleAlignRightBorderAllColor.ALIGN_LEFT); styleAlignRightBorderAllColor.setBorderTop(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderBottom(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderRight(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setBorderLeft(styleAlignRightBorderAllColor.BORDER_THIN); styleAlignRightBorderAllColor.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); HSSFCellStyle styleAlignRightBorderAllDetailTable = wb.createCellStyle(); styleAlignRightBorderAllDetailTable.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont())); styleAlignRightBorderAllDetailTable.setAlignment(styleAlignRightBorderAllDetailTable.ALIGN_LEFT); styleAlignRightBorderAllDetailTable.setBorderTop(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderBottom(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderRight(styleAlignRightBorderAllDetailTable.BORDER_THIN); styleAlignRightBorderAllDetailTable.setBorderLeft(styleAlignRightBorderAllDetailTable.BORDER_THIN); HSSFCellStyle total = wb.createCellStyle(); total.setFont(excelFunction.getHeadDetailBoldFont(wb.createFont())); total.setAlignment(total.ALIGN_CENTER); total.setBorderTop(total.BORDER_THIN); total.setBorderBottom(total.BORDER_THIN); total.setBorderRight(total.BORDER_THIN); total.setBorderLeft(total.BORDER_THIN); // set Header Report (Row 1) HSSFCellStyle styleC11 = wb.createCellStyle(); HSSFRow row01 = sheet1.createRow(0); HSSFCell cell01 = row01.createCell(0); cell01.setCellValue("Overdue Summary"); styleC11.setFont(excelFunction.getHeaderFont(wb.createFont())); cell01.setCellStyle(styleC11);// w ww .j a v a 2s. co m sheet1.addMergedRegion(CellRangeAddress.valueOf("A1:G1")); List<OverdueSummartExcel> listOver = new ArrayList<OverdueSummartExcel>(); if (refundTicket != null && refundTicket.size() != 0) { listOver = refundTicket; } else { listOver = null; } OverdueSummartExcel over = new OverdueSummartExcel(); if ((refundTicket != null) && (refundTicket.size() != 0)) { over = (OverdueSummartExcel) refundTicket.get(0); } // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); // Row 2 HSSFRow row02 = sheet1.createRow(1); HSSFCell cell021 = row02.createCell(0); cell021.setCellValue("Client : "); cell021.setCellStyle(styleC21); HSSFCell cell022 = row02.createCell(1); cell022.setCellValue(over.getClientname_page()); cell022.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B2:D2")); HSSFCell cell023 = row02.createCell(4); cell023.setCellValue("Agent : "); cell023.setCellStyle(styleC21); HSSFCell cell024 = row02.createCell(5); cell024.setCellValue(over.getStaffname_page()); cell024.setCellStyle(styleC22); // Row 3 HSSFRow row03 = sheet1.createRow(2); HSSFCell cell031 = row03.createCell(0); cell031.setCellValue("Date : "); cell031.setCellStyle(styleC21); HSSFCell cell032 = row03.createCell(1); cell032.setCellValue(over.getFrom_page()); cell032.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B3:D3")); HSSFCell cell033 = row03.createCell(4); cell033.setCellValue("Vat Type : "); cell033.setCellStyle(styleC21); HSSFCell cell034 = row03.createCell(5); cell034.setCellValue(over.getVattype_page()); cell034.setCellStyle(styleC22); // Row 4 HSSFRow row04 = sheet1.createRow(3); HSSFCell cell041 = row04.createCell(0); cell041.setCellValue("Department : "); cell041.setCellStyle(styleC21); HSSFCell cell042 = row04.createCell(1); cell042.setCellValue(over.getDepart_page()); cell042.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B4:D4")); HSSFCell cell043 = row04.createCell(4); cell043.setCellValue("View : "); cell043.setCellStyle(styleC21); HSSFCell cell044 = row04.createCell(5); cell044.setCellValue(over.getView_page()); cell044.setCellStyle(styleC22); // Row 5 HSSFRow row05 = sheet1.createRow(4); HSSFCell cell051 = row05.createCell(0); cell051.setCellValue("Group : "); cell051.setCellStyle(styleC21); sheet1.addMergedRegion(CellRangeAddress.valueOf("B5:D5")); HSSFCell cell052 = row05.createCell(1); cell052.setCellValue(over.getGroup_page()); cell052.setCellStyle(styleC22); HSSFCell cell053 = row05.createCell(4); cell053.setCellValue("Print By : "); cell053.setCellStyle(styleC21); HSSFCell cell054 = row05.createCell(5); cell054.setCellValue(over.getPrintby_page()); cell054.setCellStyle(styleC22); // Row 3 HSSFRow row06 = sheet1.createRow(5); HSSFCell cell061 = row06.createCell(0); cell061.setCellValue("Print Date : "); cell061.setCellStyle(styleC21); HSSFCell cell062 = row06.createCell(1); cell062.setCellValue(over.getPrintdate_page()); cell062.setCellStyle(styleC22); sheet1.addMergedRegion(CellRangeAddress.valueOf("B6:D6")); // Header Table HSSFRow row6 = sheet1.createRow(8); HSSFCell cell61 = row6.createCell(0); cell61.setCellValue("Invoice No."); cell61.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(0); HSSFCell cell62 = row6.createCell(1); cell62.setCellValue("Date"); cell62.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(1); HSSFCell cell63 = row6.createCell(2); cell63.setCellValue("Detail"); sheet1.autoSizeColumn(2); cell63.setCellStyle(styleAlignRightBorderAllHeaderTable); HSSFCell cell64 = row6.createCell(3); cell64.setCellValue("Baht"); cell64.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(3); HSSFCell cell65 = row6.createCell(4); cell65.setCellValue("JPY"); cell65.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(4); HSSFCell cell66 = row6.createCell(5); cell66.setCellValue("USD"); cell66.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(5); HSSFCell cell67 = row6.createCell(6); cell67.setCellValue("Rec No"); cell67.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(6); HSSFCell cell68 = row6.createCell(7); cell68.setCellValue("Rec Amt"); cell68.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(7); HSSFCell cell69 = row6.createCell(8); cell69.setCellValue("Department"); cell69.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(8); HSSFCell cell70 = row6.createCell(9); cell70.setCellValue("Credit"); cell70.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(9); HSSFCell cell71 = row6.createCell(10); cell71.setCellValue("Ref No"); cell71.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(10); HSSFCell cell72 = row6.createCell(11); cell72.setCellValue("Due Date"); cell72.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(11); HSSFCell cell73 = row6.createCell(12); cell73.setCellValue("Overdue Status"); cell73.setCellStyle(styleAlignRightBorderAllHeaderTable); sheet1.autoSizeColumn(12); int start = 11; int end = 0; int num = 0; int count = 9; String temp = ""; // String sumThbAll = ""; // String sumJpyAll = ""; // String sumUsdAll = ""; // String sumRecAmtAll = ""; BigDecimal bahtTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal jpyTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal usdTotalAll = new BigDecimal(BigInteger.ZERO); BigDecimal recamtTotalAll = new BigDecimal(BigInteger.ZERO); if (listOver != null && listOver.size() != 0) { for (int r = 0; r < listOver.size(); r++) { if (r != 0) { if ("Agent".equals(listOver.get(r).getGroup())) { bahtTotalAll = bahtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); if (temp.equals(listOver.get(r).getInvto())) { // equal type if (r != (listOver.size() - 1)) { // check not last row HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } else { HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } } else { // not equal type // Start New Row (Group) String totalthb = "SUM(D" + start + ":D" + (r + count) + ")"; String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")"; String totalusd = "SUM(F" + start + ":F" + (r + count) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")"; // sumThbAll += ",D"+(count+r+1); // sumJpyAll += ",E"+(count+r+1); // sumUsdAll += ",F"+(count+r+1); // sumRecAmtAll += ",H"+(count+r+1); start = count + r + 3; HSSFRow row00 = sheet1.createRow(r + count); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + "")); HSSFRow row0 = sheet1.createRow(r + count + 1); HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getInvto()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row122 = sheet1.createRow(r + count + 2); createCell(row122, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 2; } temp = listOver.get(r).getInvto(); if (r == (listOver.size() - 1)) { String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")"; String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")"; String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")"; // sumThbAll += ",D"+(count+r+2); // sumJpyAll += ",E"+(count+r+2); // sumUsdAll += ",F"+(count+r+2); // sumRecAmtAll += ",H"+(count+r+2); HSSFRow row00 = sheet1.createRow(count + r + 1); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + "")); HSSFRow rowTotalAll = sheet1.createRow(count + r + 2); HSSFCell cellTotal00 = rowTotalAll.createCell(0); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(total); HSSFCell cellTotal001 = rowTotalAll.createCell(1); cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cellTotal002 = rowTotalAll.createCell(2); cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + "")); HSSFCell cellTotal003 = rowTotalAll.createCell(3); cellTotal003.setCellValue(bahtTotalAll.doubleValue()); cellTotal003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal004 = rowTotalAll.createCell(4); cellTotal004.setCellValue(jpyTotalAll.doubleValue()); cellTotal004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal005 = rowTotalAll.createCell(5); cellTotal005.setCellValue(usdTotalAll.doubleValue()); cellTotal005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal006 = rowTotalAll.createCell(6); cellTotal006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal007 = rowTotalAll.createCell(7); cellTotal007.setCellValue(recamtTotalAll.doubleValue()); cellTotal007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cellTotal008 = rowTotalAll.createCell(k); cellTotal008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + "")); } } else if ("Owner".equals(listOver.get(r).getGroup())) { bahtTotalAll = bahtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); if (temp.equals(listOver.get(r).getOwnername())) { // equal type if (r != (listOver.size() - 1)) { // check not last row HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } else { HSSFRow row = sheet1.createRow(r + count); createCell(row, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); } } else { // not equal type // Start New Row (Group) String totalthb = "SUM(D" + start + ":D" + (r + count) + ")"; String totaljpy = "SUM(E" + start + ":E" + (r + count) + ")"; String totalusd = "SUM(F" + start + ":F" + (r + count) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (r + count) + ")"; // sumThbAll += ",D"+(count+r+1); // sumJpyAll += ",E"+(count+r+1); // sumUsdAll += ",F"+(count+r+1); // sumRecAmtAll += ",H"+(count+r+1); start = count + r + 3; HSSFRow row00 = sheet1.createRow(r + count); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 1) + ":C" + (count + r + 1) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 1) + ":M" + (count + r + 1) + "")); HSSFRow row0 = sheet1.createRow(r + count + 1); HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getOwnername()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); String add = "A" + (r + count + 2) + ":M" + (r + count + 2) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row122 = sheet1.createRow(r + count + 2); createCell(row122, listOver, r, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 2; } temp = listOver.get(r).getOwnername(); if (r == (listOver.size() - 1)) { String totalthb = "SUM(D" + start + ":D" + (count + r + 1) + ")"; String totaljpy = "SUM(E" + start + ":E" + (count + r + 1) + ")"; String totalusd = "SUM(F" + start + ":F" + (count + r + 1) + ")"; String totalrecamt = "SUM(H" + start + ":H" + (count + r + 1) + ")"; // sumThbAll += ",D"+(count+r+2); // sumJpyAll += ",E"+(count+r+2); // sumUsdAll += ",F"+(count+r+2); // sumRecAmtAll += ",H"+(count+r+2); HSSFRow row00 = sheet1.createRow(count + r + 1); HSSFCell cell00 = row00.createCell(0); cell00.setCellValue(""); cell00.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell001 = row00.createCell(1); cell001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cell002 = row00.createCell(2); cell002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 2) + ":C" + (count + r + 2) + "")); HSSFCell cell003 = row00.createCell(3); cell003.setCellFormula(totalthb); cell003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell004 = row00.createCell(4); cell004.setCellFormula(totaljpy); cell004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell005 = row00.createCell(5); cell005.setCellFormula(totalusd); cell005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell006 = row00.createCell(6); cell006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cell007 = row00.createCell(7); cell007.setCellFormula(totalrecamt); cell007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cell008 = row00.createCell(k); cell008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 2) + ":M" + (count + r + 2) + "")); HSSFRow rowTotalAll = sheet1.createRow(count + r + 2); HSSFCell cellTotal00 = rowTotalAll.createCell(0); cellTotal00.setCellValue("Total"); cellTotal00.setCellStyle(total); HSSFCell cellTotal001 = rowTotalAll.createCell(1); cellTotal001.setCellStyle(styleAlignRightBorderAllDetailTable); HSSFCell cellTotal002 = rowTotalAll.createCell(2); cellTotal002.setCellStyle(styleAlignRightBorderAllDetailTable); sheet1.addMergedRegion( CellRangeAddress.valueOf("A" + (count + r + 3) + ":C" + (count + r + 3) + "")); HSSFCell cellTotal003 = rowTotalAll.createCell(3); cellTotal003.setCellValue(bahtTotalAll.doubleValue()); cellTotal003.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal004 = rowTotalAll.createCell(4); cellTotal004.setCellValue(jpyTotalAll.doubleValue()); cellTotal004.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal005 = rowTotalAll.createCell(5); cellTotal005.setCellValue(usdTotalAll.doubleValue()); cellTotal005.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal006 = rowTotalAll.createCell(6); cellTotal006.setCellStyle(styleAlignRightBorderAllNumber); HSSFCell cellTotal007 = rowTotalAll.createCell(7); cellTotal007.setCellValue(recamtTotalAll.doubleValue()); cellTotal007.setCellStyle(styleAlignRightBorderAllNumber); for (int k = 8; k < 13; k++) { HSSFCell cellTotal008 = rowTotalAll.createCell(k); cellTotal008.setCellStyle(styleAlignRightBorderAllNumber); } sheet1.addMergedRegion( CellRangeAddress.valueOf("I" + (count + r + 3) + ":M" + (count + r + 3) + "")); } } } else { bahtTotalAll = bahtTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getBath())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getBath()))); jpyTotalAll = jpyTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getJpy())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getJpy()))); usdTotalAll = usdTotalAll .add("".equalsIgnoreCase(String.valueOf(listOver.get(r).getUsd())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getUsd()))); recamtTotalAll = recamtTotalAll.add( "".equalsIgnoreCase(String.valueOf(listOver.get(r).getRecamt())) ? new BigDecimal(0) : (new BigDecimal(listOver.get(r).getRecamt()))); HSSFRow row0 = sheet1.createRow(count + r); if ("Owner".equals(listOver.get(r).getGroup())) { HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getOwnername()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); } else if ("Agent".equals(listOver.get(r).getGroup())) { HSSFCell cell = row0.createCell(0); cell.setCellValue(listOver.get(r).getInvto()); cell.setCellStyle(styleAlignRightBorderAllDetailTable); } String add = "A" + (count + r + 1) + ":M" + (count + r + 1) + ""; sheet1.addMergedRegion(CellRangeAddress.valueOf(add)); row0.createCell(12).setCellStyle(styleAlignRightBorderAllColor); HSSFRow row = sheet1.createRow(count + r + 1); createCell(row, listOver, num, styleAlignRightBorderAllNumber, styleAlignRightBorderAll); sheet1.autoSizeColumn(13); count = count + 1; } } for (int i = 0; i < 30; i++) { sheet1.autoSizeColumn(i); } } }
From source file:com.svi.main.logic.ExtractAndPrint.java
private void writeLogFile(List<Nodes> nodesHolder) { DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HHmm"); Date date = new Date(); Sheet mainSheet;// w w w . j a v a 2 s . c o m Sheet dataSheet; Sheet elementSheet; Row dataSheetRow; Row elementSheetRow; InputStream fis; XSSFWorkbook workbook; File outputPath = new File(logPath + "\\Logs"); File outputFile = new File(logPath + "\\Logs\\BPO KPI Report " + dateFormat.format(date) + ".xlsx"); // File name int dataSheetRowCount = 1; int elementSheetRowCount = 1; int totalElementException = 0; try { if (!Files.exists(outputPath.toPath())) { Files.createDirectories(outputPath.toPath()); } fis = ExtractAndPrint.class.getResourceAsStream("bpo_template.xlsx"); workbook = new XSSFWorkbook(fis); //Style for exception sheet XSSFCellStyle style = workbook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //Get data Sheet mainSheet = workbook.getSheetAt(0); writeProjectDetails(mainSheet); dataSheet = workbook.getSheetAt(4); dataSheetRow = dataSheet.createRow(0); elementSheet = workbook.getSheetAt(1); elementSheetRow = elementSheet.createRow(0); //Write excel headers writeDataSheetHeaders(dataSheetRow); writeElementSheetHeaders(elementSheetRow); //Set progress bar values progress = new AtomicInteger(0); total = new AtomicInteger(nodesHolder.size()); mf.setJprogressValues(total, progress); // Sort the nodes per Node ID Collections.sort(nodesHolder, new Comparator<Nodes>() { public int compare(Nodes o1, Nodes o2) { return o1.getNodeId().compareTo(o2.getNodeId()); } }); //Write Data Sheet for (Nodes node : nodesHolder) { mf.loader(); dataSheetRow = dataSheet.createRow(dataSheetRowCount++); writeDataSheet(node, dataSheetRow); } for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { totalElementException++; } } } progress = new AtomicInteger(0); total = new AtomicInteger(totalElementException); mf.setJprogressValues(total, progress); //Write exception sheet for (Nodes node : nodesHolder) { for (Elements e : node) { if ((e.getStatus().equalsIgnoreCase("COMPLETE") || e.getStatus().equalsIgnoreCase("PROCESSING")) && e.getTotalProcTime() > MAX_MINUTES_ELEMENT) { elementSheetRow = elementSheet.createRow(elementSheetRowCount++); writeElementSheet(e, elementSheetRow); mf.elementLoader(); } } } XSSFFormulaEvaluator.evaluateAllFormulaCells((XSSFWorkbook) workbook); try (FileOutputStream outputStream = new FileOutputStream(outputFile)) { workbook.write(outputStream);//Write the Excel File outputStream.close(); } workbook.close(); fis.close(); mf.generateMessage(); } catch (Exception ex) { Logger.getLogger(ExtractAndPrint.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.wantdo.stat.excel.poi_src.SSPerformanceTest.java
License:Apache License
static Map<String, CellStyle> createStyles(Workbook wb) { Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); CellStyle style;/*from w w w . j av a 2 s . c o m*/ Font headerFont = wb.createFont(); headerFont.setFontHeightInPoints((short) 14); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFont(headerFont); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); styles.put("header", style); Font monthFont = wb.createFont(); monthFont.setFontHeightInPoints((short) 12); monthFont.setColor(IndexedColors.RED.getIndex()); monthFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(monthFont); styles.put("red-bold", style); String[] nfmt = { "#,##0.00", "$#,##0.00", "m/d/yyyy" }; for (String fmt : nfmt) { style = wb.createCellStyle(); style.setDataFormat(wb.createDataFormat().getFormat(fmt)); styles.put(fmt, style); } return styles; }
From source file:eventHandlers.CompareDrotVSRoster.java
private void foregroundColorSetUp(Row row, XSSFWorkbook myWorkBook) { Cell ssnCell = row.getCell(GlobalVar.LAST4_CELL_INDEX_V1); DataFormatter df = new DataFormatter(); String ssnString = df.formatCellValue(ssnCell); //return ***-**-**** ssnString = ssnString.replace("-", ""); Cell soCell = row.getCell(GlobalVar.SIGN_OUT_DATE_CELL_INDEX_V1); String signOutDateString = df.formatCellValue(soCell); //return ***-**-**** Cell ctrlNumCell = row.getCell(GlobalVar.CTRL_NUM_CELL_INDEX_V1); String ctrlNumString = ctrlNumCell.getStringCellValue(); if (proclibrary.containsKey(ssnString)) { // Map< SSN, Map<ctrlNum, signOutdate>> Map<String, String> leaves = proclibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.GREEN.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); }/* ww w . jav a 2 s . c o m*/ } } else if (rejlibrary.containsKey(ssnString)) { Map<String, String> leaves = rejlibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.RED.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } else if (recylibrary.containsKey(ssnString)) { Map<String, String> leaves = recylibrary.get(ssnString); if (leaves.containsKey(ctrlNumString)) { String date = leaves.get(ctrlNumString); if (date.equals(signOutDateString)) { CellStyle style = createStandardStyle(myWorkBook); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); ctrlNumCell.setCellStyle(style); } } } }
From source file:Excel.InformeArticulos.java
public void GenerarInforme(ArrayList listadoClientes) throws SQLException { HSSFWorkbook libro = new HSSFWorkbook(); HSSFSheet hoja = libro.createSheet("Listado de Articulos"); ArrayList listadoPorSucursal = new ArrayList(); Editables edi = new Articulos(); /*/*from w w w .j av a2 s. c om*/ * GENERAR LAS SIGUIENTES HOJAS * 1- DETALLE DE MOVIMIENTOS DE CAJA - LEE EN MOVIMIENTOS CAJA INDENTIFICANDO EL TIPO DE MOVIMIENTO, USUARIOS Y * NUMERO DE CAJA * 2- DETALLE DE ARTICULOS VENDIDOS: LISTADO DE MOVIEMIENTOS DE ARTICULOS, CON USUARIOS Y CAJA * 3- DETALLE DE GASTOS : MOVIMIENTOS DE CAJA DETALLANDO LOS GASTOS * */ String ttx = "celda numero :"; HSSFRow fila = null; HSSFCell celda; HSSFCell celda1; HSSFCell celda2; HSSFCell celda3; HSSFCell celda4; HSSFCell celda5; HSSFCell celda6; HSSFCell celda7; HSSFCell celda8; HSSFCell celda9; HSSFCell celda10; HSSFCell celda11; HSSFFont fuente = libro.createFont(); //fuente.setFontHeight((short)21); fuente.setFontName(fuente.FONT_ARIAL); fuente.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); String form = null; HSSFCellStyle titulo = libro.createCellStyle(); Iterator iCli = listadoClientes.listIterator(); Articulos cliente = new Articulos(); titulo.setFont(fuente); //titulo.setFillBackgroundColor((short)22); titulo.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); titulo.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); //for(int a=0;a < 100;a++){ int col = 0; int a = 0; if (a == 0) { fila = hoja.createRow(a); celda = fila.createCell(0); celda.setCellStyle(titulo); celda.setCellValue("Codigo"); celda1 = fila.createCell(1); celda1.setCellStyle(titulo); celda1.setCellValue("Descripcion"); celda2 = fila.createCell(2); celda2.setCellStyle(titulo); celda2.setCellValue("Stock"); celda3 = fila.createCell(3); celda3.setCellStyle(titulo); celda3.setCellValue("Stock Mnimo"); celda4 = fila.createCell(4); celda4.setCellStyle(titulo); celda4.setCellValue("Costo"); celda5 = fila.createCell(5); celda5.setCellStyle(titulo); celda5.setCellValue("Precio de Venta"); celda6 = fila.createCell(6); celda6.setCellStyle(titulo); celda6.setCellValue("Servicio"); } while (iCli.hasNext()) { cliente = (Articulos) iCli.next(); a++; //col=rs.getInt("tipoMovimiento"); switch (col) { case 1: break; default: break; } fila = hoja.createRow(a); celda = fila.createCell(0); ttx = ttx; celda.setCellType(HSSFCell.CELL_TYPE_STRING); celda.setCellValue(cliente.getCodigoAsignado()); celda1 = fila.createCell(1); ttx = ttx; celda1.setCellType(HSSFCell.CELL_TYPE_STRING); celda1.setCellValue(cliente.getDescripcionArticulo()); celda2 = fila.createCell(2); celda2.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda2.setCellValue(cliente.getStockActual()); celda3 = fila.createCell(3); celda3.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda3.setCellValue(cliente.getStockMinimo()); celda4 = fila.createCell(4); celda4.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda4.setCellValue(cliente.getPrecioDeCosto()); celda5 = fila.createCell(5); //celda5.setCellFormula(rs.getString("observaciones")); celda5.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda5.setCellValue(cliente.getPrecioUnitarioNeto()); //celda5.setCellValue(rs.getDate("fecha")); celda6 = fila.createCell(6); //celda5.setCellFormula(rs.getString("observaciones")); celda6.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda6.setCellValue(cliente.getPrecioServicio()); listadoPorSucursal = edi.ListarPorSucursal(cliente); Iterator il = listadoPorSucursal.listIterator(); Articulos arr = new Articulos(); int cont = 0; while (il.hasNext()) { arr = (Articulos) il.next(); cont++; switch (cont) { case 1: celda7 = fila.createCell(7); celda7.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda7.setCellValue(arr.getCantidad()); break; case 2: celda8 = fila.createCell(8); celda8.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda8.setCellValue(arr.getCantidad()); break; case 3: celda9 = fila.createCell(9); celda9.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda9.setCellValue(arr.getCantidad()); break; case 4: celda10 = fila.createCell(10); celda10.setCellType(HSSFCell.CELL_TYPE_NUMERIC); celda10.setCellValue(arr.getCantidad()); break; } } } //texto+="\r\n"; String ruta = "C://Informes//listadoDeArticulos.xls"; try { FileOutputStream elFichero = new FileOutputStream(ruta); try { libro.write(elFichero); elFichero.close(); Runtime.getRuntime().exec("rundll32 url.dll,FileProtocolHandler " + ruta); } catch (IOException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } } catch (FileNotFoundException ex) { Logger.getLogger(InformeMensual.class.getName()).log(Level.SEVERE, null, ex); } }