Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:comparator.Comparator.java

public static void delta_MVC_MTC() throws IOException {
    //Get the input files
    //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx"));
    //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx"));
    FileInputStream mtcFile = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));
    FileInputStream mtcFile2 = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));
    FileInputStream mvcFile = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx"));

    //Prepare the output file
    //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8"));
    Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"),
            "UTF-8"));
    csvW.write('\ufeff');

    csvW.write("Expand Project;");
    csvW.write("\n\n");

    //Get the workbook instance for XLS file 
    XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile);
    XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2);
    XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile);

    //Output//ww w . ja v  a  2 s  .c  om
    csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;");
    csvW.write("\n");
    csvW.write("********************;");
    csvW.write("\n");
    csvW.write("Set name;");
    csvW.write("\n");
    csvW.write("MTC mismatches;List of the codes missing in MVC");
    csvW.write("\n");
    csvW.write("MVC mismatches;List of the codes missing in MTC");
    csvW.write("\n");
    csvW.write("********************;");

    XSSFSheet mtcSheet;
    XSSFSheet mtcSheet2;
    Iterator<Row> mtcRowIterator;
    Iterator<Row> mtcRowIterator2;
    Iterator<Row> mvcRowIterator;
    Iterator<Cell> mtcCellIterator;
    Iterator<Cell> mvcCellIterator;
    int mtcCol;
    int mvcCol;
    boolean mtcColFound;
    boolean mvcColFound;
    ArrayList mtcCodes;
    ArrayList mvcCodes;
    ArrayList mtcEnglishNames;
    ArrayList mvcEnglishNames;
    ArrayList englishNamesdifferences;
    Row mtcRow;
    Row mtcRow2;
    Row mvcRow;
    Row mvcRow2;
    Row newRow;
    Cell newCell;
    CellStyle myStyle;
    String mtcSplit[];
    String mvcSplit[];
    String mtcSheetName;
    String mvcSheetName;

    //Get the sheet from the MTC workbook
    for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) {
        mtcSheet = mtcWorkbook.getSheetAt(i);
        mtcSheet2 = mtcWorkbook2.getSheetAt(i);

        //Get iterator to all the rows in current MTC sheet
        mtcRowIterator = mtcSheet.iterator();
        mtcRowIterator2 = mtcSheet2.iterator();

        //Get the sheet from the MVC workbook
        for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) {
            XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j);

            //Get iterator to all the rows in current MVC sheet
            mvcRowIterator = mvcSheet.iterator();

            //Get the name of MTC sheet and MVC sheet, compare them if they contain data
            //MTC data files are called "VSX_sheetName"
            //MVC data files are called "epSOSsheetName"
            mtcSplit = mtcSheet.getSheetName().split("_");
            mvcSplit = mvcSheet.getSheetName().split("SOS");
            mtcSheetName = mtcSplit[mtcSplit.length - 1];
            mvcSheetName = mvcSplit[mvcSplit.length - 1];

            //And process the file matching or throw out the file that has no equivalent
            if (mtcSheetName.equals(mvcSheetName)) {

                mtcCol = 0;
                mvcCol = 0;
                mtcColFound = false;
                mvcColFound = false;
                mtcCodes = new ArrayList();
                mvcCodes = new ArrayList();
                mtcEnglishNames = new ArrayList();
                mvcEnglishNames = new ArrayList();
                englishNamesdifferences = new ArrayList();

                //For each row, iterate through each columns
                //Get iterator to all cells of current row
                //In MTC
                while (mtcRowIterator.hasNext()) {
                    mtcRow = mtcRowIterator.next();
                    mtcRow2 = mtcRow;

                    if (mtcColFound == false) {
                        mtcCellIterator = mtcRow.cellIterator();

                        while (mtcCellIterator.hasNext()) {
                            Cell mtcCell = mtcCellIterator.next();
                            if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code")
                                    || mtcCell.getStringCellValue().equals("epSOS Code"))) {
                                mtcCol = mtcCell.getColumnIndex();
                                mtcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);
                        mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim());
                        mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim());
                    }
                }

                //In MVC
                while (mvcRowIterator.hasNext()) {
                    mvcRow = mvcRowIterator.next();
                    mvcRow2 = mvcRow;
                    if (mvcColFound == false) {
                        mvcCellIterator = mvcRow.cellIterator();

                        while (mvcCellIterator.hasNext()) {
                            Cell mvcCell = mvcCellIterator.next();

                            if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code")
                                    || mvcCell.getStringCellValue().equals("Code"))) {
                                mvcCol = mvcCell.getColumnIndex();
                                mvcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);
                        mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim());
                        mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim());
                    }
                }

                //Processing
                colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences);

                //Output
                //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {}
                csvW.write("\n\n");
                csvW.write(mtcSheetName + ";");
                csvW.write("\n");
                csvW.write("MTC mismatches;");
                for (int a = 0; a < mtcCodes.size(); a++) {
                    csvW.write(mtcCodes.get(a) + ";");
                }
                csvW.write("\n");
                csvW.write("MVC mismatches\n");
                for (int b = 0; b < mvcCodes.size(); b++) {
                    csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n");
                }

                csvW.write("english names differences\n");
                if (!englishNamesdifferences.isEmpty()) {
                    csvW.write("code;MTC 2.0;MVC 2.0.1\n");
                    for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) {
                        csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1)
                                + ";" + englishNamesdifferences.get(c + 2) + "\n");
                    }
                }

                /* work on currents MTC2.0 sheet */
                mtcColFound = false;
                mtcCol = 0;
                List<Integer> delRows = new ArrayList();

                //recreate iterator to all the rows in current MTC sheet
                while (mtcRowIterator2.hasNext()) {
                    mtcRow = mtcRowIterator2.next();
                    mtcRow2 = mtcRow;
                    if (mtcColFound == false) {
                        mtcCellIterator = mtcRow.cellIterator();

                        while (mtcCellIterator.hasNext()) {
                            Cell mtcCell = mtcCellIterator.next();
                            if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code")
                                    || mtcCell.getStringCellValue().equals("epSOS Code"))) {
                                mtcCol = mtcCell.getColumnIndex();
                                mtcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);

                        for (int a = 0; a < mtcCodes.size(); a++) {
                            if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) {
                                // delete row corresponding to useless code
                                delRows.add(mtcRow.getRowNum());
                                break;
                            }
                        }

                        if (!englishNamesdifferences.isEmpty()) {
                            for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) {
                                if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()
                                        .equals(englishNamesdifferences.get(c + 1))) {
                                    mtcRow2.getCell(mtcCol + 1)
                                            .setCellValue(englishNamesdifferences.get(c + 2).toString());
                                    break;
                                }
                            }
                        }
                    }
                }
                for (int d = delRows.size() - 1; d >= 0; d--) {
                    mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1);
                }
                myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle();
                for (int b = 0; b < mvcCodes.size(); b++) {
                    newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1);
                    for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) {
                        newCell = newRow.createCell(bb);
                        newCell.setCellStyle(myStyle);
                        if (bb == mtcCol) {
                            newCell.setCellValue(mvcCodes.get(b).toString());
                        } else if (bb == mtcCol + 1) {
                            newCell.setCellValue(mvcEnglishNames.get(b).toString());
                        }
                    }
                }
            }
        }
    }
    //close InputStream
    mtcFile.close();
    mtcFile2.close();
    mvcFile.close();
    //close OutputStream
    csvW.close();

    //Open FileOutputStream to write updates
    FileOutputStream output_file = new FileOutputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx"));
    //write changes
    mtcWorkbook2.write(output_file);
    //close the stream
    output_file.close();
}

