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

Example 36 with XSSFSheet

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

the class AssociationFileUploadServiceTest method processAndValidateAssociationFile.

@Test
public void processAndValidateAssociationFile() throws Exception {
    // Create a temp file
    final File file = folder.newFile("myfile.txt");
    // Stubbing
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("test");
    when(sheetCreationService.createSheet(file.getAbsolutePath())).thenReturn(sheet);
    when(uploadSheetProcessorBuilder.buildProcessor("full")).thenReturn(uploadSheetProcessor);
    ValidationSummary validationSummary = associationFileUploadService.processAndValidateAssociationFile(file, "full");
    verify(sheetCreationService, times(1)).createSheet(Matchers.anyString());
    verify(uploadSheetProcessorBuilder, times(1)).buildProcessor("full");
    verify(uploadSheetProcessor, times(1)).readSheetRows(sheet);
}

Example 37 with XSSFSheet

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

the class AssociationFileUploadService method processAndValidateAssociationFile.

/**
     * Process uploaded file and return a list of errors
     *
     * @param file XLSX file supplied by user
     */
public ValidationSummary processAndValidateAssociationFile(File file, String validationLevel) throws FileNotFoundException, SheetProcessingException {
    ValidationSummary validationSummary = new ValidationSummary();
    Collection<RowValidationSummary> rowValidationSummaries = new ArrayList<>();
    Collection<AssociationSummary> associationSummaries = new ArrayList<>();
    Collection<AssociationUploadRow> fileRows = new ArrayList<>();
    if (file.exists()) {
        // Create sheet
        XSSFSheet sheet = null;
        try {
            // Create a sheet for reading
            sheet = sheetCreationService.createSheet(file.getAbsolutePath());
            // Process file, depending on validation level, into a generic row object
            UploadSheetProcessor uploadSheetProcessor = uploadSheetProcessorBuilder.buildProcessor(validationLevel);
            fileRows = uploadSheetProcessor.readSheetRows(sheet);
        } catch (InvalidFormatException | InvalidOperationException | IOException e) {
            getLog().error("File: " + file.getName() + " cannot be processed", e);
            file.delete();
            throw new SheetProcessingException("File: " + file.getName() + " cannot be processed", e);
        }
    } else {
        getLog().error("File: " + file.getName() + " cannot be found");
        throw new FileNotFoundException("File does not exist");
    }
    String eRelease = ensemblRestTemplateService.getRelease();
    // Error check each row
    if (!fileRows.isEmpty()) {
        // Check for missing values and syntax errors that would prevent code creating an association
        for (AssociationUploadRow row : fileRows) {
            getLog().info("Syntax checking row: " + row.getRowNumber() + " of file, " + file.getAbsolutePath());
            RowValidationSummary rowValidationSummary = createRowValidationSummary(row, eRelease);
            // Only store summary if there is an error
            if (!rowValidationSummary.getErrors().isEmpty()) {
                rowValidationSummaries.add(rowValidationSummary);
            }
        }
        if (rowValidationSummaries.isEmpty()) {
            //Proceed to carry out full checks of values
            fileRows.forEach(row -> {
                associationSummaries.add(createAssociationSummary(row, validationLevel, eRelease));
            });
        }
    } else {
        getLog().error("Parsing file failed");
    }
    validationSummary.setAssociationSummaries(associationSummaries);
    validationSummary.setRowValidationSummaries(rowValidationSummaries);
    return validationSummary;
}

Example 38 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class CreatePivotTable method main.

public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();
    //Create some data to build the pivot table on
    setCellData(sheet);
    AreaReference source = new AreaReference("A1:D4", SpreadsheetVersion.EXCEL2007);
    CellReference position = new CellReference("H5");
    // Create a pivot table on this sheet, with H5 as the top-left cell..
    // The pivot table's data source is on the same sheet in A1:D4
    XSSFPivotTable pivotTable = sheet.createPivotTable(source, position);
    //Configure the pivot table
    //Use first column as row label
    pivotTable.addRowLabel(0);
    //Sum up the second column
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1);
    //Set the third column as filter
    pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2);
    //Add filter on forth column
    pivotTable.addReportFilter(3);
    FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 39 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class TestSXSSFWorkbookWithCustomZipEntrySource method customZipEntrySource.

