@Override public RockTourSolution read(File inputSolutionFile) { try (InputStream in = new BufferedInputStream(new FileInputStream(inputSolutionFile))) { XSSFWorkbook workbook = new XSSFWorkbook(in); return new RockTourXlsxReader(workbook).read(); } catch (IOException | RuntimeException e) { throw new IllegalStateException("Failed reading inputSolutionFile (" + inputSolutionFile + ").", e); } }
/** * @return returns the workbook object of embedded excel file * @throws IOException * @throws InvalidFormatException * @since POI 4.0.0 */ public XSSFWorkbook getWorkbook() throws IOException, InvalidFormatException { if (workbook == null) { try { PackagePart worksheetPart = getWorksheetPart(); if (worksheetPart == null) { workbook = new XSSFWorkbook(); workbook.createSheet(); } else { workbook = new XSSFWorkbook(worksheetPart.getInputStream()); } } catch (NotOfficeXmlFileException e) { workbook = new XSSFWorkbook(); workbook.createSheet(); } } return workbook; }
/** * @return the list of Single Xml Cells that provide a map rule to this mapping. */ public List<XSSFSingleXmlCell> getRelatedSingleXMLCell() { List<XSSFSingleXmlCell> relatedSimpleXmlCells = new ArrayList<>(); int sheetNumber = mapInfo.getWorkbook().getNumberOfSheets(); for (int i = 0; i < sheetNumber; i++) { XSSFSheet sheet = mapInfo.getWorkbook().getSheetAt(i); for (POIXMLDocumentPart p : sheet.getRelations()) { if (p instanceof SingleXmlCells) { SingleXmlCells singleXMLCells = (SingleXmlCells) p; for (XSSFSingleXmlCell cell : singleXMLCells.getAllSimpleXmlCell()) { if (cell.getMapId() == ctMap.getID()) { relatedSimpleXmlCells.add(cell); } } } } } return relatedSimpleXmlCells; }
/** * Create an XSSFSheet for this workbook, adds it to the sheets and returns * the high level representation. Use this to create new sheets. * * @return XSSFSheet representing the new sheet. */ @Override public XSSFSheet createSheet() { String sheetname = "Sheet" + (sheets.size()); int idx = 0; while(getSheet(sheetname) != null) { sheetname = "Sheet" + idx; idx++; } return createSheet(sheetname); }
public void writeSetup() { workbook = new XSSFWorkbook(); creationHelper = workbook.getCreationHelper(); createStyles(); }
@Override public EvaluationSheet getSheet(int sheetIndex) { // Performance optimization: build sheet cache the first time this is called // to avoid re-creating the XSSFEvaluationSheet each time a new cell is evaluated // EvaluationWorkbooks make not guarantee to synchronize changes made to // the underlying workbook after the EvaluationWorkbook is created. if (_sheetCache == null) { final int numberOfSheets = _uBook.getNumberOfSheets(); _sheetCache = new XSSFEvaluationSheet[numberOfSheets]; for (int i=0; i < numberOfSheets; i++) { _sheetCache[i] = new XSSFEvaluationSheet(_uBook.getSheetAt(i)); } } if (sheetIndex < 0 || sheetIndex >= _sheetCache.length) { // do this to reuse the out-of-bounds logic and message from XSSFWorkbook _uBook.getSheetAt(sheetIndex); } return _sheetCache[sheetIndex]; }
String file = "c:\\poitest.xlsx"; FileOutputStream outputStream = new FileOutputStream(file); Workbook wb = new XSSFWorkbook(); CellStyle unlockedCellStyle = wb.createCellStyle(); unlockedCellStyle.setLocked(false); Sheet sheet = wb.createSheet(); sheet.protectSheet("password"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("TEST"); cell.setCellStyle(unlockedCellStyle); wb.write(outputStream); outputStream.close();
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();
Workbook wb = new XSSFWorkbook(); Sheet sheet1 = wb.createSheet("Ripon"); FileOutputStream fileOut = new FileOutputStream("D:\\RiponAlWasim.xlsx"); wb.write(fileOut); fileOut.close(); wb.close();
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");
//out is the file after jasper report export XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(out)); int ns = workbook.getNumberOfSheets(); for (int i = 0; i < ns; i++) { XSSFSheet sheet = workbook.getSheetAt(i); sheet.setRightToLeft(true); } FileOutputStream outStream = new FileOutputStream(out); workbook.write(outStream); outStream.close();
void method(int i) throws InvalidFormatException, IOException { ... // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xlsx"); workbook.write(fileOut); fileOut.close(); // Reload the workbook, workaround for bug 49940 // https://issues.apache.org/bugzilla/show_bug.cgi?id=49940 workbook = new XSSFWorkbook(new FileInputStream("workbook.xlsx")); }
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 SO{ public static void main(String[] args) { try { FileInputStream is = new FileInputStream(new File("D:\\Users\\user2777005\\Desktop\\bob.xlsx")); XSSFWorkbook wb = new XSSFWorkbook(is); String header = "123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789"; Sheet sheet = wb.getSheet("Sheet1"); sheet.setColumnWidth(0, 18000); Row row = sheet.createRow(0); Cell cell = row.createCell(0); if(header.length() > 50){ //Length of String for my test sheet.setColumnWidth(0, 18000); //Set column width, you'll probably want to tweak the second int CellStyle style = wb.createCellStyle(); //Create new style style.setWrapText(true); //Set wordwrap cell.setCellStyle(style); //Apply style to cell cell.setCellValue(header); //Write header } wb.write(new FileOutputStream(new File("D:\\Users\\user2777005\\Desktop\\bob.xlsx"))); } catch (IOException e) { e.printStackTrace(); } } }
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 } }
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 workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Employee Data"); Row row = sheet.createRow(rownum++); Cell cell = row.createCell(cellnum++); if (obj instanceof String) cell.setCellValue((String) obj); cell.setCellValue((Integer) obj); 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.");
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(); switch (cell.getCellType()) System.out.print(cell.getNumericCellValue() + "\t"); break; case Cell.CELL_TYPE_STRING: System.out.print(cell.getStringCellValue() + "\t"); break; file.close(); } catch (Exception e) { e.printStackTrace();
Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(2); Cell cell = row.createCell(2); cell.setCellValue("Use \n with word wrap on to create a new line"); //to enable newlines you need set a cell styles with wrap=true CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); cell.setCellStyle(cs); //increase row height to accomodate two lines of text row.setHeightInPoints((2*sheet.getDefaultRowHeightInPoints())); //adjust column width to fit the content sheet.autoSizeColumn((short)2); FileOutputStream fileOut = new FileOutputStream("ooxml-newlines.xlsx"); wb.write(fileOut); fileOut.close();