Example usage for org.apache.poi.ss.usermodel Cell getColumnIndex

List of usage examples for org.apache.poi.ss.usermodel Cell getColumnIndex

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Cell
  6. getColumnIndex

Introduction

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

Prototype

int getColumnIndex();

Source Link

Document

Returns column index of this cell

Usage

From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java

License:Open Source License

private void doCellCustomStyling(final Cell cell) {
    CellStyle cellStyle = cell.getCellStyle();
    final Integer key = (int) cellStyle.getIndex();
    if (key == 0) { // default style
        return;//from ww  w.  jav  a2  s .  co m
    }

    // merged regions have their borders in edge cells that are "invisible"
    // inside the region -> right and bottom cells need to be transfered to
    // the actual merged cell
    final int columnIndex = cell.getColumnIndex();
    final int rowIndex = cell.getRowIndex();
    MergedRegion region = spreadsheet.mergedRegionContainer.getMergedRegion((columnIndex + 1), (rowIndex + 1));
    if (region != null) {
        final String borderRight = getBorderRightStyle(cellStyle);
        final String borderBottom = getBorderBottomStyle(cellStyle);
        if ((borderRight != null && !borderRight.isEmpty())
                || (borderBottom != null && !borderBottom.isEmpty())) {
            StringBuilder sb = new StringBuilder(".col");
            sb.append(region.col1);
            sb.append(".row");
            sb.append(region.row1);
            final String cssKey = sb.toString();
            final String currentBorders = mergedCellBorders.get(cssKey);
            StringBuilder style;
            if (currentBorders != null && !currentBorders.isEmpty()) {
                style = new StringBuilder(currentBorders);
            } else {
                style = new StringBuilder();
            }
            if (borderRight != null && !borderRight.isEmpty()
                    && (currentBorders == null || !currentBorders.contains("border-right"))) {
                style.append(borderRight);
            }
            if (borderBottom != null && !borderBottom.isEmpty()
                    && (currentBorders == null || !currentBorders.contains("border-bottom"))) {
                style.append(borderBottom);
            }
            final String newBorders = style.toString();
            if (!newBorders.isEmpty()) {
                mergedCellBorders.put(cssKey, newBorders);
            }
        }

    }

    // only take transfered borders into account on the (possible) merged
    // regions edges
    if (region == null || region.col1 == (columnIndex + 1) || region.col2 == (columnIndex + 1)
            || region.row1 == (rowIndex + 1) || region.row2 == (rowIndex + 1)) {

        if (shiftedBorderLeftStyles.containsKey(key)) {
            // need to add the border right style to previous cell on
            // left, which might be a merged cell
            if (columnIndex > 0) {
                int row, col;

                MergedRegion previousRegion = spreadsheet.mergedRegionContainer.getMergedRegion(columnIndex,
                        rowIndex + 1);
                if (previousRegion != null) {
                    col = previousRegion.col1;
                    row = previousRegion.row1;
                } else {
                    col = columnIndex;
                    row = rowIndex + 1;
                }
                insertMapEntryIfNeeded(shiftedBorderLeftStylesMap, key, row, col);
            }
        }
        if (shiftedBorderTopStyles.containsKey(key)) {
            // need to add the border bottom style to cell on previous
            // row, which might be a merged cell
            if (rowIndex > 0) {
                int row, col;
                MergedRegion previousRegion = spreadsheet.mergedRegionContainer.getMergedRegion(columnIndex + 1,
                        rowIndex);

                if (previousRegion != null) {
                    col = previousRegion.col1;
                    row = previousRegion.row1;
                } else {
                    col = columnIndex + 1;
                    row = rowIndex;
                }
                insertMapEntryIfNeeded(shiftedBorderTopStylesMap, key, row, col);

            }
        }

    }
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetUtil.java

License:Open Source License

/**
 * Translates cell coordinates from the given Cell object to a cell key used
 * to identify cells in the server<->client communiScation.
 * /*from   w  w w .  j av a2 s.  co  m*/
 * @param cell
 *            Cell to fetch the coordinates from
 * @return Cell key
 */
public static final String toKey(Cell cell) {
    return toKey(cell.getColumnIndex() + 1, cell.getRowIndex() + 1);
}

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

License:Apache License

/**
 * Adds the values from excel.//from ww  w .ja v a  2s .com
 * 
 * @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.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 {/*  ww w  .  ja  va2  s  .  c o m*/

        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:com.yqboots.initializer.core.builder.excel.MessageSheetBuilder.java

License:Apache License

/**
 * Gets the languages from the specified row.
 *
 * @param row the row of a sheet on Excel
 * @return list of languages//from   ww  w .j av a2  s. co  m
 */
private List<String> getLanguages(Row row) {
    final List<String> results = new ArrayList<>();
    for (Cell cell : row) {
        if (cell.getColumnIndex() < 1) {
            continue;
        }

        results.add(cell.getStringCellValue());
    }

    return results;
}

From source file:com.yqboots.initializer.core.builder.excel.SystemPropertiesSheetBuilder.java

License:Apache License

private static List<String> getProfiles(Row row) {
    final List<String> results = new ArrayList<>();
    for (final Cell cell : row) {
        if (cell.getColumnIndex() == 0) {
            continue;
        }/*  w  w w  .  j  av a2  s .c o  m*/

        final String profile = cell.getStringCellValue();
        if (StringUtils.isBlank(profile)) {
            break;
        }

        results.add(profile);
    }

    Assert.isTrue(!results.isEmpty());
    return results;
}

From source file:common.ReadExcelData.java

License:Apache License

public ArrayList<String> getColumnValue(String sheetName, String header) {
    HSSFSheet sheet = workbook.getSheet(sheetName);
    ArrayList<String> list = new ArrayList<String>();
    int index = 0;
    for (Row r : sheet) {
        for (Cell c : r) {
            if (c.getCellType() != Cell.CELL_TYPE_NUMERIC)
                if (c.getStringCellValue().equals(header))
                    index = c.getColumnIndex();
        }/* w  w  w . ja va2s. co m*/
        list.add(r.getCell(index).getStringCellValue());
    }
    return list;
}

From source file:common.ReadExcelData.java

License:Apache License

public String getCellValue(int index, String heading) {
    String cellValue = "";
    try {/*from   w ww .ja v a2 s  . c o m*/
        sheet = workbook.getSheet(sheetName);
        row = sheet.getRow(0);
        int cellNumber = 0;
        for (Cell cell : row) {
            if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
                if (cell.getRichStringCellValue().getString().trim().equals(heading)) {
                    cellNumber = cell.getColumnIndex();
                }
            }
        }
        row = sheet.getRow(findRow(sheet, index));
        cell = row.getCell(cellNumber);
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_NUMERIC:
            cellValue = String.valueOf(((long) cell.getNumericCellValue()));
            break;
        case Cell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            cellValue = String.valueOf(cell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_BLANK:
            cellValue = null;
        }
    } catch (NullPointerException e) {
        cellValue = null;
    }
    return cellValue;
}

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;//w  ww .j a v a 2 s .  c  o 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// w  w  w.j a v a  2s .  c o  m
    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();
}