From source file:comparator.Comparator.java

public static void translation() throws IOException {

    //Get the input files
    FileInputStream newMTC = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));

    String icdCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\CIM-10\\CIM10GM2014_S_FR_ClaML_2014.10.31.xml";
    String atcCodes = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\ATCcodes\\ATCDPP.CSV";

    //Prepare the output file
    Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\CIM10-treated.csv"),
            "UTF-8"));
    csvW.write('\ufeff');
    Writer csvW2 = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\ATC-treated.csv"),
            "UTF-8"));
    csvW2.write('\ufeff');
    List<String> translationList = new ArrayList();
    Map<String, String> translatList = new HashMap();
    Map<String, String> translatAtcList = new HashMap();
    Map<String, String> translatAtcList2 = new HashMap();
    String codeTemp = "";
    boolean prefered = false;

    InputStream ips = new FileInputStream(icdCodes);
    //Cp1252 --> ANSI
    InputStreamReader ipsr = new InputStreamReader(ips, "UTF-8");
    BufferedReader br = new BufferedReader(ipsr);
    String ligne;/*from   ww  w.ja va  2s.c  o m*/
    Pattern p1 = Pattern.compile("<Class code=\"(.+?)\"");
    Pattern p2 = Pattern.compile("xml:space=\"default\">(.+?)<");
    Pattern p3 = Pattern.compile("(.+?)\\..");
    Pattern pActiveIngredient = Pattern.compile("(?:.*;){8}\"(.+?)\";(?:.*;)\"(.+?)\";(?:.*;){5}.*");
    Pattern pActiveIngredient2 = Pattern.compile("(?:.*;){4}\"(.+?)\";(?:.*;){5}\"(.+?)\";(?:.*;){5}.*");
    Matcher m1;
    Matcher m2;
    Matcher m3;
    Matcher mActiveIngredient;
    Matcher mActiveIngredient2;

    while ((ligne = br.readLine()) != null) {
        m1 = p1.matcher(ligne);
        m2 = p2.matcher(ligne);

        if (ligne.matches("</Class>")) {
            prefered = false;
            codeTemp = "";
        }

        if (m1.find()) {
            codeTemp = m1.group(1);
        }

        if (ligne.matches("(.*)kind=\"preferred\"(.*)")) {
            prefered = true;
        }

        if (m2.find() && prefered == true) {
            translatList.put(codeTemp, m2.group(1));
            prefered = false;
        }

        //si traduction franais ET anglais
        if (ligne.matches(".*<FR_OMS>.*</FR_OMS>.*") && ligne.matches(".*<EN_OMS>.*</EN_OMS>.*")) {
            translationList.add(ligne.replace("\u00A0", " "));
        }
    }
    br.close();

    ips = new FileInputStream(atcCodes);
    //Cp1252 --> ANSI
    ipsr = new InputStreamReader(ips, "UTF-8");
    br = new BufferedReader(ipsr);

    while ((ligne = br.readLine()) != null) {
        mActiveIngredient = pActiveIngredient.matcher(ligne);
        mActiveIngredient2 = pActiveIngredient2.matcher(ligne);
        if (mActiveIngredient.find()) {
            translatAtcList.put(mActiveIngredient.group(1), mActiveIngredient.group(2));
        }
        if (mActiveIngredient2.find()) {
            translatAtcList2.put(mActiveIngredient.group(1), mActiveIngredient.group(2));
        }
    }
    br.close();

    //Get the workbook instance for XLS file 
    XSSFWorkbook newMtcWorkbook = new XSSFWorkbook(newMTC);
    XSSFSheet newMtcSheet;
    Iterator<Row> newMtcRowIterator;
    Iterator<Cell> newMtcCellIterator;
    int newMtcCol;
    boolean newMtcColFound;
    ArrayList newMtcCodes;
    ArrayList newMtcCodes2;
    Row newMtcRow;
    Row newMtcRow2;
    Cell newMtcCell;

    //Get the sheet from the MTC workbook
    for (int i = 0; i < newMtcWorkbook.getNumberOfSheets(); i++) {
        newMtcSheet = newMtcWorkbook.getSheetAt(i);

        //Get iterator to all the rows in current MTC sheet
        newMtcRowIterator = newMtcSheet.iterator();

        //And process the file matching or throw out the file that has no equivalent
        if (newMtcSheet.getSheetName().equals("VS21_IllnessesandDisorders")) {
            newMtcCol = 0;
            newMtcColFound = false;
            newMtcCodes = new ArrayList();

            //For each row, iterate through each columns
            //Get iterator to all cells of current row
            //In MTC
            while (newMtcRowIterator.hasNext()) {
                newMtcRow = newMtcRowIterator.next();

                if (newMtcColFound == false) {
                    newMtcCellIterator = newMtcRow.cellIterator();

                    while (newMtcCellIterator.hasNext()) {
                        newMtcCell = newMtcCellIterator.next();
                        if (newMtcCell.getCellType() == 1 && newMtcCell.getStringCellValue().equals("Code")) {
                            newMtcCol = newMtcCell.getColumnIndex();
                            newMtcColFound = true;
                            break;
                        }
                    }
                } else {
                    newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim());
                }
            }

            for (int j = 0; j < newMtcCodes.size(); j++) {
                csvW.write(newMtcCodes.get(j) + ";");

                if (translatList.containsKey(newMtcCodes.get(j))) {
                    csvW.write(translatList.get(newMtcCodes.get(j)));
                } else {
                    m3 = p3.matcher((String) newMtcCodes.get(j));
                    if (m3.find() && translatList.containsKey(m3.group(1))) {
                        csvW.write(translatList.get(m3.group(1)));
                    }
                }

                /*for (int k=0; k<translationList.size(); k++) {
                String frTrad = "";
                        
                if (translationList.get(k).trim().contains("<EN_OMS>"+newMtcCodes.get(j)+"</EN_OMS>")) {
                    Pattern p = Pattern.compile("<FR_OMS>(.+?)</FR_OMS>");
                    Matcher m = p.matcher(translationList.get(k).trim());
                    if (m.find()){
                        frTrad = m.group(1);
                        translationList.remove(k);
                    }
                    csvW.write(StringUtils.capitalize(frTrad));
                }
                }*/
                csvW.write("\n");
            }
        } else if (newMtcSheet.getSheetName().equals("VS3_ActiveIngredient")) {
            newMtcCol = 0;
            newMtcColFound = false;
            newMtcCodes = new ArrayList();
            newMtcCodes2 = new ArrayList();

            //For each row, iterate through each columns
            //Get iterator to all cells of current row
            //In MTC
            while (newMtcRowIterator.hasNext()) {
                newMtcRow = newMtcRowIterator.next();
                newMtcRow2 = newMtcRow;

                if (newMtcColFound == false) {
                    newMtcCellIterator = newMtcRow.cellIterator();

                    while (newMtcCellIterator.hasNext()) {
                        newMtcCell = newMtcCellIterator.next();
                        if (newMtcCell.getCellType() == 1
                                && newMtcCell.getStringCellValue().equals("English Display Name")) {
                            newMtcCol = newMtcCell.getColumnIndex();
                            newMtcColFound = true;
                            break;
                        }
                    }
                } else {
                    newMtcRow.getCell(newMtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes.add(newMtcRow.getCell(newMtcCol).getStringCellValue().trim());
                    newMtcRow2.getCell(newMtcCol - 1, Row.CREATE_NULL_AS_BLANK)
                            .setCellType(Cell.CELL_TYPE_STRING);
                    newMtcCodes2.add(newMtcRow.getCell(newMtcCol - 1).getStringCellValue().trim());
                }
            }

            for (int j = 0; j < newMtcCodes.size(); j++) {
                csvW2.write(newMtcCodes2.get(j) + ";");
                csvW2.write(newMtcCodes.get(j) + ";");

                if (translatAtcList.containsKey(newMtcCodes.get(j))) {
                    csvW2.write(translatAtcList.get(newMtcCodes.get(j)));
                } else if (translatAtcList2.containsKey(newMtcCodes2.get(j))) {
                    csvW2.write(translatAtcList.get(newMtcCodes.get(j)));
                } else {
                    System.out.println(newMtcCodes.get(j));
                }

                csvW2.write("\n");
            }
        }
    }

    csvW.close();
    csvW2.close();
    newMTC.close();

}

