Example usage for org.apache.poi.ss.usermodel CreationHelper createDataFormat

List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. CreationHelper
  6. createDataFormat

Introduction

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

Prototype

DataFormat createDataFormat();

Source Link

Document

Creates a new DataFormat instance

Usage

From source file:org.geoserver.wfs.response.ExcelCellStyles.java

License:Open Source License

public ExcelCellStyles(Workbook wb) {
    CreationHelper helper = wb.getCreationHelper();
    DataFormat fmt = helper.createDataFormat();

    dateStyle = wb.createCellStyle();//from  w  w w  .ja  va2s .  co m
    dateStyle.setDataFormat(fmt.getFormat("yyyy-mm-dd hh:mm:ss"));

    headerStyle = wb.createCellStyle();
    Font headerFont = wb.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerStyle.setFont(headerFont);

    warningStyle = wb.createCellStyle();
    Font warningFont = wb.createFont();
    warningFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    warningFont.setColor(Font.COLOR_RED);
    warningStyle.setFont(warningFont);
}

From source file:org.hil.children.service.impl.ChildrenManagerImpl.java

License:Open Source License

public String printListChildren(ChildrenSearchVO params) {
    List<ChildrenPrintVO> children = childrenDaoExt.searchChildrenForPrint(params);
    SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");
    String strDOBFrom = format.format(params.getDateOfBirthFrom());
    String strDOBTo = format.format(params.getDateOfBirthTo());
    String path = "";
    String prefixFileName = "";
    Commune commune = communeDao.get(params.getCommuneId());
    prefixFileName = commune.getDistrict().getProvince().getProvinceId() + commune.getDistrict().getDistrictId()
            + commune.getCommuneId();//from  w  ww. j  a v a  2s.c  o  m

    GraniteContext gc = GraniteContext.getCurrentInstance();
    ServletContext sc = ((HttpGraniteContext) gc).getServletContext();
    String reportDir = sc.getRealPath(config.getBaseReportDir());
    long currentTime = System.currentTimeMillis();
    String filePath = reportDir + "/" + prefixFileName + "_List_Children_Excel_" + currentTime;

    POIFSFileSystem fs;
    try {
        filePath += ".xls";
        fs = new POIFSFileSystem(new FileInputStream(reportDir + "/excel/ListOfChildrenInCommune.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs, true);

        HSSFSheet s = wb.getSheetAt(0);

        HSSFRow r = null;
        HSSFCell c = null;

        r = s.getRow(0);
        c = r.getCell(1);
        c.setCellValue(commune.getCommuneName());

        c = r.getCell(2);
        c.setCellValue("(" + strDOBFrom + " - " + strDOBTo + ")");

        HSSFCellStyle cs = wb.createCellStyle();
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle cs1 = wb.createCellStyle();
        cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        CreationHelper createHelper = wb.getCreationHelper();
        cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));

        int rownum = 3;
        for (rownum = 3; rownum < children.size() + 3; rownum++) {
            r = s.createRow(rownum);

            c = r.createCell(0);
            c.setCellStyle(cs1);
            c.setCellValue(rownum - 2);

            c = r.createCell(1);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getFullName());

            c = r.createCell(2);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getDateOfBirth());

            c = r.createCell(3);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).isGender() == true ? "N" : "Nam");

            c = r.createCell(4);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getVillageName());

            c = r.createCell(5);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getMotherName());

            c = r.createCell(6);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getMotherBirthYear() != null
                    ? children.get(rownum - 3).getMotherBirthYear()
                    : 0);

            c = r.createCell(7);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getMotherMobile());

            c = r.createCell(8);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getFatherName());

            c = r.createCell(9);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getFatherBirthYear() != null
                    ? children.get(rownum - 3).getFatherBirthYear()
                    : 0);

            c = r.createCell(10);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getFatherMobile());

            c = r.createCell(11);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getCaretakerName());

            c = r.createCell(12);
            c.setCellStyle(cs);
            c.setCellValue(children.get(rownum - 3).getCaretakerMobile());

            c = r.createCell(13);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getVGB());

            c = r.createCell(14);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getBCG());

            c = r.createCell(15);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib1());

            c = r.createCell(16);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib2());

            c = r.createCell(17);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getDPT_VGB_Hib3());

            c = r.createCell(18);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getOPV1());

            c = r.createCell(19);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getOPV2());

            c = r.createCell(20);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getOPV3());

            c = r.createCell(21);
            c.setCellStyle(cs2);
            c.setCellValue(children.get(rownum - 3).getMeasles1());
        }

        FileOutputStream fileOut = new FileOutputStream(filePath);
        wb.write(fileOut);
        fileOut.close();
        path = "/reports/" + prefixFileName + "_List_Children_Excel_" + currentTime + ".xls";
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return path;
}

