Example usage for org.apache.poi.ss.usermodel WorkbookFactory 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:org.opentestsystem.delivery.testreg.upload.ExcelFileAppenderTest.java

License:Open Source License

@Test
public void testSuccess() throws IOException, InvalidFormatException {
    InputStream appendedStream = appender.insertAtTop("DISTRICT", createWorkbookWithNoFormatTypeString());

    Workbook workbook = WorkbookFactory.create(appendedStream);
    assertThat(workbook.getNumberOfSheets(), is(1));

    Sheet sheet = workbook.getSheet("Test");
    assertThat(sheet, is(notNullValue()));

    //FormatType Row
    assertThat(sheet.getRow(0).getLastCellNum(), is((short) 1));
    assertThat(sheet.getRow(0).getCell(0).getStringCellValue(), is("DISTRICT"));

    //Header Row/*from   w  ww  . j av a2 s  .c  o  m*/
    assertThat(sheet.getRow(1).getCell(0).getStringCellValue(), is("Column1"));
    assertThat(sheet.getRow(1).getCell(1).getStringCellValue(), is("Column2"));
    assertThat(sheet.getRow(1).getCell(2).getStringCellValue(), is("Column3"));
    assertThat(sheet.getRow(1).getCell(3).getStringCellValue(), is("Column4"));
}

---

From source file:org.opentestsystem.delivery.testreg.upload.ExcelUtils.java

License:Open Source License

/**
 * Process an excel file using ExcelWorksheetProcessor iterating through individual spreadsheets.
 *
 * @param excelFile//from  w w w.  j av  a  2  s .  c om
 *        Excel File that needs to be processed.
 * @param worksheetProcessor
 *        A Worksheet processor.
 * @throws InvalidFormatException
 *         InvalidFormatException when parsing excel files.
 * @throws IOException
 *         When reading the excel file.
 */
public void processExcelFile(final InputStream excelFile, final ExcelWorksheetProcessor worksheetProcessor)
        throws InvalidFormatException, IOException {
    final Workbook workbook = WorkbookFactory.create(excelFile);

    // Find Iterate index
    final int iterateIndex = this.iterateAllSheets ? workbook.getNumberOfSheets() : DEFAULT_SHEET_INDEX;

    for (int i = 0; i < iterateIndex; i++) {
        worksheetProcessor.process(workbook.getSheetAt(i));
    }

}

---

From source file:org.patientview.ibd.util.MedicationImporter.java

License:Open Source License

public void run(String excelFileLocation, String outputFileLocation) {
    this.outputFileLocation = outputFileLocation;

    // first check to see if the file already exists and if it does delete it so we dont append more sql
    // to what exists in it from a previous export
    if (fileExists(outputFileLocation)) {
        deleteFile(outputFileLocation);/*from   w  ww . j  a va2 s. c  o m*/
    }

    try {
        // try and read the file
        InputStream inp = new FileInputStream(excelFileLocation);

        // create a spreadsheet so we can move through it
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);

        for (Row row : sheet) {
            String medicationTypeName = null;
            String medicationName = null;
            String medicationDosages = null;

            // first row is the title of the columns
            if (row.getRowNum() > 0) {
                for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                    // we only expect 3 cols of data
                    if (cn > MAX_COLS) {
                        break;
                    }

                    Cell cell = row.getCell(cn);

                    if (cell != null) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);

                        String value = cell.getStringCellValue();

                        // check what cell it is and set data we need
                        if (cn == MEDICATION_TYPE_NAME_COL) {
                            medicationTypeName = value.trim();
                        } else if (cn == MEDICATION_NAME_COL) {
                            medicationName = value.trim();
                        } else if (cn == MEDICATION_DOSAGES_COL) {
                            medicationDosages = value.trim();
                        }

                        if (medicationTypeName != null && medicationTypeName.length() > 0
                                && medicationName != null && medicationName.length() > 0
                                && medicationDosages != null && medicationDosages.length() > 0) {
                            // first check if a medication type already exists with this name else create
                            MedicationType medicationType = medicationTypes.get(medicationTypeName);

                            if (medicationType == null) {
                                medicationType = new MedicationType();
                                medicationType.setName(medicationTypeName);
                                medicationType.setMedications(new ArrayList<Medication>());
                                medicationTypes.put(medicationTypeName, medicationType);

                                // set the id and increment for the next one
                                medicationType.setId(medicationTypeCurrentId);
                                medicationTypeCurrentId++;
                            }

                            // then create a medication object that we can assign the dosages
                            Medication medication = new Medication();
                            medication.setName(medicationName);

                            // now parse any dosages and assign to the medication
                            medication.setAllowedDosages(parseMedicationDosages(medicationDosages));

                            // set the id and increment for the next one
                            medication.setId(medicationCurrentId);
                            medicationCurrentId++;

                            // add this medication to the medication type
                            medicationType.getMedications().add(medication);
                        }
                    }
                }
            }
        }
    } catch (FileNotFoundException e) {
        System.out.println("Could not find file " + e);
    } catch (IOException e) {
        System.out.println("Could not read file " + e);
    } catch (Exception e) {
        System.out.println("Unknown error " + e);
    }

    // if the file was processed and we have any objects then build some sql statements
    if (!medicationTypes.isEmpty()) {
        for (MedicationType medicationType : medicationTypes.values()) {
            // first enter the medications and dosages
            buildMedicationSqlInsert(medicationType.getMedications());

            // then buld the sql for the medication type and map the medications to it
            buildMedicationTypeSqlInsert(medicationType);
        }
    }

    System.out.println("Import file created " + outputFileLocation);
}

