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:it.eng.spagobi.engines.qbe.exporter.QbeXLSExporter.java

License:Mozilla Public License

private CellStyle getDecimalNumberFormat(int j, Sheet sheet, CreationHelper createHelper,
        CellStyle dCellStyle) {//from  ww  w .ja v  a2 s  .  com

    if (decimalFormats.get(j) != null)
        return decimalFormats.get(j);
    String decimals = "";
    for (int i = 0; i < j; i++) {
        decimals += "0";
    }

    CellStyle cellStyleDoub = this.buildCellStyle(sheet); // cellStyleDoub is the default cell style for doubles
    cellStyleDoub.cloneStyleFrom(dCellStyle);
    DataFormat df = createHelper.createDataFormat();
    String format = "#,##0";
    if (decimals.length() > 0) {
        format += "." + decimals;
    }
    cellStyleDoub.setDataFormat(df.getFormat(format));

    decimalFormats.put(j, cellStyleDoub);
    return cellStyleDoub;
}

From source file:it.inspired.exporter.ExcelExporter.java

License:Open Source License

@Override
public void init() {
    super.init();

    // 2 rows are left for the header
    super.currentRow = 0;
    if (super.isEnabledHeader()) {
        super.currentRow = 2;
    }/*from w  ww .  j  av  a2  s . c o  m*/
    workbook = new HSSFWorkbook();
    sheet = workbook.createSheet("export");

    // BigDecimal Style
    DataFormat formatbd = workbook.createDataFormat();
    bigDecimalStyle = workbook.createCellStyle();
    bigDecimalStyle.setDataFormat(formatbd.getFormat("#,##0.0000"));

    // Double Style
    DataFormat formatdb = workbook.createDataFormat();
    doubleStyle = workbook.createCellStyle();
    doubleStyle.setDataFormat(formatdb.getFormat("0.00"));

    // Date Style
    dateStyle = workbook.createCellStyle();
    CreationHelper helper = workbook.getCreationHelper();
    dateStyle.setDataFormat(helper.createDataFormat().getFormat(dateFormat));

    // Integer Style
    DataFormat formatint = workbook.createDataFormat();
    integerStyle = workbook.createCellStyle();
    integerStyle.setDataFormat(formatint.getFormat("0"));

}

From source file:it.redev.parco.ext.ExportableModelEntityQuery.java

License:Open Source License

private void setCell(Cell cell, Object obj) {
    if (obj == null) {
        cell.setCellValue("");
    } else if (obj instanceof Date) {
        cell.setCellValue((Date) obj);
        CellStyle style = workbook.createCellStyle();
        CreationHelper helper = workbook.getCreationHelper();
        style.setDataFormat(helper.createDataFormat().getFormat(dateFormat));
        cell.setCellStyle(style);/*  w w w.java  2s .c o  m*/
    } else if (obj instanceof Boolean) {
        cell.setCellValue((Boolean) obj);
    } else if (obj instanceof Integer) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("0"));
        cell.setCellStyle(style);
    } else if (obj instanceof Double) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("0.00"));
        cell.setCellStyle(style);
    } else if (obj instanceof BigDecimal) {
        cell.setCellValue(Double.parseDouble(obj.toString()));
        DataFormat format = workbook.createDataFormat();
        CellStyle style = workbook.createCellStyle();
        style.setDataFormat(format.getFormat("#,##0.0000"));
        cell.setCellStyle(style);
    } else {
        CreationHelper helper = workbook.getCreationHelper();
        cell.setCellValue(helper.createRichTextString(obj.toString()));
    }
}

From source file:jdbreport.model.io.xls.poi.Excel2003Writer.java

License:Apache License

private CellStyle getStyle(Object styleId, Type cellType, Workbook wb, CreationHelper createHelper) {
    if (cellType == Type.DATE || cellType == Type.FLOAT || cellType == Type.CURRENCY) {
        String key = String.valueOf(styleId) + cellType;
        CellStyle style = styleMap.get(key);
        if (style == null) {
            style = wb.createCellStyle();
            CellStyle parentStyle = styleMap.get(styleId);
            if (parentStyle != null) {
                style.cloneStyleFrom(parentStyle);
            }/*from   w  ww. j a va 2s  .c  o  m*/
            if (cellType == Type.DATE) {
                style.setDataFormat(createHelper.createDataFormat().getFormat("dd.mm.yyyy"));
            } else {
                style.setDataFormat(createHelper.createDataFormat().getFormat("General"));
            }
            styleMap.put(key, style);
        }
        return style;
    }
    return styleMap.get(styleId);
}

