Example usage for org.apache.poi.hssf.usermodel HSSFRow getCell

List of usage examples for org.apache.poi.hssf.usermodel HSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFRow
  6. getCell

Introduction

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

Prototype

@Override
public HSSFCell getCell(int cellnum)

Source Link

Document

Get the hssfcell representing a given column (logical cell) 0-based.

Usage

From source file:com.bluecubs.xinco.index.filetypes.XincoIndexMicrosoftExcel.java

License:Apache License

public String getFileContentString(File f) {
    int i, j, j2, k;
    short k2;//from www.j a va 2s  . c o m
    HSSFWorkbook wb = null;
    HSSFSheet sheet = null;
    HSSFRow row = null;
    HSSFCell cell = null;
    InputStream is = null;
    String cell_string = "";
    try {
        is = new FileInputStream(f);
        POIFSFileSystem fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
        for (i = 0; i < wb.getNumberOfSheets(); i++) {
            sheet = wb.getSheetAt(i);
            j2 = 0;
            for (j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {
                while ((row = sheet.getRow(j2)) == null) {
                    j2++;
                }
                j2++;
                k2 = 0;
                for (k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                    while ((cell = row.getCell(k2)) == null) {
                        k2++;
                    }
                    k2++;
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_FORMULA:
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        cell_string = cell_string + cell.getNumericCellValue() + "\t";
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        cell_string = cell_string + cell.getStringCellValue() + "\t";
                        break;
                    default:
                    }

                }
                cell_string = cell_string + "\n";
            }
            cell_string = cell_string + "\n\n\n";
        }
        is.close();
    } catch (Exception fe) {
        cell_string = null;
        if (is != null) {
            try {
                is.close();
            } catch (Exception ise) {
            }
        }
    }
    return cell_string;
}

From source file:com.cablelabs.fsm.SystemSettings.java

License:Open Source License

/**
 * Reads the configuration parameters from the specified worksheet in the 
 * Xcel spreadsheet. /*from ww  w  . jav  a2s.co  m*/
 * 
 * @param arglist - the arguments of the spreadsheet to read, such as file
 *          name, sheet, and columns to read.
 * @param forRegistrar - a flag stating whether to use the hashKey as the key
 *          in the properties table or the value of the IP property
 * @param forRemoval - whether this is being added or removed from the 
 *          properties table.
 * 
 * @return - returns the Network Element Label this properties were saved under or
 * null.
 */
