List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:com.bayareasoftware.chartengine.ds.util.ExcelInference.java
License:Apache License
private static boolean match(Row row1, Row row2) { if (row1.getLastCellNum() != row2.getLastCellNum() || row1.getFirstCellNum() != row2.getFirstCellNum()) { return false; }//from w w w . ja va2 s . c o m //p("rows " + row1.getRowNum() + "/" + row2.getRowNum() + " MIGHT be a match"); Iterator cs1 = row1.cellIterator(); Iterator cs2 = row2.cellIterator(); while (cs1.hasNext()) { HSSFCell c1 = (HSSFCell) cs1.next(); if (!cs2.hasNext()) { return false; } HSSFCell c2 = (HSSFCell) cs2.next(); if (c1.getCellNum() != c2.getCellNum()) return false; if (c1.getCellType() != c2.getCellType()) return false; } return true; }
From source file:com.ben12.reta.util.RETAAnalysis.java
License:Open Source License
public void writeExcel(Window parent) throws IOException, InvalidFormatException { logger.info("Start write excel output"); Path outputFile = Paths.get(output); if (!outputFile.isAbsolute()) { Path root = config.getAbsoluteFile().getParentFile().toPath(); outputFile = root.resolve(outputFile); }//www . j a v a 2s. c o m // test using template InputStream is = getClass().getResourceAsStream("/com/ben12/reta/resources/template/template.xlsx"); ExcelTransformer transformer = new ExcelTransformer(); List<String> sheetNames = new ArrayList<>(); List<String> sheetTemplateNames = new ArrayList<>(); for (InputRequirementSource requirementSource : requirementSources.values()) { sheetTemplateNames.add("DOCUMENT"); sheetTemplateNames.add("COVERAGE"); sheetNames.add(requirementSource.getName()); sheetNames.add(requirementSource.getName() + " coverage"); } List<Map<String, Object>> sheetValues = new ArrayList<>(); for (InputRequirementSource source : requirementSources.values()) { Map<String, Object> values = new HashMap<>(); values.put("source", source); values.put("null", null); values.put("line", "\n"); Set<String> attributes = new LinkedHashSet<>(); attributes.add(Requirement.ATTRIBUTE_ID); if (source.getAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) { attributes.add(Requirement.ATTRIBUTE_VERSION); } attributes.addAll(source.getAttributesGroup().keySet()); attributes.remove(Requirement.ATTRIBUTE_TEXT); values.put("attributes", attributes); Set<String> refAttributes = new LinkedHashSet<>(); refAttributes.add(Requirement.ATTRIBUTE_ID); if (source.getRefAttributesGroup().containsKey(Requirement.ATTRIBUTE_VERSION)) { refAttributes.add(Requirement.ATTRIBUTE_VERSION); } refAttributes.addAll(source.getRefAttributesGroup().keySet()); refAttributes.remove(Requirement.ATTRIBUTE_TEXT); values.put("refAttributes", refAttributes); sheetValues.add(values); sheetValues.add(values); } Workbook wb = transformer.transform(is, sheetTemplateNames, sheetNames, sheetValues); int sheetCount = wb.getNumberOfSheets(); for (int i = 0; i < sheetCount; i++) { Sheet sheet = wb.getSheetAt(i); int columns = 0; for (int j = 0; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); if (row != null) { row.setHeight((short) -1); columns = Math.max(columns, row.getLastCellNum() + 1); } } for (int j = 0; j < columns; j++) { sheet.autoSizeColumn(j); } } try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) { wb.write(fos); } catch (FileNotFoundException e) { int confirm = MessageDialog.showQuestionMessage(null, "Excel output file must be closed."); if (confirm == MessageDialog.OK_OPTION) { try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) { wb.write(fos); } catch (IOException e2) { throw e2; } } else { throw e; } } logger.info("End write excel output"); }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean deleteColumn(int[] columnIndex) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Sheet sheet = null;//from w w w . j a v a2 s. co m try { sheet = getSheet(); if (sheet == null) { return false; } for (int i = 0; i <= sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i); for (int j = columnIndex.length - 1; j > -1; j--) { //???? for (int k = columnIndex[j]; k < tmpRow.getLastCellNum(); k++) { Cell tmpCell = tmpRow.getCell(k); if (null != tmpCell) { tmpRow.removeCell(tmpCell); } Cell rightCell = tmpRow.getCell(k + 1); if (null != rightCell) { HSSFRow hr = (HSSFRow) tmpRow; hr.moveCell((HSSFCell) rightCell, (short) k); } } } } m_InputStream.close(); try ( // Write the output to a file final FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } return flag; }
From source file:com.beyondb.io.ExcelControl.java
@Override public boolean addColumn(Object[] columnName, Object[][] columnData) throws FileNotFoundException, IOException, InvalidFormatException { boolean flag = true; Row rowCaption; Sheet sheet = null;/*from w ww . j a v a 2 s.c om*/ try { sheet = getSheet(); if (sheet == null) { return false; } // rowCaption = sheet.getRow(0); if (rowCaption != null) { int columnsCount = rowCaption.getLastCellNum(); for (int i = 0; i < columnName.length; i++) { Cell cell = rowCaption.createCell(columnsCount + i); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(String.valueOf(columnName[i])); } for (int i = 0; i < sheet.getLastRowNum(); i++) { //? Row tmpRow = sheet.getRow(i + 1); for (int cIndex = 0; cIndex < columnName.length; cIndex++) { Cell cell = tmpRow.getCell(columnsCount + cIndex); if (cell == null) { cell = tmpRow.createCell(columnsCount + cIndex); } //? Object obj = columnData[i][cIndex]; if (obj.getClass().getName().equals(Double.class.getName())) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); } else if (obj.getClass().getName().equals(String.class.getName())) { cell.setCellType(Cell.CELL_TYPE_STRING); } else { //? cell.setCellType(Cell.CELL_TYPE_STRING); } setCellValue(cell, obj); } } } m_InputStream.close(); try ( // Write the output to a file FileOutputStream fileOut = new FileOutputStream(m_File)) { m_Workerbook.write(fileOut); } } catch (FileNotFoundException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } catch (IOException | InvalidFormatException ex) { flag = false; Logger.getLogger(ExcelControl.class.getName()).log(Level.SEVERE, "", ex); throw ex; } return flag; }
From source file:com.chlq.fileprocessor.ToCSV.java
License:Apache License
/** * Called to convert a row of cells into a line of data that can later be * output to the CSV file./*from w w w. ja v a 2 s . c o m*/ * * @param row An instance of either the HSSFRow or XSSFRow classes that * encapsulates information about a row of cells recovered from * an Excel workbook. */ private void rowToCSV(Row row) { Cell cell = null; int lastCellNum = 0; ArrayList<String> csvLine = new ArrayList<String>(); // Check to ensure that a row was recovered from the sheet as it is // possible that one or more rows between other populated rows could be // missing - blank. If the row does contain cells then... if (row != null) { // Get the index for the right most cell on the row and then // step along the row from left to right recovering the contents // of each cell, converting that into a formatted String and // then storing the String into the csvLine ArrayList. lastCellNum = row.getLastCellNum(); for (int i = 0; i <= lastCellNum; i++) { cell = row.getCell(i); if (cell == null) { csvLine.add(""); } else { if (cell.getCellTypeEnum() != CellType.FORMULA) { csvLine.add(this.formatter.formatCellValue(cell)); } else { csvLine.add(this.formatter.formatCellValue(cell, this.evaluator)); } } } // Make a note of the index number of the right most cell. This value // will later be used to ensure that the matrix of data in the CSV file // is square. if (lastCellNum > this.maxRowWidth) { this.maxRowWidth = lastCellNum; } } this.csvData.add(csvLine); }
From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java
License:LGPL
public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception { InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream(); try {/*from w w w . ja v a 2s. c om*/ Workbook book = WorkbookFactory.create(is); Sheet sheet = book.getSheet(execBean.getSheetName()); List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); List<String> names = new ArrayList<String>(); Map<String, String> typeMap = execBean.getTypeMap(); int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum(); for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) { Row excelRow = sheet.getRow(rowIdx); short minColIx = excelRow.getFirstCellNum(); short maxColIx = excelRow.getLastCellNum(); Map<String, Object> row = new HashMap<String, Object>(); for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) { Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK); if (rowIdx == 0) { names.add(cell.getStringCellValue()); } else { String type = null; if (names.size() > colIdx) { type = typeMap.get(names.get(colIdx)); } if (StringUtils.isNotEmpty(type)) { if (type.equals("string")) { cell.setCellType(Cell.CELL_TYPE_STRING); row.put(names.get(colIdx), cell.getStringCellValue().trim()); } else if (type.equals("double")) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); row.put(names.get(colIdx), cell.getNumericCellValue()); } else if (type.equals("int")) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); row.put(names.get(colIdx), (int) cell.getNumericCellValue()); } else if (type.equals("date")) { row.put(names.get(colIdx), cell.getDateCellValue()); } else { throw new DataProcessException("??Excel?"); } } } } if (rowIdx != 0) { result.add(row); } } context.setAttribute(execBean.getResultName(), result); } finally { if (is != null) { is.close(); } } }
From source file:com.cn.util.Units.java
public static boolean isEmptyRowForExcel(Row row) { for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) { Cell cell = row.getCell(c);// w w w .j a v a 2 s . c o m if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) { return false; } } return true; }
From source file:com.codellect.util.ExcelReader.java
private void read() { try (FileInputStream inputStream = new FileInputStream(excel); Workbook workbook = new XSSFWorkbook(inputStream)) { Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> iterator = firstSheet.iterator(); Row nextRow = iterator.next(); while (iterator.hasNext()) { nextRow = iterator.next();/*from ww w . java 2 s . c o m*/ short count = nextRow.getLastCellNum(); if (count == 4 & notEmpty(nextRow.getCell(ColumnType.STATEMENT.value()))) { QbankBean qbankBean = new QbankBean(); qbankBean.setId((int) nextRow.getCell(ColumnType.ID.value()).getNumericCellValue()); qbankBean.setLevel((int) nextRow.getCell(ColumnType.LEVEL.value()).getNumericCellValue()); qbankBean.setTag(nextRow.getCell(ColumnType.TAG.value()).getStringCellValue()); qbankBean.setQuestion(nextRow.getCell(ColumnType.STATEMENT.value()).getStringCellValue()); push(qbankBean); } } } catch (IOException ex) { System.out.println("\033[0;1m" + ex.getMessage()); System.out.println( "Please place the data.xlsx file here or provide path of data file as in command line argument" + "\033[0;0m"); } }
From source file:com.common.report.util.html.ToHtml.java
License:Apache License
private void printSheetContent(Sheet sheet) { // printColumnHeads(); out.format("<tbody>%n"); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); out.format(" <tr>%n"); out.format(" <td class=%s>%d</td>%n", ROW_HEAD_CLASS, row.getRowNum() + 1); for (int i = firstColumn; i < endColumn; i++) { String content = " "; String attrs = ""; CellStyle style = null;/* w w w .java 2 s . c o m*/ if (i >= row.getFirstCellNum() && i < row.getLastCellNum()) { Cell cell = row.getCell(i); if (cell != null) { style = cell.getCellStyle(); attrs = tagStyle(cell, style); //Set the value that is rendered for the cell //also applies the format CellFormat cf = CellFormat.getInstance(style.getDataFormatString()); CellFormatResult result = cf.apply(cell); content = result.text; if (content.equals("")) content = " "; } } out.format(" <td class=%s %s>%s</td>%n", styleName(style), attrs, content); } out.format(" </tr>%n"); } out.format("</tbody>%n"); }
From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java
License:Apache License
/** * Validates the reader-config.xml with the Excel file * * @param vcConfig The validator configuration object. * @param filename Name of the Excel file. * @param dDoc Document conatins the request. * @param iResultNode The record XML structure root node, or zero, if only validation is needed. * @param sheetno Sheet index of the Excel file. * @param startrow row index from which data to be read. * @param endrow row index upto which data to be read. * @param lErrorList LinkedList contains all the errors. *//* w ww . ja va2s.c o m*/ public static void validate(ValidatorConfig vcConfig, String filename, Document dDoc, int iResultNode, int sheetno, int startrow, int endrow, List<FileException> lErrorList) { try { setRecordsread(0); setEndoffile(false); Workbook book = null; Sheet sheet = null; Row row; FileInputStream fileinp = null; //String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName; int sheetindex; int noofsheets; if (filename == null) { throw new FileException(LogMessages.PLEASE_PROVIDE_FILE_NAME); } File file = new File(filename); fileinp = new FileInputStream(filename); if (file.exists()) { if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) { try { book = (Workbook) new HSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) { try { book = new XSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.INPUT_FILE_NOT_SUPPORTED); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.FILE_NOT_FOUND); } if (sheetno != -1) { sheetindex = sheetno; noofsheets = sheetindex + 1; } else { sheetindex = 0; noofsheets = book.getNumberOfSheets(); } //check whether the sheetindex exists or not for (; sheetindex < noofsheets; sheetindex++) { if (sheetindex >= book.getNumberOfSheets()) { //no sheet throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } sheet = book.getSheetAt(sheetindex); if (sheet == null) { throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } } //validate columns //get last column index for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); if (maxcol < row.getLastCellNum()) { maxcol = row.getLastCellNum(); } } //check column index in reader-config ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList.listIterator(); while (fieldslist.hasNext()) { FieldType excelfields = (FieldType) fieldslist.next(); try { if (Short.parseShort(excelfields.sColumnIndex) < 0 || Short.parseShort(excelfields.sColumnIndex) >= maxcol) { throw new FileException(LogMessages.COLUMN_INDEX_NOT_FOUND, excelfields.sColumnIndex, (maxcol - 1)); } } catch (NumberFormatException ex) { throw new FileException(ex, LogMessages.COLUMN_INDEX_NOT_VALID, excelfields.sColumnIndex); } } if (endrow == -1) { endrow = sheet.getLastRowNum(); if (startrow == -1) { startrow = 0; } } else { endrow = startrow + endrow - 1; if (endrow > sheet.getLastRowNum()) { endrow = sheet.getLastRowNum(); } } setRecordsread(endrow - startrow + 1); } catch (IOException ex) { lErrorList.add(new FileException(ex, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename)); } catch (FileException ex) { lErrorList.add(ex); } }