Example usage for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. autoSizeColumn

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn.

Prototype

@Override
public void autoSizeColumn(int column)

Source Link

Document

Adjusts the column width to fit the contents.

This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.

Usage

From source file:com.openitech.util.HSSFWrapper.java

License:Apache License

public static final HSSFWorkbook getWorkbook(JTable source, boolean countRows) {
    HSSFWorkbook xls_workbook = new HSSFWorkbook();
    HSSFSheet xls_sheet = xls_workbook.createSheet("Pregled podatkov");
    HSSFPrintSetup xls_sheet_printsetup = xls_sheet.getPrintSetup();
    xls_sheet_printsetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);
    xls_sheet_printsetup.setFitWidth((short) 1);

    TableColumnModel columnModel = source.getColumnModel();
    Enumeration<TableColumn> columns = columnModel.getColumns();

    HSSFRow xls_row = xls_sheet.createRow(0);
    short cell = 1;
    HSSFCellStyle xls_header_cell_style = xls_workbook.createCellStyle();
    HSSFFont xls_header_font = xls_workbook.createFont();

    xls_header_font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

    xls_header_cell_style.setFont(xls_header_font);
    xls_header_cell_style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    xls_header_cell_style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    xls_header_cell_style.setFillForegroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());
    //xls_header_cell_style.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);

    java.util.Map<String, HSSFCellStyle> cellStyles = new java.util.HashMap<String, HSSFCellStyle>();

    HSSFDataFormat xls_data_format = xls_workbook.createDataFormat();

    HSSFCellStyle xls_date_cell_style = xls_workbook.createCellStyle();
    xls_date_cell_style.setDataFormat(xls_data_format.getFormat("d.m.yyyy"));
    cellStyles.put("d.m.yyyy", xls_date_cell_style);

    HSSFCellStyle xls_double_cell_style = xls_workbook.createCellStyle();
    xls_double_cell_style.setDataFormat(xls_data_format.getFormat("#,##0.00"));
    cellStyles.put("#,##0.00", xls_double_cell_style);

    while (columns.hasMoreElements()) {
        TableColumn column = columns.nextElement();

        HSSFCell xls_cell = xls_row.createCell(cell++);
        xls_cell.setCellValue(new HSSFRichTextString(column.getHeaderValue().toString()));
        xls_cell.setCellStyle(xls_header_cell_style);
    }/*from   w  ww  .j a  v  a  2  s . c om*/

    TableModel tableModel = source.getModel();
    DbTableModel dbTableModel = (tableModel instanceof DbTableModel) ? (DbTableModel) tableModel : null;
    Integer fetchSize = null;

    if (dbTableModel != null) {
        try {
            fetchSize = dbTableModel.getDataSource().getFetchSize();
            dbTableModel.getDataSource().setFetchSize(2000);
        } catch (SQLException ex) {
            Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            fetchSize = null;
        }
    }

    short row = 1;

    JWProgressMonitor progress = new JWProgressMonitor((java.awt.Frame) null);

    progress.setTitle("Izvoz podatkov v Excel");
    progress.setMax(tableModel.getRowCount());

    progress.setVisible(true);

    try {
        while (row <= tableModel.getRowCount()) {
            xls_row = xls_sheet.createRow(row);
            cell = 0;

            HSSFCell xls_cell = xls_row.createCell(cell++);
            if (countRows) {
                xls_cell.setCellValue(new HSSFRichTextString(Short.toString(row)));
            }

            while (cell <= columnModel.getColumnCount()) {
                Object value = tableModel.getValueAt(source.convertRowIndexToModel(row - 1),
                        source.convertColumnIndexToModel(cell - 1));
                if (value != null) {
                    if (value instanceof DbTableModel.ColumnDescriptor.ValueMethod) {
                        DbTableModel.ColumnDescriptor.ValueMethod vm = (DbTableModel.ColumnDescriptor.ValueMethod) value;

                        if (vm.getColumnNames().size() == 1) {
                            java.util.List<Object> values = vm.getValues();
                            java.util.List<String> cellFormats = vm.getCellFormats();

                            for (String cellFormat : cellFormats) {
                                if (cellFormat != null) {
                                    if (!cellStyles.containsKey(cellFormat)) {
                                        HSSFCellStyle xls_cell_style = xls_workbook.createCellStyle();
                                        xls_cell_style.setDataFormat(xls_data_format.getFormat(cellFormat));
                                        cellStyles.put(cellFormat, xls_cell_style);
                                    }
                                }
                            }

                            Object vm_value = values.get(0);
                            HSSFCellStyle xls_cell_style = cellFormats.get(0) == null ? null
                                    : cellStyles.get(cellFormats.get(0));

                            if (vm_value != null) {
                                xls_cell = xls_row.createCell(cell);

                                if (vm_value instanceof java.util.Date) {
                                    xls_cell.setCellValue((java.util.Date) vm_value);
                                    xls_cell.setCellStyle(
                                            xls_cell_style == null ? xls_date_cell_style : xls_cell_style);
                                } else if (vm_value instanceof java.lang.Number) {
                                    xls_cell.setCellValue(((java.lang.Number) vm_value).doubleValue());
                                    if ((vm_value instanceof java.math.BigDecimal)
                                            || (vm_value instanceof java.lang.Double)
                                            || (vm_value instanceof java.lang.Float)) {
                                        xls_cell.setCellStyle(xls_cell_style == null ? xls_double_cell_style
                                                : xls_cell_style);
                                    }
                                } else if (vm_value instanceof java.lang.Boolean) {
                                    xls_cell.setCellValue(((java.lang.Boolean) vm_value).booleanValue());
                                } else {
                                    xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                                }
                            }
                        } else {
                            xls_cell = xls_row.createCell(cell);
                            xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                        }
                    } else {
                        xls_cell = xls_row.createCell(cell);
                        xls_cell.setCellValue(new HSSFRichTextString(value.toString()));
                    }
                }
                cell++;
            }

            row++;
            progress.next();
        }

        for (cell = 0; cell <= columnModel.getColumnCount(); cell++) {
            xls_sheet.autoSizeColumn(cell);
        }

        xls_sheet.createFreezePane(1, 1);
    } finally {
        progress.setVisible(false);

        if (fetchSize != null) {
            try {
                dbTableModel.getDataSource().setFetchSize(fetchSize);
            } catch (SQLException ex) {
                Logger.getLogger(HSSFWrapper.class.getName()).log(Level.WARNING, null, ex);
            }
        }
    }

    return xls_workbook;
}

