Example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow

List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFSheet
  6. getRow

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFSheet getRow.

Prototype

@Override
public HSSFRow getRow(int rowIndex)

Source Link

Document

Returns the logical row (not physical) 0-based.

Usage

From source file:endrov.customData.ImportTable.java

License:BSD License

/**
 * Import Excel file/* w  w w. j ava2 s  . co m*/
 */
public void importExcel(String filename) throws Exception {
    rows.clear();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    //Take first sheet
    HSSFSheet sheet = wb.getSheetAt(0);

    for (int rowi = 0; sheet.getRow(rowi) != null; rowi++) {
        HSSFRow row = sheet.getRow(rowi);
        List<String> a = new LinkedList<String>();

        for (int coli = 0; row.getCell((short) coli) != null; coli++) {
            HSSFCell c = row.getCell((short) coli);
            if (c.getCellType() == HSSFCell.CELL_TYPE_STRING)
                a.add(c.getRichStringCellValue().getString());
            else if (c.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                a.add("" + c.getNumericCellValue());
        }
        rows.add(a);
    }
}

From source file:endrov.util.io.EvSpreadsheetImporter.java

License:BSD License

/**
 * Import Excel file/* w ww . j a va 2  s  .co  m*/
 */
public void importExcel(String filename) throws Exception {
    rows.clear();
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    //Take first sheet
    HSSFSheet sheet = wb.getSheetAt(0);

    int lastCapacity = 0;
    for (int rowi = 0; sheet.getRow(rowi) != null; rowi++) {
        HSSFRow row = sheet.getRow(rowi);
        ArrayList<String> a = new ArrayList<String>(lastCapacity);

        for (int coli = 0; row.getCell((short) coli) != null; coli++) {
            HSSFCell c = row.getCell((short) coli);
            if (c.getCellType() == HSSFCell.CELL_TYPE_STRING)
                a.add(c.getRichStringCellValue().getString());
            else if (c.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
                a.add("" + c.getNumericCellValue());
        }
        rows.add(a);
        lastCapacity = a.size();
    }
}

From source file:energy.usef.pbcfeeder.PbcFeeder.java

License:Apache License

private void fillColStubInputMap(HSSFSheet pbcDataSheet) {
    stubColInputMap = new HashMap<>();
    for (Cell topRowCell : pbcDataSheet.getRow(0)) {
        if (topRowCell.getStringCellValue().equals("")) {
            break;
        }//from   w  w w. java 2  s .co m
        String columnName = topRowCell.getStringCellValue();
        List<Double> colValues = new ArrayList<>();
        for (Row r : pbcDataSheet) {
            if (r.getRowNum() == 0) {
                continue;
            }
            Cell c = r.getCell(topRowCell.getColumnIndex());
            if (c != null) {
                if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                    colValues.add(c.getNumericCellValue());
                }
            }
        }
        stubColInputMap.put(columnName, colValues);
    }
}

From source file:energy.usef.pbcfeeder.PbcFeeder.java

License:Apache License

/**
 * This method fills in the map with CongestionPoint-->Lower Power Limit and the map CongestionPoint-->Upper Power Limit.
 *
 * @param pbcCongestionPointLimitsSheet the excel page with the power limits.
 *///ww w.  ja  va2s  . co  m
private void fillCongestionPointLimitsMaps(HSSFSheet pbcCongestionPointLimitsSheet) {
    congestionPointLowerLimitMap = new HashMap<>();
    congestionPointUpperLimitMap = new HashMap<>();
    for (Cell columnHeading : pbcCongestionPointLimitsSheet.getRow(0)) {
        // stop when one encounters the first empty cell.
        if (StringUtils.isBlank(columnHeading.getStringCellValue())) {
            break;
        }
        for (Row row : pbcCongestionPointLimitsSheet) {
            // skip first row.
            if (row.getRowNum() == 0) {
                continue;
            }
            String congestionPoint = row.getCell(0).getStringCellValue();
            Cell cell = row.getCell(columnHeading.getColumnIndex());
            if (LOWER_LIMIT.equals(columnHeading.getStringCellValue())) {
                congestionPointLowerLimitMap.put(congestionPoint, new BigDecimal(cell.getNumericCellValue()));
            }
            if (UPPER_LIMIT.equals(columnHeading.getStringCellValue())) {
                congestionPointUpperLimitMap.put(congestionPoint, new BigDecimal(cell.getNumericCellValue()));
            }
        }
    }
}

From source file:eu.cassandra.training.entities.Installation.java

License:Apache License

/**
 * This is the parser for the measurement file. It parses through the file and
 * creates the arrays of the active and reactive power consumptions.
 *///from w  w w.j ava2  s.  co  m
public void parseMeasurementsFile() throws IOException {

    ArrayList<Double> temp = new ArrayList<Double>();
    ArrayList<Double> temp2 = new ArrayList<Double>();

    String extension = measurementsFile.substring(measurementsFile.length() - 3, measurementsFile.length());

    switch (extension) {

    case "csv":

        boolean startFlag = true;

        File file = new File(measurementsFile);
        Scanner scanner = new Scanner(file);

        int counter = 0;

        while (scanner.hasNext()) {

            String line = scanner.nextLine();
            // System.out.println(line);

            if (startFlag) {
                if (line.split(",")[0].equalsIgnoreCase("1")) {

                    startDate = new DateTime(2012, 01, 01, 00, 00);

                } else {

                    int year = Integer.parseInt(line.split(",")[0].substring(0, 4));
                    int month = Integer.parseInt(line.split(",")[0].substring(4, 6));
                    int day = Integer.parseInt(line.split(",")[0].substring(6, 8));
                    int hour = 0;
                    int minute = 0;

                    startDate = new DateTime(year, month, day, hour, minute);

                }

                // System.out.println(startDate.toString());
                startFlag = false;
            }

            temp.add(Double.parseDouble(line.split(",")[1]));

            if (!activeOnly)
                temp2.add(Double.parseDouble(line.split(",")[2]));

            counter++;
        }

        endDate = startDate.plusMinutes(counter);

        // System.out.println(endDate.toString());

        scanner.close();
        break;

    case "xls":

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(measurementsFile));

        // Get the first sheet.
        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {

            // Set value of the first cell.
            HSSFRow row = sheet.getRow(i + 1);
            temp.add(row.getCell(1).getNumericCellValue());
            if (!activeOnly)
                temp2.add(row.getCell(2).getNumericCellValue());
        }

        break;

    }

    activePower = new double[temp.size()];

    for (int i = 0; i < temp.size(); i++)
        activePower[i] = temp.get(i);

    if (!activeOnly) {
        reactivePower = new double[temp2.size()];
        for (int i = 0; i < temp2.size(); i++)
            reactivePower[i] = temp2.get(i);
    }

}