From source file:containerMath.containerMath.java

public void readFromExcel(String pathToFile, String name, String range, String numberOfItems, String inPack,
        String numberOfPacks, String netWeight, String sumNetWeight, String grossWeight, String sumGrossWeight,
        String volumeOfPack, String sumVolume) {
    //{/*  www.j  a v  a  2  s.  c o  m*/
    //        cont.rowsRangeProcessing(range);
    int itemName = CellReference.convertColStringToIndex(name);
    int itemsQuantity = CellReference.convertColStringToIndex(numberOfItems);
    int inPackIndex = CellReference.convertColStringToIndex(inPack);
    int numOfPacksIndex = CellReference.convertColStringToIndex(numberOfPacks);
    int netWeightIndex = CellReference.convertColStringToIndex(netWeight);
    int sumNetWeightIndex = CellReference.convertColStringToIndex(sumNetWeight);
    int grossWeightIndex = CellReference.convertColStringToIndex(grossWeight);
    int sumGrossWeightIndex = CellReference.convertColStringToIndex(sumGrossWeight);
    int volumeOfPackIndex = CellReference.convertColStringToIndex(volumeOfPack);
    int sumVolumeIndex = CellReference.convertColStringToIndex(sumVolume);
    try {
        OPCPackage pkg;
        try {
            pkg = OPCPackage.open(new File(pathToFile));
            // pkg = OPCPackage.open(new File("/home/igor/Documents/China/HDHardware/test.xlsx"));

            XSSFWorkbook book = new XSSFWorkbook(pkg);
            Sheet sheet1 = book.getSheetAt(0);
            //Sheet sheet2 = book.createSheet();
            XSSFWorkbook book2 = new XSSFWorkbook();
            Sheet bookSheet = book2.createSheet();

            items = new ArrayList<Item>();
            //itemsList = new Instances();
            for (int n = cont.getFirstNumber(); n < cont.getSecondNumber(); n++) {
                Row row = sheet1.getRow(n);
                Item item = new Item(row.getCell(itemName).toString(),
                        row.getCell(itemsQuantity).getNumericCellValue(),
                        row.getCell(inPackIndex).getNumericCellValue(),
                        row.getCell(numOfPacksIndex).getNumericCellValue(),
                        (int) row.getCell(grossWeightIndex).getNumericCellValue(),
                        row.getCell(sumGrossWeightIndex).getNumericCellValue(),
                        row.getCell(volumeOfPackIndex).getNumericCellValue(),
                        row.getCell(sumVolumeIndex).getNumericCellValue());
                items.add(item);
            }
            this.numberOfItems = items.size();
            allWeight();
            allVolume();
        } catch (IOException ex) {
            ex.printStackTrace();
        }
    } catch (InvalidFormatException ex) {
        ex.printStackTrace();
    }
}

