Example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress

Introduction

In this page you can find the example usage for org.apache.poi.ss.util CellRangeAddress CellRangeAddress.

Prototype

public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol) 

Source Link

Document

Creates new cell range.

Usage

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

public void simplePostProcessXLSForPurchase(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);/*from  ww w .  j  ava2s . 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(HelperUtil.getPageName().getPageName());
    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));
}

---

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

public void simplePostProcessXLSForPurchaseforSearch(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate7");
    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  ww. ja v a 2s  .c o  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(HelperUtil.getPageName().getPageName());
    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));
}

---

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  av a 2s .c om
    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.ReportCommonBean.java

public void simplePostProcessXLSXOfStock(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle 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);

    XSSFSheet sheet = wb.getSheetAt(0);/*from  www  .  j av  a2 s  .  co m*/
    int noOfColumns = sheet.getRow(2).getLastCellNum();
    for (int i = 0; i < noOfColumns; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.shiftRows(0, sheet.getLastRowNum(), 4);

    XSSFRow firstRow = sheet.createRow(1);
    firstRow.createCell(0).setCellValue("STOCK VALUE WITH VALUES");
    firstRow.getCell(0).setCellStyle(headerCellStyle);

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow 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);

    XSSFRow 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));
}

---

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

public void simplePostProcessXLSX(Object document) {
    String startDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate4");
    String endDateString = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap()
            .get("nepaliDate1");
    XSSFWorkbook wb = (XSSFWorkbook) document;
    XSSFCellStyle headerCellStyle = wb.createCellStyle();
    XSSFCellStyle headerCellStyle1 = wb.createCellStyle();
    XSSFCellStyle 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);

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

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

    XSSFRow secondRow = sheet.createRow(0);
    secondRow.createCell(0).setCellValue(getLoggedInOffice().getName());
    secondRow.getCell(0).setCellStyle(headerCellStyle);
    //
    XSSFRow 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);

    XSSFRow 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));
}

---

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 w w  w.  j  av a2  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.report.AbstractReport.java

License:Open Source License

