Example usage for org.apache.poi.ss.usermodel Workbook createSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. createSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook createSheet.

Prototype

Sheet createSheet();

Source Link

Document

Create a Sheet for this Workbook, adds it to the sheets and returns the high level representation.

Usage

From source file:CreateTable.java

License:Apache License

public static void main(String[] args) throws FileNotFoundException, IOException {

    Workbook wb = new XSSFWorkbook();
    XSSFSheet sheet = (XSSFSheet) wb.createSheet();

    //Create /*from   www . j  a  v a  2  s  . c om*/
    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(3, 3));
    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(i + j + 0.0);
            }
        }
    }

    FileOutputStream fileOut = new FileOutputStream("ooxml-table.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:adams.data.io.output.ExcelSpreadSheetWriter.java

License:Open Source License

/**
 * Performs the actual writing. The caller must ensure that the writer gets
 * closed.//from w  w  w  . jav a 2s .  c o  m
 *
 * @param content   the spreadsheet to write
 * @param out      the writer to write the spreadsheet to
 * @return      true if successfully written
 */
@Override
protected boolean doWrite(SpreadSheet[] content, OutputStream out) {
    boolean result;
    Workbook workbook;
    Sheet sheet;
    Row row;
    adams.data.spreadsheet.Row spRow;
    adams.data.spreadsheet.Cell spCell;
    Cell cell;
    int i;
    int n;
    int count;
    CellStyle styleDate;
    CellStyle styleDateTime;
    CellStyle styleTime;
    HashSet<String> names;
    String name;

    result = true;

    try {
        if (getWriteOOXML())
            workbook = new XSSFWorkbook();
        else
            workbook = new HSSFWorkbook();
        styleDate = ExcelHelper.getDateCellStyle(workbook, Constants.DATE_FORMAT);
        styleDateTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIMESTAMP_FORMAT);
        styleTime = ExcelHelper.getDateCellStyle(workbook, Constants.TIME_FORMAT);

        count = 0;
        names = new HashSet<>();
        for (SpreadSheet cont : content) {
            if (m_Stopped)
                return false;

            sheet = workbook.createSheet();
            if (cont.getName() != null) {
                name = cont.getName().replace("'", "");
                if (names.contains(name))
                    name += (count + 1);
            } else {
                name = m_SheetPrefix + (count + 1);
            }
            names.add(name);
            workbook.setSheetName(count, name);

            // header
            row = sheet.createRow(0);
            for (i = 0; i < cont.getColumnCount(); i++) {
                cell = row.createCell(i);
                cell.setCellValue(cont.getHeaderRow().getCell(i).getContent());
            }

            // data
            for (n = 0; n < cont.getRowCount(); n++) {
                if (m_Stopped)
                    return false;
                row = sheet.createRow(n + 1);
                spRow = cont.getRow(n);
                for (i = 0; i < cont.getColumnCount(); i++) {
                    cell = row.createCell(i);
                    spCell = spRow.getCell(i);
                    if ((spCell == null) || spCell.isMissing()) {
                        if (m_MissingValue.length() > 0)
                            cell.setCellValue(m_MissingValue);
                        else
                            cell.setCellType(Cell.CELL_TYPE_BLANK);
                        continue;
                    }

                    if (spCell.isFormula() && !m_OutputAsDisplayed) {
                        cell.setCellFormula(spCell.getFormula().substring(1));
                    } else {
                        if (spCell.isDate()) {
                            cell.setCellValue(spCell.toDate());
                            cell.setCellStyle(styleDate);
                        } else if (spCell.isTime()) {
                            cell.setCellValue(spCell.toTime());
                            cell.setCellStyle(styleTime);
                        } else if (spCell.isDateTime()) {
                            cell.setCellValue(spCell.toDateTime());
                            cell.setCellStyle(styleDateTime);
                        } else if (spCell.isNumeric()) {
                            cell.setCellValue(Utils.toDouble(spCell.getContent()));
                        } else {
                            cell.setCellValue(spCell.getContent());
                        }
                    }
                }
            }

            // next sheet
            count++;
        }

        // save
        workbook.write(out);
    } catch (Exception e) {
        result = false;
        getLogger().log(Level.SEVERE, "Failed writing spreadsheet data", e);
    }

    return result;
}

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

static Cell createCell(int row, int column, Date date) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, CELL_TYPE_NUMERIC);
    cell.setCellValue(date);//from w w  w  .  j a va  2 s . com
    CellStyle style = workbook.createCellStyle();
    style.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("m/d/yy h:mm"));
    cell.setCellStyle(style);
    return cell;
}

From source file:bad.robot.excel.matchers.StubCell.java

License:Apache License