From source file:jgnash.convert.exportantur.ssf.AccountExport.java

License:Open Source License

public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate,
        final LocalDate endDate, final File file) {
    Objects.requireNonNull(account);
    Objects.requireNonNull(startDate);
    Objects.requireNonNull(endDate);
    Objects.requireNonNull(file);
    Objects.requireNonNull(columnNames);

    final String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        final Sheet s = wb.createSheet(account.getName());

        // create 2 fonts objects
        final Font defaultFont = wb.createFont();
        final Font headerFont = wb.createFont();

        defaultFont.setFontHeightInPoints((short) 10);
        defaultFont.setColor(IndexedColors.BLACK.getIndex());

        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBold(true);//  ww  w .  j  a  va2  s  .c o  m

        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();

        // Set the other cell style and formatting
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        DataFormat df_header = wb.createDataFormat();

        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        final CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy"));
        dateStyle.setFont(defaultFont);

        final CellStyle timestampStyle = wb.createCellStyle();
        timestampStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-DD HH:MM:SS"));
        timestampStyle.setFont(defaultFont);

        final CellStyle textStyle = wb.createCellStyle();
        textStyle.setFont(defaultFont);

        final CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(defaultFont);
        amountStyle.setAlignment(HorizontalAlignment.RIGHT);

        final DecimalFormat format = (DecimalFormat) NumericFormats
                .getFullCommodityFormat(account.getCurrencyNode());
        final String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        final DataFormat df = wb.createDataFormat();
        amountStyle.setDataFormat(df.getFormat(pattern));

        // Create headers
        int row = 0;
        Row r = s.createRow(row);
        for (int i = 0; i < columnNames.length; i++) {
            Cell c = r.createCell(i);
            c.setCellValue(createHelper.createRichTextString(columnNames[i]));
            c.setCellStyle(headerStyle);
        }

        // Dump the transactions
        for (final Transaction transaction : account.getTransactions(startDate, endDate)) {
            r = s.createRow(++row);

            int col = 0;

            // date
            Cell c = r.createCell(col, CellType.STRING);
            c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
            c.setCellStyle(dateStyle);

            // timestamp
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(DateUtils.asDate(transaction.getTimestamp()));
            c.setCellStyle(timestampStyle);

            // number
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getNumber());
            c.setCellStyle(textStyle);

            // payee
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getPayee());
            c.setCellStyle(textStyle);

            // memo
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getMemo());
            c.setCellStyle(textStyle);

            // account
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(getAccountColumnValue(transaction, account));
            c.setCellStyle(textStyle);

            // clr, strip any zero width spaces
            c = r.createCell(++col, CellType.STRING);
            c.setCellValue(transaction.getReconciled(account).toString().replaceAll(ZERO_WIDTH_SPACE, ""));
            c.setCellStyle(textStyle);

            final BigDecimal amount = transaction.getAmount(account);

            // increase
            c = r.createCell(++col, CellType.NUMERIC);
            if (amount.signum() >= 0) {
                c.setCellValue(amount.doubleValue());
            }
            c.setCellStyle(amountStyle);

            // decrease
            c = r.createCell(++col, CellType.NUMERIC);
            if (amount.signum() < 0) {
                c.setCellValue(amount.abs().doubleValue());
            }
            c.setCellStyle(amountStyle);

            // balance
            c = r.createCell(++col, CellType.NUMERIC);
            c.setCellValue(account.getBalanceAt(transaction).doubleValue());
            c.setCellStyle(amountStyle);
        }

        // autosize the column widths
        final short columnCount = s.getRow(1).getLastCellNum();

        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }

        Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used",
                wb.getNumCellStyles());

        // Save
        final String filename;

        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls";
        }

        try (final OutputStream out = Files.newOutputStream(Paths.get(filename))) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
        }

    } catch (final IOException e) {
        Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:jgnash.convert.exports.ssf.AccountExport.java

License:Open Source License

public static void exportAccount(final Account account, final String[] columnNames, final LocalDate startDate,
        final LocalDate endDate, final File file) {
    Objects.requireNonNull(account);
    Objects.requireNonNull(startDate);
    Objects.requireNonNull(endDate);
    Objects.requireNonNull(file);
    Objects.requireNonNull(columnNames);

    final String extension = FileUtils.getFileExtension(file.getAbsolutePath());

    try (final Workbook wb = extension.equals("xlsx") ? new XSSFWorkbook() : new HSSFWorkbook()) {
        final CreationHelper createHelper = wb.getCreationHelper();

        // create a new sheet
        final Sheet s = wb.createSheet(account.getName());

        // create 2 fonts objects
        final Font defaultFont = wb.createFont();
        final Font headerFont = wb.createFont();

        defaultFont.setFontHeightInPoints((short) 10);
        defaultFont.setColor(IndexedColors.BLACK.getIndex());

        headerFont.setFontHeightInPoints((short) 11);
        headerFont.setColor(IndexedColors.BLACK.getIndex());
        headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);

        // create header cell styles
        final CellStyle headerStyle = wb.createCellStyle();

        // Set the other cell style and formatting
        headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
        headerStyle.setBorderTop(CellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
        headerStyle.setBorderRight(CellStyle.BORDER_THIN);
        headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

        DataFormat df_header = wb.createDataFormat();

        headerStyle.setDataFormat(df_header.getFormat("text"));
        headerStyle.setFont(headerFont);
        headerStyle.setAlignment(CellStyle.ALIGN_CENTER);

        final CellStyle dateStyle = wb.createCellStyle();
        dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mm/dd/yy"));
        dateStyle.setFont(defaultFont);//w  w  w .  j  a v  a2  s. c o  m

        final CellStyle textStyle = wb.createCellStyle();
        textStyle.setFont(defaultFont);

        final CellStyle amountStyle = wb.createCellStyle();
        amountStyle.setFont(defaultFont);
        amountStyle.setAlignment(CellStyle.ALIGN_RIGHT);

        final DecimalFormat format = (DecimalFormat) CommodityFormat
                .getFullNumberFormat(account.getCurrencyNode());
        final String pattern = format.toLocalizedPattern().replace("", account.getCurrencyNode().getPrefix());
        final DataFormat df = wb.createDataFormat();
        amountStyle.setDataFormat(df.getFormat(pattern));

        // Create headers
        int row = 0;
        Row r = s.createRow(row);
        for (int i = 0; i < columnNames.length; i++) {
            Cell c = r.createCell(i);
            c.setCellValue(createHelper.createRichTextString(columnNames[i]));
            c.setCellStyle(headerStyle);
        }

        // Dump the transactions
        for (final Transaction transaction : account.getTransactions(startDate, endDate)) {
            r = s.createRow(++row);

            int col = 0;

            // date
            Cell c = r.createCell(col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(DateUtils.asDate(transaction.getLocalDate()));
            c.setCellStyle(dateStyle);

            // number
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getNumber());
            c.setCellStyle(textStyle);

            // payee
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getPayee());
            c.setCellStyle(textStyle);

            // memo
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getMemo());
            c.setCellStyle(textStyle);

            // account
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(getAccountColumnValue(transaction, account));
            c.setCellStyle(textStyle);

            // clr
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_STRING);
            c.setCellValue(transaction.getReconciled(account).toString());
            c.setCellStyle(textStyle);

            final BigDecimal amount = transaction.getAmount(account);

            // increase
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() >= 0) {
                c.setCellValue(amount.doubleValue());
            }
            c.setCellStyle(amountStyle);

            // decrease
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            if (amount.signum() < 0) {
                c.setCellValue(amount.abs().doubleValue());
            }
            c.setCellStyle(amountStyle);

            // balance
            c = r.createCell(++col);
            c.setCellType(Cell.CELL_TYPE_NUMERIC);
            c.setCellValue(account.getBalanceAt(transaction).doubleValue());
            c.setCellStyle(amountStyle);
        }

        // autosize the column widths
        final short columnCount = s.getRow(1).getLastCellNum();

        // autosize all of the columns + 10 pixels
        for (int i = 0; i <= columnCount; i++) {
            s.autoSizeColumn(i);
            s.setColumnWidth(i, s.getColumnWidth(i) + 10);
        }

        Logger.getLogger(AccountExport.class.getName()).log(Level.INFO, "{0} cell styles were used",
                wb.getNumCellStyles());

        // Save
        final String filename;

        if (wb instanceof XSSFWorkbook) {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xlsx";
        } else {
            filename = FileUtils.stripFileExtension(file.getAbsolutePath()) + ".xls";
        }

        try (final FileOutputStream out = new FileOutputStream(filename)) {
            wb.write(out);
        } catch (final Exception e) {
            Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
        }

    } catch (IOException e) {
        Logger.getLogger(AccountExport.class.getName()).log(Level.SEVERE, e.getLocalizedMessage(), e);
    }
}

