Example usage for org.apache.poi.xssf.usermodel XSSFSheet getRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet getRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. getRow

Introduction

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

Prototype

@Override
public XSSFRow getRow(int rownum)

Source Link

Document

Returns the logical row ( 0-based).

Usage

From source file:com.dao.DatabaseDao.java

public static void insertFromFile(MainForm mf, String sect, String destpath, String fname, Statement st,
        Connection c, long maxRecID, ArrayList<Long> arl) throws Exception {

    FileInputStream fin = new FileInputStream(destpath);
    XSSFWorkbook wb = new XSSFWorkbook(fin);
    XSSFSheet sheet = wb.getSheet("EmployeeInfo");
    long lastrowno = sheet.getLastRowNum();
    XSSFRow row;//w  w w.  j  a  v  a2s .c  om

    long i = 1;
    while (i <= lastrowno) {
        row = sheet.getRow((int) i);

        long id = (long) row.getCell(0).getNumericCellValue();//ID
        String name = row.getCell(1).getStringCellValue();//NAME
        long recp_no = (long) row.getCell(2).getNumericCellValue();//RECEIPT_NO
        Date edate = (Date) row.getCell(3).getDateCellValue();//ENTRY_DATE
        int subrate = (int) row.getCell(4).getNumericCellValue();//SUB_RATE
        int jan = (int) row.getCell(5).getNumericCellValue();//JAN
        int feb = (int) row.getCell(6).getNumericCellValue();//FEB
        int mar = (int) row.getCell(7).getNumericCellValue();//MAR
        int apr = (int) row.getCell(8).getNumericCellValue();//APR
        int may = (int) row.getCell(9).getNumericCellValue();//MAY
        int jun = (int) row.getCell(10).getNumericCellValue();//JUN
        int jul = (int) row.getCell(11).getNumericCellValue();//JUL
        int aug = (int) row.getCell(12).getNumericCellValue();//AUG
        int sep = (int) row.getCell(13).getNumericCellValue();//SEP
        int oct = (int) row.getCell(14).getNumericCellValue();//OCT
        int nov = (int) row.getCell(15).getNumericCellValue();//NOV
        int dec = (int) row.getCell(16).getNumericCellValue();//DECB
        long tot = (long) row.getCell(17).getNumericCellValue();//TOTAL
        String remark = row.getCell(18).getStringCellValue();//REMARK
        String sector = row.getCell(19).getStringCellValue();//SECTOR
        String sub_frm = row.getCell(20).getStringCellValue();//SUB_FROM
        String sub_to = row.getCell(21).getStringCellValue();//SUB_TO
        String place = row.getCell(22).getStringCellValue();//PLACE
        boolean isAlready = arl.contains(recp_no);

        Employee emp = new Employee();
        emp.setName(name);
        emp.setEntry_date(edate);
        emp.setSub_rate(subrate);
        emp.setJan(jan);
        emp.setFeb(feb);
        emp.setMar(mar);
        emp.setApr(apr);
        emp.setMay(may);
        emp.setJun(jun);
        emp.setJul(jul);
        emp.setAug(aug);
        emp.setSep(sep);
        emp.setOct(oct);
        emp.setNov(nov);
        emp.setDecb(dec);
        emp.setTotal(tot);
        emp.setRemark(remark);
        emp.setSector(sector);
        emp.setSub_from(sub_frm);
        emp.setSub_to(sub_to);
        emp.setPlace(place);
        if (isAlready) {
            emp.setReceipt_no(maxRecID);
            maxRecID++;
        } else {
            emp.setReceipt_no(recp_no);
        }
        EmployeeDao.save(emp);

        i++;
    }

    st = c.createStatement();
    st.execute("INSERT INTO IMPORTFILE_INFO(NAME) VALUES('" + fname + "')");

    mf.initData(sect);
    JOptionPane.showMessageDialog(null, "Database Import Successfully...!!");
}

From source file:com.denimgroup.threadfix.csv2ssl.parser.FormatParser.java

License:Mozilla Public License

public static Option<String[]> getHeadersExcel(File file) {
    try {/*from   w w w  .j  av a 2s.c o m*/
        FileInputStream fis = new FileInputStream(file);
        XSSFWorkbook wb = new XSSFWorkbook(fis);

        XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
        int totalRows = ws.getLastRowNum();

        if (totalRows == 0) {
            return Option.failure("No lines found in file " + file.getName());
        }

        XSSFRow row = ws.getRow(0);

        String[] headers = new String[row.getLastCellNum()];

        for (int index = 0; index < row.getLastCellNum(); index++) {
            XSSFCell cell = row.getCell(index);

            assert cell != null : "Got null cell at index " + index;

            headers[index] = cell.toString();
        }

        return Option.success(headers);

    } catch (IOException e) {
        e.printStackTrace();
        return Option.failure("Encountered IOException.");
    }
}

