/** * @param rowIndex the zero based row index to collapse */ private void collapseRow(int rowIndex) { XSSFRow row = getRow(rowIndex); if (row != null) { int startRow = findStartOfRowOutlineGroup(rowIndex); // Hide all the columns until the end of the group int lastRow = writeHidden(row, startRow, true); if (getRow(lastRow) != null) { getRow(lastRow).getCTRow().setCollapsed(true); } else { XSSFRow newRow = createRow(lastRow); newRow.getCTRow().setCollapsed(true); } } }
/** * @param row the zero based row index to find from */ public int findEndOfRowOutlineGroup(int row) { short level = getRow(row).getCTRow().getOutlineLevel(); int currentRow; final int lastRowNum = getLastRowNum(); for (currentRow = row; currentRow < lastRowNum; currentRow++) { if (getRow(currentRow) == null || getRow(currentRow).getCTRow().getOutlineLevel() < level) { break; } } return currentRow; }
private void writeDaySheet(LocalDate day, List<Timeslot> timeslotList, List<Talk> talkList) { nextSheet(DAY_FORMATTER.format(day), 1, 1, true); nextRow(); nextHeaderCell(DAY_FORMATTER.format(day)); writeTimeslotHoursVertically(timeslotList); List<Room> dayRoomList = talkList.stream().map(Talk::getRoom).distinct().collect(toList()); dayRoomList.sort(Comparator.comparing(Room::getName)); for (Room room : dayRoomList) { currentColumnNumber++; currentRowNumber = -1; nextCellVertically().setCellValue(room.getName()); List<Talk> roomTalkList = talkList.stream() .filter(talk -> talk.getRoom() == room) .collect(toList()); writeRoomTalks(timeslotList, room, roomTalkList); } currentSheet.autoSizeColumn(0); for (int i = 1; i < currentSheet.getRow(0).getPhysicalNumberOfCells(); i++) { currentSheet.setColumnWidth(i, 15 * 256); } }
/** * this method return row on given index if row is null then create new row * * @param sheet * current sheet object * @param index * index of current row * @return this method return sheet row on given index * @since POI 4.0.0 */ private XSSFRow getRow(XSSFSheet sheet, int index) { if (sheet.getRow(index) != null) { return sheet.getRow(index); } else { return sheet.createRow(index); } }
/** * @param sheet * @param row * @return height in twips (1/20th of point) for row or default */ private static float getRowHeight(XSSFSheet sheet, int row) { XSSFRow r = sheet.getRow(row); return r == null ? sheet.getDefaultRowHeightInPoints() : r.getHeightInPoints(); }
/** * @param row the zero based row index to find from */ private boolean isRowGroupCollapsed(int row) { int collapseRow = findEndOfRowOutlineGroup(row) + 1; if (getRow(collapseRow) == null) { return false; } return getRow(collapseRow).getCTRow().getCollapsed(); }
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 file = new FileInputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx")); XSSFWorkbook workbook = new XSSFWorkbook(file); XSSFSheet sheet = workbook.getSheetAt(0); Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) Iterator<Cell> cellIterator = row.cellIterator(); file.close(); } catch (Exception e) { e.printStackTrace();
public void setData(String adr,String sht,int rn,int cn,String val) throws Exception{ FileInputStream fsIP= new FileInputStream(new File(adr)); //Read the spreadsheet that needs to be updated XSSFWorkbook wb = new XSSFWorkbook(fsIP); //Access the workbook XSSFSheet worksheet = wb.getSheet(sht); //Access the worksheet, so that we can update / modify it. Cell cell = null; // declare a Cell object cell = worksheet.getRow(rn).getCell(cn); // Access the second cell in second row to update the value cell.setCellValue(val); // Get current cell value value and overwrite the value fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File(adr)); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream }
File excelFile = new File("C:\\path\\of\\excel\\file\\excel.xlsx"); String cellNo = 3; //column number of "UserName" column String rowNo = 1; // row number String userName = "Test1"; // username fetched from textbox FileInputStream fis = new FileInputStream(excelFile); XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(fis); XSSFSheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(rowNo); row.createCell(cellNo).setCellValue(userName); fis.close(); FileOutputStream fos = new FileOutputStream(excelFile); workbook.write(fos); fos.close();
StringBuffer data = new StringBuffer(); try { FileOutputStream fos = new FileOutputStream(outputFile); XSSFWorkbook wBook = new XSSFWorkbook(new FileInputStream(inputFile)); // Get SECOND sheet from the workbook XSSFSheet sheet = wBook.getSheetAt(1); Row row; Cell cell; // Iterate through each rows from SECOND sheet Iterator<Row> rowIterator = sheet.iterator(); while (rowIterator.hasNext()) { row = rowIterator.next(); // For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { // } } fos.write(data.toString().getBytes()); fos.close(); } catch (Exception ioe) { ioe.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();
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Employee Data"); Row row = sheet.createRow(rownum++); Cell cell = row.createCell(cellnum++); if (obj instanceof String) FileOutputStream out = new FileOutputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx")); workbook.write(out); out.close(); System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
public static void main(String[] args) { Path myFile = Paths.get(System.getProperty("user.home"), "Desktop", "tester.xlsx"); try { XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(myFile.toFile())); XSSFSheet sheet = wb.getSheetAt(0); makeRowBold(wb, sheet.getRow(0)); wb.write(new FileOutputStream(myFile.toFile())); } catch (IOException e) { e.printStackTrace(); } } public static void makeRowBold(Workbook wb, Row row){ CellStyle style = wb.createCellStyle();//Create style Font font = wb.createFont();//Create font font.setBoldweight(Font.BOLDWEIGHT_BOLD);//Make font bold style.setFont(font);//set it to bold for(int i = 0; i < row.getLastCellNum(); i++){//For each cell in the row row.getCell(i).setCellStyle(style);//Set the sty;e } }
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); sheet.getRow(37).getCell(13). setCellValue("USD");
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();
File file = new File("D:/temp/test.xlsx"); FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); XSSFSheet sh = wb.getSheet("Hola"); System.out.println(sh.getLastRowNum()); System.out.println("Name: "+sh.getSheetName()); Row row = sh.getRow(1); System.out.println(row.getRowNum()); System.out.println("Val: "+sh.getRow(1).getCell(1).getStringCellValue());
FileInputStream file = new FileInputStream(new File("C:\\test.xlsx")); //Get the workbook instance for XLS file XSSFWorkbook workbook = new XSSFWorkbook (file); //Get first sheet from the workbook XSSFSheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); String data = row.getCell(columnindex).getStringCellValue();