/** * Also creates cells if they don't exist */ private CellRange<XSSFCell> getCellRange(CellRangeAddress range) { int firstRow = range.getFirstRow(); int firstColumn = range.getFirstColumn(); int lastRow = range.getLastRow(); int lastColumn = range.getLastColumn(); int height = lastRow - firstRow + 1; int width = lastColumn - firstColumn + 1; List<XSSFCell> temp = new ArrayList<>(height * width); for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { XSSFRow row = getRow(rowIn); if (row == null) { row = createRow(rowIn); } XSSFCell cell = row.getCell(colIn); if (cell == null) { cell = row.createCell(colIn); } temp.add(cell); } } return SSCellRange.create(firstRow, firstColumn, height, width, temp, XSSFCell.class); }
/** * 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(); }
/** * Alias for {@link #cellIterator()} to allow foreach loops: * <blockquote><pre> * for(Cell cell : row){ * ... * } * </pre></blockquote> * * @return an iterator over cells in this row. */ @Override public Iterator<Cell> iterator() { return cellIterator(); }
protected XSSFCell nextCell() { currentColumnNumber++; XSSFCell cell = currentRow.getCell(currentColumnNumber); // TODO HACK to workaround the fact that LibreOffice and Excel automatically remove empty trailing cells if (cell == null) { // Return dummy cell return currentRow.createCell(currentColumnNumber); } return cell; }
/** * update cell references when shifting rows * * @param n the number of rows to move */ protected void shift(int n) { int rownum = getRowNum() + n; String msg = "Row[rownum=" + getRowNum() + "] contains cell(s) included in a multi-cell array formula. " + "You cannot change part of an array."; for(Cell c : this){ ((XSSFCell)c).updateCellReferencesForShifting(msg); } setRowNum(rownum); }
public static void main(String[] args) throws InvalidFormatException, IOException{ FileInputStream fis=new FileInputStream("D://Data.xlsx"); XSSFWorkbook wb= new XSSFWorkbook(fis); //XSSFSheet sh= wb.getSheetAt(0); Or XSSFSheet sh = wb.createSheet("Test"); XSSFRow row=sh.createRow(0); XSSFCell cell= row.createCell(0); //cell.setCellType(cell.CELL_TYPE_STRING); cell.setCellValue("Ish Mishra"); FileOutputStream fos=new FileOutputStream("D:\\Data.xlsx"); wb.write(fos); fos.close(); System.out.println("Excel File Written successfully");
FileInputStream inputStream = new FileInputStream(new File(excelFilePath)); XSSFWorkbook workbook = new XSSFWorkbook(inputStream); XSSFSheet firstSheet = workbook.getSheetAt(0); int rowCnt = firstSheet.getLastRowNum(); try{ for(int i = 1; i <= rowCnt; i++){ if(i == 3) break; XSSFRow r = firstSheet.getRow(i); int res = i; Cell cell = null; if(cell == null){ cell = r.createCell(5); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("f" + res); e.printStackTrace(); }finally{ FileOutputStream out = new FileOutputStream(new File("C:\\Users\\gotpist1\\Desktop\\SRBNOI.xlsx")); workbook.write(out); out.close(); inputStream.close(); System.out.println("done");
fis = new FileInputStream(path); workbook = new XSSFWorkbook(); int index = workbook.getSheetIndex(sheetName); if (index == -1) return false; XSSFCellStyle style = workbook.createCellStyle(); XSSFColor myColor = new XSSFColor(Color.GRAY); style.setFillForegroundColor(myColor); sheet = workbook.getSheetAt(index); row = sheet.getRow(0); if (row == null) row = sheet.createRow(0); if (row.getLastCellNum() == -1) cell = row.createCell(0); else cell = row.createCell(row.getLastCellNum()); cell.setCellValue(colName); cell.setCellStyle(style); fileOut = new FileOutputStream(path); workbook.write(fileOut); fileOut.close();
FileInputStream fileInputStream = new FileInputStream(new File(filename)); workbook = new XSSFWorkbook(fileInputStream); List<String> sheetNameList = new ArrayList<String>(); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { sheetNameList.add(workbook.getSheetName(i)); row = sheet.getRow(0); row = sheet.createRow(0); for (int y = 0; y < columnCount; y++){ cell = row.createCell((short) y); cell.setCellValue(array.get(0).get(y)); row = sheet.createRow(sheet.getLastRowNum() + 1); for (int x = 0; x < columnCount; x++) { cell = row.createCell((short) x); cell.setCellValue(array.get(1).get(x)); FileOutputStream fileOutputStream = new FileOutputStream(filename); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (FileNotFoundException e) { e.printStackTrace();
if (!isSheetExist(sheetName)) return false; fis = new FileInputStream(path); workbook = new XSSFWorkbook(); sheet = workbook.getSheet(sheetName); XSSFCellStyle style = workbook.createCellStyle(); XSSFColor myColor = new XSSFColor(Color.GRAY); style.setFillForegroundColor(myColor); row = sheet.getRow(i); if (row != null) { cell = row.getCell(colNum); if (cell != null) { cell.setCellStyle(style); row.removeCell(cell); fileOut = new FileOutputStream(path); workbook.write(fileOut); fileOut.close(); } catch (Exception e) { e.printStackTrace();
fis = new FileInputStream(new File(fileName)); XSSFWorkbook workbook = new XSSFWorkbook (fis); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row1 = sheet.createRow(0); XSSFCell r1c1 = row1.createCell(0); r1c1.setCellValue("Demo"); fis.close(); FileOutputStream fos = new FileOutputStream(new File(outputFile)); workbook.write(fos); fos.close();
public class ExcelReader { public String a1Val; public String b1Val; public static void main(String[] args) { try { FileInputStream fileInputStream = new FileInputStream("C:/Users/TEST.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream); XSSFSheet worksheet = workbook.getSheetAt(0); XSSFRow row1 = worksheet.getRow(0); XSSFCell cellA1 = row1.getCell((int) 0); a1Val = cellA1.getStringCellValue(); XSSFCell cellB1 = row1.getCell((int) 1); b1Val = cellB1.getStringCellValue();
FileInputStream myInput = new FileInputStream(inputFile); XSSFWorkbook wb = new XSSFWorkbook(myInput); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row = sheet.getRow(0); XSSFCell cell = row.getCell(1); cell.setCellValue(123);
XSSFWorkbook wookbook = new XSSFWorkbook(new FileInputStream(filePath)); XSSFSheet sheet = wookbook.getSheet("Sheet1"); int rows = sheet.getPhysicalNumberOfRows(); XSSFRow title = sheet.getRow(0); int index = title.getFirstCellNum(); int rowcount = title.getLastCellNum(); for (int i = 1; i < rows; i++){ XSSFRow row = sheet.getRow(i); if(isBlankRow(row, index, rowcount)) continue; if (row != null){ Map<String,String> map = new TreeMap<String,String>(); int cells = title.getPhysicalNumberOfCells(); XSSFCell cell = row.getCell(j); if (cell != null){ switch (cell.getCellType()){ case HSSFCell.CELL_TYPE_FORMULA: break; case HSSFCell.CELL_TYPE_NUMERIC: cell.setCellType(HSSFCell.CELL_TYPE_STRING); value += cell.getStringCellValue().trim(); break; case HSSFCell.CELL_TYPE_STRING: map.put(title.getCell(j).getStringCellValue().trim(), value);
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet("PersonList"); XSSFRow row; for (Object obj : objectArr)// for (Person person : personService.listPersons()) { Cell cell = row.createCell(cellid++); cell.setCellValue((String)obj); } for(int columnIndex = 0; columnIndex < 24; columnIndex++) { spreadsheet.autoSizeColumn(columnIndex); } if(List.size()!=0) { FileOutputStream out; out = new FileOutputStream(new File(file_path)); workbook.write(out); out.close(); }
String srcPath = "C:\\projects\\source.xlsx"; String destPath = "C:\\projects\\destSheet.xlsx"; XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(new File(srcPath)); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = sheet.getRow(1); if(row == null) { row = sheet.createRow(1); } XSSFCell cell = row.getCell(0); if(cell == null) { cell = row.createCell(0); } cell.setCellValue("Testing123"); FileOutputStream fileOut = new FileOutputStream(new File(destPath)); workbook.write(fileOut); fileOut.flush(); fileOut.close();
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."); } }
void readXLSXFile(String fileName) throws IOException { FileInputStream fs= new FileInputStream(fileName); XSSFWorkbook wb = new XSSFWorkbook(fs); XSSFSheet ws = wb.getSheetAt(0);//you can change the value to index value of your required sheet Iterator<Row> rows = ws.rowIterator(); XSSFRow row; while(rows.hasNext()) { row=(XSSFRow) rows.next(); XSSFCell cell=row.getCell(7);//8th cell of the row String CName= cell.getStringCellValue(); //getting the cell value as string countryNames(CName);// Your method for passing the country name to the method for your requirement } wb.close(); }
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx"); XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead); XSSFWorkbook test = new XSSFWorkbook(); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Iterator rows = sheet.rowIterator(); Iterator cells = row.cellIterator(); while (cells.hasNext()) if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) System.out.print(cell.getStringCellValue()+" "); else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) System.out.print(cell.getNumericCellValue()+" ");