Example usage for org.apache.poi.xssf.usermodel XSSFRow getCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public XSSFCell getCell(int cellnum)

Source Link

Document

Returns the cell at the given (0 based) index, with the org.apache.poi.ss.usermodel.Row.MissingCellPolicy from the parent Workbook.

Usage

From source file:com.viettel.hqmc.DAO.FilesDAO.java

/**
 * load mainly target tu excel//w ww.j av a 2s .c o  m
 *
 * @return
 */
public String loadMainlyTargetExcel() throws FileNotFoundException, IOException {
    // mainly target
    Category item = new Category();
    String linkFile = getRequest().getParameter("path");
    String fileError = "";
    List customInfo = new ArrayList();
    InputStream myxls = new FileInputStream(linkFile);//get file excel
    XSSFWorkbook wb = new XSSFWorkbook(myxls);
    List<MainlyTarget> lstMainlyTarget = null;
    try {
        XSSFSheet sheet2 = wb.getSheetAt(2);
        if (sheet2 == null) {
            fileError += "Khng tm thy Sheet Ch tiu cht lng ch yu, ";
        } else {
            String sheetName = sheet2.getSheetName();
            if (!"Chi_tieu_chat_luong_chu_yeu".equals(sheetName)) {
                fileError += "Sai tn Sheet Ch tiu cht lng ch yu, ";
            }
        }

        XSSFRow row;
        int rowNums2 = sheet2.getLastRowNum();
        lstMainlyTarget = new ArrayList<MainlyTarget>();
        CategoryDAOHE cdhed = new CategoryDAOHE();

        for (int i = 2; i < rowNums2; i++) {
            row = sheet2.getRow(i);
            if (row.getCell((short) 1) != null && row.getCell((short) 1).toString().trim() != "") {
                MainlyTarget temp = new MainlyTarget();
                XSSFCell targetName = row.getCell((short) 1);
                XSSFCell unitId = row.getCell((short) 2);
                XSSFCell publishLevel = row.getCell((short) 3);
                XSSFCell meetLevel = row.getCell((short) 4);
                item = cdhed.findCategoryByName("DVI", unitId.toString());
                if (item != null) {
                    temp.setMeetLevel(meetLevel.toString());
                    temp.setTargetName(targetName.toString());
                    temp.setUnitId(item.getCategoryId().toString());
                    temp.setPublishLevel(publishLevel.toString());
                    lstMainlyTarget.add(temp);
                } else {
                    fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, ";
                }
            } else {
                break;
            }
        }
        fileError += "Thm mi cc ch tiu cht lng ch yu thnh cng ";
        customInfo.add(fileError);
    } catch (Exception ex) {
        fileError += "Thm mi cc ch tiu cht lng ch yu khng thnh cng ";
        customInfo.add(fileError);
        //            log.error(e.getMessage());
        LogUtil.addLog(ex);//binhnt sonar a160901
    }
    jsonDataGrid.setCustomInfo(customInfo);
    jsonDataGrid.setItems(lstMainlyTarget);
    return GRID_DATA;
}

From source file:com.viettel.hqmc.DAO.FilesDAO.java

/**
 * load product target tu excel/*from  w  ww .j  ava  2s .  c o m*/
 *
 * @return
 */