From source file:com.qcadoo.mes.assignmentToShift.print.xls.AssignmentToShiftXlsService.java

License:Open Source License

@Override
protected void addSeries(final HSSFSheet sheet, final Entity entity) {
    List<DateTime> days = assignmentToShiftXlsHelper.getDaysBetweenGivenDates(entity);

    if (days != null) {
        int rowNum = 4;
        List<Entity> occupationTypesWithoutTechnicalCode = getOccupationTypeDictionaryWithoutTechnicalCode();
        List<Entity> productionlines = assignmentToShiftXlsHelper.getProductionLines();

        if (!productionlines.isEmpty()) {
            rowNum = fillColumnWithStaffForWorkOnLine(sheet, rowNum, entity, days, productionlines,
                    getDictionaryItemWithProductionOnLine());
        }//  w w w. j ava  2 s  . c o m
        for (Entity dictionaryItem : occupationTypesWithoutTechnicalCode) {
            rowNum = fillColumnWithStaffForOtherTypes(sheet, rowNum, entity, days, dictionaryItem);
            // rowNum++;
        }
        fillColumnWithStaffForOtherTypes(sheet, rowNum, entity, days, getDictionaryItemWithOtherCase());
        sheet.autoSizeColumn(0);
    }

}

From source file:com.qcadoo.mes.materialFlow.print.xls.MaterialFlowXlsService.java

License:Open Source License

@Override
protected void addSeries(final HSSFSheet sheet, final Entity materialsInLocation) {
    Map<Entity, BigDecimal> reportData = materialFlowService
            .calculateMaterialQuantitiesInLocation(materialsInLocation);

    int rowNum = 1;
    for (Map.Entry<Entity, BigDecimal> data : reportData.entrySet()) {
        HSSFRow row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(data.getKey().getStringField(NUMBER));
        row.createCell(1).setCellValue(data.getKey().getStringField(NAME));
        row.createCell(2).setCellValue(numberService.format(data.getValue()));
        row.createCell(3).setCellValue(data.getKey().getStringField(UNIT));
    }/*from w  ww  .  j a  va2 s.  co  m*/
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
}

From source file:com.qcadoo.mes.materialRequirements.print.xls.MaterialRequirementXlsService.java

License:Open Source License

