Example usage for org.apache.poi.ss.usermodel Row getCell

List of usage examples for org.apache.poi.ss.usermodel Row getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row getCell.

Prototype

Cell getCell(int cellnum);

Source Link

Document

Get the cell representing a given column (logical cell) 0-based.

Usage

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

public byte[] build(List<Map<String, String>> row) throws IOException {
    List<String> colLabels = new ArrayList<String>();
    Sheet reportSheet = workbook.getSheet("Report");
    if (reportSheet == null) {
        reportSheet = workbook.createSheet("Report");
    }// w  w w . j ava 2s  . c om
    Row firstRow = reportSheet.getRow(0);
    if (firstRow == null) {
        firstRow = reportSheet.createRow(0);
        int cellNum = 0;
        for (Map<String, String> col : row) {
            for (String key : col.keySet()) {
                Cell cell = firstRow.createCell(cellNum++);
                setCellValue(cell, key);
            }
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        Cell cell = firstRow.getCell(i);
        if (cell != null) {
            colLabels.add(cell.getStringCellValue());
        }
    }
    Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1);
    int lastIndex = -1;
    for (Map<String, String> col : row) {
        for (String key : col.keySet()) {
            int colNum = -1;
            Cell cell = null;
            if (colLabels.contains(key)) {
                colNum = colLabels.indexOf(key);
                lastIndex = colNum;
            }
            if (colNum == -1) {
                lastIndex++;
                colNum = lastIndex;
                shiftColumns(reportSheet, colNum, key);
                colLabels.add(colNum, key);
            }
            cell = newRow.getCell(colNum);
            if (cell == null) {
                cell = newRow.createCell(colNum);
            }
            setCellValue(cell, col.get(key));
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        reportSheet.autoSizeColumn(i);
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    workbook.write(baos);
    return baos.toByteArray();
}

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

private void shiftColumns(Sheet reportSheet, int colNum, String key) {
    boolean firstRow = true;
    for (Row row : reportSheet) {
        for (int i = row.getLastCellNum() + 1; i >= colNum; i--) {
            Cell nextCell = row.getCell(i + 1);
            Cell oldCell = row.getCell(i);
            if (oldCell != null) {
                if (nextCell == null) {
                    nextCell = row.createCell(i + 1);
                }/*  w w w  .j  a  v  a  2 s .c o m*/
                setCellValue(nextCell, oldCell.getStringCellValue());
                if (firstRow && i == colNum) {
                    setCellValue(oldCell, key);
                    firstRow = false;
                } else {
                    setCellValue(oldCell, "");
                }
            } else if (firstRow && i == colNum) {
                oldCell = row.createCell(i);
                setCellValue(oldCell, key);
                firstRow = false;
            }
        }
    }
}

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);/*  ww  w .ja  v  a 2s. c o 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.khodev.sc2.quiz.Quiz.java

License:Open Source License

public Quiz(String xls) throws IOException {
    FileInputStream file = new FileInputStream(xls);
    Workbook workbook = new XSSFWorkbook(file);
    Sheet sheet = workbook.getSheetAt(0);
    boolean headerSkipped = false;
    for (Row row : sheet) {
        if (!headerSkipped) {
            headerSkipped = true;/* ww w .j a va 2s  . co m*/
            continue;
        }
        questions.add(new Question(row.getCell(2).toString(), row.getCell(3).toString()));
    }

}

From source file:com.kongwu.insweb.utils.ReadExcel.java

License:Apache License

/**
 * @param filepath excel//from  www  .  j a v a 2  s .  c o m
 *        
 *        
 *        
 * @return ??,:list<usrid,query,biz,semantic>
 *                        :
 *                           :
 * @throws IOException
 */
