InputStream inp = new FileInputStream("wb.xls"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt([sheet index]); Row row = sheet.getRow([row index]); Cell cell = row.getCell([cell index]); String cellContents = cell.getStringCellValue(); //Modify the cellContents here // Write the output to a file cell.setCellValue(cellContents); FileOutputStream fileOut = new FileOutputStream("wb.xls"); wb.write(fileOut); fileOut.close();
Workbook wb = new HSSFWorkbook(); //Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); // Or do it on one line. row.createCell(1).setCellValue(1.2); row.createCell(2).setCellValue( createHelper.createRichTextString("This is a string")); row.createCell(3).setCellValue(true); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("format sheet"); CellStyle style; DataFormat format = wb.createDataFormat(); Row row; Cell cell; short rowNum = 0; short colNum = 0; row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("0.0")); cell.setCellStyle(style); row = sheet.createRow(rowNum++); cell = row.createCell(colNum); cell.setCellValue(11111.25); style = wb.createCellStyle(); style.setDataFormat(format.getFormat("#,##0.0000")); cell.setCellStyle(style); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
/** * The actual render step: taking the POI {@link Workbook} and rendering * it to the given response. * @param workbook the POI Workbook to render * @param response current HTTP response * @throws IOException when thrown by I/O methods that we're delegating to */ protected void renderWorkbook(Workbook workbook, HttpServletResponse response) throws IOException { ServletOutputStream out = response.getOutputStream(); workbook.write(out); workbook.close(); }
public String[] getSheetNames() { int nrSheets = workbook.getNumberOfSheets(); String[] names = new String[nrSheets]; for ( int i = 0; i < nrSheets; i++ ) { names[i] = workbook.getSheetName( i ); } return names; }
/** * 自定义需要读取或写出的Sheet,如果给定的sheet不存在,创建之。<br> * 在读取中,此方法用于切换读取的sheet,在写出时,此方法用于新建或者切换sheet。 * * @param sheetName sheet名 * @return this * @since 4.0.10 */ @SuppressWarnings("unchecked") public T setSheet(String sheetName) { this.sheet = this.workbook.getSheet(sheetName); if(null == this.sheet) { this.sheet = this.workbook.createSheet(sheetName); } return (T) this; }
InputStream inp = new FileInputStream("workbook.xls"); //InputStream inp = new FileInputStream("workbook.xlsx"); Workbook wb = WorkbookFactory.create(inp); Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(2); Cell cell = row.getCell(3); if (cell == null) cell = row.createCell(3); cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue("a test"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("workbook.xls"); wb.write(fileOut); fileOut.close();
String xlsxPath = "C:\\path\\test.xlsx"; Workbook wb = WorkbookFactory.create(new FileInputStream(xlsxPath)); // get first row Row row = wb.getSheetAt(0).getRow(0); // remove second cell from first row row.removeCell(row.getCell(1)); // save changes FileOutputStream fileOut = new FileOutputStream(xlsxPath); wb.write(fileOut); fileOut.close();
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();
@Override public void write(ConferenceSolution solution, File outputSolutionFile) { try (FileOutputStream out = new FileOutputStream(outputSolutionFile)) { Workbook workbook = new ConferenceSchedulingXlsxWriter(solution).write(); workbook.write(out); } catch (IOException | RuntimeException e) { throw new IllegalStateException("Failed writing outputSolutionFile (" + outputSolutionFile + ") for solution (" + solution + ").", e); } }
FileInputStream fis = new FileInputStream("test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("test.xls") Sheet sheet = wb.getSheetAt(0); Row row = sheet.getRow(0); //assume first row is column header row System.out.println("<ptnr_label_names>"); for(int col=0; col< numXolumns; col++){ Cell cell = row.getCell(col); String columnName = cell.getStringCellValue(); System.out.println("<field_name> "+columnName +"</field_name>"); } System.out.println("</ptnr_label_names>");
Workbook wb3=WorkbookFactory.create(new FileInputStream("Book1.xls")); Sheet sh=wb3.getSheet("sheet1"); int rows=sh.getLastRowNum();
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();
FileInputStream fis = new FileInputStream("/somepath/test.xls"); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook("/somepath/test.xls") Sheet sheet = wb.getSheetAt(0); FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); Row row = sheet.getRow(cellReference.getRow()); Cell cell = row.getCell(cellReference.getCol()); System.out.println(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: System.out.println(cell.getStringCellValue()); break; case Cell.CELL_TYPE_BLANK:
Workbook wb = WorkbookFactory.create(new File("myFile.xls")); // Or .xlsx Sheet s = wb.getSheet(0); Row r1 = s.getRow(0); r1.createCell(4).setCellValue(4.5); r1.createCell(5).setCellValue("Hello"); FileOutputStream out = new FileOutputStream("newFile.xls"); // Or .xlsx wb.write(out); out.close();
Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("workbook.xls"); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 0); row.setHeight((short) (2*sheet.getDefaultRowHeight())); CellStyle cs = wb.createCellStyle(); cs.setWrapText(true); Cell cell = row.createCell(0); cell.setCellStyle(cs); cell.setCellValue( createHelper.createRichTextString("This is \n a string")); wb.write(fileOut); fileOut.close();
import org.apache.poi.ss.usermodel.WorkbookFactory; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.Sheet; //..... static final String excelLoc = "C:/Documents and Settings/Users/Desktop/testing.xlsx"; public static void ReadExcel() { InputStream inputStream = null; try { inputStream = new FileInputStream(new File(excelLoc)); Workbook wb = WorkbookFactory.create(inputStream); int numberOfSheet = wb.getNumberOfSheets(); for (int i = 0; i < numberOfSheet; i++) { Sheet sheet = wb.getSheetAt(i); //.... Customize your code here // To get sheet name, try -> sheet.getSheetName() } } catch {} }
Workbook wb = WorkbookFactory.create(new FileInputStream("file.xls")); Sheet sheet = wb.getSheetAt(0); for (int j=0; j< sheet.getLastRowNum() + 1; j++) { Row row = sheet.getRow(j); Cell cell = row.getCell(0); //get first cell // Printing Stuff }
@Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Sheet sheet = workbook.createSheet("Test Sheet"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Test Value"); } };
@Test @SuppressWarnings("resource") public void testXlsxStreamingView() throws Exception { View excelView = new AbstractXlsxStreamingView() { @Override protected void buildExcelDocument(Map<String, Object> model, Workbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Sheet sheet = workbook.createSheet("Test Sheet"); Row row = sheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue("Test Value"); } }; excelView.render(new HashMap<>(), request, response); Workbook wb = new XSSFWorkbook(new ByteArrayInputStream(response.getContentAsByteArray())); assertEquals("Test Sheet", wb.getSheetName(0)); Sheet sheet = wb.getSheet("Test Sheet"); Row row = sheet.getRow(0); Cell cell = row.getCell(0); assertEquals("Test Value", cell.getStringCellValue()); }