/** * @param index * @return * @throws Xlsx4jException * @ since 3.0.1 */ public WorksheetPart getWorksheet(int index) throws Xlsx4jException { List<Sheet> sheets; try { sheets = this.getContents().getSheets().getSheet(); } catch (Docx4JException e1) { throw new Xlsx4jException(e1.getMessage(), e1); } int zeroBasedCount = sheets.size() -1; if (index< 0 || index>zeroBasedCount) { throw new Xlsx4jException("No sheet at index " + index + ". (There are " + sheets.size() + " sheets) "); } try { Sheet s = sheets.get(index); return (WorksheetPart)this.getRelationshipsPart().getPart(s.getId()); } catch (Exception e) { throw new Xlsx4jException("Sheet " + index + " not found", e); } }
/** * Create an instance of {@link Workbook } * */ public Workbook createWorkbook() { return new Workbook(); }
Workbook workerBook = new Workbook(); Worksheet worksheet = workerBook.getWorksheets().get(0); //Display word "Date" in cell B4 Cell cellB4 = worksheet.getCells().get("B4"); cellB4.putValue("Date"); //Display word "Time" in cell C4 Cell cellC4 = worksheet.getCells().get("C4"); cellC4.putValue("Time"); //Save the workbook in xlsx format workerBook.save("output.xlsx", SaveFormat.XLSX);
protected void validateTemplateContainsNamedRange() { if (Objects.isNull(template.getWorkbook().getDefinedNames())) { throw wrapWithReportingException("An error occurred while rendering document from template. Template does not contain named ranges"); } }
Workbook book = new Workbook(); Worksheet sheet = book.getWorksheets().get(0); int styleIndex = book.getStyles().add(); Style styleUnlocked = book.getStyles().get(styleIndex); book.save(dataDir + "protectedrange.xlsx");
//Open the first excel file. Workbook SourceBook1 = new Workbook("F:\\Downloads\\charts.xlsx"); //Define the second source book. //Open the second excel file. Workbook SourceBook2 = new Workbook("F:\\Downloads\\picture.xlsx"); //Combining the two workbooks SourceBook1.combine(SourceBook2); //Save the target book file. SourceBook1.save("F:\\Downloads\\combined.xlsx");
ArrayList<String> list = new ArrayList<>(); Workbook workbook = new Workbook("spreadsheet.xlsx"); Worksheet worksheet = workbook.getWorksheets().get("Sheet1"); for (Object o: worksheet.getCells().getRows()) { Cell cell = ((Row) o).getCellByIndex(0); list.add(cell.getStringValueWithoutFormat()); }
//Create workbook object Workbook wb = new Workbook("source.xlsx"); //This is how to save Workbook to XLS format MemoryStream ms1 = new MemoryStream(); wb.Save(ms1, SaveFormat.Excel97To2003); //Just to check if memory stream is good and contains XLS bytes byte[] b1 = ms1.ToArray(); File.WriteAllBytes("output.xls", b1); //This is how to save Workbook to XLSX format MemoryStream ms2 = new MemoryStream(); wb.Save(ms2, SaveFormat.Xlsx); //Just to check if memory stream is good and contains XLSX bytes byte[] b2 = ms2.ToArray(); File.WriteAllBytes("output.xlsx", b2);
protected void init() { try { template = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent())); result = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent())); result.getWorkbook().getCalcPr().setCalcMode(STCalcMode.AUTO); result.getWorkbook().getCalcPr().setFullCalcOnLoad(true); marshaller = XmlCopyUtils.createMarshaller(Context.jcSML); unmarshaller = XmlCopyUtils.createUnmarshaller(Context.jcSML); } catch (Exception e) { throw wrapWithReportingException(String.format("An error occurred while loading template [%s]", reportTemplate.getDocumentName()), e); } }
//Suppose your person id is 7, so your data will be inserted in row 7 int personId = 7; //Suppose that person's data is 5, then it will be inserted in column E (A, B, C, D, E, F, G...) int personData = 5; Workbook workbook = new Workbook(); Worksheet worksheet = workbook.getWorksheets().get(0); String cellName = CellsHelper.cellIndexToName(personId - 1, personData - 1); worksheet.getCells().get(cellName).putValue(personData); workbook.save("output.xlsx", SaveFormat.XLSX);
public CTDefinedName getDefinedName(String name) { List<CTDefinedName> definedName = workbook.getDefinedNames().getDefinedName(); CTDefinedName targetRange = null; for (CTDefinedName namedRange : definedName) { if (name.equals(namedRange.getName())) { targetRange = namedRange; } } return targetRange; }
Workbook workbook = new Workbook(); Cells cells = workbook.getWorksheets().get(0).getCells(); ListObjectCollection tables = workbook.getWorksheets().get(0).getListObjects(); tables.add(0, 0, totalNoOfRows - 1, totalNoOfColumns - 1, listObject.getShowHeaderRow());
protected void init() { try { template = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent())); result = Document.create(SpreadsheetMLPackage.load(reportTemplate.getDocumentContent())); result.getWorkbook().getCalcPr().setCalcMode(STCalcMode.AUTO); result.getWorkbook().getCalcPr().setFullCalcOnLoad(true); marshaller = XmlCopyUtils.createMarshaller(Context.jcSML); unmarshaller = XmlCopyUtils.createUnmarshaller(Context.jcSML); } catch (Exception e) { throw wrapWithReportingException(String.format("An error occurred while loading template [%s]", reportTemplate.getDocumentName()), e); } }
Sheets sheets = wb.getJaxbElement().getSheets();
int headerRows = 3; Workbook workerBook = new Workbook(); Worksheet workSheet = workerBook.getWorksheets().get(0); //style.setTextWrapped(true); Cell workSheetCell = workSheet.getCells().get("A1"); workSheet.getCells().merge(3, 0, headerRows, 1); workSheet.getCells().merge(3, 1, headerRows, 1); workSheet.getCells().merge(3, 2, headerRows, 1); // workSheet.getCells().get("C4").putValue("Time"); workSheet.getCells().get("A4").putValue("Respondent"); workSheet.getCells().get("B4").putValue("Date"); workSheet.getCells().get("C4").putValue("Time"); workerBook.save(DIR + "SettingData.xltx", SaveFormat.XLTX);
public CTDefinedName getDefinedName(String name) { List<CTDefinedName> definedName = workbook.getDefinedNames().getDefinedName(); CTDefinedName targetRange = null; for (CTDefinedName namedRange : definedName) { if (name.equals(namedRange.getName())) { targetRange = namedRange; } } return targetRange; }
/** * Create an instance of {@link Workbook } * */ public Workbook createWorkbook() { return new Workbook(); }