public static List<List<String>> readTestset(String filepath) throws IOException {
    FileInputStream fileIn = null;
    List<List<String>> testsetList = new ArrayList<List<String>>();
    /**?excel**/
    try {
        fileIn = new FileInputStream(filepath);
        /**excel 2007**/
        if (filepath.endsWith(".xlsx")) {
            Workbook wb = new XSSFWorkbook(fileIn);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                /**
                 * ?cell
                 */
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(i);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }

                testsetList.add(list);
            }
        } else {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;
            HSSFCell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)// ???
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }
                testsetList.add(list);
            }
        }
    } finally {
        if (fileIn != null)
            fileIn.close();
    }
    return testsetList;
}

From source file:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }/*from  w  w  w .j  a v a2  s. c om*/

    JSONArray jArr = new JSONArray();
    for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
        Row row = sheet.getRow(i);
        JSONObject obj = new JSONObject();
        JSONObject jtemp1 = new JSONObject();
        if (row == null) {
            jArr.put(obj);
            continue;
        }
        if (maxCol < row.getLastCellNum())
            maxCol = row.getLastCellNum();
        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            if (cell == null)
                continue;
            String colHeader = new CellReference(i, j).getCellRefParts()[2];
            String val = null;
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                val = Double.toString(cell.getNumericCellValue());
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                    String df_full = "yyyy-MM-dd";
                    DateFormat sdf = new SimpleDateFormat(df_full);
                    val = sdf.format(df);
                }
                break;
            case Cell.CELL_TYPE_STRING:
                val = cell.getRichStringCellValue().getString();
                break;
            }
            if (i == 0) { // List of Headers (Consider first row as Headers)
                jtemp1 = new JSONObject();
                jtemp1.put("header", val);
                jtemp1.put("index", j);
                jobj.append("Header", jtemp1);
            }
            obj.put(colHeader, val);
        }
        jArr.put(obj);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.esp.servlets.XLSDataExtractor.java

License:Open Source License