---

From source file:org.patientview.radar.util.RadarPhase2ExcelDataToSqlMapper.java

License:Open Source License

public static void main(String params[]) {
    InputStream inp = null;//from  w ww  .jav  a  2s  . c  om
    try {
        /************** 1. first create the prd codes sql  ***************/
        // this is the file from radar originally called ERA_EDTA_new_PRD_codes_27042012_def for NDT_RADAR
        inp = new FileInputStream(BASE_PATH + "input/prd_codes_and_working_group.xls");
        Workbook wb = WorkbookFactory.create(inp);
        Sheet sheet = wb.getSheetAt(0);
        // each list item represents a row to insert
        List<List<String>> dataList = new ArrayList<List<String>>();

        //iterate through the rows in excel file
        for (Row row : sheet) {
            // ignore non data rows
            if (row.getRowNum() < FIRST_DATA_ROW) {
                continue;
            } else if (row.getRowNum() > LAST_DATA_ROW) {
                break;
            }

            List<String> values = new ArrayList<String>();
            // iterate through cells
            for (int cn = 0; cn < row.getLastCellNum(); cn++) {
                Cell cell = row.getCell(cn);
                // ignore non data cells
                if (cn > 23) {
                    break;
                }
                String value = "";
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                // convert x values to 1 which means true, or blank to 0 which means false
                if ((cn >= FIRST_BOOLEAN_FIELD && cn <= LAST_BOOLEAN_FIELD)) {
                    value = value.equals("x") ? "1" : "0";
                }

                values.add(value);
            }

            dataList.add(values);
        }

        StringBuilder outputText = new StringBuilder();
        StringBuilder prdSql = new StringBuilder();
        String sqlBaseInsert = "INSERT INTO rdr_prd_code(ERA_EDTA_PRD_code, ERA_EDTA_primaryRenalDiagnosisTerm, "
                + "histology, clinicalHistory, familyHistory, clinicalExam, biochemistry, immunology, "
                + "urineAnalysis, " + "imaging, geneTest, otherCriteriaAndNotes, "
                + "SNOMED_CT_conceptIdentifierForFocusConcept, " + "SNOMED_CT_fullySpecifiedName, "
                + "SNOMED_CT_expressionConstraint, majorHeading, mappingToOldPRDCode, "
                + "mappingToOldPRDTerm, ERA_EDTA_defaultSortOrder, geneticsHomeReferenceLink, "
                + "nationalCenterForBiotechnologyLink, ICD_10_code, ICD10_rubricTerm, alternativesearchTerms) "
                + "VALUES (";
        int index = 0;

        // for each row in the data list create an sql insert statement
        for (List<String> row : dataList) {
            String sqlInsert = sqlBaseInsert;
            int valueIndex = 0;
            for (String value : row) {
                value = value.replace("'", "").replace("\"", "");
                sqlInsert += "'" + value + "'" + (valueIndex != row.size() - 1 ? "," : "");
                valueIndex++;
            }
            sqlInsert += ");" + System.getProperty("line.separator");
            prdSql.append(sqlInsert);
            index++;
        }

        // append to output text - output text will eventually be written to a file
        outputText.append(prdSql + System.getProperty("line.separator"));

        /************** 2. create the working groups sql  ***************/
        Row row = sheet.getRow(1);
        List<String> workingGroups = new ArrayList<String>();
        // iterate through all working groups
        for (Cell cell : row) {
            if (cell.getColumnIndex() < FIRST_WORKING_GROUP_INDEX) {
                continue;
            } else if (cell.getColumnIndex() > LAST_WORKING_GROUP_INDEX) {
                break;
            }
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            value = value.replace("'", "\\'");
            workingGroups.add(value);
        }

        // create sql for working groups sql insert
        String workingGroupSql = "" + System.getProperty("line.separator");
        int workingGroupIndex = 0;
        for (String workingGroup : workingGroups) {
            String unitCode = workingGroup.split(" ")[0] + workingGroupIndex; // this is id, has to be unique
            workingGroupSql += "INSERT INTO unit(unitcode, name, shortName, sourceType) VALUES('" + unitCode
                    + "', '" + workingGroup + "', '','radargroup');" + System.getProperty("line.separator");
            workingGroupIndex++;
        }

        // append to output text - output text will eventually be written to a file
        outputText.append(workingGroupSql + System.getProperty("line.separator"));

        /************** 3. create the mapping table sql - this is the tricky bit!  ***************/
        List<List<String>> mappingData = new ArrayList<List<String>>();

        // for each working group collect mapping values to working group
        for (int columnIndex = FIRST_WORKING_GROUP_INDEX; columnIndex < LAST_WORKING_GROUP_INDEX; columnIndex++) {
            List<String> list = new ArrayList<String>();
            for (int rowIndex = FIRST_DATA_ROW; rowIndex <= LAST_DATA_ROW; rowIndex++) {
                Row mappingRow = sheet.getRow(rowIndex);
                Cell cell = mappingRow.getCell(columnIndex);
                String value = "0";
                if (cell != null) {
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    value = cell.getStringCellValue();
                }
                list.add(value);
            }
            mappingData.add(list);
        }

        // create list of prd ids
        List<String> prdIds = new ArrayList<String>();
        for (int i = FIRST_DATA_ROW; i <= LAST_DATA_ROW; i++) {
            Row aRow = sheet.getRow(i);
            Cell cell = aRow.getCell(0);
            cell.setCellType(Cell.CELL_TYPE_STRING);
            String value = cell.getStringCellValue();
            prdIds.add(value);
        }

        // create sql insert statements based on where working group and disease intersect
        String mappingSql = "";
        String baseSql = "INSERT INTO rdr_diagnosis_mapping(workingGroup, PRDCode, ordering) VALUES(";
        for (int i = 0; i < mappingData.size(); i++) {
            String sql = "";
            List<String> list = mappingData.get(i);
            for (int j = 0; j < list.size(); j++) {
                sql = baseSql;
                String value = list.get(j);
                if (!value.equals("0")) {
                    sql += "'" + (workingGroups.get(i).split(" ")[0] + i) + "', '" + prdIds.get(j) + "','"
                            + value + "');";
                    if (!sql.equals(baseSql)) {
                        mappingSql += sql + System.getProperty("line.separator");
                    }
                }
            }

        }

        outputText.append(mappingSql);
        // output all sql stuff to file
        FileWriter fileWriter = new FileWriter(BASE_PATH + "output/phase2Data.sql");
        BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
        bufferedWriter.write(outputText.toString());
        //Close the output stream
        bufferedWriter.close();
    } catch (Exception e) {
        //To change body of catch statement use File | Settings | File Templates.
        LOGGER.error(e.getMessage());
        LOGGER.debug(e.getMessage(), e);

    }

}

