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:poi.xslf.usermodel.PieChartDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    if (args.length < 2) {
        usage();/*w  ww .  j av  a  2 s  . com*/
        return;
    }

    BufferedReader modelReader = new BufferedReader(new FileReader(args[1]));

    String chartTitle = modelReader.readLine(); // first line is chart title

    XMLSlideShow pptx = new XMLSlideShow(new FileInputStream(args[0]));
    XSLFSlide slide = pptx.getSlides()[0];

    // find chart in the slide
    XSLFChart chart = null;
    for (POIXMLDocumentPart part : slide.getRelations()) {
        if (part instanceof XSLFChart) {
            chart = (XSLFChart) part;
            break;
        }
    }

    if (chart == null)
        throw new IllegalStateException("chart not found in the template");

    // embedded Excel workbook that holds the chart data
    POIXMLDocumentPart xlsPart = chart.getRelations().get(0);
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet = wb.createSheet();

    CTChart ctChart = chart.getCTChart();
    CTPlotArea plotArea = ctChart.getPlotArea();

    CTPieChart pieChart = plotArea.getPieChartArray(0);
    //Pie Chart Series
    CTPieSer ser = pieChart.getSerArray(0);

    // Series Text
    CTSerTx tx = ser.getTx();
    tx.getStrRef().getStrCache().getPtArray(0).setV(chartTitle);
    sheet.createRow(0).createCell(1).setCellValue(chartTitle);
    String titleRef = new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString();
    tx.getStrRef().setF(titleRef);

    // Category Axis Data
    CTAxDataSource cat = ser.getCat();
    CTStrData strData = cat.getStrRef().getStrCache();

    // Values
    CTNumDataSource val = ser.getVal();
    CTNumData numData = val.getNumRef().getNumCache();

    strData.setPtArray(null); // unset old axis text
    numData.setPtArray(null); // unset old values

    // set model
    int idx = 0;
    int rownum = 1;
    String ln;
    while ((ln = modelReader.readLine()) != null) {
        String[] vals = ln.split("\\s+");
        CTNumVal numVal = numData.addNewPt();
        numVal.setIdx(idx);
        numVal.setV(vals[1]);

        CTStrVal sVal = strData.addNewPt();
        sVal.setIdx(idx);
        sVal.setV(vals[0]);

        idx++;
        XSSFRow row = sheet.createRow(rownum++);
        row.createCell(0).setCellValue(vals[0]);
        row.createCell(1).setCellValue(Double.valueOf(vals[1]));
    }
    numData.getPtCount().setVal(idx);
    strData.getPtCount().setVal(idx);

    String numDataRange = new CellRangeAddress(1, rownum - 1, 1, 1).formatAsString(sheet.getSheetName(), true);
    val.getNumRef().setF(numDataRange);
    String axisDataRange = new CellRangeAddress(1, rownum - 1, 0, 0).formatAsString(sheet.getSheetName(), true);
    cat.getStrRef().setF(axisDataRange);

    // updated the embedded workbook with the data
    OutputStream xlsOut = xlsPart.getPackagePart().getOutputStream();
    wb.write(xlsOut);
    xlsOut.close();

    // save the result
    FileOutputStream out = new FileOutputStream("pie-chart-demo-output.pptx");
    pptx.write(out);
    out.close();
}

From source file:poi.xssf.usermodel.examples.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);//  w  w  w.  j  a  v  a2  s .co 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();
}

From source file:poi.xssf.usermodel.examples.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);//  www.j a  v  a  2s  . co 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();
}

From source file:poi.xssf.usermodel.examples.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   w ww .  j  a  v a2s.  co  m*/
    //&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();

}

From source file:poi.xssf.usermodel.examples.WorkingWithRichText.java

License:Apache License

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

    XSSFWorkbook wb = new XSSFWorkbook(); //or new HSSFWorkbook();

    XSSFSheet sheet = wb.createSheet();
    XSSFRow row = sheet.createRow((short) 2);

    XSSFCell cell = row.createCell(1);/*from ww w .  ja  v  a2s. com*/
    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
    FileOutputStream fileOut = new FileOutputStream("xssf-richtext.xlsx");
    wb.write(fileOut);
    fileOut.close();
}

From source file:pricecomparator.fileOutput.java

