Examples with CSheet eu.ggnet.lucidcalc.CSheet used on opensource projects

Example 1 with CSheet

use of eu.ggnet.lucidcalc.CSheet in project dwoss by gg-net.

the class CreditMemoReporterOperation method toOptimizedXls.

@Override
public FileJacket toOptimizedXls(Date start, Date end) {
    SubMonitor m = monitorFactory.newSubMonitor("Generating Report", 25);
    m.message("Loading Credit Memos");
    UniqueUnitEao unitEao = new UniqueUnitEao(uuEm);
    List<Object[]> rows = new ArrayList<>();
    List<Document> documents = new DocumentEao(redTapeEm).findDocumentsBetweenDates(start, end, DocumentType.CREDIT_MEMO, DocumentType.ANNULATION_INVOICE);
    m.setWorkRemaining(documents.size());
    for (Document document : documents) {
        String salesDate = SimpleDateFormat.getDateInstance(SimpleDateFormat.SHORT, Locale.GERMANY).format(document.getActual());
        UiCustomer customer = customerService.asUiCustomer(document.getDossier().getCustomerId());
        double price = 0;
        double afterTaxPrice = 0;
        boolean full = true;
        String sopos = "";
        for (Position position : document.getPositions().values()) {
            if (position.getType() == PositionType.COMMENT)
                continue;
            if (position.getType() == PositionType.UNIT || position.getType() == PositionType.UNIT_ANNEX) {
                full = position.getType() == PositionType.UNIT;
                String refurbishId = unitEao.findById(position.getUniqueUnitId()).getRefurbishId();
                sopos += (sopos.isEmpty()) ? refurbishId : " ," + refurbishId;
            } else if (position.getType() == PositionType.SHIPPING_COST) {
                Document invoice = document.getDossier().getActiveDocuments(DocumentType.INVOICE).get(0);
                SortedMap<Integer, Position> positions = invoice.getPositions(PositionType.SHIPPING_COST);
                if (positions.size() > 0) {
                    Position get = positions.get(positions.firstKey());
                    if (Math.abs(Math.abs(get.getPrice()) - Math.abs(position.getPrice())) > 0.001)
                        full = false;
                }
            }
            price += position.getPrice();
            afterTaxPrice += position.toAfterTaxPrice();
        }
        rows.add(new Object[] { document.getDossier().getIdentifier(), customer.getId(), salesDate, document.getIdentifier(), (full) ? "V" : "T", price, afterTaxPrice, customer.getCompany(), customer.toNameLine(), sopos });
        m.worked(1);
    }
    m.message("Generating Document");
    m.setWorkRemaining(10);
    STable table = new STable();
    table.setTableFormat(new CFormat(BLACK, WHITE, new CBorder(BLACK)));
    table.setHeadlineFormat(new CFormat(CFormat.FontStyle.BOLD, Color.BLACK, Color.YELLOW, CFormat.HorizontalAlignment.LEFT, CFormat.VerticalAlignment.BOTTOM, CFormat.Representation.DEFAULT));
    table.add(new STableColumn("DossierId", 15)).add(new STableColumn("Kid", 10)).add(new STableColumn("Belegdatum", 10));
    table.add(new STableColumn("DokumentId", 15)).add(new STableColumn("Voll/Teil", 10));
    table.add(new STableColumn("Netto", 10, new CFormat(CFormat.HorizontalAlignment.RIGHT, CFormat.Representation.CURRENCY_EURO)));
    table.add(new STableColumn("Brutto", 10, new CFormat(CFormat.HorizontalAlignment.RIGHT, CFormat.Representation.CURRENCY_EURO)));
    table.add(new STableColumn("Firma", 25)).add(new STableColumn("Name", 25)).add(new STableColumn("SopoNr.", 10));
    table.setModel(new STableModelList(rows));
    CCalcDocument cdoc = new TempCalcDocument("Gutschriften_");
    cdoc.add(new CSheet("Sheet1", table));
    File file = LucidCalc.createWriter(LucidCalc.Backend.XLS).write(cdoc);
    m.finish();
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd_MM_yyy");
    return new FileJacket("Gutschriften_" + dateFormat.format(start) + "-" + dateFormat.format(end), ".xls", file);
}

Example 2 with CSheet

use of eu.ggnet.lucidcalc.CSheet in project dwoss by gg-net.

the class CreditMemoReporterOperation method toXls.