From source file:controller.DAORequest.java

private ArrayList<Resolution> readResolutions() {
    ArrayList<Resolution> resolutions = new ArrayList();
    try {/*ww w  .j  a v a  2  s .  c o m*/
        FileInputStream fis = new FileInputStream(new File("src//files//DatosResolucion.xlsx"));
        XSSFWorkbook wb = new XSSFWorkbook(fis);
        XSSFSheet sheet = wb.getSheetAt(0);
        for (Row row : sheet) {
            int id = 0;
            String attention = null;
            String title = null;
            String intro = null;
            String result = null;
            String resolve = null;
            String notify = null;
            String considerations = null;
            for (Cell cell : row) {
                if (row.getRowNum() != 0) {
                    switch (cell.getColumnIndex()) {
                    case 0:
                        id = (int) cell.getNumericCellValue();
                        break;
                    case 1:
                        attention = cell.getStringCellValue();
                        break;
                    case 2:
                        title = cell.getStringCellValue();
                        break;
                    case 3:
                        intro = cell.getStringCellValue();
                        break;
                    case 4:
                        result = cell.getStringCellValue();
                        break;
                    case 5:
                        resolve = cell.getStringCellValue();
                        break;
                    case 6:
                        notify = cell.getStringCellValue();
                        break;
                    case 7:
                        considerations = cell.getStringCellValue();
                        break;
                    }
                }
            }
            if (id != 0) {
                System.out.println("Resolution: [id: " + id + " attention: " + attention + "\ntitle: " + title
                        + " \nintro: " + intro + " \nresult: " + result + " \nresolve: " + resolve
                        + " \nnotify: " + notify + " \nconsiderations: " + considerations + "\n]");
                resolutions.add(
                        new Resolution(id, attention, title, intro, result, resolve, notify, considerations));
            }
        }

    }

    catch (FileNotFoundException e) {
        System.out.println("No hay archivo que cargar de Resolutions");
    } catch (IOException ex) {
        Logger.getLogger(DAORequest.class.getName()).log(Level.SEVERE, null, ex);
    }

    return resolutions;

}

