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

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

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

Introduction

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

Prototype

int getRowNum();

Source Link

Document

Get row number this row represents

Usage

From source file:com.accounting.inventory.InventorySalesMBean.java

public void postProcessXLSSalesReturn(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);/*w  ww  .  jav  a 2s .com*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("SALES RETURN REPORT");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    if (stage == 1) {
        HSSFRow fourthRow = sheet.createRow(2);
        fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
        fourthRow.getCell(0).setCellStyle(headerCellStyle);
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 4));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 4));

    HSSFRow lastRow;
    double totalSold = 0;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {

            String cost = row.getCell(4).getStringCellValue();
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_BLANK);
            row.getCell(4).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            row.getCell(4).setCellValue(Double.valueOf(cost));
            totalSold += Double.valueOf(cost);

        }
    }
    lastRow = sheet.createRow(sheet.getLastRowNum() + 1);
    HSSFCell totalSumTextCell = lastRow.createCell(3);
    totalSumTextCell.setCellValue("Total : ");

    HSSFCell totalUnitsCell = lastRow.createCell(4);
    totalUnitsCell.setCellValue(totalSold);
}

From source file:com.accounting.office.PayRollManagedBean.java

public void simplePostProcessXLSEmployeeReport(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate5");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);/*  w w w . j  a  v  a 2 s. c  o m*/
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 3);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("Salary Posting Report");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(2);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 5));
    for (Row row : sheet) {
        if (row.getRowNum() > 3) {
            for (int i = 2; i < 6; i++) {
                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.accounting.reportMBean.ReportCommonBean.java

public void simplePostProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);

    HSSFSheet sheet = wb.getSheetAt(0);//  ww  w  . j  a  va2 s  .  co  m
    int noOfColumns = sheet.getRow(0).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue(pageName);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String date = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");
    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + date + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("FROM: " + startDateString + " TO: " + endDateString);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 7));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 7));
    HSSFRow lastRow;
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 3; i < 8; i++) {

                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.accounting.reportMBean.StockLedgerMBeans.java

public void postProcessXLS(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateReading = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    HSSFWorkbook wb = (HSSFWorkbook) document;
    HSSFCellStyle headerCellStyle = wb.createCellStyle();
    HSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    HSSFCellStyle headerCellStyle2 = wb.createCellStyle();

    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle.setFont(headerFont);
    headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER);

    Font headerFont1 = wb.createFont();
    headerFont1.setBoldweight(Font.U_SINGLE_ACCOUNTING);
    headerFont1.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle1.setFont(headerFont);
    headerCellStyle1.setAlignment(CellStyle.ALIGN_RIGHT);
    HSSFSheet sheet = wb.getSheetAt(0);//from   ww w.  j  av  a 2  s. c  o  m
    int noOfColumns = sheet.getRow(0).getLastCellNum();

    Font headerFont3 = wb.createFont();
    headerFont3.setBoldweight(Font.U_SINGLE);
    headerFont3.setFontName(HSSFFont.FONT_ARIAL);
    headerCellStyle2.setFont(headerFont1);
    headerCellStyle2.setAlignment(CellStyle.ALIGN_RIGHT);
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue(reportHeader);
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow secondRow = sheet.createRow(1);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);

    HSSFRow thirdRow = sheet.createRow(3);
    String generatedDate = ndc.convertToNepaliDate(new Date());
    SimpleDateFormat sdf = new SimpleDateFormat("hh:mm:ss a");

    String time = sdf.format(new Date());
    thirdRow.createCell(0)
            .setCellValue("Generated on:" + generatedDate + " " + time + " by:" + getLoggedInUser().getName());
    thirdRow.getCell(0).setCellStyle(headerCellStyle2);

    HSSFRow fourthRow = sheet.createRow(2);
    fourthRow.createCell(0).setCellValue("From: " + startDateString + " To: " + endDateReading);
    fourthRow.getCell(0).setCellStyle(headerCellStyle);
    System.out.println("The cell count " + cellCount);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, cellCount));
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, cellCount));
    for (Row row : sheet) {
        if (row.getRowNum() > 4) {
            for (int i = 3; i < count; i++) {
                String cost = row.getCell(i).getStringCellValue();
                if (cost != null && !cost.isEmpty()) {
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_BLANK);
                    row.getCell(i).setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                    row.getCell(i).setCellValue(Double.parseDouble(cost.replace(",", "")));
                }
            }
        }
    }
}