From source file:main.ExcelUtils.java

/**
 * Devuelve el CellStyle adecuado para mostrar una fecha en un formato legible
 * @return /*from   w  w w.j av a2  s .  com*/
 */
public CellStyle getDateStyle() {
    CellStyle style = wb.createCellStyle();
    CreationHelper ch = wb.getCreationHelper();
    style.setDataFormat(ch.createDataFormat().getFormat("dd/mm/yyyy"));

    return style;
}

From source file:mx.edu.um.mateo.activos.dao.impl.ActivoDaoHibernate.java

License:Open Source License

@SuppressWarnings("unchecked")
@Override//w w w .jav  a2 s .  c  o m
public void arreglaFechas(OutputStream out) {
    log.debug("Arreglando fechas");
    Date inicio = new Date();
    XSSFWorkbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy"));
    XSSFSheet fechas = wb.createSheet("FECHAS-ANTERIORES");
    int fechasRow = 0;
    XSSFSheet fechas2 = wb.createSheet("FECHAS-POSTERIORES");
    int fechas2Row = 0;
    Transaction tx = null;
    try {
        tx = currentSession().beginTransaction();
        Query update = currentSession()
                .createQuery("update Activo set fechaCompra = :fechaCompra where id = :id");
        Query query = currentSession().createQuery(
                "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra < :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo");
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        query.setDate("fechaCompra", sdf.parse("01/01/1970"));
        List<Activo> activos = query.list();
        int cont = 0;
        for (Activo activo : activos) {
            Calendar cal1 = Calendar.getInstance();
            cal1.setTime(activo.getFechaCompra());
            if (cal1.get(Calendar.YEAR) < 10) {
                log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra());
                cal1.add(Calendar.YEAR, 2000);
                update.setDate("fechaCompra", cal1.getTime());
                update.setLong("id", activo.getId());
                update.executeUpdate();
                XSSFRow renglon = fechas.createRow(fechasRow++);
                renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta());
                renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta());
                renglon.createCell(2).setCellValue(activo.getCodigo());
                renglon.createCell(3).setCellValue(activo.getDescripcion());
                renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra()));
                Cell cell = renglon.createCell(5);
                cell.setCellValue(cal1.getTime());
                cell.setCellStyle(cellStyle);
            } else if (cal1.get(Calendar.YEAR) < 100) {
                log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra());
                cal1.add(Calendar.YEAR, 1900);
                update.setDate("fechaCompra", cal1.getTime());
                update.setLong("id", activo.getId());
                update.executeUpdate();
                XSSFRow renglon = fechas.createRow(fechasRow++);
                renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta());
                renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta());
                renglon.createCell(2).setCellValue(activo.getCodigo());
                renglon.createCell(3).setCellValue(activo.getDescripcion());
                renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra()));
                Cell cell = renglon.createCell(5);
                cell.setCellValue(cal1.getTime());
                cell.setCellStyle(cellStyle);
            } else if (cal1.get(Calendar.YEAR) >= 1900 && cal1.get(Calendar.YEAR) <= 1912) {
                log.debug("Pasando al ao 2000 {} - {}", activo.getDescripcion(), activo.getFechaCompra());
                cal1.add(Calendar.YEAR, 100);
                update.setDate("fechaCompra", cal1.getTime());
                update.setLong("id", activo.getId());
                update.executeUpdate();
                XSSFRow renglon = fechas.createRow(fechasRow++);
                renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta());
                renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta());
                renglon.createCell(2).setCellValue(activo.getCodigo());
                renglon.createCell(3).setCellValue(activo.getDescripcion());
                renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra()));
                Cell cell = renglon.createCell(5);
                cell.setCellValue(cal1.getTime());
                cell.setCellStyle(cellStyle);
            }
            cont++;
        }
        currentSession().flush();

        query = currentSession().createQuery(
                "select new Activo(a.id, a.descripcion, a.fechaCompra, a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo) from Activo a where a.fechaCompra > :fechaCompra order by a.tipoActivo.cuenta.id.idCtaMayor, a.centroCosto.id.idCosto, a.codigo");
        query.setDate("fechaCompra", new Date());
        activos = query.list();
        for (Activo activo : activos) {
            Calendar cal1 = Calendar.getInstance();
            cal1.setTime(activo.getFechaCompra());
            if (cal1.get(Calendar.YEAR) < 2020) {
                log.debug("Quitandole 10 anios {} - {}", activo.getDescripcion(), activo.getFechaCompra());
                cal1.add(Calendar.YEAR, -10);
                update.setDate("fechaCompra", cal1.getTime());
                update.setLong("id", activo.getId());
                update.executeUpdate();
                XSSFRow renglon = fechas2.createRow(fechas2Row++);
                renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta());
                renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta());
                renglon.createCell(2).setCellValue(activo.getCodigo());
                renglon.createCell(3).setCellValue(activo.getDescripcion());
                renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra()));
                Cell cell = renglon.createCell(5);
                cell.setCellValue(cal1.getTime());
                cell.setCellStyle(cellStyle);
            } else if (cal1.get(Calendar.YEAR) >= 2020) {
                log.debug("Pasando al ao 1900 {} - {}", activo.getDescripcion(), activo.getFechaCompra());
                cal1.add(Calendar.YEAR, -100);
                update.setDate("fechaCompra", cal1.getTime());
                update.setLong("id", activo.getId());
                update.executeUpdate();
                XSSFRow renglon = fechas2.createRow(fechas2Row++);
                renglon.createCell(0).setCellValue(activo.getTipoActivoCuenta());
                renglon.createCell(1).setCellValue(activo.getCentroCostoCuenta());
                renglon.createCell(2).setCellValue(activo.getCodigo());
                renglon.createCell(3).setCellValue(activo.getDescripcion());
                renglon.createCell(4).setCellValue(sdf.format(activo.getFechaCompra()));
                Cell cell = renglon.createCell(5);
                cell.setCellValue(cal1.getTime());
                cell.setCellStyle(cellStyle);
            }
            cont++;
        }
        currentSession().flush();

        tx.commit();
        log.debug("Termino actualizando {} de {} en {}",
                new Object[] { cont, activos.size(), ((new Date().getTime() - inicio.getTime()) / 1000) });
        wb.write(out);
    } catch (ParseException | HibernateException | IOException e) {
        log.error("No se pudieron arreglar las fechas de los activos", e);
        tx.rollback();
        throw new RuntimeException("No se pudieron arreglar las fechas de los actios", e);
    }
}

