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:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private int[] doImportNewFormat(HSSFWorkbook wb, JProgressBar progress) {
    int numSuccess = 0;
    int numFails = 0;
    HSSFSheet transactionSheet = wb.getSheet("NewTransactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    HashMap<String, Integer[]> storedIDs = new HashMap<String, Integer[]>();
    HashMap<String, String> storedBackS = new HashMap<String, String>();
    int numRows = transactionSheet.getLastRowNum() + 1;
    progress.setMaximum(numRows);/*  w  ww  .  j  a  va 2  s  . c om*/
    progress.setValue(0);

    for (int i = 1; i < numRows; i++) {
        HSSFRow row = transactionSheet.getRow(i);
        if (row != null) {
            String backSerial = getStrVal(row.getCell(0), 16383);
            String serial = getStrVal(row.getCell(1));

            if ((serial == null || serial.trim().isEmpty())) {
                continue;//break;
            }

            String adressSup = getStrVal(row.getCell(2));
            String idSup = null;
            String nameSup = null;
            String streetSup = null;
            String streetNoSup = null;
            String zipSup = null;
            String citySup = null;
            String countySup = null;
            String countrySup = null;
            String vatSup = null;
            String tobSup = null;
            HSSFRow busRow = getRow(businessSheet, adressSup, 9);
            if (busRow != null) {
                idSup = getStrVal(busRow.getCell(0));
                nameSup = getStrVal(busRow.getCell(1));
                streetSup = getStrVal(busRow.getCell(2));
                streetNoSup = getStrVal(busRow.getCell(3), 10);
                zipSup = getStrVal(busRow.getCell(4), 10);
                citySup = getStrVal(busRow.getCell(5));
                countySup = getStrVal(busRow.getCell(6), 30);
                countrySup = getStrVal(busRow.getCell(7));
                vatSup = getStrVal(busRow.getCell(8));
                tobSup = getStrVal(busRow.getCell(10));
            } else if (adressSup != null) {
                //System.err.println("business not there??? Row: " + (i + 1) + "\tadressSup: " + adressSup);
                String msg = "Row: " + (i + 1) + "\tbusiness not there???\tadressSup: " + adressSup;
                System.err.println(msg);
                logMessages += msg + "\n";
            } else {
                //System.err.println("adressSup is null??? Row: " + (i + 1) + "\t" + adressSup + (adressSup != null ? "" : " -> Station not defined"));
                String msg = "Row: " + (i + 1) + "\tadressSup is null???\t" + adressSup
                        + (adressSup != null ? "" : " -> Station not defined");
                System.err.println(msg);
                logMessages += msg + "\n";
            }

            String prodName = getStrVal(row.getCell(3));
            String prodNum = getStrVal(row.getCell(4));
            String prodTreatment = getStrVal(row.getCell(5));

            String lotNo_ = getStrVal(row.getCell(6));
            String dayPD = getStrVal(row.getCell(7));
            String monthPD = getStrVal(row.getCell(8));
            String yearPD = getStrVal(row.getCell(9));
            String dayMHD = getStrVal(row.getCell(10));
            String monthMHD = getStrVal(row.getCell(11));
            String yearMHD = getStrVal(row.getCell(12));

            String day = getStrVal(row.getCell(13));
            String month = getStrVal(row.getCell(14));
            String year = getStrVal(row.getCell(15));
            String amountKG_ = getStrVal(row.getCell(16));
            String numPU = getStrVal(row.getCell(17));
            String typePU = getStrVal(row.getCell(18));

            String adressRec = getStrVal(row.getCell(19));
            String idRec = null;
            String nameRec = adressRec;
            String streetRec = null;
            String streetNoRec = null;
            String zipRec = null;
            String cityRec = null;
            String countyRec = null;
            String countryRec = null;
            String vatRec = null;
            String tobRec = null;
            busRow = getRow(businessSheet, adressRec, 9);
            if (busRow != null) {
                idRec = getStrVal(busRow.getCell(0));
                nameRec = getStrVal(busRow.getCell(1));
                streetRec = getStrVal(busRow.getCell(2));
                streetNoRec = getStrVal(busRow.getCell(3), 10);
                zipRec = getStrVal(busRow.getCell(4), 10);
                cityRec = getStrVal(busRow.getCell(5));
                countyRec = getStrVal(busRow.getCell(6), 30);
                countryRec = getStrVal(busRow.getCell(7));
                vatRec = getStrVal(busRow.getCell(8));
                tobRec = getStrVal(busRow.getCell(10));
            } else if (adressRec != null) {
                //System.err.println("business not there??? Row: " + (i + 1) + "\tadressRec: " + adressRec);
                String msg = "Row: " + (i + 1) + "\tbusiness not there???\tadressRec: " + adressRec;
                System.err.println(msg);
                logMessages += msg + "\n";
            } else {
                //System.err.println("adressRec is null??? Row: " + (i + 1) + "\t" + adressRec + (adressRec != null ? "" : " -> Station not defined"));
                String msg = "Row: " + (i + 1) + "\tadressRec is null???\t" + adressRec
                        + (adressRec != null ? "" : " -> Station not defined");
                System.err.println(msg);
                logMessages += msg + "\n";
            }

            String ec = getStrVal(row.getCell(21)); // EndChain
            String ece = getStrVal(row.getCell(22)); // Explanation_EndChain
            String oc = getStrVal(row.getCell(23)); // OriginCountry
            String cqr = getStrVal(row.getCell(24), 16383); // Contact_Questions_Remarks
            String ft = getStrVal(row.getCell(25)); // Further_Traceback
            String ms = getStrVal(row.getCell(26)); // MicrobiologicalSample

            Integer[] c = null;
            if (nameSup != null && !nameSup.trim().isEmpty()) {
                c = getCharge_Lieferung(idSup, nameSup, streetSup, streetNoSup, zipSup, citySup, countySup,
                        countrySup, tobSup, vatSup, prodName, prodNum, prodTreatment, lotNo_, dayMHD, monthMHD,
                        yearMHD, dayPD, monthPD, yearPD, oc, day, month, year, amountKG_, typePU, numPU, idRec,
                        nameRec, streetRec, streetNoRec, zipRec, cityRec, countyRec, countryRec, tobRec, vatRec,
                        serial, cqr, ec, ece, ft, ms);
                storedIDs.put(serial, c);
                storedBackS.put(serial, backSerial);
            }
        }
    }
    for (String serial : storedIDs.keySet()) {
        Integer[] c = storedIDs.get(serial);
        String backSerials = storedBackS.get(serial);
        if (backSerials != null && !backSerials.isEmpty()) {
            StringTokenizer tok = new StringTokenizer(backSerials, "\n");
            while (tok.hasMoreTokens()) {
                String backS = tok.nextToken();
                if (storedIDs.containsKey(backS)) {
                    Integer[] cBack = storedIDs.get(backS);
                    if (is1SurelyNewer(cBack[3], c[3])) {
                        //System.err.println("- Dates not in temporal order, dateOut < dateIn!!! Serial: " + serial + "; PreviousSerial: " + backS);
                        String msg = "Dates not in temporal order, dateOut < dateIn!!! Serial: " + serial
                                + "; PreviousSerial: " + backS;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    }
                    if (c[2] == null) {
                        //System.err.println("Fehlerchenchen_1!! Serial: " + backS);
                        String msg = "Error Type 1 (Batches)!! Serial: " + backS; // Fehlerchenchen_1
                        System.err.println(msg);
                        logMessages += msg + "\n";
                        numFails++;
                    } else if (cBack[3] == null) {
                        //System.err.println("Fehlerchenchen_2!! E.g. Station not defined? Serial: " + serial);
                        String msg = "Error Type 2 (Deliveries)!! E.g. Station not defined? Serial: " + backS; // Fehlerchenchen_1
                        System.err.println(msg);
                        logMessages += msg + "\n";
                        numFails++;
                    } else if (cBack[4].intValue() != c[0].intValue()) {
                        //System.err.println("Fehlerchenchen_3!! Recipient and Supplier different... Serial: " + serial);
                        String msg = "Error Type 3!! Recipient and Supplier different... Serial: " + serial;
                        System.err.println(msg);
                        logMessages += msg + "\n";
                    } else {
                        if (getID("ChargenVerbindungen", new String[] { "Zutat", "Produkt" },
                                new String[] { cBack[3].toString(), c[2].toString() }, null, null) == null) {
                            //System.err.println("Fehlerchenchen_4!! Serial/PreviousSerial: " + serial + " / " + backS);
                            String msg = "Error Type 4 (Links)!! Serial/PreviousSerial: " + serial + " / "
                                    + backS;
                            System.err.println(msg);
                            logMessages += msg + "\n";
                            numFails++;
                        } else {
                            numSuccess++;
                        }
                    }
                } else {
                    //System.err.println("backSerial not there..." + backS);
                    String msg = "backSerial not there..." + backS;
                    System.err.println(msg);
                    logMessages += msg + "\n";
                }
            }
        }
    }
    return new int[] { numSuccess, numFails };
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private void transformFormat(HSSFWorkbook wb, HSSFWorkbook wbNew) {
    HSSFSheet transactionSheet = wb.getSheet("Transactions");
    HSSFSheet businessSheet = wb.getSheet("Business_List");
    int numRows = transactionSheet.getLastRowNum() + 1;
    HSSFSheet transactionSheetNew = wbNew.getSheet("NewTransactions");
    HSSFRow newRow;//from   ww  w  .  ja  v  a 2 s  .  c  om
    HSSFCell newCell;
    HSSFSheet lookupNew = wbNew.getSheet("NewLookUp");
    HashMap<Long, HSSFRow> storedRows = new HashMap<Long, HSSFRow>();
    HashMap<Long, String> storedSerials = new HashMap<Long, String>();
    CRC32 crc32 = new CRC32();
    CellStyle cs = wbNew.createCellStyle();
    cs.setWrapText(true);

    int newRowLfd = 0;
    int i = 1;
    for (; i < numRows; i++) {
        HSSFRow row = transactionSheet.getRow(i);
        if (row != null) {
            String serial = getStrVal(row.getCell(0)); // Serial_number
            String contactPerson = getStrVal(row.getCell(2)); // person
            String adressRec = getStrVal(row.getCell(4)); // Address
            if ((serial == null || serial.trim().isEmpty())) {
                if (contactPerson != null && !contactPerson.isEmpty()
                        || adressRec != null && !adressRec.isEmpty()) {
                    System.err.println("serial is seriously null... " + (i + 1));
                }
            } else {
                /*
                int index = serial.lastIndexOf("_");
                if (index <= 0) {
                   System.err.println("index error ... no '_' there... " + (i + 1));
                }
                serial = serial.substring(0, index) + "_" + (i + 1);
                 */

                HSSFRow busRow = getRow(businessSheet, adressRec, 9);
                if (busRow == null) {
                    System.err.println("Id issue on recs...Row: " + (i + 1) + "\t" + adressRec);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressRec = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String prodNameOut = getStrVal(row.getCell(6)); // ProductName
                String prodNumOut = getStrVal(row.getCell(7)); // ProductNo
                String dayOut = getStrVal(row.getCell(8)); // Day
                String monthOut = getStrVal(row.getCell(9)); // Month
                String yearOut = getStrVal(row.getCell(10)); // Year
                String amountKG_Out = getStrVal(row.getCell(11)); // amountKG
                String typePUOut = getStrVal(row.getCell(12)); // typePU
                String numPUOut = getStrVal(row.getCell(13)); // numPU
                String lotNo_Out = getStrVal(row.getCell(14)); // 
                String dayMHDOut = getStrVal(row.getCell(15));
                String monthMHDOut = getStrVal(row.getCell(16));
                String yearMHDOut = getStrVal(row.getCell(17)); // 
                String dayPDOut = getStrVal(row.getCell(18));
                String monthPDOut = getStrVal(row.getCell(19));
                String yearPDOut = getStrVal(row.getCell(20));

                String adressInsp = getStrVal(row.getCell(22)); // Address
                String activityInsp = getStrVal(row.getCell(23)); // Activity
                busRow = getRow(businessSheet, adressInsp, 9);
                if (busRow == null) {
                    System.err.println("Id issue on insps...Row: " + (i + 1) + "\t" + adressInsp);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressInsp = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String prodNameIn = getStrVal(row.getCell(24)); // ProductName
                String prodNumIn = getStrVal(row.getCell(25)); // ProductNo
                String dayIn = getStrVal(row.getCell(26)); // Day
                String monthIn = getStrVal(row.getCell(27)); // Month
                String yearIn = getStrVal(row.getCell(28)); // Year
                String amountKG_In = getStrVal(row.getCell(29)); // amountKG
                String typePUIn = getStrVal(row.getCell(30)); // typePU
                String numPUIn = getStrVal(row.getCell(31)); // numPU
                String lotNo_In = getStrVal(row.getCell(32)); // 
                String dayMHDIn = getStrVal(row.getCell(33));
                String monthMHDIn = getStrVal(row.getCell(34));
                String yearMHDIn = getStrVal(row.getCell(35)); // 
                String dayPDIn = getStrVal(row.getCell(36));
                String monthPDIn = getStrVal(row.getCell(37));
                String yearPDIn = getStrVal(row.getCell(38));

                String adressSup = getStrVal(row.getCell(40)); // Address
                String activitySup = getStrVal(row.getCell(41)); // Activity
                busRow = getRow(businessSheet, adressSup, 9);
                if (busRow == null) {
                    System.err.println("Id issue on susps...Row: " + (i + 1) + "\t" + adressSup);
                } else {
                    String s1 = getStrVal(busRow.getCell(1));
                    if (s1 == null)
                        s1 = "";
                    String s2 = getStrVal(busRow.getCell(2));
                    if (s2 == null)
                        s2 = "";
                    String s3 = getStrVal(busRow.getCell(3), 10);
                    if (s3 == null)
                        s3 = "";
                    String s4 = getStrVal(busRow.getCell(5));
                    if (s4 == null)
                        s4 = "";
                    String s5 = getStrVal(busRow.getCell(7));
                    if (s5 == null)
                        s5 = "";
                    adressSup = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3
                }

                String ec = getStrVal(row.getCell(42)); // EndChain
                String ece = getStrVal(row.getCell(43)); // Explanation_EndChain
                String oc = getStrVal(row.getCell(44)); // OriginCountry
                String cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks
                String ft = getStrVal(row.getCell(46)); // Further_Traceback
                String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample

                busRow = getRow(lookupNew, activityInsp, 9);
                String treatmentIn = null, treatmentOut = null;
                if (busRow != null)
                    treatmentOut = busRow.getCell(13).getStringCellValue();
                busRow = getRow(lookupNew, activitySup, 9);
                if (busRow != null)
                    treatmentIn = busRow.getCell(13).getStringCellValue();

                String sOut = adressInsp + "_" + prodNameOut + "_" + prodNumOut + "_" + lotNo_Out + "_"
                        + dayPDOut + "_" + monthPDOut + "_" + yearPDOut + "_" + dayMHDOut + "_" + monthMHDOut
                        + "_" + yearMHDOut + "_" + dayOut + "_" + monthOut + "_" + yearOut + "_" + amountKG_Out
                        + "_" + numPUOut + "_" + typePUOut + "_" + adressRec;
                crc32.reset();
                crc32.update(sOut.getBytes());
                long crc32Out = crc32.getValue();
                //System.err.println(crc32Out + " -> " + sOut);
                String sIn = adressSup + "_" + prodNameIn + "_" + prodNumIn + "_" + lotNo_In + "_" + dayPDIn
                        + "_" + monthPDIn + "_" + yearPDIn + "_" + dayMHDIn + "_" + monthMHDIn + "_" + yearMHDIn
                        + "_" + dayIn + "_" + monthIn + "_" + yearIn + "_" + amountKG_In + "_" + numPUIn + "_"
                        + typePUIn + "_" + adressInsp;
                crc32.reset();
                crc32.update(sIn.getBytes());
                long crc32In = crc32.getValue();
                //System.err.println(crc32In + " -> " + sIn);                     

                String backSerial = serial + ".1";
                if (storedRows.containsKey(crc32In)) {
                    //HSSFRow r = storedRows.get(crc32In); backSerial = r.getCell(1).getStringCellValue();
                    backSerial = storedSerials.get(crc32In);
                }

                if (storedRows.containsKey(crc32Out)) {
                    HSSFRow r = storedRows.get(crc32Out);
                    HSSFCell c = r.getCell(0);
                    if (c == null) {
                        c = r.createCell(0);
                        c.setCellStyle(cs);
                        c.setCellValue(backSerial);
                    } else
                        add2Cell(c, backSerial);
                    add2Cell(r.getCell(20), contactPerson);
                    add2Cell(r.getCell(23), oc);
                    add2Cell(r.getCell(24), cqr);
                    add2Cell(r.getCell(26), ms);
                } else {
                    newRowLfd++;
                    newRow = transactionSheetNew.createRow(newRowLfd);
                    newCell = newRow.createCell(0, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(backSerial);
                    newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(serial + ".0");
                    newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressInsp);
                    newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNameOut);
                    newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNumOut);
                    if (treatmentOut != null) {
                        newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING);
                        newCell.setCellValue(treatmentOut);
                    }
                    newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(lotNo_Out);
                    newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayPDOut);
                    newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthPDOut);
                    newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearPDOut);
                    newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayMHDOut);
                    newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthMHDOut);
                    newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearMHDOut);
                    newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayOut);
                    newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthOut);
                    newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearOut);
                    newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(amountKG_Out);
                    newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(numPUOut);
                    newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(typePUOut);
                    newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressRec);
                    newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(contactPerson);
                    newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(oc);
                    newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(cqr);
                    newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ms);
                    storedRows.put(crc32Out, newRow);
                    storedSerials.put(crc32Out, serial + ".0");
                }

                if (storedRows.containsKey(crc32In)) {
                    HSSFRow r = storedRows.get(crc32In);
                    add2Cell(r.getCell(20), contactPerson);
                    add2Cell(r.getCell(21), ec);
                    add2Cell(r.getCell(22), ece);
                    add2Cell(r.getCell(23), oc);
                    add2Cell(r.getCell(24), cqr);
                    add2Cell(r.getCell(25), ft);
                    add2Cell(r.getCell(26), ms);
                } else {
                    newRowLfd++;
                    newRow = transactionSheetNew.createRow(newRowLfd);
                    newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(serial + ".1");
                    newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressSup);
                    newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNameIn);
                    newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(prodNumIn);
                    if (treatmentIn != null) {
                        newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING);
                        newCell.setCellValue(treatmentIn);
                    }
                    newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(lotNo_In);
                    newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayPDIn);
                    newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthPDIn);
                    newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearPDIn);
                    newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayMHDIn);
                    newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthMHDIn);
                    newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearMHDIn);
                    newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(dayIn);
                    newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(monthIn);
                    newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(yearIn);
                    newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(amountKG_In);
                    newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(numPUIn);
                    newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(typePUIn);
                    newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellValue(adressInsp);
                    newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(contactPerson);
                    newCell = newRow.createCell(21, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ec);
                    newCell = newRow.createCell(22, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ece);
                    newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(oc);
                    newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(cqr);
                    newCell = newRow.createCell(25, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ft);
                    newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING);
                    newCell.setCellStyle(cs);
                    newCell.setCellValue(ms);
                    storedRows.put(crc32In, newRow);
                    storedSerials.put(crc32In, serial + ".1");
                }
            }
        }
    }
    System.err.println("last row: " + i);
}