protected Cell set(Sheet sheet, int row, int col, Object text, Style style, int rowspan, int colspan) {
    Row r = sheet.getRow(row);/*from www.  j  av a2 s . com*/
    if (r == null)
        r = sheet.createRow(row);
    Cell c = r.getCell(col);
    if (c == null)
        c = r.createCell(col);
    c.setCellStyle(styles.get(style));
    if (text == null) {
        if (c.getCellStyle().getDataFormatString().startsWith("0")) {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue("");
        } else {
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue("");
        }
    } else if (text instanceof String) {
        try {
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(Integer.parseInt((String) text));
        } catch (Exception e) {
            try {
                c.setCellType(Cell.CELL_TYPE_NUMERIC);
                c.setCellValue(Double.parseDouble((String) text));
            } catch (Exception e2) {
                c.setCellType(Cell.CELL_TYPE_STRING);
                c.setCellValue((String) text);
            }
        }
    } else if (text instanceof Double) {
        c.setCellValue((Double) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Integer) {
        c.setCellValue((Integer) text);
        c.setCellType(Cell.CELL_TYPE_NUMERIC);
    } else if (text instanceof Date) {
        c.setCellValue((Date) text);
    }
    if (rowspan > 1 || colspan > 1) {
        sheet.addMergedRegion(new CellRangeAddress(row, row + rowspan - 1, col, col + colspan - 1));
        for (int i = 0; i < rowspan; i++) {
            for (int j = 0; j < colspan; j++) {
                if (i > 0 || j > 0)
                    set(sheet, row + i, col + j, "", style);
            }
        }
    }
    return c;
}

---

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;/* www.  j a va 2s.  c om*/
    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.mcp.impl.ProcessErrorReportExcelServlet.java

License:Apache License

@SuppressWarnings("squid:S3776")
protected Workbook createSpreadsheet(ManagedProcess report) {
    Workbook wb = new XSSFWorkbook();

    String name = report.getName();
    for (char ch : new char[] { '\\', '/', '*', '[', ']', ':', '?' }) {
        name = StringUtils.remove(name, ch);
    }// w  w w  . j  av a 2 s . c  o m
    Sheet sheet = wb.createSheet(name);
    sheet.createFreezePane(0, 1, 0, 1);

    Row headerRow = sheet.createRow(0);
    CellStyle headerStyle = createHeaderStyle(wb);
    CellStyle dateStyle = wb.createCellStyle();
    CreationHelper createHelper = wb.getCreationHelper();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyy/mm/dd h:mm:ss"));

    for (String columnName : Arrays.asList("Time", "Path", "Error", "Stack trace")) {
        Cell headerCell = headerRow.createCell(headerRow.getPhysicalNumberOfCells());
        headerCell.setCellValue(columnName);
        headerCell.setCellStyle(headerStyle);
    }

    Collection<ArchivedProcessFailure> rows = report.getReportedErrorsList();
    //make rows, don't forget the header row
    for (ArchivedProcessFailure error : rows) {
        Row row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        Cell c;

        c = row.createCell(0);
        c.setCellValue(error.time);
        c.setCellStyle(dateStyle);
        c = row.createCell(1);
        c.setCellValue(error.nodePath);
        c = row.createCell(2);
        c.setCellValue(error.error);
        c = row.createCell(3);
        c.setCellValue(error.stackTrace);
    }
    autosize(sheet, 4);
    sheet.setAutoFilter(new CellRangeAddress(0, 1 + rows.size(), 0, 3));
    return wb;
}

---

From source file:com.ah.ui.actions.admin.LicenseMgrAction.java

License:Open Source License

private void exportEntitleKeyInfo() {
    try {// w w w .java2  s  .  c  o m
        // create a new file
        FileOutputStream out = new FileOutputStream(ORDERKEYINFO_EXPORT_FILE_PATH);
        // create a new workbook
        HSSFWorkbook wb = new HSSFWorkbook();
        // create a new sheet
        HSSFSheet s = wb.createSheet("Sheet1");
        // declare a row object reference
        HSSFRow r = null;
        // declare a cell object reference
        HSSFCell c = null;
        // row index
        int rowNum = 0;
        // cell count
        int cellcount = 0;
        if (NmsUtil.isHMForOEM()) {
            cellcount = 7;
        } else if (getIsInHomeDomain()) {
            cellcount = 8;
        } else if (NmsUtil.isHostedHMApplication()) {
            cellcount = 9;
        }
        if (cellcount == 0) {
            return;
        } else {
            for (int i = 0; i < cellcount; i++) {
                s.setColumnWidth(i, getColumnWidthByIndex(i) * 256);
            }
        }
        // create cell style
        HSSFCellStyle cs = wb.createCellStyle();
        // create font object
        HSSFFont f = wb.createFont();

        //set font to 12 point type
        f.setFontHeightInPoints((short) 12);
        f.setFontName("Calibri");

        //set cell stlye
        cs.setFont(f);
        cs.setAlignment(CellStyle.ALIGN_CENTER);
        cs.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs1 = wb.createCellStyle();
        // create font object
        HSSFFont f1 = wb.createFont();

        //set font to 12 point type
        f1.setFontHeightInPoints((short) 12);
        f1.setFontName("Calibri");
        f1.setBoldweight(Font.BOLDWEIGHT_BOLD);

        //set cell stlye
        cs1.setFont(f1);
        cs1.setAlignment(CellStyle.ALIGN_RIGHT);
        cs1.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setFont(f1);
        cs2.setAlignment(CellStyle.ALIGN_CENTER);
        cs2.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs3 = wb.createCellStyle();
        cs3.setFont(f);
        cs3.setAlignment(CellStyle.ALIGN_RIGHT);
        cs3.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs4 = wb.createCellStyle();
        cs4.setFont(f1);
        cs4.setAlignment(CellStyle.ALIGN_LEFT);
        cs4.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs5 = wb.createCellStyle();
        // create font object
        HSSFFont f2 = wb.createFont();

        //set font to 12 point type
        f2.setFontHeightInPoints((short) 12);
        f2.setFontName("Calibri");
        f2.setBoldweight(Font.BOLDWEIGHT_BOLD);
        f2.setColor(Font.COLOR_RED);
        cs5.setFont(f2);
        cs5.setAlignment(CellStyle.ALIGN_LEFT);
        cs5.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create cell style
        HSSFCellStyle cs6 = wb.createCellStyle();
        cs6.setFont(f);
        cs6.setAlignment(CellStyle.ALIGN_LEFT);
        cs6.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM);

        // create a row
        // row 1
        s.addMergedRegion(new CellRangeAddress(0, 0, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.title"));
        c.setCellStyle(cs);

        // row 2
        String sysInfo = "";
        if (getIsInHomeDomain()) {
            sysInfo = "System ID: " + getSystemId();
            // HiveManager Online user
        } else if (NmsUtil.isHostedHMApplication()) {
            sysInfo = "VHM ID: " + getDomain().getVhmID();
        }
        s.addMergedRegion(new CellRangeAddress(1, 1, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(sysInfo);
        c.setCellStyle(cs);

        // row 3
        s.addMergedRegion(new CellRangeAddress(2, 2, 0, cellcount - 1));
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        List<?> userInfo = QueryUtil.executeQuery(
                "SELECT email, company FROM " + UserRegInfoForLs.class.getSimpleName(), null,
                new FilterParams("owner.domainName", getDomain().getDomainName()));
        if (!userInfo.isEmpty()) {
            Object[] userInfos = (Object[]) userInfo.get(0);
            c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.email.company",
                    new String[] { (String) userInfos[0], (String) userInfos[1] }));
        }
        c.setCellStyle(cs);

        // row 4
        r = s.createRow(rowNum++);

        // row 5
        r = s.createRow(rowNum++);

        // row 6 cell 1
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.licensed"));
        c.setCellStyle(cs1);

        // row 6 cell 2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getHiveAps());
        c.setCellStyle(cs2);

        // device management info
        Map<String, Integer> apcount = HiveAPInfoFromeDatabase
                .getManagedDeviceTypeAndNumber(getDomain().getDomainName());
        int vpnCount = 0;
        int totalCount = 0;
        if (null != apcount) {
            Object[] typeNames = apcount.keySet().toArray();
            Arrays.sort(typeNames);
            for (Object typeName : typeNames) {

                // VPN Gateway VA does not belong to device
                if (MgrUtil.getEnumString("enum.hiveAp.model.10").equals(typeName)) {
                    vpnCount = apcount.get(typeName);
                } else {
                    r = s.createRow(rowNum++);
                    c = r.createCell(0);
                    c.setCellValue((String) typeName);
                    c.setCellStyle(cs3);

                    c = r.createCell(1);
                    c.setCellValue(apcount.get(typeName));
                    c.setCellStyle(cs);
                    totalCount += apcount.get(typeName);
                }
            }

        }

        // managed device total number cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.device.total"));
        c.setCellStyle(cs1);

        // managed device total number cell2
        c = r.createCell(1);
        c.setCellValue(totalCount);
        c.setCellStyle(cs2);

        // blank row
        r = s.createRow(rowNum++);

        // licensed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.licensed"));
        c.setCellStyle(cs1);

        // licensed VPN Gateway VA cell2
        c = r.createCell(1);
        c.setCellValue(licenseInfo.getCvgNumber());
        c.setCellStyle(cs2);

        // managed VPN Gateway VA cell1
        r = s.createRow(rowNum++);
        c = r.createCell(0);
        c.setCellValue(MgrUtil.getUserMessage("admin.license.orderkey.export.cvg.total"));
        c.setCellStyle(cs1);

        // managed VPN Gateway VA cell 2
        c = r.createCell(1);
        c.setCellValue(vpnCount);
        c.setCellStyle(cs2);

        // entitlement key information
        preparePage();
        if (null != page && !page.isEmpty()) {
            r = s.createRow(rowNum++);
            List<OrderHistoryInfo> normalKey = new ArrayList<>();
            List<OrderHistoryInfo> invalidKey = new ArrayList<>();
            List<OrderHistoryInfo> expiredKey = new ArrayList<>();

            for (Object obj : page) {
                OrderHistoryInfo orderInfo = (OrderHistoryInfo) obj;
                if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL
                        && orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_NORMAL) {
                    normalKey.add(orderInfo);
                } else if (orderInfo.getStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE
                        || orderInfo.getCvgStatusFlag() == OrderHistoryInfo.ENTITLE_KEY_STATUS_DISABLE) {
                    invalidKey.add(orderInfo);
                } else {
                    expiredKey.add(orderInfo);
                }
            }

            if (!normalKey.isEmpty()) {

                // normal entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs4, null, r, null, MgrUtil.getUserMessage("order.key"));

                // normal entitle key info
                for (OrderHistoryInfo keyInfo : normalKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!invalidKey.isEmpty()) {

                if (!normalKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // invalid entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.invalidkey.title"));

                // invalid entitle key info
                for (OrderHistoryInfo keyInfo : invalidKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }

            if (!expiredKey.isEmpty()) {

                if (!normalKey.isEmpty() || !invalidKey.isEmpty()) {
                    r = s.createRow(rowNum++);
                    r = s.createRow(rowNum++);
                }

                // expired entitle key title
                r = s.createRow(rowNum++);
                setEntitlementKeyCellValue(cellcount, cs5, null, r, null,
                        MgrUtil.getUserMessage("admin.license.orderkey.export.expiredkey.title"));

                // expired entitle key info
                for (OrderHistoryInfo keyInfo : expiredKey) {
                    r = s.createRow(rowNum++);
                    setEntitlementKeyCellValue(cellcount, cs6, cs3, r, keyInfo, null);
                }
            }
        }

        // write the workbook to the output stream
        // close our file (don't blow out our file handles)
        wb.write(out);
        out.close();
        generateAuditLog(HmAuditLog.STATUS_SUCCESS,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
    } catch (Exception ex) {
        generateAuditLog(HmAuditLog.STATUS_FAILURE,
                MgrUtil.getUserMessage("hm.audit.log.export.entitlement.key"));
        log.error("exportEntitleKeyInfo()", ex.getMessage());
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »