Examples with XSSFRow org.apache.poi.xssf.usermodel.XSSFRow used on opensource projects

Example 26 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project kindergarten by clear-group-ausbildung.

the class ExportReceipt method createVendorHeaderRow.

private int createVendorHeaderRow(int pRowCount, int pColIndex, Integer pVendorNumber) {
    XSSFRow vendorRow = sheet.createRow(pRowCount);
    XSSFCell vendorNumberCell = vendorRow.createCell(pColIndex);
    vendorNumberCell.setCellValue("Verk" + "\u00E4" + "ufer Nummer: " + pVendorNumber);
    vendorNumberCell.setCellStyle(vendorHeaderStyle);
    sheet.addMergedRegion(new CellRangeAddress(pRowCount, pRowCount, vendorNumberCell.getColumnIndex(), vendorNumberCell.getColumnIndex() + 1));
    return ++pRowCount;
}

Example 27 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project kindergarten by clear-group-ausbildung.

the class ExportInternalPayoff method createVendorList.

private void createVendorList(PayoffDataInternal pPayoffDataInternal, Cell pStartCell) {
    int rowCountGlobal = pStartCell.getRowIndex();
    int labelColumnIndex = pStartCell.getColumnIndex();
    int valueColumnIndex = labelColumnIndex + 1;
    for (PayoffDataInternalVendor payoffDataInternalVendor : pPayoffDataInternal.getPayoffDataInternalVendor()) {
        rowCountGlobal = createPlaceholderRow(rowCountGlobal, labelColumnIndex, "");
        XSSFRow nameRow = sheet.createRow(rowCountGlobal);
        XSSFCell nameLabelCell = nameRow.createCell(labelColumnIndex);
        nameLabelCell.setCellValue("Name");
        nameLabelCell.setCellStyle(labelStyle);
        XSSFCell nameValueCell = nameRow.createCell(valueColumnIndex);
        nameValueCell.setCellValue(payoffDataInternalVendor.getVendor().getLastName());
        nameValueCell.setCellStyle(textStyle);
        rowCountGlobal++;
        XSSFRow firstnameRow = sheet.createRow(rowCountGlobal);
        XSSFCell firstnameLabelCell = firstnameRow.createCell(labelColumnIndex);
        firstnameLabelCell.setCellValue("Vorname");
        firstnameLabelCell.setCellStyle(labelStyle);
        XSSFCell firstnameValueCell = firstnameRow.createCell(valueColumnIndex);
        firstnameValueCell.setCellValue(payoffDataInternalVendor.getVendor().getFirstName());
        firstnameValueCell.setCellStyle(textStyle);
        rowCountGlobal++;
        XSSFRow vendorNumberRow = sheet.createRow(rowCountGlobal);
        XSSFCell vendorNumberLabelCell = vendorNumberRow.createCell(labelColumnIndex);
        vendorNumberLabelCell.setCellValue("Nummer(n)");
        vendorNumberLabelCell.setCellStyle(labelStyle);
        XSSFCell vendorNumberValueCell = vendorNumberRow.createCell(valueColumnIndex);
        vendorNumberValueCell.setCellValue(payoffDataInternalVendor.getVendor().getVendorNumbers().stream().map(vendorNumber -> String.valueOf(vendorNumber.getVendorNumber())).collect(Collectors.joining(", ")));
        vendorNumberValueCell.setCellStyle(textStyle);
        rowCountGlobal++;
        XSSFRow paymentRow = sheet.createRow(rowCountGlobal);
        XSSFCell paymentLabelCell = paymentRow.createCell(labelColumnIndex);
        paymentLabelCell.setCellValue("Auszahlungsbetrag");
        paymentLabelCell.setCellStyle(labelStyle);
        XSSFCell paymentValueCell = paymentRow.createCell(valueColumnIndex);
        paymentValueCell.setCellValue(payoffDataInternalVendor.getVendorPayment());
        paymentValueCell.setCellStyle(priceStyle);
        rowCountGlobal++;
    }
}

Example 28 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project goci by EBISPOT.

the class SheetProcessorImpl method readSheetRows.

// Read and parse uploaded spreadsheet
@Override
public Collection<AssociationUploadRow> readSheetRows(XSSFSheet sheet) {
    XSSFRow headerRow = sheet.getRow(0);
    Map<Integer, UploadFileHeader> headerRowMap = createHeaderMap(headerRow);
    // Create collection to store all newly created rows
    Collection<AssociationUploadRow> associationUploadRows = new ArrayList<>();
    Integer lastRow = sheet.getLastRowNum();
    Integer rowNum = 1;
    while (rowNum <= lastRow) {
        AssociationUploadRow associationUploadRow = new AssociationUploadRow();
        // Set row number so its consistent with numbering curator will see via Excel
        associationUploadRow.setRowNumber(rowNum + 1);
        XSSFRow row = sheet.getRow(rowNum);
        // If the row contains defined cell values
        if (row.getPhysicalNumberOfCells() > 0) {
            for (Map.Entry<Integer, UploadFileHeader> heading : headerRowMap.entrySet()) {
                Integer colNum = heading.getKey();
                UploadFileHeader headerName = heading.getValue();
                XSSFCell cell = row.getCell(colNum, Row.RETURN_BLANK_AS_NULL);
                if (cell != null) {
                    try {
                        switch(headerName) {
                            case GENES:
                                associationUploadRow.setAuthorReportedGene(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case SNP:
                                associationUploadRow.setSnp(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case EFFECT_ALLELE:
                                associationUploadRow.setStrongestAllele(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case SECONDARY_EFFECT_ALLELE:
                                associationUploadRow.setEffectAllele(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case OTHER_ALLELES:
                                associationUploadRow.setOtherAllele(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case PROXY_SNP:
                                associationUploadRow.setProxy(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case EFFECT_ELEMENT_FREQUENCY_IN_CONTROLS:
                                associationUploadRow.setAssociationRiskFrequency(SheetCellProcessingService.processStringValue(cell));
                                break;
                            case INDEPENDENT_SNP_EFFECT_ALLELE_FREQUENCY_IN_CONTROLS:
                                associationUploadRow.setRiskFrequency(SheetCellProcessingService.processStringValue(cell));
                                break;
                            case PVALUE_MANTISSA:
                                associationUploadRow.setPvalueMantissa(SheetCellProcessingService.processIntValues(cell));
                                break;
                            case PVALUE_EXPONENT:
                                associationUploadRow.setPvalueExponent(SheetCellProcessingService.processIntValues(cell));
                                break;
                            case PVALUE_DESCRIPTION:
                                associationUploadRow.setPvalueDescription(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case OR:
                                associationUploadRow.setOrPerCopyNum(SheetCellProcessingService.processFloatValues(cell));
                                break;
                            case OR_RECIPROCAL:
                                associationUploadRow.setOrPerCopyRecip(SheetCellProcessingService.processFloatValues(cell));
                                break;
                            case BETA:
                                associationUploadRow.setBetaNum(SheetCellProcessingService.processFloatValues(cell));
                                break;
                            case BETA_UNIT:
                                associationUploadRow.setBetaUnit(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case BETA_DIRECTION:
                                associationUploadRow.setBetaDirection(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case RANGE:
                                associationUploadRow.setRange(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case OR_RECIPROCAL_RANGE:
                                associationUploadRow.setOrPerCopyRecipRange(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case STANDARD_ERROR:
                                associationUploadRow.setStandardError(SheetCellProcessingService.processFloatValues(cell));
                                break;
                            case DESCRIPTION:
                                associationUploadRow.setDescription(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case MULTI_SNP_HAPLOTYPE:
                                associationUploadRow.setMultiSnpHaplotype(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case SNP_INTERACTION:
                                associationUploadRow.setSnpInteraction(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case SNP_STATUS:
                                associationUploadRow.setSnpStatus(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case SNP_TYPE:
                                associationUploadRow.setSnpType(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            case EFO_TRAITS:
                                associationUploadRow.setEfoTrait(SheetCellProcessingService.processMandatoryStringValue(cell));
                                break;
                            default:
                                getLog().warn("Column with unknown heading found in file.");
                                break;
                        }
                    } catch (CellProcessingException cpe) {
                        // Add an excel error to the list of the errors.
                        ValidationError cpeValidationError = new ValidationError(headerName.toString(), cpe.getMessage(), false, "excel");
                        associationUploadRow.addCellErrorType(cpeValidationError);
                    }
                }
            }
            associationUploadRows.add(associationUploadRow);
        }
        rowNum++;
    }
    return associationUploadRows;
}

Example 29 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project goci by EBISPOT.

the class SheetCellProcessingServiceTest method setUp.

@Before
public void setUp() throws Exception {
    // Create spreadsheet for testing
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("test");
    XSSFRow row = sheet.createRow(0);
    row.createCell(0).setCellValue("this is a string");
    row.createCell(1).setCellValue(12);
    row.createCell(2).setCellValue(1.22);
    row.createCell(3);
    row.createCell(4).setCellValue("1.22");
    cellWithString = row.getCell(0);
    cellWithInteger = row.getCell(1);
    cellWithFloat = row.getCell(2);
    blankCell = row.getCell(3, row.RETURN_BLANK_AS_NULL);
    cellWithNumericString = row.getCell(4);
}

Example 30 with XSSFRow

use of org.apache.poi.xssf.usermodel.XSSFRow in project goci by EBISPOT.

the class SheetProcessorImplTest method setUp.

@Before
public void setUp() throws Exception {
    uploadSheetProcessor = new SheetProcessorImpl(translateAuthorUploadHeaders);
    // Create spreadsheet for testing
    XSSFWorkbook authorWorkbook = new XSSFWorkbook();
    authorDepositedSheet = authorWorkbook.createSheet("test");
    // Create header row
    XSSFRow row1 = authorDepositedSheet.createRow(0);
    row1.createCell(0).setCellValue("SNP ID (ideally rsID) (see below) (mandatory)");
    row1.createCell(1).setCellValue("Chr(Optional)");
    row1.createCell(2).setCellValue("Bp(Optional)");
    row1.createCell(3).setCellValue("Genome Build(Optional)");
    row1.createCell(4).setCellValue("Effect Allele(Optional)");
    row1.createCell(5).setCellValue("Other Alleles(Optional)");
    row1.createCell(6).setCellValue("Effect Allele Frequency in Controls(Optional)");
    row1.createCell(7).setCellValue("p-value mantissa(Mandatory)");
    row1.createCell(8).setCellValue("p-value exponent(Mandatory)");
    row1.createCell(9).setCellValue("OR(Optional)");
    row1.createCell(10).setCellValue("Beta(Optional)");
    row1.createCell(11).setCellValue("Beta Unit(mandatory if beta is entered)");
    row1.createCell(12).setCellValue("Beta Direction(mandatory if beta is entered)");
    row1.createCell(13).setCellValue("OR/Beta SE(Optional)");
    row1.createCell(14).setCellValue("OR/Beta Range(95% confidence intervals)(Optional)");
    row1.createCell(15).setCellValue("Association Description(Optional)");
    // Create second row
    XSSFRow row2 = authorDepositedSheet.createRow(1);
    // SNP
    row2.createCell(0).setCellValue("rs123");
    row2.createCell(1);
    row2.createCell(2);
    row2.createCell(3);
    // Strongest SNP-Risk Allele
    row2.createCell(4).setCellValue("rs123-?");
    // Other Allele
    row2.createCell(5).setCellValue("rs222-T");
    // Effect allele frequency in controls
    row2.createCell(6).setCellValue("0.6");
    // P-value mantissa
    row2.createCell(7).setCellValue(1);
    // P-value exponent
    row2.createCell(8).setCellValue(-9);
    // OR
    row2.createCell(9).setCellValue(2.48);
    // Beta
    row2.createCell(10);
    // Beta unit
    row2.createCell(11);
    // Beta direction
    row2.createCell(12);
    // Standard Error
    row2.createCell(13).setCellValue(0.01);
    // Range
    row2.createCell(14).setCellValue("[1.22-1.43]");
    // Description
    row2.createCell(15).setCellValue("This is a description");
    // Create spreadsheet for testing
    XSSFWorkbook curatorWorkbook = new XSSFWorkbook();
    curatorSheet = curatorWorkbook.createSheet("curator_test");
    // Create header row
    XSSFRow rowc1 = curatorSheet.createRow(0);
    rowc1.createCell(0).setCellValue("Gene(s)");
    rowc1.createCell(1).setCellValue("Strongest SNP-Risk Allele");
    rowc1.createCell(2).setCellValue("SNP");
    rowc1.createCell(3).setCellValue("Proxy SNP");
    rowc1.createCell(4).setCellValue("Independent SNP risk allele frequency in controls");
    rowc1.createCell(5).setCellValue("Risk element (allele, haplotype or SNPxSNP interaction) frequency in controls");
    rowc1.createCell(6).setCellValue("P-value mantissa");
    rowc1.createCell(7).setCellValue("P-value exponent");
    rowc1.createCell(8).setCellValue("P-value description");
    rowc1.createCell(9).setCellValue("OR");
    rowc1.createCell(10).setCellValue("OR reciprocal");
    rowc1.createCell(11).setCellValue("Beta");
    rowc1.createCell(12).setCellValue("Beta Unit");
    rowc1.createCell(13).setCellValue("Beta direction");
    rowc1.createCell(14).setCellValue("Range");
    rowc1.createCell(15).setCellValue("OR reciprocal range");
    rowc1.createCell(16).setCellValue("Standard Error");
    rowc1.createCell(17).setCellValue("OR/Beta description");
    rowc1.createCell(18).setCellValue("Multi-SNP Haplotype");
    rowc1.createCell(19).setCellValue("SNP:SNP interaction");
    rowc1.createCell(20).setCellValue("SNP Status");
    rowc1.createCell(21).setCellValue("SNP type (novel/known)");
    rowc1.createCell(22).setCellValue("EFO traits");
    // Create second row
    XSSFRow rowc2 = curatorSheet.createRow(1);
    rowc2.createCell(0).setCellValue("HIBCH, INPP1, STAT1, PMS1");
    rowc2.createCell(1).setCellValue("rs9845942-?");
    rowc2.createCell(2).setCellValue("rs9845942");
    rowc2.createCell(3);
    rowc2.createCell(4);
    rowc2.createCell(5);
    // P-value mantissa
    rowc2.createCell(6).setCellValue(1);
    // P-value exponent
    rowc2.createCell(7).setCellValue(-9);
    // P-value exponent
    rowc2.createCell(8).setCellValue("test");
    // OR
    rowc2.createCell(9).setCellValue(2.48);
    rowc2.createCell(10);
    // Beta
    rowc2.createCell(11);
    // Beta unit
    rowc2.createCell(12);
    // Beta direction
    rowc2.createCell(13);
    rowc2.createCell(14);
    rowc2.createCell(15);
    rowc2.createCell(16).setCellValue(0.56);
    rowc2.createCell(17);
    rowc2.createCell(18);
    rowc2.createCell(19);
    rowc2.createCell(20);
    // Range
    rowc2.createCell(21).setCellValue("novel");
    rowc2.createCell(22);
    // Translate headers
    when(translateAuthorUploadHeaders.translateToEnumValue("Chr(Optional)")).thenReturn(UploadFileHeader.CHR);
    when(translateAuthorUploadHeaders.translateToEnumValue("Bp(Optional)")).thenReturn(UploadFileHeader.BP);
    when(translateAuthorUploadHeaders.translateToEnumValue("Genome Build(Optional)")).thenReturn(UploadFileHeader.GENOME_BUILD);
    when(translateAuthorUploadHeaders.translateToEnumValue("Other Alleles(Optional)")).thenReturn(UploadFileHeader.OTHER_ALLELES);
    when(translateAuthorUploadHeaders.translateToEnumValue("Gene(s)")).thenReturn(UploadFileHeader.GENES);
    when(translateAuthorUploadHeaders.translateToEnumValue("Effect Allele(Optional)")).thenReturn(UploadFileHeader.EFFECT_ALLELE);
    when(translateAuthorUploadHeaders.translateToEnumValue("Strongest SNP-Risk Allele")).thenReturn(UploadFileHeader.EFFECT_ALLELE);
    when(translateAuthorUploadHeaders.translateToEnumValue("SNP ID (ideally rsID) (see below) (mandatory)")).thenReturn(UploadFileHeader.SNP);
    when(translateAuthorUploadHeaders.translateToEnumValue("SNP")).thenReturn(UploadFileHeader.SNP);
    when(translateAuthorUploadHeaders.translateToEnumValue("Proxy SNP")).thenReturn(UploadFileHeader.PROXY_SNP);
    when(translateAuthorUploadHeaders.translateToEnumValue("Effect Allele Frequency in Controls(Optional)")).thenReturn(UploadFileHeader.EFFECT_ELEMENT_FREQUENCY_IN_CONTROLS);
    when(translateAuthorUploadHeaders.translateToEnumValue("Risk element (allele, haplotype or SNPxSNP interaction) frequency in controls")).thenReturn(UploadFileHeader.EFFECT_ELEMENT_FREQUENCY_IN_CONTROLS);
    when(translateAuthorUploadHeaders.translateToEnumValue("Independent SNP risk allele frequency in controls")).thenReturn(UploadFileHeader.INDEPENDENT_SNP_EFFECT_ALLELE_FREQUENCY_IN_CONTROLS);
    when(translateAuthorUploadHeaders.translateToEnumValue("p-value mantissa(Mandatory)")).thenReturn(UploadFileHeader.PVALUE_MANTISSA);
    when(translateAuthorUploadHeaders.translateToEnumValue("P-value mantissa")).thenReturn(UploadFileHeader.PVALUE_MANTISSA);
    when(translateAuthorUploadHeaders.translateToEnumValue("p-value exponent(Mandatory)")).thenReturn(UploadFileHeader.PVALUE_EXPONENT);
    when(translateAuthorUploadHeaders.translateToEnumValue("P-value exponent")).thenReturn(UploadFileHeader.PVALUE_EXPONENT);
    when(translateAuthorUploadHeaders.translateToEnumValue("Association Description(Optional)")).thenReturn(UploadFileHeader.PVALUE_DESCRIPTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("P-value description")).thenReturn(UploadFileHeader.PVALUE_DESCRIPTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR(Optional)")).thenReturn(UploadFileHeader.OR);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR")).thenReturn(UploadFileHeader.OR);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR reciprocal")).thenReturn(UploadFileHeader.OR_RECIPROCAL);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta(Optional)")).thenReturn(UploadFileHeader.BETA);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta")).thenReturn(UploadFileHeader.BETA);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta Unit(mandatory if beta is entered)")).thenReturn(UploadFileHeader.BETA_UNIT);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta Unit")).thenReturn(UploadFileHeader.BETA_UNIT);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta Direction(mandatory if beta is entered)")).thenReturn(UploadFileHeader.BETA_DIRECTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("Beta direction")).thenReturn(UploadFileHeader.BETA_DIRECTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR/Beta Range(95% confidence intervals)(Optional)")).thenReturn(UploadFileHeader.RANGE);
    when(translateAuthorUploadHeaders.translateToEnumValue("Range")).thenReturn(UploadFileHeader.RANGE);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR reciprocal range")).thenReturn(UploadFileHeader.OR_RECIPROCAL_RANGE);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR/Beta SE(Optional)")).thenReturn(UploadFileHeader.STANDARD_ERROR);
    when(translateAuthorUploadHeaders.translateToEnumValue("Standard Error")).thenReturn(UploadFileHeader.STANDARD_ERROR);
    when(translateAuthorUploadHeaders.translateToEnumValue("OR/Beta description")).thenReturn(UploadFileHeader.DESCRIPTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("Multi-SNP Haplotype")).thenReturn(UploadFileHeader.MULTI_SNP_HAPLOTYPE);
    when(translateAuthorUploadHeaders.translateToEnumValue("SNP:SNP interaction")).thenReturn(UploadFileHeader.SNP_INTERACTION);
    when(translateAuthorUploadHeaders.translateToEnumValue("SNP Status")).thenReturn(UploadFileHeader.SNP_STATUS);
    when(translateAuthorUploadHeaders.translateToEnumValue("SNP type (novel/known)")).thenReturn(UploadFileHeader.SNP_TYPE);
    when(translateAuthorUploadHeaders.translateToEnumValue("EFO traits")).thenReturn(UploadFileHeader.EFO_TRAITS);
}