List of usage examples for org.apache.poi.ss.usermodel Cell getStringCellValue
String getStringCellValue();
For numeric cells we throw an exception.
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 w w. j a va 2 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/* ww w.j a v a2 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;/* www .j a v a2 s .c om*/ 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:connect.Bootstrap.java
public static boolean bootstrap() { Connection conn = DatabaseConnectionManager.connect(); try {/*ww w . ja v a 2 s.c om*/ int noOfLines = 0; ArrayList<Data> list = new ArrayList<>(); Set<Customer> clist = new HashSet<>(); Set<Outlet> olist = new HashSet<>(); InputStream is = new FileInputStream(new File("./excel/SMUX - Outlet Data V1.xlsx")); StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .sheetIndex(0) // index of sheet to use (defaults to 0) .read(is); // InputStream or File for XLSX file (required) int counter = 0; for (Row r : reader) { counter++; //initialize a data object Data data = new Data(0, 0, "NULL", 0, "NULL", "", "Outlet", 0, 0, "", "", 0, 0, 0); if (noOfLines > 0) { // For each row, iterate through each columns Iterator<Cell> cellIterator = r.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: //customer id int custid = (int) cell.getNumericCellValue(); data.setCustomerId(custid); break; case 1: //age try { int age = (int) cell.getNumericCellValue(); data.setAge(age); } catch (Exception e) { //leave it as 0 } break; case 2: //gender String gender = cell.getStringCellValue(); data.setGender(gender); //System.out.println("case 2"); break; case 3: //transact id data.setTransactId((int) cell.getNumericCellValue()); break; case 4: //transact date data.setTransactDate(df.format(cell.getDateCellValue())); break; case 5: //transact time time.setTime(cell.getDateCellValue()); data.setTransactTime(df2.format(time.getTime())); break; case 6: //outlet data.setOutlet(cell.getStringCellValue()); break; case 7: //outlet district data.setOutletDistrict((int) cell.getNumericCellValue()); break; case 8: //transact details id data.setTransactDetailsId((int) cell.getNumericCellValue()); break; case 9: //item //try { data.setItem(cell.getStringCellValue()); break; case 10: //item description data.setItemDesc(cell.getStringCellValue()); break; case 11: //quantity data.setQuantity((int) cell.getNumericCellValue()); break; case 12: //price data.setPrice(cell.getNumericCellValue()); break; case 13: //spending double spending = cell.getNumericCellValue(); data.setSpending(spending); break; default: } } list.add(data); } noOfLines++; if (noOfLines == 32740) { //establish connection, sql, execute sql try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeBatch(); pstmt.close(); conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } noOfLines = 1; //System.out.println("batch submitted"); } else if (counter > 556560 && counter <= 556580) { try { String sql = "Insert into data VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = null; //upload by batches conn.setAutoCommit(false); //total 556581 pstmt = conn.prepareStatement(sql); //loop through user list for (Data d : list) { pstmt.setInt(1, d.getCustomerId()); pstmt.setInt(2, d.getAge()); pstmt.setString(3, d.getGender()); pstmt.setInt(4, d.getTransactId()); pstmt.setString(5, d.getTransactDate()); pstmt.setString(6, d.getTransactTime()); pstmt.setString(7, d.getOutlet()); pstmt.setInt(8, d.getOutletDistrict()); pstmt.setInt(9, d.getTransactDetailsId()); pstmt.setString(10, d.getItem()); pstmt.setString(11, d.getItemDesc()); pstmt.setInt(12, d.getQuantity()); pstmt.setDouble(13, d.getPrice()); pstmt.setDouble(14, d.getSpending()); //pstmt.addBatch(); } //System.out.println(pstmt); pstmt.executeUpdate(); //conn.commit(); System.out.println("current counter = " + counter); } catch (SQLException k) { k.printStackTrace(); } } } } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } return true; }
From source file:connect.LoadCategory.java
public static boolean loadCategory() { Connection conn = DatabaseConnectionManager.connect(); PreparedStatement pstmt = null; PreparedStatement pstmt2 = null; ResultSet rs = null;//from w ww.j a va 2 s . co m try { int noOfLines = 0; HashMap<String, FoodCategory> foodCategoryHashMap = new HashMap<>(); InputStream is = new FileInputStream(new File("./excel/category.xlsx")); StreamingReader reader = StreamingReader.builder().rowCacheSize(100) // number of rows to keep in memory (defaults to 10) .bufferSize(4096) // buffer size to use when reading InputStream to file (defaults to 1024) .sheetIndex(0) // index of sheet to use (defaults to 0) .read(is); // InputStream or File for XLSX file (required) int counter = 0; for (Row r : reader) { counter++; FoodCategory foodCategory = new FoodCategory("", "", 0.0, "", "", "", ""); if (noOfLines > 0) { // For each row, iterate through each columns Iterator<Cell> cellIterator = r.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellIndex = cell.getColumnIndex(); switch (cellIndex) { case 0: foodCategory.setItem_id(cell.getStringCellValue()); break; case 1: foodCategory.setItemDesc(cell.getStringCellValue()); break; case 2: foodCategory.setCourse(cell.getStringCellValue()); break; case 3: foodCategory.setOrigin(cell.getStringCellValue()); break; case 4: foodCategory.setTags(cell.getStringCellValue()); break; case 5: foodCategory.setHotcold(cell.getStringCellValue()); break; default: } } foodCategoryHashMap.put(foodCategory.getItem_id() + "|" + foodCategory.getItemDesc(), foodCategory); } noOfLines++; } pstmt = conn.prepareStatement(SELECTUNIQUECATEGORY); rs = pstmt.executeQuery(); ArrayList<FoodCategory> foodCategoryList = new ArrayList<>(); while (rs.next()) { String item = rs.getString(1); String itemdesc = rs.getString(2); double price = rs.getDouble(3); foodCategoryList.add(new FoodCategory(item, itemdesc, price)); } conn.setAutoCommit(false); pstmt2 = conn.prepareStatement(INSERTCATEGORYSQL); for (FoodCategory foodCategory : foodCategoryList) { pstmt2.setString(1, foodCategory.getItem_id()); pstmt2.setString(2, foodCategory.getItemDesc()); pstmt2.setDouble(3, foodCategory.getPrice()); String key = foodCategory.getItem_id() + "|" + foodCategory.getItemDesc(); FoodCategory mapFoodCategory = foodCategoryHashMap.get(key); if (mapFoodCategory == null) { continue; } pstmt2.setString(4, mapFoodCategory.getCourse()); pstmt2.setString(5, mapFoodCategory.getOrigin()); pstmt2.setString(6, mapFoodCategory.getTags()); pstmt2.setString(7, mapFoodCategory.getHotcold()); pstmt2.addBatch(); } pstmt2.executeBatch(); conn.commit(); conn.setAutoCommit(true); } catch (Exception e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt != null) { try { pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if (pstmt2 != null) { try { pstmt2.close(); } catch (SQLException e) { e.printStackTrace(); } } } return true; }
From source file:controller.DAOData.java
public Plan readPlan() { ArrayList<Course> courses = new ArrayList<>(); //takes the sheet you ask for XSSFSheet sheet = workbook.getSheet("PLAN"); //for to go over the sheet info - like the rows for (Row row : sheet) { String code = null;/*from w w w. j a va2s . c om*/ String name = null; double credits = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: name = cell.getStringCellValue(); break; case 1: code = cell.getStringCellValue(); break; case 2: credits = cell.getNumericCellValue(); break; } } } if (code != null) { Course course = new Course(code, name, credits); courses.add(course); code = null; name = null; credits = 0; } } Plan plan = new Plan(410, 2010, courses); return plan; }
From source file:controller.DAOData.java
public ArrayList<Employee> readProfessors() { ArrayList<Employee> employees = new ArrayList<>(); //takes the sheet you ask for XSSFSheet sheet = workbook.getSheet("PROFESORES"); //for to go over the sheet info - like the rows for (Row row : sheet) { String id = null;//from w ww . java2 s.c o m String name = null; String email = null; String phone = null; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: id = cell.getStringCellValue(); break; case 1: name = cell.getStringCellValue(); break; case 2: email = cell.getStringCellValue(); break; case 3: phone = cell.getStringCellValue(); break; } } } if (id != null) { employees.add(new Employee(EEmployeeRol.PROFESSOR, id, name, email, phone)); id = null; name = null; email = null; phone = null; } } return employees; }
From source file:controller.DAOData.java
public ArrayList<Object> readGroups() { ArrayList<Object> groups = new ArrayList(); XSSFSheet sheet = workbook.getSheet("OFERTA"); //for to go over the sheet info - like the rows for (Row row : sheet) { String period = null;//ww w .j a v a 2 s .c o m Course course = null; int numberGroup = 0; Employee employee = null; ArrayList<Schedule> schedules = new ArrayList(); String scheduleS = null; String classroom = null; for (Cell cell : row) { if (row.getRowNum() != 0) { Schedule schedule = new Schedule(); switch (cell.getColumnIndex()) { case 0: period = cell.getStringCellValue(); break; case 1: course = School.getInstance().selectCourse(cell.getStringCellValue()); break; case 2: numberGroup = (int) cell.getNumericCellValue(); break; case 3: employee = School.getInstance().findEmployee(cell.getStringCellValue()); break; case 4: scheduleS = cell.getStringCellValue(); break; case 5: classroom = cell.getStringCellValue(); break; } } } if (period != null) { schedules = identifySchedules(scheduleS, classroom); groups.add(new Group(numberGroup, period, true, employee, schedules, course)); numberGroup = 0; course = null; period = null; employee = null; schedules = null; } } return groups; }
From source file:controller.DAORequest.java
public ArrayList<Object> readRequests() { ArrayList<Object> requests = new ArrayList(); ArrayList<Resolution> resolutions = readResolutions(); XSSFSheet sheet = workbook.getSheetAt(0); for (Row row : sheet) { System.out.println("1"); Date date = null;//from w w w. j av a 2 s .c o m Student affected; String carnet = null; String name = null; String email = null; String celStu = null; Group group; String period = null; String course = null; int numberGroup = 0; EInconsistencie einconsistencie; String inconsistencie = null; String description = null; Person requester; String idReq = null; String nameReq = null; ERequestState reqState; String sreqState = null; int numRes = 0; for (Cell cell : row) { if (row.getRowNum() != 0) { switch (cell.getColumnIndex()) { case 0: date = row.getCell(0).getDateCellValue(); break; case 1: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) carnet = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) carnet = cell.getStringCellValue(); break; case 2: //es el nombre del estudiante name = cell.getStringCellValue(); break; case 3: email = cell.getStringCellValue(); break; case 4: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) celStu = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) celStu = cell.getStringCellValue(); break; case 5: period = cell.getStringCellValue(); break; case 6: course = cell.getStringCellValue(); break; case 7: numberGroup = (int) cell.getNumericCellValue(); break; case 8: inconsistencie = cell.getStringCellValue(); break; case 9: description = cell.getStringCellValue(); break; case 10: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) idReq = Integer.toString((int) cell.getNumericCellValue()); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) idReq = cell.getStringCellValue(); break; case 11: nameReq = cell.getStringCellValue(); break; case 12: sreqState = cell.getStringCellValue(); break; case 13: if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) numRes = (int) cell.getNumericCellValue(); else if (cell.getCellType() == Cell.CELL_TYPE_STRING) numRes = Integer.parseInt(cell.getStringCellValue()); break; } } } if (carnet != null) { affected = new Student(carnet, name, email, celStu); requester = new Person(idReq, nameReq, null, null); group = School.getInstance().selectGroup(period, numberGroup, course); einconsistencie = identifyEInconsistencie(inconsistencie); if (sreqState == null) { sreqState = "PENDIENTE"; } reqState = identifyEReqState(sreqState); Request request = new Request(date, description, einconsistencie, reqState, affected, requester, group); request.setRequestState(reqState); requests.add(request); if (numRes != 0) { for (Resolution r : resolutions) { if (r.getId() == numRes) request.setResolution(r); System.out.println("linquea numRes: " + numRes + " con ReqId:" + request.getId()); } } } } return requests; }
From source file:controller.DAORequest.java
private ArrayList<Resolution> readResolutions() { ArrayList<Resolution> resolutions = new ArrayList(); try {/*from www. ja v a2 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; }