List of usage examples for org.apache.poi.ss.extractor ExcelExtractor getText
public String getText();
From source file:org.cirdles.squid.tasks.squidTask25.TaskSquid25.java
License:Apache License
public static TaskSquid25 importSquidTaskFile(File squidTaskFile) { taskSquid25 = null;/* ww w .j a v a 2s .co m*/ try { InputStream inp = new FileInputStream(squidTaskFile); HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inp)); ExcelExtractor extractor = new org.apache.poi.hssf.extractor.ExcelExtractor(wb); extractor.setFormulasNotResults(true); extractor.setIncludeSheetNames(false); String text = extractor.getText(); String[] lines = text.split("\n"); boolean isSquid2_20 = false; if (lines[0].startsWith("Created by SQUID")) { taskSquid25 = new TaskSquid25(); taskSquid25.constantNames = new ArrayList<>(); taskSquid25.constantValues = new ArrayList<>(); taskSquid25.squidVersion = lines[0].split("\t")[1]; // July 2018 detect if version 2.20 isSquid2_20 = (taskSquid25.squidVersion.startsWith("2.20")); int firstRow = Integer.parseInt(lines[1].split("\t")[1]) - 1; taskSquid25.squidTaskFileName = lines[firstRow].split("\t")[1]; taskSquid25.taskType = TaskTypeEnum .valueOf(lines[firstRow + 1].split("\t")[1].toUpperCase(Locale.ENGLISH)); taskSquid25.taskName = lines[firstRow + 2].split("\t")[1]; taskSquid25.taskDescription = lines[firstRow + 3].split("\t")[1]; taskSquid25.authorName = ""; taskSquid25.labName = lines[firstRow + 7].split("\t")[1]; String[] nominalMasses = lines[firstRow + 13].split("\t"); int countOfMasses = Integer.valueOf(nominalMasses[1]); taskSquid25.nominalMasses = new ArrayList<>(); for (int i = 0; i < countOfMasses; i++) { taskSquid25.nominalMasses.add(nominalMasses[i + 2]); } // July 2018 // decrement first row to handle missing line for Hidden Masses in Squid 2.20 if (isSquid2_20) { firstRow--; } String[] ratioStrings = lines[firstRow + 15].split("\t"); if (isSquid2_20 && ratioStrings[0].toUpperCase(Locale.ENGLISH).startsWith("HIDDEN")) { // special case of 2.2 where this row exists firstRow++; ratioStrings = lines[firstRow + 15].split("\t"); } int countOfRatios = Integer.valueOf(ratioStrings[1]); taskSquid25.ratioNames = new ArrayList<>(); for (int i = 0; i < countOfRatios; i++) { taskSquid25.ratioNames.add(ratioStrings[i + 2]); } String[] backgroundStrings = lines[firstRow + 19].split("\t"); taskSquid25.backgroundMass = backgroundStrings[1]; String[] parentNuclideStrings = lines[firstRow + 20].split("\t"); taskSquid25.parentNuclide = parentNuclideStrings[1]; String[] directAltPDStrings = lines[firstRow + 21].split("\t"); taskSquid25.directAltPD = Boolean.valueOf(directAltPDStrings[1]); taskSquid25.task25Equations = new ArrayList<>(); // determine where uranium or thorium is primary or secondary String primaryUThEqnName = UNCOR206PB238U_CALIB_CONST; String primaryUThEqnOtherName = UNCOR208PB232TH_CALIB_CONST; taskSquid25.specialSquidFourExpressionsMap = new TreeMap<>(); String[] primaryUThPbEqn = lines[firstRow + 22].split("\t"); if (primaryUThPbEqn.length > 1) { if (taskSquid25.parentNuclide.contains("232")) { primaryUThEqnName = UNCOR208PB232TH_CALIB_CONST; primaryUThEqnOtherName = UNCOR206PB238U_CALIB_CONST; } taskSquid25.specialSquidFourExpressionsMap.put(primaryUThEqnName, prepareSquid25ExcelEquationStringForSquid3(primaryUThPbEqn[1])); } String[] secondaryUThPbEqn = lines[firstRow + 23].split("\t"); if (secondaryUThPbEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(primaryUThEqnOtherName, prepareSquid25ExcelEquationStringForSquid3(secondaryUThPbEqn[1])); } String[] ThUEqn = lines[firstRow + 24].split("\t"); if (ThUEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(TH_U_EXP_DEFAULT, prepareSquid25ExcelEquationStringForSquid3(ThUEqn[1])); } String[] ppmParentEqn = lines[firstRow + 25].split("\t"); if (ppmParentEqn.length > 1) { taskSquid25.specialSquidFourExpressionsMap.put(PARENT_ELEMENT_CONC_CONST, prepareSquid25ExcelEquationStringForSquid3(ppmParentEqn[1])); } String[] equations = lines[firstRow + 26].split("\t"); int countOfEquations = Integer.valueOf(equations[1]); String[] equationNames = lines[firstRow + 27].split("\t"); // June 2019 skip Hidden row 34 String[] switchST = lines[firstRow + 28].split("\t"); if (switchST[0].toUpperCase(Locale.ENGLISH).startsWith("HIDDEN")) { firstRow++; } // these switches split into a//n array of length equations mius 1 in Squid2.20 due to missing count entry switchST = lines[firstRow + 28].split("\t"); String[] switchSA = lines[firstRow + 29].split("\t"); String[] switchSC = lines[firstRow + 30].split("\t"); String[] switchNU = lines[firstRow + 32].split("\t"); // run constants first so can detect in equations String[] constantNamesSource = lines[firstRow + 40].split("\t"); String[] constantValuesSource = lines[firstRow + 41].split("\t"); int countOfConstants = 0; if (constantNamesSource.length > 1) { countOfConstants = Integer.valueOf(constantNamesSource[1]); } for (int i = 0; i < countOfConstants; i++) { taskSquid25.constantNames.add(constantNamesSource[i + 2].replaceFirst("_", "")); taskSquid25.constantValues.add(constantValuesSource[i + 2]); } for (int i = 0; i < countOfEquations; i++) { // handle backwards logic of Squid25 where both ST, SA false, means both True boolean switchRM = Boolean.parseBoolean(switchST[i + (isSquid2_20 ? 1 : 2)]); boolean switchUN = Boolean.parseBoolean(switchSA[i + (isSquid2_20 ? 1 : 2)]); if (!switchRM && !switchUN) { switchRM = true; switchUN = true; } String excelExpression = prepareSquid25ExcelEquationStringForSquid3(equations[i + 2]); if (excelExpression.length() > 0) { //detect if name contains "Age" or "abs" - undo change to % for errors if ((equationNames[i + 2].toUpperCase(Locale.ENGLISH).contains("ABS")) || (equationNames[i + 2].toUpperCase(Locale.ENGLISH).contains("AGE"))) { if (excelExpression.startsWith("[%\"")) { excelExpression = excelExpression.replaceFirst("\\[%\"", "\\[\""); } } taskSquid25.task25Equations.add(new TaskSquid25Equation(excelExpression, prepareSquid25ExcelEquationNameForSquid3(equationNames[i + 2]), switchRM, switchUN, Boolean.parseBoolean(switchSC[i + (isSquid2_20 ? 1 : 2)]), Boolean.parseBoolean(switchNU[i + (isSquid2_20 ? 1 : 2)]), false, false)); } } } } catch (IOException iOException) { } return taskSquid25; }