From source file:com.denimgroup.threadfix.csv2ssl.serializer.RecordToXMLSerializer.java

License:Mozilla Public License

public static String getFromExcel(XSSFWorkbook wb, String... format) {
    StringBuilder builder = getStart();

    int line = Configuration.CONFIG.shouldSkipFirstLine ? 1 : 0;

    XSSFSheet ws = wb.getSheetAt(0); // read the first sheet
    int totalColumns = ws.getRow(0).getLastCellNum();
    int totalRows = ws.getLastRowNum();
    Map<String, String> rowMap = map();

    for (; line <= totalRows; line++) { // we want <= because the index returned from ws.getLastRowNum() is valid
        XSSFRow row = ws.getRow(line);//from  w w w . ja  v  a 2s  .  c om

        for (int column = 0; column < totalColumns; column++) {
            XSSFCell cell = row.getCell(column);

            if (cell == null) {
                // cells are null if there's no data in them; this is fine.
                continue;
            }

            String value = cell.toString();

            if (format.length > column) {
                rowMap.put(format[column], value);
            } else {
                System.err.println("format wasn't long enough for column. Column length = " + totalColumns
                        + ", format was " + format.length);
            }
        }

        addRecord(builder, line, rowMap);
        rowMap.clear();
    }

    return writeEnd(builder);
}

From source file:com.docdoku.server.export.ExcelGenerator.java

License:Open Source License

public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
    File excelFile = new File("export_parts.xls");
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Parts Data");

    String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
    String[] columns = header.split(";");

    Map<Integer, String[]> data = new HashMap<>();
    String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
    data.put(1, headerFormatted);/*from  w  w w.  j  a  va  2 s. c o m*/

    Map<Integer, String[]> commentsData = new HashMap<>();
    String[] headerComments = createXLSHeaderRowComments(header, columns);
    commentsData.put(1, headerComments);

    List<String> selects = queryResult.getQuery().getSelects();
    int i = 1;
    for (QueryResultRow row : queryResult.getRows()) {
        i++;
        data.put(i, createXLSRow(selects, row, baseURL));
        commentsData.put(i, createXLSRowComments(selects, row));
    }

    //Iterate over data and write to sheet
    Set<Integer> keyset = data.keySet();
    int rownum = 0;

    for (Integer key : keyset) {

        Row row = sheet.createRow(rownum++);
        String[] objArr = data.get(key);
        int cellnum = 0;
        for (String obj : objArr) {
            Cell cell = row.createCell(cellnum++);
            cell.setCellValue(obj);
        }

        CreationHelper factory = workbook.getCreationHelper();
        Drawing drawing = sheet.createDrawingPatriarch();
        String[] commentsObjArr = commentsData.get(key);
        cellnum = 0;
        for (String commentsObj : commentsObjArr) {
            if (commentsObj.length() > 0) {
                Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);

                // When the comment box is visible, have it show in a 1x3 space
                ClientAnchor anchor = factory.createClientAnchor();
                anchor.setCol1(cell.getColumnIndex());
                anchor.setCol2(cell.getColumnIndex() + 1);
                anchor.setRow1(row.getRowNum());
                anchor.setRow2(row.getRowNum() + 1);

                Comment comment = drawing.createCellComment(anchor);
                RichTextString str = factory.createRichTextString(commentsObj);
                comment.setString(str);

                // Assign the comment to the cell
                cell.setCellComment(comment);
            }
            cellnum++;
        }
    }

    // Define header style
    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontHeightInPoints((short) 10);
    headerFont.setFontName("Courier New");
    headerFont.setItalic(true);
    headerFont.setColor(IndexedColors.WHITE.getIndex());
    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);
    headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
    headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

    // Set header style
    for (int j = 0; j < columns.length; j++) {
        Cell cell = sheet.getRow(0).getCell(j);
        cell.setCellStyle(headerStyle);

        if (cell.getCellComment() != null) {
            String comment = cell.getCellComment().getString().toString();

            if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER)
                    || comment.equals(QueryField.PART_MASTER_NUMBER)) {
                for (int k = 0; k < queryResult.getRows().size(); k++) {
                    Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j)
                            : sheet.getRow(k + 1).createCell(j);
                    grayCell.setCellStyle(headerStyle);
                }
            }
        }
    }

    try {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(excelFile);
        workbook.write(out);
        out.close();
    } catch (Exception e) {
        LOGGER.log(Level.FINEST, null, e);
    }
    return excelFile;

}

