/** * Sreate an Sheet for this Workbook, adds it to the sheets and returns * the high level representation. Use this to create new sheets. * * @return Sheet representing the new sheet. */ @Override public SXSSFSheet createSheet() { return createAndRegisterSXSSFSheet(_wb.createSheet()); }
/** * Create an Sheet for this Workbook, adds it to the sheets and returns * the high level representation. Use this to create new sheets. * * @param sheetname sheetname to set for the sheet. * @return Sheet representing the new sheet. * @throws IllegalArgumentException if the name is greater than 31 chars or contains <code>/\?*[]</code> */ @Override public SXSSFSheet createSheet(String sheetname) { return createAndRegisterSXSSFSheet(_wb.createSheet(sheetname)); }
protected XSSFDialogsheet createDialogsheet(String sheetname, CTDialogsheet dialogsheet) { XSSFSheet sheet = createSheet(sheetname); return new XSSFDialogsheet(sheet); }
/** * 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); }
/** * @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; }
protected void nextSheet(String sheetName, int colSplit, int rowSplit, boolean view) { currentSheet = workbook.createSheet(sheetName); currentDrawing = currentSheet.createDrawingPatriarch(); currentSheet.createFreezePane(colSplit, rowSplit); currentRowNumber = -1; headerCellCount = 0; if (view) { currentSheet.setTabColor(VIEW_TAB_COLOR); } }
XSSFSheet clonedSheet = createSheet(newName);
XSSFSheet sheet = workbook.createSheet("Employee Data");
import org.apache.poi.xssf.usermodel.*; //import needed XSSFWorkbook wb = new XSSFWorkbook (); // Create workbook XSSFSheet sheet = wb.createSheet(); // Create spreadsheet in workbook XSSFRow row = sheet.createRow(rowIndex); // Create the row in the spreadsheet //1. Create the date cell style XSSFCreationHelper createHelper = wb.getCreationHelper(); XSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("MMMM dd, yyyy")); //2. Apply the Date cell style to a cell //This example sets the first cell in the row using the date cell style cell = row.createCell(0); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle);
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);
public static void main(String[] args) throws FileNotFoundException, IOException, InvalidFormatException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = (XSSFSheet) wb.createSheet(); //Create some data to build the pivot table on setCellData(sheet); XSSFPivotTable pivotTable = sheet.createPivotTable(new AreaReference("A1:D4"), new CellReference("H5")); //Configure the pivot table //Use first column as row label pivotTable.addRowLabel(0); //Sum up the second column pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1); //Set the third column as filter pivotTable.addColumnLabel(DataConsolidateFunction.AVERAGE, 2); //Add filter on forth column pivotTable.addReportFilter(3); FileOutputStream fileOut = new FileOutputStream("ooxml-pivottable.xlsx"); wb.write(fileOut); fileOut.close(); }
XSSFWorkbook wb = new XSSFWorkbook(); CellStyle borderStyle = wb.createCellStyle(); borderStyle.setBorderBottom(CellStyle.BORDER_THIN); borderStyle.setBorderLeft(CellStyle.BORDER_THIN); borderStyle.setBorderRight(CellStyle.BORDER_THIN); borderStyle.setBorderTop(CellStyle.BORDER_THIN); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); Sheet sheet = wb.createSheet("Test Sheet"); Row row = sheet.createRow(1); for (int i = 1; i <= 5; ++i) { Cell cell = row.createCell(i); cell.setCellStyle(borderStyle); if (i == 1) { cell.setCellValue("Centred Text"); } } sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 5));
public ExcelSheet getSheet(String name) { if (sheets.containsKey(name)) { return sheets.get(name); } else { Sheet sheet = workbook.createSheet(name); ExcelSheet excelsheet = new ExcelSheet(this, name, sheet); sheets.put(name, excelsheet); return excelsheet; } }
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Sample.xlsx"); //Create a new row in current sheet Row row = sheet.createRow(0); //Create a new cell in current row Cell cell = row.createCell(0); //Set value to new value cell.setCellValue("Blahblah"); //finally write data to excel file FileOutputStream out = new FileOutputStream(new File("C:\\test.xls")); workbook.write(out); out.close();
@Override protected void buildExcelDocument(Map<String, Object> model, XSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { XSSFSheet sheet = workbook.createSheet(deliveryByPalletTypeXlsService.getReportTitle(LocaleContextHolder.getLocale())); deliveryByPalletTypeXlsService.buildExcelContent(workbook,sheet,model, LocaleContextHolder.getLocale()); } }
@Override protected void buildExcelDocument(Map<String, Object> filters, XSSFWorkbook xssfWorkbook, HttpServletRequest httpServletRequest, HttpServletResponse httpServletResponse){ XSSFSheet sheet = xssfWorkbook.createSheet( plannedEventsXlsService.getReportTitle(LocaleContextHolder.getLocale())); plannedEventsXlsService.buildExcelContent(xssfWorkbook, sheet,filters, LocaleContextHolder.getLocale()); } }
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Data Validation"); XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(new String[]{"11", "21", "31"}); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0); XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation( dvConstraint, addressList); validation.setShowErrorBox(true); sheet.addValidationData(validation);
XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("test"); Row row = sheet.createRow(0); Cell inCell = row.createCell(0); inCell.setCellValue("01.0234500"); Cell outCell = row.createCell(1); FormulaEvaluator fev = wb.getCreationHelper().createFormulaEvaluator(); String value = inCell.getStringCellValue(); outCell.setCellFormula("VALUE(" + value + ")"); fev.evaluateInCell(outCell);
XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); XSSFCell cellvalue = sheet.createRow(0).createCell(0); sheet.getRow(0).createCell(1).setCellValue(2); sheet.getRow(0).createCell(2).setCellValue(5); cellvalue.setCellFormula("B1+C1"); if (cellvalue.getCellType() == XSSFCell.CELL_TYPE_FORMULA) { evaluator.evaluateFormulaCell(cellvalue); } System.out.println(cellvalue.getNumericCellValue());
protected void nextSheet(String sheetName, int colSplit, int rowSplit, boolean view) { currentSheet = workbook.createSheet(sheetName); currentDrawing = currentSheet.createDrawingPatriarch(); currentSheet.createFreezePane(colSplit, rowSplit); currentRowNumber = -1; headerCellCount = 0; if (view) { currentSheet.setTabColor(VIEW_TAB_COLOR); } }