Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel WorkbookFactory create.

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:ie.cmrc.tabular.DatasetFactory.java

License:Apache License

/**
 * Creates a {@link TabularDataset} instance from an Excel file. The sheets in the Excel file must follow a tabular style. Each sheet must include a header as the first row. The header defines the name of each column. Each subsequent row is considered as a data record ({@link TableRow}).
 * @param file A <code>link java.io.File</code> pointing to the Excel file.
 * @return A {@link TabularDataset} object for accessing the content of the Excel file as a set of tables.
 * @throws IOException If an I/O error is occurs while reading the file 
 * @throws InvalidFormatException If the file format is not a valid Excel format
 * @throws FileNotFoundException If the file does not exist
 * @throws NullPointerException If the provided file is null
 *///from  ww w.  j  a v  a  2 s.  c  om
public static TabularDataset createFromExcel(File file)
        throws IOException, InvalidFormatException, FileNotFoundException, NullPointerException {
    if (file != null) {
        if (file.exists()) {
            if (file.isFile()) {
                Workbook wb;
                try {
                    wb = WorkbookFactory.create(file);
                    return new ExcelDataset(wb);
                } catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException ex) {
                    throw new InvalidFormatException(ex.getMessage());
                }
            } else
                throw new InvalidFormatException("'" + file.getAbsolutePath() + "' is not a file.");
        } else {
            throw new FileNotFoundException("Directory '" + file.getAbsolutePath() + "' does not exist.");
        }
    } else
        throw new NullPointerException("Specified file is null");
}

From source file:img.Img.java

/**
 * @param args the command line arguments
 *//* w w  w  .j  a  v  a 2s .  c  o  m*/

public static void main(String[] args) throws Exception {

    HSSFWorkbook result = new HSSFWorkbook();

    File dir = new File(UPLOAD_PATH);
    File[] directoryListing = dir.listFiles();

    Workbook wb = WorkbookFactory.create(new File(UPLOAD_PATH + "/T5UBF.XLSX"));

    Sheet sheet = wb.getSheetAt(0);
    System.out.println(sheet.getProtect());
    Row row = sheet.getRow(0);
    Cell cell = row.getCell(0);
    System.out.println(cell.getStringCellValue());

    /*if (directoryListing != null) {
        for (File child : directoryListing) {
    System.out.println("Reading from: "+child.getAbsolutePath());
    Workbook wb = WorkbookFactory.create(child);
    Sheet sheet  = wb.getSheetAt(0);
            
                
                
        }
                
    }*/
    FileOutputStream out = new FileOutputStream(new File("C:/Users/nk91008743/Desktop/result.xls"));
    result.write(out);
    out.close();
}

From source file:Import.SheetFrameController.java