From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java

License:Open Source License

private HSSFRow getRow(HSSFSheet sheet, String value, int column) {
    HSSFRow result = null;//  w w  w.  j a  va 2 s. c  o m
    if (value != null && !value.trim().isEmpty()) {
        int numRows = sheet.getLastRowNum() + 1;
        for (int i = 1; i < numRows; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row != null) {
                String val = getStrVal(row.getCell(column));
                if (val != null && !val.trim().isEmpty()) {
                    if (value.trim().equalsIgnoreCase(val.trim())) {
                        result = row;
                        break;
                    }
                }
            }
        }
    }
    return result;
}

From source file:de.bund.bfr.knime.openkrise.db.imports.GeneralXLSImporter.java

License:Open Source License

public boolean doImport(final String filename, final JProgressBar progress, final boolean showResults) {
    Runnable runnable = new Runnable() {
        public void run() {
            try {
                if (progress != null) {
                    progress.setVisible(true);
                    progress.setStringPainted(true);
                    progress.setString("Importiere Excel Datei...");
                    progress.setMinimum(0);
                }/* w w w .  j  a  v  a  2  s. c  o  m*/

                InputStream is = null;
                System.out.println(filename);
                if (filename.startsWith("http://")) {
                    URL url = new URL(filename);
                    URLConnection uc = url.openConnection();
                    is = uc.getInputStream();
                } else if (filename.startsWith("/de/bund/bfr/knime/openkrise/db/res/")) {
                    is = this.getClass().getResourceAsStream(filename);
                } else {
                    is = new FileInputStream(filename);
                }

                try (HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is))) {
                    HSSFSheet sheet;
                    HSSFRow row;

                    int numSuccess = 0;
                    int numFailed = 0;
                    String unusedFields = "";
                    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
                        sheet = wb.getSheetAt(i);
                        String tableName = sheet.getSheetName();
                        MyTable myT = DBKernel.myDBi.getTable(tableName);
                        if (myT != null) {
                            int numRows = sheet.getLastRowNum();
                            if (progress != null) {
                                progress.setMaximum(numRows);
                                progress.setValue(0);
                            }

                            row = sheet.getRow(0);
                            String sql1 = "";
                            String sql2 = "";
                            String sql3 = "";
                            Vector<String> codeSql1 = new Vector<>();
                            Vector<String> codeSql2 = new Vector<>();
                            LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>();
                            int numCols = row.getLastCellNum();
                            String[] fieldNames = new String[numCols];
                            String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT);
                            String[] ffieldTypes = new String[numCols];
                            MyTable[] myForeignTables = new MyTable[numCols];
                            String[] kzS = new String[numCols];
                            String[] dbFieldnames = new String[numCols];
                            int lfdCol = 0;
                            Hashtable<String, String> dbFieldNames = new Hashtable<>();
                            for (int j = 0; j < numCols; j++) {
                                String fieldName = row.getCell(j).getStringCellValue();
                                fieldNames[j] = fieldName;
                                int ffe;
                                String dbFieldName = getDBFieldName(fieldName, myT, takecareofID);
                                if (dbFieldName != null) {
                                    String ft = getForeignTable(dbFieldName, myT);
                                    if (ft != null && ft.equals("DoubleKennzahlen")) {
                                        kzS[j] = getKZ(fieldName, dbFieldName);
                                        dbFieldnames[j] = dbFieldName;
                                    } else if (!dbFieldNames.containsKey(dbFieldName)) {
                                        dbFieldNames.put(dbFieldName, dbFieldName);
                                        sql1 += DBKernel.delimitL(dbFieldName) + ",";
                                        sql2 += "?,";
                                        sql3 += DBKernel.delimitL(dbFieldName) + "=?,";
                                        lfdCol++;
                                    }
                                    fieldTypes[j] = getType(dbFieldName, myT, takecareofID);
                                } else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) {
                                    if (!foreignTables.containsKey(myT.getForeignFields()[ffe]))
                                        foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>());
                                    ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false);
                                    foreignTables.get(myT.getForeignFields()[ffe]).add(j);
                                    myForeignTables[j] = myT.getForeignFields()[ffe];
                                } else if (DBKernel.showHierarchic(tableName)
                                        && fieldName.toLowerCase().endsWith("-code")) {
                                    codeSql1.add(DBKernel.delimitL("CodeSystem") + ","
                                            + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis"));
                                    codeSql2.add(
                                            "'" + fieldName.substring(0, fieldName.length() - "-code".length())
                                                    + "',?,?");
                                } else if (!fieldName.equalsIgnoreCase("id")) {
                                    unusedFields += "," + fieldName;
                                }
                            }
                            if (sql1.length() > 0 && sql2.length() > 0) {
                                String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " ("
                                        + sql1.substring(0, sql1.length() - 1) + ") VALUES ("
                                        + sql2.substring(0, sql2.length() - 1) + ")";
                                PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql,
                                        Statement.RETURN_GENERATED_KEYS);
                                int idCol = lfdCol + 1;
                                sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET "
                                        + sql3.substring(0, sql3.length() - 1) + " WHERE "
                                        + DBKernel.delimitL("ID") + "=?";
                                PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql);
                                PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()];
                                boolean doCode[] = new boolean[codeSql1.size()];
                                int codesI;
                                for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                    sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName))
                                            + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI)
                                            + ")";
                                    psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql);
                                }
                                LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>();
                                LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>();
                                for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) {
                                    Vector<Integer> vs = entry.getValue();
                                    String ssql1 = "", ssql2 = "", ssql3 = "";
                                    for (int ii = 0; ii < vs.size(); ii++) {
                                        ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]);
                                        ssql2 += ",?";
                                        ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?";
                                    }
                                    if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) {
                                        sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename())
                                                + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1)
                                                + ")";
                                        psForeign.put(entry.getKey(), DBKernel.getDBConnection()
                                                .prepareStatement(sql, Statement.RETURN_GENERATED_KEYS));
                                        sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename())
                                                + " SET " + ssql3.substring(1) + " WHERE "
                                                + DBKernel.delimitL("ID") + "=?";
                                        psForeignUpdate.put(entry.getKey(),
                                                DBKernel.getDBConnection().prepareStatement(sql));
                                    }
                                }

                                LinkedHashMap<Object, String> hashBL = null;
                                Iterator<Row> rows = sheet.rowIterator();
                                int lfd = 0;
                                while (rows.hasNext()) {
                                    row = (HSSFRow) rows.next();
                                    boolean setID = false;
                                    Integer lastID = null;
                                    ps.clearParameters();
                                    psUpdate.clearParameters();
                                    for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                        psCodes[codesI].clearParameters();
                                        doCode[codesI] = false;
                                    }
                                    LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>();
                                    for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate
                                            .entrySet()) {
                                        MyTable myT1 = entry.getKey();
                                        psForeign.get(myT1).clearParameters();
                                        psForeignUpdate.get(myT1).clearParameters();
                                        lfdColsForeign.put(myT1, 0);
                                    }

                                    if (row.getRowNum() > 0) {
                                        lfdCol = 0;
                                        codesI = 0;
                                        Object[] kzVal = new Object[numCols];
                                        for (int j = 0; j < numCols; j++) {
                                            if (fieldTypes[j] != null) {
                                                /*
                                                 * if (fieldNames[j].equals(
                                                 * "Bundesland")) { hashBL =
                                                 * DBKernel
                                                 * .myDBi.getHashMap("County");
                                                 * } else { hashBL = null; }
                                                 */
                                                lfdCol++;
                                                if (fieldTypes[j].startsWith("VARCHAR(")
                                                        || fieldTypes[j].startsWith("CHAR(")
                                                        || fieldTypes[j].startsWith("BLOB(")
                                                                && !tableName.equals("DateiSpeicher"))
                                                    manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL);
                                                else if (fieldTypes[j].equals("BOOLEAN"))
                                                    manageBoolean(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("INTEGER"))
                                                    manageInteger(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("BIGINT"))
                                                    manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("DATE"))
                                                    manageDate(ps, psUpdate, lfdCol, row.getCell(j));
                                                else if (fieldTypes[j].equals("DOUBLE")) {
                                                    if (kzS[j] != null) {
                                                        lfdCol--;
                                                        //System.err.println(dbFieldnames[j] + "\t" + kzS[j]);
                                                        if (DBKernel.kzIsString(kzS[j]))
                                                            kzVal[j] = manageString(null, null, lfdCol,
                                                                    row.getCell(j));
                                                        else if (DBKernel.kzIsBoolean(kzS[j]))
                                                            kzVal[j] = manageBoolean(null, null, lfdCol,
                                                                    row.getCell(j));
                                                        else
                                                            kzVal[j] = manageDouble(null, null, lfdCol,
                                                                    row.getCell(j));
                                                    } else {
                                                        manageDouble(ps, psUpdate, lfdCol, row.getCell(j));
                                                    }
                                                } else
                                                    System.err.println(
                                                            "Wasn hier los? Undefinierter Feldtyp???? ->\t"
                                                                    + fieldNames[j]);
                                            } else if (myForeignTables[j] != null && ffieldTypes[j] != null) {
                                                lfdColsForeign.put(myForeignTables[j],
                                                        lfdColsForeign.get(myForeignTables[j]) + 1);
                                                if (ffieldTypes[j].startsWith("VARCHAR(")
                                                        || ffieldTypes[j].startsWith("CHAR(")
                                                        || ffieldTypes[j].startsWith("BLOB(")
                                                                && !tableName.equals("DateiSpeicher"))
                                                    manageString(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j), hashBL);
                                                else if (ffieldTypes[j].equals("BOOLEAN"))
                                                    manageBoolean(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("INTEGER"))
                                                    manageInteger(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("BIGINT"))
                                                    manageBigInteger(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (fieldTypes[j].equals("DATE"))
                                                    manageDate(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                else if (ffieldTypes[j].equals("DOUBLE")) {
                                                    manageDouble(psForeign.get(myForeignTables[j]),
                                                            psForeignUpdate.get(myForeignTables[j]),
                                                            lfdColsForeign.get(myForeignTables[j]),
                                                            row.getCell(j));
                                                } else
                                                    System.err.println(fieldNames[j] + " Feldtype????");
                                            } else if (fieldNames[j].equals("ID")) {
                                                lastID = manageInteger(null, null, 0, row.getCell(j));
                                                if (lastID != null) {
                                                    if (DBKernel.hasID(tableName, lastID.intValue())) {
                                                        psUpdate.setInt(idCol, lastID.intValue());
                                                        setID = true;
                                                    }
                                                }
                                            } else if (DBKernel.showHierarchic(tableName)
                                                    && fieldNames[j].toLowerCase().endsWith("-code")) {
                                                String code = manageString(psCodes[codesI], null, 1,
                                                        row.getCell(j));
                                                if (code != null && code.length() > 0)
                                                    doCode[codesI] = true;
                                                codesI++;
                                            } else {
                                                //System.out.println(fieldNames[j]);                                 
                                            }
                                        }
                                        try {
                                            if (setID) {
                                                psUpdate.execute();
                                            } else {
                                                if (ps.executeUpdate() > 0) {// execute()
                                                    lastID = DBKernel.getLastInsertedID(ps);
                                                } else {
                                                    System.err.println("W");
                                                }
                                            }
                                            numSuccess++;
                                            if (lastID != null) {
                                                for (int j = 0; j < numCols; j++) {
                                                    if (dbFieldnames[j] != null && kzVal[j] != null) {
                                                        DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j],
                                                                lastID, null, kzS[j], kzVal[j]);
                                                    }
                                                }
                                                for (codesI = 0; codesI < codeSql1.size(); codesI++) {
                                                    if (doCode[codesI]) {
                                                        psCodes[codesI].setInt(2, lastID);
                                                        try {
                                                            psCodes[codesI].execute();
                                                            numSuccess++;
                                                        } catch (SQLException e1) {
                                                            numFailed++;
                                                            System.err.println(psCodes[codesI]);
                                                        }
                                                    }
                                                }

                                                for (Map.Entry<MyTable, PreparedStatement> entry : psForeign
                                                        .entrySet()) {
                                                    MyTable myT1 = entry.getKey();
                                                    MyTable[] foreignTs = myT.getForeignFields();
                                                    for (int ii = 0; ii < foreignTs.length; ii++) {
                                                        if (foreignTs[ii] != null
                                                                && foreignTs[ii].equals(myT1)) {
                                                            if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT
                                                                int lID = DBKernel
                                                                        .getLastInsertedID(psForeign.get(myT1));
                                                                // Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt
                                                                DBKernel.sendRequest("UPDATE "
                                                                        + DBKernel.delimitL(tableName) + " SET "
                                                                        + DBKernel.delimitL(
                                                                                myT.getFieldNames()[ii])
                                                                        + "=" + lID + " WHERE "
                                                                        + DBKernel.delimitL("ID") + "="
                                                                        + lastID, false);
                                                            }
                                                            break;
                                                        }
                                                    }
                                                }
                                                /*
                                                 * for (int j=0;j<numCols;j++) {
                                                 * if (myForeignTables[j] !=
                                                 * null && ffieldTypes[j] !=
                                                 * null) { MyTable[] foreignTs =
                                                 * myT.getForeignFields(); for
                                                 * (int
                                                 * ii=0;ii<foreignTs.length;
                                                 * ii++) { if (foreignTs[ii] !=
                                                 * null && foreignTs[ii].equals(
                                                 * myForeignTables[j])) { if
                                                 * (psForeign
                                                 * .get(myForeignTables
                                                 * [j]).executeUpdate() > 0) {
                                                 * // INSERT int lID =
                                                 * DBKernel.getLastInsertedID
                                                 * (psForeign
                                                 * .get(myForeignTables[j]));
                                                 * DBKernel
                                                 * .sendRequest("UPDATE " +
                                                 * DBKernel.delimitL(tableName)
                                                 * + " SET " +
                                                 * DBKernel.delimitL(
                                                 * myT.getFieldNames()[ii]) +
                                                 * "=" + lID + " WHERE " +
                                                 * DBKernel.delimitL("ID") + "="
                                                 * + lastID, false); } break; }
                                                 * } } }
                                                 */
                                            }
                                        } catch (Exception e1) {
                                            numFailed++;
                                            MyLogger.handleMessage(ps.toString());
                                            MyLogger.handleException(e1);
                                        }
                                    }
                                    if (progress != null) {
                                        lfd++;
                                        progress.setValue(lfd);
                                    }
                                }
                            }

                            myT.doMNs();
                            if (progress != null) {
                                // Refreshen:
                                MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable();
                                if (myDB.getActualTable() != null) {
                                    String actTablename = myDB.getActualTable().getTablename();
                                    if (actTablename.equals(tableName)
                                            || actTablename.equals(DBKernel.getCodesName(tableName))) {
                                        myDB.setTable(myDB.getActualTable());
                                    }
                                }
                                MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree();
                                if (myTR.getActualTable() != null) {
                                    String actTablename = myTR.getActualTable().getTablename();
                                    if (actTablename.equals(tableName)
                                            || actTablename.equals(DBKernel.getCodesName(tableName))) {
                                        myTR.setTable(myTR.getActualTable());
                                    }
                                }
                            }
                        } else {
                            System.err.println(tableName + " nicht in DB???");
                        }
                    }
                    if (progress != null) {
                        progress.setVisible(false);
                    }
                    String log = numSuccess + " erfolgreiche Importe.\n";
                    log += numFailed + " fehlgeschlagene Importe.\n";
                    if (unusedFields.length() > 0)
                        log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n";
                    if (showResults) {
                        InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null);
                        ib.setVisible(true);
                    } else {
                        System.out.println("GeneralXLSImporter (" + filename + "):\n" + log);
                    }
                } catch (Exception e) {
                    MyLogger.handleException(e);
                }
            } catch (Exception e) {
                MyLogger.handleException(e);
            }
        }
    };

    Thread thread = new Thread(runnable);
    thread.start();
    try {
        thread.join();
    } catch (InterruptedException e) {
        MyLogger.handleException(e);
    }
    return true;
}

