Example usage for org.apache.poi.ss.usermodel Row cellIterator

List of usage examples for org.apache.poi.ss.usermodel Row cellIterator

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Row
  6. cellIterator

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Row cellIterator.

Prototype

Iterator<Cell> cellIterator();

Source Link

Usage

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

/**
 *
 * @param file//from   www  .ja v  a2s .  c o  m
 * @return
 * @throws FileNotFoundException
 * @throws InvalidFormatException
 * @throws IOException
 */
/*this method convert a multipart file to json object */
@Override
public JSONObject convert(MultipartFile file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();
            // System.out.println(cell.getCellType());
            //           cells.put(cell.getDateCellValue());
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cells.put(cell.getRichStringCellValue().getString());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else {
                    cells.put(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cells.put(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                cells.put(cell.getCellFormula());
                break;
            default:
                System.out.println();
            }
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.vermeg.convertisseur.service.ConvServiceImpl.java

@Override
public JSONObject convert(String file, String name)
        throws FileNotFoundException, InvalidFormatException, IOException {

    // File file = new File("C:\\Users\\Ramzi\\Documents\\PFE\\developpement\\avancement.xlsx");

    File filez = File.createTempFile("fichier", "xslx");
    byte[] data = Base64.decodeBase64(file);
    FileOutputStream fos = new FileOutputStream(filez);
    fos.write(data);/*from   w w  w  .j a va  2 s.c o  m*/
    fos.close();
    //file.transferTo(filez);
    FileInputStream inp = new FileInputStream(filez);
    Workbook workbook = WorkbookFactory.create(inp);
    //Sheet sheet = workbook.getSheetAt( 0 );
    Sheet sheet = workbook.getSheet(name);
    // Start constructing JSON.
    JSONObject json = new JSONObject();

    // Iterate through the rows.
    JSONArray rows = new JSONArray();
    for (Iterator<Row> rowsIT = sheet.rowIterator(); rowsIT.hasNext();) {
        Row row = rowsIT.next();
        JSONObject jRow = new JSONObject();

        // Iterate through the cells.
        JSONArray cells = new JSONArray();
        for (Iterator<Cell> cellsIT = row.cellIterator(); cellsIT.hasNext();) {
            Cell cell = cellsIT.next();

            if (cell.getCellType() == CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    cells.put(cell.getDateCellValue());
                } else
                    cells.put(cell.getNumericCellValue());
            } else
                cells.put(cell.getStringCellValue());
        }
        jRow.put("cell", cells);
        rows.put(cells);
        //rows.put( jRow );
    }

    // Create the JSON.
    json.put("rows", rows);
    System.out.println(json.toString());
    return json;
}

From source file:com.vertec.daoimpl.AttendanceDAOImpl.java

public List<Object> readexcel(String path) {
    //        String data = "";
    List<Object> table = new ArrayList<Object>();
    try {//from   w ww. j  ava2s  .  co m
        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();
            List<String> rows = new ArrayList<String>();
            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    rows.add(cell.getRichStringCellValue().getString());
                    //                            data+=cell.getRichStringCellValue().getString();
                    //                            System.out.print(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {

                        rows.add(cell.getDateCellValue() + "");
                        //                                data+=cell.getDateCellValue();
                        //                                System.out.print(cell.getDateCellValue());
                    } else {
                        rows.add(cell.getNumericCellValue() + "");
                        //                                data+=cell.getNumericCellValue();
                        //                                System.out.print(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    rows.add(cell.getBooleanCellValue() + "");
                    //                            data+=cell.getBooleanCellValue();
                    //                            System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    rows.add(cell.getCellFormula() + "");
                    //                            data+=cell.getCellFormula();
                    //                            System.out.print(cell.getCellFormula());
                    break;
                default:
                    //                            System.out.print("");
                }
                //                    data += "-";
                //                    System.out.print(" - ");
            }
            table.add(rows);
            //                data += ";;;";
            //                System.out.println(";;;");
        }
    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
    return table;
}

From source file:com.vertec.daoimpl.AttendanceDAOImpl.java