public String loadProductTargetExcel() throws FileNotFoundException, IOException {

    Category item = new Category();
    String linkFile = getRequest().getParameter("path");
    String fileError = "";
    List customInfo = new ArrayList();
    InputStream myxls = new FileInputStream(linkFile);//get file excel
    XSSFWorkbook wb = new XSSFWorkbook(myxls);
    List<ProductTarget> lstProductTarget = null;
    try {
        XSSFSheet sheet3 = wb.getSheetAt(3);
        if (sheet3 == null) {
            fileError += "Khng tm thy Sheet Ch tiu vi sinh vt, ";
        } else {
            String sheetName = sheet3.getSheetName();
            if (!"Chi_tieu_vi_sinh_vat".equals(sheetName)) {
                fileError += "Sai tn Sheet Ch tiu vi sinh vt, ";
            }
        }
        XSSFRow row;
        int rowNums3 = sheet3.getLastRowNum();
        lstProductTarget = new ArrayList<ProductTarget>();
        CategoryDAOHE cdhed = new CategoryDAOHE();
        // vi sinh vat
        for (int i = 2; i < rowNums3; i++) {
            row = sheet3.getRow(i);
            if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) {
                ProductTarget temp = new ProductTarget();
                XSSFCell targetName = row.getCell((short) 1);
                XSSFCell unitId = row.getCell((short) 2);
                XSSFCell maxLevel = row.getCell((short) 3);
                item = cdhed.findCategoryByName("DVI", unitId.toString().trim());
                if (item != null) {
                    temp.setTargetName(targetName.toString());
                    temp.setUnitId(item.getCategoryId().toString());
                    temp.setMaxLevel(maxLevel.toString());
                    temp.setTargetType(1l);
                    lstProductTarget.add(temp);
                } else {
                    fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, ";
                }
            } else {
                break;
            }

        }
        //kim loai nang

        XSSFSheet sheet4 = wb.getSheetAt(4);
        if (sheet4 == null) {
            fileError += "Khng tm thy Sheet Hm lng kim loi nng, ";
        } else {
            String sheetName2 = sheet4.getSheetName();
            if (!"Ham_luong_kim_loai_nang".equals(sheetName2)) {
                fileError += "Sai tn Sheet Hm lng kim loi nng, ";
            }
        }
        int rowNums4 = sheet4.getLastRowNum();
        for (int i = 2; i < rowNums4; i++) {
            row = sheet4.getRow(i);
            if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) {
                ProductTarget temp = new ProductTarget();
                XSSFCell targetName = row.getCell((short) 1);
                XSSFCell unitId = row.getCell((short) 2);
                XSSFCell maxLevel = row.getCell((short) 3);
                item = cdhed.findCategoryByName("DVI", unitId.toString().trim());
                if (item != null) {
                    temp.setTargetName(targetName.toString());
                    temp.setUnitId(item.getCategoryId().toString());
                    temp.setMaxLevel(maxLevel.toString());
                    temp.setTargetType(2l);
                    lstProductTarget.add(temp);
                } else {
                    fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, ";
                }
            } else {
                break;
            }
        }

        //hoa chat khong mong muon
        XSSFSheet sheet5 = wb.getSheetAt(5);
        if (sheet5 == null) {
            fileError += "Khng tm thy Sheet Hm lng ha cht khng mong mun, ";
        } else {
            String sheetName1 = sheet5.getSheetName();

            if (!"Ham_luong_hoa_chat".equals(sheetName1)) {
                fileError += "Sai tn Sheet Hm lng ha cht khng mong mun, ";
            }
        }
        int rowNums5 = sheet5.getLastRowNum();
        for (int i = 2; i < rowNums5; i++) {
            row = sheet5.getRow(i);
            if (row.getCell((short) 1) != null && !"".equals(row.getCell((short) 1).toString().trim())) {
                ProductTarget temp = new ProductTarget();
                XSSFCell targetName = row.getCell((short) 1);
                XSSFCell unitId = row.getCell((short) 2);
                XSSFCell maxLevel = row.getCell((short) 3);
                item = cdhed.findCategoryByName("DVI", unitId.toString().trim());
                if (item != null) {
                    temp.setTargetName(targetName.toString());
                    temp.setUnitId(item.getCategoryId().toString());
                    temp.setMaxLevel(maxLevel.toString());
                    temp.setTargetType(3l);
                    lstProductTarget.add(temp);
                } else {
                    fileError += "Danh mc n v " + unitId.toString() + " khng chnh xc, ";
                }
            } else {
                break;
            }
        }
        fileError += "Thm mi cc ch tiu vi sinh vt, hm lng kim loi nng, hm lng ha cht khng mong mun thnh cng ";
        customInfo.add(fileError);

    } catch (Exception ex) {
        fileError += "Thm mi cc ch tiu vi sinh vt, hm lng kim loi nng, hm lng ha cht khng mong mun khng thnh cng ";
        customInfo.add(fileError);
        //            log.error(e.getMessage());
        LogUtil.addLog(ex);//binhnt sonar a160901
    }
    jsonDataGrid.setCustomInfo(customInfo);
    jsonDataGrid.setItems(lstProductTarget);
    return GRID_DATA;
}

