Examples with Workbook org.apache.poi.ss.usermodel.Workbook used on opensource projects

Example 16 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class CreateTable method main.

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();
    //Create 
    XSSFTable table = sheet.createTable();
    table.setDisplayName("Test");
    CTTable cttable = table.getCTTable();
    //Style configurations
    CTTableStyleInfo style = cttable.addNewTableStyleInfo();
    style.setName("TableStyleMedium2");
    style.setShowColumnStripes(false);
    style.setShowRowStripes(true);
    //Set which area the table should be placed in
    AreaReference reference = new AreaReference(new CellReference(0, 0), new CellReference(2, 2));
    cttable.setRef(reference.formatAsString());
    cttable.setId(1);
    cttable.setName("Test");
    cttable.setTotalsRowCount(1);
    CTTableColumns columns = cttable.addNewTableColumns();
    columns.setCount(3);
    CTTableColumn column;
    XSSFRow row;
    XSSFCell cell;
    for (int i = 0; i < 3; i++) {
        //Create column
        column = columns.addNewTableColumn();
        column.setName("Column");
        column.setId(i + 1);
        //Create row
        row = sheet.createRow(i);
        for (int j = 0; j < 3; j++) {
            //Create cell
            cell = row.createCell(j);
            if (i == 0) {
                cell.setCellValue("Column" + j);
            } else {
                cell.setCellValue("0");
            }
        }
    }
    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 17 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class CreateUserDefinedDataFormats method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    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("ooxml_dataFormat.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 18 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class WorkingWithBorders method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("borders");
    // Create a row and put some cells in it. Rows are 0 based.
    Row row = sheet.createRow((short) 1);
    // Create a cell and put a value in it.
    Cell cell = row.createCell((short) 1);
    cell.setCellValue(4);
    // Style the cell with borders all around.
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setBorderLeft(BorderStyle.THIN);
    style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
    style.setBorderRight(BorderStyle.THIN);
    style.setRightBorderColor(IndexedColors.BLUE.getIndex());
    style.setBorderTop(BorderStyle.MEDIUM_DASHED);
    style.setTopBorderColor(IndexedColors.BLACK.getIndex());
    cell.setCellStyle(style);
    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-borders.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 19 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class HeadersAndFooters method main.

public static void main(String[] args) throws IOException {
    //or new HSSFWorkbook();
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = wb.createSheet("first-header - format sheet");
    sheet.createRow(0).createCell(0).setCellValue(123);
    //set page numbers in the footer
    Footer footer = sheet.getFooter();
    //&P == current page number
    //&N == page numbers
    footer.setRight("Page &P of &N");
    Header firstHeader = ((XSSFSheet) sheet).getFirstHeader();
    //&F == workbook file name
    firstHeader.setLeft("&F ......... first header");
    for (int i = 0; i < 100; i = i + 10) {
        sheet.createRow(i).createCell(0).setCellValue(123);
    }
    XSSFSheet sheet2 = (XSSFSheet) wb.createSheet("odd header-even footer");
    Header oddHeader = sheet2.getOddHeader();
    //&B == bold
    //&E == double underline
    //&D == date
    oddHeader.setCenter("&B &E oddHeader     &D ");
    Footer evenFooter = sheet2.getEvenFooter();
    evenFooter.setRight("even footer &P");
    sheet2.createRow(10).createCell(0).setCellValue("Second sheet with an oddHeader and an evenFooter");
    for (int i = 0; i < 200; i = i + 10) {
        sheet2.createRow(i).createCell(0).setCellValue(123);
    }
    XSSFSheet sheet3 = (XSSFSheet) wb.createSheet("odd header- odd footer");
    sheet3.createRow(10).createCell(0).setCellValue("Third sheet with oddHeader and oddFooter");
    Header oddH = sheet3.getOddHeader();
    //&C == centered
    oddH.setCenter("centered oddHeader");
    oddH.setLeft("left ");
    oddH.setRight("right ");
    Footer oddF = sheet3.getOddFooter();
    oddF.setLeft("Page &P");
    oddF.setRight("Pages &N ");
    FileOutputStream fileOut = new FileOutputStream("headerFooter.xlsx");
    wb.write(fileOut);
    fileOut.close();
    wb.close();
}

Example 20 with Workbook

use of org.apache.poi.ss.usermodel.Workbook in project poi by apache.

the class IterateCells method main.

public static void main(String[] args) throws IOException {
    Workbook wb = new XSSFWorkbook(new FileInputStream(args[0]));
    for (int i = 0; i < wb.getNumberOfSheets(); i++) {
        Sheet sheet = wb.getSheetAt(i);
        System.out.println(wb.getSheetName(i));
        for (Row row : sheet) {
            System.out.println("rownum: " + row.getRowNum());
            for (Cell cell : row) {
                System.out.println(cell);
            }
        }
    }
    wb.close();
}