From source file:org.hil.children.service.impl.ChildrenManagerImpl.java

License:Open Source License

public String printListVaccinatedInLocationReport(String type, String timeFrom, String timeTo, Commune commune,
        District district, Vaccination vaccine, List<ChildrenVaccinatedInLocationVO> statistics) {

    SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy");

    String path = "";
    String prefixFileName = "";
    if (commune != null)
        prefixFileName = district.getDistrictId() + "_" + commune.getCommuneId();
    else if (district != null)
        prefixFileName = district.getDistrictId();
    GraniteContext gc = GraniteContext.getCurrentInstance();
    ServletContext sc = ((HttpGraniteContext) gc).getServletContext();
    String reportDir = sc.getRealPath(config.getBaseReportDir());
    long currentTime = System.currentTimeMillis();
    String filePath = reportDir + "/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_"
            + currentTime;/*from ww  w. j a  v a 2s  .c  o  m*/

    POIFSFileSystem fs;
    try {
        filePath += ".xls";
        fs = new POIFSFileSystem(
                new FileInputStream(reportDir + "/excel/ListOfChildrenVaccinatedInLocation.xls"));
        HSSFWorkbook wb = new HSSFWorkbook(fs, true);

        HSSFSheet s = wb.getSheetAt(0);

        HSSFRow r = null;
        HSSFCell c = null;

        r = s.getRow(0);
        c = r.getCell(0);
        c.setCellValue("Danh sch tr n tim chng " + vaccine.getName()
                + " (bao gm c tr tim  bnh vin/phng khm)");

        r = s.getRow(1);
        c = r.getCell(1);
        c.setCellValue(district.getDistrictName());
        if (commune != null) {
            c = r.getCell(2);
            c.setCellValue("X");
            c = r.getCell(3);
            c.setCellValue(commune.getCommuneName());
            c = r.getCell(4);
            c.setCellValue("(" + timeFrom + " - " + timeTo + ")");
        } else {
            c = r.getCell(2);
            c.setCellValue("(" + timeFrom + " - " + timeTo + ")");
        }
        HSSFCellStyle cs = wb.createCellStyle();
        cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs.setBorderRight(HSSFCellStyle.BORDER_THIN);

        HSSFCellStyle cs1 = wb.createCellStyle();
        cs1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs1.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cs1.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        HSSFCellStyle cs2 = wb.createCellStyle();
        cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cs2.setBorderRight(HSSFCellStyle.BORDER_THIN);
        CreationHelper createHelper = wb.getCreationHelper();
        cs2.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));

        int rownum = 3;
        for (rownum = 3; rownum < statistics.size() + 3; rownum++) {
            r = s.createRow(rownum);

            c = r.createCell(0);
            c.setCellStyle(cs1);
            c.setCellValue(rownum - 2);

            c = r.createCell(1);
            c.setCellStyle(cs);
            c.setCellValue(statistics.get(rownum - 3).getCommuneName());

            c = r.createCell(2);
            c.setCellStyle(cs);
            c.setCellValue(statistics.get(rownum - 3).getVillageName());

            c = r.createCell(3);
            c.setCellStyle(cs);
            c.setCellValue(statistics.get(rownum - 3).getChildCode());

            c = r.createCell(4);
            c.setCellStyle(cs);
            c.setCellValue(statistics.get(rownum - 3).getFullName());

            c = r.createCell(5);
            c.setCellStyle(cs1);
            c.setCellValue(statistics.get(rownum - 3).getGender() == true ? "N" : "Nam");

            c = r.createCell(6);
            c.setCellStyle(cs2);
            c.setCellValue(statistics.get(rownum - 3).getDateOfBirth());

            c = r.createCell(7);
            c.setCellStyle(cs);
            c.setCellValue(statistics.get(rownum - 3).getMotherName());

            c = r.createCell(8);
            c.setCellStyle(cs2);
            c.setCellValue(statistics.get(rownum - 3).getDateOfImmunization());

            c = r.createCell(9);
            c.setCellStyle(cs);
            String vaccinatedLocation = "";
            if (statistics.get(rownum - 3).getOtherLocation() != null
                    && statistics.get(rownum - 3).getOtherLocation() >= 1
                    && statistics.get(rownum - 3).getOtherLocation() <= 4) {
                if (statistics.get(rownum - 3).getOtherLocation() == 1)
                    vaccinatedLocation = "Bnh vin TW";
                else if (statistics.get(rownum - 3).getOtherLocation() == 2)
                    vaccinatedLocation = "Bnh vin tnh";
                else if (statistics.get(rownum - 3).getOtherLocation() == 3)
                    vaccinatedLocation = "Bnh vin huyn";
                else if (statistics.get(rownum - 3).getOtherLocation() == 4)
                    vaccinatedLocation = "Phng khm/Bnh vin t nhn";
            } else
                vaccinatedLocation = statistics.get(rownum - 3).getVaccinatedCommune();
            c.setCellValue(vaccinatedLocation);
        }

        FileOutputStream fileOut = new FileOutputStream(filePath);
        wb.write(fileOut);
        fileOut.close();
        path = "/reports/" + prefixFileName + "_DanhSachTreDenTiem_" + vaccine.getName() + "_" + currentTime
                + ".xls";
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return path;
}