@Override
public FileJacket toXls(Date start, Date end) {
    SubMonitor m = monitorFactory.newSubMonitor("Generating Report", 25);
    m.message("Loading Credit Memos");
    List<Position> creditMemoPositions = new ArrayList<>();
    for (Document document : new DocumentEao(redTapeEm).findDocumentsBetweenDates(start, end, DocumentType.COMPLAINT, DocumentType.CREDIT_MEMO, DocumentType.ANNULATION_INVOICE)) {
        for (Position position : document.getPositions().values()) {
            creditMemoPositions.add(position);
        }
    }
    m.setWorkRemaining(creditMemoPositions.size() * 2);
    UniqueUnitEao unitEao = new UniqueUnitEao(uuEm);
    m.message(null);
    List<Object[]> rows = new ArrayList<>();
    for (Position position : creditMemoPositions) {
        if (position.getType() == PositionType.COMMENT)
            continue;
        Document doc = position.getDocument();
        UiCustomer customer = customerService.asUiCustomer(position.getDocument().getDossier().getCustomerId());
        UniqueUnit unit = unitEao.findById(position.getUniqueUnitId());
        String error = "ERROR FINDING UNIT";
        String sopoNr = "-";
        String serial = "-";
        String contractor = "-";
        String fullOrPartial = "-";
        double customerPrice = 0;
        double retailerPrice = 0;
        if (position.getType() == PositionType.UNIT || position.getType() == PositionType.UNIT_ANNEX) {
            if (unit == null) {
                sopoNr = error;
                serial = error;
                contractor = error;
            } else {
                sopoNr = unit.getIdentifier(UniqueUnit.Identifier.REFURBISHED_ID);
                serial = unit.getIdentifier(UniqueUnit.Identifier.SERIAL);
                contractor = unit.getContractor().getName();
                customerPrice = unit.getPrice(PriceType.CUSTOMER);
                retailerPrice = unit.getPrice(PriceType.RETAILER);
                fullOrPartial = (position.getType() == PositionType.UNIT ? "V" : "T");
                if (position.getDocument().getType() == DocumentType.COMPLAINT)
                    fullOrPartial = "Rekla";
            }
        }
        rows.add(new Object[] { sopoNr, serial, position.getName(), contractor, fullOrPartial, position.getPrice(), position.toAfterTaxPrice(), position.getDocument().getDossier().getIdentifier(), doc.getActual(), position.getDocument().getIdentifier(), customer.getId(), customer.toNameCompanyLine(), customerPrice, retailerPrice });
        m.worked(5);
    }
    Collections.sort(rows, comperator);
    m.message("Generating Document");
    m.setWorkRemaining(10);
    STable table = new STable();
    table.setTableFormat(new CFormat(BLACK, WHITE, new CBorder(BLACK)));
    table.setHeadlineFormat(new CFormat(CFormat.FontStyle.BOLD, Color.BLACK, Color.YELLOW, CFormat.HorizontalAlignment.LEFT, CFormat.VerticalAlignment.BOTTOM, CFormat.Representation.DEFAULT));
    table.add(new STableColumn("SopoNr", 15)).add(new STableColumn("Seriennummer", 30)).add(new STableColumn("Name", 50)).add(new STableColumn("Contractor", 15)).add(new STableColumn("Voll/Teil", 10)).add(new STableColumn("Netto", 10, EURO)).add(new STableColumn("Brutto", 10, EURO)).add(new STableColumn("DossierId", 15)).add(new STableColumn("Belegdatum", 10, new CFormat(SHORT_DATE))).add(new STableColumn("DokumentId", 15)).add(new STableColumn("Kid", 10)).add(new STableColumn("Kunde", 50)).add(new STableColumn("Endkundenpreis(Netto)", 25, EURO)).add(new STableColumn("Händlerpreis(Netto)", 25, EURO));
    table.setModel(new STableModelList(rows));
    CCalcDocument cdoc = new TempCalcDocument("Gutschriften_");
    cdoc.add(new CSheet("Sheet1", table));
    File file = LucidCalc.createWriter(LucidCalc.Backend.XLS).write(cdoc);
    m.finish();
    SimpleDateFormat dateFormat = new SimpleDateFormat("dd_MM_yyy");
    return new FileJacket("Gutschriften_" + dateFormat.format(start) + "-" + dateFormat.format(end), ".xls", file);
}

Example 3 with CSheet

use of eu.ggnet.lucidcalc.CSheet in project dwoss by gg-net.

the class JExcelLucidCalcWriter method write.

