Spreadsheet Reporting

Main Page | Discussion | Edit | History | Help | Switch to MediaWiki mode

---

Contents 1 Instantiate a new workbook 2 Access the document associated with a workbook 3 Initialize a blank workbook 4 Lock/Unlock the workbook to improve performance and ensure thread safety 5 Read workbook (SOD or XLS) from InputStream 6 Read workbook (SOD or XLS) from filesystem 7 Read from BLOB 8 Get Array of ReportParameters 9 Getting a specific ReportParameter 10 Setting Report Parameters 11 Refresh the datasource 12 Insert one or more sheets 13 Add a Sheet to the right of all current sheets 14 Set the contents of a cell 15 Set a formula for a cell 16 Set the fill color of a cell 17 Save the current state of the spreadsheet 18 Writing a document to the filesystem 19 Writing workbook to OutputStream 20 Writing entire workbook to an HTML Table 21 Write a range to HTML

Instantiate a new workbook

BookModel book = BookModel.Factory.create();
//or
JBook book = new JBook();

Access the document associated with a workbook

Document doc =  book.getBook().getDocument();

Initialize a blank workbook

book.initWorkbook();

Lock/Unlock the workbook to improve performance and ensure thread safety

book.getLock();
try {
  ...
} catch(F1Exception ex){
  ...
}finally {
  book.releaseLock();
}

Read workbook (SOD or XLS) from InputStream

Document doc = new Document(null, getServletContext().getResourceAsStream("/WEB-INF/templates/template.sod"), new DocumentOpenCallback());
BookModel book = BookModel.Factory.create(doc.getBook());

Read workbook (SOD or XLS) from filesystem

Document doc = new Document(null, path/file.sod, new DocumentOpenCallback());
BookModel book = BookModel.Factory.create(doc.getBook());

Read from BLOB

The byte array represents the spreadsheet. This byte array could come from a file upload mechanism, a spreadsheet stored in a database, etc.

Document doc = new Document(null, bytearray, new DocumentOpenCallback());
BookModel book = BookModel.Factory.create(doc.getBook());

Get Array of ReportParameters

ReportParameter[] rp = book.getReportParameterCollection().get();

Getting a specific ReportParameter

ReportParameter rp = book.getReportParameterCollection().get("ParameterName");

Setting Report Parameters

rp.setValue("ParameterValue");

Refresh the datasource

This executes the queries that are part of this workbook object. This is only valid for e.Spreadsheet SOD files

book.getDataSourceCollection().refresh();

Insert one or more sheets

int sheetIndex = 0;
int sheetsToInsert = 2;
book.insertSheets(sheet, sheets);

Add a Sheet to the right of all current sheets

int numSheets = book.getNumSheets();
book.setNumSheets(numSheets + 1);

Set the contents of a cell

book.setEntry(int sheet, int row, int col, "value");

Set a formula for a cell

book.setFormula(int sheet, int row, int col, "SUM(A1:B3)");

Set the fill color of a cell

CellFormat cf = book.getCellFormat();
FillFormat ff = cf.fill();
ff.setForeColor(JBook.color2RGB(Color.RED));
book.setCellFormat(cf, int sheet1, int row1, int col1, int sheet2, int row2, int col2);

Save the current state of the spreadsheet

saves active cell, active sheet, zoom level, etc

book.saveViewInfo();

Writing a document to the filesystem

doc.fileSaveAs(new java.io.File("path/filename"), DocumentType.EXCEL_97_REPORT_VIEW, new DocumentSaveCallback());
--or
doc.fileSaveCopyAs(new java.io.File("path/filename"), DocumentType.EXCEL_97_REPORT_VIEW, new DocumentSaveCallback());
--or
doc.fileSave(new DocumentSaveCallback());

Writing workbook to OutputStream

doc.fileSaveAs(outputStream, DocumentType.EXCEL_97_REPORT_VIEW, new DocumentSaveCallback());
--or
doc.fileSaveCopyAs(outputStream, DocumentType.EXCEL_97_REPORT_VIEW, new DocumentSaveCallback());

Writing entire workbook to an HTML Table

HTMLWriter htmlWriter = new HTMLWriter();
htmlWriter.write(book.getBook(), java.io.Writer);

Write a range to HTML

HTMLWriter htmlWriter = new HTMLWriter();
htmlWriter.write(book.getBook(), iSheet1, iRow1, iCol1, iSheet2, iRow2, iCol2, java.io.Writer);

---

This page has been accessed 4,517 times. This page was last modified 04:50, 29 November 2007.