public String readexcel2(String path) {
    try {//w  w  w .  j  av  a 2s  .  c  o  m
        // Get the workbook instance for XLSX file
        XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(path));

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

        Row row;
        Cell cell;

        // Iterate through each rows from first sheet
        Iterator<Row> rowIterator = sheet.iterator();

        while (rowIterator.hasNext()) {
            row = rowIterator.next();

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                cell = cellIterator.next();

                switch (cell.getCellType()) {
                case Cell.CELL_TYPE_STRING:
                    System.out.print(cell.getRichStringCellValue().getString());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        System.out.print(cell.getDateCellValue());
                    } else {
                        System.out.print(cell.getNumericCellValue());
                    }
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    System.out.print(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    System.out.print(cell.getCellFormula());
                    break;
                default:
                    System.out.print("");
                }
                System.out.print(" - ");
            }
            System.out.println(";;;");
        }
    } catch (Exception e) {
        System.err.println("Exception :" + e.getMessage());
    }
    return null;
}

From source file:com.virtusa.isq.vtaf.runtime.SeleniumTestBase.java

License:Apache License

/**
 * Adds the values from excel.//from  w w  w  . j  a v  a2 s  .c  o  m
 * 
 * @param path
 *            the path
 * @param index
 *            the index
 * @return the string[][]
 * @throws IOException
 *             Signals that an I/O exception has occurred.
 * @throws InvalidFormatException
 *             the invalid format exception
 */
public final String[][] addValuesFromExcel(final String path, final String index)
        throws IOException, InvalidFormatException {

    String cellStringValue = null;
    double cellDoubleValue = 0;
    Boolean cellBooleanValue;
    byte cellErrorValue = 0;
    String[][] arrExcelContent;
    FileInputStream file = null;
    Workbook workbook = null;

    Sheet sheet = null;
    try {
        file = new FileInputStream(new File(path));
        workbook = WorkbookFactory.create(file);
        sheet = workbook.getSheetAt(Integer.parseInt(index));
        Iterator<Row> rowIterator = sheet.iterator();
        arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][];
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            int rowNumber = row.getRowNum();
            Iterator<Cell> cellIterator = row.cellIterator();
            arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()];
            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                int cellNumber = cell.getColumnIndex();
                if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                    cellStringValue = cell.getStringCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;
                } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
                    cellBooleanValue = cell.getBooleanCellValue();
                    arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString();
                } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    cellErrorValue = cell.getErrorCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue);
                } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                    cellStringValue = cell.getCellFormula();
                    arrExcelContent[rowNumber][cellNumber] = cellStringValue;

                } else {

                    cellDoubleValue = cell.getNumericCellValue();
                    arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue);
                }
            }

        }
    } finally {
        if (((InputStream) workbook) != null) {
            ((InputStream) workbook).close();
        }
    }
    return arrExcelContent;
}

From source file:com.voicecomdemomvn.logic.XLSParser.java

