Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:org.talend.mdm.webapp.browserecords.server.servlet.DownloadData.java

License:Open Source License

protected void fillSheet(XSSFSheet sheet) throws Exception {
    entity = org.talend.mdm.webapp.browserecords.server.util.CommonUtil.getEntityModel(concept, language);
    List<String> results = new LinkedList<String>();

    WSViewPK wsViewPK = new WSViewPK(viewPk);
    WSView wsView = CommonUtil.getPort().getView(new WSGetView(wsViewPK));

    if (idsList != null && idsList.size() > defaultMaxExportCount) {
        idsList = idsList.subList(0, defaultMaxExportCount);
    }//from   w ww.j  a  v a 2  s .  c o  m

    String[] result = null;
    if (idsList != null && idsList.size() > FETCH_SIZE) {
        for (int i = 0; i < idsList.size(); i = i + FETCH_SIZE) {
            int toIndex = i + FETCH_SIZE;
            if (toIndex > idsList.size()) {
                toIndex = idsList.size();
            }
            result = fetchResultWithIdList(wsViewPK, wsView, idsList.subList(i, toIndex));
            if (result.length > 1) {
                results.addAll(Arrays.asList(Arrays.copyOfRange(result, 1, result.length)));
            }
        }
    } else {
        result = fetchResultWithIdList(wsViewPK, wsView, idsList);
        if (result.length > 1) {
            results = Arrays.asList(Arrays.copyOfRange(result, 1, result.length));
        }
    }

    for (int i = 0; i < results.size(); i++) {
        Document document = XmlUtil.parseText(results.get(i));
        XSSFRow row = sheet.createRow(i + 1);
        fillRow(row, document);
    }
}

From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java

/**
 * This function is used by Unit Test to export a {@link XSSFWorkbook} with all of the submissions as separate rows
 * //from   www  .j  a  va 2s  .c  o m
 * This is needed because the Unit Test does not run through the ExportService (which is the iterator of {@link List} {@link Submission})
 * 
 * @param submissions
 *            - list of submissions
 * @param columns
 *            - the columns to include in the sheet
 * @return - The Excel workbook file (xssf format only)
 */
public XSSFWorkbook testWorkbook(List<Submission> submissions, List<SearchOrder> columns) {

    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow header = sheet.createRow(0);

    int i = 1; // row counter

    for (Submission sub : submissions) {
        XSSFRow row = sheet.createRow(i);
        processWorkbookRow(header, row, sub, columns);
        i++;
    }
    return wb;
}

From source file:org.tdl.vireo.export.impl.ExcelPackagerImpl.java

/**
 * Takes {@link XSSFWorkbook} argument, a single {@link Submission}, and a {@link List} of {@link SearchOrder}.
 * /*from   w w w  .j a  v a2  s.  c  o m*/
 * Creates a new {@link XSSFSheet} in the {@link XSSFWorkbook} and adds a header {@link XSSFRow} and a data {@link XSSFRow}.
 * 
 * Passes the header row, the data row, the submission, and the {@link SearchOrder} {@link List} to processWorkbookRow()
 * 
 * @param wb
 *            - the workbook to modify in-place
 * @param sub
 *            - the submission to add to the workbook
 * @param columns
 *            - the columns to include in the sheet
 */
public XSSFWorkbook writeWorkbook(Submission sub, List<SearchOrder> columns) {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow header = sheet.createRow(0);
    XSSFRow row = sheet.createRow(1);
    processWorkbookRow(header, row, sub, columns);
    return wb;
}

From source file:org.tdl.vireo.model.jpa.JpaSubmissionImpl.java