@Override
public File write(CCalcDocument document) {
    if (document == null)
        return null;
    validate(document);
    try {
        Map<CFormat, WritableCellFormat> formatCache = new HashMap<>();
        File file = document.getFile();
        WorkbookSettings ws = new WorkbookSettings();
        ws.setLocale(Locale.GERMANY);
        WritableWorkbook workbook = Workbook.createWorkbook(file, ws);
        FormatUtil util = new FormatUtil();
        for (int sheetIndex = 0; sheetIndex < document.getSheets().size(); sheetIndex++) {
            CSheet csheet = document.getSheets().get(sheetIndex);
            WritableSheet jsheet = workbook.createSheet(csheet.getName(), sheetIndex);
            jsheet.getSettings().setShowGridLines(csheet.isShowGridLines());
            for (CColumnView cColumn : csheet.getColumnViews()) {
                jsheet.setColumnView(cColumn.getColumnIndex(), cColumn.getSize());
            }
            for (CRowView cRowView : csheet.getRowViews()) {
                jsheet.setRowView(cRowView.getRowIndex(), cRowView.getSize());
            }
            for (CCell cCell : csheet.getCells()) {
                WritableCellFormat jformat;
                CFormat cFormat = cCell.getFormat();
                if (cFormat == null) {
                    jformat = WritableWorkbook.NORMAL_STYLE;
                } else {
                    cFormat = cFormat.fillNull(DEFAULT_FORMAT);
                    if (formatCache.containsKey(cFormat)) {
                        jformat = formatCache.get(cFormat);
                    } else {
                        WritableFont jfont = new WritableFont(WritableFont.createFont(cFormat.getName()));
                        jfont.setPointSize(cFormat.getSize());
                        jfont.setColour(util.discover(cFormat.getForeground()));
                        switch(cFormat.getStyle()) {
                            case BOLD:
                                jfont.setBoldStyle(jxl.write.WritableFont.BOLD);
                                break;
                            case ITALIC:
                                jfont.setItalic(true);
                                break;
                            case BOLD_ITALIC:
                                jfont.setBoldStyle(jxl.write.WritableFont.BOLD);
                                jfont.setItalic(true);
                                break;
                        }
                        jformat = new WritableCellFormat(jfont, util.discover(cFormat.getRepresentation()));
                        jformat.setBackground(util.discover(cFormat.getBackground()));
                        jformat.setAlignment(util.discover(cFormat.getHorizontalAlignment()));
                        jformat.setVerticalAlignment(util.discover(cFormat.getVerticalAlignment()));
                        if (cFormat.getBorder() != null) {
                            jformat.setBorder(Border.ALL, util.discover(cFormat.getBorder().getLineStyle()), util.discover(cFormat.getBorder().getColor()));
                        }
                        if (cFormat.isWrap() != null)
                            jformat.setWrap(cFormat.isWrap());
                        formatCache.put(cFormat, jformat);
                    }
                }
                Object elem = cCell.getValue();
                WritableCell jcell = null;
                if (elem == null) {
                    jcell = new Blank(cCell.getColumnIndex(), cCell.getRowIndex(), jformat);
                } else if (elem instanceof Number) {
                    double value = Double.MIN_VALUE;
                    if (elem instanceof Integer) {
                        value = ((Integer) elem).doubleValue();
                    } else if (elem instanceof Long) {
                        value = ((Long) elem).doubleValue();
                    } else if (elem instanceof Float) {
                        value = ((Float) elem).doubleValue();
                    } else if (elem instanceof Double) {
                        value = (Double) elem;
                    }
                    // TODO The rest of the 8 datatypes
                    jcell = new jxl.write.Number(cCell.getColumnIndex(), cCell.getRowIndex(), value, jformat);
                } else if (elem instanceof Boolean) {
                    jcell = new jxl.write.Boolean(cCell.getColumnIndex(), cCell.getRowIndex(), (Boolean) elem, jformat);
                } else if (elem instanceof Date) {
                    jcell = new jxl.write.DateTime(cCell.getColumnIndex(), cCell.getRowIndex(), (Date) elem, jformat);
                } else if (elem instanceof IFormula) {
                    IFormula f = ((IFormula) elem);
                    try {
                        FormulaParser parser = new FormulaParser(f.toRawFormula(), null, null, ws);
                        parser.parse();
                        jcell = new jxl.write.Formula(cCell.getColumnIndex(), cCell.getRowIndex(), f.toRawFormula(), jformat);
                    } catch (Exception e) {
                        jcell = new jxl.write.Label(cCell.getColumnIndex(), cCell.getRowIndex(), "Error in Formula: " + f.toRawFormula() + " | " + e, jformat);
                    }
                } else {
                    jcell = new jxl.write.Label(cCell.getColumnIndex(), cCell.getRowIndex(), elem.toString(), jformat);
                }
                jsheet.addCell(jcell);
            }
        }
        workbook.write();
        workbook.close();
        return file;
    } catch (WriteException | IOException ex) {
        throw new RuntimeException("Exception during document creation", ex);
    }
}

Example 4 with CSheet

use of eu.ggnet.lucidcalc.CSheet in project dwoss by gg-net.

the class ImageIdHandlerOperation method exportMissing.

