private void readTimeslotDays() { columnIndexToDateMap.clear(); String previousDateString = null; for (int i = 0; i < currentRow.getLastCellNum(); i++) { XSSFCell cell = currentRow.getCell(i); if (!cell.getStringCellValue().isEmpty() || previousDateString != null) { if (!cell.getStringCellValue().isEmpty()) { previousDateString = cell.getStringCellValue(); } try { columnIndexToDateMap.put(i, LocalDate.parse(previousDateString, DAY_FORMATTER)); } catch (DateTimeParseException e) { throw new IllegalStateException(currentPosition() + ": The date (" + cell.getStringCellValue() + ") does not parse as a date."); } } } }
minCell = Math.min(minCell, row.getFirstCellNum()); if(row.getLastCellNum() != -1) { maxCell = Math.max(maxCell, row.getLastCellNum()-1);
/** * Construct a XSSFCell. * * @param row the parent row. * @param cell the xml bean containing information about the cell. */ protected XSSFCell(XSSFRow row, CTCell cell) { _cell = cell; _row = row; if (cell.getR() != null) { _cellNum = new CellReference(cell.getR()).getCol(); } else { int prevNum = row.getLastCellNum(); if(prevNum != -1){ _cellNum = row.getCell(prevNum-1, MissingCellPolicy.RETURN_NULL_AND_BLANK).getColumnIndex() + 1; } } _sharedStringSource = row.getSheet().getWorkbook().getSharedStringSource(); _stylesSource = row.getSheet().getWorkbook().getStylesSource(); }
if(xssfRow != null){ List<String> rowList = new ArrayList<String>(); int colNum = xssfRow.getLastCellNum(); boolean isAdd = false; for(int i=0; i<colNum; i++){
@Override protected int getColumns(XSSFSheet sheet, XSSFRow row) { return row.getLastCellNum(); }
@Override public int getColumnCount() { int columns = 0; for (int i = 0; i < getRowCount(); i++) { columns = Math.max(columns, sheet.getRow(i).getLastCellNum()); } return columns; }
public static Option<String[]> getHeadersExcel(File file) { try { FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet ws = wb.getSheetAt(0); // read the first sheet int totalRows = ws.getLastRowNum(); if (totalRows == 0) { return Option.failure("No lines found in file " + file.getName()); } XSSFRow row = ws.getRow(0); String[] headers = new String[row.getLastCellNum()]; for (int index = 0; index < row.getLastCellNum(); index++) { XSSFCell cell = row.getCell(index); assert cell != null : "Got null cell at index " + index; headers[index] = cell.toString(); } return Option.success(headers); } catch (IOException e) { e.printStackTrace(); return Option.failure("Encountered IOException."); } }
/** * Gets the column count. * * @author SulakkhanaW * @param sheetName the sheet name * @return the column count */ public int getColumnCount(String sheetName) { if (!isSheetExist(sheetName)) return -1; sheet = workbook.getSheet(sheetName); row = sheet.getRow(0); if (row == null) return -1; return row.getLastCellNum(); }
public static void test(XSSFSheet sheet){ XSSFRow Row = null; XSSFCell Cell = null; int LastRowNum = sheet.getLastRowNum(); for(int RowNum= 0;RowNum<LastRowNum-1;RowNum++){ Row=sheet.getRow(RowNum); for(int CellNum = 0; CellNum<Row.getLastCellNum();CellNum++){ Cell = Row.getCell(CellNum); String TextInCell=Cell.toString(); if(TextInCell.contains("JUNI")){ sheet.shiftRows(RowNum+1, LastRowNum, -1); LastRowNum--; RowNum--; break; } } } }
for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++)
// Open workbook and sheet final XSSFWorkbook workbook = new XSSFWorkbook(new File("filename.xlsx")); final XSSFSheet sheet = workbook.getSheetAt(0); // Iterate rows final int firstRowNumber = sheet.getFirstRowNum(); final int lastRowNumber = sheet.getLastRowNum(); for (int rowNumber = firstRowNumber; rowNumber < lastRowNumber; rowNumber++ ) { final XSSFRow row = sheet.getRow(rowNumber); if (row == null) continue; // Iterate columns final int firstColumnNumber = row.getFirstCellNum(); final int lastColumnNumber = row.getLastCellNum(); for (int columnNumber = firstColumnNumber; columnNumber < lastColumnNumber; columnNumber++ ) { final XSSFCell cell = row.getCell(firstColumnNumber); if (cell == null) continue; // Make some changes cell.setCellValue("new Value"); } } // Save changes workbook.write(new FileOutputStream("newFilename.xlsx"));
row = sheet.createRow(0); if (row.getLastCellNum() == -1) cell = row.createCell(0); else cell = row.createCell(row.getLastCellNum());
private static void addValueCell(XSSFSheet sheet, Object value) { short lastCell = sheet .getRow(sheet.getLastRowNum()) .getLastCellNum(); lastCell = lastCell < 0 ? 0 : lastCell; sheet .getRow(sheet.getLastRowNum()) .createCell(lastCell) .setCellValue("" + value); }
private static void addValueCell(XSSFSheet sheet, Object value) { short lastCell = sheet .getRow(sheet.getLastRowNum()) .getLastCellNum(); lastCell = lastCell < 0 ? 0 : lastCell; sheet .getRow(sheet.getLastRowNum()) .createCell(lastCell) .setCellValue("" + value); }
public static String getFromExcel(XSSFWorkbook wb, String... format) { StringBuilder builder = getStart(); int line = Configuration.CONFIG.shouldSkipFirstLine ? 1 : 0; XSSFSheet ws = wb.getSheetAt(0); // read the first sheet int totalColumns = ws.getRow(0).getLastCellNum(); int totalRows = ws.getLastRowNum(); Map<String, String> rowMap = map(); for (; line <= totalRows; line++) { // we want <= because the index returned from ws.getLastRowNum() is valid XSSFRow row = ws.getRow(line); for (int column = 0; column < totalColumns; column++){ XSSFCell cell = row.getCell(column); if (cell == null) { // cells are null if there's no data in them; this is fine. continue; } String value = cell.toString(); if (format.length > column) { rowMap.put(format[column], value); } else { System.err.println("format wasn't long enough for column. Column length = " + totalColumns + ", format was " + format.length); } } addRecord(builder, line, rowMap); rowMap.clear(); } return writeEnd(builder); }
private void readTimeslotDays() { columnIndexToDateMap.clear(); String previousDateString = null; for (int i = 0; i < currentRow.getLastCellNum(); i++) { XSSFCell cell = currentRow.getCell(i); if (!cell.getStringCellValue().isEmpty() || previousDateString != null) { if (!cell.getStringCellValue().isEmpty()) { previousDateString = cell.getStringCellValue(); } try { columnIndexToDateMap.put(i, LocalDate.parse(previousDateString, DAY_FORMATTER)); } catch (DateTimeParseException e) { throw new IllegalStateException(currentPosition() + ": The date (" + cell.getStringCellValue() + ") does not parse as a date."); } } } }
for (int i = sourceRow.getFirstCellNum(); i <= sourceRow.getLastCellNum(); i++) { XSSFCell sourceCell = sourceRow.getCell(i); SXSSFCell targetCell = (SXSSFCell) targetRow.getCell(i);
public Node parseRows(Node top, XSSFRow row) { int len = row.getCTRow().getCArray().length; Node rowNode = len == 0 ? createEmptyRow(sheet, getCurrentRow()) : createRowNode(row.getSheet(), row); for (int i = 0; i < row.getLastCellNum(); i++) { XSSFCell cell = row.getCell(i); Node cellNode; if (cell == null) { cellNode = createEmtyCell(sheet, row.getCell(i, Row.CREATE_NULL_AS_BLANK)); } else { cellNode = createCellNode(row.getSheet(), cell, cell.getColumnIndex(), CellExpressionParser.parse(cell), rowNode); } rowNode.getChildren().add(cellNode); } return rowNode; }
/** * Construct a XSSFCell. * * @param row the parent row. * @param cell the xml bean containing information about the cell. */ protected XSSFCell(XSSFRow row, CTCell cell) { _cell = cell; _row = row; if (cell.getR() != null) { _cellNum = new CellReference(cell.getR()).getCol(); } else { int prevNum = row.getLastCellNum(); if(prevNum != -1){ _cellNum = row.getCell(prevNum-1, MissingCellPolicy.RETURN_NULL_AND_BLANK).getColumnIndex() + 1; } } _sharedStringSource = row.getSheet().getWorkbook().getSharedStringSource(); _stylesSource = row.getSheet().getWorkbook().getStylesSource(); }
public void parse(Table table) { final String tableName = table.getCTTable().getName(); int end = table.getEndCellReference().getRow(); TableNode tableNode = sheetParser.createTableNode(sheet,top,table); top.getChildren().add(tableNode); int headerRowCount = (int) table.getCTTable().getHeaderRowCount(); if(headerRowCount > 0){ Node header = parseHeader(tableNode); tableNode.addHeader(header); } ForEachNode forEach = new ForEachNode(sheet); tableNode.addLoop(forEach); forEach.setRows(new TableExpression(tableName)); XSSFRow template = sheet.getRow(row.getRowNum() + headerRowCount); Node templateNode = sheetParser.createRowNode(sheet, template ); for( int i = 0; i < template.getLastCellNum(); i++){ buildTableCell(table,templateNode, template.getCell(i, Row.CREATE_NULL_AS_BLANK)); } forEach.getChildren().add(templateNode ); addTotals(tableNode,templateNode); sheetParser.setCurrentRow(end + 1); }