public JSONObject parseXLS1(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.getSheetAt(sheetNo);
    ArrayList<String> arr = new ArrayList<String>();
    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;

    JSONArray jArr = new JSONArray();
    try {/* ww w  .  j  a v a2 s.c o m*/
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                jArr.put(obj);
                continue;
            }
            if (maxCol < row.getLastCellNum())
                maxCol = row.getLastCellNum();
            for (int j = 0; j < row.getLastCellNum(); j++) {
                Cell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    arr.add(val);
                    continue;
                }
                ;
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    val = Double.toString(cell.getNumericCellValue());
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        java.util.Date df = HSSFDateUtil.getJavaDate(Double.parseDouble(val));
                        String df_full = "yyyy-MM-dd";
                        DateFormat sdf = new SimpleDateFormat(df_full);
                        val = sdf.format(df);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    val = cell.getRichStringCellValue().getString();
                    break;
                }
                if (i == 0) { // List of Headers (Consider first row as Headers)
                    jtemp1 = new JSONObject();
                    jtemp1.put("header", val);
                    jtemp1.put("index", j);
                    jobj.append("Header", jtemp1);
                    obj.put(colHeader, val);
                    arr.add(val);
                } else {
                    if (arr.get(j) != null)
                        obj.put(arr.get(j), val);
                }

            }
            jArr.put(obj);
        }
    } catch (Exception ex) {
        Logger.getLogger(XLSDataExtractor.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "Image has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.ksa.myanmarlottery.service.parser.ExcelFileParser.java

@Override
public List<Result> getResult(InputStream in) throws FileNotFoundException, IOException, ParseException {
    List<Prize> prizes = null;
    List<Result> resultList = new ArrayList<>();
    SimpleDateFormat format = new SimpleDateFormat("dd-MM-yyyy"); // 01-May-2017
    try {/*ww w  .ja va 2 s .com*/
        Workbook workbook = new XSSFWorkbook(in);
        Sheet datatypeSheet = workbook.getSheetAt(0);
        Iterator<Row> iterator = datatypeSheet.iterator();

        while (iterator.hasNext()) {

            Row currentRow = iterator.next();
            Cell cell0 = currentRow.getCell(0); // get first cell.

            if (cell0.getCellTypeEnum() == CellType.NUMERIC) {
                int numberic = (int) cell0.getNumericCellValue();
                log.info("Numberic - " + numberic);

                // check lottery type
                if (ConstantUtil.OLD_LOTTERY_TYPE == numberic || ConstantUtil.NEW_LOTTERY_TYPE == numberic) { // for lottery type result
                    Result result = new Result();
                    result.setType(numberic);
                    result.setNumberOfTimes((int) currentRow.getCell(1).getNumericCellValue());
                    //                        result.setResultFor(format.parse(currentRow.getCell(2).toString()));
                    result.setResultFor(currentRow.getCell(2).getDateCellValue());
                    result.setDataProvider(currentRow.getCell(3).getStringCellValue());
                    result.setCompanyName(currentRow.getCell(4).getStringCellValue());

                    prizes = new ArrayList<>();
                    result.setPrizes(prizes);
                    resultList.add(result);
                }

            } else if (cell0.getCellTypeEnum() == CellType.STRING) { // result data
                String character = cell0.getStringCellValue();
                log.info("character - " + character);

                // check validation for character.
                String value = charMap.get(character);
                if (value == null) {
                    throw new ParseException(
                            "Character is Not valid at Row: " + currentRow.getRowNum() + " > column:" + 0, 400);
                }
                Cell cell1 = currentRow.getCell(1);
                if (cell1.getCellTypeEnum() != CellType.NUMERIC) {
                    throw new ParseException(
                            "Should be Number at Row: " + currentRow.getRowNum() + " > column:" + 1, 400);
                }
                log.info("Cell Type " + cell1.getCellTypeEnum());
                int code = (int) cell1.getNumericCellValue();
                log.info("code - " + code + " Row:" + currentRow.getRowNum() + " > column:" + 1);
                String prizeTitle = currentRow.getCell(2).getStringCellValue();
                log.info("prizeTitle - " + prizeTitle);
                String prizeDesc = currentRow.getCell(4).getStringCellValue();
                log.info("prizeDesc - " + prizeDesc);
                prizes.add(new Prize(character, code, prizeTitle, prizeDesc));
            }
        }
        log.info("resultList size: " + resultList.size());
        for (Result r : resultList) {
            log.info("prizeList size: " + r.getPrizes().size());
        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
        throw e;
    } catch (IOException e) {
        e.printStackTrace();
        throw e;
    }
    return resultList;
}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

/**
 * Retrieve a cell at coordinates [rowIndex,colIndex].
 *
 * @param rowIndex row-number of the cell
 * @param colIndex column-number of the cell
 * @return the cell, if exists in the sheet, null otherwise
 *///w  w  w .  j a v  a2  s . co m
public Cell getCellAt(int rowIndex, int colIndex) {
    Row r = getSheet().getRow(rowIndex);
    Cell cell = null;
    if (r != null) {
        cell = r.getCell((short) colIndex);
    }
    return cell;
}

From source file:com.kybelksties.excel.ExcelSheetTableModel.java

License:Open Source License

@Override
public void setValueAt(Object obj, int rowIndex, int columnIndex) {
    Row row = getSheet().getRow(rowIndex);
    if (row == null) {
        row = getSheet().createRow(rowIndex);
    }/*ww  w .  j  a va  2  s .co  m*/
    Cell cell = row.getCell(columnIndex);
    if (cell == null) {
        cell = row.createCell(columnIndex);
    }

    if (obj != null) {
        Class<?> clazz = obj.getClass();
        if (String.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((String) obj));
        } else if (Number.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((Number) obj).doubleValue());
        } else if (Boolean.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((Boolean) obj));
        } else if (Calendar.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((Calendar) obj));
        } else if (Date.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((Date) obj));
        } else if (RichTextString.class.isAssignableFrom(clazz)) {
            cell.setCellValue(((RichTextString) obj));
        } else {
            cell.setCellValue(obj.toString());
        }
    }

    if (columnIndex >= getColumnCount() || rowIndex >= getRowCount()) {
        fireTableStructureChanged();
    } else {
        fireTableChanged(new TableModelEvent(this, rowIndex, rowIndex, columnIndex));
    }
}