From source file:com.viettel.hqmc.DAO.FilesDAO.java

/**
 * load quality control plan excel/*from  w w  w . j  av  a2  s  . c o  m*/
 *
 * @return
 */
public String loadQualityControlsExcel() throws FileNotFoundException, IOException {
    // mainly target
    //        Category item = new Category();
    String linkFile = getRequest().getParameter("path");
    String fileError = "";
    List customInfo = new ArrayList();
    InputStream myxls = new FileInputStream(linkFile);//get file excel
    XSSFWorkbook wb = new XSSFWorkbook(myxls);
    List<QualityControlPlan> lstQualityControl = null;
    try {
        XSSFSheet sheet6 = wb.getSheetAt(6);
        if (sheet6 == null) {
            fileError += "Khng tm thy Sheet K hoch kim sot, ";
        } else {
            String sheetName1 = sheet6.getSheetName();

            if (!"Ke_Hoach_Kiem_soat".equals(sheetName1)) {
                fileError += "Khng tm thy Sheet K hoch kim sot, ";
            }
        }
        XSSFRow row;
        int rowNums6 = sheet6.getLastRowNum();
        lstQualityControl = new ArrayList<QualityControlPlan>();
        //            CategoryDAOHE cdhed = new CategoryDAOHE();

        for (int i = 2; i < rowNums6; i++) {
            row = sheet6.getRow(i);
            if (row.getCell((short) 1).toString().trim() != "") {
                QualityControlPlan temp = new QualityControlPlan();
                XSSFCell processDetails = row.getCell((short) 1);
                XSSFCell controlTarget = row.getCell((short) 2);
                XSSFCell technicalRegulation = row.getCell((short) 3);
                XSSFCell patternFrequence = row.getCell((short) 4);
                XSSFCell testDevice = row.getCell((short) 5);
                XSSFCell testMethod = row.getCell((short) 6);
                //                    XSSFCell noteForm = row.getCell((short) 7);
                XSSFCell note = row.getCell((short) 8);
                temp.setProductProcessDetail(processDetails.toString());
                temp.setControlTarget(controlTarget.toString());
                temp.setTechnicalRegulation(technicalRegulation.toString());
                temp.setPatternFrequence(patternFrequence.toString());
                temp.setTestDevice(testDevice.toString());
                temp.setTestMethod(testMethod.toString());
                temp.setNote(note.toString());
                temp.setNoteForm(note.toString());
                lstQualityControl.add(temp);
            } else {
                break;
            }

        }
        fileError = "Thm mi k hoch kim sot cht lng thnh cng ";
        customInfo.add(fileError);
    } catch (Exception ex) {
        fileError = "Thm mi k hoch kim sot cht lng khng thnh cng ";
        customInfo.add(fileError);
        //            log.error(e.getMessage());
        LogUtil.addLog(ex);//binhnt sonar a160901
    }

    jsonDataGrid.setCustomInfo(customInfo);
    jsonDataGrid.setItems(lstQualityControl);
    return GRID_DATA;
}

From source file:com.vsquaresystem.safedeals.amenity.AmenityService.java