private void updateListSheet() {
    // initalisation du listSheet
    ArrayList l = new ArrayList();
    l.clear();/*w w  w  .  j  a  v a 2s  . com*/

    // chargement du fichier excel
    if (modelDataSearch.getFile() != null) {
        try {
            book = WorkbookFactory.create(modelDataSearch.getFile());
            // rcupration du nombre de sheet
            int nbSheet = book.getNumberOfSheets();
            // rcupration des sheets
            for (int i = 0; i < nbSheet; i++) {
                Sheet sh = book.getSheetAt(i);
                l.add(sh.getSheetName());
            }

        } catch (FileNotFoundException ex) {
            Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex);
            this.alertException(ex.getMessage());
        } catch (IOException ex) {
            this.alertException(ex.getMessage());
            Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InvalidFormatException ex) {
            this.alertException(ex.getMessage());
            Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex);
        } catch (EncryptedDocumentException ex) {
            this.alertException(ex.getMessage());
            Logger.getLogger(SheetFrameController.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    ObservableList<String> ol = FXCollections.observableArrayList(l);

    listSheet.setItems(ol);
}

From source file:info.informationsea.java.excel2csv.Converter.java

License:Open Source License

private void doConvertAllSheets(List<File> inputFiles, File outputFile) throws Exception {
    Workbook workbook;/* www.  j av a  2s  .c o  m*/

    if (outputFile.isFile() && outputFile.length() > 512) {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
        case FILETYPE_XLSX:
            workbook = WorkbookFactory.create(outputFile);
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    } else {
        switch (Utilities.suggestFileTypeFromName(outputFile.getName())) {
        case FILETYPE_XLS:
            workbook = new HSSFWorkbook();
            break;
        case FILETYPE_XLSX:
            if (largeExcelMode)
                workbook = new SXSSFWorkbook();
            else
                workbook = new XSSFWorkbook();
            break;
        default:
            throw new IllegalArgumentException("Output file format should be Excel format");
        }
    }

    if (largeExcelMode && !(workbook instanceof SXSSFWorkbook)) {
        log.warn("Streaming output mode is disabled");
    }
    //log.info("workbook: {}", workbook.getClass());

    for (File oneInput : inputFiles) {
        switch (Utilities.suggestFileTypeFromName(oneInput.getName())) {
        case FILETYPE_XLSX:
        case FILETYPE_XLS: {
            Workbook inputWorkbook = WorkbookFactory.create(oneInput);
            int sheetNum = inputWorkbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                try (TableReader reader = new ExcelSheetReader(inputWorkbook.getSheetAt(i))) {
                    ExcelSheetWriter sheetWriter = new ExcelSheetWriter(
                            Utilities.createUniqueNameSheetForWorkbook(workbook, inputWorkbook.getSheetName(i),
                                    overwriteSheet));
                    sheetWriter.setPrettyTable(prettyTable);
                    try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes,
                            fistCount)) {
                        Utilities.copyTable(reader, tableWriter, useHeader);
                    }
                }
            }
            break;
        }
        default: {
            try (TableReader reader = Utilities.openReader(oneInput, inputSheetIndex, inputSheetName)) {
                ExcelSheetWriter sheetWriter = new ExcelSheetWriter(Utilities
                        .createUniqueNameSheetForWorkbook(workbook, oneInput.getName(), overwriteSheet));
                sheetWriter.setPrettyTable(prettyTable);
                try (TableWriter tableWriter = new FilteredWriter(sheetWriter, convertCellTypes, fistCount)) {
                    Utilities.copyTable(reader, tableWriter, useHeader);
                }
            }
            break;
        }
        }
    }

    workbook.write(new FileOutputStream(outputFile));
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(true).build()
            .doConvert(Arrays.asList(new File(temporaryDirectory.toFile(), "CO2.csv"),
                    new File(temporaryDirectory.toFile(), "DNase.csv"),
                    new File(temporaryDirectory.toFile(), "iris.csv")), temporaryOutput.toFile());

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    for (String one : new String[] { "iris", "CO2", "DNase" }) {
        try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one + ".csv"))) {
            Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
        }/*from   www .ja v  a 2  s . c o m*/
    }
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert2() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(true).build()
            .doConvert(/* w  w  w  . j a va  2  s.c  o m*/
                    Arrays.asList(new File(temporaryDirectory.toFile(), "multisheet.xls"),
                            new File(temporaryDirectory.toFile(), "multisheet.xlsx")),
                    temporaryOutput.toFile());

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    for (String one : new String[] { "iris", "CO2", "DNase" }) {
        for (String suffix : new String[] { "", "-1" }) {
            try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one + suffix))) {
                Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
            }
        }
    }
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert3() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(true).build().doConvert(
            Collections.singletonList(new File(temporaryDirectory.toFile(), "multisheet.xls")),
            temporaryOutput.toFile());/*w  ww  .j av a 2  s.  c  o  m*/

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    for (String one : new String[] { "iris", "CO2", "DNase" }) {
        try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one))) {
            Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
        }
    }
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert4() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(false).outputSheetName("iris").build().doConvert(
            Collections.singletonList(new File(temporaryDirectory.toFile(), "multisheet.xls")),
            temporaryOutput.toFile());/* ww w . j  a  va 2  s  .  c  o m*/

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    Assert.assertEquals(1, workbook.getNumberOfSheets());
    for (String one : new String[] { "iris" }) {
        log.info("Sheet name: {}", workbook.getSheetName(0));
        try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one))) {
            Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
        }
    }
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert5() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(false).inputSheetName("DNase").outputSheetName("DNase").build().doConvert(
            Collections.singletonList(new File(temporaryDirectory.toFile(), "multisheet.xls")),
            temporaryOutput.toFile());//from   ww  w. ja  va2 s.  com

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    Assert.assertEquals(1, workbook.getNumberOfSheets());
    for (String one : new String[] { "DNase" }) {
        try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one))) {
            Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
        }
    }
}

From source file:info.informationsea.java.excel2csv.ConverterTest.java

License:Open Source License

@Test
public void testDoConvert6() throws Exception {
    Path temporaryOutput = Files.createTempFile("excel2csv", ".xlsx");
    Converter.builder().copyAllSheets(false).inputSheetIndex(2).outputSheetName("DNase").build().doConvert(
            Collections.singletonList(new File(temporaryDirectory.toFile(), "multisheet.xls")),
            temporaryOutput.toFile());/*from   ww w.jav a2 s  . c  o  m*/

    Workbook workbook = WorkbookFactory.create(temporaryOutput.toFile());
    Assert.assertEquals(1, workbook.getNumberOfSheets());
    for (String one : new String[] { "DNase" }) {
        try (TableReader reader = new ExcelSheetReader(workbook.getSheet(one))) {
            Excel2CSVTest.assertObjects(reference.get(one), reader.readAll());
        }
    }
}