From source file:eu.cassandra.training.utils.Utils.java

License:Apache License

/**
 * This is the parser for the measurement file. It parses through the file and
 * checks for errors. It can parse through .csv and .xls file and uses
 * different libraries for each file type.
 * /*from   ww w. j ava  2  s .  c  o  m*/
 * @param measurementsFile
 *          The file name of the measurements file.
 * @param power
 *          The type of data sets contained within the file (only active or
 *          active and reactive power)
 * @return the line of error or -1 if no error is found.
 * @throws IOException
 */
public static int parseMeasurementsFile(String measurementsFile, boolean power) throws IOException {

    int result = -1;

    String extension = measurementsFile.substring(measurementsFile.length() - 3, measurementsFile.length());

    switch (extension) {

    case "csv":

        File file = new File(measurementsFile);
        Scanner scanner = new Scanner(file);
        scanner.nextLine();
        int counter = 2;
        while (scanner.hasNext()) {

            String line = scanner.nextLine();

            String[] testString = line.split(",");

            if (power) {
                if (testString.length != 2)
                    result = counter;
                try {
                    Double.parseDouble(testString[1]);
                } catch (NumberFormatException e) {
                    result = counter;
                }
            } else {
                if (testString.length != 3)
                    result = counter;
                try {
                    Double.parseDouble(testString[1]);
                    Double.parseDouble(testString[2]);
                } catch (NumberFormatException e) {
                    result = counter;
                }

                if (result != -1)
                    break;
            }
            counter++;
        }

        scanner.close();
        System.out.println("Your csv file has been read!");
        break;

    case "xls":

        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(measurementsFile));

        // Get the first sheet.
        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int i = 0; i < sheet.getLastRowNum(); i++) {
            // Set value of the first cell.
            HSSFRow row = sheet.getRow(i + 1);

            if (power) {
                if (row.getCell(2) != null)
                    result = i + 2;
                try {
                    Double.parseDouble(row.getCell(1).toString());
                } catch (NumberFormatException e) {
                    result = i + 2;
                }
            } else {
                if (row.getCell(3) != null)
                    result = i + 2;
                try {
                    Double.parseDouble(row.getCell(1).toString());
                    Double.parseDouble(row.getCell(2).toString());
                } catch (NumberFormatException e) {
                    result = i + 2;
                }
            }

            if (result != -1)
                break;
        }

        System.out.println("Your excel file has been read!");
        break;

    }

    return result;

}