From source file:controller.ExcelConsultant.java

static ArrayList<String> getUnrepeatableDataOfACollum(File inputFile, int col) {

    ArrayList<String> collection = new ArrayList<>();
    try {/*from  ww  w. j a  v a  2  s  .c o m*/

        FileInputStream fis = new FileInputStream(inputFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet sheet = myWorkBook.getSheetAt(0);

        int nRows = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < nRows; i++) {

            String item = sheet.getRow(i).getCell(col).getStringCellValue();
            String delim = " ";
            String[] separatedWords = item.split(delim);

            for (String word : separatedWords) {

                word = word.replaceAll(",", "");
                word = word.replaceAll(" ", "");

                if (!collection.contains(word)) {

                    collection.add(word);

                }
            }

        }

    } catch (Exception e) {

        e.printStackTrace();

    }

    collection.sort(null);

    return collection;

}

From source file:controller.ExcelConsultant.java

public static ArrayList<Result> makeConsult(File inputFile, String trade, String area) {

    ArrayList<Result> result = new ArrayList<>();

    try {/* w ww  .  ja  v a 2 s. c  o m*/

        FileInputStream fis = new FileInputStream(inputFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet sheet = myWorkBook.getSheetAt(0);

        int nRows = sheet.getPhysicalNumberOfRows();

        for (int i = 1; i < nRows; i++) {

            Row actualRow = sheet.getRow(i);

            if (actualRow.getCell(1).getStringCellValue().contains(trade)
                    && actualRow.getCell(5).getStringCellValue().contains(area)) {

                String name = actualRow.getCell(0).getStringCellValue();
                String tradeName = actualRow.getCell(2).getStringCellValue();
                String person = actualRow.getCell(3).getStringCellValue();
                String number = actualRow.getCell(4).getStringCellValue();
                String notes = actualRow.getCell(6).getStringCellValue();

                Result r = new Result(name, tradeName, person, number, notes);

                result.add(r);

            }

        }

    } catch (Exception e) {
        e.printStackTrace();
    }

    return result;

}

From source file:controller.ExcelConsultant.java

static void logResult(ArrayList<Result> result, File outputFile, String name) {

    try {//from  w ww .j  a v a2  s . com

        FileInputStream fis;
        fis = new FileInputStream(outputFile);
        XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
        XSSFSheet sheet = myWorkBook.getSheetAt(0);

        int nRow = sheet.getPhysicalNumberOfRows();

        if (nRow == 0) {
            sheet.createRow(nRow);

            sheet.getRow(nRow).createCell(0).setCellValue("Timestamp");
            sheet.getRow(nRow).createCell(1).setCellValue("Name");
            sheet.getRow(nRow).createCell(2).setCellValue("Trade Name");
            sheet.getRow(nRow).createCell(3).setCellValue("Number");
            sheet.getRow(nRow).createCell(4).setCellValue("Person");
            sheet.getRow(nRow).createCell(5).setCellValue("Notes");

            nRow++;
        }

        for (Result res : result) {

            sheet.createRow(nRow);

            Date date = new Date();
            sheet.getRow(nRow).createCell(0).setCellValue(date.toString());
            sheet.getRow(nRow).createCell(1).setCellValue(name);
            sheet.getRow(nRow).createCell(2).setCellValue(res.getTradeName());
            sheet.getRow(nRow).createCell(3).setCellValue(res.getNumber());
            sheet.getRow(nRow).createCell(4).setCellValue(res.getPerson());
            sheet.getRow(nRow).createCell(5).setCellValue(res.getNotes());

            nRow++;
        }
        //important to close InputStream
        fis.close();
        //Open FileOutputStream to write updates
        FileOutputStream output_file = new FileOutputStream(outputFile);
        //write changes
        myWorkBook.write(output_file);
        //close the stream
        output_file.close();

    } catch (Exception ex) {
        ex.printStackTrace();
    }

}

From source file:controller.UploadExcelStudentsFile.java

public String readFromExcel(String path, MyPerson p) {
    String messages = "";
    String ColumnsMesages = "";

    try {/*from w ww .  j  av a2 s .c o m*/
        FileInputStream file;
        file = new FileInputStream(new File(path));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        int rowCount = 0;
        boolean isEverythingIsOK = true;
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            rowCount++;
            System.out.println("rcount:" + rowCount);

            if (rowCount > 1) {
                //For each row, iterate through all the columns
                int Code = 0;
                String Fname = "", Lname = "", Email = "";
                String password = "";
                int userType = 0;
                int level = 0;
                String gender = "";
                Iterator<Cell> cellIterator = row.cellIterator();
                int ColumnCount = 0;
                int rowCountMesages = rowCount - 1;

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    ColumnCount++;

                    //Check the cell type and format accordingly
                    System.out.println("ccount:" + ColumnCount);

                    /*  switch (cell.getCellType()) {
                     case Cell.CELL_TYPE_NUMERIC:
                     System.out.println(cell.getNumericCellValue() + "");
                     System.out.println("numeric type case:" + Cell.CELL_TYPE_NUMERIC);// numeric type case:0
                     System.out.println("type:" + cell.getCellType());//type:0
                     break;
                     case Cell.CELL_TYPE_STRING:
                     System.out.println(cell.getStringCellValue());
                            
                     System.out.print("string type case:" + Cell.CELL_TYPE_STRING + "");//string type case:0
                     System.out.println("type:" + cell.getCellType());//type:1
                            
                     break;
                     }*/
                    if (ColumnCount == 1) {
                        if (cell.getCellType() == 0) {
                            Code = (int) cell.getNumericCellValue();
                            password = String.valueOf(Code);
                            System.out.println("Code:" + Code);
                            System.out.println("pass:" + password);

                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'>First Column is Code must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 2) {
                        if (cell.getCellType() == 1) {

                            Fname = cell.getStringCellValue();
                            System.out.println("Fname:" + Fname);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'>Column number 2  is Fname  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 3) {
                        if (cell.getCellType() == 1) {

                            Lname = cell.getStringCellValue();
                            System.out.println("Lname:" + Lname);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 3 is Lname  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br>";
                        }
                    } /*else if(ColumnCount==4){
                      String Pass=cell.getStringCellValue();
                      System.out.println("Pass:"+Pass);
                              
                      }*/ else if (ColumnCount == 4) {
                        if (cell.getCellType() == 1) {

                            Email = cell.getStringCellValue();
                            System.out.println("Email:" + Email);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'>Column number 4  is Email  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 5) {
                        if (cell.getCellType() == 1) {

                            gender = cell.getStringCellValue();
                            System.out.println("gender:" + gender);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 5  is gender  must be a String please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 6) {
                        if (cell.getCellType() == 0) {

                            userType = (int) cell.getNumericCellValue();
                            System.out.println("userType:" + userType);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += " <font color='red'> Column  number 6 is userType  must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";
                        }
                    } else if (ColumnCount == 7) {
                        if (cell.getCellType() == 0) {

                            level = (int) cell.getNumericCellValue();
                            System.out.println("level:" + level);
                        } else {
                            isEverythingIsOK = false;
                            ColumnsMesages += "<font color='red'> Column number 7 is level  must be an integer please  in student number ("
                                    + rowCountMesages + ")</font><br/>";

                        }
                    }
                } //end of celIterator

                int rowAffected = 0;
                if (isEverythingIsOK) {
                    try {
                        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss a");
                        String RegistrationDate = sdf1.format(new Date());
                        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddhhmmss");
                        String MyUniversityCodeString = sdf2.format(new Date()) + p.getFaculityID() + Code;

                        rowAffected = p.RegisterUser(Code, Fname, Lname, Email, password, userType, 1, gender,
                                p.getFaculityID(), p.getUniversityID(), RegistrationDate, 1,
                                MyUniversityCodeString);

                        if (rowAffected > 0) {
                            //response.sendRedirect("MyAccount.jsp?page=CourseViewPOSTContents&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+"");
                            //response.sendRedirect("MyAccount.jsp?page=viewPost&POSTID=" +POSTID+"&Subject_Code="+Subject_Code+"");
                            messages += "<font color='blue'>Student(" + rowCountMesages
                                    + ")was Adding Successfully  ^_^</font>" + "<br/>";
                            System.out.println(
                                    "<script type='text/javascript' > alert('Student was Adding Successfully  ^_^ ');history.back();</script>");
                        } else {
                            messages += "<font color='red'> Student(" + rowCountMesages
                                    + ")was Adding Failed  ^_^</font>" + "<br/>";

                            System.out.println(
                                    "<script type='text/javascript' > alert('Student was Failed ^_^ ');history.back();</script>");
                        }

                    } catch (Exception ex) {
                        System.err.println("Add Students Error" + ex.getMessage());
                        messages += "<font color='red'>Adding Students Error" + ex.getMessage()
                                + "</font><br/>";
                        messages += "<center><a href='index.jsp' >Home</a></center>";
                    }
                } //end of if IsEverythingIsOk Or Not
                System.out.println("");

            } //end of if this is not first row
        } //end of while rowIterator
        file.close();
    } //end of try 
    catch (Exception e) {
        e.printStackTrace();
        messages += "<font color='red'>" + e.getMessage() + "</font><br/>";
    }
    messages += ColumnsMesages;

    return messages;
}

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

public List<Team> readXlsx(String filename, CourtScheduleInfo info) throws Exception {

    File file = new File(filename);
    if (!file.exists()) {
        return null;
    }/*w  w  w.  ja v a 2 s  . co  m*/

    FileInputStream fis = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fis);

    // Get worksheet by index
    XSSFSheet sh = wb.getSheetAt(0);

    rowNumber = 2;
    Integer rowCount = sh.getLastRowNum();

    if (Main.LOG_LEVEL >= 1) {
        System.out.println(new java.util.Date() + "[INFO] Worksheet Name: " + sh.getSheetName());
        System.out.println(new java.util.Date() + "[INFO] Worksheet has " + (rowCount - 1) + " lines of data.");
    }

    while (rowNumber <= rowCount) {
        Row currentRow = sh.getRow(rowNumber);
        if (currentRow != null && currentRow.getLastCellNum() > 0) {
            Team nextTeam = processRow(currentRow, info);
            if (nextTeam != null && nextTeam.getTeamId() != null) {
                teamList.add(nextTeam);
            } else
                break;
        }
        rowNumber += 1;
    }

    if (Main.LOG_LEVEL >= 1) {
        /*for (int x = 0; x < teamList.size(); x++) {
        System.out.println(teamList.get(x));
        }*/
        System.out.println(new java.util.Date() + " [INFO] Input parsed. Constructing possible matches...");
    }

    return teamList;
}

From source file:courtscheduler.persistence.CourtScheduleIO.java

License:Apache License

public short getColumnWidth(File file) throws Exception {

    FileInputStream fis = new FileInputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(fis);

    // Get worksheet by index
    XSSFSheet sh = wb.getSheetAt(0);

    short columnWidth = 0;
    Integer rowCounter = 0;/*from ww  w.  j  a  va2 s .  co  m*/
    Integer rowCount = sh.getLastRowNum();

    while (rowCounter <= rowCount) {
        Row currentRow = sh.getRow(rowCounter);
        short columnCount = currentRow.getLastCellNum();

        if (columnCount > columnWidth)
            columnWidth = columnCount;
    }

    return columnWidth;
}