public XLSParser(String filePath) {
    if (!"".equals(filePath)) {
        if (filePath.toLowerCase().endsWith(".xls")) {
            try {
                results = new ArrayList<>(); //..      ? - ? ?
                FileInputStream file = new FileInputStream(new File(filePath));
                workbook = new HSSFWorkbook(file);
                sheet = workbook.getSheetAt(0);
                rowIterator = sheet.iterator();
                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();
                    cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        Cell cell = cellIterator.next();
                        cell.setCellType(CellType.STRING);
                        results.add(cell.getStringCellValue());
                    }/*from   w  w  w. jav a 2  s.  com*/
                }
            } catch (FileNotFoundException ex) {
                JOptionPane.showMessageDialog(null, "   ??!",
                        "!", JOptionPane.ERROR_MESSAGE, null);
                Logger.getLogger(XLSParser.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(null, "?    !",
                        "!", JOptionPane.ERROR_MESSAGE, null);
                Logger.getLogger(XLSParser.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else
            JOptionPane.showMessageDialog(null, "    *.xls",
                    "!", JOptionPane.ERROR_MESSAGE, null);
    } else
        JOptionPane.showMessageDialog(null, "   !", "!",
                JOptionPane.ERROR_MESSAGE, null);
}

From source file:com.xl.main.ReadExcelSampleSilk.java

public static String read(String filename) {
    Gson gson = new Gson();
    Map<String, List<SampleSinkBean>> values = new HashMap<String, List<SampleSinkBean>>();
    List<SampleSinkBean> byRow = new ArrayList<SampleSinkBean>();
    try {/*from ww  w. j  a va 2 s. com*/

        FileInputStream file = null;
        if (filename == null) {
            file = new FileInputStream(new File("H:\\anil\\sample-sink.xlsx"));
        } else {
            file = new FileInputStream(new File(filename));
        }

        //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();

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            if (row.getRowNum() > 0 && row.getRowNum() < 20) {
                SampleSinkBean sb = new SampleSinkBean();
                //System.out.println("row value" + sheet.getRow(3).getCell(3));
                while (cellIterator.hasNext()) {//
                    Cell cell = cellIterator.next();

                    String cellString = " ";
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        cellString = cell.getNumericCellValue() + "";
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        cellString = cell.getStringCellValue() + "";

                        break;
                    case Cell.CELL_TYPE_ERROR:
                        cellString = cell.getErrorCellValue() + "";
                        break;
                    default:
                        cellString = cell.getStringCellValue() + "";

                    }

                    switch (cell.getColumnIndex()) {
                    case 0:
                        sb.setFrYear(cellString);
                        break;
                    case 1:
                        sb.setVpmod(cellString);
                    case 2:
                        sb.setProjectName(cellString);
                    case 3:
                        sb.setProjectWorktype(cellString);
                    case 4:
                        sb.setBusinessObjective(cellString);

                    }

                }
                byRow.add(sb);

            }
            // System.out.println("");

        }
        values.put("sink", byRow);
        System.out.println("output *********" + gson.toJson(values));

        file.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
    return gson.toJson(values);
}

From source file:comparararchivos.CompararArchivos.java

/**
 * @param args the command line arguments
 *///from  w w  w .j  a va 2 s . c o m
public static void main(String[] args) {
    // TODO code application logic here
    File excel1 = null;
    FileInputStream fl1 = null;
    XSSFWorkbook book1 = null;

    File excel2 = null;
    FileInputStream fl2 = null;
    XSSFWorkbook book2 = null;

    try {
        excel1 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorB.xlsx");
        excel2 = new File("D:\\Users\\jose.gil\\Documents\\Anotaciones\\ProfesorD.xlsx");

        PrintWriter file = new PrintWriter("diferencias.txt", "UTF-8");

        fl1 = new FileInputStream(excel1);
        fl2 = new FileInputStream(excel2);

        book1 = new XSSFWorkbook(fl1);
        book2 = new XSSFWorkbook(fl2);

        XSSFSheet sheet_A = book1.getSheetAt(0);
        XSSFSheet sheet_B = book2.getSheetAt(0);

        Iterator<Row> itrA = sheet_A.iterator();
        Iterator<Row> itrB = sheet_B.iterator();

        int totalDiferencias = 0;
        int numFila = 2;
        while (itrA.hasNext() && itrB.hasNext()) {
            Row rowA = itrA.next();
            Row rowB = itrB.next();
            if (rowA.getRowNum() == 0)
                continue;

            Iterator<Cell> cellitA = rowA.cellIterator();
            Iterator<Cell> cellitB = rowB.cellIterator();

            Cell celA = cellitA.next();
            Cell celB = cellitB.next();

            //Se esta en las celdas del numero de Aviso
            celA = cellitA.next();
            celB = cellitB.next();

            int numAvisoA = (int) celA.getNumericCellValue();
            int numAvisoB = (int) celB.getNumericCellValue();

            if (numAvisoA != numAvisoB) {
                System.out.println("Numero de Aviso: " + numAvisoA);
                continue;
            }

            //Se esta en las celdas de la categoria
            celA = cellitA.next();
            celB = cellitB.next();

            String textA = celA.getStringCellValue();
            //System.out.println("Categoria A: "+textA);
            String textB = celB.getStringCellValue();
            //System.out.println("Categoria B: "+textB);

            if (!textA.equals(textB)) {
                System.out.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA
                        + " - Texto D: " + textB);
                file.println("Fila: " + numFila + " Numero de Aviso: " + numAvisoA + " Texto B: " + textA
                        + " - Texto D: " + textB);
                totalDiferencias++;
            }

            numFila++;
        }

        System.out.println("\nTotal diferencias: " + totalDiferencias);
        file.println("\nTotal diferencias: " + totalDiferencias);

        file.close();

    } catch (FileNotFoundException fe) {
        fe.printStackTrace();
    } catch (IOException ie) {
        ie.printStackTrace();
    }

}

From source file:comparator.Comparator.java