From source file:com.envision.envservice.report.template.PraiseReportExcel.java

License:Open Source License

private static XSSFRow getRow(XSSFSheet sheet, int rowNum) {
    return sheet.getRow(rowNum) == null ? sheet.createRow(rowNum) : sheet.getRow(rowNum);
}

From source file:com.esd.cs.common.XExcelSheetParser.java

License:Open Source License

public List<List<Object>> getDatasInSheet(int sheetNumber) {
    List<List<Object>> result = new ArrayList<List<Object>>();
    // sheet//w w w .j  av  a  2 s .  c o  m
    XSSFSheet sheet = workbook.getSheetAt(sheetNumber);
    // sheet
    int rowCount = sheet.getLastRowNum();
    logger.info("found excel rows count:" + rowCount);
    if (rowCount < 1) {
        return result;
    }
    // ??row
    for (int rowIndex = 0; rowIndex < rowCount; rowIndex++) {
        // 
        XSSFRow row = sheet.getRow(rowIndex);
        if (null != row) {
            List<Object> rowData = new ArrayList<Object>();
            // ?
            int cellCount = row.getLastCellNum();
            // ??cell
            for (short cellIndex = 0; cellIndex < cellCount; cellIndex++) {
                XSSFCell cell = row.getCell(cellIndex);
                // ??
                Object cellStr = this.getCellString(cell);

                rowData.add(cellStr);
            }
            result.add(rowData);
        }
    }

    return result;
}

From source file:com.excel.javafx.frames.MainFrame.java

private void sourceColumnSelector() {
    FileInputStream sourceFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {//from www  .j av a 2s .c o m
        sourceFile1 = new FileInputStream(sourceFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(sourceFile1);
        XSSFSheet sheet = workbook1.getSheet(sourceSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        sourceColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            sourceFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

private void destinationColumnSelector() {
    FileInputStream destFile1 = null;
    final DefaultListModel model = new DefaultListModel();

    try {/*from   w w  w  . ja v  a  2s .c  o m*/
        destFile1 = new FileInputStream(destFileName);
        XSSFWorkbook workbook1 = new XSSFWorkbook(destFile1);
        XSSFSheet sheet = workbook1.getSheet(destSheetSelector.getSelectedItem().toString());
        int columncount = sheet.getRow(0).getLastCellNum();

        for (int columnno = 0; columnno < columncount; columnno++) {
            model.addElement(sheet.getRow(0).getCell(columnno).toString());
        }
        destColumnList.setModel(model);

    } catch (FileNotFoundException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        try {
            destFile1.close();
        } catch (IOException ex) {
            Logger.getLogger(MainFrame.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

From source file:com.excel.javafx.frames.MainFrame.java

public boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
    sourceRow = new ArrayList<String>();
    destRow = new ArrayList<String>();
    int firstRow1 = sheet1.getFirstRowNum() + 1;
    int lastRow1 = sheet1.getLastRowNum();
    boolean equalSheets = true;
    for (int i = firstRow1; i <= lastRow1; i++) {

        System.out.println("\n\nComparing Row " + i);

        XSSFRow row1 = sheet1.getRow(i);
        XSSFRow row2 = sheet2.getRow(i);
        if (!compareTwoRows(row1, row2)) {
            equalSheets = false;/*from ww w .  ja va 2 s. c o m*/
            System.out.println("Row " + i + " - Not Equal");
            sourceRow.add("Row" + i);
            destRow.add("Row" + i);
        } else {
            System.out.println("Row " + i + " - Equal");
        }
    }
    return equalSheets;
}

From source file:com.FuntionLibrary.java

public static String[] getData(int r, XSSFSheet sheet) {
    String[] str = new String[6];
    XSSFRow row = sheet.getRow(r);
    int i = 0;/*from   w ww .ja v a 2 s .  c o  m*/
    for (Cell cell : row) {
        str[i] = cell.getRichStringCellValue().getString().trim();
        i++;
        if (i == 6)
            break;
    }
    return str;
}