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); } }
protected XSSFCell nextCellVertically(XSSFCellStyle cellStyle) { currentRowNumber++; currentRow = currentSheet.getRow(currentRowNumber); XSSFCell cell = currentRow.createCell(currentColumnNumber); cell.setCellStyle(cellStyle); return cell; }
private void mapCellOnNode(XSSFCell cell, Node node) { switch (cell.getCellType()) { case STRING: value = cell.getStringCellValue(); break; case BOOLEAN: value += cell.getBooleanCellValue(); break; case ERROR: value = cell.getErrorCellString(); break; case FORMULA: if (cell.getCachedFormulaResultType() == CellType.STRING) { value = cell.getStringCellValue(); } else { if (DateUtil.isCellDateFormatted(cell)) { value = getFormattedDate(cell); } else { value += cell.getNumericCellValue(); value = getFormattedDate(cell); } else { value += cell.getRawValue();
protected XSSFCell nextNumericCellOrBlank() { XSSFCell cell = nextCell(); if (cell.getCellTypeEnum() == CellType.BLANK || (cell.getCellTypeEnum() == CellType.STRING && cell.getStringCellValue().isEmpty())) { return null; } if (cell.getCellTypeEnum() == CellType.STRING) { throw new IllegalStateException(currentPosition() + ": The cell with value (" + cell.getStringCellValue() + ") has a string type but should be numeric."); } return cell; }
/** * Set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as * a date. * * @param value the date value to set this cell to. For formulas we'll set the * precalculated value, for numerics we'll set its value. For other types we * will change the cell to a numeric cell and set its value. */ @Override public void setCellValue(Date value) { if(value == null) { setCellType(CellType.BLANK); return; } boolean date1904 = getSheet().getWorkbook().isDate1904(); setCellValue(DateUtil.getExcelDate(value, date1904)); }
XSSFDataFormat df = workBook.createDataFormat(); cs.setDataFormat(df.getFormat("d-mmm-yy")); // Get / Create our cell XSSFRow row = sheet.createRow(2); XSSFCell cell = row.createCell(3); // Set it to be a date Calendar c = Calendar.getInstance(); c.set(2012,3-1,18); // Don't forget months are 0 based on Calendar cell.setCellValue( c.getTime() ); // Style it as a date cell.setCellStyle(cs);
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");
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 ExcelFile = new FileInputStream(Path); ExcelWBook = new XSSFWorkbook(ExcelFile); ExcelWSheet = ExcelWBook.getSheet(SheetName); } catch (Exception e) { throw (e); Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum); String CellData = Cell.getStringCellValue(); return CellData; } catch (Exception e) { Row = ExcelWSheet.createRow(RowNum - 1); Cell = Row.createCell(ColNum - 1); Cell.setCellValue(Result); FileOutputStream fileOut = new FileOutputStream(Path); ExcelWBook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (Exception e) {
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);
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();
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(); }
// initialize objects XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(sheetName); XSSFRow row = spreadsheet.createRow(0); XSSFCell cell; // font/style XSSFFont font = workbook.createFont(); font.setFontName("Arial"); XSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); // create/set cell & style cell = row.createCell(0); cell.setCellValue("New Cell"); cell.setCellStyle(style); // auto size spreadsheet.autoSizeColumn(0); // create file File aFile = new File("Your Filename"); FileOutputStream out = new FileOutputStream(aFile); workbook.write(out);
// 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"));
public static void main(String[] args) throws Exception { try (XSSFWorkbook wb = new XSSFWorkbook()) { XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow(2); XSSFCell cell = row.createCell(1); XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox"); XSSFFont font1 = wb.createFont(); font1.setBold(true); font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0), wb.getStylesSource().getIndexedColors())); rt.append(" Jumped over the lazy dog", font3); cell.setCellValue(rt); try (OutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx")) { wb.write(fileOut);
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFRow row = sheet.createRow(0); XSSFCell cell = row.createCell( 0); cell.setCellValue("custom XSSF colors"); XSSFCellStyle style1 = wb.createCellStyle(); style1.setFillForegroundColor(new XSSFColor(new java.awt.Color(128, 0, 128))); style1.setFillPattern(CellStyle.SOLID_FOREGROUND);
XSSFComment cellComment = tplSheetCell.getCellComment(); if (cellComment != null) sheetCell.setCellComment(cellComment); XSSFCellStyle cellStyle = tplSheetCell.getCellStyle(); if (cellStyle != null) sheetCell.setCellStyle(cellStyle); int cellType = tplSheetCell.getCellType(); sheetCell.setCellType(cellType); switch (cellType) { case Cell.CELL_TYPE_STRING: sheetCell.setCellValue(tplSheetCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: sheetCell.setCellValue(tplSheetCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: sheetCell.setCellValue(tplSheetCell.getNumericCellValue()); break; case Cell.CELL_TYPE_ERROR: sheetCell.setCellValue(tplSheetCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: String cellFormula = tplSheetCell.getCellFormula(); if (cellFormula != null) sheetCell.setCellFormula(cellFormula); break; case Cell.CELL_TYPE_BLANK: XSSFHyperlink hyperlink = tplSheetCell.getHyperlink();
private static void appendNotesRow(XSSFWorkbook wb, XSSFSheet sheet, int rowNum, String app, double effort, String notes) { XSSFRow row = sheet.createRow(rowNum); XSSFCell t1 = row.createCell(0); t1.setCellValue(app); XSSFCell t2 = row.createCell(1); XSSFCellStyle t2s = wb.createCellStyle(); t2s.setAlignment(HorizontalAlignment.RIGHT); t2.setCellStyle(t2s); t2.setCellValue(effort); row.createCell(2); XSSFCell t4 = row.createCell(3); t4.setCellValue(notes); }