From source file:com.actelion.research.spiritapp.ui.util.PDFUtils.java

License:Open Source License

public static void convertHSSF2Pdf(Workbook wb, String header, File reportFile) throws Exception {
    assert wb != null;
    assert reportFile != null;

    //Precompute formula
    FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);//  w  w  w  .j  a  v a 2  s  .com

        for (Row r : sheet) {
            for (Cell c : r) {
                if (c.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
                    try {
                        evaluator.evaluateFormulaCell(c);
                    } catch (Exception e) {
                        System.err.println(e);
                    }
                }
            }
        }
    }

    File tmp = File.createTempFile("tmp_", ".xlsx");
    try (OutputStream out = new BufferedOutputStream(new FileOutputStream(tmp))) {
        wb.write(out);
    }

    //Find page orientation
    int maxColumnsGlobal = 0;
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            maxColumnsGlobal = Math.max(maxColumnsGlobal, row.getLastCellNum());
        }
    }

    Rectangle pageSize = maxColumnsGlobal < 10 ? PageSize.A4 : PageSize.A4.rotate();
    Document pdfDocument = new Document(pageSize, 10f, 10f, 20f, 20f);

    PdfWriter writer = PdfWriter.getInstance(pdfDocument, new FileOutputStream(reportFile));
    addHeader(writer, header);
    pdfDocument.open();
    //we have two columns in the Excel sheet, so we create a PDF table with two columns
    //Note: There are ways to make this dynamic in nature, if you want to.
    //Loop through sheets
    for (int sheetNo = 0; sheetNo < wb.getNumberOfSheets(); sheetNo++) {
        Sheet sheet = wb.getSheetAt(sheetNo);

        //Loop through rows, to find number of columns
        int minColumns = 1000;
        int maxColumns = 0;
        for (Iterator<Row> rowIterator = sheet.iterator(); rowIterator.hasNext();) {
            Row row = rowIterator.next();
            if (row.getFirstCellNum() >= 0)
                minColumns = Math.min(minColumns, row.getFirstCellNum());
            if (row.getLastCellNum() >= 0)
                maxColumns = Math.max(maxColumns, row.getLastCellNum());
        }
        if (maxColumns == 0)
            continue;

        //Loop through first rows, to find relative width
        float[] widths = new float[maxColumns];
        int totalWidth = 0;
        for (int c = 0; c < maxColumns; c++) {
            int w = sheet.getColumnWidth(c);
            widths[c] = w;
            totalWidth += w;
        }

        for (int c = 0; c < maxColumns; c++) {
            widths[c] /= totalWidth;
        }

        //Create new page and a new chapter with the sheet's name
        if (sheetNo > 0)
            pdfDocument.newPage();
        Chapter pdfSheet = new Chapter(sheet.getSheetName(), sheetNo + 1);

        PdfPTable pdfTable = null;
        PdfPCell pdfCell = null;
        boolean inTable = false;

        //Loop through cells, to create the content
        //         boolean leftBorder = true;
        //         boolean[] topBorder = new boolean[maxColumns+1];
        for (int r = 0; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);

            //Check if we exited a table (empty line)
            if (row == null) {
                if (pdfTable != null) {
                    addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
                    pdfTable = null;
                }
                inTable = false;
                continue;
            }

            //Check if we start a table (>MIN_COL_IN_TABLE columns)
            if (row.getLastCellNum() >= MIN_COL_IN_TABLE) {
                inTable = true;
            }

            if (!inTable) {
                //Process the data outside table, just add the text
                boolean hasData = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    if (cell.getCellType() == Cell.CELL_TYPE_BLANK)
                        continue;
                    Chunk chunk = getChunk(wb, cell);
                    pdfSheet.add(chunk);
                    pdfSheet.add(new Chunk(" "));
                    hasData = true;
                }
                if (hasData)
                    pdfSheet.add(Chunk.NEWLINE);

            } else {
                //Process the data in table
                if (pdfTable == null) {
                    //Create table
                    pdfTable = new PdfPTable(maxColumns);
                    pdfTable.setWidths(widths);
                    //                  topBorder = new boolean[maxColumns+1];
                }

                int cellNumber = minColumns;
                //               leftBorder = false;
                Iterator<Cell> cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    for (; cellNumber < cell.getColumnIndex(); cellNumber++) {
                        pdfCell = new PdfPCell();
                        pdfCell.setBorder(0);
                        pdfTable.addCell(pdfCell);
                    }

                    Chunk phrase = getChunk(wb, cell);
                    pdfCell = new PdfPCell(new Phrase(phrase));
                    pdfCell.setFixedHeight(row.getHeightInPoints() - 3);
                    pdfCell.setNoWrap(!cell.getCellStyle().getWrapText());
                    pdfCell.setPaddingLeft(1);
                    pdfCell.setHorizontalAlignment(
                            cell.getCellStyle().getAlignment() == CellStyle.ALIGN_CENTER ? PdfPCell.ALIGN_CENTER
                                    : cell.getCellStyle().getAlignment() == CellStyle.ALIGN_RIGHT
                                            ? PdfPCell.ALIGN_RIGHT
                                            : PdfPCell.ALIGN_LEFT);
                    pdfCell.setUseBorderPadding(false);
                    pdfCell.setUseVariableBorders(false);
                    pdfCell.setBorderWidthRight(cell.getCellStyle().getBorderRight() == 0 ? 0 : .5f);
                    pdfCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom() == 0 ? 0
                            : cell.getCellStyle().getBorderBottom() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft() == 0 ? 0
                            : cell.getCellStyle().getBorderLeft() > 1 ? 1 : .5f);
                    pdfCell.setBorderWidthTop(cell.getCellStyle().getBorderTop() == 0 ? 0
                            : cell.getCellStyle().getBorderTop() > 1 ? 1 : .5f);
                    String color = cell.getCellStyle().getFillForegroundColorColor() == null ? null
                            : ((XSSFColor) cell.getCellStyle().getFillForegroundColorColor()).getARGBHex();
                    if (color != null)
                        pdfCell.setBackgroundColor(new Color(Integer.decode("0x" + color.substring(2))));
                    pdfTable.addCell(pdfCell);
                    cellNumber++;
                }
                for (; cellNumber < maxColumns; cellNumber++) {
                    pdfCell = new PdfPCell();
                    pdfCell.setBorder(0);
                    pdfTable.addCell(pdfCell);
                }
            }

            //Custom code to add all images on the first sheet (works for reporting)
            if (sheetNo == 0 && row.getRowNum() == 0) {
                for (PictureData pd : wb.getAllPictures()) {
                    try {
                        Image pdfImg = Image.getInstance(pd.getData());
                        pdfImg.scaleToFit(
                                pageSize.getWidth() * .8f - pageSize.getBorderWidthLeft()
                                        - pageSize.getBorderWidthRight(),
                                pageSize.getHeight() * .8f - pageSize.getBorderWidthTop()
                                        - pageSize.getBorderWidthBottom());
                        pdfSheet.add(pdfImg);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        if (pdfTable != null) {
            addTable(pdfDocument, pdfSheet, totalWidth, widths, pdfTable);
        }

        pdfDocument.add(pdfSheet);
    }
    pdfDocument.close();

}

From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java

License:Open Source License

@SuppressWarnings("rawtypes")
public static void exportToExcel(String[][] table, ExportMode exportMode) throws IOException {
    Class[] types = getTypes(table);
    Workbook wb = new XSSFWorkbook();
    Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
    CellStyle style;// w w  w.  ja v  a  2  s .co  m
    DataFormat df = wb.createDataFormat();

    Font font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 15);
    style = wb.createCellStyle();
    style.setFont(font);
    styles.put("title", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 10);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_CENTER);
    style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    style.setFillPattern(CellStyle.SOLID_FOREGROUND);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("th", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderTop(CellStyle.BORDER_THIN);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_LEFT);
    style.setFont(font);
    style.setWrapText(true);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-border", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-double", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-right", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-bold", style);

    font = wb.createFont();
    font.setFontName("Serif");
    font.setFontHeightInPoints((short) 9);
    style = wb.createCellStyle();
    style.setBorderRight(CellStyle.BORDER_THIN);
    style.setRightBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(CellStyle.BORDER_THIN);
    style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(CellStyle.ALIGN_RIGHT);
    style.setFont(font);
    style.setDataFormat(df.getFormat("d.mm.yyyy h:MM"));
    style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
    styles.put("td-date", style);

    Sheet sheet = wb.createSheet();
    sheet.setFitToPage(true);

    Cell cell;

    int maxRows = 0;
    for (int r = 0; r < table.length; r++) {
        Row row = sheet.createRow(r);
        if (r == 0) {
            row.setRowStyle(styles.get("th"));
        }

        int rows = 1;
        for (int c = 0; c < table[r].length; c++) {
            cell = row.createCell(c);
            String s = table[r][c];
            if (s == null)
                continue;
            rows = Math.max(rows, s.split("\n").length);
            try {
                if (exportMode == ExportMode.HEADERS_TOP && r == 0) {
                    cell.setCellStyle(styles.get("th"));
                    cell.setCellValue(s);

                } else if (exportMode == ExportMode.HEADERS_TOPLEFT && (r == 0 || c == 0)) {
                    if (r == 0 && c == 0) {
                        cell.setCellStyle(styles.get("td"));
                    } else {
                        cell.setCellStyle(styles.get("th"));
                    }
                    cell.setCellValue(s);
                } else if (types[c] == Double.class) {
                    cell.setCellStyle(styles.get("td-double"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(Double.parseDouble(s));
                } else if (types[c] == String.class) {
                    cell.setCellStyle(
                            styles.get(exportMode == ExportMode.HEADERS_TOPLEFT ? "td-border" : "td"));
                    cell.setCellType(Cell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(s);
                } else {
                    cell.setCellStyle(styles.get("td-right"));
                    cell.setCellValue(s);
                }
            } catch (Exception e) {
                cell.setCellStyle(styles.get("td"));
                cell.setCellValue(s);
            }
        }
        maxRows = Math.max(maxRows, rows);
        row.setHeightInPoints(rows * 16f);

    }

    // Add footer notes
    if (footerData.size() > 0) {
        Row row = sheet.createRow(table.length);
        row.setHeightInPoints((footerData.size() * sheet.getDefaultRowHeightInPoints()));
        cell = row.createCell(0);
        sheet.addMergedRegion(new CellRangeAddress(row.getRowNum(), //first row (0-based)
                row.getRowNum(), //last row  (0-based)
                0, //first column (0-based)
                table[0].length - 1 //last column  (0-based)
        ));
        //for ( String data : footerData ) {
        style = wb.createCellStyle();
        style.setWrapText(true);
        cell.setCellStyle(style);
        cell.setCellValue(MiscUtils.flatten(footerData, "\n"));
        //}
    }
    footerData.clear();

    autoSizeColumns(sheet);
    if (table.length > 0) {
        for (int c = 0; c < table[0].length; c++) {
            if (sheet.getColumnWidth(c) > 10000)
                sheet.setColumnWidth(c, 3000);
        }
    }

    if (exportMode == ExportMode.HEADERS_TOPLEFT) {
        for (int r = 1; r < table.length; r++) {
            sheet.getRow(r).setHeightInPoints(maxRows * 16f);
        }
    }

    File reportFile = IOUtils.createTempFile("export_", ".xlsx");
    FileOutputStream out = new FileOutputStream(reportFile);
    wb.write(out);
    wb.close();
    out.close();
    Desktop.getDesktop().open(reportFile);
}

From source file:com.adobe.acs.commons.data.Spreadsheet.java

License:Apache License

@SuppressWarnings("squid:S3776")
private Optional<Map<String, CompositeVariant>> buildRow(Row row) {
    Map<String, CompositeVariant> out = new LinkedHashMap<>();
    out.put(ROW_NUMBER, new CompositeVariant(row.getRowNum()));
    List<Variant> data = readRow(row);
    boolean empty = true;
    for (int i = 0; i < data.size() && i < getHeaderRow().size(); i++) {
        String colName = getHeaderRow().get(i);
        if (colName != null && data.get(i) != null && !data.get(i).isEmpty()) {
            empty = false;//from  ww  w . j a v a  2s  .  c  o m
            if (!out.containsKey(colName)) {
                out.put(colName, new CompositeVariant(headerTypes.get(colName)));
            }
            if (headerTypes.get(colName).isArray()) {
                String[] values = data.get(i).toString()
                        .split(Pattern.quote(delimiters.getOrDefault(colName, DEFAULT_DELIMITER)));
                for (String value : values) {
                    if (value != null && !value.isEmpty()) {
                        out.get(colName).addValue(value.trim());
                    }
                }
            } else {
                out.get(colName).addValue(data.get(i));
            }
        }
    }
    if (empty || (!requiredColumns.isEmpty() && !out.keySet().containsAll(requiredColumns))) {
        return Optional.empty();
    } else {
        return Optional.of(out);
    }
}

From source file:com.aistor.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?/*from  ww w . j av a  2s .  c  om*/
 * @param row 
 * @param column ?
 * @param val 
 * @param align ??1?23??
 * @return ?
 */
public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
    Cell cell = row.createCell(column);
    CellStyle style = wb.createCellStyle();
    style.cloneStyleFrom(styles.get("data"));
    if (align == 1) {
        style.setAlignment(CellStyle.ALIGN_LEFT);
    } else if (align == 2) {
        style.setAlignment(CellStyle.ALIGN_CENTER);
    } else if (align == 3) {
        style.setAlignment(CellStyle.ALIGN_RIGHT);
    }
    try {
        if (val == null) {
            cell.setCellValue("");
        } else if (val instanceof String) {
            cell.setCellValue((String) val);
        } else if (val instanceof Integer) {
            cell.setCellValue((Integer) val);
        } else if (val instanceof Long) {
            cell.setCellValue((Long) val);
        } else if (val instanceof Double) {
            cell.setCellValue((Double) val);
        } else if (val instanceof Float) {
            cell.setCellValue((Float) val);
        } else if (val instanceof Date) {
            DataFormat format = wb.createDataFormat();
            style.setDataFormat(format.getFormat("yyyy-MM-dd"));
            cell.setCellValue((Date) val);
        } else {
            if (fieldType != Class.class) {
                cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val));
            } else {
                cell.setCellValue((String) Class
                        .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                "fieldtype." + val.getClass().getSimpleName() + "Type"))
                        .getMethod("setValue", Object.class).invoke(null, val));
            }
        }
    } catch (Exception ex) {
        log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString());
        cell.setCellValue(val.toString());
    }
    cell.setCellStyle(style);
    return cell;
}