From source file:de.cenote.jasperstarter.ReportNGTest.java

License:Apache License

/**
 * Test of exportXls method, of class Report.
 *//*  w w  w .  j  a va  2s . c  o  m*/
@Test(dependsOnMethods = { "testFill" })
public void testExportXls() throws Exception {
    System.out.println("exportXls");
    Config config = null;
    config = new Config();
    config.input = "target/test-classes/reports/compileToFile.jrprint";
    Report instance = new Report(config, new File(config.getInput()));
    instance.exportXls();
    assertEquals(((File) new File("target/test-classes/reports/compileToFile.xls")).exists(), true);
    // Read the content of a cell:
    InputStream inputStream = new FileInputStream("target/test-classes/reports/compileToFile.xls");
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0); // first sheet
    // select cell C12
    HSSFRow row = sheet.getRow(11);
    HSSFCell cell = row.getCell(2);
    assertEquals(cell.getStringCellValue(), "Carl Grant");
}

From source file:de.cenote.jasperstarter.ReportNGTest.java

License:Apache License

/**
 * Test of exportXlsMeta method, of class Report.
 *//*from  ww  w  .ja v  a  2 s . co  m*/
@Test(dependsOnMethods = { "testFillMeta" })
public void testExportXlsMeta() throws Exception {
    System.out.println("exportXlsMeta");
    Config config = null;
    config = new Config();
    config.input = "target/test-classes/reports/csvMeta.jrprint";
    Report instance = new Report(config, new File(config.getInput()));
    instance.exportXlsMeta();
    assertEquals(((File) new File("target/test-classes/reports/csvMeta.xls")).exists(), true);
    // Read the content of a cell:
    InputStream inputStream = new FileInputStream("target/test-classes/reports/csvMeta.xls");
    HSSFWorkbook wb = new HSSFWorkbook(inputStream);
    HSSFSheet sheet = wb.getSheetAt(0); // first sheet
    // select cell C12
    HSSFRow row = sheet.getRow(11);
    HSSFCell cell = row.getCell(2);
    assertEquals(cell.getStringCellValue(), "Dampremy");
}