/**
 * Returns a FileJacket of XLS, which contains all Products with missing ImageIds.
 * <p/>
 * Acitve Filters are:
 * <ul>
 * <li>If SalesChannel is supplied, only Products, which have units in the SalesChannel.</li>
 * <li>Only Products, which don't have an image id.</li>
 * <li>Only Products, which have Unit in stock.</li>
 * </ul>
 * <p/>
 * @param salesChannel
 * @return
 */
@Override
public FileJacket exportMissing(SalesChannel salesChannel) {
    SubMonitor m = monitorFactory.newSubMonitor("Fehlende Image Ids exportieren", 100);
    m.message("Loading Missing IDs");
    m.start();
    List<Integer> uniqueUnitIds = new StockUnitEao(stockEm).findByNoLogicTransactionAsUniqueUnitId();
    List<UniqueUnit> uniqueUnits = new UniqueUnitEao(uuEm).findByIds(uniqueUnitIds);
    SortedSet<Product> products = new TreeSet<>();
    for (UniqueUnit uniqueUnit : uniqueUnits) {
        if (uniqueUnit.getProduct() == null)
            continue;
        if (salesChannel != null && uniqueUnit.getSalesChannel() != salesChannel)
            continue;
        if (uniqueUnit.getProduct().getImageId() > 0)
            continue;
        products.add(uniqueUnit.getProduct());
    }
    STable table = new STable();
    table.setHeadlineFormat(new CFormat(BOLD, BLACK, LIGHT_GRAY, CENTER, MIDDLE));
    table.add(new STableColumn("ArtikelNr", 15).setAction(SUtil.getBeanProperty("partNo")));
    table.add(new STableColumn("Warengruppe", 22).setAction(SUtil.getBeanProperty("group.name")));
    table.add(new STableColumn("Hersteller", 16).setAction(SUtil.getBeanProperty("tradeName.name")));
    table.add(new STableColumn("Bezeichnung", 45).setAction(SUtil.getBeanProperty("name")));
    table.add(new STableColumn("Image Id", 10).setAction(SUtil.getNull()));
    table.setModel(new STableModelList(products));
    CCalcDocument doc = new TempCalcDocument("ImageIds");
    doc.add(new CSheet("ImageIds", table));
    FileJacket fj = new FileJacket("MissingImageIds", ".xls", LucidCalc.createWriter(LucidCalc.Backend.XLS).write(doc));
    m.finish();
    return fj;
}

Example 5 with CSheet

use of eu.ggnet.lucidcalc.CSheet in project dwoss by gg-net.

the class TableToExcelExporter method export.

/**
 * Export a JTable view to a temporary .xls file.
 * Supported cell renderer:<ol>
 * </ol>
 * <p/>
 * @param table
 * @param fileName
 * @return
 * @throws de.dw.util.UserInfoException
 */
public static FileJacket export(JTable table, String fileName) throws UserInfoException {
    int visibleRowCount = table.getRowCount();
    if (visibleRowCount < 1)
        throw new UserInfoException("Die Tabelle enthält keine Daten.");
    int columnCount = table.getColumnCount();
    TableModel model = table.getModel();
    List<Object[]> rows = new ArrayList<>();
    for (int i = 0; i < visibleRowCount; i++) {
        Object[] row = new Object[columnCount + 1];
        for (int j = 0; j < columnCount; j++) {
            // convert value of table to specified value
            Object value = model.getValueAt(table.convertRowIndexToModel(i), table.convertColumnIndexToModel(j));
            if (value instanceof Date) {
                row[j] = SimpleDateFormat.getDateInstance(SimpleDateFormat.SHORT, Locale.GERMANY).format(value);
                continue;
            }
            // check for enums with getNote() or getName() via PojoUtil reflection
            if (value instanceof Enum) {
                try {
                    row[j] = PojoUtil.getValue("note", value);
                    continue;
                } catch (RuntimeException e) {
                    try {
                        row[j] = PojoUtil.getValue("name", value);
                        continue;
                    } catch (RuntimeException ex) {
                    }
                }
            }
            row[j] = value;
        }
        rows.add(row);
    }
    STable sTable = new STable();
    for (int i = 0; i < columnCount; i++) {
        int width = table.getColumnModel().getColumn(i).getWidth();
        sTable.add(new STableColumn(table.getColumnName(i), width / 5));
    }
    sTable.setModel(new STableModelList(rows));
    CCalcDocument cdoc = new TempCalcDocument(fileName);
    cdoc.add(new CSheet("Sheet1", sTable));
    File file = LucidCalc.createWriter(LucidCalc.Backend.XLS).write(cdoc);
    return new FileJacket(fileName, ".xls", file);
}