From source file:com.aistor.common.utils.excel.ExportExcel.java

License:Open Source License

/**
 * ?annotation.ExportField?/* w  ww .  j  a v a2  s  . c  om*/
 * @return list ?
 */
public <E> ExportExcel setDataList(List<E> list) {
    for (E e : list) {
        int colunm = 0;
        Row row = this.addRow();
        StringBuilder sb = new StringBuilder();
        for (Object[] os : annotationList) {
            ExcelField ef = (ExcelField) os[0];
            Object val = null;
            // Get entity value
            try {
                if (StringUtils.isNotBlank(ef.value())) {
                    val = Reflections.invokeGetter(e, ef.value());
                } else {
                    if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field) os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {},
                                new Object[] {});
                    }
                }
                // If is dict, get dict label
                if (StringUtils.isNotBlank(ef.dictType())) {
                    val = DictUtils.getDictLabel(val == null ? "" : val.toString(), ef.dictType(), "");
                }
            } catch (Exception ex) {
                // Failure to ignore
                log.info(ex.toString());
                val = "";
            }
            this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
            sb.append(val + ", ");
        }
        log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString());
    }
    return this;
}

From source file:com.anritsu.mcrepositorymanager.packageinfoparser.RSSParser.java

public void parseRSS() {
    try {/* w  w w .  jav  a  2  s .com*/
        FileInputStream file = new FileInputStream(new File(this.filePath));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get package list sheet from the workbook
        String sheetName = "MC " + mcVersion;
        XSSFSheet sheet = workbook.getSheet(sheetName);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            if (row.getRowNum() < 3) {
                continue;
            }
            McPackage p = new McPackage();
            p.setMcVersion(mcVersion);
            p.setName(row.getCell(2).getStringCellValue());
            HashSet<String> downloadLinks = new HashSet<>();
            try {
                String link = row.getCell(7).getHyperlink().getAddress();
                downloadLinks.add(link);
                int urlIndex = link.split("/").length;
                String fileName = link.split("/")[urlIndex - 1];
                p.setFileName(fileName);
            } catch (NullPointerException exp) {
                exp.printStackTrace();
            } finally {
                p.setDownloadLinks(downloadLinks);
            }

            p.setPackageVersion(row.getCell(3).getStringCellValue());
            p.setAvailability(row.getCell(4).getStringCellValue());
            availability.add(row.getCell(4).getStringCellValue());

            // Set customers list
            ArrayList<String> cusList = new ArrayList<>();
            String[] customerCell = row.getCell(5).getStringCellValue().split(",");
            for (int i = 0; i < customerCell.length; i++) {
                customers.add(customerCell[i]);
                cusList = new ArrayList<>(Arrays.asList(customerCell));
            }
            //System.out.println("Parsing line " + row.getRowNum());
            LOGGER.log(Level.INFO, "Parsing " + this.filePath + "/" + row.getRowNum());

            p.setCustomerList(new HashSet<>(cusList));
            packageList.add(p);
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}