From source file:de.fionera.javamailer.dataProcessors.parseFilesForImport.java

/**
 * Gets a XLS file and parse it//from  ww w.ja  v a2 s .co m
 * @param file The XLS File that you want to get parsed
 * @return A ArrayList where the first object is a Array containing the Data and the Second the Header
 */
public ArrayList<Object> parseXLSFile(File file) {
    int index = -1;
    HSSFWorkbook workbook = null;
    try {
        try {
            FileInputStream inputStream = new FileInputStream(file);
            workbook = new HSSFWorkbook(inputStream);
        } catch (IOException ex) {
            ex.printStackTrace();
        }

        assert workbook != null;
        String[] strings = new String[workbook.getNumberOfSheets()];
        //get all sheet names from selected workbook
        for (int i = 0; i < strings.length; i++) {
            strings[i] = workbook.getSheetName(i);
        }
        JFrame frame = new JFrame("Input Dialog");

        String selectedsheet = (String) JOptionPane.showInputDialog(frame,
                "Which worksheet you want to import ?", "Select Worksheet", JOptionPane.QUESTION_MESSAGE, null,
                strings, strings[0]);

        if (selectedsheet != null) {
            for (int i = 0; i < strings.length; i++) {
                if (workbook.getSheetName(i).equalsIgnoreCase(selectedsheet))
                    index = i;
            }
            HSSFSheet sheet = workbook.getSheetAt(index);
            HSSFRow row = sheet.getRow(0);

            if (row != null) {
                headers = new String[row.getLastCellNum()];

                for (int i = 0; i < row.getLastCellNum(); i++) {
                    headers[i] = row.getCell(i).toString();
                }
            }

            data = new String[sheet.getLastRowNum()][];
            for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
                row = sheet.getRow(j);
                int rowCount = row.getLastCellNum();
                String[] dataRow = new String[rowCount];
                for (int i = 0; i < rowCount; i++) {
                    HSSFCell cell = row.getCell(i, org.apache.poi.ss.usermodel.Row.CREATE_NULL_AS_BLANK);
                    dataRow[i] = cell.toString();
                }
                data[j - 1] = dataRow;
            }
        } else {
            return null;
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    ArrayList<Object> returnData = new ArrayList<>();
    returnData.add(data);
    returnData.add(headers);

    return returnData;
}

From source file:de.ma.it.common.excel.ExcelFileManager.java

License:Open Source License

/**
 * //  w w  w .ja  v  a 2s. com
 * @param sheet
 * @param rowIdx
 * @return
 */
public HSSFRow getRow(HSSFSheet sheet, int rowIdx) {
    HSSFRow row = null;
    try {
        row = sheet.getRow(rowIdx);
    } catch (NullPointerException ex) {
        // Occurs if row isn't present, is deliberately swallowed -> method returns null
    }

    return row;
}

From source file:DomainToolCore.Report.XLSSubDomains.java

License:Open Source License

private void SetDomain(HSSFSheet sh) {
    sh.createRow(1).createCell(0).setCellValue("Domain: " + domainxls);
    sh.getRow(1).getCell(0).setCellStyle(setBold());

}

From source file:DomainToolCore.Report.XLSSubDomains.java

License:Open Source License

private void SetDomainIP(HSSFSheet sh2) {
    sh2.createRow(2).createCell(0).setCellValue("Domain IP: " + ip_domainxls);
    sh2.getRow(2).getCell(0).setCellStyle(setBold());
}