Howto examples

How to:

---

Create an Excel Workbook and add tabs:

SpreadsheetWorkbook workbook = new SpreadsheetWorkbook();
SpreadsheetTab tab1 = workbook.createTab("First Tab");  // tabs are the same as POI sheets
SpreadsheetTab tab2 = workbook.createTab("Second Tab");

---

Create a Taro workbook from an existing POI workbook

XSSFWorkbook poiWorkbook = // get this from somewhere, perhaps with tabs and cells already populated
SpreadsheetWorkbook workbook = new SpreadsheetWorkbook(poiWorkbook);
SpreadsheetTab firstTab = workbook.getTab(0);                      // get by tab index
SpreadsheetTab tabByName = workbook.getTab("Existing tab title");  // get by tab name

---

Write a workbook to a file:

OutputStream out = new FileOutputStream("filename.xlsx");
workbook.write(out);

---

Return a workbook in an HTTP response from a servlet, controller, etc.:

HttpServletResponse response = // get from servlet or controller
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition","inline; filename=\\"myExcelFile.xlsx\\"");
OutputStream out = response.getOutputStream();
excelWorkbook.write(out);

---

Create a cell style

SpreadsheetCellStyle style = new SpreadsheetCellStyle().withAlign(CellStyle.ALIGN_LEFT).withBold(true)
        .withBottomBorder(CellStyle.BORDER_THIN);

or use one of the built-in starting styles, either directly or with additions:

SpreadsheetCellStyle style = SpreadsheetCellStyle.CENTER_ONE_DECIMAL.withBold(true);

---

Apply a cell style to a cell in a tab

tab.setStyle("B7", style);  // with cell address
tab.setStyle(1, 6, style);  // with row and col index (1, 6 also points to B7)

---

Apply a cell style to a range of cells

tab.setStyle("A1", "B7", style);  // apply to the block from A1 to B7
tab.setStyle(0, 6, 0, 1, style);  // apply to the block from A1 to B7

---

Set a value on a cell

// Set row A using cell addresses
tab.setValue("A1", "Some text");
tab.setValue("A2", 26.5);
tab.setValue("A3", new Date());

// Set row B using row/col indexes
tab.setValue(0, 1, "Some text");  // B1
tab.setValue(1, 1, 26.5);         // B2
tab.setValue(2, 1, new Date());   // B3

---

Set a style at the same time we set a value

tab.setValue("A1", "some text", style);
tab.setValue(0, 1, "some text", style);

---

Write a set of headers across the row

tab.printAcross("A1", style, "Apple", "Orange", "Pear");
tab.printAcross(0, 1, style, "Apple", "Orange", "Pear");

---

Fill in a list of numbers down a column

tab.printDown("A1", style, "Apple", "Orange", "Pear");
tab.printDown(0, 1, style, "Apple", "Orange", "Pear");

---

Surround a table with a border

tab.setSurroundBorder("A1", "B7", CellStyle.BORDER_THIN);
tab.setSurroundBorder(0, 6, 0, 1, CellStyle.BORDER_THIN);

---

Add multiline text to a cell

tab.setValue("A1", "Multi-line \n text", SpreadsheetCellStyle.DEFAULT.withWrapText(true));

---

Merge cells

tab.mergeCells("A1", "B2", "some text", style);

---

Add an image to a workbook

byte[] imageBytes = // read the image from somewhere
tab.addPicture("A1", imageBytes, Workbook.PICTURE_TYPE_PNG);

---

Automatically resize rows and columns to fit the content I added

tab.autosizeRowsAndCols();

---