@SuppressWarnings("deprecation")
private Hashtable<String, Properties> readConfigParameters(String[] arglist) {

    POIFSFileSystem fs = null;
    HSSFWorkbook wb = null;

    int xlsSheetIndex = 0;
    ArrayList<Object> hashKeyList = new ArrayList<Object>();
    String hashKey = null;
    String propertyKey = null;
    String propertyValue = null;
    Hashtable<String, Properties> table = null;
    // verify execution syntax - make sure proper number of parameters are passed in
    if (arglist.length != 5) {
        logger.trace(PC2LogCategory.Settings, subCat,
                "Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n");
        //System.out.print("Syntax: configparams <xls_filename> <xls_sheetname> <hashkey_column> <propertykey_column> <propertyvalue_column>\n");
        return table;
    }
    String xlsFileName = arglist[0];
    String xlsSheetName = arglist[1];
    String xlsHashKeyColumn = arglist[2];
    String xlsPropertyKeyColumn = arglist[3];
    String xlsPropertyValueColumn = arglist[4];
    logger.trace(PC2LogCategory.Settings, subCat,
            "Excel File Name is: " + xlsFileName + "\nExcel Sheet Name is: " + xlsSheetName
                    + "\nExcel Key Column is: " + xlsHashKeyColumn + "\nExcel Field Name Column is: "
                    + xlsPropertyKeyColumn + "\nExcel Value Column is: " + xlsPropertyValueColumn);

    // use POI to read the excel file
    try {
        fs = new POIFSFileSystem(new FileInputStream(xlsFileName));
        logger.trace(PC2LogCategory.Settings, subCat, "FS= " + fs);
    } catch (IOException exception) {
        logger.trace(PC2LogCategory.Settings, subCat, "Failed to read the file named " + xlsFileName);
    }
    ;

    // read the workbook
    try {
        wb = new HSSFWorkbook(fs);
    } catch (IOException exception) {
        logger.trace(PC2LogCategory.Settings, subCat, "Failed to create a workbook");
    }
    ;

    try {
        xlsSheetIndex = wb.getSheetIndex(xlsSheetName);
        HSSFSheet sheet = wb.getSheetAt(xlsSheetIndex);
        HSSFRow row = null;
        HSSFCell cell = null;
        boolean formatKnown = false;
        table = new Hashtable<String, Properties>();
        int initialRow = 0;
        if (sheet.getRow(0) == null) //|| sheet.getRow(0).getCell(Short.decode("0")) != null)
            initialRow = 1;

        int rows = sheet.getLastRowNum();
        for (int r = initialRow; r <= rows; r++) {
            row = sheet.getRow(r);
            if (row != null) {
                if (xlsHashKeyColumn != null) {
                    cell = row.getCell(Short.decode(xlsHashKeyColumn));
                    // Support the original format of the settings files where the network element is in column 1 instead of zero
                    if (!formatKnown && cell == null && xlsHashKeyColumn.equals("0")) {
                        xlsHashKeyColumn = "1";
                        xlsPropertyKeyColumn = "3";
                        xlsPropertyValueColumn = "4";
                        cell = row.getCell(Short.decode(xlsHashKeyColumn));
                    }
                    formatKnown = true;

                } else if (!formatKnown) {
                    hashKey = SettingConstants.DUT;
                    cell = row.getCell(Short.decode("0"));
                    // Support the original format of the settings files where the network element is in column 1 instead of zero
                    cell = row.getCell(Short.decode(xlsPropertyKeyColumn));
                    propertyKey = getXlsCellStringValue(cell);
                    if (cell == null || propertyKey.startsWith("Step 1")) {
                        xlsPropertyKeyColumn = "3";
                        xlsPropertyValueColumn = "4";

                    }
                    cell = null;
                    formatKnown = true;

                }
                if (cell != null) {
                    hashKey = getXlsCellStringValue(cell);

                }
                if ((hashKey != null) && (hashKey != "")) {
                    if (!hashKeyList.contains(hashKey)) {
                        hashKeyList.add(hashKey);
                        table.put(hashKey, new Properties());
                    }
                }
                Properties p = null;
                if (hashKey != null)
                    p = table.get(hashKey);
                if (p != null) {

                    cell = row.getCell(Short.decode(xlsPropertyKeyColumn));
                    propertyKey = getXlsCellStringValue(cell);
                    cell = row.getCell(Short.decode(xlsPropertyValueColumn));
                    propertyValue = getXlsCellStringValue(cell);

                    if (propertyKey.equals("MAC Address")) {
                        p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue));
                    }
                    // PCPCSII-125  
                    // Create a colon verions of each MAC Address in the configuration files
                    if (propertyKey.contains("MAC Address")) {
                        String value = createMACAddressWithColons(propertyValue);
                        p.setProperty(propertyKey + " Colon", value);
                    }

                    // Before putting the key/value pair into the property class,
                    // (except for the LinkLocalAddress property),
                    // see if it is an IP property and if so determine if the
                    // value is an IPv6 address using the compressed form.
                    if (propertyKey.contains(SettingConstants.IP)
                            && propertyValue.contains(SettingConstants.IP_COMP_FORM)) {
                        try {
                            propertyValue = Conversion.ipv6LongForm(propertyValue);
                        } catch (IllegalArgumentException iae) {
                            logger.error(PC2LogCategory.Settings, subCat,
                                    hashKey + "- Error processing key=" + propertyKey + " value="
                                            + propertyValue + ". Detected an invalid IPv6 address.");
                        }

                    }

                    if (propertyKey != null && propertyValue != null && propertyKey != ""
                            && propertyValue != "")
                        p.setProperty(propertyKey, propertyValue);
                    cell = null;
                    logger.trace(PC2LogCategory.Settings, subCat,
                            hashKey + "- Adding key=" + propertyKey + " value=" + propertyValue);

                }
            }
        }

        for (int q = 0; q < hashKeyList.size(); q++) {

            String currentHashKey = hashKeyList.get(q).toString();
            Properties p = table.get(currentHashKey);
            addDeviceType(currentHashKey, p);
            // key = currentHashKey;
            if (currentHashKey.equals("DUT")) {
                String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE);
                if (deviceType != null) {
                    table.put(deviceType + "0", p);
                    logger.debug(PC2LogCategory.PCSim2, subCat,
                            "Adding label=" + deviceType + "0 to system settings.");

                }
            } else if (currentHashKey.startsWith("UE")) {
                String sim = p.getProperty(SettingConstants.SIMULATED);
                if (sim != null && (sim.equalsIgnoreCase("false") || sim.equalsIgnoreCase("no")
                        || sim.equalsIgnoreCase("disable"))) {
                    String pui = p.getProperty(SettingConstants.PUI);
                    String pui2 = p.getProperty(SettingConstants.PUI2);
                    if (pui != null) {
                        table.put(pui, p);
                        logger.debug(PC2LogCategory.PCSim2, subCat,
                                "Adding label=" + pui + " to system settings.");

                    }
                    if (pui2 != null && !pui2.equals(pui)) {
                        table.put(pui2, p);
                        logger.debug(PC2LogCategory.PCSim2, subCat,
                                "Adding label=" + pui2 + " to system settings.");
                    }
                }
            }
        }

        //         table = new Hashtable<String, Properties>();
        //         
        //         for (int q = 0; q < hashKeyList.size(); q++) {
        //
        //            String currentHashKey = hashKeyList.get(q).toString();
        //            logger.trace(PC2LogCategory.Settings, subCat,
        //                  "****** OK hashKey(q) = " + hashKeyList.get(q) + " ******");
        //            logger.trace(PC2LogCategory.Settings, subCat,
        //                  "Loop " + q);
        //            Properties p = new Properties();
        //            for (int r = 0; r <= rows; r++) {
        //               row = sheet.getRow(r);
        //               if (row != null) {
        //                  if (xlsHashKeyColumn != null) {
        //                     cell = row.getCell(Short.decode(xlsHashKeyColumn));
        //                  }
        //                  if (cell != null) {
        //                     hashKey = getXlsCellStringValue(cell);
        //                  }
        //                  else {
        //                     hashKey = SettingConstants.DUT;
        //                  }   
        //                  if (hashKey == hashKeyList.get(q)) {
        //                     cell = row.getCell(Short.decode(xlsPropertyKeyColumn));
        //                     propertyKey = getXlsCellStringValue(cell);
        //                     cell = row.getCell(Short.decode(xlsPropertyValueColumn));
        //                     propertyValue = getXlsCellStringValue(cell);
        //
        //                     if (propertyKey.equals("MAC Address")) {
        //                        p.setProperty("LinkLocalAddress", createLinkLocalAddress(propertyValue));
        //                     }
        //                     // PCPCSII-125  
        //                     // Create a colon verions of each MAC Address in the configuration files
        //                     if (propertyKey.contains("MAC Address")) {
        //                        String value = createMACAddressWithColons(propertyValue);
        //                        p.setProperty(propertyKey + " Colon", value);
        //                     }
        //
        //                     // Before putting the key/value pair into the property class,
        //                     // (except for the LinkLocalAddress property),
        //                     // see if it is an IP property and if so determine if the
        //                     // value is an IPv6 address using the compressed form.
        //                     if (propertyKey.contains(SettingConstants.IP) &&
        //                           propertyValue.contains(SettingConstants.IP_COMP_FORM)) {
        //                        try {
        //                           propertyValue = Conversion.ipv6LongForm(propertyValue);
        //                        }
        //                        catch (IllegalArgumentException iae) {
        //                           logger.error(PC2LogCategory.Settings, subCat, 
        //                                 currentHashKey + "- Error processing key=" + propertyKey + " value=" + propertyValue 
        //                                 + ". Detected an invalid IPv6 address.");
        //                        }
        //
        //                     }
        //
        //                     p.setProperty(propertyKey, propertyValue);
        //                     logger.trace(PC2LogCategory.Settings, subCat,
        //                           currentHashKey + "- Adding key=" + propertyKey + " value=" + propertyValue);
        //                  }
        //
        //               }   
        //            }
        //
        //            if (!(currentHashKey.equals("Network Element Label") ||
        //                  currentHashKey.equals("Label"))){
        //               addDeviceType(currentHashKey, p);
        //               table.put(currentHashKey, p);
        //               // key = currentHashKey;
        //               if (currentHashKey.equals("DUT")) {
        //                  String deviceType = p.getProperty(SettingConstants.DEVICE_TYPE);
        //                  if (deviceType != null) {
        //                     table.put(deviceType+"0", p);
        //                     logger.debug(PC2LogCategory.PCSim2, subCat, 
        //                        "Adding label=" + deviceType + "0 to system settings.");
        //                     
        //                  }
        //               }
        //               else if (currentHashKey.startsWith("UE")) {
        //                  String sim = p.getProperty(SettingConstants.SIMULATED);
        //                  if (sim != null &&
        //                        (sim.equalsIgnoreCase("false") ||
        //                         sim.equalsIgnoreCase("no")||
        //                         sim.equalsIgnoreCase("disable"))) {
        //                     String pui = p.getProperty(SettingConstants.PUI);
        //                     String pui2 = p.getProperty(SettingConstants.PUI2);
        //                     if (pui != null) {
        //                        table.put(pui, p);
        //                        logger.debug(PC2LogCategory.PCSim2, subCat, 
        //                              "Adding label=" + pui + " to system settings.");
        //                        
        //                     }
        //                     if (pui2 != null && !pui2.equals(pui)) {
        //                        table.put(pui2, p);
        //                        logger.debug(PC2LogCategory.PCSim2, subCat, 
        //                              "Adding label=" + pui2 + " to system settings.");
        //                     }
        //                  }
        //               }
        //            }
        //
        //         }

    } catch (Exception e) {
        logger.error(PC2LogCategory.Settings, subCat,
                "Check xls workbook name, sheet name, and column parameters.");
        e.printStackTrace();
    }
    return table;

}