From source file:org.isisaddons.module.excel.dom.ExcelConverter.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    final CreationHelper createHelper = wb.getCreationHelper();
    final short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    final CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

From source file:org.isisaddons.wicket.excel.cpt.ui.ExcelFileModel.java

License:Apache License

protected CellStyle createDateFormatCellStyle(final Workbook wb) {
    CreationHelper createHelper = wb.getCreationHelper();
    short dateFormat = createHelper.createDataFormat().getFormat("yyyy-mm-dd");
    CellStyle dateCellStyle = wb.createCellStyle();
    dateCellStyle.setDataFormat(dateFormat);
    return dateCellStyle;
}

From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Date value, String format) {
    HSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }/* w  w w.  jav  a 2 s.c  o m*/

    HSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

From source file:org.kopsox.spreadsheet.data.excel.ExcelSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Time value, String format) {
    HSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }/*ww  w . j a  v  a  2 s.com*/

    HSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Date value, String format) {
    XSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }/*from  w  w  w .j av a2  s .co m*/

    XSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

From source file:org.kopsox.spreadsheet.data.ooxml.OOXMLSheet.java

License:Open Source License

@Override
public void setValueAt(int row, int column, Time value, String format) {
    XSSFRow excelRow = sheet.getRow(row);
    if (excelRow == null) {
        excelRow = sheet.createRow(row);
    }// www  .j  ava  2 s. c  om

    XSSFCell excelCell = excelRow.getCell(column);
    if (excelCell == null) {
        excelCell = excelRow.createCell(column);
    }

    CreationHelper createHelper = this.sheet.getWorkbook().getCreationHelper();
    CellStyle cellStyle = this.sheet.getWorkbook().createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat(format));
    excelCell.setCellStyle(cellStyle);

    excelCell.setCellValue(value);
    excelCell.setCellType(Cell.CELL_TYPE_NUMERIC);
}

From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java

License:Apache License

@Override
public void generaReporte(ParametrosReporteGlobal param) {
    FileOutputStream fileOut = null;
    try {//w  w  w . j ava2  s. co  m
        //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00"));
        List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal());
        GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination());
        GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial());
        GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal());
        XSSFWorkbook wb = new XSSFWorkbook();
        //estilo para las fechas
        XSSFCellStyle dateCellStyle = wb.createCellStyle();
        CreationHelper createHelper = wb.getCreationHelper();
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        //estilos para las cantidades
        XSSFCellStyle moneyCellStyle = wb.createCellStyle();
        XSSFDataFormat mf = wb.createDataFormat();
        moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00"));
        Sheet sheet = wb.createSheet("Hoja1");
        int rowCount = 1;
        Cell actual;
        Row r;
        for (Servicio x : datos) {
            RenglonRG datRen = renglonFactory.build(x);
            r = sheet.createRow(rowCount);
            rowCount = rowCount + 1;
            for (int i = 0; i < valueMap.length; i++) {
                actual = r.createCell(i);
                if (valueMap[i].getType() == CellValueType.TEXT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    actual.setCellValue(obj.toString());
                }
                if (valueMap[i].getType() == CellValueType.DATE) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    if (obj != null) {
                        Date val = (Date) obj;
                        actual.setCellValue(val);
                        actual.setCellStyle(dateCellStyle);
                    }
                }
                if (valueMap[i].getType() == CellValueType.AMOUNT) {
                    Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue());
                    double val = (double) obj;
                    actual.setCellValue(val);
                    actual.setCellStyle(moneyCellStyle);
                }
                if (valueMap[i].getType() == CellValueType.FORMULA) {
                    String formulaRaw = valueMap[i].getValue();
                    ST formula = new ST(formulaRaw);
                    formula.add("row", rowCount + "");
                    actual.setCellFormula(formula.render());
                    actual.setCellStyle(moneyCellStyle);
                }
            }
        }
        llenarEncabezado(sheet, 0, 0);
        fileOut = new FileOutputStream(param.getDestination());
        wb.write(fileOut);
    } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException
            | IOException ex) {
        GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex);
    } finally {
        try {
            fileOut.close();
        } catch (IOException | NullPointerException ex) {
            GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex);
        }
    }
}