// write an unencrypted workbook to disk, but any temporary files are encrypted
@Test
public void customZipEntrySource() throws IOException {
    SXSSFWorkbookWithCustomZipEntrySource workbook = new SXSSFWorkbookWithCustomZipEntrySource();
    SXSSFSheet sheet1 = workbook.createSheet(sheetName);
    SXSSFRow row1 = sheet1.createRow(1);
    SXSSFCell cell1 = row1.createCell(1);
    cell1.setCellValue(cellValue);
    ByteArrayOutputStream os = new ByteArrayOutputStream(8192);
    workbook.write(os);
    workbook.close();
    workbook.dispose();
    XSSFWorkbook xwb = new XSSFWorkbook(new ByteArrayInputStream(os.toByteArray()));
    XSSFSheet xs1 = xwb.getSheetAt(0);
    assertEquals(sheetName, xs1.getSheetName());
    XSSFRow xr1 = xs1.getRow(1);
    XSSFCell xc1 = xr1.getCell(1);
    assertEquals(cellValue, xc1.getStringCellValue());
    xwb.close();
}

Example 40 with XSSFSheet

use of org.apache.poi.xssf.usermodel.XSSFSheet in project poi by apache.

the class TestXSSFImportFromXML method testMultiTable.

@Test(timeout = 60000)
public void testMultiTable() throws IOException, XPathExpressionException, SAXException {
    XSSFWorkbook wb = XSSFTestDataSamples.openSampleWorkbook("CustomXMLMappings-complex-type.xlsx");
    try {
        String cellC6 = "c6";
        String cellC7 = "c7";
        String cellC8 = "c8";
        String cellC9 = "c9";
        String testXML = "<ns1:MapInfo xmlns:ns1=\"" + NS_SPREADSHEETML + "\" SelectionNamespaces=\"\">" + "<ns1:Schema ID=\"" + cellC6 + "\" SchemaRef=\"a\" />" + "<ns1:Schema ID=\"" + cellC7 + "\" SchemaRef=\"b\" />" + "<ns1:Schema ID=\"" + cellC8 + "\" SchemaRef=\"c\" />" + "<ns1:Schema ID=\"" + cellC9 + "\" SchemaRef=\"d\" />";
        for (int i = 10; i < 10010; i++) {
            testXML += "<ns1:Schema ID=\"c" + i + "\" SchemaRef=\"d\" />";
        }
        testXML += "<ns1:Map ID=\"1\" Name=\"\" RootElement=\"\" SchemaID=\"\" ShowImportExportValidationErrors=\"\" AutoFit=\"\" Append=\"\" PreserveSortAFLayout=\"\" PreserveFormat=\"\">" + "<ns1:DataBinding DataBindingLoadMode=\"\" />" + "</ns1:Map>" + "<ns1:Map ID=\"2\" Name=\"\" RootElement=\"\" SchemaID=\"\" ShowImportExportValidationErrors=\"\" AutoFit=\"\" Append=\"\" PreserveSortAFLayout=\"\" PreserveFormat=\"\">" + "<ns1:DataBinding DataBindingLoadMode=\"\" />" + "</ns1:Map>" + "<ns1:Map ID=\"3\" Name=\"\" RootElement=\"\" SchemaID=\"\" ShowImportExportValidationErrors=\"\" AutoFit=\"\" Append=\"\" PreserveSortAFLayout=\"\" PreserveFormat=\"\">" + "<ns1:DataBinding DataBindingLoadMode=\"\" />" + "</ns1:Map>" + "</ns1:MapInfo>";
        XSSFMap map = wb.getMapInfo().getXSSFMapByName("MapInfo_mapping");
        assertNotNull(map);
        XSSFImportFromXML importer = new XSSFImportFromXML(map);
        importer.importFromXML(testXML);
        //Check for Schema element
        XSSFSheet sheet = wb.getSheetAt(1);
        assertEquals(cellC6, sheet.getRow(5).getCell(2).getStringCellValue());
        assertEquals(cellC7, sheet.getRow(6).getCell(2).getStringCellValue());
        assertEquals(cellC8, sheet.getRow(7).getCell(2).getStringCellValue());
        assertEquals(cellC9, sheet.getRow(8).getCell(2).getStringCellValue());
        assertEquals("c5001", sheet.getRow(5000).getCell(2).getStringCellValue());
    } finally {
        wb.close();
    }
}