private static Cell create(int row, int column, int type) {
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet();
    Cell cell = sheet.createRow(row).createCell(column, type);
    return cell;/*from   w w  w  .jav a 2  s.  c  om*/
}

From source file:bouttime.fileinput.ExcelFileInputTest.java

License:Open Source License

@BeforeClass
public static void setUpClass() throws Exception {
    // Create the input file
    inputFile = new File(INPUT_FILENAME);
    assertNotNull(inputFile);/*from w ww .  ja va  2  s .c o  m*/
    assertTrue(inputFile.createNewFile());
    Workbook wb = new XSSFWorkbook();
    CreationHelper createHelper = wb.getCreationHelper();
    Sheet s = wb.createSheet();

    makeWrestler(s, 0, "David", "Robinson", "Open", "5", "88", "88", "Spurs", "A", "50", "SW");
    makeWrestler(s, 1, "Tim", "Duncan", "Open", "4", "99", "99", "Spurs", "A", "21", "SW");
    makeWrestler(s, 2, "Tony", "Parker", "Rookie", "1", "77", "77", "Spurs", "B", "6", "SW");
    makeWrestler(s, 3, "Manu", "Ginobili", "Rookie", "3", "82", "80", "Spurs", "B", "12", "SW");
    makeWrestler(s, 4, "David", "Robinson", "Open", "5", "88", "88", "Spurs", "A", "50", "SW");

    FileOutputStream outputStream = new FileOutputStream(inputFile);
    wb.write(outputStream);
    outputStream.close();
}

From source file:br.com.itfox.test.Excel.java

public void gerarExcel(boolean simple) {
    try {// ww  w .  j a va 2 s  .  c  o m
        String ini = "01/01/2015";
        String fim = "22/06/2016";
        String seg = "'21','22','23'";
        String areaOper = "'47','24','23','29','4','18','5','48','10','31','43','35','36','7','33','45','3','32','9','39','13','38','16','44','30','15','2','17','12','6','42','41','34','40','1','19','14','26','22','51','46','49','27','25','8','50','52','28','11','20','37','21'";
        BusinessDelegate bd = new BusinessDelegate();
        String path = "/Users/belchiorpalma/Desktop/template/";
        String pathTemplate = "/Users/belchiorpalma/NetBeansProjects/Quest_Iveco/src/br/com/itfox/generator/";
        InputStream is = null;
        try {
            is = new FileInputStream(pathTemplate + "TemplateGic.xlsx");
        } catch (FileNotFoundException ex) {
            // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }
        //try(InputStream is = GeneratorObjectCollection.class.getResourceAsStream(pathTemplate+"TemplateGic.xlsx"))
        // {
        SimpleDateFormat sdf = new SimpleDateFormat("dd_M_yyyy_hh_mm_ss");
        String date = sdf.format(new Date());

        Workbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
        Sheet sh = wb.createSheet();

        ini = (Utils.dateFormat(ini));
        fim = (Utils.dateFormat(fim));

        List<Gic> gics = bd.selectGic(ini, fim, seg, areaOper);
        int i = 0;
        for (Gic g : gics) {
            Row row = sh.createRow(i);
            for (int cellnum = 0; cellnum < 153; cellnum++) {
                Cell cell = row.createCell(cellnum);
                cell.setCellValue(g.getC_nomeproprietario());
            }
            i++;
        }
        /*
        for (int rownum = 0; rownum < 1000000; rownum++) {
            Row row = sh.createRow(rownum);
            for (int cellnum = 0; cellnum < 2; cellnum++) {
                Cell cell = row.createCell(cellnum);
                String address = new CellReference(cell).formatAsString();
                cell.setCellValue(address);
            }
        }*/

        FileOutputStream out;
        try {
            out = new FileOutputStream(path + "object_collection_output.xlsx");
            wb.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            //Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
            ex.printStackTrace();
        }

    } catch (IOException ex) {
        // Logger.getLogger(Excel.class.getName()).log(Level.SEVERE, null, ex);
        ex.printStackTrace();
    }
}

From source file:br.com.tecsinapse.dataio.util.WorkbookUtil.java

License:LGPL

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    replaceColorsPallete(table.getColorsReplaceMap(), wb);

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;//  w  w  w .  j  a v a2  s  .  com
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderRight(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderBottom(BorderStyle.THIN, cellRange, sheet);
                RegionUtil.setBorderLeft(BorderStyle.THIN, cellRange, sheet);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            }
            sheet.autoSizeColumn(i, true);
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                }
                sheet.autoSizeColumn(i, true);
            } else {
                int width = table.getMinOrMaxOrActualCellWidth(defaultColumnWidth.get(i));
                sheet.setColumnWidth(i, width);
            }
        }
    }
    return wb;
}

From source file:br.com.tecsinapse.exporter.util.WorkbookUtil.java

License:LGPL

