Examples with CreationHelper org.apache.poi.ss.usermodel.CreationHelper used on opensource projects

Example 16 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project ma-core-public by infiniteautomation.

the class SpreadsheetEmporter method prepareSheetExport.

public void prepareSheetExport(AbstractSheetEmporter sheetEmporter) {
    if (wb == null) {
        return;
    }
    // Setup Stats For Entire Workbook
    rowsProcessed = 0;
    rowErrors = 0;
    errorMessages = new ArrayList<TranslatableMessage>();
    Sheet currentSheet = wb.createSheet(sheetEmporter.getSheetName());
    sheetEmporter.setSheet(currentSheet);
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat(this.getDateFormat()));
    sheetEmporter.setDateStyle(dateStyle);
    CellStyle percentStyle = wb.createCellStyle();
    percentStyle.setDataFormat(createHelper.createDataFormat().getFormat(DeltamationCommon.decimalFormat));
    sheetEmporter.setPercentStyle(percentStyle);
    rowNum = 0;
    int cellNum = 0;
    Cell cell;
    Row row;
    // headers
    CellStyle headerStyle = wb.createCellStyle();
    Font headerFont = wb.createFont();
    headerFont.setBold(true);
    headerStyle.setFont(headerFont);
    row = currentSheet.createRow(sheetEmporter.incrementRowNum());
    int[] columnWidths = sheetEmporter.getColumnWidths();
    for (String text : sheetEmporter.getHeaders()) {
        cell = row.createCell(cellNum);
        cell.setCellValue(text);
        currentSheet.setColumnWidth(cellNum, columnWidths[cellNum]);
        cell.setCellStyle(headerStyle);
        cellNum++;
    }
}

Example 17 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project ma-core-public by infiniteautomation.

the class SpreadsheetEmporter method doExport.

/**
 * Export machine states to the spreadsheet
 * Check for error messages afterwards by calling getErrorMessages()
 * @param monitorId set to null if dont care
 * @param machineId set to null if dont care
 */
private void doExport(AbstractSheetEmporter sheetEmporter) {
    Sheet sheet = wb.createSheet(sheetEmporter.getSheetName());
    rowNum = 0;
    int cellNum = 0;
    Cell cell;
    Row row;
    CreationHelper createHelper = wb.getCreationHelper();
    CellStyle dateStyle = wb.createCellStyle();
    dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yy hh:mm:ss"));
    CellStyle percentStyle = wb.createCellStyle();
    percentStyle.setDataFormat(createHelper.createDataFormat().getFormat(DeltamationCommon.decimalFormat));
    // headers
    CellStyle headerStyle = wb.createCellStyle();
    Font headerFont = wb.createFont();
    headerFont.setBold(true);
    headerStyle.setFont(headerFont);
    row = sheet.createRow(rowNum++);
    int[] columnWidths = sheetEmporter.getColumnWidths();
    for (String text : sheetEmporter.getHeaders()) {
        cell = row.createCell(cellNum);
        cell.setCellValue(text);
        sheet.setColumnWidth(cellNum, columnWidths[cellNum]);
        cell.setCellStyle(headerStyle);
        cellNum++;
    }
    List<List<Object>> rows = sheetEmporter.exportRows();
    CellType[] columnTypes = sheetEmporter.getColumnTypes();
    for (List<Object> rowData : rows) {
        row = sheet.createRow(rowNum++);
        cellNum = 0;
        for (Object cellData : rowData) {
            cell = row.createCell(cellNum);
            if (cellData == null) {
                // empty cell
                cellNum++;
                continue;
            }
            // Switch On Type
            switch(columnTypes[cellNum]) {
                case STRING:
                    cell.setCellValue((String) cellData);
                    break;
                case NUMERIC:
                    if (cellData instanceof Integer) {
                        cell.setCellValue((Integer) cellData);
                    } else if (cellData instanceof Long) {
                        cell.setCellValue((Long) cellData);
                    }
                    break;
                case DATE:
                    cell.setCellValue((Date) cellData);
                    cell.setCellStyle(dateStyle);
                    break;
                case PERCENT:
                    cell.setCellValue((Double) cellData);
                    cell.setCellStyle(percentStyle);
                    break;
                default:
                    throw new RuntimeException("Unknown cell data type");
            }
            // Increment It
            cellNum++;
        }
        rowsProcessed++;
    }
}

Example 18 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project Aspose.Cells-for-Java by aspose-cells.

the class ApacheAddImage method main.