---

From source file:org.paxml.bean.excel.ReadExcelTag.java

License:Open Source License

public static void main(String[] args) throws Exception {
    File file = new File("C:\\Users\\niuxuetao\\Downloads\\Untitled spreadsheet.xls");
    Workbook wb1 = WorkbookFactory.create(file);
    Workbook wb2 = WorkbookFactory.create(file);

    ReadExcelTag tag = new ReadExcelTag();
    tag.setValue(file.getAbsolutePath());
    tag.setRange("A2:C");
    tag.setSheet("Sheet1");
    tag.afterPropertiesInjection(null);//w w  w  . j a va  2 s .com
    Object result = tag.doInvoke(null);
    System.out.println(result);
}

---

From source file:org.paxml.bean.excel.WriteExcelTag.java

License:Open Source License

public static void main(String[] args) throws Exception {
    File file = new File("C:\\Users\\niuxuetao\\Downloads\\Untitled spreadsheet.xls");
    Workbook wb1 = WorkbookFactory.create(file);
    Workbook wb2 = WorkbookFactory.create(file);

    WriteExcelTag tag = new WriteExcelTag();
    tag.setValue(file.getAbsolutePath());
    tag.setRange("A:C1");
    tag.setSheet("Sheet1");
    tag.afterPropertiesInjection(null);/*from  w w w  . j  av a  2  s .  c  om*/
    Object result = tag.doInvoke(null);
    System.out.println(result);
}