public Boolean exportExcel() throws IOException {

    List<Amenity> rs = amenityDAL.findAllAmenities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Amenity Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from   www.j av a2  s  . com*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("Amenity Name");
    xssfrow.getCell(3).setCellValue("Amenity Code Id");
    //        List<Book> listBook = excelWriter.getListBook();
    String fileName = "/AmenityMasterData.xls";
    String exportPath = attachmentUtils.getAmenityExportAttachmentRootDirectory() + fileName;

    System.out.println("exportPath" + exportPath);

    int rowCount = 0;

    for (Amenity aAmenity : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aAmenity, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.vsquaresystem.safedeals.city.CityService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<City> rs = cityDAL.findAllCities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("City Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);//  w  w  w.ja  v a  2 s  . co m
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.getCell(1).setCellValue("City Id");
    xssfrow.getCell(2).setCellValue("Name");
    xssfrow.getCell(3).setCellValue("Country Id");
    xssfrow.getCell(4).setCellValue("State Id");
    xssfrow.getCell(5).setCellValue("Latitude Id");
    xssfrow.getCell(6).setCellValue("Longitude Id");
    String fileName = "/CityMasterData.xls";
    String exportPath = attachmentUtils.getCityExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;

    for (City aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.vsquaresystem.safedeals.location.LocationService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<Location> rs = locationDAL.findAllLocations();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Location Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from www  .  ja v a  2 s .  c  o  m*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Location Id");
    xssfrow.getCell(2).setCellValue("Location Name");
    xssfrow.getCell(3).setCellValue("City Id");
    String fileName = "/LocationMasterData.xls";
    String exportPath = attachmentUtils.getLocationExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;
    for (Location aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }
    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }
    return true;
}

From source file:com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working for raw market price");
    List<RawMarketPrice> rmp = rawMarketPriceDAL.findAllRawMarketPrice();
    //        logger.info("result set excel sop" + rmp);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Raw Market Price Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);//from   w  ww  .  ja  v  a 2s .  c o  m
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.createCell(7);
    xssfrow.createCell(8);
    xssfrow.createCell(9);
    xssfrow.createCell(10);
    xssfrow.createCell(11);
    xssfrow.createCell(12);
    xssfrow.createCell(13);
    xssfrow.createCell(14);
    xssfrow.createCell(15);
    xssfrow.createCell(16);
    xssfrow.createCell(17);
    xssfrow.createCell(18);
    xssfrow.createCell(19);
    xssfrow.createCell(20);
    xssfrow.createCell(21);
    xssfrow.createCell(22);
    xssfrow.createCell(23);
    xssfrow.createCell(24);
    xssfrow.createCell(25);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("City Name");
    xssfrow.getCell(3).setCellValue("Location Name");
    xssfrow.getCell(4).setCellValue("Year");
    xssfrow.getCell(5).setCellValue("Month");
    xssfrow.getCell(6).setCellValue("MP Agri Land Lowest");
    xssfrow.getCell(7).setCellValue("MP Agri Land Highest");
    xssfrow.getCell(8).setCellValue("MP Plot Lowest");
    xssfrow.getCell(9).setCellValue("MP Plot Highest");
    xssfrow.getCell(10).setCellValue("MP Residential Lowest");
    xssfrow.getCell(11).setCellValue("MP Residential Highest");
    xssfrow.getCell(12).setCellValue("MP Commercial Lowest");
    xssfrow.getCell(13).setCellValue("MP Commercial Highest");
    xssfrow.getCell(14).setCellValue("Safedeal Zone Id");
    xssfrow.getCell(15).setCellValue("Location Type Id");
    xssfrow.getCell(16).setCellValue("Location Categories");
    xssfrow.getCell(17).setCellValue("Description");
    xssfrow.getCell(18).setCellValue("Major Approach Road");
    xssfrow.getCell(19).setCellValue("Source of Water");
    xssfrow.getCell(20).setCellValue("Public Transport");
    xssfrow.getCell(21).setCellValue("Advantage");
    xssfrow.getCell(22).setCellValue("Disadvantage");
    xssfrow.getCell(23).setCellValue("Population");
    xssfrow.getCell(24).setCellValue("Migration Rate");
    xssfrow.getCell(25).setCellValue("Commercial Center");

    String fileName = "/RawMarketPriceMasterData.xls";
    String exportPath = attachmentUtils.getRawMarketPriceExportAttachmentRootDirectory() + fileName;

    logger.info("exportPath" + exportPath);

    int rowCount = 0;

    for (RawMarketPrice aRawMarketPrice : rmp) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aRawMarketPrice, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.xandrev.altafitcalendargenerator.XLSExtractor.java

public HashMap<Integer, ArrayList<TimeTrack>> importExcelSheet(String fileName) {
    HashMap<Integer, String> tmpHours = new HashMap<Integer, String>();
    HashMap<Integer, ArrayList<TimeTrack>> out = new HashMap<Integer, ArrayList<TimeTrack>>();
    init(out);//from  www.ja v  a  2s.  co m
    try {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();
        int rowIdx = 0;
        boolean started = false;
        boolean finished = false;
        while (rowIter.hasNext() && !finished) {
            XSSFRow row = (XSSFRow) rowIter.next();
            if (row != null && !started) {
                XSSFCell cell = row.getCell(0);
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    if (value == null || value.isEmpty() || !"HORA".equals(value)) {
                        rowIdx++;
                        started = true;
                        continue;
                    }
                }
                row = (XSSFRow) rowIter.next();
            }

            Iterator<Cell> cellIter = row.cellIterator();
            int cellIndex = 0;
            while (cellIter.hasNext()) {
                XSSFCell cell = (XSSFCell) cellIter.next();
                if (cell != null) {
                    String value = cell.getStringCellValue();
                    installHashMap(tmpHours, out, cellIndex, rowIdx, value);
                }
                cellIndex++;
            }
            rowIdx++;
        }

    } catch (Exception e) {
        System.out.println(e.getMessage());
    }
    return out;
}

From source file:com.xidu.framework.common.view.documenthandler.AbstractExcel2007DocumentHandler.java

License:Open Source License

/**
 * Convenient method to obtain the cell in the given sheet, row and column.
 * <p>Creates the row and the cell if they still doesn't already exist.
 * Thus, the column can be passed as an int, the method making the needed downcasts.
 * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
 * @param row thr row number/*  w  w  w .j  av a2 s.c o  m*/
 * @param col the column number
 * @return the HSSFCell
 */
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
    XSSFRow sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(row);
    }
    XSSFCell cell = sheetRow.getCell(col);
    if (cell == null) {
        cell = sheetRow.createCell(col);
    }
    return cell;
}

From source file:com.yanglb.utilitys.codegen.core.reader.impl.DdlReaderImpl.java

License:Apache License

/**
 * ?DBSheet// w  ww . j a  va  2 s  .  com
 * @throws CodeGenException 
 */
@Override
protected DdlModel onReader(XSSFSheet sheet) throws CodeGenException {
    DdlModel model = super.onReader(sheet, DdlModel.class);
    XSSFRow row = null;

    // ??
    row = sheet.getRow(1);
    model.setNameSpace(this.getCellStringValue(row.getCell(2)));
    model.setAuthor(this.getCellStringValue(row.getCell(4)));
    model.setVersion(this.getCellStringValue(row.getCell(6)));
    model.setDescription(this.getCellStringValue(row.getCell(8)));
    //      model.setIndex(this.getCellStringValue(row.getCell(8)));
    //      model.setForeign(this.getCellStringValue(row.getCell(12)));

    row = sheet.getRow(2);
    model.setName(this.getCellStringValue(row.getCell(2)));
    model.setResponsibility(this.getCellStringValue(row.getCell(4)));
    model.setRenewDate(this.getCellStringValue(row.getCell(6)));

    // ?
    model.setDetail(this.readerTable(sheet));
    return model;
}