public static void main(String[] args) throws Exception {
    // The path to the documents directory.
    String dataDir = Utils.getDataDir(ApacheAddImage.class);
    // create a new workbook
    // or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    // add picture data to this workbook.
    InputStream is = new FileInputStream(dataDir + "aspose.jpg");
    byte[] bytes = IOUtils.toByteArray(is);
    int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
    is.close();
    CreationHelper helper = wb.getCreationHelper();
    // create sheet
    Sheet sheet = wb.createSheet();
    // Create the drawing patriarch.  This is the top level container for all shapes.
    Drawing drawing = sheet.createDrawingPatriarch();
    // add a picture shape
    ClientAnchor anchor = helper.createClientAnchor();
    // set top-left corner of the picture,
    // subsequent call of Picture#resize() will operate relative to it
    anchor.setCol1(3);
    anchor.setRow1(2);
    Picture pict = drawing.createPicture(anchor, pictureIdx);
    // auto-size picture relative to its top-left corner
    pict.resize();
    // save workbook
    String file = dataDir + "ApacheImage.xls";
    if (wb instanceof XSSFWorkbook)
        file += "x";
    FileOutputStream fileOut = new FileOutputStream(file);
    wb.write(fileOut);
    fileOut.close();
    System.out.println("Done...");
}

Example 19 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project TranskribusCore by Transkribus.

the class TrpXlsxBuilder method ExcelTest.