private Attachment generateActionLogAttachment() {
    // create an Excel Workbook to store the action log as an attachment
    Attachment actionLogAttachment = null;
    String sheetName = "ActionLog";
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet(sheetName);
    XSSFRow header = sheet.createRow(0);
    int rowNum = 1;
    XSSFRow row = sheet.createRow(rowNum);
    int colNum = 0;
    for (ActionLog actionLog : actionLogs) {
        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Action Date");
        }/*from ww w . ja v  a2 s . co m*/
        row.createCell(colNum).setCellValue(actionLog.getActionDate());
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Type");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getType().name());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Date");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getDate());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Name");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getName());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Attachment Size");
        }
        if (actionLog.getAttachment() != null) {
            row.createCell(colNum).setCellValue(actionLog.getAttachment().getSize());
        }
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Action Entry");
        }
        row.createCell(colNum).setCellValue(actionLog.getEntry());
        colNum++;

        if (rowNum == 1) {
            header.createCell(colNum).setCellValue("Submission State");
        }
        row.createCell(colNum).setCellValue(actionLog.getSubmissionState().getBeanName());
        colNum++;

        rowNum++;
        row = sheet.createRow(rowNum);
        colNum = 0;
    }
    try {
        File actionLogFile = File.createTempFile("actionlog-", ".xlsx");
        actionLogFile.deleteOnExit();
        FileOutputStream actionLogFileOS = new FileOutputStream(actionLogFile);
        wb.write(actionLogFileOS);
        actionLogFileOS.flush();
        actionLogFileOS.close();
        actionLogAttachment = new ActionLogAttachment(this, actionLogFile);
    } catch (IOException e) {
        play.Logger.error("Error while generating Action Log Attachment! [%s]", e);
    }
    return actionLogAttachment;
}

From source file:org.wise.vle.web.VLEGetXLS.java

License:Open Source License

/**
 * Create the row in the sheet/*from www.  ja  v  a 2 s.c  om*/
 * 
 * @param sheet the sheet to create the row in
 * @param rowCounter the row index
 * 
 * @return the new row or null if the sheet does not exist
 */
private Row createRow(XSSFSheet sheet, int rowCounter) {
    Row newRow = null;

    if (sheet != null) {
        newRow = sheet.createRow(rowCounter);
    }

    return newRow;
}

From source file:packtest.AligningCells.java

License:Apache License

public static void main(String[] args) throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);
    row.setHeightInPoints(30);/*from w w  w.  j  a  va  2  s .c o  m*/
    for (int i = 0; i < 8; i++) {
        //column width is set in units of 1/256th of a character width
        sheet.setColumnWidth(i, 256 * 15);
    }

    createCell(wb, row, (short) 0, XSSFCellStyle.ALIGN_CENTER, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 1, XSSFCellStyle.ALIGN_CENTER_SELECTION, XSSFCellStyle.VERTICAL_BOTTOM);
    createCell(wb, row, (short) 2, XSSFCellStyle.ALIGN_FILL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 3, XSSFCellStyle.ALIGN_GENERAL, XSSFCellStyle.VERTICAL_CENTER);
    createCell(wb, row, (short) 4, XSSFCellStyle.ALIGN_JUSTIFY, XSSFCellStyle.VERTICAL_JUSTIFY);
    createCell(wb, row, (short) 5, XSSFCellStyle.ALIGN_LEFT, XSSFCellStyle.VERTICAL_TOP);
    createCell(wb, row, (short) 6, XSSFCellStyle.ALIGN_RIGHT, XSSFCellStyle.VERTICAL_TOP);

    //center text over B4, C4, D4
    row = sheet.createRow((short) 3);
    centerAcrossSelection(wb, row, (short) 1, (short) 3, XSSFCellStyle.VERTICAL_CENTER);

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("xssf-align.xlsx");
    wb.write(fileOut);
    fileOut.close();

    wb.close();
}

From source file:packtest.CalendarDemo.java

License:Apache License