private void printOut() {
    try {//w w w. ja v a2 s.  c o m
        //stream = new FileOutputStream(new File(System.getProperty("user.dir") + File.separator + "PriceCompare" +".xlsx"));            
        stream = new FileOutputStream(new File(path + File.separator + "PriceCompare" + ".xlsx"));
        workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Table");
        XSSFRow row;
        Cell cell;
        row = sheet.createRow(0);
        for (int i = 0; i < columns.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(columns[i]);
        }

        for (int i = 1; i < shopItems.size(); i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < columns.length; j++) {
                cell = row.createCell(j);
                switch (j) {
                case 0:
                    cell.setCellValue(shopItems.get(i - 1).upc); //shop upc
                    break;
                case 1:
                    cell.setCellValue(amazonItems.get(i - 1).title); //Amazon Title                                              
                    break;
                case 2:
                    cell.setCellValue(amazonItems.get(i - 1).url); //Amazon url
                    break;
                case 3:
                    cell.setCellValue(amazonItems.get(i - 1).price); //Amazon price                            
                    break;
                case 4:
                    cell.setCellValue(shopItems.get(i - 1).title); //shop Title
                    break;
                case 5:
                    cell.setCellValue(shopItems.get(i - 1).sku); //shop sku
                    break;
                case 6:
                    cell.setCellValue(shopItems.get(i - 1).url); //shop price                            
                    break;
                case 7:
                    cell.setCellValue(shopItems.get(i - 1).price); //ASIN
                    break;
                case 8:
                    cell.setCellValue(amazonItems.get(i - 1).ASIN); //Sales Rank
                    break;
                case 9:
                    cell.setCellValue(amazonItems.get(i - 1).rank); //Sales Rank
                    break;
                }
            }
        }
        workbook.write(stream);
        stream.close();
    } catch (Exception e) {
        System.err.println("com.mycompany.pricecomparator.fileOutput.printOut: " + e);
    }
}

From source file:pt.webdetails.cda.exporter.PivotXlsExporter.java

License:Open Source License

private void writePivotColumns(MetadataTableModel table, XSSFSheet sheet, PivotTableData pivotTableData,
        String[] pivotGroupColumns) {
    // create first header row
    CellStyle headerCellStyle = sheet.getRow(0).getCell(0).getCellStyle();
    Row header = sheet.createRow(0);
    boolean processingPivotColumns = false;
    int columnsToCreateIndex = 0;
    while (true) {
        if (!processingPivotColumns) {
            Cell cell = header.createCell(columnsToCreateIndex);
            cell.setCellStyle(headerCellStyle);
            cell.setCellValue("");
            if (pivotTableData.lastFixedColumnIndex == columnsToCreateIndex) {
                processingPivotColumns = true;
            } else {
                ++columnsToCreateIndex;//www. jav  a 2s.co  m
            }
        } else {
            // create one column group for each columnGroupTitle

            for (String groupTitle : pivotTableData.groupTitleSet) {
                boolean writeTitle = true;
                for (String columnTitle : pivotGroupColumns) {
                    Cell cell = header.createCell(++columnsToCreateIndex);
                    cell.setCellStyle(headerCellStyle);
                    if (writeTitle) {
                        cell.setCellValue(groupTitle);
                        writeTitle = false;
                    }
                }
            }
            break;
        }
    }
    // create second header row
    header = sheet.createRow(1);
    processingPivotColumns = false;
    columnsToCreateIndex = 0;
    while (true) {
        if (!processingPivotColumns) {
            Cell cell = header.createCell(columnsToCreateIndex);
            cell.setCellStyle(headerCellStyle);
            cell.setCellValue(table.getColumnName(columnsToCreateIndex));
            if (pivotTableData.lastFixedColumnIndex == columnsToCreateIndex) {
                processingPivotColumns = true;
            } else {
                ++columnsToCreateIndex;
            }
        } else {
            // create one column group for each columnGroupTitle
            for (String groupTitle : pivotTableData.groupTitleSet) {
                for (String columnTitle : pivotGroupColumns) {
                    Cell cell = header.createCell(++columnsToCreateIndex);
                    cell.setCellStyle(headerCellStyle);
                    cell.setCellValue(columnTitle);
                }
            }
            break;
        }
    }
    // finish creating header rows
    sheet.createFreezePane(0, 2);
    pivotTableData.pivotTableColumnNumber = columnsToCreateIndex + 1;
    logger.debug("columns lastFixedColumnIndex=" + pivotTableData.lastFixedColumnIndex + " columnNumber="
            + pivotTableData.pivotTableColumnNumber);
}

From source file:pt.webdetails.cda.exporter.PivotXlsExporter.java

License:Open Source License

private void writePivotRows(XSSFSheet sheet, PivotTableData pivotTableData, String[] pivotGroupColumns) {
    // create sheet content
    CellStyle rowCellStyle = sheet.getRow(2).getCell(0).getCellStyle();
    int sheetRowIdx = 2;
    for (String rowGroupSelector : pivotTableData.rowGroupSelectors) {
        Row row = sheet.createRow(sheetRowIdx++);
        int sheetRowColumnIdx = 0;
        for (String rowGroupDataValue : pivotTableData.rowGroupData.get(rowGroupSelector)) {
            Cell cell = row.createCell(sheetRowColumnIdx++);
            cell.setCellStyle(rowCellStyle);
            cell.setCellValue(rowGroupDataValue);
        }//from   w  ww.j a  va2  s.  co  m
        Map<String, List<String>> currentRow = pivotTableData.pivotData.get(rowGroupSelector);
        for (String columnGroupTitle : pivotTableData.groupTitleSet) {
            List<String> columnGroupData = currentRow.get(columnGroupTitle);
            if (columnGroupData != null) {
                for (String columnData : columnGroupData) {
                    Cell cell = row.createCell(sheetRowColumnIdx++);
                    cell.setCellStyle(rowCellStyle);
                    cell.setCellValue(columnData);
                }
            } else {
                // no data for this column group in this row
                for (String columnTitle : pivotGroupColumns) {
                    Cell cell = row.createCell(sheetRowColumnIdx++);
                    cell.setCellStyle(rowCellStyle);
                    cell.setCellValue("");
                }
            }
        }

    }
}