public static void transcoding_Map_HUG() throws IOException {
    //Get the input files
    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"));
    //Get the workbook instance for XLS file 
    XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile);
    XSSFSheet mvcSheet;/*from  w ww . j a v a2 s  .co m*/
    Iterator<Row> mvcRowIterator;
    String mvcSheetName;
    int mvcCol;
    boolean mvcColFound;
    Row mvcRow;
    Row mvcRow2;
    Iterator<Cell> mvcCellIterator;
    boolean statusOK = false;

    //OUTPUT
    String code_src;
    String code_dest;
    String name_dest = "";
    String value_set_name_dest = "";
    String status = "none";
    String value_set_name_source = "";
    String value_set_oid_dest = "";
    String parent_system_code_dest = "";
    String parent_system_oid_dest = "";
    String comment = "";
    String map_level = "0";
    String review = "0";
    String version = "";

    //Prepare the output file
    Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Map_HUG\\map_hug_to_mvc_2.0.csv"),
            "UTF-8"));
    csvW.write('\ufeff');
    csvW.write(
            "code_src;code_dest;name_dest;value_set_name_dest;status;value_set_name_source;value_set_oid_dest;parent_system_code_dest;parent_system_oid_dest;comment;map_level;review;version;");
    csvW.write("\n");

    //Read csv map
    String map = "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Map_HUG\\map_hug_to_mvc_1_9.csv";
    try {
        BufferedReader br = new BufferedReader(new FileReader(map));
        String line = "";
        String csvSplitBy = ";";
        String[] maLigne;

        //jump over the first line
        br.readLine();
        //pour chaque ligne de la map
        while ((line = br.readLine()) != null) {
            statusOK = false;

            maLigne = line.split(csvSplitBy);
            code_src = maLigne[0];
            code_dest = maLigne[1];

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

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

                //Get the name of MTTC sheet, compare them MAP entries
                //MVC data files are called "epSOSsheetName"
                mvcSheetName = mvcSheet.getSheetName();

                //And process the file matching to find the good sheet
                if (mvcSheetName.equals(maLigne[3])) {
                    value_set_name_dest = mvcSheetName;
                    value_set_name_source = maLigne[5];

                    mvcCol = 0;
                    mvcColFound = false;

                    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("Parent Code System:"))) {
                                    mvcCol = mvcCell.getColumnIndex() + 1;
                                    mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK)
                                            .setCellType(Cell.CELL_TYPE_STRING);
                                    parent_system_code_dest = mvcRow.getCell(mvcCol).getStringCellValue()
                                            .trim();
                                }
                                if (mvcCell.getCellType() == 1
                                        && (mvcCell.getStringCellValue().equals("OID Parent Code System:"))) {
                                    mvcCol = mvcCell.getColumnIndex() + 1;
                                    mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK)
                                            .setCellType(Cell.CELL_TYPE_STRING);
                                    parent_system_oid_dest = mvcRow.getCell(mvcCol).getStringCellValue().trim();
                                }
                                if (mvcCell.getCellType() == 1
                                        && (mvcCell.getStringCellValue().equals("epSOS OID:"))) {
                                    mvcCol = mvcCell.getColumnIndex() + 1;
                                    mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK)
                                            .setCellType(Cell.CELL_TYPE_STRING);
                                    value_set_oid_dest = mvcRow.getCell(mvcCol).getStringCellValue().trim();
                                }
                                if (mvcCell.getCellType() == 1
                                        && (mvcCell.getStringCellValue().equals("version:"))) {
                                    mvcCol = mvcCell.getColumnIndex() + 1;
                                    mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK)
                                            .setCellType(Cell.CELL_TYPE_STRING);
                                    version = mvcRow.getCell(mvcCol).getStringCellValue().trim();
                                }

                                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);
                            if (mvcRow.getCell(mvcCol).getStringCellValue().trim().equals(code_dest)) {
                                statusOK = true;
                                mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                        .setCellType(Cell.CELL_TYPE_STRING);
                                name_dest = mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim();
                                break;
                            }

                        }
                    }
                    if (statusOK == true) {
                        break;
                    } else {
                        parent_system_code_dest = "";
                        parent_system_oid_dest = "";
                        value_set_oid_dest = "";
                        version = "";
                    }
                }
            }

            if (statusOK != true) {
                //TO CHECK MANUALY
                status = "manual";
                name_dest = maLigne[2];
                comment = "mvc2.0 no hug code";
            }

            //Write the mapping
            csvW.write(code_src + ";" + code_dest + ";" + name_dest + ";" + value_set_name_dest + ";" + status
                    + ";" + value_set_name_source + ";" + value_set_oid_dest + ";" + parent_system_code_dest
                    + ";" + parent_system_oid_dest + ";" + comment + ";" + map_level + ";" + review + ";"
                    + version + ";");
            csvW.write("\n");
            //reset status
            status = "none";
            comment = "";

        }

        br.close();

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

    csvW.flush();
    csvW.close();

}

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//from   w ww . j  a  va 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();
}