public static void main(String[] args) throws Exception {

    Calendar calendar = Calendar.getInstance();
    if (args.length > 0)
        calendar.set(Calendar.YEAR, Integer.parseInt(args[0]));

    int year = calendar.get(Calendar.YEAR);

    XSSFWorkbook wb = new XSSFWorkbook();
    Map<String, XSSFCellStyle> styles = createStyles(wb);

    for (int month = 0; month < 12; month++) {
        calendar.set(Calendar.MONTH, month);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        //create a sheet for each month
        XSSFSheet sheet = wb.createSheet(months[month]);

        //turn off gridlines
        sheet.setDisplayGridlines(false);
        sheet.setPrintGridlines(false);/*from  w ww. jav  a2  s.c o  m*/
        XSSFPrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setOrientation(PrintOrientation.LANDSCAPE);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);

        //the header row: centered text in 48pt font
        XSSFRow headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(80);
        XSSFCell titleCell = headerRow.createCell(0);
        titleCell.setCellValue(months[month] + " " + year);
        titleCell.setCellStyle(styles.get("title"));
        sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1"));

        //header with month titles
        XSSFRow monthRow = sheet.createRow(1);
        for (int i = 0; i < days.length; i++) {
            //for compatibility with HSSF we have to set column width in units of 1/256th of a character width
            sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide
            sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide
            sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1));
            XSSFCell monthCell = monthRow.createCell(i * 2);
            monthCell.setCellValue(days[i]);
            monthCell.setCellStyle(styles.get("month"));
        }

        int cnt = 1, day = 1;
        int rownum = 2;
        for (int j = 0; j < 6; j++) {
            XSSFRow row = sheet.createRow(rownum++);
            row.setHeightInPoints(100);
            for (int i = 0; i < days.length; i++) {
                XSSFCell dayCell_1 = row.createCell(i * 2);
                XSSFCell dayCell_2 = row.createCell(i * 2 + 1);

                int day_of_week = calendar.get(Calendar.DAY_OF_WEEK);
                if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) {
                    dayCell_1.setCellValue(day);
                    calendar.set(Calendar.DAY_OF_MONTH, ++day);

                    if (i == 0 || i == days.length - 1) {
                        dayCell_1.setCellStyle(styles.get("weekend_left"));
                        dayCell_2.setCellStyle(styles.get("weekend_right"));
                    } else {
                        dayCell_1.setCellStyle(styles.get("workday_left"));
                        dayCell_2.setCellStyle(styles.get("workday_right"));
                    }
                } else {
                    dayCell_1.setCellStyle(styles.get("grey_left"));
                    dayCell_2.setCellStyle(styles.get("grey_right"));
                }
                cnt++;
            }
            if (calendar.get(Calendar.MONTH) > month)
                break;
        }
    }

    // Write the output to a file
    FileOutputStream out = new FileOutputStream("calendar-" + year + ".xlsx");
    wb.write(out);
    out.close();

    wb.close();
}

From source file:packtest.CreateTable.java

License:Apache License

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

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

    //Create /* ww w  .  ja  va  2  s  .c o m*/
    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(Utils.getPath("ooxml-table.xlsx"));
    wb.write(fileOut);
    fileOut.close();
}

From source file:packtest.HeadersAndFooters.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    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();//from ww w.j  a  va  2  s  .c om
    //&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(Utils.getPath("headerFooter.xlsx"));
    wb.write(fileOut);
    fileOut.close();

}

From source file:packtest.WorkingWithRichText.java

License:Apache License

public static void main(String[] args) throws Exception {

    XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();
    try {/*from w  w  w  . j  av a  2  s.  c  om*/
        XSSFSheet sheet = wb.createSheet();
        XSSFRow row = sheet.createRow((short) 2);

        XSSFCell cell = row.createCell(1);
        XSSFRichTextString rt = new XSSFRichTextString("The quick brown fox");

        XSSFFont font1 = wb.createFont();
        font1.setBold(true);
        font1.setColor(new XSSFColor(new java.awt.Color(255, 0, 0)));
        rt.applyFont(0, 10, font1);

        XSSFFont font2 = wb.createFont();
        font2.setItalic(true);
        font2.setUnderline(XSSFFont.U_DOUBLE);
        font2.setColor(new XSSFColor(new java.awt.Color(0, 255, 0)));
        rt.applyFont(10, 19, font2);

        XSSFFont font3 = wb.createFont();
        font3.setColor(new XSSFColor(new java.awt.Color(0, 0, 255)));
        rt.append(" Jumped over the lazy dog", font3);

        cell.setCellValue(rt);

        // Write the output to a file
        OutputStream fileOut = new FileOutputStream(Utils.getPath("xssf-richtext.xlsx"));
        try {
            wb.write(fileOut);
        } finally {
            fileOut.close();
        }
    } finally {
        wb.close();
    }
}