---

From source file:org.paxml.table.excel.ExcelFile.java

License:Open Source License

public ExcelFile(Object f) {

    super();//from   w w w  .j  a  v a  2 s  .c  o m

    if (f instanceof ExcelFile) {
        ExcelFile ef = (ExcelFile) f;
        workbook = ef.workbook;
        evaluator = ef.evaluator;
        file = ef.file;

    } else {
        this.file = f instanceof File ? (File) f : new File(f.toString());

        if (log.isDebugEnabled()) {
            log.debug("Opening excel file: " + file.getAbsolutePath());
        }
        try {
            if (file.exists()) {

                workbook = WorkbookFactory.create(file);

            } else {
                workbook = file.getName().toLowerCase().endsWith(".xlsx") ? new XSSFWorkbook()
                        : new HSSFWorkbook();
            }
            evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        } catch (Exception e) {
            close();
            throw new PaxmlRuntimeException("Cannot open excel file: " + file.getAbsolutePath(), e);
        }
    }
}

---

From source file:org.pentaho.reporting.engine.classic.bugs.Prd4968Test.java

License:Open Source License

@Test
public void testExcelExport() throws Exception {
    URL resource = getClass().getResource("Prd-4968.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, bout);
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
    assertEquals(34, workbook.getNumCellStyles());
    assertEquals(9, workbook.getNumberOfFonts());
}

---

From source file:org.pentaho.reporting.engine.classic.bugs.Prd4968Test.java

License:Open Source License

@Test
public void testExcel2007Export() throws Exception {
    URL resource = getClass().getResource("Prd-4968.prpt");
    ResourceManager mgr = new ResourceManager();
    MasterReport report = (MasterReport) mgr.createDirectly(resource, MasterReport.class).getResource();
    ByteArrayOutputStream bout = new ByteArrayOutputStream();
    ExcelReportUtil.createXLSX(report, bout);
    Workbook workbook = WorkbookFactory.create(new ByteArrayInputStream(bout.toByteArray()));
    assertEquals(14, workbook.getNumCellStyles());
    assertEquals(6, workbook.getNumberOfFonts());

    //    File testOutputFile = DebugReportRunner.createTestOutputFile();
    //    ExcelReportUtil.createXLSX(report, "test-output/Prd-4988.xlsx");

}

---

From source file:org.pentaho.reporting.engine.classic.core.bugs.Prd3431IT.java

License:Open Source License

public void testAsExcelOutput() throws ResourceException, ReportProcessingException, IOException, SAXException,
        ParserConfigurationException, InvalidFormatException {
    final URL url = getClass().getResource("Prd-3431.prpt");
    assertNotNull(url);/*from w  w w . jav a2s . c o  m*/
    final ResourceManager resourceManager = new ResourceManager();
    resourceManager.registerDefaults();
    final Resource directly = resourceManager.createDirectly(url, MasterReport.class);
    final MasterReport report = (MasterReport) directly.getResource();
    final MemoryByteArrayOutputStream mbos = new MemoryByteArrayOutputStream();
    ExcelReportUtil.createXLS(report, new NoCloseOutputStream(mbos));

    final ByteArrayInputStream bin = new ByteArrayInputStream(mbos.getRaw(), 0, mbos.getLength());
    final Workbook workbook = WorkbookFactory.create(bin);
    assertEquals(4, workbook.getNumberOfSheets());
    assertEquals("Summary", workbook.getSheetAt(0).getSheetName());
    assertEquals("AuthorPublisher A", workbook.getSheetAt(1).getSheetName());
    assertEquals("AuthorPublisher B", workbook.getSheetAt(2).getSheetName());
    assertEquals("AuthorPublisher C", workbook.getSheetAt(3).getSheetName());
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »