List of usage examples for org.apache.poi.hssf.util CellReference getCellRefParts
public String[] getCellRefParts()
From source file:com.krawler.spring.importFunctionality.ImportHandler.java
License:Open Source License
public JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); }/*from w w w. ja v a 2 s.co m*/ JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = Double.toString(cell.getNumericCellValue()); java.util.Date date1 = HSSFDateUtil.getJavaDate(Double.parseDouble(val)); DateFormat sdf = new SimpleDateFormat(df); val = sdf.format(date1); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid/*from ww w .j av a2 s . co m*/ * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLS(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filename)); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb); HSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int maxSheetColCnt = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } int firstValidRec = 0; JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { HSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i != 0 && firstValidRec == 0 && !jobj.has("Header")) // get first valid row which having some columns with data as a header firstValidRec = i; // if(i==0) { maxCol = row.getLastCellNum(); if (maxSheetColCnt < maxCol) // get max column count maxSheetColCnt = maxCol; // } for (int cellcount = 0; cellcount < maxCol; cellcount++) { HSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case HSSFCell.CELL_TYPE_NUMERIC: if (HSSFDateUtil.isCellDateFormatted(cell)) { val = cell.toString();//Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case HSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == firstValidRec) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxSheetColCnt); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLS has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:com.krawler.spring.importFunctionality.ImportUtil.java
License:Open Source License
/** * Generate the preview of the xls grid// w w w. j a v a 2 s. co m * @param filename * @param sheetNo * @return * @throws FileNotFoundException * @throws IOException * @throws JSONException */ public static JSONObject parseXLSX(String filename, int sheetNo) throws FileNotFoundException, IOException, JSONException { JSONObject jobj = new JSONObject(); FileInputStream fs = new FileInputStream(filename); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet sheet = wb.getSheetAt(sheetNo); //DateFormat sdf = new SimpleDateFormat(df); int startRow = 0; int maxRow = sheet.getLastRowNum(); int maxCol = 0; int noOfRowsDisplayforSample = 20; if (noOfRowsDisplayforSample > sheet.getLastRowNum()) { noOfRowsDisplayforSample = sheet.getLastRowNum(); } JSONArray jArr = new JSONArray(); try { for (int i = 0; i <= noOfRowsDisplayforSample; i++) { XSSFRow row = sheet.getRow(i); JSONObject obj = new JSONObject(); JSONObject jtemp1 = new JSONObject(); if (row == null) { continue; } if (i == 0) { maxCol = row.getLastCellNum(); } for (int cellcount = 0; cellcount < maxCol; cellcount++) { XSSFCell cell = row.getCell(cellcount); CellReference cref = new CellReference(i, cellcount); String colHeader = cref.getCellRefParts()[2]; String val = null; if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { val = Long.toString(cell.getDateCellValue().getTime()); } else { val = dfmt.format(cell.getNumericCellValue()); } break; case XSSFCell.CELL_TYPE_STRING: val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString()); break; } } if (i == 0) { // List of Headers (Consider first row as Headers) if (val != null) { jtemp1 = new JSONObject(); jtemp1.put("header", val == null ? "" : val); jtemp1.put("index", cellcount); jobj.append("Header", jtemp1); } } obj.put(colHeader, val); } // if(obj.length()>0){ //Don't show blank row in preview grid[SK] jArr.put(obj); // } } } catch (Exception ex) { Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex); } jobj.put("startrow", startRow); jobj.put("maxrow", maxRow); jobj.put("maxcol", maxCol); jobj.put("index", sheetNo); jobj.put("data", jArr); jobj.put("filename", filename); jobj.put("msg", "XLSX has been successfully uploaded"); jobj.put("lsuccess", true); jobj.put("valid", true); return jobj; }
From source file:de.thb.ue.backend.util.EvaluationExcelFileGenerator.java
License:Apache License
public void generateExcelFile() { Row row;/*from w w w .j a v a2 s .com*/ Cell cell; int yOffset = 1; File workingDirectory = new File( (workingDirectoryPath.isEmpty() ? "" : (workingDirectoryPath + File.separatorChar)) + evaluationUID); if (!workingDirectory.exists()) { workingDirectory.mkdir(); } File file = new File(workingDirectory, "auswertung.xls"); try { FileOutputStream out = new FileOutputStream(file); Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Evaluation"); // configure cell styles configureCellStyles(wb); /* * ********************************** * begin formatting document * ********************************** * */ //construct first row of infopanel yOffset = constructInfoPanelRow("Lehrveranstaltung", subject, numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.topLeftCorner, InfoPanelBorderStyles.top, InfoPanelBorderStyles.topRightCorner); //construct second row of infopanel yOffset = constructInfoPanelRow("Semester", semesterType == SemesterType.WINTER ? "Winter" : "Sommer", numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct third row of infopanel StringBuilder tutors = new StringBuilder(); for (int i = 0; i < this.tutors.size(); i++) { if (i + 1 < this.tutors.size()) { tutors.append(this.tutors.get(i)).append(", "); } else { tutors.append(this.tutors.get(i)); } } yOffset = constructInfoPanelRow("Lehrende(r)", tutors.toString(), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fourth row of infopanel yOffset = constructInfoPanelRow("Datum der Befragung", dateOfEvaluation.toString("dd.MM.yy HH:mm"), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct fifth row of infopanel yOffset = constructInfoPanelRow("Anzahl der Teilnehmer", Integer.toString(numberStudentsAll), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.left, InfoPanelBorderStyles.none, InfoPanelBorderStyles.right); //construct sixth row of infopanel () last yOffset = constructInfoPanelRow("Anzahl der ausgefllten Fragebgen", Integer.toString(numberStudentsVoted), numberStudentsAll, yOffset, wb, sheet, InfoPanelBorderStyles.bottomLeftCorner, InfoPanelBorderStyles.bottom, InfoPanelBorderStyles.bottomRightCorner); //begin construction of evaluationPanel yOffset++; row = sheet.createRow(yOffset); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellValue("Frage"); cell.setCellStyle(headerStyle); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellValue("MW"); cell.setCellStyle(headerStyle); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellValue("Ifd NR."); cell.setCellStyle(headerStyle); cell = row.createCell(4); cell.setCellStyle(commonStyle); // add count of valid evaluations (how many students voted) (horizontal) for (int i = 0; i < numberStudentsVoted; i++) { cell = row.createCell(i + 5); cell.setCellValue(i + 1); sheet.setColumnWidth(cell.getColumnIndex(), 4 * 256); cell.setCellStyle(headerStyle); } // get letter of last student column CellReference cellReference = new CellReference(cell.getRowIndex(), cell.getColumnIndex()); String endCellName = cellReference.getCellRefParts()[2]; Row headRow = row; for (int i = 1; i < mcQuestionTexts.size() + 1; i++) { //add number of questions row = sheet.createRow(i + yOffset); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(i); cell.setCellStyle(commonStyle); //add average formula cell = row.createCell(2, Cell.CELL_TYPE_FORMULA); //formlua works with blanks, empty strings and negative values String formula = "SUMPRODUCT(ABS(N(+F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))/COUNT(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")"; //String averageFormula = "AVERAGE(IF(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + "<>\"\", ABS(F" + (i + yOffset + 1) + ":" + endCellName + (i + yOffset + 1) + ")))"; cell.setCellFormula(formula); cell.setCellStyle(commonStyle); //fill blank cells cell = row.createCell(3); cell.setCellStyle(commonStyle); //add question texts sheet.setColumnWidth(4, findLongestString(mcQuestionTexts) * 256 * (wb.getFontAt(questionStyle.getFontIndex()).getFontHeightInPoints()) / 10); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellValue(mcQuestionTexts.get(i - 1)); cell.setCellStyle(questionStyle); } //add student votes for (int i = 0; i < studentVotes.size(); i++) { Vote vote = studentVotes.get(i); for (int k = 0; k < mcQuestionTexts.size(); k++) { row = sheet.getRow(headRow.getRowNum() + 1 + k); cell = row.createCell(5 + i); for (MCAnswer answer : vote.getMcAnswers()) { //if question of inner loop equals question of outer loop we found // the correct question for this cell if (answer.getQuestion().getText().equals(mcQuestionTexts.get(k))) { Choice choice = answer.getChoice(); if (choice != null && choice.getGrade() != 0) { cell = colorizeCell(cell, wb, choice.getGrade()); cell.setCellValue(answer.getChoice().getGrade()); } else { cell = colorizeCell(cell, wb, -1); cell.setCellValue(""); } } } } } // include textual answers createTextualAnswers(studentVotes, textualQuestionTexts, sheet, wb); wb.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }