Example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

List of usage examples for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.poifs.filesystem.*
  5. POIFSFileSystem
  6. POIFSFileSystem

Introduction

In this page you can find the example usage for org.apache.poi.poifs.filesystem POIFSFileSystem POIFSFileSystem.

Prototype


public POIFSFileSystem(InputStream stream) throws IOException

Source Link

Document

Create a POIFSFileSystem from an InputStream.

Usage

From source file:com.taobao.tddl.common.SQLPreParserTest.java

License:Open Source License

public static void main1(String[] args) throws IOException {
    String fileName = "D:/12_code/tddl/trunk/tddl/tddl-parser/test.xls";
    Workbook wb = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(fileName)));
    Sheet sheet = wb.getSheetAt(0);/*from ww w .  j a  v  a2 s. c  om*/
    for (Row row : sheet) {
        Cell cell = row.getCell(2);
        System.out.println(cell.getStringCellValue());
    }
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param fileName - Name of the excel file.
*//*from  ww  w  .  ja  v  a2 s  . c  om*/
private List readExcelFile() {

    List cellDataList = new ArrayList();
    try {

        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet hssfSheet = workBook.getSheetAt(0);
        Iterator rowIterator = hssfSheet.rowIterator();

        while (rowIterator.hasNext()) {
            HSSFRow hssfRow = (HSSFRow) rowIterator.next();
            Iterator iterator = hssfRow.cellIterator();
            List cellTempList = new ArrayList();
            while (iterator.hasNext()) {
                HSSFCell hssfCell = (HSSFCell) iterator.next();
                cellTempList.add(hssfCell);
            }
            cellDataList.add(cellTempList);
        }
    } catch (Exception e) {
        WmLog.printMessage("Can not read XLs file=" + this.fileName);
        e.printStackTrace();
    }

    //printToConsole(cellDataList);

    return cellDataList;
}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to modify data from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* @param value - value to be modified/*w  ww.j  av a 2s.c om*/
*/
public void modifyExcelCol(int sheetIndex, int rowIndex, int colIndex, String value) {
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        sheet.setForceFormulaRecalculation(true);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cell.setCellValue(value);
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workBook.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to modify data from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.   * 
* @param rowColFilter - [filtercol=value]@[modifyCol1=value,modifyCol2=value,...] 
* filterCol= filter column to identify the row[s] to be modify with a value
* modifyCol[n]= column to be modified found by filter col with a value
*//*from   w ww.  j a v a  2  s . c  o m*/
public void modifyMultiRowExcel(int sheetIndex, String rowColFilterText) {
    try {

        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        sheet.setForceFormulaRecalculation(true);
        String[] rowColFilters = rowColFilterText.split(";");
        for (String rowColFilter : rowColFilters) {
            String filter = "";
            String modcol = "";
            try {
                filter = rowColFilter.split("@")[0].replace("[", "").replace("]", "").trim();
                modcol = rowColFilter.split("@")[1].replace("[", "").replace("]", "").trim();
                if (filter != null && !filter.isEmpty()) {
                    String[] filters = filter.split("=");
                    int colIndex = this.getColumnIndex(filters[0]);
                    int rowIndex = this.getRowIndexByColumnValue(colIndex, filters[1]);
                    if (rowIndex >= 0) {
                        Row row = sheet.getRow(rowIndex);
                        String[] colModList = modcol.split(",");
                        for (String eachCol : colModList) {
                            String[] eachList = eachCol.split("=");
                            if (eachList.length > 0) {
                                int modColIndex = this.getColumnIndex(eachList[0]);
                                Cell cell = row.getCell(modColIndex);
                                cell.setCellValue(eachList[1]);
                            }
                        }
                    }
                }
            } catch (Exception e) {
                WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + " Excel Sheet Index="
                        + sheetIndex + "  Col Filter=" + filter + " Mod Col=" + modcol);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workBook.write(fileOut);
        fileOut.close();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " with Excel Row Col Filter=" + rowColFilterText);
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (Exception e) {
        WmLog.printMessage("ERROR in modifying XLs file =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " with Excel Row Col Filter=" + rowColFilterText);
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* @param colIndex - Index of col 0,1,2 etc.
* 
*//*from   ww w  .  j av a  2 s . co  m*/
private String readExcelCol(int sheetIndex, int rowIndex, int colIndex) {
    String cellContents = "";
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        cellContents = cell.getStringCellValue();

    } catch (IOException e) {
        // TODO Auto-generated catch block
        WmLog.printMessage("ERROR in reading =" + this.fileName + "Excel Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + "Excel Col Index=" + colIndex);
        e.printStackTrace();
        return null;
    }

    return (cellContents);

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* @param rowIndex - Index of row 0,1,2 etc.
* 
*///from   w w w .  ja  va2  s .  c  o m
private List<String> readExcelRow(int sheetIndex, int rowIndex) {
    String cellContents = "";
    ArrayList<String> rowVal = new ArrayList<String>();
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        Iterator<Cell> colIt = row.cellIterator();
        while (colIt.hasNext()) {
            Cell cell = colIt.next();
            cellContents = cell.getStringCellValue();
            rowVal.add(cellContents);
        }

    } catch (IOException e) {
        WmLog.printMessage("ERROR in reading Excel File=" + this.fileName + " Sheet Index=" + sheetIndex
                + " Excel Row Index=" + rowIndex + " " + e.getMessage());
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }

    return (rowVal);

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

/**
* This method is used to read the data's from an excel file.
* @param sheetIndex - Index of sheet 0,1,2 etc.
* 
*///from  w  ww  . ja va 2s.c o  m
private List<ArrayList> readExcel(int sheetIndex) {
    String cellContents = "";
    ArrayList<ArrayList> excel = new ArrayList<ArrayList>();
    ArrayList<String> rowVal = new ArrayList<String>();
    try {
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workBook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet sheet = workBook.getSheetAt(sheetIndex);
        Iterator<Row> rowIt = sheet.rowIterator();
        while (rowIt.hasNext()) {
            Row row = rowIt.next();
            Iterator<Cell> colIt = row.cellIterator();
            while (colIt.hasNext()) {
                Cell cell = colIt.next();
                cellContents = cell.getStringCellValue();
                rowVal.add(cellContents);
            }
            excel.add(rowVal);
        }

    } catch (IOException e) {
        WmLog.printMessage("ERROR in reading Excel Sheet Index=" + sheetIndex + " Excel File=" + this.fileName
                + " " + e.getMessage());
        // TODO Auto-generated catch block
        e.printStackTrace();
        return null;
    }

    return (excel);

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRowWithFormat(String sheetName, int row, String[] value, String[] format) {
    try {//from w  w w .  j  a  v a  2 s .co  m
        String formatV = "";
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
            if (format.length >= col) {
                HSSFCellStyle cellStyle = workbook.createCellStyle();
                cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(format[col]));
                //cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                //cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                cellA1.setCellStyle(cellStyle);
            }
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.testmax.util.ExcelSheet.java

License:CDDL license

public void addRow(String sheetName, int row, String[] value) {
    try {//from   w w  w .j a  v a  2 s  . co  m
        FileInputStream fileInputStream = new FileInputStream(this.fileName);
        POIFSFileSystem fsFileSystem = new POIFSFileSystem(fileInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fsFileSystem);
        HSSFSheet worksheet = workbook.getSheet(sheetName);
        if (worksheet == null) {
            worksheet = workbook.createSheet(sheetName);
        }
        // index from 0,0... cell A1 is cell(0,0)
        HSSFRow row1 = worksheet.createRow(row);

        for (int col = 0; col < value.length; col++) {
            HSSFCell cellA1 = row1.createCell(col);
            cellA1.setCellValue(value[col]);
        }
        FileOutputStream fileOut = new FileOutputStream(this.fileName);
        workbook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (FileNotFoundException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    } catch (IOException e) {
        WmLog.printMessage("ERROR in adding row XLs file =" + this.fileName + "Excel Sheet Index=" + sheetName
                + " with Excel Row =" + row + " " + e.getMessage());
        e.printStackTrace();
    }

}

From source file:com.thingtrack.xbom.parser.XBomParserApplication.java

License:Apache License

private void parseXbom(InputStream file) throws FileNotFoundException, IOException, XbomParsingException {

    //Clear previous importations
    assemblingPartNodes.clear();/*  ww w .  j av  a 2s . c  om*/

    // Load XLS file
    POIFSFileSystem fs = new POIFSFileSystem(file);
    HSSFWorkbook workbook = new HSSFWorkbook(fs);
    HSSFSheet sheet = workbook.getSheet(XBOM_SHEET_CAPTION);

    // Parsing assembling parts
    for (int i = 11; i < sheet.getLastRowNum(); i++) {

        HSSFRow row = (HSSFRow) sheet.getRow(i);

        try {
            assemblingPartNodes.add(getAssemblingPart(row));
        }
        //The assembling part parsing has ended
        catch (NoAssemblingPartException e) {
            break;
        }
    }

    if (assemblingPartNodes.size() > 2) {
        //Build tree relationship
        buildTreeRelationship(assemblingPartNodes.get(0),
                assemblingPartNodes.subList(1, assemblingPartNodes.size()));
        loadTreeTable(assemblingPartNodes.get(0));
    }

    assemblingPartNodes.size();
}