From source file:punchcardrecords.ui.PunchCardRecordsMainFrame.java

License:Open Source License

/**
 * Excel/*from w  ww  .ja v  a  2s  .  c o  m*/
 * @param year 
 */
private void writeYearCountResult(Map<String, double[]> yearRecords, String path) {
    XSSFWorkbook resultWorkBook = new XSSFWorkbook();
    XSSFSheet sheet = resultWorkBook.createSheet();
    Set<String> userNumNames = yearRecords.keySet();
    // 
    String[] titles = { "?", "??", "()", "(?)",
            "?(?)" };
    int col = 0;// 
    XSSFRow titleRow = sheet.createRow(0);
    for (String title : titles) {
        titleRow.createCell(col).setCellValue(title);
        sheet.autoSizeColumn((short) (col));
        col++;
    }
    int row = 1;// 
    // 
    for (String userStr : userNumNames) {
        XSSFRow recordRow = sheet.createRow(row);
        int innerCol = 0;// ?
        // ?
        String[] userInfos = userStr.split(":");
        for (String userInfo : userInfos) {
            recordRow.createCell(innerCol).setCellValue(userInfo);
            innerCol++;
        }
        // 
        double[] records = yearRecords.get(userStr);
        for (double record : records) {
            recordRow.createCell(innerCol).setCellValue(record);
            innerCol++;
        }
        // ?
        recordRow.createCell(innerCol)
                .setCellValue(new BigDecimal(records[0] == 0 ? 0 : records[1] / records[0])
                        .setScale(1, BigDecimal.ROUND_HALF_UP).doubleValue());
        row++;
    }

    // ???,??
    JFileChooser fileSaveChooser = new JFileChooser();

    fileSaveChooser.setDialogTitle("?");
    fileSaveChooser.setSelectedFile(new File(path + "/.xlsx"));
    String[] saveType = { "xlsx" };
    fileSaveChooser.setAcceptAllFileFilterUsed(false);
    fileSaveChooser.setFileFilter(new FileNameExtensionFilter("*.xlsx", saveType));
    int saveResult = fileSaveChooser.showSaveDialog(this);
    if (saveResult == JFileChooser.APPROVE_OPTION) {
        File saveFile = fileSaveChooser.getSelectedFile();

        // ???
        String saveFilePath = saveFile.getAbsolutePath();
        addMessage("?,??->" + saveFilePath);
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(saveFile);
            resultWorkBook.write(out);
            out.close();
        } catch (FileNotFoundException ex) {
            Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        Object[] options = { "", "", ",?" };
        int response = JOptionPane.showOptionDialog(this,
                "??,???", "?", JOptionPane.YES_OPTION,
                JOptionPane.QUESTION_MESSAGE, null, options, options[0]);
        if (0 == response) {// 
            // ??
            addMessage(",??");
            try {
                Desktop.getDesktop().open(saveFile);
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else if (1 == response) {// 
            addMessage(",??");
            String[] cmd = new String[5];
            cmd[0] = "cmd";
            cmd[1] = "/c";
            cmd[2] = "start";
            cmd[3] = " ";
            cmd[4] = saveFile.getAbsolutePath().substring(0, saveFile.getAbsolutePath().lastIndexOf("\\"));
            try {
                Runtime.getRuntime().exec(cmd);
            } catch (IOException ex) {
                Logger.getLogger(PunchCardRecordsMainFrame.class.getName()).log(Level.SEVERE, null, ex);
            }
        } else {
            alert("??,?()");
        }
    } else {
        // ??,?
        clearMessage();
        fileName.setText("");
        // ???
        addMessage("??");
    }
}

From source file:regression.gui.MainWindow.java

void createExcelFile(List<Double> xes) {
    try {/*from   w w w  . ja  va2 s  .com*/
        String filename = "single.xlsx";
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("FirstSheet");
        int rownum = 0;
        for (Iterator<Double> iterator = xes.iterator(); iterator.hasNext();) {

            Double next = iterator.next();
            XSSFRow row = sheet.createRow(rownum);
            row.createCell(0).setCellValue(next);
            row.createCell(1).setCellValue(0.0);
            rownum++;
        }

        FileOutputStream fileOut = new FileOutputStream(filename);
        workbook.write(fileOut);
        fileOut.close();

        System.out.println("Stworzono");
    } catch (Exception ex) {
        System.out.println(ex);
    }
}