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();
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();
public static void cloneCellContent(Cell srcCell, Cell destCell, Map<Integer, CellStyle> styleMap) { if(styleMap != null) { if(srcCell.getSheet().getWorkbook() == destCell.getSheet().getWorkbook()){ destCell.setCellStyle(srcCell.getCellStyle()); } else { int stHashCode = srcCell.getCellStyle().hashCode(); CellStyle newCellStyle = styleMap.get(stHashCode); if(newCellStyle == null){ newCellStyle = destCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(srcCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); destCell.setCellStyle(newCellStyle); switch(srcCell.getCellType()) { case STRING: destCell.setCellValue(srcCell.getStringCellValue()); break; case NUMERIC: destCell.setCellValue(srcCell.getNumericCellValue()); break; case BLANK: destCell.setCellType(CellType.BLANK); break; case BOOLEAN: destCell.setCellValue(srcCell.getBooleanCellValue()); break; case ERROR: destCell.setCellErrorValue(srcCell.getErrorCellValue()); break;
Cell cell; cell = rowxl.createCell(0); cell.setCellValue("ABC"); cell.setCellStyle(style); cell = rowxl.createCell(1); cell.setCellValue("aaa"); cell.setCellStyle(style);
/** * Create a new CellAddress object. * * @param cell the Cell to get the location of */ public CellAddress(Cell cell) { this(cell.getRowIndex(), cell.getColumnIndex()); }
Cell cell = sheet.getRow(i).getCell(0); cell.setCellType ( Cell.CELL_TYPE_STRING ); String j_username = cell.getStringCellValue();
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();
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();
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();
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();
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Employee Data"); 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.");
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(); } } }
@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"); } };
cc.setCellStyle(style); if(obj!=null){ if(obj instanceof String){ cell.setCellValue((String)obj); cell.setCellType(CellType.STRING); }else if(obj instanceof Number){ BigDecimal bigDecimal=Utils.toBigDecimal(obj); cell.setCellValue(bigDecimal.floatValue()); cell.setCellType(CellType.NUMERIC); }else if(obj instanceof Boolean){ cell.setCellValue((Boolean)obj); cell.setCellType(CellType.BOOLEAN); }else if(obj instanceof Image){ Image img=(Image)obj; cell.setCellValue((Date)obj); cc.setCellStyle(style); if(obj!=null){ if(obj instanceof String){ cell.setCellValue((String)obj); cell.setCellType(CellType.STRING); }else if(obj instanceof Number){ BigDecimal bigDecimal=Utils.toBigDecimal(obj); cell.setCellValue(bigDecimal.floatValue()); cell.setCellType(CellType.NUMERIC); }else if(obj instanceof Boolean){
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: break; case Cell.CELL_TYPE_ERROR: System.out.println(cell.getErrorCellValue()); break;
stepData.wb = stepMeta.getExtension().equalsIgnoreCase( "xlsx" ) ? new XSSFWorkbook() : new HSSFWorkbook(); stepData.sheet = stepData.wb.createSheet(); stepData.file = null; stepData.clearStyleCache( numOfFields ); DataFormat format = stepData.wb.createDataFormat(); Row xlsRow = stepData.sheet.createRow( 0 ); Cell cell = xlsRow.createCell( 5 ); CellStyle cellStyle = stepData.wb.createCellStyle(); cellStyle.setBorderRight( BorderStyle.THICK ); cellStyle.setFillPattern( FillPatternType.FINE_DOTS ); cell.setCellStyle( cellStyle ); cellStyle = stepData.wb.createCellStyle(); cellStyle.cloneStyleFrom( cell.getCellStyle() ); cell = xlsRow.createCell( 6 ); cellStyle.setDataFormat( format.getFormat( "##0,000.0" ) ); cell.setCellStyle( cellStyle );
if (cell.getCellType() != Cell.CELL_TYPE_STRING) { throw new IllegalArgumentException("第一行必须全部为字符串,第" + cell.getColumnIndex() + 1 + "有问题"); existHeaderSet.add(cell.getStringCellValue()); existHeader.add(cell.getStringCellValue()); }); lineNumber++; Map<String, Object> tempMap = new HashMap<>(); Iterator<Cell> cellIterator = x.cellIterator(); cellIterator.forEachRemaining(y -> { Object value = null; switch (y.getCellType()) { case Cell.CELL_TYPE_STRING: value = y.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: value = y.getBooleanCellValue(); break; case Cell.CELL_TYPE_NUMERIC: value = y.getNumericCellValue(); break; case Cell.CELL_TYPE_ERROR: String key = headerMapper.get(existHeader.get(y.getColumnIndex())); tempMap.put(key, value); });
org.apache.poi.ss.usermodel.Cell cell = row.getCell(colNum); if(cell!=null){ continue; cell=row.createCell(colNum); com.bstek.ureport.model.Cell cellInfo=null; if(colCell!=null){ Cell cc=rr.getCell(c-skipCol); cc.setCellStyle(style); if(obj!=null){ if(obj instanceof String){ cell.setCellValue((String)obj); cell.setCellType(CellType.STRING); }else if(obj instanceof Number){ BigDecimal bigDecimal=Utils.toBigDecimal(obj); cell.setCellValue(bigDecimal.floatValue()); cell.setCellType(CellType.NUMERIC); }else if(obj instanceof Boolean){ cell.setCellValue((Boolean)obj); cell.setCellType(CellType.BOOLEAN); }else if(obj instanceof Image){ Image img=(Image)obj; cell.setCellValue((Date)obj);
private void copyRange(CellRangeAddress sourceRange, int deltaX, int deltaY, Sheet sourceClone) { //NOSONAR, it's a bit complex but monolith method, does not make much sense to divide it if(deltaX != 0) horizontalFormulaShifter = FormulaShifter.createForColumnCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstColumn(), sourceRange.getLastColumn(), deltaX, sourceSheet.getWorkbook().getSpreadsheetVersion()); if(deltaY != 0) verticalFormulaShifter = FormulaShifter.createForRowCopy(sourceSheet.getWorkbook().getSheetIndex(sourceSheet), sourceSheet.getSheetName(), sourceRange.getFirstRow(), sourceRange.getLastRow(), deltaY, sourceSheet.getWorkbook().getSpreadsheetVersion()); for(int rowNo = sourceRange.getFirstRow(); rowNo <= sourceRange.getLastRow(); rowNo++) { Row sourceRow = sourceClone.getRow(rowNo); // copy from source copy, original source might be overridden in process! for (int columnIndex = sourceRange.getFirstColumn(); columnIndex <= sourceRange.getLastColumn(); columnIndex++) { Cell sourceCell = sourceRow.getCell(columnIndex); if(sourceCell == null) continue; Row destRow = destSheet.getRow(rowNo + deltaY); if(destRow == null) destRow = destSheet.createRow(rowNo + deltaY); Cell newCell = destRow.getCell(columnIndex + deltaX); if(newCell != null) newCell.setCellType(sourceCell.getCellType()); else newCell = destRow.createCell(columnIndex + deltaX, sourceCell.getCellType()); cloneCellContent(sourceCell, newCell, null); if(newCell.getCellType() == CellType.FORMULA) adjustCellReferencesInsideFormula(newCell, destSheet, deltaX, deltaY); } } }
@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()); }