Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. getSheet

Introduction

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

Prototype


@Override
public HSSFSheet getSheet(String name)

Source Link

Document

Get sheet with the given name (case insensitive match)

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);/* ww w .  ja va2 s  . co  m*/
    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 w  w w. j  a  v  a  2s.  co m
    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:eafit.cdei.asignacion.input.ReadCurrentCourses.java

public List<Teacher> loadCurrentOffering() throws Exception {

    try {/*from   ww  w .  j  a  v  a  2 s  . c om*/
        FileInputStream fileInputStream = new FileInputStream("current_classes.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("currentOffering");

        boolean keepDoing = true;
        for (int i = 0; i < worksheet.getLastRowNum() + 1; i++) {

            HSSFRow row1 = worksheet.getRow(i);

            HSSFCell cellF1 = row1.getCell((short) 5);
            String f1Val = ((int) cellF1.getNumericCellValue()) + "";

            HSSFCell cellW1 = row1.getCell((short) 22);
            String w1Val = cellW1.getStringCellValue();

            HSSFCell cellX1 = row1.getCell((short) 23);
            String x1Val = cellX1.getStringCellValue();

            HSSFCell cellY1 = row1.getCell((short) 24);
            String y1Val = cellY1.getStringCellValue();

            HSSFCell cellAU1 = row1.getCell((short) 46);
            String au1Val = ((int) cellAU1.getNumericCellValue()) + "";

            HSSFCell cellAW1 = row1.getCell((short) 48);
            String aw1Val = cellAW1.getStringCellValue();

            if (f1Val == null || f1Val.length() == 0 || f1Val.equals("0")) {
                keepDoing = false;
            }

            Course c = new Course();

            c.setHourStart(f1Val);
            c.setHourEnd(au1Val);
            c.setDowList(generateDaysList(aw1Val));
            c.setNameCourse(x1Val);
            c.setCodeCourse(w1Val);
            c.setCodeCourse(y1Val);

            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HHmm");
            if (au1Val.length() == 3) {
                au1Val = "0" + au1Val;
            }

            if (f1Val.length() == 3) {
                f1Val = "0" + f1Val;
            }

            c.setHourEndLocal(LocalTime.parse(au1Val, dtf));
            c.setHourStartLocal(LocalTime.parse(f1Val, dtf));

            c.setDateStartLocal(LocalTime.parse(au1Val, dtf));
            c.setDateEndLocal(LocalTime.parse(aw1Val, dtf));

            System.out.println(c);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return null;
}

From source file:eafit.cdei.asignacion.input.ReadCurrentOffering.java

public List<Teacher> loadCurrentOffering() throws Exception {

    try {/* w w w .j  a  va 2  s. co  m*/
        FileInputStream fileInputStream = new FileInputStream("currentOffering.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("currentOffering");

        boolean keepDoing = true;
        for (int i = 0; i < worksheet.getLastRowNum() + 1; i++) {

            HSSFRow row1 = worksheet.getRow(i);

            HSSFCell cellF1 = row1.getCell((short) 5);
            String f1Val = ((int) cellF1.getNumericCellValue()) + "";

            HSSFCell cellW1 = row1.getCell((short) 22);
            String w1Val = cellW1.getStringCellValue();

            HSSFCell cellX1 = row1.getCell((short) 23);
            String x1Val = cellX1.getStringCellValue();

            HSSFCell cellY1 = row1.getCell((short) 24);
            String y1Val = cellY1.getStringCellValue();

            HSSFCell cellAU1 = row1.getCell((short) 46);
            String au1Val = ((int) cellAU1.getNumericCellValue()) + "";

            HSSFCell cellAW1 = row1.getCell((short) 48);
            String aw1Val = cellAW1.getStringCellValue();

            HSSFCell cellAB = row1.getCell((short) 27);
            String abVal = cellAB.getStringCellValue();

            HSSFCell cellAC = row1.getCell((short) 28);
            String acVal = cellAC.getStringCellValue();

            if (f1Val == null || f1Val.length() == 0 || f1Val.equals("0")) {
                keepDoing = false;
            }

            Course c = new Course();

            c.setHourStart(f1Val);
            c.setHourEnd(au1Val);
            c.setDowList(generateDaysList(aw1Val));
            c.setNameCourse(x1Val);
            c.setCodeCourse(w1Val);
            c.setCodeCourse(y1Val);

            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HHmm");
            if (au1Val.length() == 3) {
                au1Val = "0" + au1Val;
            }

            if (f1Val.length() == 3) {
                f1Val = "0" + f1Val;
            }

            c.setHourEndLocal(LocalTime.parse(au1Val, dtf));
            c.setHourStartLocal(LocalTime.parse(f1Val, dtf));

            DateTimeFormatter dtf1 = DateTimeFormatter.ofPattern("yyyyMMdd");

            //c.setDateStart(LocalDate.parse(abVal,dtf1));
            //c.setHourStartLocal(LocalTime.parse(f1Val,dtf));

            System.out.println(c);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return null;
}

From source file:eafit.cdei.asignacion.input.ReadTeacherAvailability.java

@Override
public List<Teacher> loadTeacherAvailability() throws Exception {

    try {/* w ww.  ja v  a  2  s  .c om*/
        FileInputStream fileInputStream = new FileInputStream("googleDoc.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Form Responses 1");

        boolean keepDoing = true;
        for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) {

            HSSFRow row1 = worksheet.getRow(i);

            HSSFCell cellB1 = row1.getCell((short) 1);
            String b1Val = cellB1.getStringCellValue() + "";

            HSSFCell cellG1 = row1.getCell((short) 6);
            String g1Val = cellG1.getStringCellValue() + "";

            HSSFCell cellH1 = row1.getCell((short) 7);
            String h1Val = cellH1.getStringCellValue() + "";

            HSSFCell cellJ1 = row1.getCell((short) 8);
            String j1Val = cellJ1.getStringCellValue() + "";

            HSSFCell cellK1 = row1.getCell((short) 9);
            String k1Val = cellK1.getStringCellValue() + "";

            HSSFCell cellI1 = row1.getCell((short) 10);
            String i1Val = cellI1.getStringCellValue() + "";

            HSSFCell cellL1 = row1.getCell((short) 11);
            String l1Val = cellL1.getStringCellValue() + "";

            HSSFCell cellM1 = row1.getCell((short) 12);
            String m1Val = cellM1.getStringCellValue() + "";

            HSSFCell cellN1 = row1.getCell((short) 13);
            String n1Val = cellN1.getStringCellValue() + "";

            HSSFCell cellO1 = row1.getCell((short) 14);
            String o1Val = cellO1.getStringCellValue() + "";

            HSSFCell cellP1 = row1.getCell((short) 15);
            String p1Val = cellP1.getStringCellValue() + "";

            HSSFCell cellQ1 = row1.getCell((short) 16);
            String q1Val = cellQ1.getStringCellValue() + "";

            HSSFCell cellR1 = row1.getCell((short) 17);
            String r1Val = cellR1.getStringCellValue() + "";

            HSSFCell cellS1 = row1.getCell((short) 18);
            String s1Val = cellS1.getStringCellValue() + "";

            HSSFCell cellT1 = row1.getCell((short) 19);
            String t1Val = cellT1.getStringCellValue() + "";

            HSSFCell cellU1 = row1.getCell((short) 20);
            String u1Val = cellU1.getStringCellValue() + "";

            HSSFCell cellV1 = row1.getCell((short) 21);
            String v1Val = cellV1.getStringCellValue() + "";

            HSSFCell cellW1 = row1.getCell((short) 22);
            String w1Val = cellW1.getStringCellValue() + "";

            HSSFCell cellX1 = row1.getCell((short) 23);
            String x1Val = cellX1.getStringCellValue() + "";

            HSSFCell cellY1 = row1.getCell((short) 24);
            String y1Val = cellY1.getStringCellValue() + "";

            HSSFCell cellZ1 = row1.getCell((short) 25);
            String z1Val = cellZ1.getStringCellValue() + "";

            HSSFCell cellAA1 = row1.getCell((short) 26);
            String aa1Val = cellAA1.getStringCellValue() + "";

            HSSFCell cellAB1 = row1.getCell((short) 27);
            String ab1Val = cellAB1.getStringCellValue() + "";

            if (b1Val.length() == 0) {
                keepDoing = false;
            } else {
                Teacher t = new Teacher();
                t.setFullName(b1Val);

                t.addCourseAvaliability(generateMTF(getAnswer(g1Val), "06", "08"));
                t.addCourseAvaliability(generateMTF(getAnswer(h1Val), "08", "10"));
                t.addCourseAvaliability(generateMTF(getAnswer(i1Val), "10", "12"));
                t.addCourseAvaliability(generateMTF(getAnswer(j1Val), "12", "14"));
                t.addCourseAvaliability(generateMTF(getAnswer(k1Val), "14", "16"));

                t.addCourseAvaliability(generateMWF(getAnswer(l1Val), "06", "08"));
                t.addCourseAvaliability(generateMWF(getAnswer(n1Val), "08", "10"));
                t.addCourseAvaliability(generateMWF(getAnswer(m1Val), "10", "12"));
                t.addCourseAvaliability(generateMWF(getAnswer(o1Val), "12", "14"));
                t.addCourseAvaliability(generateMWF(getAnswer(p1Val), "14", "16"));

                t.addCourseAvaliability(generateMTT(getAnswer(q1Val), "18", "15", "20", "45"));
                t.addCourseAvaliability(generateMTW(getAnswer(r1Val), "18", "30", "20", "30"));

                t.addCourseAvaliability(generateTTT(getAnswer(s1Val), "10", "12"));
                t.addCourseAvaliability(generateTTT(getAnswer(t1Val), "12", "14"));
                t.addCourseAvaliability(generateTTT(getAnswer(u1Val), "14", "16"));
                t.addCourseAvaliability(generateTTT(getAnswer(v1Val), "18", "30", "20", "30"));

                t.addCourseAvaliability(generateTTT(getAnswer(w1Val), "06", "09"));
                t.addCourseAvaliability(generateTTT(getAnswer(x1Val), "09", "12"));

                t.addCourseAvaliability(generateWF(getAnswer(y1Val), "06", "09"));

                t.addCourseAvaliability(generateWF(getAnswer(z1Val), "07", "00", "08", "30"));

                t.addCourseAvaliability(generateTTT(getAnswer(aa1Val), "12", "00", "13", "30"));
                t.addCourseAvaliability(generateTTT(getAnswer(ab1Val), "07", "00", "08", "30"));

                System.out.println(t);
            }

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return null;
}

From source file:eafit.cdei.asignacion.input.ReadTeacherPreferences.java

public List<Teacher> loadTeacherPreferences() throws Exception {

    try {//from ww w  .  j a v a  2 s . c o  m
        FileInputStream fileInputStream = new FileInputStream("TeacherPreferences.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet worksheet = workbook.getSheet("Preferences");

        boolean keepDoing = true;
        for (int i = 1; i < worksheet.getLastRowNum() + 1; i++) {

            HSSFRow row1 = worksheet.getRow(i);
            HSSFCell cellA1 = row1.getCell((short) 0);
            String a1Val = cellA1.getNumericCellValue() + "";
            HSSFCell cellB1 = row1.getCell((short) 1);
            String b1Val = cellB1.getStringCellValue();
            HSSFCell cellC1 = row1.getCell((short) 2);
            String c1Val = cellC1.getStringCellValue();

            String d1Val = "";

            HSSFCell cellD1 = row1.getCell((short) 3);

            if (cellD1 != null) {
                d1Val = cellD1.getStringCellValue();
            }

            HSSFCell cellE1 = row1.getCell((short) 4);
            double e1Val = cellE1.getNumericCellValue();

            if (a1Val == null || a1Val.length() == 0 || a1Val.equals("0")) {
                keepDoing = false;
            }

            Teacher t = new Teacher();
            t.setFullName(b1Val);

            t.setMaxCourses((int) e1Val);

            String[] preferedCourses = c1Val.split(",");

            ArrayList<LevelCourse> preferedCoursed = new ArrayList<LevelCourse>();

            for (String preferedCoursed1 : preferedCourses) {
                LevelCourse lc = new LevelCourse();
                lc.setLevelName(preferedCoursed1);
                preferedCoursed.add(lc);
            }

            String[] preferedTimes = d1Val.split(",");
            ArrayList<String> preferedTime = new ArrayList<>();

            for (String pf : preferedTimes) {

                preferedTime.add(pf);
            }

            t.setListPreferedCourses(preferedCoursed);

            System.out.println(t);

        }

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return null;
}

From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicGovTermController.java

License:Apache License

/**
*   ? //from ww w  .ja v  a  2 s. com
* @param ComDefaultVO - ?    VO
* @param model
* @return "forward:/cms/cmm/selectDicGovTermList.do"
* @exception Exception
*/
@RequestMapping("/cms/cmm/addDicGovTermExcelOK.do")
public String addDicGovTermExcelRegister(final HttpServletRequest request,
        @ModelAttribute("egovOe1DicGovTermVO") EgovOe1DicGovTermVO egovOe1DicGovTermVO,
        BindingResult bindingResult, Model model, SessionStatus status) throws Exception {

    Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated();
    if (!isAuthenticated) {
        return "/cms/com/EgovLoginUsr"; //? ??
    }

    if (bindingResult.hasErrors()) {
        model.addAttribute("egovOe1DicGovTermVO", egovOe1DicGovTermVO);
        return "/cms/cmm/addDicGovTermExcel";
    }

    model.addAttribute("searchMode", egovOe1DicGovTermVO);

    //
    EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser();

    try {

        final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
        final Map<String, MultipartFile> files = multiRequest.getFileMap();

        Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator();
        MultipartFile file;

        String filePath = "";

        while (itr.hasNext()) {
            Entry<String, MultipartFile> entry = itr.next();

            file = entry.getValue();
            if (!"".equals(file.getOriginalFilename())) {

                String _storePath = propertyService.getString("Globals.fileStorePath");

                File saveFolder = new File(_storePath);
                if (!saveFolder.exists() || saveFolder.isFile()) {
                    saveFolder.mkdirs();
                }

                String orginFileName = file.getOriginalFilename();
                int _index = orginFileName.lastIndexOf(".");
                String fileName = orginFileName.substring(0, _index);
                String fileExt = orginFileName.substring(_index + 1);
                long _size = file.getSize();

                if (!"".equals(orginFileName)) {
                    filePath = _storePath + File.separator + "EgovStandardTermStatus.xls";
                    file.transferTo(new File(filePath));
                }

                //System.out.println("?=====>"+orginFileName);
                //System.out.println("?=====>"+fileName);
                //System.out.println("?=====>"+fileExt);
                //System.out.println("??=====>"+_size);
                //System.out.println("??=====>"+filePath);

                if ((fileExt.equals("xls") || fileExt.equals("xlsx"))
                        && (filePath != null && !"".equals(filePath))) {

                    HSSFWorkbook wbT = excelService.loadWorkbook(filePath);
                    HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0));

                    HSSFRow rowValue = sheetT.getRow(1);
                    HSSFCell cellValue1 = rowValue.getCell(1); //
                    HSSFCell cellValue2 = rowValue.getCell(2); //
                    HSSFCell cellValue3 = rowValue.getCell(3); //?
                    HSSFCell cellValue4 = rowValue.getCell(4); //?
                    HSSFCell cellValue5 = rowValue.getCell(5); //?
                    HSSFCell cellValue6 = rowValue.getCell(6); //?
                    HSSFCell cellValue8 = rowValue.getCell(8); //?
                    String AdministWordSe = (cellValue1 + "").trim();
                    String AdministWordNm = (cellValue2 + "").trim();
                    String AdministWordEngNm = (cellValue3 + "").trim();
                    String AdministWordAbrv = (cellValue4 + "").trim();
                    String AdministWordDc = (cellValue5 + "").trim();
                    String ThemaRelm = (cellValue6 + "").trim();
                    String UseAt = (cellValue8 + "").trim();

                    //System.out.println("AdministWordSe====>"+AdministWordSe);
                    //System.out.println("AdministWordNm====>"+AdministWordNm);
                    //System.out.println("AdministWordEngNm====>"+AdministWordEngNm);
                    //System.out.println("AdministWordAbrv====>"+AdministWordAbrv);
                    //System.out.println("AdministWordDc====>"+AdministWordDc);
                    //System.out.println("ThemaRelm====>"+ThemaRelm);
                    //System.out.println("UseAt====>"+UseAt);

                    if (!"".equals(AdministWordSe) || !"".equals(AdministWordNm)
                            || !"?".equals(AdministWordEngNm)
                            || !"?".equals(AdministWordAbrv) || !"?".equals(AdministWordDc)
                            || !"?".equals(ThemaRelm) || !"?".equals(UseAt)) {
                        model.addAttribute("resultMsg",
                                " EXCEL ?? .   .");
                        return "/cms/cmm/EgovDicGovTermExcelRegist";
                    }

                    List<EgovOe1DicGovTermVO> dicGovTermListVO = new ArrayList<EgovOe1DicGovTermVO>();
                    for (int i = 2; i <= sheetT.getLastRowNum(); i++) {
                        HSSFRow row1 = sheetT.getRow(i);

                        HSSFCell cell1 = row1.getCell(1); //(/??)
                        HSSFCell cell2 = row1.getCell(2); //
                        HSSFCell cell3 = row1.getCell(3); //?
                        HSSFCell cell4 = row1.getCell(4); //?
                        HSSFCell cell5 = row1.getCell(5); //
                        HSSFCell cell6 = row1.getCell(6); //?
                        HSSFCell cell8 = row1.getCell(8); //

                        AdministWordSe = (cell1 + "").trim();
                        AdministWordNm = (cell2 + "").trim();
                        AdministWordEngNm = (cell3 + "").trim();
                        AdministWordAbrv = (cell4 + "").trim();
                        AdministWordDc = (cell5 + "").trim();
                        ThemaRelm = (cell6 + "").trim();
                        UseAt = (cell8 + "").trim();

                        if (("".equals(AdministWordSe) || "??".equals(AdministWordSe))
                                && (!"".equals(AdministWordSe) && AdministWordSe != null)
                                && (!"".equals(AdministWordNm) && AdministWordNm != null)
                                && (!"".equals(AdministWordEngNm) && AdministWordEngNm != null)
                                && (!"".equals(AdministWordAbrv) && AdministWordAbrv != null)
                                && (!"".equals(AdministWordDc) && AdministWordDc != null)) {
                            EgovOe1DicGovTermVO egovOe1DicGovTermExcelVO = new EgovOe1DicGovTermVO();
                            if ("".equals(AdministWordSe)) {
                                egovOe1DicGovTermExcelVO.setAdministWordSe("001");
                            } else {
                                egovOe1DicGovTermExcelVO.setAdministWordSe("002");
                            }
                            egovOe1DicGovTermExcelVO.setAdministWordNm(AdministWordNm);
                            egovOe1DicGovTermExcelVO.setAdministWordEngNm(AdministWordEngNm);
                            egovOe1DicGovTermExcelVO.setAdministWordAbrv(AdministWordAbrv);
                            egovOe1DicGovTermExcelVO.setAdministWordDc(AdministWordDc);
                            egovOe1DicGovTermExcelVO.setThemaRelm(ThemaRelm);
                            if (UseAt.equals("")) {
                                egovOe1DicGovTermExcelVO.setUseAt("Y");
                            } else {
                                egovOe1DicGovTermExcelVO.setUseAt("N");
                            }
                            egovOe1DicGovTermExcelVO.setFrstRegisterId(user.getMberId());
                            dicGovTermListVO.add(egovOe1DicGovTermExcelVO);

                            for (int kk = 0; kk < dicGovTermListVO.size(); kk++) {
                                if (kk > 1) {
                                    if (dicGovTermListVO.get(kk - 1).getAdministWordNm().trim()
                                            .equals(AdministWordNm)
                                            && dicGovTermListVO.get(kk - 1).getAdministWordEngNm().trim()
                                                    .equals(AdministWordEngNm)
                                            && dicGovTermListVO.get(kk - 1).getAdministWordAbrv().trim()
                                                    .equals(AdministWordAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? , ?, ?? . ?    .");
                                        return "/cms/cmm/EgovDicGovTermExcelRegist";
                                    }
                                } else if (kk == 1) {
                                    if (dicGovTermListVO.get(kk - 1).getAdministWordNm().trim()
                                            .equals(AdministWordNm)
                                            && dicGovTermListVO.get(kk - 1).getAdministWordEngNm().trim()
                                                    .equals(AdministWordEngNm)
                                            && dicGovTermListVO.get(kk - 1).getAdministWordAbrv().trim()
                                                    .equals(AdministWordAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? , ?, ?? . ?    .");
                                        return "/cms/cmm/EgovDicGovTermExcelRegist";
                                    }
                                }
                            }

                        } else {
                            if (!"".equals(AdministWordSe) && !"??".equals(AdministWordSe)) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "???  ? /??  . ?    .");
                            }
                            if ("".equals(AdministWordSe) || AdministWordSe == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "???  ? . ?    .");
                            }
                            if ("".equals(AdministWordNm) || AdministWordNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            if ("".equals(AdministWordEngNm) || AdministWordEngNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ?? . ?    .");
                            }
                            if ("".equals(AdministWordAbrv) || AdministWordAbrv == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            if ("".equals(AdministWordDc) || AdministWordDc == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            return "/cms/cmm/EgovDicGovTermExcelRegist";
                        }
                    }

                    // ? ??    .
                    egovOe1DicGovTermService.deleteExcelDicGovTerm();

                    List<EgovOe1DicGovTermVO> list = dicGovTermListVO;

                    for (EgovOe1DicGovTermVO dicGovTermVo : list) {
                        egovOe1DicGovTermService.insertExcelDicGovTerm(dicGovTermVo);
                    }

                } else {
                    model.addAttribute("resultMsg",
                            " EXCEL ?? .   .");
                    return "/cms/cmm/EgovDicGovTermExcelRegist";
                }
            }
        }

        status.setComplete();

        model.addAttribute("resultMsg", " Excel ? ?  ");

        return "forward:/cms/cmm/selectDicGovTermList.do";

    } catch (Exception ex) {

        model.addAttribute("resultMsg",
                " Excel ? ? ,  Excel ? ? ?. ");

        return "/cms/cmm/EgovDicGovTermExcelRegist";
    }
}

From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicTermController.java

License:Apache License

/**
*   ? /*from   ww  w . j a v  a2  s. co  m*/
* @param ComDefaultVO - ?    VO
* @param model
* @return "forward:/cms/cmm/selectDicTermList.do"
* @exception Exception
*/
@RequestMapping("/cms/cmm/addDicTermExcelOK.do")
public String addDicTermExcelRegister(final HttpServletRequest request,
        @ModelAttribute("egovOe1DicTermVO") EgovOe1DicTermVO egovOe1DicTermVO, BindingResult bindingResult,
        Model model, SessionStatus status) throws Exception {

    Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated();
    if (!isAuthenticated) {
        return "/cms/com/EgovLoginUsr"; //? ??
    }

    if (bindingResult.hasErrors()) {
        model.addAttribute("egovOe1DicTermVO", egovOe1DicTermVO);
        return "/cms/cmm/addDicTermExcel";
    }

    model.addAttribute("searchMode", egovOe1DicTermVO);

    //
    EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser();

    try {

        final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
        final Map<String, MultipartFile> files = multiRequest.getFileMap();

        Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator();
        MultipartFile file;

        String filePath = "";

        while (itr.hasNext()) {
            Entry<String, MultipartFile> entry = itr.next();

            file = entry.getValue();
            if (!"".equals(file.getOriginalFilename())) {

                String _storePath = propertyService.getString("Globals.fileStorePath");

                File saveFolder = new File(_storePath);
                if (!saveFolder.exists() || saveFolder.isFile()) {
                    saveFolder.mkdirs();
                }

                String orginFileName = file.getOriginalFilename();
                int _index = orginFileName.lastIndexOf(".");
                String fileName = orginFileName.substring(0, _index);
                String fileExt = orginFileName.substring(_index + 1);
                long _size = file.getSize();

                if (!"".equals(orginFileName)) {
                    filePath = _storePath + File.separator + "StandardTermStatus.xls";
                    file.transferTo(new File(filePath));
                }

                //                  System.out.println("?=====>"+orginFileName);
                //                  System.out.println("?=====>"+fileName);
                //                  System.out.println("?=====>"+fileExt);
                //                  System.out.println("??=====>"+_size);
                //                  System.out.println("??=====>"+filePath);

                if ((fileExt.equals("xls") || fileExt.equals("xlsx"))
                        && (filePath != null && !"".equals(filePath))) {

                    HSSFWorkbook wbT = excelService.loadWorkbook(filePath);
                    HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0));

                    HSSFRow rowValue = sheetT.getRow(2);
                    HSSFCell cellValue0 = rowValue.getCell(0); //
                    HSSFCell cellValue1 = rowValue.getCell(1); //
                    HSSFCell cellValue2 = rowValue.getCell(2); //?(Full Name)
                    HSSFCell cellValue3 = rowValue.getCell(3); //?
                    HSSFCell cellValue4 = rowValue.getCell(4); //
                    HSSFCell cellValue6 = rowValue.getCell(6); //?
                    String synonm = (cellValue0 + "").trim();
                    String wordNm = (cellValue1 + "").trim();
                    String engNm = (cellValue2 + "").trim();
                    String wordNmEngAbrv = (cellValue3 + "").trim();
                    String wordDc = (cellValue4 + "").trim();
                    String useAt = (cellValue6 + "").trim();

                    //System.out.println("synonm====>"+synonm);
                    //System.out.println("wordNm====>"+wordNm);
                    //System.out.println("engNm====>"+engNm);
                    //System.out.println("wordNmEngAbrv====>"+wordNmEngAbrv);
                    //System.out.println("wordDc====>"+wordDc);
                    //System.out.println("useAt====>"+useAt);

                    if (!"".equals(synonm) || !"".equals(wordNm)
                            || !"?(Full Name)".equals(engNm) || !"?".equals(wordNmEngAbrv)
                            || !"".equals(wordDc) || !"?".equals(useAt)) {
                        model.addAttribute("resultMsg",
                                " EXCEL ?? .   .");
                        return "/cms/cmm/EgovDicTermExcelRegist";
                    }

                    List<EgovOe1DicTermVO> dicTermListVO = new ArrayList<EgovOe1DicTermVO>();

                    for (int i = 3; i <= sheetT.getLastRowNum(); i++) {

                        HSSFRow row1 = sheetT.getRow(i);

                        HSSFCell cell0 = row1.getCell(0); //(/??)
                        HSSFCell cell1 = row1.getCell(1); //
                        HSSFCell cell2 = row1.getCell(2); //?
                        HSSFCell cell3 = row1.getCell(3); //?
                        HSSFCell cell4 = row1.getCell(4); //
                        HSSFCell cell6 = row1.getCell(6); //

                        synonm = (cell0 + "").trim();
                        wordNm = (cell1 + "").trim();
                        engNm = (cell2 + "").trim();
                        wordNmEngAbrv = (cell3 + "").trim();
                        wordDc = (cell4 + "").trim();
                        useAt = (cell6 + "").trim();

                        //                        System.out.println("synonm=======>"+synonm);
                        //                        System.out.println("wordNm=======>"+wordNm);
                        //                        System.out.println("engNm=======>"+engNm);
                        //                        System.out.println("wordNmEngAbrv=======>"+wordNmEngAbrv);
                        //                        System.out.println("wordDc=======>"+wordDc);
                        //                        System.out.println("useAt=======>"+useAt);

                        if (("".equals(synonm) || "??".equals(synonm))
                                && (!"".equals(synonm) && synonm != null)
                                && (!"".equals(wordNm) && wordNm != null)
                                && (!"".equals(engNm) && engNm != null)
                                && (!"".equals(wordNmEngAbrv) && wordNmEngAbrv != null)) {
                            EgovOe1DicTermVO egovOe1DicTermExcelVO = new EgovOe1DicTermVO();
                            egovOe1DicTermExcelVO.setSynonm(synonm);
                            egovOe1DicTermExcelVO.setWordNm(wordNm);
                            egovOe1DicTermExcelVO.setEngNm(engNm);
                            egovOe1DicTermExcelVO.setWordNmEngAbrv(wordNmEngAbrv);
                            egovOe1DicTermExcelVO.setWordDc(wordDc);
                            if (useAt.equals("")) {
                                egovOe1DicTermExcelVO.setUseAt("Y");
                            } else {
                                egovOe1DicTermExcelVO.setUseAt("N");
                            }
                            egovOe1DicTermExcelVO.setFrstRegisterId(user.getMberId());

                            dicTermListVO.add(egovOe1DicTermExcelVO);

                            for (int kk = 0; kk < dicTermListVO.size(); kk++) {
                                if (kk > 1) {
                                    //                                   System.out.println("synonm=======>"+egovOe1DicTermExcelVO.getSynonm());
                                    //                                   System.out.println("wordNm=======>"+egovOe1DicTermExcelVO.getWordNm());
                                    //                                   System.out.println("engNm=======>"+egovOe1DicTermExcelVO.getEngNm());
                                    //                                   System.out.println("wordNmEngAbrv=======>"+egovOe1DicTermExcelVO.getWordNmEngAbrv());
                                    //                                   System.out.println("wordDc=======>"+egovOe1DicTermExcelVO.getWordDc());
                                    //                                   System.out.println("useAt=======>"+egovOe1DicTermExcelVO.getUseAt());
                                    //                                   System.out.println("dicTermListVO.get(kk-1).getWordNm().trim()====>"+dicTermListVO.get(kk-1).getWordNm().trim());
                                    //                                  System.out.println("kk=================="+kk);
                                    //                                   System.out.println("jj=================="+i);
                                    //                                   System.out.println("============================================");
                                    if (dicTermListVO.get(kk - 1).getWordNm().trim().equals(wordNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                    if (dicTermListVO.get(kk - 1).getEngNm().trim().equals(engNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?(Full Name)? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                    if (dicTermListVO.get(kk - 1).getWordNmEngAbrv().trim()
                                            .equals(wordNmEngAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                } else if (kk == 1) {
                                    //                                   System.out.println("synonm=======>"+egovOe1DicTermExcelVO.getSynonm());
                                    //                                   System.out.println("wordNm=======>"+egovOe1DicTermExcelVO.getWordNm());
                                    //                                   System.out.println("engNm=======>"+egovOe1DicTermExcelVO.getEngNm());
                                    //                                   System.out.println("wordNmEngAbrv=======>"+egovOe1DicTermExcelVO.getWordNmEngAbrv());
                                    //                                   System.out.println("wordDc=======>"+egovOe1DicTermExcelVO.getWordDc());
                                    //                                   System.out.println("useAt=======>"+egovOe1DicTermExcelVO.getUseAt());
                                    //                                   System.out.println("dicTermListVO.get(kk-1).getWordNm().trim()====>"+dicTermListVO.get(kk-1).getWordNm().trim());
                                    //                                  System.out.println("kk=================="+i);
                                    //                                   System.out.println("============================================");
                                    if (dicTermListVO.get(kk - 1).getWordNm().trim().equals(wordNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                    if (dicTermListVO.get(kk - 1).getEngNm().trim().equals(engNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?(Full Name)? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                    if (dicTermListVO.get(kk - 1).getWordNmEngAbrv().trim()
                                            .equals(wordNmEngAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?? . ?    .");
                                        return "/cms/cmm/EgovDicTermExcelRegist";
                                    }
                                }
                            }

                        } else {
                            if (!"".equals(synonm) && !"??".equals(synonm)) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "???  ? /??  . ?    .");
                            }
                            if ("".equals(synonm) || synonm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "???  ? . ?    .");
                            }
                            if ("".equals(wordNm) || wordNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            if ("".equals(engNm) || engNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ?(Full Name)? . ?    .");
                            }
                            if ("".equals(wordNmEngAbrv) || wordNmEngAbrv == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            if ("".equals(wordDc) || wordDc == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            return "/cms/cmm/EgovDicTermExcelRegist";
                        }
                    }

                    // ? ??    .
                    egovOe1DicTermService.deleteExcelDicTerm();

                    List<EgovOe1DicTermVO> list = dicTermListVO;

                    for (EgovOe1DicTermVO dicTermVO : list) {

                        if ("??".equals(dicTermVO.getSynonm())) {
                            egovOe1DicTermService.insertExcelDicTermSynonm(dicTermVO);
                        } else {
                            egovOe1DicTermService.insertExcelDicTerm(dicTermVO);
                        }
                    }

                } else {
                    model.addAttribute("resultMsg",
                            " EXCEL ?? .   .");
                    return "/cms/cmm/EgovDicTermExcelRegist";
                }
            }
        }

        status.setComplete();

        model.addAttribute("resultMsg", " Excel ? ?  ");

        return "forward:/cms/cmm/selectDicTermList.do";

    } catch (Exception ex) {

        model.addAttribute("resultMsg",
                " Excel ? ? ,  Excel ? ? ?. ");

        return "/cms/cmm/EgovDicTermExcelRegist";
    }
}

From source file:egovframework.oe1.cms.cmm.web.EgovOe1DicWordController.java

License:Apache License

/**
*   ? /*from  ww w. jav  a2 s .c  om*/
* @param EgovOe1DicWordVO - ?    VO
* @param model
* @return "forward:/cms/cmm/selectDicWordList.do"
* @exception Exception
*/
@RequestMapping("/cms/cmm/addDicWordExcelOK.do")
public String addDicWordExcelRegister(final HttpServletRequest request,
        @ModelAttribute("egovOe1DicWordVO") EgovOe1DicWordVO egovOe1DicWordVO, BindingResult bindingResult,
        Model model, SessionStatus status) throws Exception {

    Boolean isAuthenticated = EgovUserDetailsHelper.isAuthenticated();
    if (!isAuthenticated) {
        return "/cms/com/EgovLoginUsr"; //? ??
    }

    if (bindingResult.hasErrors()) {
        model.addAttribute("egovOe1DicWordVO", egovOe1DicWordVO);
        return "/cms/cmm/addDicWord";
    }

    model.addAttribute("searchMode", egovOe1DicWordVO);

    //
    EgovOe1LoginVO user = (EgovOe1LoginVO) EgovUserDetailsHelper.getAuthenticatedUser();

    try {

        final MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
        final Map<String, MultipartFile> files = multiRequest.getFileMap();

        Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator();
        MultipartFile file;

        String filePath = "";

        while (itr.hasNext()) {
            Entry<String, MultipartFile> entry = itr.next();

            file = entry.getValue();
            if (!"".equals(file.getOriginalFilename())) {

                String _storePath = propertyService.getString("Globals.fileStorePath");

                File saveFolder = new File(_storePath);
                if (!saveFolder.exists() || saveFolder.isFile()) {
                    saveFolder.mkdirs();
                }

                String orginFileName = file.getOriginalFilename();
                int _index = orginFileName.lastIndexOf(".");
                String fileName = orginFileName.substring(0, _index);
                String fileExt = orginFileName.substring(_index + 1);
                long _size = file.getSize();

                if (!"".equals(orginFileName)) {
                    filePath = _storePath + File.separator + "StandardWordStatus.xls";
                    file.transferTo(new File(filePath));
                }

                //System.out.println("?=====>"+orginFileName);
                //System.out.println("?=====>"+fileName);
                //System.out.println("?=====>"+fileExt);
                //System.out.println("??=====>"+_size);
                //System.out.println("??=====>"+filePath);

                if ((fileExt.equals("xls") || fileExt.equals("xlsx"))
                        && (filePath != null && !"".equals(filePath))) {

                    HSSFWorkbook wbT = excelService.loadWorkbook(filePath);
                    HSSFSheet sheetT = wbT.getSheet(wbT.getSheetName(0));

                    HSSFRow rowValue = sheetT.getRow(2);
                    HSSFCell cellValue0 = rowValue.getCell(0); //
                    HSSFCell cellValue1 = rowValue.getCell(1); //?(Full Name)
                    HSSFCell cellValue2 = rowValue.getCell(2); //?
                    HSSFCell cellValue3 = rowValue.getCell(3); //
                    HSSFCell cellValue5 = rowValue.getCell(5); //?
                    String wordNm = (cellValue0 + "").trim();
                    String engNm = (cellValue1 + "").trim();
                    String wordNmEngAbrv = (cellValue2 + "").trim();
                    String wordDc = (cellValue3 + "").trim();
                    String useAt = (cellValue5 + "").trim();

                    //System.out.println("wordNm====>"+wordNm);
                    //System.out.println("engNm====>"+engNm);
                    //System.out.println("wordNmEngAbrv====>"+wordNmEngAbrv);
                    //System.out.println("wordDc====>"+wordDc);
                    //System.out.println("useAt====>"+useAt);

                    if (!"".equals(wordNm) || !"?(Full Name)".equals(engNm)
                            || !"?".equals(wordNmEngAbrv) || !"".equals(wordDc)
                            || !"?".equals(useAt)) {
                        model.addAttribute("resultMsg",
                                " EXCEL ?? .   .");
                        return "/cms/cmm/EgovDicWordExcelRegist";
                    }

                    List<EgovOe1DicWordVO> dicWordListVO = new ArrayList<EgovOe1DicWordVO>();

                    for (int i = 3; i <= sheetT.getLastRowNum(); i++) {
                        HSSFRow row1 = sheetT.getRow(i);
                        HSSFCell cell0 = row1.getCell(0); //
                        HSSFCell cell1 = row1.getCell(1); //?
                        HSSFCell cell2 = row1.getCell(2); //?
                        HSSFCell cell3 = row1.getCell(3); //
                        HSSFCell cell5 = row1.getCell(5); //

                        wordNm = (cell0 + "").trim();
                        engNm = (cell1 + "").trim();
                        wordNmEngAbrv = (cell2 + "").trim();
                        wordDc = (cell3 + "").trim();
                        useAt = (cell5 + "").trim();

                        if ((!"".equals(wordNm) && wordNm != null) && (!"".equals(engNm) && engNm != null)
                                && (!"".equals(wordNmEngAbrv) && wordNmEngAbrv != null)
                                && (!"".equals(wordDc) && wordDc != null)) {
                            EgovOe1DicWordVO egovOe1DicWordExcelVO = new EgovOe1DicWordVO();
                            egovOe1DicWordExcelVO.setWrdNm(wordNm);
                            egovOe1DicWordExcelVO.setWrdEngNm(engNm);
                            egovOe1DicWordExcelVO.setWrdEngAbrv(wordNmEngAbrv);
                            egovOe1DicWordExcelVO.setWrdDc(wordDc);
                            if (useAt.equals("")) {
                                egovOe1DicWordExcelVO.setUseAt("Y");
                            } else {
                                egovOe1DicWordExcelVO.setUseAt("N");
                            }
                            egovOe1DicWordExcelVO.setFrstRegisterId(user.getMberId());

                            dicWordListVO.add(egovOe1DicWordExcelVO);

                            for (int kk = 0; kk < dicWordListVO.size(); kk++) {
                                if (kk > 1) {
                                    if (dicWordListVO.get(kk - 1).getWrdNm().trim().equals(wordNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                    if (dicWordListVO.get(kk - 1).getWrdEngNm().trim().equals(engNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?(Full Name)? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                    if (dicWordListVO.get(kk - 1).getWrdEngAbrv().trim()
                                            .equals(wordNmEngAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                } else if (kk == 1) {
                                    if (dicWordListVO.get(kk - 1).getWrdNm().trim().equals(wordNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                    if (dicWordListVO.get(kk - 1).getWrdEngNm().trim().equals(engNm)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?(Full Name)? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                    if (dicWordListVO.get(kk - 1).getWrdEngAbrv().trim()
                                            .equals(wordNmEngAbrv)) {
                                        model.addAttribute("resultMsg", (i + 1)
                                                + "??? ?? . ?    .");
                                        return "/cms/cmm/EgovDicWordExcelRegist";
                                    }
                                }
                            }

                        } else {
                            if ("".equals(wordNm) || wordNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            if ("".equals(engNm) || engNm == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ?(Full Name)? . ?    .");
                            }
                            if ("".equals(wordNmEngAbrv) || wordNmEngAbrv == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ?? . ?    .");
                            }
                            if ("".equals(wordDc) || wordDc == null) {
                                model.addAttribute("resultMsg", (i + 1)
                                        + "??? ? . ?    .");
                            }
                            return "/cms/cmm/EgovDicWordExcelRegist";
                        }
                    }

                    // ? ??    .
                    egovOe1DicWordService.deleteExcelDicWord();

                    List<EgovOe1DicWordVO> list = dicWordListVO;
                    for (EgovOe1DicWordVO dicWordVo : list) {
                        egovOe1DicWordService.insertExcelDicWord(dicWordVo);
                    }

                } else {
                    model.addAttribute("resultMsg",
                            " EXCEL ?? .   .");
                    return "/cms/cmm/EgovDicWordExcelRegist";
                }
            }
        }

        status.setComplete();

        model.addAttribute("resultMsg", " Excel ? ?  ");

        return "forward:/cms/cmm/selectDicWordList.do";

    } catch (Exception ex) {

        model.addAttribute("resultMsg",
                " Excel ? ? ,  Excel ? ? ?. ");

        return "/cms/cmm/EgovDicWordExcelRegist";
    }
}

From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java

License:Apache License

/**
 * [Flow #-4]   ?  :  ? ? ?(Header, Footer)? 
 *//*from w  w w.j av a2  s.com*/
@Test
public void testModifyDocAttribute() throws Exception {

    try {
        log.debug("testModifyDocAttribute start....");

        StringBuffer sb = new StringBuffer();
        sb.append(fileLocation).append("/").append("testModifyDocAttribute.xls");

        if (EgovFileUtil.isExistsFile(sb.toString())) {
            EgovFileUtil.delete(new File(sb.toString()));

            log.debug("Delete file...." + sb.toString());
        }

        HSSFWorkbook wbTmp = new HSSFWorkbook();
        wbTmp.createSheet();

        //  ? ?
        excelService.createWorkbook(wbTmp, sb.toString());

        //  ? 
        HSSFWorkbook wb = excelService.loadWorkbook(sb.toString());
        log.debug("testModifyCellContents after loadWorkbook....");

        HSSFSheet sheet = wb.createSheet("doc test sheet");

        HSSFRow row = sheet.createRow(1);
        HSSFCell cell = row.createCell(1);
        cell.setCellValue(new HSSFRichTextString("Header/Footer Test"));

        // Header
        HSSFHeader header = sheet.getHeader();
        header.setCenter("Center Header");
        header.setLeft("Left Header");
        header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16)
                + "Right Stencil-Normal Italic font and size 16");

        // Footer
        HSSFFooter footer = sheet.getFooter();
        footer.setCenter(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -");
        log.debug("Style is ... " + HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12)
                + "- 1 -");
        footer.setLeft("Left Footer");
        footer.setRight("Right Footer");

        //  ? 
        FileOutputStream out = new FileOutputStream(sb.toString());
        wb.write(out);
        out.close();

        assertTrue(EgovFileUtil.isExistsFile(sb.toString()));

        //////////////////////////////////////////////////////////////////////////
        // ?
        HSSFWorkbook wbT = excelService.loadWorkbook(sb.toString());
        HSSFSheet sheetT = wbT.getSheet("doc test sheet");

        HSSFHeader headerT = sheetT.getHeader();
        assertEquals("Center Header", headerT.getCenter());
        assertEquals("Left Header", headerT.getLeft());
        assertEquals(HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16)
                + "Right Stencil-Normal Italic font and size 16", headerT.getRight());

        HSSFFooter footerT = sheetT.getFooter();
        assertEquals("Right Footer", footerT.getRight());
        assertEquals("Left Footer", footerT.getLeft());
        assertEquals(HSSFHeader.font("Fixedsys", "Normal") + HSSFHeader.fontSize((short) 12) + "- 1 -",
                footerT.getCenter());

    } catch (Exception e) {
        log.error(e.toString());
        throw new Exception(e);
    } finally {
        log.debug("testModifyDocAttribute end....");
    }
}