From source file:eu.squadd.timesheets.eolas.TimeTemplate.java

public String prepareTimesheet(String[] args) {
    String response = null;/*from w  w  w.  j a v  a  2 s  .c  om*/
    try {
        String[] ym = args[0].split("/");
        month = Integer.parseInt(ym[0]);
        year = Integer.parseInt(ym[1]);

        Calendar cal = Calendar.getInstance(TimeZone.getDefault());
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.MONTH, month - 1);
        int days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        monthName = cal.getDisplayName(Calendar.MONTH, Calendar.SHORT_FORMAT, Locale.ENGLISH);
        String periodName = monthName + "-" + year;
        cal.set(Calendar.DATE, 1);
        String dayOfWeek = new SimpleDateFormat("EE").format(cal.getTime());

        System.out.println("Month: " + periodName);
        System.out.println("Days in month: " + days);
        System.out.println("Month starts in: " + dayOfWeek);

        Map<String, String> bankHolidays = year == 2016 ? publicHolidays2016 : publicHolidays2017;
        Map<String, String> holidays = this.extractHolidays(args);

        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(template));
        HSSFSheet sheet = wb.getSheet("timesheet"); //getSheetAt(0);
        HSSFRow currentRow;
        SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        sheet.getRow(4).getCell(1).setCellValue(periodName);
        int row = 7;
        int startRow = 0;
        int i = 1;
        while (i <= days) {
            currentRow = sheet.getRow(row);
            if (currentRow.getRowNum() > 47)
                break;
            String day = currentRow.getCell(0).getStringCellValue();

            if (day.startsWith("Total")) {
                evaluator.evaluateFormulaCell(currentRow.getCell(2));
                evaluator.evaluateFormulaCell(currentRow.getCell(4));
                row++;
                continue;
            }

            if (startRow == 0) {
                if (dayOfWeek.equals(day.substring(0, 3))) {
                    startRow = currentRow.getRowNum();
                    System.out.println("Starting row found: " + startRow + 1);
                } else {
                    row++;
                    continue;
                }
            }
            cal.set(Calendar.DATE, i);
            String date = sdf.format(cal.getTime());
            if (!day.equals("Saturday") && !day.equals("Sunday") && bankHolidays.get(date) == null
                    && holidays.get(date) == null) {
                currentRow.getCell(1).setCellValue(date);
                currentRow.getCell(2).setCellValue(defaultHours); // regular hours
                //currentRow.getCell(3).setCellValue(defaultHours);   // overtime hours
                currentRow.getCell(4).setCellValue(defaultHours); // total hours                    
            }
            i++;
            row++;
        }
        currentRow = sheet.getRow(46);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        currentRow = sheet.getRow(47);
        evaluator.evaluateFormulaCell(currentRow.getCell(2));
        evaluator.evaluateFormulaCell(currentRow.getCell(4));
        response = outFilePath.replace("#MONTH#", periodName);
        wb.write(new FileOutputStream(response));

    } catch (IOException ex) {
        Logger.getLogger(Timesheets.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Timesheet created.");
    return response;
}

From source file:excel.FileExcel.java

public void get_daily_data(String pathfile) {
    File excelfile = new File(pathfile);
    updStockData = new ArrayList<>();
    if (excelfile.exists()) {
        try {//from w  w  w.  ja va 2  s.  c o  m
            FileInputStream fis = new FileInputStream(excelfile);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = workbook.getSheetAt(0);
            int flagfound;
            for (int i = 10; i < (sheet.getLastRowNum() - 1); i++) {
                if (!sheet.getRow(i).getCell(13).getStringCellValue().isEmpty()) {
                    if (Integer.valueOf(
                            (int) Math.round(sheet.getRow(i).getCell(18).getNumericCellValue())) != 0) { // if price 0
                        flagfound = 0;
                        for (int j = 0; j < updStockData.size(); j++) { // in case there are many incoming data for same item
                            if (updStockData.get(j).kode_barang
                                    .equals(sheet.getRow(i).getCell(13).getStringCellValue())) {
                                updStockData.get(j).jumlah_barang += Integer.valueOf(
                                        (int) Math.round(sheet.getRow(i).getCell(19).getNumericCellValue()));
                                flagfound = 1;
                                break;
                            }
                        }
                        if (flagfound == 0) { // if already found, doesn't need to create new entry
                            StockData constructor = new StockData();
                            constructor.kode_barang = sheet.getRow(i).getCell(13).getStringCellValue();
                            constructor.jumlah_barang = Integer.valueOf(
                                    (int) Math.round(sheet.getRow(i).getCell(19).getNumericCellValue()));
                            updStockData.add(constructor);
                        }
                    }
                }
            }
        } catch (Exception e) {
            System.out.println("error : " + e);
        }
    } else {
        System.out.println("error file doesn't exist");
    }
}

From source file:fft.FFT.java

License:Open Source License

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

    InputStream myxls = new FileInputStream("/Users/huangge/Documents/workspace/fft/src/BxDec99.xls");
    HSSFWorkbook wb = new HSSFWorkbook(myxls);
    HSSFSheet sheet = wb.getSheetAt(0);
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());
    Row r_for_rowCount = sheet.getRow(0);
    int lastColumn = Math.min(r_for_rowCount.getLastCellNum(), 1000);

    double[][] res = new double[lastColumn - 1][rowEnd];
    Workbook wb_out = new HSSFWorkbook(); // or new XSSFWorkbook();
    Sheet sheet_out = wb_out.createSheet();
    int count = 0;
    for (int j = 1; j < lastColumn; j++) {//make res matrix
        count = 0;/*from   w  w  w.j  a v a2 s .c  o m*/
        for (int i = 1; i <= rowEnd; i++) {
            Row r = sheet.getRow(i);
            Cell c = r.getCell(3, Row.RETURN_BLANK_AS_NULL);
            if (c == null || c.getCellType() == Cell.CELL_TYPE_BLANK) {
                break;
            } else if (c.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                res[j - 1][i - 1] = c.getNumericCellValue();
                count++;
            }
        }
    }

    int N = count;
    int nextPowTwo = 1;
    while (nextPowTwo < N) {
        nextPowTwo += nextPowTwo;
    }
    N = nextPowTwo;
    FFT fft = new FFT(N);
    double[] window = fft.getWindow();
    double[] re = new double[N];
    Arrays.fill(re, 0);
    ;
    double[] im = new double[N];

    for (int i = 0; i < re.length / 2; i++) {//initial sheet
        Row row_cre = sheet_out.createRow(i);
        for (int k = 0; k < lastColumn - 1; k++) {
            Cell cell = row_cre.createCell((short) (k));
        }
    }

    for (int j = 1; j < lastColumn; j++) {//make result sheet
        for (int i = 0; i < count; i++) {
            re[i] = res[j - 1][i];
            im[i] = 0;
        }
        beforeAfter(fft, re, im);
        for (int i = 0; i < re.length / 2; i++) {
            Row row_out = sheet_out.getRow(i);
            Cell cell = row_out.getCell((short) (j - 1));
            cell.setCellValue(Math.abs(re[i]));
        }

    }

    FileOutputStream fileOut//write file
            = new FileOutputStream("/Users/huangge/Documents/workspace/fft/src/workbook.xls");
    wb_out.write(fileOut);
    fileOut.close();

    long time = System.currentTimeMillis();
    double iter = 10;
    for (int i = 0; i < iter; i++)
        // fft.fft(re,im);
        time = System.currentTimeMillis() - time;
    System.out.println("Averaged " + (time / iter) + "ms per iteration");
}

From source file:File.XLS.ReadXLS.java

public void readXLSFile(String path, String filename, int Fcol, int Lcol, int Frow, int Lrow) {
    try {//from w ww . j a  v  a2s.  c  om
        //String upper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

        FileInputStream file = new FileInputStream(new File(path + filename + ".xls"));

        //Create Workbook instance holding reference to .xlsx file
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        HSSFSheet sheet = workbook.getSheetAt(0);
        for (int x = Frow; x < Lrow; x++) {
            Row rows = sheet.getRow(x);
            if (rows != null) {
                String key = null;
                for (int colIndex = Fcol; colIndex <= Lcol; colIndex++) {
                    Cell cell = rows.getCell(colIndex);
                    if (cell != null) {
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "\t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "\t");
                            break;
                        }
                    }
                }
            }
            System.out.println("");
        }
    } catch (Exception e) {
        e.printStackTrace();
    }

}