private static void ExcelTest(String Path) {
    Workbook wb = new XSSFWorkbook();
    Sheet employees = wb.createSheet(WorkbookUtil.createSafeSheetName("Mitarbeiter"));
    wb.getNumberOfSheets();
    wb.getSheet("test").getLastRowNum();
    Sheet s2 = wb.createSheet(WorkbookUtil.createSafeSheetName("Schorsch"));
    CreationHelper crHelper = wb.getCreationHelper();
    Row firstRow = employees.createRow(0);
    firstRow.createCell(0).setCellValue(crHelper.createRichTextString("Vorname"));
    firstRow.createCell(1).setCellValue(crHelper.createRichTextString("Nachname"));
    firstRow.createCell(2).setCellValue(crHelper.createRichTextString("Geburtsdatum"));
    Row secondRow = employees.createRow(1);
    secondRow.createCell(0).setCellValue(crHelper.createRichTextString("Santa"));
    secondRow.createCell(1).setCellValue(crHelper.createRichTextString("Claus"));
    secondRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1823-12-23"));
    Row thirdRow = employees.createRow(2);
    thirdRow.createCell(0).setCellValue(crHelper.createRichTextString("Oster"));
    thirdRow.createCell(1).setCellValue(crHelper.createRichTextString("Hase"));
    thirdRow.createCell(2).setCellValue(crHelper.createDataFormat().getFormat("1682-01-01"));
    CellStyle formatTableHead = wb.createCellStyle();
    formatTableHead.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
    formatTableHead.setFillPattern(CellStyle.SOLID_FOREGROUND);
    Font fontTableHead = wb.createFont();
    fontTableHead.setColor(IndexedColors.WHITE.getIndex());
    formatTableHead.setFont(fontTableHead);
    firstRow.getCell(0).setCellStyle(formatTableHead);
    firstRow.getCell(1).setCellStyle(formatTableHead);
    firstRow.getCell(2).setCellStyle(formatTableHead);
    CellStyle formatGebDate = wb.createCellStyle();
    formatGebDate.setDataFormat(crHelper.createDataFormat().getFormat("dd.mm.yy"));
    secondRow.getCell(2).setCellStyle(formatGebDate);
    thirdRow.getCell(2).setCellStyle(formatGebDate);
    employees.autoSizeColumn(0);
    employees.autoSizeColumn(1);
    employees.autoSizeColumn(2);
    FileOutputStream fOut;
    try {
        fOut = new FileOutputStream(Path);
        wb.write(fOut);
        fOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

Example 20 with CreationHelper

use of org.apache.poi.ss.usermodel.CreationHelper in project TranskribusCore by Transkribus.

the class TrpXlsxBuilder method writeTagsForShapeElement.

private static void writeTagsForShapeElement(ITrpShapeType element, String context, String doc, String page, String regionID, String lineID, String wordId, Set<String> selectedTags) throws IOException {
    String textStr = element.getUnicodeText();
    CustomTagList cl = element.getCustomTagList();
    if (textStr == null || cl == null)
        throw new IOException("Element has no text or custom tag list: " + element + ", class: " + element.getClass().getName());
    /*
		 * custom tags
		 * alle attribute auslesen und schreiben
		 * wenn 1. row: attribute keys schreiben und values schreiben
		 * wenn n. row: index von key in 1. row (=0) suchen und den value dort hineinschreiben
		 * 
		 */
    for (CustomTag nonIndexedTag : cl.getNonIndexedTags()) {
        if (!nonIndexedTag.getTagName().equals("textStyle") && !nonIndexedTag.getTagName().equals("readingOrder")) {
            nonIndexedTag.getAttributesValuesMap();
        // logger.debug("nonindexed tag found " + nonIndexedTag.getTagName());
        }
    }
    for (CustomTag indexedTag : cl.getIndexedTags()) {
        if (!indexedTag.getTagName().equals("textStyle")) {
            // logger.debug("indexed tag found " + indexedTag.getTagName());
            Sheet firstSheet;
            Sheet currSheet;
            String tagname = indexedTag.getTagName();
            String overview = "Overview";
            if (!selectedTags.contains(tagname)) {
                break;
            }
            /*
				 *first Excel page is the overview -> all tags without their special tag attributes
				 */
            if (wb.getSheet(overview) != null) {
                firstSheet = wb.getSheet(overview);
            } else {
                firstSheet = wb.createSheet(WorkbookUtil.createSafeSheetName(overview));
            }
            // either find existent sheet or create new one
            if (wb.getSheet(tagname) != null) {
                currSheet = wb.getSheet(tagname);
            // logger.debug("existent sheet " + tagname);
            } else {
                currSheet = wb.createSheet(WorkbookUtil.createSafeSheetName(tagname));
            // logger.debug("new sheet " + tagname);
            }
            CreationHelper crHelper = wb.getCreationHelper();
            Map<String, Object> attributes = indexedTag.getAttributeNamesValuesMap();
            Iterator<String> attributeIterator = attributes.keySet().iterator();
            int offset = (int) attributes.get("offset");
            int length = (int) attributes.get("length");
            // logger.debug("text string " + textStr + " length " +textStr.length() + " offset " + offset + " length of substring " + length);
            String tmpTextStr = textStr.substring(offset, offset + length);
            int lastRowIdxOfFirstSheet = firstSheet.getLastRowNum();
            if (lastRowIdxOfFirstSheet == 0) {
                fillFirstOverviewRow(firstSheet);
            }
            int lastRowIdx = currSheet.getLastRowNum();
            // logger.debug("lastRowIdx " + lastRowIdx);
            if (lastRowIdx == 0) {
                fillFirstRow(currSheet, attributes, crHelper);
            }
            /*
				 * the first (overview) sheet shows all custom tags of the doc - tag attributes are stored as a list in one cell
				 */
            Row nextRowOfFirstSheet = firstSheet.createRow(++lastRowIdxOfFirstSheet);
            int idxHelper = 0;
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(tmpTextStr);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(context);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(doc);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(page);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(regionID);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(lineID);
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(wordId);
            // all attributes are s
            nextRowOfFirstSheet.createCell(idxHelper++).setCellValue(tagname + " " + attributes.toString());
            /*
				 * subsequent sheets shows all different tags on their own sheet
				 * 
				 */
            Row nextRow = currSheet.createRow(++lastRowIdx);
            int idx = 0;
            nextRow.createCell(idx++).setCellValue(tmpTextStr);
            nextRow.createCell(idx++).setCellValue(context);
            nextRow.createCell(idx++).setCellValue(doc);
            nextRow.createCell(idx++).setCellValue(page);
            nextRow.createCell(idx++).setCellValue(regionID);
            nextRow.createCell(idx++).setCellValue(lineID);
            nextRow.createCell(idx++).setCellValue(wordId);
            // for (int i = 0; i < attributes.size(); i++){
            // String attributeName = attributeIterator.next();
            // logger.debug("attributeName " + attributeName);
            // firstRow.createCell(i+idx).setCellValue(crHelper.createRichTextString(attributeName));
            // Object value = attributes.get(attributeName);
            // logger.debug("attribute value " + value);
            // nextRow.createCell(i+idx).setCellValue(crHelper.createRichTextString(String.valueOf(value)));
            // }
            /*
				 * each attribute of a custom tag is stored in a single cell
				 */
            Row row = currSheet.getRow(0);
            for (int i = 0; i < attributes.size(); i++) {
                String attributeName = attributeIterator.next();
                Object value = attributes.get(attributeName);
                for (int colIdx = 0; colIdx < row.getLastCellNum(); colIdx++) {
                    Cell cell = row.getCell(colIdx);
                    if (cell.getRichStringCellValue().getString().equals(attributeName)) {
                        nextRow.createCell(colIdx).setCellValue(crHelper.createRichTextString(String.valueOf(value)));
                        break;
                    }
                }
            }
        }
    }
}