From source file:com.cedarsoft.utils.excel.SpreadSheetColumnMapping.java

License:Open Source License

@Nonnull
public T getValue(@Nonnull HSSFRow row) throws NoValueFoundException {
    HSSFCell cell = row.getCell(getIndex());
    if (cell == null) {
        throw new NoValueFoundException("No cell found at index " + getIndex() + " for row " + row.getRowNum());
    }// www .j ava  2  s . com
    return getValue(cell);
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

/**
 * Method that load the sample detail into the HSSFWorkbook
 *//*www .  j  a  v  a  2 s. c  om*/
private void loadExcelList() {
    String rutaResumen = property.getKey(REPORT_PATH_KBIO, Bundle.conf);
    InputStream inputStream = getClass().getResourceAsStream(rutaResumen);
    try {
        listBook = new HSSFWorkbook(inputStream);
        HSSFSheet sheetDetails = listBook.getSheetAt(0);
        loadDetailSheet(sheetDetails);
        HSSFSheet sheetList = listBook.getSheetAt(1);
        HSSFSheet actsheetUsefull = listBook.getSheetAt(2);
        int inidatsheetList = 2;
        int inidatsheetUsefull = 1;
        String wellkbio;
        String platenameact = "";
        Integer platevoy = 0;
        Integer rowusefull = 0;
        int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
        int plates = setShipmentDetail.size() / mod;
        //Crear hojas usefull que necesitare dependiendo del numero de platos
        for (int j = 1; j < plates; j++) {
            listBook.cloneSheet(2);
        }
        int i = 0;
        for (ShipmentDetail shipmentDetail : setShipmentDetail) {
            SampleDetail sampledet = shipmentDetail.getStSampleDetail();
            HSSFRow actrowsheetList;
            //****************    sheetList  **************************
            //
            if (sheetList.getRow(i + inidatsheetList) == null)
                sheetList.createRow(i + inidatsheetList);
            actrowsheetList = sheetList.getRow(i + inidatsheetList);
            if (actrowsheetList.getCell(0) == null)
                actrowsheetList.createCell(0);
            if (sampledet.getSamplegid() != null)
                actrowsheetList.getCell(0).setCellValue(
                        sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded()
                                ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                                : String.valueOf(sampledet.getSamplegid())));
            if (actrowsheetList.getCell(1) == null)
                actrowsheetList.createCell(1);
            actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename());
            if (actrowsheetList.getCell(2) == null)
                actrowsheetList.createCell(2);
            wellkbio = sampledet.getPlateloc();
            if (wellkbio.length() == 2)
                wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
            actrowsheetList.getCell(2).setCellValue(wellkbio);
            if (actrowsheetList.getCell(3) == null)
                actrowsheetList.createCell(3);
            actrowsheetList.getCell(3).setCellValue("");
            //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
            if (sampledet.getControltype() != null)
                if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B"))
                    actrowsheetList.getCell(3).setCellValue("C");

            if (sampledet.getSamplegid() == null)
                actrowsheetList.getCell(3).setCellValue("C");

            if (actrowsheetList.getCell(4) == null)
                actrowsheetList.createCell(4);
            actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize());

            // ********************   sheetUsefull  ***********************

            if (!platenameact.equals(sampledet.getPlatename())) {
                platenameact = sampledet.getPlatename();
                platevoy = platevoy + 1;
                actsheetUsefull = listBook.getSheetAt(platevoy + 1);
                rowusefull = inidatsheetUsefull;
            }
            HSSFRow actrowsheetUsefull;
            if (actsheetUsefull.getRow(rowusefull) == null)
                actsheetUsefull.createRow(rowusefull);
            actrowsheetUsefull = actsheetUsefull.getRow(rowusefull);

            if (actrowsheetUsefull.getCell(1) == null)
                actrowsheetUsefull.createCell(1);
            actrowsheetUsefull.getCell(1).setCellValue(wellkbio);

            if (actrowsheetUsefull.getCell(2) == null)
                actrowsheetUsefull.createCell(2);
            actrowsheetUsefull.getCell(2).setCellValue(i + 1);
            rowusefull = rowusefull + 1;
            i = i + 1;
        }
    } catch (IOException e) {
        logger.error(e.getMessage());
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListGrid() {

    String rutaResumen;/*from   w w  w.  j av  a 2 s  .  c o m*/
    int size = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
    int plates = setShipmentDetail.size() / size;

    if (size == SIZE_PLATE_96) {

        //PlateContentList.letters=PlateContentList.letters96;
        rutaResumen = property.getKey(REPORT_PATH_KBIO_G96, Bundle.conf);
        rowsplate = 8;
        colsplate = 12;
    } else {

        rowsplate = 16;
        colsplate = 24;
        rutaResumen = property.getKey(REPORT_PATH_KBIO_G384, Bundle.conf);
        //PlateContentList.letters=PlateContentList.letters384;
    }

    InputStream inputStream = null;

    String platenameac = "";
    int rowplatename;

    Integer rowItemNo;
    Integer rowinidatsamples = 4;
    Integer actualrow = 0;
    Integer actualcol = 1;
    Integer cuanhojasvoy = 1;

    try {

        inputStream = getClass().getResourceAsStream(rutaResumen);
        listBook = new HSSFWorkbook(inputStream);
        HSSFSheet sheetDetails = listBook.getSheetAt(0);
        loadDetailSheet(sheetDetails);

        //Crear las hojas que voy a necesitar dependiendo el numero de platos
        Integer cuanhojasnecesito = 1;
        if (size == SIZE_PLATE_96) {
            cuanhojasnecesito = plates / 45;
            if (plates % 45 != 0)
                cuanhojasnecesito = cuanhojasnecesito + 1;
        }

        if (size == SIZE_PLATE_384) {
            cuanhojasnecesito = plates / 26;
            if (plates % 26 != 0)
                cuanhojasnecesito = cuanhojasnecesito + 1;
        }

        for (int i = 1; i < cuanhojasnecesito; i++) {
            listBook.cloneSheet(1);
        }

        HSSFSheet sheetGridActual;

        sheetGridActual = listBook.getSheetAt(cuanhojasvoy);

        int cuanplatosvoy = 0;
        int i = 0;

        for (ShipmentDetail shipmentDetail : setShipmentDetail) {
            SampleDetail sampledetail = shipmentDetail.getStSampleDetail();
            HSSFRow actrowsheetGrid;

            if (sheetGridActual.getRow(i) == null)
                sheetGridActual.createRow(i);

            //poner nombre del plato
            if (!platenameac.equals(sampledetail.getPlatename())) {
                platenameac = sampledetail.getPlatename();

                cuanplatosvoy = cuanplatosvoy + 1;

                //si hay mas de 45 platos entonces hacer una nueva hoja 
                if (cuanplatosvoy % 46 == 0 && size == SIZE_PLATE_96) {
                    cuanhojasvoy = cuanhojasvoy + 1;
                    actualrow = 0;
                    actualcol = 1;
                    cuanplatosvoy = 1;
                    sheetGridActual = listBook.getSheetAt(cuanhojasvoy);
                }

                //si hay mas de 26 platos entonces hacer una nueva hoja 
                if (cuanplatosvoy % 26 == 0 && size == SIZE_PLATE_384) {
                    cuanhojasvoy = cuanhojasvoy + 1;
                    actualrow = 0;
                    actualcol = 1;
                    cuanplatosvoy = 1;
                    sheetGridActual = listBook.getSheetAt(cuanhojasvoy);
                }

                rowplatename = (cuanplatosvoy - 1) * (rowsplate + 2) + cuanplatosvoy;

                if (cuanplatosvoy > 8) {
                    rowplatename = rowplatename + 1;
                }

                //Indica que area se va a combinar (roiwini, rowfin, colini, colfin)

                if (sheetGridActual.getRow(rowplatename) == null)
                    sheetGridActual.createRow(rowplatename);
                actrowsheetGrid = sheetGridActual.getRow(rowplatename);

                if (actrowsheetGrid.getCell(1) == null)
                    actrowsheetGrid.createCell(1);
                actrowsheetGrid.getCell(1).setCellValue(platenameac);

                if (cuanplatosvoy == 1)
                    rowinidatsamples = 2;
                else if (cuanplatosvoy == 9 && size == SIZE_PLATE_96)
                    rowinidatsamples = rowinidatsamples + rowsplate + 4;
                else
                    rowinidatsamples = rowinidatsamples + rowsplate + 3;
            }

            //sacar la siguiente posicion

            actualrow = actualrow + 1;
            if (actualrow > rowsplate) {
                actualcol = actualcol + 1;
                actualrow = 1;
            }

            if (actualcol > colsplate) {
                actualrow = 1;
                actualcol = 1;
            }

            //PONER EL SAMPLEID
            rowItemNo = actualrow + rowinidatsamples;

            if (sheetGridActual.getRow(rowItemNo) == null)
                sheetGridActual.createRow(rowItemNo);
            actrowsheetGrid = sheetGridActual.getRow(rowItemNo);

            if (actrowsheetGrid.getCell(actualcol) == null)
                actrowsheetGrid.createCell(actualcol);

            //SI NO TIENE SAMPLEID COLOCAR "BLANK"
            if (sampledetail.getSamplegid() == null
                    || (sampledetail.getControltype() != null && sampledetail.getControltype().equals("B")))
                actrowsheetGrid.getCell(actualcol).setCellValue("BLANK");
            else
                actrowsheetGrid.getCell(actualcol).setCellValue(
                        sampledetail.getLabstudyid().getPrefix() + (sampledetail.getLabstudyid().isUsePadded()
                                ? StrUtils.getPaddingCeros(sampledetail.getSamplegid())
                                : String.valueOf(sampledetail.getSamplegid())));
            i = i + 1;
        }

    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadPlateID(HSSFSheet sheetList) {
    if (mapPlate != null && !mapPlate.isEmpty()) {
        Iterator<Map.Entry<Integer, String>> it = mapPlate.entrySet().iterator();
        int inidatsheetList = 12;
        int indexRow = 0;
        while (it.hasNext()) {
            HSSFRow actrowsheetList;
            if (sheetList.getRow(indexRow + inidatsheetList) == null)
                sheetList.createRow(indexRow + inidatsheetList);
            actrowsheetList = sheetList.getRow(indexRow + inidatsheetList);
            if (actrowsheetList.getCell(1) == null)
                actrowsheetList.createCell(1);
            Map.Entry<Integer, String> pair = it.next();
            actrowsheetList.getCell(1).setCellValue(pair.getValue());
            indexRow++;/*w w  w  . j  av  a2 s  . c  o m*/
        }
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListSampleIDGenotypingService(HSSFSheet sheetList) {
    int inidatsheetList = 15;
    String wellkbio;/*from  www .j  a  va 2  s .c o  m*/
    int i = 0;
    int indexBlank = 1;
    for (ShipmentDetail shipmentDetail : setShipmentDetail) {
        SampleDetail sampledet = shipmentDetail.getStSampleDetail();
        HSSFRow actrowsheetList;
        //****************    sheetList  **************************
        //
        if (sheetList.getRow(i + inidatsheetList) == null)
            sheetList.createRow(i + inidatsheetList);
        actrowsheetList = sheetList.getRow(i + inidatsheetList);
        if (actrowsheetList.getCell(1) == null)
            actrowsheetList.createCell(1);
        if (sampledet.getSamplegid() != null)
            actrowsheetList.getCell(1)
                    .setCellValue(sampledet.getLabstudyid().getPrefix()
                            + (sampledet.getLabstudyid().isUsePadded()
                                    ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                                    : String.valueOf(sampledet.getSamplegid()))
                            + "-" + sampledet.getStudysampleid());
        if (actrowsheetList.getCell(2) == null)
            actrowsheetList.createCell(2);
        actrowsheetList.getCell(2)
                .setCellValue(getPlate(sampledet.getPlatename(), sampledet.getLabstudyid().getPrefix()));
        if (actrowsheetList.getCell(3) == null)
            actrowsheetList.createCell(3);
        wellkbio = sampledet.getPlateloc();
        if (wellkbio.length() == 2)
            wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
        actrowsheetList.getCell(3).setCellValue(wellkbio);

        if (sampledet.getPlateloc().equals("H11") || sampledet.getPlateloc().equals("H12")) {
            actrowsheetList.getCell(1).setCellValue("");
        } else {
            //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
            if (sampledet.getControltype() != null) {
                if (!sampledet.getControltype().equals("")) {
                    actrowsheetList.getCell(1)
                            .setCellValue(getPathEmpty(indexBlank, sampledet.getStudysampleid()));
                    indexBlank++;
                }
            } else if (sampledet.getSamplegid() == null) {
                actrowsheetList.getCell(1).setCellValue(getPathEmpty(indexBlank, sampledet.getStudysampleid()));
                indexBlank++;
            }

        }
        if (actrowsheetList.getCell(7) == null)
            actrowsheetList.createCell(7);
        actrowsheetList.getCell(7).setCellValue(
                sampledet.getBreedergid() != null ? String.valueOf(sampledet.getBreedergid()) : "");
        if (actrowsheetList.getCell(8) == null)
            actrowsheetList.createCell(8);
        actrowsheetList.getCell(8)
                .setCellValue(sampledet.getNplanta() != null ? String.valueOf(sampledet.getNplanta()) : "");
        i = i + 1;
    }
}

From source file:com.cimmyt.reports.impl.ServiceReportKBioImpl.java

License:Apache License

private void loadListSampleID(HSSFSheet sheetList) {

    int inidatsheetList = 2;
    String wellkbio;/*from  w  w  w.j  a  v  a2s  .  c o  m*/
    int mod = setShipmentDetail.iterator().next().getStSampleDetail().getLabstudyid().getPlatesize();
    int plates = setShipmentDetail.size() / mod;

    int i = 0;
    for (ShipmentDetail shipmentDetail : setShipmentDetail) {
        SampleDetail sampledet = shipmentDetail.getStSampleDetail();
        HSSFRow actrowsheetList;
        //****************    sheetList  **************************
        //
        if (sheetList.getRow(i + inidatsheetList) == null)
            sheetList.createRow(i + inidatsheetList);
        actrowsheetList = sheetList.getRow(i + inidatsheetList);
        if (actrowsheetList.getCell(0) == null)
            actrowsheetList.createCell(0);
        if (sampledet.getSamplegid() != null)
            actrowsheetList.getCell(0).setCellValue(
                    sampledet.getLabstudyid().getPrefix() + (sampledet.getLabstudyid().isUsePadded()
                            ? StrUtils.getPaddingCeros(sampledet.getSamplegid())
                            : String.valueOf(sampledet.getSamplegid())));
        if (actrowsheetList.getCell(1) == null)
            actrowsheetList.createCell(1);
        actrowsheetList.getCell(1).setCellValue(sampledet.getPlatename());
        if (actrowsheetList.getCell(2) == null)
            actrowsheetList.createCell(2);
        wellkbio = sampledet.getPlateloc();
        if (wellkbio.length() == 2)
            wellkbio = wellkbio.substring(0, 1) + "0" + wellkbio.substring(1);
        actrowsheetList.getCell(2).setCellValue(wellkbio);
        if (actrowsheetList.getCell(3) == null)
            actrowsheetList.createCell(3);
        actrowsheetList.getCell(3).setCellValue("");
        //Por especificaciones de KBIo se pone C si es un control y nada si es ADN normal
        if (sampledet.getControltype() != null)
            if (sampledet.getControltype().equals("K") || sampledet.getControltype().equals("B"))
                actrowsheetList.getCell(3).setCellValue("C");

        if (sampledet.getSamplegid() == null)
            actrowsheetList.getCell(3).setCellValue("C");

        if (actrowsheetList.getCell(4) == null)
            actrowsheetList.createCell(4);
        actrowsheetList.getCell(4).setCellValue(sampledet.getLabstudyid().getPlatesize());

        i = i + 1;
    }
}

From source file:com.cladonia.xngreditor.ImportUtilities.java

License:Open Source License

public static DefaultTableModel splitExcelFile(File toSplit, int acceptFormula, int tableIndex,
        boolean convertCharsToEntites) throws Exception {

    //file = toSplit;
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(toSplit));
    HSSFWorkbook wb = new HSSFWorkbook(fs);

    if (tableIndex > -1) {
        HSSFSheet sheet = wb.getSheetAt(tableIndex - 1);

        boolean isBlankRow = true;

        int numCols = 0;
        int numRows = 0;
        ////from  w w w  .j  a  va2 s.  c  om
        //int firstRow = 0;
        int firstColumn = 0;
        int iColumn = 0;
        //firstRow = getFirstRow(sheet,-1);

        /*if(firstRow==-1) {
         //MessageHandler.showError("Error, Cannot Read Sheet: "+tableIndex,"Import From Excel Error");
          return(null);
          }
          else {*/
        HSSFRow row = sheet.getRow(sheet.getFirstRowNum());
        firstColumn = getFirstColumn(sheet);

        numCols = getNumberOfCells(sheet);

        //row = sheet.getRow(firstRow);
        //numRows = sheet.getLastRowNum() - firstRow;

        Vector rows = new Vector();
        /*System.out.println(firstColumn+":"+numCols);
         System.out.println(sheet.getFirstRowNum()+" To "+sheet.getLastRowNum());
         System.out.println(sheet.getPhysicalNumberOfRows());*/
        for (int rCnt = sheet.getFirstRowNum(); rCnt < sheet.getPhysicalNumberOfRows(); ++rCnt) {
            //reset the blank row boolean
            isBlankRow = true;

            row = sheet.getRow(rCnt);
            //System.out.println(numCols + ":" + firstColumn);
            String[] separated = new String[numCols - firstColumn];

            for (int cCnt = firstColumn; cCnt < numCols; ++cCnt) {
                try {
                    HSSFCell cell = row.getCell((short) cCnt);
                    if (cell != null) {
                        switch (cell.getCellType()) {

                        case HSSFCell.CELL_TYPE_NUMERIC://System.out.println(rCnt+":"+cCnt+ " is numeric");

                            double value = row.getCell((short) cCnt).getNumericCellValue();

                            try {
                                //get the long value which eliminates the decimal point
                                long iValue = (new Double(value)).longValue();
                                //get the double value from this long value
                                double longValue = (new Long(iValue)).doubleValue();
                                //subtract the two, if answer is 0 then
                                //value can be converted,
                                //if not then it can't
                                if (value - longValue == 0) {
                                    //use long value
                                    separated[cCnt - firstColumn] = String.valueOf(iValue);
                                } //end if
                                else {
                                    //use double value
                                    separated[cCnt - firstColumn] = String.valueOf(value);
                                } //end else
                            } //end try
                            catch (NumberFormatException e) {
                                //use double value
                                separated[cCnt - firstColumn] = String.valueOf(value);
                            } //end catch

                            break;

                        case HSSFCell.CELL_TYPE_STRING://System.out.println(rCnt+":"+cCnt+ " is string");

                            isBlankRow = false;
                            separated[cCnt - firstColumn] = row.getCell((short) cCnt).getStringCellValue();

                            break;

                        case HSSFCell.CELL_TYPE_FORMULA://System.out.println(rCnt+":"+cCnt+ " is formula");

                            isBlankRow = false;
                            if (acceptFormula == 0) {
                                //prompt
                                String[] options = { "Cell Value", "Formula Text" };
                                Object formulaValue = JOptionPane.showInputDialog(null,
                                        "This worksheet contains formulas\n"
                                                + "What format would you like to import "
                                                + "the formula cells by: ",
                                        "Import From Table", JOptionPane.INFORMATION_MESSAGE, null, options,
                                        options[0]);
                                if (formulaValue.toString().equalsIgnoreCase(options[0])) {
                                    //accept values
                                    acceptFormula = 2;
                                } //end if
                                else {
                                    acceptFormula = 1;
                                } //end else
                            } //end if
                            else if (acceptFormula == 1) {
                                //accept formula
                                separated[cCnt - firstColumn] = row.getCell((short) cCnt).getCellFormula();

                            } //end else
                            else if (acceptFormula == 2) {
                                //dont accept formula
                                double doubleValue = row.getCell((short) cCnt).getNumericCellValue();
                                Double dValue = new Double(doubleValue);
                                if (dValue.isNaN()) {
                                    //should have been a string
                                    separated[cCnt - firstColumn] = row.getCell((short) cCnt)
                                            .getStringCellValue();

                                } //end if
                                else {
                                    try {

                                        //get the long value which eliminates the decimal point
                                        long iValue = (new Double(doubleValue)).longValue();

                                        //get the double value from this long value
                                        double longValue = (new Long(iValue)).doubleValue();

                                        //subtract the two, if answer is 0 then
                                        //value can be converted,
                                        //if not then it can't
                                        if (doubleValue - longValue == 0) {
                                            //use long value
                                            separated[cCnt - firstColumn] = String.valueOf(iValue);

                                        } //end if
                                        else {
                                            //use double value
                                            separated[cCnt - firstColumn] = String.valueOf(doubleValue);

                                        } //end else

                                    } //end try
                                    catch (NumberFormatException e) {
                                        //use double value
                                        separated[cCnt - firstColumn] = String.valueOf(doubleValue);

                                    } //end catch
                                } //end else
                            } //end else

                            break;

                        case HSSFCell.CELL_TYPE_ERROR://System.out.println(rCnt+":"+cCnt+ " is error");

                            separated[cCnt - firstColumn] = "";

                            break;
                        case HSSFCell.CELL_TYPE_BOOLEAN://System.out.println(rCnt+":"+cCnt+ " is boolean");

                            isBlankRow = false;
                            boolean booleanValue = row.getCell((short) cCnt).getBooleanCellValue();
                            separated[cCnt - firstColumn] = String.valueOf(booleanValue);

                            break;
                        case HSSFCell.CELL_TYPE_BLANK://System.out.println(rCnt+":"+cCnt+ " is blank");

                            separated[cCnt - firstColumn] = "";

                            break;

                        }

                    } //end if cell!=null
                    else {

                    } //end else

                } catch (Exception e) {
                    //just a blank cell
                    separated[cCnt - firstColumn] = "";

                } //end try catch

            } //end for cCnt

            if (!isBlankRow) {
                rows.add(separated);
            }
            //HSSFCell cell;
        } //end for rCnt    

        DefaultTableModel tableModel = addRowsToTable(rows, numCols - firstColumn);
        /*fileName = file.getAbsolutePath();
         fileName = fileName.substring(0, fileName.lastIndexOf("."));
         fileName += ".xml";*/
        return (tableModel);

    } //end else
      //}

    return (null);

}

From source file:com.cladonia.xngreditor.ImportUtilities.java

License:Open Source License

public static int getFirstColumn(HSSFSheet sheet) throws Exception {

    int minimum = 0;
    boolean isFirstTime = true;
    for (int cnt = sheet.getFirstRowNum(); cnt < sheet.getPhysicalNumberOfRows(); ++cnt) {

        //get the first row
        HSSFRow row = sheet.getRow(cnt);
        boolean found = false;
        //now find the first column that isn't null or empty
        short icnt = 0;
        while ((icnt < row.getLastCellNum()) && (found != true)) {

            try {
                HSSFCell cell = row.getCell(icnt);
                //System.out.println(icnt+":"+cell.getCellType());
                if (cell != null) {
                    //System.out.println(cell.getCellType());
                    if (icnt < minimum) {
                        minimum = icnt;/*from w  w w.  j ava 2 s  .c om*/
                    }
                    if (isFirstTime) {
                        minimum = icnt;
                        isFirstTime = false;
                    }
                    found = true;

                }

            } catch (NullPointerException e) {
                // TODO Auto-generated catch block
                //System.out.println(icnt+" is null");
                e.printStackTrace();

            }
            //System.out.println("minimum for row: "+cnt+ " is "+minimum);
            ++icnt;
        }

    }

    return (minimum);
}