From source file:net.sourceforge.squirrel_sql.fw.gui.action.exportData.DataExportExcelWriter.java

License:Open Source License

private void makeTemporalCell(Cell retVal, Date cellObj, String format) {
    CreationHelper creationHelper = workbook.getCreationHelper();
    CellStyle cellStyle;//w  w  w  .j a va  2 s  .co  m
    if (formatCache == null) {
        cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
        formatCache = new HashMap<String, CellStyle>();
        formatCache.put(format, cellStyle);
    } else {
        cellStyle = formatCache.get(format);
        if (cellStyle == null) {
            cellStyle = workbook.createCellStyle();
            cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat(format));
            formatCache.put(format, cellStyle);
        }
    }
    retVal.setCellStyle(cellStyle);
    if (null != cellObj) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime((Date) cellObj);
        retVal.setCellValue(calendar);
    }
}

From source file:offishell.excel.Excel.java

License:MIT License

/**
 * <p>//from   ww w.  j a v  a  2s .com
 * Create {@link Excel} wrapper.
 * </p>
 * 
 * @param path
 * @param book
 */
private Excel(Path path, XSSFWorkbook book) {
    this.path = path;
    this.book = book;
    this.excel = Locator.file(path);
    this.sheet = book.getSheetAt(0);
    this.baseStyle = book.createCellStyle();
    this.dateStyle = book.createCellStyle();

    CreationHelper helper = book.getCreationHelper();
    DataFormat dateFormat = helper.createDataFormat();

    Font font = book.createFont();
    font.setFontName(" Medium");
    font.setFontHeightInPoints((short) 10);
    baseStyle.setFont(font);
    baseStyle.setAlignment(HorizontalAlignment.CENTER);
    baseStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    baseStyle.setShrinkToFit(true);
    baseStyle.setWrapText(true);

    dateStyle.cloneStyleFrom(baseStyle);
    dateStyle.setDataFormat(dateFormat.getFormat("yyyy/mm/dd"));
}