List of usage examples for org.apache.poi.ss.usermodel Row getRowNum
int getRowNum();
From source file:com.sccl.attech.common.utils.excel.ExportExcel.java
License:Open Source License
/** * ?//ww w . j a v a2 s. c om * @param row * @param column ? * @param val * @param align ??1?23?? * @return ? */ public Cell addCellStyle(Row row, int column, Object val, int align, Class<?> fieldType) { Cell cell = row.createCell(column); CellStyle style = styles.get("data" + (align >= 1 && align <= 3 ? align : "")); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); style.setAlignment(HSSFCellStyle.ALIGN_LEFT); style.setWrapText(true); try { if (val == null) { cell.setCellValue(""); } else if (val instanceof String) { cell.setCellValue((String) val); } else if (val instanceof Integer) { cell.setCellValue((Integer) val); } else if (val instanceof Long) { cell.setCellValue((Long) val); } else if (val instanceof Double) { cell.setCellValue((Double) val); } else if (val instanceof Float) { cell.setCellValue((Float) val); } else if (val instanceof Date) { DataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("yyyy-MM-dd")); cell.setCellValue((Date) val); } else { if (fieldType != Class.class) { cell.setCellValue((String) fieldType.getMethod("setValue", Object.class).invoke(null, val)); } else { cell.setCellValue((String) Class .forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")) .getMethod("setValue", Object.class).invoke(null, val)); } } } catch (Exception ex) { log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + ex.toString()); cell.setCellValue(val.toString()); } if (column == 8) { CellRangeAddressList regions = new CellRangeAddressList(25, 25, 8, 8); // ? DVConstraint constraint = DVConstraint .createExplicitListConstraint(new String[] { "2", "3", "" }); // HSSFDataValidation data_validation = new HSSFDataValidation(regions, constraint); // sheet data_validation.createErrorBox("Error", "Error"); data_validation.createPromptBox("", null); sheet.addValidationData(data_validation); } cell.setCellStyle(style); return cell; }
From source file:com.sfs.ucm.controller.HelpContentAction.java
License:Open Source License
/** * Extract help content/*from w ww . j ava 2 s . c o m*/ * * @param buf * byte array representing help content file * @throws UCMException */ private void extractHelpContent(byte[] buf) throws UCMException { try { InputStream inp = new ByteArrayInputStream(buf); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Iterator<Row> iter = sheet.iterator(); Cell cell = null; Row row = null; // header rows if (iter.hasNext()) { row = iter.next(); // table title row = iter.next(); // column headers } if (iter.hasNext()) { while (iter.hasNext()) { // process records row = iter.next(); // help key cell = row.getCell(0); if (cell != null) { String key = cell.getStringCellValue(); // help content cell = row.getCell(1); String contents = cell.getStringCellValue(); // log it Object[] values = new Object[3]; values[0] = row.getRowNum() + 1; // display as one-based values[1] = key; values[2] = StringUtils.abbreviate(contents, 20); logger.info("Processing row {}; contents: {};{}", values); // construct the help content object Help theHelpItem = new Help(key, contents); // if help item already exists then just update its contents otherwise add record int ndx = this.helpItems.indexOf(theHelpItem); if (ndx == -1) { this.helpItems.add(theHelpItem); logger.info("Added Help Item {}", theHelpItem.getKeyword()); // persist the object em.persist(theHelpItem); } else { Help tmp = this.helpItems.get(ndx); tmp.setContent(contents); // persist the object em.persist(tmp); logger.info("Updated Help Item {}", tmp.getKeyword()); } } } } // done inp.close(); } catch (InvalidFormatException e) { logger.error(e.getMessage()); throw new UCMException(e); } catch (IOException e) { logger.error(e.getMessage()); throw new UCMException(e); } }
From source file:com.sonicle.webtop.contacts.io.input.MemoryContactExcelFileReader.java
License:Open Source License
@Override public ArrayList<ContactInput> listContacts(LogEntries log, File file) throws IOException, UnsupportedOperationException { ArrayList<ContactInput> results = new ArrayList<>(); HashMap<String, Integer> headersIndexes = listColumnIndexes(file); FileInputStream fis = null;//from w ww. ja va 2 s.c o m try { fis = new FileInputStream(file); Workbook wb = createWorkbook(fis); Sheet sh = getSheet(wb); LogEntries rowlog = null; for (Row row : sh) { if (row.getRowNum() < firstDataRow - 1) continue; if ((lastDataRow != -1) && (row.getRowNum() > lastDataRow - 1)) break; rowlog = new LogEntries(); try { results.add(readRow(rowlog, headersIndexes, row)); if (!rowlog.isEmpty()) { log.addMaster(new MessageLogEntry(LogEntry.Level.WARN, "ROW [{0}]", row.getRowNum() + 1)); log.addAll(rowlog); } } catch (Throwable t) { log.addMaster(new MessageLogEntry(LogEntry.Level.ERROR, "ROW [{0}]. Reason: {1}", row.getRowNum() + 1, t.getMessage())); } } } finally { IOUtils.closeQuietly(fis); } return results; }
From source file:com.stam.excellatin.ExcelLatin.java
public static void main(String[] args) { List<String> options = new ArrayList<>(); int startIndex = 0; for (String arg : args) { if (validOptions.contains(arg)) { options.add(arg);/*from w w w . ja va 2 s. c o m*/ startIndex++; } } if (args[0].equals("-h") || args.length < 3) { System.out.println("usage: ExcelLatin [options] filenameIn filenameOut columnNames..."); System.out.println("options:"); System.out.println("\t-L\tto Latin (default)"); System.out.println("\t-G\tto Greek"); System.out.println("\t-d\tdon't deaccent"); System.out.println("\t-h\thelp"); } else { boolean greekToLatin = false; boolean latinToGreek = false; Transliterator transliterator = null; if ((!options.contains("-L") && !options.contains("-G")) || options.contains("-L")) { transliterator = Transliterator.getInstance("Greek-Latin/UNGEGN"); System.out.println("\nTransliterating Greek to Latin"); greekToLatin = true; } else if (options.contains("-G")) { transliterator = Transliterator.getInstance("Latin-Greek/UNGEGN"); System.out.println("\nTransliterating Latin to Greek"); latinToGreek = true; } if (transliterator == null) { System.out.println("Not a valid option for the transliteration language"); return; } boolean deAccent = true; if (options.contains("-d")) { deAccent = false; System.out.println("Will not deaccent"); } String fileNameIn = args[startIndex]; String fileNameOut = args[startIndex + 1]; List<String> columnNames = new ArrayList<>(); System.out.println("\nColumns to transliterate\n---------------------------"); for (int i = startIndex + 2; i < args.length; i++) { columnNames.add(args[i]); System.out.println(args[i]); } System.out.println("\n"); try { File file = new File(fileNameIn); if (!file.exists()) { System.out.println("The file " + fileNameIn + " was not found"); return; } Map<String, String> mapTransformations = new HashMap<>(); Scanner sc = new Scanner(new FileReader("map.txt")); while (sc.hasNextLine()) { String greekEntry = sc.next(); String latinEntry = sc.next(); if (greekToLatin) { mapTransformations.put(greekEntry, latinEntry); } else if (latinToGreek) { mapTransformations.put(latinEntry, greekEntry); } } DataFormatter formatter = new DataFormatter(); Workbook wb = WorkbookFactory.create(file); Workbook newWb = null; if (wb instanceof HSSFWorkbook) { newWb = new HSSFWorkbook(); } else if (wb instanceof XSSFWorkbook) { newWb = new XSSFWorkbook(); } FileOutputStream fileOut = new FileOutputStream(fileNameOut); if (newWb != null) { Sheet sheetOut = newWb.createSheet(); Sheet sheet = wb.getSheetAt(0); List<Integer> idxs = new ArrayList<>(); Row row = sheet.getRow(0); for (Cell cell : row) { String cellVal = formatter.formatCellValue(cell); if (cellVal == null || cellVal.trim().equals("")) { break; } if (columnNames.contains(cell.getStringCellValue())) { idxs.add(cell.getColumnIndex()); } } for (Row rowIn : sheet) { Row rowOut = sheetOut.createRow(rowIn.getRowNum()); if (rowIn.getRowNum() == 0) { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cell.getStringCellValue()); } } else { for (Cell cell : rowIn) { cell.setCellType(Cell.CELL_TYPE_STRING); String cellVal = formatter.formatCellValue(cell); String cellNewVal = cellVal; if (idxs.contains(cell.getColumnIndex()) && cellVal != null) { if (mapTransformations.containsKey(cellVal)) { cellNewVal = mapTransformations.get(cellVal); } else { if (deAccent) { cellNewVal = deAccent(transliterator.transform(cellVal)); } else { cellNewVal = transliterator.transform(cellVal); } } } Cell cellOut = rowOut.createCell(cell.getColumnIndex()); cellOut.setCellValue(cellNewVal); } } } System.out.println("Finished!"); newWb.write(fileOut); fileOut.close(); } } catch (IOException | InvalidFormatException ex) { Logger.getLogger(ExcelLatin.class.toString()).log(Level.SEVERE, null, ex); } } }
From source file:com.streamsets.pipeline.lib.parser.excel.Offsets.java
License:Apache License
public static String offsetOf(Row row) { String sheetName = row.getSheet().getSheetName(); int rowNum = row.getRowNum(); return String.format("%s::%d", sheetName, rowNum); }
From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java
License:Apache License
public WorkbookParser(WorkbookParserSettings settings, Context context, Workbook workbook, String offsetId) throws DataParserException { this.settings = requireNonNull(settings); this.context = requireNonNull(context); this.workbook = requireNonNull(workbook); this.rowIterator = iterate(this.workbook); this.offset = requireNonNull(offsetId); this.evaluator = workbook.getCreationHelper().createFormulaEvaluator(); this.currentSheet = null; // default to blank. Used to figure out when sheet changes and get new field names from header row if (!rowIterator.hasNext()) { throw new DataParserException(Errors.EXCEL_PARSER_04); }//from w w w . jav a2 s . co m headers = new HashMap<>(); // If Headers are expected, go through and get them from each sheet if (settings.getHeader() == ExcelHeader.WITH_HEADER) { Sheet sheet; String sheetName; Row hdrRow; for (int s = 0; s < workbook.getNumberOfSheets(); s++) { sheet = workbook.getSheetAt(s); sheetName = sheet.getSheetName(); hdrRow = sheet.rowIterator().next(); List<Field> sheetHeaders = new ArrayList<>(); // if the table happens to have blank columns in front of it, loop through and artificially add those as headers // This helps in the matching of headers to data later as the indexes will line up properly. for (int columnNum = 0; columnNum < hdrRow.getFirstCellNum(); columnNum++) { sheetHeaders.add(Field.create("")); } for (int columnNum = hdrRow.getFirstCellNum(); columnNum < hdrRow.getLastCellNum(); columnNum++) { Cell cell = hdrRow.getCell(columnNum); try { sheetHeaders.add(Cells.parseCell(cell, this.evaluator)); } catch (ExcelUnsupportedCellTypeException e) { throw new DataParserException(Errors.EXCEL_PARSER_05, cell.getCellTypeEnum()); } } headers.put(sheetName, sheetHeaders); } } Offsets.parse(offsetId).ifPresent(offset -> { String startSheetName = offset.getSheetName(); int startRowNum = offset.getRowNum(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); int rowNum = row.getRowNum(); String sheetName = row.getSheet().getSheetName(); // if a sheet has blank rows at the top then the starting row number may be higher than a default offset of zero or one, thus the >= compare if (startSheetName.equals(sheetName) && rowNum >= startRowNum) { if (rowIterator.hasPrevious()) { row = rowIterator.previous(); this.currentSheet = row.getRowNum() == row.getSheet().getFirstRowNum() ? null : row.getSheet().getSheetName(); // used in comparison later to see if we've moved to new sheet } else { this.currentSheet = null; } break; } } }); }
From source file:com.streamsets.pipeline.lib.parser.excel.WorkbookParser.java
License:Apache License
private void updateRecordWithCellValues(Row row, Record record) throws DataParserException { LinkedHashMap<String, Field> output = new LinkedHashMap<>(); String sheetName = row.getSheet().getSheetName(); String columnHeader;//from w ww . ja v a2 s . c o m Set<String> unsupportedCellTypes = new HashSet<>(); for (int columnNum = row.getFirstCellNum(); columnNum < row.getLastCellNum(); columnNum++) { if (headers.isEmpty()) { columnHeader = String.valueOf(columnNum); } else { if (columnNum >= headers.get(sheetName).size()) { columnHeader = String.valueOf(columnNum); // no header for this column. mismatch } else { columnHeader = headers.get(sheetName).get(columnNum).getValueAsString(); } } Cell cell = row.getCell(columnNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK); try { output.put(columnHeader, Cells.parseCell(cell, this.evaluator)); } catch (ExcelUnsupportedCellTypeException e) { output.put(columnHeader, Cells.parseCellAsString(cell)); unsupportedCellTypes.add(e.getCellType().name()); } } // Set interesting metadata about the row Record.Header hdr = record.getHeader(); hdr.setAttribute("worksheet", row.getSheet().getSheetName()); hdr.setAttribute("row", Integer.toString(row.getRowNum())); hdr.setAttribute("firstCol", Integer.toString(row.getFirstCellNum())); hdr.setAttribute("lastCol", Integer.toString(row.getLastCellNum())); record.set(Field.createListMap(output)); if (unsupportedCellTypes.size() > 0) { throw new RecoverableDataParserException(record, Errors.EXCEL_PARSER_05, StringUtils.join(unsupportedCellTypes, ", ")); } }
From source file:com.topsem.common.io.excel.ExportExcel.java
License:Open Source License
/** * ?annotation.ExportField?/* w w w. j av a2s . c o m*/ * @return list ? */ public <E> ExportExcel setDataList(List<E> list) { for (E e : list) { int colunm = 0; Row row = this.addRow(); StringBuilder sb = new StringBuilder(); for (Object[] os : annotationList) { ExcelField ef = (ExcelField) os[0]; Object val = null; // Get entity value try { if (StringUtils.isNotBlank(ef.value())) { val = Reflections.invokeGetter(e, ef.value()); } else { if (os[1] instanceof Field) { val = Reflections.invokeGetter(e, ((Field) os[1]).getName()); } else if (os[1] instanceof Method) { val = Reflections.invokeMethod(e, ((Method) os[1]).getName(), new Class[] {}, new Object[] {}); } } // If is dict, get dict label // if (StringUtils.isNotBlank(ef.dictType())){ // val = DictUtils.getDictLabel(val==null?"":val.toString(), ef.dictType(), ""); // } } catch (Exception ex) { // Failure to ignore log.info(ex.toString()); val = ""; } this.addCell(row, colunm++, val, ef.align(), ef.fieldType()); sb.append(val + ", "); } log.debug("Write success: [" + row.getRowNum() + "] " + sb.toString()); } return this; }
From source file:com.vaadin.addon.spreadsheet.command.RowData.java
private void copyCellsData(Row row) { for (Cell cell : row) { if (cell == null) { continue; } else {/*from ww w. j a v a 2 s. co m*/ CellData cellData = new CellData(spreadsheet); cellData.read(cell); cellsData.add(cellData); } } for (int i = 0; i < maxCol; ++i) { Comment cellComment = row.getSheet().getCellComment(row.getRowNum(), i); Cell cell = row.getCell(i); if (cellComment != null && cell == null) { CommentData commenData = new CommentData(); commenData.read(cellComment); commentsWithoutCell.add(commenData); } } }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java
License:Open Source License
/** * Calculate size-related values for the sheet. Includes row and column * counts, actual row heights and column widths, and hidden row and column * indexes./*from w w w.ja v a 2 s . c o m*/ * * @param spreadsheet * @param sheet */ static void calculateSheetSizes(final Spreadsheet spreadsheet, final Sheet sheet) { // Always have at least the default amount of rows int rows = sheet.getLastRowNum() + 1; if (rows < spreadsheet.getDefaultRowCount()) { rows = spreadsheet.getDefaultRowCount(); } spreadsheet.getState().rows = rows; final float[] rowHeights = new float[rows]; int cols = 0; int tempRowIndex = -1; final ArrayList<Integer> hiddenRowIndexes = new ArrayList<Integer>(); for (Row row : sheet) { int rIndex = row.getRowNum(); // set the empty rows to have the default row width while (++tempRowIndex != rIndex) { rowHeights[tempRowIndex] = spreadsheet.getState().defRowH; } if (row.getZeroHeight()) { rowHeights[rIndex] = 0.0F; hiddenRowIndexes.add(rIndex + 1); } else { rowHeights[rIndex] = row.getHeightInPoints(); } int c = row.getLastCellNum(); if (c > cols) { cols = c; } } if (rows > sheet.getLastRowNum() + 1) { float defaultRowHeightInPoints = sheet.getDefaultRowHeightInPoints(); int lastRowNum = sheet.getLastRowNum(); // if sheet is empty, also set height for 'last row' (index // zero) if (lastRowNum == 0) { rowHeights[0] = defaultRowHeightInPoints; } // set default height for the rest for (int i = lastRowNum + 1; i < rows; i++) { rowHeights[i] = defaultRowHeightInPoints; } } spreadsheet.getState().hiddenRowIndexes = hiddenRowIndexes; spreadsheet.getState().rowH = rowHeights; // Always have at least the default amount of columns if (cols < spreadsheet.getDefaultColumnCount()) { cols = spreadsheet.getDefaultColumnCount(); } spreadsheet.getState().cols = cols; final int[] colWidths = new int[cols]; final ArrayList<Integer> hiddenColumnIndexes = new ArrayList<Integer>(); for (int i = 0; i < cols; i++) { if (sheet.isColumnHidden(i)) { colWidths[i] = 0; hiddenColumnIndexes.add(i + 1); } else { colWidths[i] = ExcelToHtmlUtils.getColumnWidthInPx(sheet.getColumnWidth(i)); } } spreadsheet.getState().hiddenColumnIndexes = hiddenColumnIndexes; spreadsheet.getState().colW = colWidths; }