@Override
protected void addSeries(final HSSFSheet sheet, final Entity materialRequirement) {
    int rowNum = 1;
    List<Entity> orders = materialRequirement.getManyToManyField(MaterialRequirementFields.ORDERS);
    MrpAlgorithm algorithm = MrpAlgorithm
            .parseString(materialRequirement.getStringField(MaterialRequirementFields.MRP_ALGORITHM));

    Map<Long, BigDecimal> neededProductQuantities = productQuantitiesService.getNeededProductQuantities(orders,
            algorithm, true);/*from w  w w  . j  a  v a 2 s.  co m*/

    // TODO LUPO fix comparator
    // neededProductQuantities = SortUtil.sortMapUsingComparator(neededProductQuantities, new EntityNumberComparator());

    for (Entry<Long, BigDecimal> neededProductQuantity : neededProductQuantities.entrySet()) {
        Entity product = productQuantitiesService.getProduct(neededProductQuantity.getKey());

        HSSFRow row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(product.getStringField(ProductFields.NUMBER));
        row.createCell(1).setCellValue(product.getStringField(ProductFields.NAME));
        row.createCell(2).setCellValue(numberService.setScale(neededProductQuantity.getValue()).doubleValue());
        String unit = product.getStringField(ProductFields.UNIT);
        if (unit == null) {
            row.createCell(3).setCellValue("");
        } else {
            row.createCell(3).setCellValue(unit);
        }
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForBatchXlsView.java

License:Open Source License

private void addOrderSeries(final Map<String, Object> model, final HSSFSheet sheet) {
    int rowNum = 1;
    Map<Entity, List<Entity>> productOrders = qualityControlsReportService.getQualityOrdersForProduct(
            qualityControlsReportService.getOrderSeries(model, "qualityControlsForBatch"));
    productOrders = SortUtil.sortMapUsingComparator(productOrders, new EntityNumberComparator());
    for (Entry<Entity, List<Entity>> entry : productOrders.entrySet()) {
        List<Entity> orders = entry.getValue();
        Collections.sort(orders, new EntityBatchNumberComparator());
        for (Entity order : orders) {
            HSSFRow row = sheet.createRow(rowNum++);
            row.createCell(0)// w  w w  .ja  v a2s. c o  m
                    .setCellValue(entry.getKey() == null ? "" : entry.getKey().getStringField("number"));
            row.createCell(1).setCellValue(
                    order.getStringField("batchNr") == null ? "" : order.getStringField("batchNr"));
            row.createCell(2).setCellValue(order.getStringField("number"));
            row.createCell(3).setCellValue(
                    numberService.setScale(order.getDecimalField("controlledQuantity")).doubleValue());
            row.createCell(4).setCellValue(
                    numberService.setScale(order.getDecimalField("rejectedQuantity")).doubleValue());
            row.createCell(5).setCellValue(
                    numberService.setScale(order.getDecimalField("acceptedDefectsQuantity")).doubleValue());
        }
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForOperationXlsView.java

License:Open Source License

private void addOrderSeries(final Map<String, Object> model, final HSSFSheet sheet, final Locale locale) {
    int rowNum = 1;
    Map<Entity, List<Entity>> operationOrders = qualityControlsReportService.getQualityOrdersForOperation(
            qualityControlsReportService.getOrderSeries(model, "qualityControlsForOperation"));
    operationOrders = SortUtil.sortMapUsingComparator(operationOrders, new EntityNumberComparator());
    for (Entry<Entity, List<Entity>> entry : operationOrders.entrySet()) {
        List<Entity> orders = entry.getValue();
        Collections.sort(orders, new EntityNumberComparator());
        for (Entity order : orders) {
            HSSFRow row = sheet.createRow(rowNum++);
            row.createCell(0)//from  ww w.  j  a v a2  s.  co  m
                    .setCellValue(entry.getKey() == null ? "" : entry.getKey().getStringField("nodeNumber"));
            row.createCell(1).setCellValue(order.getStringField("number"));
            String result = "";
            if ("01correct".equals(order.getField("controlResult"))) {
                result = translationService
                        .translate("qualityControls.qualityControl.controlResult.value.01correct", locale);
            } else if ("02incorrect".equals(order.getField("controlResult"))) {
                result = translationService
                        .translate("qualityControls.qualityControl.controlResult.value.02incorrect", locale);
            } else if ("03objection".equals(order.getField("controlResult"))) {
                result = translationService
                        .translate("qualityControls.qualityControl.controlResult.value.03objection", locale);
            }
            row.createCell(2).setCellValue(result);
        }
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForOrderXlsView.java

License:Open Source License

private void addOrderSeries(final Map<String, Object> model, final HSSFSheet sheet, final Locale locale) {
    int rowNum = 1;
    Map<Entity, List<Entity>> productOrders = qualityControlsReportService.getQualityOrdersForProduct(
            qualityControlsReportService.getOrderSeries(model, "qualityControlsForOrder"));
    productOrders = SortUtil.sortMapUsingComparator(productOrders, new EntityNumberComparator());
    for (Entry<Entity, List<Entity>> entry : productOrders.entrySet()) {
        List<Entity> orders = entry.getValue();
        Collections.sort(orders, new EntityNumberComparator());
        for (Entity order : orders) {
            HSSFRow row = sheet.createRow(rowNum++);
            row.createCell(0)//from   w w  w  . j  a  va2  s . c  om
                    .setCellValue(entry.getKey() == null ? "" : entry.getKey().getStringField("number"));
            row.createCell(1).setCellValue(order.getStringField("number"));
            String result = "";
            String controlResult = order.getStringField("controlResult");
            if ("01correct".equals(controlResult)) {
                result = translationService
                        .translate("qualityControls.qualityForOrder.controlResult.value.01correct", locale);
            } else if ("02incorrect".equals(controlResult)) {
                result = translationService
                        .translate("qualityControls.qualityForOrder.controlResult.value.02incorrect", locale);
            } else if ("03objection".equals(controlResult)) {
                result = translationService
                        .translate("qualityControls.qualityForOrder.controlResult.value.03objection", locale);
            }
            row.createCell(2).setCellValue(result);
        }
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
}

From source file:com.qcadoo.mes.qualityControls.print.QualityControlForUnitXlsView.java

License:Open Source License

private void addOrderSeries(final Map<String, Object> model, final HSSFSheet sheet) {
    int rowNum = 1;
    Map<Entity, List<Entity>> productOrders = qualityControlsReportService.getQualityOrdersForProduct(
            qualityControlsReportService.getOrderSeries(model, "qualityControlsForUnit"));
    productOrders = SortUtil.sortMapUsingComparator(productOrders, new EntityNumberComparator());
    for (Entry<Entity, List<Entity>> entry : productOrders.entrySet()) {
        List<Entity> orders = entry.getValue();
        Collections.sort(orders, new EntityNumberComparator());
        for (Entity order : orders) {
            HSSFRow row = sheet.createRow(rowNum++);
            row.createCell(0)/*from  w w w  .  j  av a 2  s . c om*/
                    .setCellValue(entry.getKey() == null ? "" : entry.getKey().getStringField("number"));
            row.createCell(1).setCellValue(order.getStringField("number"));
            row.createCell(2).setCellValue(
                    numberService.setScale(order.getDecimalField("controlledQuantity")).doubleValue());
            row.createCell(3).setCellValue(
                    numberService.setScale(order.getDecimalField("rejectedQuantity")).doubleValue());
            row.createCell(4).setCellValue(
                    numberService.setScale(order.getDecimalField("acceptedDefectsQuantity")).doubleValue());
        }
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
}

From source file:com.qcadoo.mes.simpleMaterialBalance.internal.print.SimpleMaterialBalanceXlsService.java

License:Open Source License

@Override
protected void addSeries(final HSSFSheet sheet, final Entity simpleMaterialBalance) {
    int rowNum = 1;
    List<Entity> simpleMaterialBalanceOrdersComponents = simpleMaterialBalance
            .getHasManyField(L_SIMPLE_MATERIAL_BALANCE_ORDERS_COMPONENTS);
    MrpAlgorithm mrpAlgorithm = MrpAlgorithm.parseString(simpleMaterialBalance.getStringField("mrpAlgorithm"));

    Map<Long, BigDecimal> neededProductQuantities = productQuantitiesService
            .getNeededProductQuantitiesForComponents(simpleMaterialBalanceOrdersComponents, mrpAlgorithm);

    List<Entity> simpleMaterialBalanceLocationComponents = simpleMaterialBalance
            .getHasManyField(L_SIMPLE_MATERIAL_BALANCE_LOCATIONS_COMPONENTS);

    // TODO LUPO fix comparator
    // neededProductQuantities = SortUtil.sortMapUsingComparator(neededProductQuantities, new EntityNumberComparator());

    for (Entry<Long, BigDecimal> neededProductQuantity : neededProductQuantities.entrySet()) {
        Entity product = productQuantitiesService.getProduct(neededProductQuantity.getKey());

        HSSFRow row = sheet.createRow(rowNum++);
        row.createCell(0).setCellValue(product.getField(L_NUMBER).toString());
        row.createCell(1).setCellValue(product.getField(L_NAME).toString());
        row.createCell(2).setCellValue(product.getField(L_UNIT).toString());
        row.createCell(3).setCellValue(numberService.format(neededProductQuantity.getValue()));
        BigDecimal available = BigDecimal.ZERO;
        for (Entity simpleMaterialBalanceLocationComponent : simpleMaterialBalanceLocationComponents) {
            available = available.add(// w ww .j  a  v  a  2  s .  c  om
                    materialFlowService.calculateShouldBeInLocation(
                            simpleMaterialBalanceLocationComponent.getBelongsToField(L_LOCATION).getId(),
                            product.getId(), (Date) simpleMaterialBalance.getField(L_DATE)),
                    numberService.getMathContext());
        }
        row.createCell(4).setCellValue(numberService.format(available));
        row.createCell(5).setCellValue(numberService
                .format(available.subtract(neededProductQuantity.getValue(), numberService.getMathContext())));
    }
    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
}

From source file:com.qcadoo.mes.technologies.print.TechnologiesTechnologyDetailsXlsView.java

License:Open Source License

private void addOrderSeries(final Map<String, Object> model, final HSSFSheet sheet, final Locale locale) {
    checkState(model.get("id") != null, "Unable to generate report for unsaved technology! (missing id)");

    DataDefinition technologyDD = dataDefinitionService.get(PLUGIN_IDENTIFIER, MODEL_TECHNOLOGY);

    Entity technology = technologyDD.get(valueOf(model.get("id").toString()));

    EntityTree technologyTree = technology.getTreeField(TechnologyFields.OPERATION_COMPONENTS);
    treeNumberingService.generateTreeNumbers(technologyTree);

    List<Entity> technologyOperationsList = entityTreeUtilsService.getSortedEntities(technologyTree);

    int rowNum = 1;
    for (Entity technologyOperation : technologyOperationsList) {
        String nodeNumber = technologyOperation.getStringField(TechnologyOperationComponentFields.NODE_NUMBER);
        String operationName = technologyOperation
                .getBelongsToField(TechnologyOperationComponentFields.OPERATION)
                .getStringField(OperationFields.NAME);

        List<Entity> operationProductComponents = Lists.newLinkedList();

        operationProductComponents.addAll(technologyOperation
                .getHasManyField(TechnologyOperationComponentFields.OPERATION_PRODUCT_IN_COMPONENTS));
        operationProductComponents.addAll(technologyOperation
                .getHasManyField(TechnologyOperationComponentFields.OPERATION_PRODUCT_OUT_COMPONENTS));

        for (Entity operatonProductComponent : operationProductComponents) {
            HSSFRow row = sheet.createRow(rowNum++);

            Entity product = operatonProductComponent.getBelongsToField(L_PRODUCT);

            String productType = "technologies.technologiesTechnologyDetails.report.direction.out";

            if (operatonProductComponent.getDataDefinition().getName().equals("operationProductInComponent")) {
                productType = "technologies.technologiesTechnologyDetails.report.direction.in";
            }/* ww  w.ja  va 2 s . co m*/

            row.createCell(0).setCellValue(nodeNumber);
            row.createCell(1).setCellValue(operationName);
            row.createCell(2).setCellValue(translationService.translate(productType, locale));
            row.createCell(3).setCellValue(product.getStringField(ProductFields.NUMBER));
            row.createCell(4).setCellValue(product.getStringField(ProductFields.NAME));
            row.createCell(5).setCellValue(operatonProductComponent.getField(L_QUANTITY).toString());
            row.createCell(6).setCellValue(product.getStringField(ProductFields.UNIT));
        }
    }

    sheet.autoSizeColumn((short) 0);
    sheet.autoSizeColumn((short) 1);
    sheet.autoSizeColumn((short) 2);
    sheet.autoSizeColumn((short) 3);
    sheet.autoSizeColumn((short) 4);
    sheet.autoSizeColumn((short) 5);
    sheet.autoSizeColumn((short) 6);
}