public Workbook toWorkBook(Workbook wb, Table table) {
    List<List<TableCell>> matrix = table.getCells();
    List<List<TableCell>> matrixFull = table.toTableCellMatrix();

    String sheetName = table.getTitle();
    Sheet sheet = sheetName == null ? wb.createSheet() : wb.createSheet(sheetName);
    int titleRows = 0;
    int r = titleRows;
    int c = 0;//from   w w  w  .ja  va  2 s  . com
    int maxColumns = -1;
    Map<Integer, Integer> defaultColumnWidth = new HashMap<>();

    ExporterFormatter tableExporterFormatter = table.getExporterFormatter();

    for (List<TableCell> row : matrix) {
        Row sheetRow = sheet.createRow(r);

        for (TableCell tableCell : row) {
            while (matrixFull.get(r - titleRows).get(c) == EmptyTableCell.EMPTY_CELL) {
                c++;
                if (c >= matrixFull.get(r - titleRows).size()) {
                    c = 0;
                    r++;
                }
            }

            Cell cell = sheetRow.createCell(c);
            if (c > maxColumns) {
                maxColumns = c;
            }

            if (tableCell.getRowspan() > 1 || tableCell.getColspan() > 1) {
                int rowStart = r;
                int rowEnd = rowStart + (tableCell.getRowspan() - 1);
                int colStart = c;
                int colEnd = colStart + (tableCell.getColspan() - 1);

                CellRangeAddress cellRange = new CellRangeAddress(rowStart, rowEnd, colStart, colEnd);
                sheet.addMergedRegion(cellRange);

                RegionUtil.setBorderTop(1, cellRange, sheet, wb);
                RegionUtil.setBorderRight(1, cellRange, sheet, wb);
                RegionUtil.setBorderBottom(1, cellRange, sheet, wb);
                RegionUtil.setBorderLeft(1, cellRange, sheet, wb);
            } else if (!table.isAutoSizeColumnSheet()) {
                Integer maxColumnWidth = defaultColumnWidth.get(c);
                if (maxColumnWidth == null) {
                    defaultColumnWidth.put(c, tableCell.getDefaultColumnWidth());
                } else {
                    int defaultWidth = tableCell.getDefaultColumnWidth();
                    if (defaultWidth > maxColumnWidth) {
                        defaultColumnWidth.put(c, defaultWidth);
                    }
                }
            }

            String format = setConvertedValue(cell, tableCell, tableExporterFormatter);
            setCellStyle(cell, tableCell, wb, format);
            c++;
        }
        r++;
        c = 0;
    }

    if (table.isAutoSizeColumnSheet()) {
        for (int i = 0; i <= maxColumns; ++i) {
            if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
            } else {
                sheet.autoSizeColumn(i, true);
            }
        }
    } else {
        for (int i = 0; i <= maxColumns; ++i) {
            if (defaultColumnWidth.get(i) == null) {
                if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).trackColumnForAutoSizing(i);
                } else {
                    sheet.autoSizeColumn(i, true);
                }
            } else {
                sheet.setColumnWidth(i, defaultColumnWidth.get(i));
            }
        }
    }
    return wb;
}

From source file:business.SongExcelParser.java

private void songsToWorkbook(Workbook wb, ArrayList<SongContainer> songContainerList) {
    Row row;//from   w w w  . jav  a  2  s  .c  o m
    int rowNumber = 1;
    Sheet sheet = wb.createSheet();
    row = setRowHeader(sheet);
    for (SongContainer container : songContainerList) {
        for (Song song : container.getSongs()) {
            row = sheet.createRow(rowNumber);
            setRowInfo(row, song, container);
            rowNumber++;
        }
    }
    for (int x = 0; x < sheet.getRow(0).getPhysicalNumberOfCells(); x++) {
        sheet.autoSizeColumn(x);
    }
}

From source file:ch.dbs.actions.reports.HoldingsReport.java

License:Open Source License

private HSSFWorkbook getXLSContent(final Konto k) {
    final Text cn = new Text();
    final Workbook wb = new HSSFWorkbook();
    final Sheet s = wb.createSheet();

    try {/*from   ww  w.j  a va 2 s  .  c  om*/
        // add header for XLS
        initXLS(wb, s);

        // internal holdings are visible
        final List<Bestand> stock = new Bestand().getAllKontoBestand(k.getId(), true, cn.getConnection());

        short rowNr = 0;

        for (final Bestand b : stock) {
            rowNr++;
            // add holdings
            getXLSLine(wb, s, b, rowNr);
        }

        // adjust all columns in size
        short columnNr = 0;
        while (columnNr < 21) {
            s.autoSizeColumn(columnNr);
            columnNr++;
        }

    } finally {
        cn.close();
    }

    return (HSSFWorkbook) wb;
}